'Azure Synapse - Query JSON using OPENROWSET - FIELDTERMINATOR Value 0x0b

In Azure Synapse Serverless SQL Pool, while reading JSON documents using OPENROWSET via the CSV parser, we are having to specify the FIELDTERMINATOR and FIELDQUOTE as '0x0b' (vertical tab). Can someone help understand the reason why please?

If it's a single line JSON (JSONL), each line in the file is a JSON document and I would expect the entire line to be read as a single value and not requiring to specify the FIELDTERMINATOR or FIELDQUOTE.

Are we just overriding the default values of the field terminator being comma (,) and the field quite being double quote (")? If so, what is the significance of '0x0b'?

Documentation is here - https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

Example code from the documentation as below

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
azureazure-synapse


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions