In the last article, I walked you through a wizard that created an ADX table and populated it with data. you can also create and manage tables from a command line interface.

You can run the examples in this article in either the ADX Data Explorer web page (Fig. 1) or in Kusto.Explorer - a rich client Windows application that you can download for free from here.

Azure Data Explorer page
Fig. 1

Before you get started, you must create an ADX Cluster and create an ADX database.

Creating a Table

The .create table command allows you to create a new table. The syntax is:

.create table tablename
(
column1:datatype1,
column2:datatype2,
column3:datatype3,
.
.
.
columnN:datatypeN,
)

where columnN is the name of a column and datatypeN is the data type of that column. You will add as many column:datatype entries as columns to add.

For example, use the following to create a table named customers with 3 fields: FullName, YtdSales, and LastOrderDate

.create table customers
(
FullName:string, 
LastOrderDate:datetime,
YtdSales:decimal
)

You can view the structure of this table with the following command:

.show table customers

The output of the command is shown in Fig. 2.

Results of .show table command
Fig. 2

Ingesting Data

There are multiple ways to ingest data into a table. To ingest values from the command line, use the .ingest command.

The syntax is:

.ingest inline into table tablename <|
list_of_data_for_records

where:

  • tablename is the name of the table into which you wish to insert data
  • list_of_data_for_records is a comma-delimited list of values to insert into the table. The list of values should be in the same order as the columns in the table. Repeat this list to insert multiple rows.

For example, the following command inserts two rows into the customers table we created above:

.ingest inline into table customers <| 
'David Giard', datetime(2022-01-10 09:00:00), 10000
'Tim Giard', datetime(2022-01-09 10:30:00), 100000

Modifying a Table

If you try to execute the .create table command above twice, it will not succeed the second time. The table already exists and it cannot be created a second time.

However, you can use the .create-merge table to modify an existing table. If the table does not already exist, it will create the table with the structure you specify. If the table exists, it will add any new columns listed.

Here is an example of the .create-merge table command that will add two new columns (“City” and “PostalCode”) to the customers table created above.

.create-merge table customers
(
FullName:string,
LastOrderDate:datetime,
YtdSales:decimal,
City:string,
PostalCode:string
)

The .create-merge table command is not destructive, so it will not remove any data (records or values) currently in the table. This command does not allow you to modify existing columns. You can rename a column with the .rename column command. The syntax is:

.rename column tablename.old_columname to new_columname

where:

  • tablename is the name of the table in which the column exists
  • old_columname is the current name of the column
  • new_columname is the name to which you wish to rename the column.

The following command will rename the PostalCode column in the customers table to 'ZipCode'.

.rename column customers.PostalCode to ZipCode

Remove a table

If a table is no longer useful or if you just plain don't like it any more, you can remove it with the .drop table command.  The syntax is

.drop table tablename

where tablename is the name of the table to drop.

Drop the customers table with the following command:

.drop table customers

Be careful. The table is dropped immediately without warning or a confirmation prompt.

Conclusion

I have shown you many of the most useful commands for managing an ADX table. You can learn more at the following links:

Table Management Commands
Column Management Commands