Depending on the specific needs of your project, you may want null values to be sorted either at the beginning or at the end of the results. In this article, we’ll explore how to sort values in a MySQL database with null values so that null values go first or last.

Sample Data

To better showcase the results of this example, we can use the following script to generate some sample data:

CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

INSERT INTO users (id, name, age) VALUES
(1, 'John', NULL),
(2, 'Jane', 25),
(3, 'Bob', 40),
(4, 'Alice', 30),
(5, 'Sarah', NULL),
(6, 'David', 35),
(7, 'Emma', 22),
(8, 'Tom', NULL),
(9, 'Grace', 28),
(10, 'Michael', 45);

This script creates a table called users with three columns: ‘id’, ‘name’, and ‘age’. We’re inserting ten rows of data, including some null values in the “age” column.

Sorting null values first

To sort null values first, we can use the IS NULL operator in combination with the ASC keyword to sort the null values in ascending order:

SELECT * FROM users ORDER BY age IS NULL ASC, age ASC;

This will produce the following result:

+----+---------+------+
| id |  name   | age  |
+----+---------+------+
|  1 | John    | NULL |
|  5 | Sarah   | NULL |
|  8 | Tom     | NULL |
|  7 | Emma    |   22 |
|  2 | Jane    |   25 |
|  9 | Grace   |   28 |
|  4 | Alice   |   30 |
|  6 | David   |   35 |
|  3 | Bob     |   40 |
| 10 | Michael |   45 |
+----+---------+------+

As you can see, the null values in the ‘age’ column appear first in the results, followed by the non-null values sorted in ascending order.

Sorting null values last

Now, in order to sort null values last, we can use the IS NULL operator in combination with the DESC keyword to sort the null values in descending order:

SELECT * FROM users ORDER BY age IS NULL DESC, age ASC;

This will produce the following result:

+----+---------+------+
| id |  name   | age  |
+----+---------+------+
|  7 | Emma    |   22 |
|  2 | Jane    |   25 |
|  9 | Grace   |   28 |
|  4 | Alice   |   30 |
|  6 | David   |   35 |
|  3 | Bob     |   40 |
| 10 | Michael |   45 |
|  1 | John    | NULL |
|  5 | Sarah   | NULL |
|  8 | Tom     | NULL |
+----+---------+------+

As you can see, the non-null values in the ‘age’ column appear first in the results, sorted in ascending order, followed by the non-null values.

To do so, we use the same SELECT statement as before, but we modify the ORDER BY clause and we break it in the following parts:

• the first part of the ORDER BY clause uses the IS NULL operator to identify any null values in the ‘age’ column, then we order this expression in descending order, so that all null values appear last in the results,
• the second part of the ORDER BY clause sorts the remaining non-null values in ascending order.

Conclusion

Sorting values in a MySQL database with null values requires special consideration to ensure that null values are sorted correctly. By using the IS NULL operator in combination with the ASC or DESC keyword, we can sort null values first or last depending on our specific needs.

In this tutorial, we’ll show you how to connect, get the size of tables in gigabytes and order them from larger to the smaller in three common database servers, MySQL, PostgreSQL and SQL Server.

MySQL

Step 1. Connect to the database

In order to connect to MySQL server using the command-line tools, you need to run the following command:

mysql -u username -p password -h hostname database_name

Replace username, password, hostname, and database_name with the appropriate values for your MySQL database.

Step 2. Get the Table Sizes

Once you’re connected to the database, you can use the following SQL query to get the size of each table:

SELECT *
FROM (SELECT TABLE_SCHEMA                                                AS TABLE_SCHEMA,
             TABLE_NAME                                                  AS TABLE_NAME,
             ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 3) AS `Size (GB)`
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')) T
ORDER BY T.`Size (GB)` DESC;

Output Results:

+---------------+----------------------+------------+
| Table_Schema  | Table_Name           | Size (GB)  |
+---------------+----------------------+------------+
| eshop         | orders               | 10.43      |
| eshop         | customers            | 4.12       |
| eshop         | products             | 2.86       |
| eshop         | order_items          | 1.98       |
| music_app     | categories           | 0.24       |
| music_app     | reviews              | 0.05       |
+---------------+----------------------+------------+

Command-Line tools:

• MySQL Community Server: https://dev.mysql.com/downloads/mysql/
• MySQL Shell: https://dev.mysql.com/downloads/shell/

PostgreSQL

Step 1. Connect to the database

In order to connect to MySQL server using the command-line tools, you need to run the following command:

psql -h hostname -d database_name -U username -W

Replace hostname, database_name, and username with the appropriate values for your PostgreSQL database. You will be prompted to enter your password after running this command.

Step 2. Get the Table Sizes

Once you’re connected to the database, you can use the following SQL query to get the size of each table:

SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size (GB)`
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY `Size (GB)` DESC;

Output Results:

+----------------------+------------+
| Table                | Size       |
+----------------------+------------+
| orders               | 10 GB      |
| customers            | 4 GB       |
| products             | 2.8 GB     |
| order_items          | 2 GB       |
| categories           | 240 MB     |
| reviews              | 50 MB      |
+----------------------+------------+

Command-Line tools:

• PostgreSQL: https://www.postgresql.org/download/
• pgAdmin: https://www.pgadmin.org/download/

SQL Server

Step 1. Connect to the database

In order to connect to SQL Server, you can use sqlcmd command-line tool and run the following command:

sqlcmd -S servername -U username -P password -d database_name

Replace servername, username, password, and database_name with the appropriate values for your SQL Server database.

Step 2. Get the Table Sizes

Once you’re connected to the database, you can use the following SQL query to get the size of each table:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 / 1024 AS TotalSizeGB
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSizeGB DESC;

Output Results:

+--------------------+---------------+-------------+--------------+
| TableName          | SchemaName    | RowCounts   | TotalSizeGB  |
+--------------------+---------------+-------------+--------------+
| orders             | dbo           | 10,000,000  | 10.43        |
| customers          | dbo           | 5,000,000   | 4.12         |
| products           | dbo           | 2,500,000   | 2.86         |
| order_items        | dbo           | 1,000,000   | 1.98         |
| categories         | dbo           | 250,000     | 0.24         |
| reviews            | dbo           | 50,000      | 0.05         |
+--------------------+---------------+-------------+--------------+

Command-Line tools:

• SQL Server Management Studio
  https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
• sqlcmd: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

Note

To override this property value in the Azure App Service, special attention needs to be given to how the key will be declared under the Configuration section for the nested application setting, as there is a small difference between the Windows and the Linux App Service.

In the Windows App Service Configuration, the : is used; on the other hand, in the Linux App Service Configuration, instead of the : (colon) the __(double underscore) has to be used for the nested application setting key.

So, in order to access the Default log level from a configuration file such as the following:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

the following can be used:

• Logging:LogLevel:Defaultfor apps hosted on the Azure App Service on Windows
• Logging__LogLevel__Defaultfor apps hosted on the Azure App Service on Linux

and then, in the code, that app settings value can be accessed as usual using _configuration.GetValue<string>("Logging:LogLevel:Default");

How to test it

The following example presents a very simple web api application that consists of a single SettingsController and a GET method that returns the Default log level from the application settings file:

To update the nested configuration property in the Configuration section on each Azure App Service, it’s important to remember that on Linux :must be changed to __(double underscore), as shown in the following picture:

After doing so, when the setting value is fetched from each web api, the appropriate result will appear based on the relevant settings:

• `GET https://linux-sample-api.azurewebsites.net/api/settings` -> “Debug”
• `GET https://windows-sample-api.azurewebsites.net/api/settings` -> “Debug”

instead of Information which is declared in the appsettings.json file.

So, let’s get started with the basics.

What is Infrastructure as Code (IaC)?

Infrastructure as Code (IaC) is the managing and provisioning of infrastructure through code instead of manual processes. This approach offers a lot of benefits, as new infrastructure can be provisioned faster and with better consistency, since those files are the single source of truth. Furthermore, there is full traceability of the changes each code file imposes.

There are several tools to use in order to implement infrastructure as code for Azure solutions, such as Azure Resource Manager (ARM) templates or Bicep. However, Terraform has started appealing to me more and more, given that it provides the same way of working for multiple environments and it is really easy to get started with.

So, what is Terraform?

Terraform is an open-source infrastructure as code software tool that allows you to build, change and version infrastructure safely and efficiently.

It generates an execution plan, describing what it will do and asks for approval before making any infrastructure changes. This allows the review of changes before Terraform creates, updates or destroys infrastructure. Another important benefit of using Terraform is that it supports multi-cloud scenarios, so a developer can use the same tool to manage resources of different cloud providers.

To start working with Terraform, I strongly suggest you have a look at the Get Started guide, available on their website, which provides a quick overview of the basics regarding installation, main commands and state to keep track of metadata.

Let’s get started with an example

Using the configuration file below, the following resources can be provisioned on Azure:

1. an Azure app service plan;
2.  a storage account and a blob container;
3. Application Insights; and
4. an Azure app service that is using .NET framework 5.0, a startup command, a few settings, and that is connected with the Application Insights and the storage account created above.

How to run the file to provision the resources

In order to provision the resources described in the .tf file please follow these steps:

Step 1. Download the file

Download the file and store it in a folder. Let’s assume that the folder is under this path: D:\Workspace\IaC\.

Step 2. Login to Azure and select the subscription to use

In case you are not logged into an Azure account, please use the Azure CLI and terminal to login and set the desired subscription as default, using the following commands:

# follow instructions to login to portal
$ az login 

# list all available subscriptions
$ az account list -o table 

# change default subscription
$ az account set --subscription "<subscription>"

Step 3. Initialize and run the script

While you are at the directory where the .tf file is stored, the init command  terraform init needs to be run first to initialize the working directory containing the Terraform configuration files:

D:\Workspace\IaC>terraform init

Initializing the backend...

Initializing provider plugins...
- Finding latest version of hashicorp/random...
- Finding hashicorp/azurerm versions matching "2.46.0"...
- Installing hashicorp/azurerm v2.46.0...
- Installed hashicorp/azurerm v2.46.0 (signed by HashiCorp)
- Installing hashicorp/random v3.1.0...
- Installed hashicorp/random v3.1.0 (signed by HashiCorp)

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

D:\Workspace\IaC>

Then, the plan command  terraform plan to create an execution plan:

D:\Workspace\IaC>terraform plan

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with
the following symbols:
  + create

Terraform will perform the following actions:

  # azurerm_app_service.webapp will be created
...

Plan: 7 to add, 0 to change, 0 to destroy.

And, finally, the apply command terraform apply to execute the actions proposed in the Terraform plan:

D:\Workspace\IaC>terraform apply

# or if you want to use custom values
D:\Workspace\IaC>terraform apply -var="name=<your_name>" -var="region=northeurope"

After a few seconds, you will be asked if you want to continue executing the plan described in the previous step.

Once you answer yes to the prompt command, all the resources will start being created in the Azure account you have previously logged in.

Step 4. Clean Up

To delete all the resources that have been created and go back to previous state, just run the destroy command terraform destroy and, in a few minutes, all previous changes will be cleared. After that you can start all over again.

D:\Workspace\IaC>terraform destroy

My talk was about a Durable Functions, an extension of Azure Functions that lets you write stateful functions in a serverless compute environment.

Source Code

You can view clone and run the code for the demo from the following link, https://github.com/ppolyzos/demo-mlsa-functions.

Slides

About a year ago we moved to Luxembourg and my old Macbook died, so I had to replace it with a new one that unfortunately has the German layout:

The problem is that even if you set the keyboard layout to US in MacOS when you hit the key next to 1, this symbol starts appearing §. I don’t have anything with the symbol but in my day to day it’s totally useless and I started missing my beloved symbol (~) which is so often used in coding.

Long story short, I had to find a way to swap places, through software, between the key, right next to left Shift key, and the one right next to 1. To do so, I experimented with different apps, scripts, etc. but what worked at the end is the following.

Swap tilde ~ key with §

Open your favourite terminal Terminal, iTerm, etc.. and run the following script:

hidutil property --set '{"UserKeyMapping":[{"HIDKeyboardModifierMappingSrc":0x700000035,"HIDKeyboardModifierMappingDst":0x700000064},{"HIDKeyboardModifierMappingSrc":0x700000064,"HIDKeyboardModifierMappingDst":0x700000035}]}'

Now if you press the key next to 1 it will show up as ` and the key next to shift as §. This solution works until next restart.

If you want to make it permanent you need to run the following scripts:

Step 1. Store the following script in your home directory

cat << 'EOF' > ~/.tilde-switch && chmod +x ~/.tilde-switch
hidutil property --set '{"UserKeyMapping":[{"HIDKeyboardModifierMappingSrc":0x700000035,"HIDKeyboardModifierMappingDst":0x700000064},{"HIDKeyboardModifierMappingSrc":0x700000064,"HIDKeyboardModifierMappingDst":0x700000035}]}'
EOF

You can check your script at ~/.tilde-switch file your home directory.

Step 2. Create a task to run the file from step 1 on every startup

Run the following command, it will require admin privileges:

sudo /usr/bin/env bash -c "cat > /Library/LaunchAgents/org.custom.tilde-switch.plist" << EOF
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
  <dict>
    <key>Label</key>
    <string>org.custom.tilde-switch</string>
    <key>Program</key>
    <string>${HOME}/.tilde-switch</string>
    <key>RunAtLoad</key>
    <true/>
    <key>KeepAlive</key>
    <false/>
  </dict>
</plist>
EOF

Step 3. Activate the task from step 2

Run the following command:

sudo launchctl load -w -- /Library/LaunchAgents/org.custom.tilde-switch.plist

Undo the key swap

If you want to go back to the previous key setup, before the swap, you can do the following:

Undo the only switching script

hidutil property --set '{"UserKeyMapping":[{"HIDKeyboardModifierMappingSrc":0x700000035,"HIDKeyboardModifierMappingDst":0x700000035},{"HIDKeyboardModifierMappingSrc":0x700000064,"HIDKeyboardModifierMappingDst":0x700000064}]}'

Undo all three steps

sudo launchctl unload -w -- /Library/LaunchAgents/org.custom.tilde-switch.plist; sudo rm -f -- /Library/LaunchAgents/org.custom.tilde-switch.plist ~/.tilde-switch

Quick Update for MacOS 14.2+

After the update to MacOS 14.2, it seems that the above commands do not work and the only way I found that this works is by using Karabiner-Elements and just adding the following entry in Simple Modification – For all devices – `non_us_backslash` -> `grave_accent_and_titlde`.

Run the following command:

az storage blob delete-batch --account-key <storage_account_key> --account-name <storage_account_name> --source <container_name>

where:

• <storage_account_key> is the Primary or Secondary key of your storage account and must be used in conjunction with storage account name,
• <storage_account_name> is the name of your storage name and
• <container_name> is the container name you want to delete files from.

and in a few seconds all your files will be deleted from the specified azure storage container.

Errors

If you get an error in regards to Blob Snapshots like this one, This operation is not permitted because the blob has snapshots. ErrorCode: SnapshotsPresent, add the --delete-snapshots with inlcude or only parameter:

az storage blob delete-batch --account-key <storage_account_key> --account-name <storage_account_name> --source <container_name> --delete-snapshots include

Retrieve Storage Account Id

If you want to retrieve Storage Account key, there are two options. One from the portal and the other one from command line using the following command:

az storage account keys list --subscription <subscription_id> -n <storage_account_name> --query [].{ID:value} --output table

where:

• <subscription_id> is the subscription id that your storage account is using,
• <storage_account_name> is the name of your storage account,
• --query is used to fetch only the data you need from the results and
• --output table is to display results in a table format

The audio is in Greek, so if you are brave enough with the language, enjoy

Source Code

You can view clone and run the code for the demo from the following link, https://github.com/ppolyzos/azureheads-stocks-demo

Slides

The problem is that, by default, the name used in the Azure Active Directory is “Default Directory” which seems a little confusing, especially if you have access to multiple directories and subscriptions.

So, how do you change the name to something more comprehensible?

Follow these steps and you’ll be ready in no time:

1. Select the Azure Active Directory service from Azure Portal:

2. At the left sidebar, under the Manage section, choose Properties. Then in Directory Properties choose a new name:

3. Refresh the portal and you are all set

Video

A very useful tool that enables users to schedule tasks to run periodically at a specified date/time in the future, thus allowing the automation of a lot of tasks and processes that otherwise would require human intervention or another type of coordination.

 Basics

The order of the six fields in Azure is:
`{second} {minute} {hour} {day} {month} {day of the week} { year (optional)}`.

Examples

A CRON expression that can be used as a trigger that executes every three minutes looks like: 0 */3 * * * *.

Or a CRON expression that executes “at minute 5 past every hour from 4 through 7 in February and March”, looks like: `5 4-7 * 2,3 *` (details).

Tools

CRONTab Guru

In my opinion, the best tool out there to write and validate your cron schedule expressions: