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.