You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit.
You create an external table named ExtTable that has LOCATION='/topfolder/'.
When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?
When you query an external table in an Azure Synapse Analytics serverless SQL pool, it does not traverse subfolders unless you specify a wildcard pattern such as /** at the end of the LOCATION path. In this scenario, without the /** wildcard, only files directly within the specified LOCATION='/topfolder/' will be returned. Thus, only File1.csv and File4.csv, which are directly under /topfolder/, will be included in the query result.
I believe the answer should be B. In case of a serverless pool a wildcard should be added to the location. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table
I tested and proove you right, the answer is B. Remind the question is referring to serverless SQL and not dedicated SQL pool. "Unlike Hadoop external tables, native external tables don't return subfolders unless you specify /** at the end of path. In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt. It won't return mydata2.txt and mydata3.txt because they're located in a subfolder. Hadoop tables will return all files within any subfolder."
Serverless SQL pool can recursively traverse folders if you specify /** at the end of path. The following query will read all files from all folders and subfolders located in the csv/taxi folder. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files
"Serverless SQL pool can recursively traverse folders only if you specify /** at the end of path." https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-folders-multiple-csv-files
When you are quoting from Microsoft documentation, do not ADD in words to the sentence. 'Only' is not used.
The answer is B however. I could not make "/**" to work. somebody?
I have created this case in my Datalake & Synapse Severless SQL pool and run sql as below: select top 10 * from openrowset( bulk 'topfolder/', data_source = 'Test', format = 'csv', parser_version = '2.0', firstrow = 2 ) with ( EmployeeId int ) as rows The answer for this one is B (only 2 files returned)
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&preserve-view=true&tabs=dedicated#location--folder_or_filepath-1
Agree it should be B. The question is a little off, because they don't specify whether using or not using a wildcard to do so. Assuming by default then, no wildcard is used, only those top-level files will be returned.
I believe that the right answer is B, because from the root directory, there will be listed two files and two folders
CREATE EXTERNAL TABLE ExtTable ( EmployeeId INT, EmployeeName VARCHAR(100), EmployeeStartDate DATE ) WITH ( LOCATION = '/topfolder/**', DATA_SOURCE = your_data_source, FILE_FORMAT = your_file_format );
choice is C, I dont understand why such a simple question trigger such many discussions
I think the answer should be B, because there are only two possibilites that would allow to return all the subfolders : - A hadoop external table - The query contains LOCATION='/webdata/** if this is a native external tables, they don't return subfolders
It is B
Not recursive wo **
I believe C is correct
Answer should be B.
Answer B is correct for me too.
answer c is correct
Strongly B: (solid reason with reference ) 1. This query uses Serverless Pool and it is only available for native External Table https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table 2. "native external tables don't return subfolders unless you specify /** at the end of path" https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&tabs=serverless#location--folder_or_filepath-1
The answer is B based on the below doc https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated