Lesson Learned #181: Reading a JSON compressed file from OPENROWSET function from Azure SQL Database

[Jose_Manuel_Jurado]

Today, I got a question from a customer with the following scenario: 

• They have a Azure Blob Storage with a huge amount of files that are compressed using Zip. 
• Every Zip file has an unique file. This unique file has JSON format.
• They want to import this JSON to Azure SQL Database using OPENROWSET to perform some internal operations.

Unfortunately, there is not possible to read compressed file using OPENROWSET, but, adding some customized code we have an alternative.

The first thing, I developed this small piece of code in C# that receives 6 parameters:

• ZipPath: Contains the FullPath of Zip file. For example, F:\AzureFileStorage\myfile.zip
• UnCompressFile: Contains the destination path. For example, F:\AzureFileStorage\Extract\
• File: Will be the name of the JSON file to extract. For example, myfile.json
• overwrite: Boolean parameter that indicates to delete the file if exists in the destination. 
• sTableName: Will be the destination table to import the data. 
• sAzureBlobFile: Will be the URL of Azure Blob Storage that the function OpenRowSet will read the data. 

The bUnZip function in the C# source code will perform of the following operations:

• Read the ZipFile and search if the JSON file to extract exists in this Zip file. 
• If exists, search if the JSON file exists in the folder destination and delete it if needed. 
• Extract the content and import the data using OpenRowset from AzureBlobStorage.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO.Compression;
using System.IO;
using System.Threading.Tasks;
using System.Configuration;

namespace DotNetExample
{
    class ClsZip
    {
        public bool bUnZip(string ZipPath, string UncompressFile, string File, bool overwrite, string sTableName, string sAzureBlobFile = "")
        {
            bool bExecuted = false;
            string sFinalFile = "";
            try
            {

                using (ZipArchive archive = ZipFile.OpenRead(ZipPath))
                {
                    foreach (ZipArchiveEntry entry in archive.Entries)
                    {
                        if (entry.FullName == File)
                        {
                            sFinalFile = System.IO.Path.GetFullPath(Path.Combine(UncompressFile, File));
                            if (overwrite)
                            {
                                DeleteFile(sFinalFile);
                            }
                            ZipFile.ExtractToDirectory(ZipPath, UncompressFile);
                            if (bImportData(sAzureBlobFile, sTableName))
                            {
                                bExecuted = true;
                            } 
                        }
                    }
                }
            }
            catch   {}
            return bExecuted;
        }

        private void DeleteFile(string File)
        {
            try
            {
             System.IO.File.Delete(File);
            }
            catch
            { }
        }

        public bool bImportData(string sFile, string sTable)
        {
            ClsRetryLogic oClsRetry = new ClsRetryLogic();
            bool bReturn = false;
            string sSQL = "";

                sSQL = "INSERT INTO " + sTable + " (Id, Value) " +
                          "SELECT Valuesfile.* FROM " +
                          "OPENROWSET(BULK N'" + sFile + "', SINGLE_CLOB) AS json " +
                          "CROSS APPLY OPENJSON(BulkColumn) " +
                          "WITH(Id varchar(200), Value nvarchar(100)) AS Valuesfile";

            try
            {
              if (oClsRetry.RunQueryWithRetriesNonQuery(GetConnectionStringTimeout(true),
                                                        sSQL, 5, 5)) { bReturn = true; }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!! " + e.Message);
            }
            return bReturn;
        }

        private static string GetConnectionStringTimeout(bool bPooling)
        {
            return ConfigurationManager.ConnectionStrings["AzureSQLDB"].ToString());
        }

    }
}

Based on this URL you could see how to read JSON file using OpenRowset.

Enjoy!