Ingesting data from external table into ADX

September 27, 2023

Let’s say I already have an external table of kind ADL created for some other purpose but now I want to also ingest data pointed to by the external table to one of the native Kusto tables. Is there a command to do that in ADX? Logically it is like reusing external table definition for some other purpose, instead of specifying the same end point information again. Also, if it helps, I would like add that this will be periodic refresh (to keep native table in sync with external table) , basically it’s not a one time thing.

This is how my external table definition looks like:-

.create external table my_external_table
(      
    col1:string,
    col2:string,
    col3:string
) 
kind=adl
partition by (myvirtualcol:string) 
dataformat=orc
(
  h@'abfss://<storage-end-point>;<MaskedAccessKey>'
)

I can always do the following:-

.set-or-replace async my_native_table <| external_table('my_external_table')

But I would like to know if there is any special way to handle this kind of situation other than this obvious method ?

>Solution :

if the amount of data in the external table is small enough (say, up to a few GBs) – you can use .set-or-append.

for example:

.set-or-append MyTable <|
    external_table('MyExternalTable')

otherwise, it’s best to:

queue existing files for ingestion from the container(s) (e.g. using tools like LightIngest)
setup a data connection for ingestion of any newly written file to the container(s)