Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-274

Import processes for use with Microsoft Azure Blob Storage™

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASOL 6.0.9, Exasol 6.1
    • Fix Version/s: None
    • Component/s: EXAPowerlytics
    • Labels:
      None
    • Solution:
      Hide

      Parallel data exchange between EXASOL and Microsoft Azure Blob Storage

      You have an EXASOL database and want to read data from Microsoft Azure Blob Storage.

      Reading data from Microsoft Azure Blob Storage

      The best way to read data is via the http/https protocol using EXASOLs native loading interface EXAloader with the IMPORT statement.
      The theory how to access files stored in Microsoft Azure Blob Storage via http/https is described here:
      https://azure.microsoft.com/en-gb/documentation/articles/storage-dotnet-shared-access-signature-part-1/
      In practice, you will face the following problems:

      1. You want to import all files in a specific folder into a specific table. So you need to generate urls for all the files in that folder.
      2. You want to use authentication, but you don't want to create the required url signature manually.

      Summary

      In this solution, we make use of Lua scripting to generate the IMPORT sql commands.
      Additionally, we use the Python package azure-storage in user defined functions (UDFs) to generate the urls for IMPORT (see also https://azure.microsoft.com/en-gb/documentation/articles/storage-python-how-to-use-blob-storage/).

      Steps

      Follow the steps described in the comments of file azure_blob.sql.

      Show
      Parallel data exchange between EXASOL and Microsoft Azure Blob Storage You have an EXASOL database and want to read data from Microsoft Azure Blob Storage. Reading data from Microsoft Azure Blob Storage The best way to read data is via the http/https protocol using EXASOLs native loading interface EXAloader with the IMPORT statement. The theory how to access files stored in Microsoft Azure Blob Storage via http/https is described here: https://azure.microsoft.com/en-gb/documentation/articles/storage-dotnet-shared-access-signature-part-1/ In practice, you will face the following problems: You want to import all files in a specific folder into a specific table. So you need to generate urls for all the files in that folder. You want to use authentication, but you don't want to create the required url signature manually. Summary In this solution, we make use of Lua scripting to generate the IMPORT sql commands. Additionally, we use the Python package azure-storage in user defined functions (UDFs) to generate the urls for IMPORT (see also https://azure.microsoft.com/en-gb/documentation/articles/storage-python-how-to-use-blob-storage/ ). Steps Follow the steps described in the comments of file azure_blob.sql .
    • Category 1:
      ETL / Data Integration
    • Category 2:
      UDFs and In-Database Analytics

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: