You have an Azure Synapse Analytics serverless SQL pool and an Azure Data Lake Storage Gen2 account. You need to query all the files in the 'csv/taxi/' folder and all its subfolders. All the files…

This question tests your knowledge of how to use OPENROWSET in Azure Synapse Analytics serverless SQL pool to query CSV files recursively across a folder hierarchy in ADLS Gen2.

Query and transform data

Question

You have an Azure Synapse Analytics serverless SQL pool and an Azure Data Lake Storage Gen2 account.
You need to query all the files in the 'csv/taxi/' folder and all its subfolders. All the files are in CSV format and have a header row.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Explanation

This question tests your knowledge of how to use OPENROWSET in Azure Synapse Analytics serverless SQL pool to query CSV files recursively across a folder hierarchy in ADLS Gen2.

Approach. The correct query uses OPENROWSET with BULK pointing to a wildcard path like 'https://<storage>.dfs.core.windows.net/container/csv/taxi/' using '' to recursively match all subfolders and files. You must specify FORMAT = 'CSV', HEADER_ROW = TRUE (to skip the header), and use PARSER_VERSION = '2.0' for better performance. The WITH clause or column aliases define the schema, and the DATA_SOURCE can optionally be used to reference a pre-configured external data source pointing to the ADLS Gen2 account.

Concept tested. Using OPENROWSET with recursive wildcard paths (**) in Azure Synapse serverless SQL pool to query CSV files with headers across nested ADLS Gen2 folder structures

Reference. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage

Topics

#Azure Synapse Analytics#Serverless SQL pool#OPENROWSET#Data Lake querying

Community Discussion

No community discussion yet for this question.

Full DP-500 Practice