190 C H A P T E R 9 | Introducing Azure SQL Data Warehouse
-- Create an external data source
CREATE EXTERNAL DATA SOURCE AzureDWDataStorage
WITH (
TYPE = HADOOP,
LOCATION =
'wasbs://datacontainer@.blob.core.windows.net',
CREDENTIAL = AzureDWDataStorageCredential
);
-- Create an external file format
CREATE EXTERNAL FILE FORMAT TextFile
WITH (
FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = ',')
);
-- Create an external table
CREATE EXTERNAL TABLE dbo.DimSalesTerritoryExternal (
SalesTerritoryAlternateKey INT NOT NULL,
SalesTerritoryRegion NVARCHAR(50) NOT NULL,
SalesTerritoryGroup NVARCHAR(50) NOT NULL,
SalesTerritoryCountry NVARCHAR(50) NOT NULL
)
WITH (
LOCATION='/dimsalesterritory/',
DATA_SOURCE=AzureDWDataStorage,
FILE_FORMAT=TextFile
);
After creating the external resources, you can view them in SSDT in the SQL Server Object Explorer, as
shown in Figure 9-7, and you can execute a query against the external table to read the data directly
from the text file in blob storage.
Figure 9-7: Viewing external resources in SSDT.
Although you can use PolyBase to query external tables, query performance is much better if you load
the external table data into a SQL Data Warehouse internal table structure by using one of the
methods shown in Example 9-8. If you are loading data into a new table, use the CREATE TABLE AS
SELECT statement to create a table in SQL Data Warehouse from your external table. Otherwise, use
