External Table from partitioned CSV in Unity Catalog.

[vpaluch]

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.

[Kaniz]

Hi @vpaluch, External tables in the Unity Catalog should not be limited to only using non-partitioned CSVs. If you received an error message, it is not because of your data's structure but rather due to an authentication issue. The error message specifically mentions a failure to authenticate the request on the server. This could be caused by incorrect formatting of the Authorization header or the signature. It is crucial to double-check that the Authorization header and its signature are properly constructed.

Some important factors to consider are: 

1. Permissions: It's crucial to ensure that the account has the proper permissions to access the partitioned CSV files within the subfolders. 
2. Path Specification: When specifying the path for the partitioned CSV files, it's essential to double-check that the path is accurate. This is particularly crucial when working with CSV files partitioned by year and date. For instance, if your files are partitioned by year and date in the format 'year/month/day', the path should reflect this, such as 'abfss://temp@storage0account0name.dfs.core.windows.net/<schema>/DW/_partitioned/dw_agent_file/year/month/day/*'. 
3. Storage Credentials: Taking a moment to review the storage credentials used in the CREATE EXTERNAL LOCATION statement can save a lot of trouble. It's critical to confirm that they are valid and up-to-date before proceeding.

Here, attaching the references:-

• https://docs.databricks.com/en/ingestion/copy-into/unity-catalog.html
• https://www.dcac.com/dcac/external-tables-and-views-in-azure-databricks-unity-catalog/
• https://docs.databricks.com/en/ingestion/add-data/add-data-external-locations.html
• https://learn.microsoft.com/en-us/azure/databricks/ingestion/copy-into/unity-catalog
• https://learn.microsoft.com/en-us/azure/databricks/data-governance/unity-catalog/create-tables

[vpaluch]

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