ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-203 All Questions

View all questions & answers for the DP-203 exam
Go to Exam

Exam DP-203 topic 1 question 86 discussion

Actual exam question from Microsoft's DP-203
Question #: 86
Topic #: 1
[All DP-203 Questions]

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 Hide Answer
Suggested Answer: D 🗳️
by IanKwok81 at June 8, 2023, 2:55 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Zak_Zakaria
Highly Voted 1 year, 9 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
6 months 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, 5 months ago
Selected Answer: D
Was on my exam today (31.10.2023).
upvoted 12 times
...
JT16
Most Recent 1 month ago
Selected Answer: A
Since the files in storage1 are publicly accessible JSON files, setting the FORMAT option to JSON allows Azure Synapse Analytics to correctly parse and process each file as individual JSON records. This ensures that each rowset corresponds to a single JSON record, aligning with the goal of ad-hoc queries via OPENROWSET.
upvoted 1 times
...
Moli62
1 month, 1 week 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
2 months, 1 week 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
4 months, 3 weeks 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
3 months, 2 weeks 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
4 months, 3 weeks ago
Selected Answer: D
Correct Answer: D
upvoted 1 times
...
ahana1074
7 months, 3 weeks 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
6 months 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
8 months, 2 weeks 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
9 months 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
10 months 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
10 months, 1 week 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
11 months, 1 week ago
chatgpt says option A
upvoted 3 times
...
Elanche
1 year 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
1 year 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
...
s_unsworth
1 year, 1 month ago
Selected Answer: D
OPENROWSET doesn't have a JSON option
upvoted 1 times
...
falzar
1 year, 2 months ago
I checked this on another dump site and chatgpt. Both said the answer is A.
upvoted 2 times
...
Load full discussion...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago