Azure Data Explorer External Table with Partition

DataEngineer 40

I am trying to create an Azure Data Explorer external table in azure using a parquet file with partitions and somehow ,I am not able to get the URI properly using UI or KQL always getting error or return query count from external table as 0. without partition it works fine and my data is stored like this as below

https://abc.blob.core.windows.net/logs/tenantId=xyz/y=2025/m=01/d=01/h=00/m=00/abc.parquet

Here m=00 is always harcoded and other parts are dynamic and creatin a external table with partitions will help us to drill down to the exact hour for better query perfomance. Also I am open to suggestion if we say partition on data columns for me all time data column is showing as string

phemanth 14,220 Reputation points Microsoft Vendor

2025-02-26T17:58:21.9266667+00:00
@DataEngineer

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Creating an external table with partitions in Azure using a Parquet file can indeed improve query performance by allowing you to drill down to specific hours. Here are the steps

Create an External Data Source: Define the location of your data in Azure Blob Storage.

CREATE EXTERNAL DATA SOURCE MyExternalDataSource WITH ( LOCATION = 'https://abc.blob.core.windows.net/logs', CREDENTIAL = MyCredential );

Create an External File Format: Specify the format of your Parquet files.

CREATE EXTERNAL FILE FORMAT MyParquetFormat WITH ( FORMAT_TYPE = PARQUET );

Create the External Table: Use the OPENROWSET function to include partition keys found in the file path.

CREATE EXTERNAL TABLE MyExternalTable WITH ( LOCATION = 'tenantId=xyz/y=*/m=*/d=*/h=*/m=00/*.parquet', DATA_SOURCE = MyExternalDataSource, FILE_FORMAT = MyParquetFormat ) AS SELECT r.filepath(1) AS tenantId, r.filepath(2) AS year, r.filepath(3) AS month, r.filepath(4) AS day, r.filepath(5) AS hour, * FROM OPENROWSET( BULK 'tenantId=xyz/y=*/m=*/d=*/h=*/m=00/*.parquet', DATA_SOURCE = 'MyExternalDataSource', FORMAT = 'PARQUET' ) AS r;

This approach allows you to include partition keys (like year, month, day, and hour) as columns in your external table, which can help with more efficient querying

If you encounter issues with data columns being treated as strings, you might need to cast them to the appropriate data types in your SELECT statement.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
phemanth 14,220 Reputation points Microsoft Vendor

2025-02-27T16:43:10.69+00:00

@DataEngineer We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
DataEngineer 40 Reputation points

2025-02-28T11:14:35.0033333+00:00

phemanth - ICan you please check the Question. I am trying to create azure data explorer external table and need some support on that

Share via

Azure Data Explorer External Table with Partition

Your answer