Preventing padded virtual columns in external table pathformat

userhMdl 0
Viewed 34

My data is partitioned in ADL the following manner:-

year=2022/month=9/day=7/hour=5/city=chicago/ [...a bunch of data files...]
year=2022/month=9/day=7/hour=5/city=london/ [...a bunch of data file...]

So it's partitioned by year, month, day, hour & city. Hour is a 24 hour format with values ranging from 0 to 23. Also please note that directory names are not 0 padded. As you can see in the example the month=9 appears as a single digit and not 09 , the same goes for day and hour too. The thing to note is that this data is produced by another process so we can't change the way partition directories appear.

My goal is to create an external table to read this table from Kusto. To optimize any query on this table I would like to take an advantage of virtual column feature. In case of our data the actual year, month, day, hour, city values are not part of data itself but rather only appear in partition directory names (as is common in many big data scenarios). So considering this I created the external table as follows:-

.create-or-alter external table myexternaltable
(
    ...data column fields...
    ...data column fields...
    ...data column fields...
    ... etc. ...
)
kind=adl
partition by (date_id:datetime, city:string)
pathformat = (
                "year=" datetime_pattern("yyyy",date_id) 
                "/month=" datetime_pattern("MM",date_id)  
                "/day=" datetime_pattern("dd",date_id)
                "/hour=" datetime_pattern("HH",date_id)
                "/city=" city
              )
dataformat=parquet
( 
   ...ADL endpoint...
)

As you can notice I defined two virtual columns i.e. date_id & city.

The external table creation was successful. But I think it's not correctly looking into the right partitions when I tried to query it:-

external_table('myexternaltable') | where date_id == datetime(2022-9-3-5) | where city == 'london' | take 1

This returned no rows , even though there is data in the corresponding locations. I am suspecting that the issue is that the pathformat I use uses padded digit format , i.e. it probably searches year=2022/month=09/day=03/hour=05 whereas the data exists in year=2022/month=9/day=3/hour=5. Is that the reason? If so what is the correct pathformat for this sort of requirement ?

azure-data-explorer
2 Answers

Updated: I believe the following should work. See example in the docs:

pathformat = (datetime_pattern("'year='yyyy'/month='M'/day='d",date_id))
yifats0

After some playing around I found the following hack to be working:-

.create-or-alter external table myexternaltable
(
    ...data column fields...
    ...data column fields...
    ...data column fields...
    ... etc. ...
)
kind=adl
partition by (year:string, 
              month:string,
              day:string,
              hour:string,
              city:string) 
pathformat = (
                "year=" year
                "/month=" month
                "/day=" day
                "/hour=" hour
                "/city=" city
              )
dataformat=parquet
( 
   ...ADL endpoint...
)

Now both the following methods to query the table are working quite fast , as you can see now it doesn't matter whether I use 0 in the query because behind the scenes Kusto is removing that while converting number to string since I defined year,month,day,hour as strings:-

external_table('myexternaltable') | where year==2022 | where month == 09 | where day==03 | where hour==05 | where city=='london' | take 1

external_table('myexternaltable') | where year==2022 | where month == 9 | where day==3 | where hour==5 | where city=='london' | take 1

It would still be good to find a single virtual column like date_id of type datetime , so one can perform datetime arithmetic and also it would look neat to have a single virtual column instead of four.

userhMdl0
Related