Ingestion of a JSON file in kusto

Question

I have been trying to ingest a multi-JSON file in kusto (Azure Data Explorer/ADX) which is stored in a ADLS location, The following is the bunch of stuff that I tried:

1. I created a blank table with the fields in accordance with the multi-JSON

2. I have created a mapping which is in accordance with the multi-JSON

3. I have tried ingesting the data using .ingest()

The issue is even though the ingestion is successful (I was checking "HasErrors== False" in the terminal after running the query), I could see only one blank record getting inserted into the table

1. I have tried giving the read & write & owner permissions while generating the SAS token for the multi-JSON file
2. I have tried using external table which points to the ADLS location to ingest the data
3. I have tried using the let statement to ingest the data
4. I have tried parsing the JSON using parse_json()

Answer 1

I could see only one blank record getting inserted into the table

This might be due to the incorrect ingestion mapping and make sure each of your JSON object is a valid one.

Follow the below steps to achieve your requirement.

This is my multi-JSON file in the ADLS.

{"name":"Alice", "age":30}{"name":"Bob","age":25}{"name":"Rakesh","age":23}{"name":"Luffy","age":19}

After the creating the empty table, create the ingestion like below sample.

.create table <table_name> ingestion json mapping "<test_mapping_name>"
'[{"column":"age","Properties":{"path":"$.age"}},{"column":"name","Properties":{"path":"$.name"}}]'

Then, use the .ingest command like below.

.ingest into table <table_name> ('https://<storage_account_name>.blob.core.windows.net/<file_path>;<account_key>') with (format='multijson',ingestionMappingReference="<test_mapping_name>");

Result:

If the issue still persists, you can try the below approach to fix it.

First Go to your ADX cluster overview and click on ingest in the Data ingestion part. After this, a new tab will get open and here, Go to Home and click on Get Data. Now, it will give options like below. Here, Select Azure Storage.

Then, in the configure step, give the subscription, storage account and file path details. Also, you can select your table or create a new table here like below.

Click Next and it will show the data preview.

Now, go ahead and the data will be ingested into the table. You can check the correct ingestion mapping command in this step.