How to create a column with default value / How to fill a column with the same value on Kusto

3
votes

I know how to do this with the DEFAULT constraint when creating a table or with the UPDATE and SET when modifying a table with other SQL instances. Is there a simple way to do this with the .create table or the .alter-merge table Kusto command?

Here is my use case where this question comes from, if helpful:

I have a python script that drops (if exists), creates a table on Kusto, then ingests data from a local csv file into this table and it works well.

The csv file to be ingested has 3 columns. I would like to create a 4th column and insert with all the same value when it's being created/ingested into Kusto. Is this possible?

For example:

MyData.csv to be ingested (Header will be removed before ingestion) looks like this:

Date    ID    Name 
1/1    1001   ABC_1
1/1    1002   3X4Y5Z
1/2    1003   DEF_a

Table to be created on Kusto will look like this:

Date    ID    Name    The_Fixed_Value
1/1    1001   ABC_1         a
1/1    1002   3X4Y5Z        a
1/2    1003   DEF_a         a

I would like to fill The_Fixed_Value column with the same value 'a' when the python code runs.

Python reference: https://github.com/Azure/azure-kusto-python/blob/master/azure-kusto-ingest/tests/sample.py

Thanks so much in advance!

3
pythonazure-data-explorerkql

3 Answers

3
votes

You can use csv mapping with your ingestion and specify a const value for the additional column, see here the relevant docs

Here is an example of ingestion command that uses this:

  .ingest into test (@"FILE_NAME.csv") with(
           Format = csv,
           csvMapping = '[{"Name":"a", "Ordinal":"0"},{"Name":"b", "ConstValue":"some value"}]'
   )
1
votes

I dont know anything about Kusto but it seems you are creating an intermediate dataframe. Then cant you just do this?

df['The_Fixed_Value'] = 'a'
1
votes

You could use the externaldata command to make your CSV a table. Then you could do something like:

.set-or-replace YourTable <|
externaldata( (Date:datetime, ID:int, Name:string), <csv_url>)
| extend The_Fixed_Value = 'a'