How to copy a delta table into a Data Explorer Table?

Question

I have a delta table created with Azure Synapse and I want to have the same table in Azure Data Explorer.

Linked service is created, I know I can use Azure Data Explorer Command activity or Copy.

But this a table that being updated everyday, so the ADX Table also has to be updated. I would like to do UPSERT.

How can I do it ?

I though using .set-or-replace command but don't know how to mention the delta table, located in azure data lake storage with parquet files.

Following works:

.ingest into table Table ('abfss://[email protected]/db/goldtestdb/CITYID=X/part-00001-4e94e458-9f2b-4c67-903d-25105eb7179a.c000.snappy.parquet;impersonate')
  with (format='parquet')

But it comes from a delta table, so they will be other parquet files.

I didn't find examples for my case in the documentation: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/data-ingestion/ingest-from-query#supported-ingestion-properties

Answer 1

I though using .set-or-replace command but don't know how to mention the delta table, located in azure data lake storage with parquet files.

There are alternative methods to accomplish it from outside ADX, like Functions, ADF and synpse pipeline.

To do that you need to use other ways like creating pipeline in synapse and first create the table using .clear command on the table first with ADX command activity as

.clear table tablename data

and then use Copy activity or Dataflow to transfer the data to ADX Table to update existing table.