How to bulk load JSON with values into Synapse SQL dedicated pool

Asked Nov 03 '22 at 17:20

Active Nov 03 '22 at 20:54

Viewed 260 times

I'm attempting to bulk load json files, along with their filenames and paths into a synapse analytics dedicated sql table but I'm just stumped on how to accomplish it. I can load the json files solo without a problem, but I really need the additional values as well.

This is what I'm trying but it doesn't work:

Copy INTO dbo.PolicyStagingJsonOnly 
SELECT  jsonContent,
        [result].filename() as fn,
        [result].filepath() as fp
FROM
    OPENROWSET(
        BULK 'https://datalakexxxx.blob.core.windows.net/staging/policy/*.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        rowterminator = '0x0c'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]

edited Nov 03 '22 at 20:54

asked Nov 03 '22 at 17:20

brad

`OPENROWSET` is not supported in Synapse SQL dedicated pool. – Pratik Lad Nov 07 '22 at 05:48
Yes, that's true. The question is how do I load the table that includes the additional attributes? – brad Nov 07 '22 at 17:46
Do you only want to use dedicated SQL pool? – Pratik Lad Nov 07 '22 at 18:18
Yes, I'm trying to do this in a dedicated pool only. Thx. – brad Nov 08 '22 at 17:21

How to bulk load JSON with values into Synapse SQL dedicated pool

0 Answers0