External Table from partitioned CSV in Unity Catalog.

vpaluch — Sat, 27 Jan 2024 00:02:36 GMT

When I create an External Table in unity catalog from a flattened csv folder, it works as expected:

CREATE EXTERNAL LOCATION IF NOT EXISTS raw_data URL 'abfss://raw@storage0account0name.dfs.core.windows.net' WITH ( STORAGE CREDENTIAL `a579a115-8958-.....`) COMMENT 'RAW Data'; DROP TABLE IF EXISTS raw_files.<schema>.dw_agent; CREATE TABLE raw_files.<schema>.dw_agent ( Retailer_ID STRING, Retailer_Status INT, Retailer_Status_Mapped STRING, Retailer_Longitude DOUBLE ) USING csv OPTIONS ( 'header' = 'true', 'sep' = '~', 'compression' = 'gzip' ) LOCATION 'abfss://temp@storage0account0name.dfs.core.windows.net/<schema>/DW/_flatten/dw_agent_file'; SELECT * FROM raw_files.<schema>.dw_agent_file LIMIT 1000

But if the CSV file is partitioned by Year/Date, I replace the LOCATION by:

'abfss://temp@storage0account0name.dfs.core.windows.net/<schema>/DW/_partitioned/dw_agent_file/*/*';

I'm getting the following error message:

Operation failed: "Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature." , 403, GET, https://storage0account0name.dfs.core.windows.net/prod?upn=false&resource=filesystem&maxResults=5000&directory=folder_name/DW/dw_agent_file&timeout=90&recursive=false&st=2024-01-26T22:46:00Z&sv=2020-02-10&ske=2024-01-27T00:46:00Z&sig=XXXXX&sktid=910757d4-7f42-4cda-b0a2-b78bdde4c812&se=2024-01-27T00:43:27Z&sdd=5&skoid=a2301c4c-cef3-4ab8XXXXXXXXXXXXXXXXXX&spr=https&sks=b&skt=2024-01-26T22:46:00Z&sp=rl&skv=2020-02-10&sr=d, AuthenticationFailed, "Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature. RequestId:19100423-001f-0009-12b1-501e2e000000 Time:2024-01-26T23:43:27.8051986Z"

The storage account is the same, the only difference is the CSV file is now distributed in subfolders.
I'm using the very same EXTERNAL LOCATION and CREDENTIALS.

There is any restriction to use partitioned csv's as external tables? I couldn't find find a single example in this scenario.

Thanks for helping.

Re: External Table from partitioned CSV in Unity Catalog.

vpaluch — Mon, 29 Jan 2024 09:50:22 GMT

Thanks Kaniz,

I'm using an External Location authenticated using a Managed Identity. The very same used for the non-partitioned table and many others that works pretty fine. This account has Storage Blob Contributor rights for all containers and folders in the storage account.

The path is also correct, 'abfss://temp@storage0account0name.dfs.core.windows.net/<schema>/DW/_partitioned/dw_agent_file/*/*', note that I'm using /*/* for year/month reference.

It seems that when I replace the identified path by /*/* its not being able to use the registered EXTERNAL LOCATION and it's trying to access via cluster access credentials (which are not being provided) in the Admin/Compute/SQL Warehouses/Data Security. As my understanding these aren't required for Unity Catalog assets.

Regards

topic Re: External Table from partitioned CSV in Unity Catalog. in Data Engineering

External Table from partitioned CSV in Unity Catalog.

Re: External Table from partitioned CSV in Unity Catalog.