Fail to access files in ADLS Gen 2 with ADX External table "Virtual columns"

Question

I have a simple folder tree in Azure Data Lake Gen 2 that is partitioned by date with the following standard folder structure: {yyyy}/{MM}/{dd}. e.g. /Container/folder1/sub_folder/2020/11/01

In each leaf folder, I have some CSV files with few columns but without a timestamp (as the date is already embedded in the folder name).

I am trying to create an ADX external table that will include a virtual column of the date, and then query the data in ADX by date (this is a well-known pattern in Hive and Big data in general).

.create-or-alter external table TableName (col1:double, col2:double, col3:double, col4:double) 
kind=adl
partition by (Date:datetime)
pathformat = ("/date=" datetime_pattern("year={yyyy}/month={MM}/day={dd}", Date))    
dataformat=csv 
( 
   h@'abfss://container@datalake_name.dfs.core.windows.net/folder1/subfolder/;{key}'
) 
with (includeHeaders = 'All')

Unfortunately querying the table fails, and show artifacts return an empty list.

external_table("Table Name")
| take 10

.show external table Walmart_2141_OEE artifacts

with the following exception:

Query execution has resulted in error (0x80070057): Partial query failure: The parameter is incorrect. (message: 'path2
Parameter name: Argument 'path2' failed to satisfy condition 'Can't append a full path': at Concat in C:\source\Src\Common\Kusto.Cloud.Platform\Utils\UriPath.cs: line 25: 

I tried to follow many types of pathformats and datetime_pattern as described in the documentation but nothing worked.

Any ideas?

Answer 1

According to your description the following definition should work:

.create-or-alter external table TableName (col1:double, col2:double, col3:double, col4:double) 
kind=adl
partition by (Date:datetime)
pathformat = (datetime_pattern("yyyy/MM/dd", Date))    
dataformat=csv 
( 
   h@'abfss://container@datalake_name.dfs.core.windows.net/folder1/subfolder;{key}'
) 
with (includeHeaders = 'All')