Azure Datalake Gen2 as external table for Azure Data Explorer

Question

We have CSV files in Azure Data lake Gen 2 under partitioned folder, so there will be multiple CSV files for a single large table. We want to consume these files in Azure Data Explorer by creating an external table. so I am using below script to create an external table in ADX:

   .create external table TestAdx
(

    id: int,
    name: string,
    designation: string
)
kind=adl

dataformat=csv

(
    h@'abfss://[email protected]/staging/textadx;token=<<generating using .net API>>'
)

with 
(
   docstring = "Docs",
   folder = "ExternalTables",
   namePrefix="Prefix"
)

I am able to execute this query and the external table is created but when I try to fetch data from this table it is giving below error:

Semantic error: 'TestAdx' has the following semantic error: '' operator: Failed to resolve table or column or scalar expression named 'TestAdx'.

Also please let me know is this the correct approach to work with ADLS Gen2 file form ADX?

Answer 1

what is the query you're running? are you using the external_table() function?

Answer 2

You need to use external_table("TestAdx") to access the external table.

Answer 3

Following is the example for creating external table with Azure Data Explorer with Azure Data Lake Gen 2. I have added the partition key and other parameters.

.create external table BugsCSV
(
    Column1 : string,
    Column2 : string,
    Column3 : string
)
kind=adl
partition by "State="State
dataformat=csv
(
    h@'abfss://[email protected]/path;key'
)
with
(
    docstring = "Docs",
    folder = "ExternalTables",
    compressed=true,   
    compressiontype="lz4"
)