Using T-SQL to Move Data to and from an Azure SQL Data Warehouse (Azure)

You can import and export data to and from an Azure SQL Data Warehouse using Transact-SQL (T-SQL) statements. Proceed as follows.

  1. Set up a connection to the Azure SQL Data Warehouse.

    Follow instructions under Adding a Data Store. Make sure you choose Azure Azure SQL Data Warehouse from the Database Type dropdown.

  2. Set up your transaction:

    1. In the QDS UI, navigate to the Analyze page and choose + Compose in the left pane and Db Query from the drop-down in the right pane.

    2. From the Data Store dropdown menu, select the Azure SQL Data Warehouse you configured in Step 1.

    3. In the right pane, code the T-SQL statements to set up the data transfer:

    • Set up credentials (this needs to be done only once, at first setup):

      CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
      CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
      WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';
      

      where IDENTITY can be any string, and Secret is your Azure storage account key.

    • Create external data sources (do this for each data source that needs to be made available in Azure SQL Data Warehouse):

      CREATE EXTERNAL DATA SOURCE AzureStorage with (
       TYPE = HADOOP,
       LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
       CREDENTIAL = AzureStorageCredential
       );
      
    • Create external file formats (do this for each data source); for example:

      CREATE EXTERNAL FILE FORMAT TextFileFormat
      WITH (
             FORMAT_TYPE = DELIMITEDTEXT,
             FORMAT_OPTIONS (
               FIELD_TERMINATOR ='|',
               USE_TYPE_DEFAULT = TRUE
             )
      );
      
    • Create external tables (do this for each data source); for example:

      CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (
              [SensorKey] int NOT NULL,
              [CustomerKey] int NOT NULL,
              [GeographyKey] int NULL,
              [Speed] float NOT NULL,
              [YearMeasured] int NOT NULL
      )
      WITH (LOCATION='/<path>/',
              DATA_SOURCE = AzureStorage,
              FILE_FORMAT = TextFileFormat
      );
      

      where LOCATION is the path (relative to the Blob container) to a file or directory that contains the data.

  3. Move data.

  • To move data from Blob storage to the Data Warehouse, use a SELECT... INTO statement, as in the Importing data example here.

  • To move data from the Data Warehouse to Blob storage, use an INSERT INTO statement (with a target table created with CREATE EXTERNAL TABLE as in the previous step); see the Exporting data example here.