How to query a 3rd party Azure Data Lake (2nd Gen) via Shared Access Siganture (SAS URL) in Azure Synapse?

JasonW-5564 161

I have been granted read only access to a Vendor's azure data lake and given a SAS connect string. How can I query this data via Azure Synapse and store the results in my own data lake?

fyi- The vendor's data is stored in JSON file format in this following folder structure YYYY/MM/DD/HH/(then hundreds of JSON files in each HH folder).

1 answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-14T11:22:46.48+00:00
Hi @JasonW-5564 ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

The credential to data lake can be defined with SAS secret and below documents are tutorials explaining the loading using examples. Wild cards can be used to select all the JSON files under specific folder.

sql-data-warehouse-load-from-azure-data-lake-store
copy-into-transact-sql

Please suggest for further queries and we will be glad to assist.

--

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Please sign in to rate this answer.
JasonW-5564 161 Reputation points

2020-12-15T16:00:07.9+00:00

Thank you for the reply, let me clarify what I am trying to do a bit more. I want to query and aggregate the raw JSON files stored in a 3rd party data lake and store those aggregates in my own data lake or relation db. I do not want to physically copy all of those raw JSON files because of the data volume, storage cost and introduce any un necessary latency. I am looking for how to do that/what is the best tool set to use for this.

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-17T13:11:29.137+00:00

Hi @JasonW-5564 ,

Please suggest if approach posted in the other thread is useful in implementing such scenario.

Looking forward for your response!

JasonW-5564 161 Reputation points

2020-12-18T04:54:46.007+00:00

Thanks @HarithaMaddi-MSFT but neither of those seem to support JSON format? Seem to only support CSV, parquet and ORC (which I am not familiar with). Is there a way to do this?

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-22T12:31:52.647+00:00

Hi @JasonW-5564 ,

Please refer this document that guides in reading JSON files into Synapse using CSV format which explains with examples.

Kindly let us know for further queries and we will be glad to assist.

JasonW-5564 161 Reputation points

2020-12-24T04:00:51.04+00:00

Thanks @HarithaMaddi-MSFT that is VERY close to what I need. In those examples they filter down to pointing to a specific .json file. I want to query across all .json files in a folder. For instance I want to use a path like this:

*bulk 'latest/yyyy/mm/dd/HH/*.json**',*

Is this possible? If so is Synapse the only product to support this or can I do this in Azure SQL as well?

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-24T08:54:14.193+00:00

Thanks @JasonW-5564 for confirmation that it matches the requirement. Yes, wild card expressions using * is possible to load all .json files in a folder. For loading JSON files into Azure SQL, please refer this document.

Please let us know for further queries and we will be glad to assist.

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-28T12:38:32.99+00:00

Hi @JasonW-5564 ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-29T11:20:21.937+00:00

Hi @JasonW-5564 ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.
Sign in to comment

Share via

How to query a 3rd party Azure Data Lake (2nd Gen) via Shared Access Siganture (SAS URL) in Azure Synapse?

1 answer