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.
Creating a Table
The .create table command allows you to create a new table. The syntax is:
.create table tablename
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
You can view the structure of this table with the following command:
The output of the command is shown in Fig. 2.
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 <|
- 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:
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.
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
- 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'.
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:
Be careful. The table is dropped immediately without warning or a confirmation prompt.
I have shown you many of the most useful commands for managing an ADX table. You can learn more at the following links: