Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 86 discussion

Actual exam question from Microsoft's DP-203

Question #: 86
Topic #: 1

You have an Azure subscription that contains the resources shown in the following table.

You need to read the files in storage1 by using ad-hoc queries and the OPENROWSET function. The solution must ensure that each rowset contains a single JSON record.

To what should you set the FORMAT option of the OPENROWSET function?

A. JSON
B. DELTA
C. PARQUET
D. CSV

Show Suggested Answer

Suggested Answer: D 🗳️

Community vote distribution

D (91%)

by IanKwok81 at June 8, 2023, 2:55 p.m.

Comments

Submit Cancel

Zak_Zakaria

Highly Voted 1 year, 8 months ago

Selected Answer: D

It should be D normally. I'll appreciate it if the one who curates the right answer and when the majority doesn't agree with his choice, brings some explanation so we can discuss and understand why he chooses it. It's not the first time I see a total disagreement without any explanation from the ones who select the right answers.

upvoted 13 times

humma

4 months, 3 weeks ago

To return product data from a folder containing multiple JSON files in this format, you could use the following SQL query: SQL Copy SELECT doc FROM OPENROWSET( BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json', FORMAT = 'csv', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b' ) WITH (doc NVARCHAR(MAX)) as rows OPENROWSET has no specific format for JSON files, so you must use csv format with FIELDTERMINATOR, FIELDQUOTE, and ROWTERMINATOR set to 0x0b, and a schema that includes a single NVARCHAR(MAX) column. The result of this query is a rowset containing a single column of JSON documents, like this:

upvoted 3 times

...

phydev

Highly Voted 1 year, 4 months ago

Selected Answer: D

Was on my exam today (31.10.2023).

upvoted 12 times

...

Moli62

Most Recent 3 days, 1 hour ago

Selected Answer: D

https://learn.microsoft.com/en-us/training/modules/query-data-lake-using-azure-synapse-serverless-sql-pools/3-query-files

upvoted 1 times

...

samirarian

1 month ago

Selected Answer: A

SELECT * FROM OPENROWSET( BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json', FORMAT = 'JSON' ) AS doc

upvoted 1 times

...

moize

3 months, 1 week ago

Selected Answer: A

Pour garantir que chaque ensemble de lignes contient un seul enregistrement JSON lorsque vous utilisez la fonction OPENROWSET pour lire des fichiers dans Azure Data Lake Storage Gen2, vous devez définir l'option FORMAT sur A. JSON. L'option D. CSV ne serait pas correcte dans ce contexte, car elle est utilisée pour lire des fichiers CSV, et non des fichiers JSON. Utiliser FORMAT = 'JSON' permet de s'assurer que chaque ligne de la sortie correspond à un enregistrement JSON unique. Voici un exemple de requête : SELECT * FROM OPENROWSET( BULK 'https://storage1.dfs.core.windows.net/container1/folder1/*.json', FORMAT = 'JSON' ) AS rows;

upvoted 2 times

hypersam

2 months, 1 week ago

This is wrong, just try it using serverless sql pool and it gives error: Invalid or unknown format type 'JSON'.

upvoted 2 times

...

EmnCours

3 months, 2 weeks ago

Selected Answer: D

Correct Answer: D

upvoted 1 times

...

ahana1074

6 months, 1 week ago

This allows the SQL pool to treat each JSON object as a row and parse the file accordingly. The correct answer is: A. JSON

upvoted 2 times

humma

4 months, 3 weeks ago

OPENROWSET has no specific format for JSON files, so you must use csv format with FIELDTERMINATOR, FIELDQUOTE, and ROWTERMINATOR set to 0x0b, and a schema that includes a single NVARCHAR(MAX) column. The result of this query is a rowset containing a single column of JSON documents, like this: https://learn.microsoft.com/en-us/training/modules/query-data-lake-using-azure-synapse-serverless-sql-pools/3-query-files

upvoted 2 times

...

AAhmadH

7 months, 1 week ago

Selected Answer: A

A OPENROWSET support JSON format in Synapse. In traditional SQL Server on-prem JSON format wasn't supported so we used CSV method. Synapse support JSON OPENROWSET, json object is treated as separate row, no need for CSV workaround, it would complicate the process requiring additional parsing record

upvoted 2 times

...

vik_sahani

7 months, 3 weeks ago

A. OPENROWSET(BULK) is a table-valued function that can read data from any file on the local drive or network, if SQL Server has read access to that location. It returns a table with a single column that contains the contents of the file. Eg.. SELECT BulkColumn FROM OPENROWSET(BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

upvoted 1 times

...

Prt1nov89

8 months, 4 weeks ago

You have three choices for input files that contain the target data for querying. Valid values are: 'CSV' - Includes any delimited text file with row/column separators. Any character can be used as a field separator, such as TSV: FIELDTERMINATOR = tab. 'PARQUET' - Binary file in Parquet format 'DELTA' - A set of Parquet files organized in Delta Lake (preview) format Values with blank spaces are not valid, e.g. 'CSV ' is not a valid value. So, its D

upvoted 1 times

...

ageorgieva

8 months, 4 weeks ago

Selected Answer: D

Gemini says it is a CSV: Based on the information provided in the image, the format option of the OPENROWSET function should be set to D. CSV. Here's why: The data files in Azure Blob storage are JSON files. However, SQL Server doesn't natively support the JSON format for the OPENROWSET function. As a workaround, you can specify the format option as CSV and configure the field terminator and fieldquote options appropriately to process each line of the JSON file as a single record. Even though the data is in JSON format, choosing CSV as the format option allows you to read the data into a SQL table using OPENROWSET.

upvoted 5 times

...

qadeeros

10 months ago

chatgpt says option A

upvoted 3 times

...

Elanche

11 months, 2 weeks ago

The easiest way to see to the content of your JSON file is to provide the file URL to the OPENROWSET function, specify csv FORMAT, and set values 0x0b for fieldterminator and fieldquote. If you need to read line-delimited JSON files, then this is enough.

upvoted 1 times

...

Elanche

11 months, 2 weeks ago

select top 10 * from openrowset( bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl', format = 'csv', fieldterminator ='0x0b', fieldquote = '0x0b' ) with (doc nvarchar(max)) as rows go select top 10 * from openrowset( bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json', format = 'csv', fieldterminator ='0x0b', fieldquote = '0x0b', rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON ) with (doc nvarchar(max)) as rows

upvoted 1 times

...