Question 104858 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Load JSON to SQL database tables

I have JSON files that at the end of the day should be inserted to SQL Database.
To explain what I mean, let’s assume the JSON looks like this:{ name : "John", surname : "Smith", movie: "Snatch" }

And database has tables: names(id, name), surnames(id, surname), fav_movie(name_id, surname_id, movie_title)

I would like to upload the JSON to fav_movie table but convert name and surname fields into ids from name and surname table so the insert into fav_movie looks like (1,1,’Snatch’)

I have full flexibility in the services I use. My idea would be to use Event Hub + Stream Analytics or Azure Queue Storage + Function Apps. But to be honest I don’t know if it’s the right approach.

Each of these configurations seems to be invalid:

Event Hub + Stream Analytics – I don’t know how I can do lookups on the name and surname tables while inserting them into the fav_movie table

Queue Storage + Function Apps – Looks like Function Apps has no output to Azure SQL Database (only Cosmos DB)

Tags:

2

Answers


  1. 0

    As you are mentioning different types of queueing mechanisms to front this I assume you expect to be getting these messages one at a time or in small batches.

    You can write a stored procedure that takes the json and updates the tables appropriately. Then you can listen to the queue and just call the stored procedure – this can be done with a Function App (which can connect to Azure SQL DB) or in this case a Logic App would be a lot easier since the steps are simple. By encapsulating all the logic in the stored procedure it is easy to change.

    What you use for the queueing mechanism doesn’t matter from the processing end so use what makes sense from the publishing side.

    If my assumption was incorrect and you are going to get these messages in large batches I would use a Data Factory to write the batch to a staging table and then write sql code (could be in stored procedure) that does the updates.

    Login or Signup to reply.
  2. 0

    You can just use T-SQL to import JSON data. With OPENROWSET you can access JSON data available in a Storage Account, so that you can then do something like:

    select 
    cast(bulkcolumn as nvarchar(max)) as jsondata 
from 
    openrowset(bulk 'import-demo/user1.json', data_source = 'dmstore2_misc', single_clob) as azureblob
go

    Full sample here: https://github.com/azure-samples/azure-sql-db-import-data/tree/main/

    Otherwise you can use an Azure Function (see here: https://github.com/yorek/AzureFunctionUploadToSQL) or Azure Data Factory

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search