Use PolyBase to load data to a SQL pool

PolyBase is a technology that enables integrated querying across disparate data sources such as SQL server, Microsoft Azure Data Lake Storage, and Oracle. PolyBase allows users to use Transact-SQL statements to access data stored in non-relational sources. In the context of loading data to a SQL pool, it offers a convenient and effective solution.

Why PolyBase?

PolyBase simplifies the data loading process into the SQL pool by leveraging parallel data transfer to ensure optimal performance. Key points to note are:

• PolyBase enables parquet and delimited text files to be loaded into SQL Data Warehouse using simple T-SQL commands.
• It allows the use of simple T-SQL queries to import and export data between Azure SQL Data Warehouse and Azure Blob Storage or Data Lake stores.
• PolyBase allows you to run the query on a scale-out cluster which greatly increases performance and decreases CPU usage.

Implementation of PolyBase:

Let’s delve into the implementation of PolyBase to load data into an Azure SQL pool.

Step 1: Installation and Configuration

PolyBase is a feature in SQL Server 2019 and you must enable this feature when installing SQL Server. A PolyBase scale-out group can also be implemented which is beneficial when dealing with large data sets.

Step 2: Create Master Key

Creating a master key is necessary to protect the database scoped credential.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

Step 3: Defining External Data Source

The external data source must be defined. Create a database scoped credential with the proper SAS token.

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '';


Create an external data source with the appropriate location of the blob storage.

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


Step 4: Loading Data

Loading data is as simple as creating an external table and using the SELECT INTO statement.

CREATE EXTERNAL TABLE MyExternalTable
(
...
)
WITH (
DATA_SOURCE = AzureStorage,
...
);

SELECT *
INTO MyAzureSQLDataWarehouseTable
FROM MyExternalTable;

Conclusion:

To sum it all up, PolyBase offers a convenient option for loading data into Azure SQL pools. It simplifies the process, boosts performance, and increases versatility, thus making it an essential tool in the DP-203 Data Engineering on Microsoft Azure certification exam preparation.

While PolyBase has its advantages, it’s crucial noting that its efficiency largely depends on the configuration and the data structure being dealt with. Therefore, professionals striving for DP-203 Certification should not only understand its workings but also the best practices in its application.

Remember that practice is key when preparing for any exam. Include practical sessions to master the implementation and usage of PolyBase, and you’ll be one step closer to passing your DP-203 Data Engineering on Microsoft Azure certification exam.

Practice Test

True or False: PolyBase is a technology that allows SQL Server to run queries directly on data stored in Microsoft Azure.

• True
• False

Answer: True

Explanation: PolyBase is a technology that allows SQL Server to read data from external sources, including Microsoft Azure, without having to import it.

Which of the following databases supports PolyBase as a data loading technology?

• A. MariaDB
• B. PostgreSQL
• C. MongoDB
• D. Azure SQL Data Warehouse

Answer: D. Azure SQL Data Warehouse

Explanation: Azure SQL Data Warehouse supports PolyBase as a data loading technology. It allows the use of T-SQL (Transact-SQL) statements to access data stored in Hadoop or Azure Blob Storage and query it in an ad-hoc fashion.

True or False: PolyBase allows SQL Server to write data directly to external data sources.

• True
• False

Answer: False

Explanation: PolyBase allows SQL Server to read data from external sources, not write data to it.

In the Azure SQL Data Warehouse, what is the fastest method to load data?

• A. BCP
• B. Bulk Insert
• C. SSIS
• D. PolyBase

Answer: D. PolyBase

Explanation: In the Azure SQL Data Warehouse, PolyBase is the fastest way to move data. This is because PolyBase works by creating an external table that points to the data stored.

Multiple Select: Which of the following options can be used as data sources for PolyBase?

• A. Azure Blob Storage
• B. Hadoop/HDFS
• C. SQL Server
• D. MongoDB

Answer: A. Azure Blob Storage, B. Hadoop/HDFS, and C. SQL Server

Explanation: PolyBase supports Azure Blob storage, Hadoop/HDFS, and SQL Server as data sources.

True or False: PolyBase requires the data to be imported into SQL Server before it can query the data.

• True
• False

Answer: False

Explanation: PolyBase allows SQL Server to run queries on external data without it needing to be imported.

What is the role of PolyBase in SQL pool?

• A. Data Backup
• B. Data Loading
• C. Data Compression
• D. Data Encryption

Answer: B. Data Loading

Explanation: PolyBase is used for data loading in SQL pool as it simplifies the process of reading data stored in Azure Blob Storage or Hadoop.

True or False: PolyBase needs a specialized driver or software installation to connect to Microsoft Azure.

• True
• False

Answer: False

Explanation: PolyBase connects to Microsoft Azure directly without the need for specialized drivers or software installation.

In the context of using PolyBase with Azure SQL Data Warehouse, what is an external table?

• A. A table created outside of the database
• B. A table pointing to data stored in Azure Blob Storage or Hadoop
• C. A table used for storing temporary data
• D. A table created in another database

Answer: B. A table pointing to data stored in Azure Blob Storage or Hadoop

Explanation: An external table in this context is a table created within your SQL pool that points to data stored in Azure Blob Storage or Hadoop.

True or False: It’s not possible to apply transformations to the data while loading data using PolyBase.

• True
• False

Answer: False

Explanation: While it’s true that PolyBase is typically used to load data into SQL pool as it is, it’s possible to apply transformations during data loading using T-SQL query capabilities.

Interview Questions

What is PolyBase in the context of Azure SQL Data Warehouse (SQL pool)?

PolyBase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server. It allows you to run queries on external data in Hadoop or Azure Blob Storage. The queries are optimized to push computation to Hadoop.

How can you use PolyBase to load data to a SQL pool in Azure?

You can use PolyBase to load data into a SQL pool by creating external file formats and external tables that reference the external data, then using the CREATE TABLE AS SELECT (CTAS) T-SQL command to load the data.

What is the purpose of the external file formats in PolyBase?

External file formats specify the data format of the external data source in Hadoop or Azure Blob Storage. Supported formats include parquet, rcfile, delimited text, orc, and others.

What are the requirements for source data files when using PolyBase load to a SQL pool?

The source data files must be in UTF-8 encoding, must not exceed 1GB in size after compression, and should not have a column with more than 1 million unique values.

How does PolyBase improve the performance in loading data into SQL pools?

PolyBase improves loading performance by using parallel data loading. It can use multiple read-only database copies and evenly distribute the load across them, speeding up the data loading process.

What is the role of the master key in PolyBase to load data to SQL Pool?

A master key is required to secure the credentials used by the SQL Data Warehouse to access the external data sources.

What happens when an error appears during a data loading process with PolyBase?

If an error occurs during the data loading process, PolyBase will stop the operation and return an error message. It provides detailed diagnostics which help to isolate and fix the issue.

Can PolyBase load data to a SQL pool from Azure Blob Storage?

Yes, PolyBase can load data from Azure Blob Storage. You will need to define an external data source that references Blob Storage and create an external file format that specifies the format of the files in Storage.

Can you use Transact-SQL commands to load data with PolyBase?

Yes, you can use Transact-SQL commands such as CREATE TABLE AS SELECT (CTAS) to execute a data loading job in PolyBase.

Is there a limitation on the number of rows that PolyBase can load into a SQL pool from a file?

No, there is no limit on the number of rows. However, the size of the source data file should not exceed 1GB after compression when using PolyBase to load the data.

What are the security configuration needs to access external data sources with PolyBase?

To access external data sources, you need to create a database scoped credential with the appropriate identity and secret to the external data source, and grant necessary permissions.

When should you consider using PolyBase to load data to SQL pools?

PolyBase is typically considered when loading data from Azure Storage Blobs or Hadoop. It capitalizes on performance by utilizing parallel data loading and can handle large volumes of data efficiently.

What data types does PolyBase support when loading data to SQL pools?

PolyBase supports many data types including bigint, bit, date, float, int, smallint, and others. However, it doesn’t support text, image, ntext, hierarchyid, xml, and spatial data types.

Can PolyBase update data in SQL pools?

No, PolyBase cannot be used to update data. It is primarily used for data loading and data querying purposes.

What role does the Database Master Key play in PolyBase?

The Database Master Key is used to protect the database credential, which contains the secret necessary for external data access. This mechanism secures communication between Azure Synapse Analytics and an external data source.