How to load to Azure SQL Data Warehouse data stored as JSON in Azure Blobs?

3
votes

I have a bunch of data stored as JSON file in Azure Blobs. I want to load them into Azure SQL Data Warehouse, but Azure SQL Data Warehouse does not seem to accept JSON input files.

What is the best approach?

Solutions I looked into:

1) Using Azure Data Factory with custom activities (e.g. https://github.com/Azure/azure-content/blob/master/articles/data-factory/data-factory-use-custom-activities.md) But it seems a bit 'overhead' to spin an HDInsight cluster for this (and also I prefer using a JavaScript and not a C# solution).

2) Writing an Azure api-app (or custom code) to transform a file to a CSV and then writing a external-table script to upload it into the Azure SQL Data Warehouse then delete the CSV file. I am not sure if I need to keep this CSV file for later use.

Any better suggestions?

2
jsoncsvazureazure-sqldwazure-data-factory
If you would like Polybase to support JSON, please vote it up on Azure's Customer Feedback page: feedback.azure.com/forums/34192--general-feedback/suggestions/… – Kyle Hale
Azure SQL DW now supports JSON functions so you should not have to leverage other services to load this data depending on exactly how the files in blob storage are formatted and whether Polybase can query them: azure.microsoft.com/en-us/updates/… – GregGalloway

2 Answers

2
votes

You can use Azure Data Factory to convert the json blob data to csv and then insert into Azure SQL Data Warehouse.

Your pipeline would have one Copy activity, one json AzureBlob Dataset and one AzureSqlDWTable.

It is important that you add a column mapping with translator rules to the Copy activity.

0
votes

I would prefer the second option. Azure Web jobs seems a good option. It works in the same environment as Web Apps. You can just zip a folder that has a console app, a PowerShell script or a bash script and its dependencies and schedule it as a batch or run it continuously. If you are in C#, you can even leverage the Azure SDK 1.1 to handle the blobs as streams: https://azure.microsoft.com/en-us/documentation/articles/websites-dotnet-webjobs-sdk-storage-blobs-how-to/