nerdexam
Microsoft

DP-500 · Question #23

You have a file named File1.txt that has the following characteristics: A header row Tab delimited values UNIX-style line endings You need to read File1.txt by using an Azure Synapse Analytics…

The correct answer is A. SELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a', FIRSTROW = 2 ). To read File1.txt with a header, tab delimiters, and UNIX-style line endings using a Synapse serverless SQL pool, the OPENROWSET query must specify FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a', and FIRSTROW = 2.

Query and transform data

Question

You have a file named File1.txt that has the following characteristics:
  • A header row
  • Tab delimited values
  • UNIX-style line endings You need to read File1.txt by using an Azure Synapse Analytics serverless SQL pool. Which query should you execute?

Options

  • ASELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a', FIRSTROW = 2 )
  • BSELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', FIRSTROW = 2 )
  • CSELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0d0a', FIRSTROW = 2 )
  • DSELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a', FIRSTROW = 1 )

How the community answered

(48 responses)
  • A
    83% (40)
  • B
    10% (5)
  • C
    2% (1)
  • D
    4% (2)

Why each option

To read File1.txt with a header, tab delimiters, and UNIX-style line endings using a Synapse serverless SQL pool, the OPENROWSET query must specify `FIELDTERMINATOR = '\t'`, `ROWTERMINATOR = '0x0a'`, and `FIRSTROW = 2`.

ASELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a', FIRSTROW = 2 )Correct

This query correctly specifies `FIELDTERMINATOR = '\t'` for tab-delimited values, `ROWTERMINATOR = '0x0a'` for UNIX-style line endings (LF, represented as hexadecimal 0x0a), and `FIRSTROW = 2` to skip the header row.

BSELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', FIRSTROW = 2 )

`ROWTERMINATOR = '\n'` is not the correct hexadecimal representation for a UNIX-style line feed (LF) in SQL `OPENROWSET`; the correct hexadecimal is `0x0a`.

CSELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0d0a', FIRSTROW = 2 )

`ROWTERMINATOR = '0x0d0a'` represents Windows-style line endings (CRLF), not UNIX-style line endings (LF).

DSELECT* FROM OPENROWSET( BULK 'File1.txt', DATA_SOURCE = 'Sql1', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR = '\t', ROWTERMINATOR = '0x0a', FIRSTROW = 1 )

`FIRSTROW = 1` indicates that the first row is data, but the file has a header row that needs to be skipped, making `FIRSTROW = 2` the correct option.

Concept tested: Azure Synapse serverless SQL pool OPENROWSET parameters

Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-csv-files#parser-arguments

Topics

#Azure Synapse Analytics#Serverless SQL pool#OPENROWSET#External data querying

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-500 Practice