Open row source

Question

nsnapshot.txt

Hi, how to run json file on synapse workspace.. Tried with open row set but not working.. Pls share screenshot if sny

Answer 1

You'll need to use the built-in serverless SQL pool or a dedicated SQL pool to query JSON files directly from Azure Data Lake Storage or Azure Blob Storage.

Upload your JSON file to a storage account linked to your Synapse workspace.

You can query the JSON file directly using the OPENROWSET function in a Synapse SQL query.

SELECT *

FROM OPENROWSET(

    BULK 'https://<storageaccount>.blob.core.windows.net/<container>/<path_to_json_file>.json',

    FORMAT='SINGLE_CLOB'

) AS json_file

CROSS APPLY OPENJSON(BulkColumn)

WITH (

    id INT '$.id',

    customer_id INT '$.customer_id',

    DEBUG_ASYNC VARCHAR(100) '$.data.DEBUG_ASYNC',

    DEBUG_RATING VARCHAR(100) '$.data.DEBUG_RATING',

    currency VARCHAR(10) '$.data.al_avn_52_limit.currency',

    limit FLOAT '$.data.al_avn_52_limit.limit',

    al_brokerage_hull FLOAT '$.data.al_brokerage_hull',

    al_brokerage_liab FLOAT '$.data.al_brokerage_liab',

    off_rate FLOAT '$.data.al_cargo.off_rate',

    own_rate FLOAT '$.data.al_cargo.own_rate',

    value FLOAT '$.data.al_cargo.value',

    perc_calculated FLOAT '$.data.al_dep_min_prem_dep.perc.calculated',

    perc_selected FLOAT '$.data.al_dep_min_prem_dep.perc.selected',

    value_calculated FLOAT '$.data.al_dep_min_prem_dep.value.calculated',

    value_selected FLOAT '$.data.al_dep_min_prem_dep.value.selected',

    min_prem_est FLOAT '$.data.al_dep_min_prem_est.value',

    min_prem_min_calculated FLOAT '$.data.al_dep_min_prem_min.value.calculated',

    min_prem_min_selected FLOAT '$.data.al_dep_min_prem_min.value.selected'

// complete the other fields

) AS json_data;

• OPENROWSET: Reads the file as a single string (SINGLE_CLOB).
• OPENJSON: Parses the JSON string and allows you to extract individual elements.
• CROSS APPLY: Applies the OPENJSON function to the rows returned by OPENROWSET.