You have an Azure subscription that contains the resources shown in the following table.
You need to read the TSV files by using ad-hoc queries and the OPENROWSET function. The solution must assign a name and override the inferred data type of each column.
What should you include in the OPENROWSET function?
To read tab-separated values (TSV) files using ad-hoc queries and the OPENROWSET function in Azure Synapse Analytics, and to assign a name and override the inferred data type of each column, you should include the WITH clause in the OPENROWSET function. The WITH clause allows you to explicitly define the column names and data types, which is necessary when dealing with files that do not have a header row to define these automatically.
In the Question "The solution must assign a name and override the inferred data type of each column", so we must need a WITH Clause to define the column names and data types.
I think it's A. WITH CLAUSE https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset
Agreed - Should be A. "To specify explicit column names and data types, you can override the default column names and inferred data types by providing a schema definition in a WITH clause" (https://learn.microsoft.com/en-us/training/modules/query-data-lake-using-azure-synapse-serverless-sql-pools/3-query-files)
They ask for "in the function", not "in the query"
It is not D because the files are PUBICLY ACCESSIBLE in the storage account, therefore according to documentation: "Any user can use OPENROWSET without DATA_SOURCE to read publicly available files on Azure storage." (https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset#security) Also DATA_SOURCE does not provide the necessary requirements, therefore WITH CLAUSE Example: OPENROWSET ( { BULK 'unstructured_data_path' , [DATA_SOURCE = <data source name>, ] FORMAT= ['PARQUET' | 'DELTA'] } ) [WITH ( {'column_name' 'column_type' }) ] [AS] table_alias(column_alias,...n)
There isn't header, so you have to specify columns name manually in WITH clause
To read TSV (Tab-Separated Values) files using ad-hoc queries and the OPENROWSET function in Azure Synapse Analytics, and to assign a name and override the inferred data type of each column, you should include the following in the OPENROWSET function: A. the WITH clause The WITH clause allows you to specify options for reading the data, including defining the column names and data types. You can use the WITH clause to provide column definitions and specify the data type for each column in the TSV file, which allows you to override the inferred data types.
DATA_SOURCE is not even an existing parameter option in th OPENROWSET function, the right existing option is DATASOURCE, without underscore, but it is not needed here, so A is the right option, see doc: https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16
A is correct
Question insists on the point that the file has not the header row! specifying a datasource is not strictly required, since OPENROWSET can connect to storage even with a simple link and managed identity. The WITH clause is the most reasonable recommendation to include in the solution.
With clause can be omitted for csv files see automatic schema discovery at https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset#automatic-schema-discovery Generic colnames will be assigned
on secodn thought, even if the types CAN be inferred, the question clearly states that the inferred types MUST be overwritten. Therefore WITH
There is no header row, therefore you should define one using the WITH Clause
The WITH clause allows you to specify the schema of the data source and override the inferred data type of each column. You can also use the WITH clause to specify the name of the external table and the location of the data source
WITH CLAUSE
To read TSV files without a header row using the `OPENROWSET` function and to assign a name and specify the data type for each column, you should use: A. the WITH clause The WITH clause is used in the `OPENROWSET` function to define the format file or to directly define the structure of the file by specifying the column names and data types.
A - WITH Clause is the correct answer.
D is right.
Correct is A (explained in another comment)