As per this Microsoft blog an external table can be defined as delta.
However if I try to create such a table , I get an error.
So this is the control command to create the external table:-
.create-or-alter external table external_mytable
(
region:string,
fieldX:string,
fieldY:string,
fieldZ:string,
partitiontime:datetime
)
kind=delta
partition by (region:string=region, partitiontime:datetime = bin(partitiontime, 1h) )
pathformat = (region "/" datetime_pattern("yyyy/MM/dd/HH",partitiontime) )
(
'abfss://mycontainer@mystorage.dfs.core.windows.net/;impersonate'
)
This throws syntax error. I have purposely not specified dataformat=parquet here since that should be implied, since I am already specifying kind=delta.
This command only succeeds if I remove the two clauses from it i.e. partition by and pathformat. But then it becomes useless to me, because I want the external table to be partitioned and also I need control over exact path, so I need these two clauses. Is there a way to make this command work while keeping kind=delta and still not remove the two clauses?
Also, let me call out another thing, my goal is to be able to export using .export command (on demand export) and not continuous export. I know that the blob refers to continuous export specifically. But the external table comes into an existence even before export takes place, so I thought probably it should not matter. But pls let me know otherwise if delta external table is not allowed for a regular on demand export.
If you want to query an existing partitioned delta table, then you should create the external table without specifying the partitioning schema, and the engine will automatically get the partitioning definition from the delta table's metadata.
If you want to export data into a partitioned delta table, then this is not currently supported.