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.
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.
Set up your transaction:
- 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.
- From the Data Store dropdown menu, select the Azure SQL Data Warehouse you configured in Step 1.
- 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.
- To move data from Blob storage to the Data Warehouse, use a
SELECT... INTOstatement, as in the Importing data example here.
- To move data from the Data Warehouse to Blob storage, use an
INSERT INTOstatement (with a target table created with
CREATE EXTERNAL TABLEas in the previous step); see the Exporting data example here.