Use PolyBase to load data to a SQL pool

PolyBase is a powerful feature in Azure SQL Data Warehouse that enables you to load data into a SQL pool from various external data sources, such as Azure Blob storage or Azure Data Lake Storage. This functionality is especially useful for data engineers who need to ingest and process large volumes of data efficiently. In this article, we will explore how to use PolyBase to load data to a SQL pool.

1. Prepare your data

Before loading data, ensure that it is stored in a compatible format. PolyBase supports data in formats like CSV, Parquet, ORC, and Avro. Make sure that your data is organized into files or folders according to your desired file format.

2. Set up external data sources

To load data from external sources, you need to create external data sources that point to the location of your data files. External data sources define the connection information required to access the external data. You can create an external data source using T-SQL statements.

Here’s an example of creating an external data source for Azure Blob storage:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://@.blob.core.windows.net',
CREDENTIAL = MyAzureBlobStorageCredential
);


3. Create external file formats

Once the external data source is set up, you need to define the format of the external files using external file formats. External file formats specify the properties of the files, such as field separators, row terminators, compression codecs, and more.

Here’s an example of creating an external file format for CSV files:

CREATE EXTERNAL FILE FORMAT MyCsvFileFormat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
STRING_DELIMITER = '"',
FIRST_ROW = 2
)
);

4. Create external tables

After setting up the data source and file format, you can create external tables that represent the structure of the external data. External tables provide a logical view of the data stored in the external files and bridge the gap between the external data and the SQL pool.

Here’s an example of creating an external table:

CREATE EXTERNAL TABLE MyExternalTable
(
Column1 INT,
Column2 STRING
)
WITH
(
DATA_SOURCE = MyAzureBlobStorage,
LOCATION = '/folder/data.csv',
FILE_FORMAT = MyCsvFileFormat
);

5. Load data to SQL pool

Once the external table is created, you can load the data into the SQL pool using the standard SQL INSERT INTO statement. You can use the external table like any other table in the SQL pool and perform various operations on it.

Here’s an example of loading data from an external table to a SQL pool table:

INSERT INTO MySqlPoolTable
SELECT *
FROM MyExternalTable;

By executing the INSERT INTO statement, the data from the external table will be loaded into the SQL pool table.

PolyBase simplifies the process of loading data into a SQL pool by providing a seamless integration with external data sources. It allows data engineers to efficiently load and process large volumes of data for analytics and reporting purposes.

In conclusion, PolyBase is a valuable feature for data engineers working with Azure SQL Data Warehouse. It enables easy loading of data from various external sources into a SQL pool. By following the steps outlined in this article, you can leverage PolyBase to efficiently load data and maximize the capabilities of your SQL pool.