{"id":91469,"date":"2021-07-01T17:45:57","date_gmt":"2021-07-01T17:45:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91469"},"modified":"2021-07-21T20:05:26","modified_gmt":"2021-07-21T20:05:26","slug":"query-blob-storage-sql-using-azure-synapse","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/query-blob-storage-sql-using-azure-synapse\/","title":{"rendered":"How to query blob storage with SQL using Azure Synapse"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. How to query blob storage with SQL using Azure Synapse<\/a><\/li>\n
  2. How to query private blob storage with SQL and Azure Synapse<\/a><\/li>\n
  3. Performance of querying blob storage with SQL<\/a>\u00a0<\/li>\n<\/ol>\n\n

    When you deal with Big Data<\/strong> in Azure<\/strong>, you will store lots of files on Azure Storage<\/strong>. From time to time, you may need to investigate these files. Wouldn’t it be cool if you could just open a window and execute a SQL Query over the storage? In this article, I explain how to query blob storage with SQL using Azure Synapse.<\/p>\n

    Synapse Analytics<\/strong> makes this not only possible but also very affordable since you pay as you go. At the time of this writing, it estimates only $5.00 USD for each terabyte processed. Synapse Analytics makes it easy to query your storage and analyse files using the SQL language.<\/p>\n

    Disclaimer:<\/strong> This is only the price for data processing on the Synapse Serverless SQL pool<\/strong>. It doesn\u2019t include the storage price and other services used. This price may change anytime. Please, check the pricing on the Azure website<\/a> for more precise information.<\/p>\n

    Next, I\u2019ll show you how to implement Azure Synapse and dig into the details to show you how it works.<\/p>\n

    Provisioning the workspace<\/h2>\n

    I will not duplicate the good Microsoft documentation. You can follow this QuickStart to provision the workspace<\/a>. Use the option to create a new storage account together with the workspace.<\/p>\n

    When the workspace is provisioned, only the SQL On Demand<\/strong> pool is created. It\u2019s the most affordable one, and you can stick to that: use the SQL On Demand<\/strong> pool to query your blob storage.<\/p>\n

    The Synapse Workspace name needs to be unique because It can become a public URL. In fact, it is one by default. I will call my Synapse Workspace<\/strong> MaltaLake. <\/em>You need to create your own custom name for your Synapse Workspace<\/strong>. Every time I refer to MaltaLake<\/em>, replace it with your workspace name.<\/p>\n

    Provisioning a storage account<\/h2>\n

    The storage account you created in the previous step will be used by Synapse Analytics<\/strong> itself for things like metadata. In order to make a demonstration of a small data lake, you will need another storage account.<\/p>\n

    During the example, I will call the storage LakeDemo<\/em>. This is the name for my storage, which also needs to be unique. You will need to create a unique name for your storage and use it every time you run an example that uses LakeDemo<\/em> by adjusting the scripts, replacing LakeDemo<\/em> with your storage account name.<\/p>\n

    You can use a PowerShell script to provision the storage account and upload demo files to it. You can download the scripts and demos<\/a> and execute the file Install.bat<\/em>. It will ask you some questions and create the environment on your Azure account.<\/p>\n

    This is what the script will do for you:<\/p>\n