KQL ingest query not working with 'Where' statement

Question

I'm trying to ingest into a testing table in Kusto.

The table is created using this syntax:

.create table testing(id: string, label: string)

I'm trying to insert inline into this table using the below query:

.ingest inline into table testing <|
"5671343d-8eaa-4939-8225-cb17805868d8","2"
| where id != "5671343d-8eaa-4939-8225-cb17805868d8"

When I try to ingest it normally, it is working. But, when i use the Where statement, to make sure I do not ingest duplicates, the query is failing to execute.

Answer 1

The issue with your query is that the where clause is not valid in an inline ingestion command. The where clause is used to filter data in a Kusto query, but it is not supported in an inline ingestion command. To avoid ingesting duplicates, you can use the extent tags

• In this approach, you can ingest the data along with the tags.

example:

.ingest ... with (tags = '["ingest-by:2016-02-17"]')

• To prevent duplicate ingestion, you can use ingestIfNotExists in the .ingest command

example:

.ingest ... with (ingestIfNotExists = '["2016-02-17"]')

Refer MS doc on handling duplicate rows in azure data explorer.