Using CSV files from Azure Data Lake Storage Gen2 in SQL Server

Let’s say we have on-premises SQL Server and Azure blob storage account. An external company or source uploads data in the blob storage daily basis. The requirement is to query these csv file data after receiving the csv files in the Azure blob storage. We need to process those files from an on-premises SQL Server using PolyBase.

This tutorial is to show you how to configure PolyBase and query Azure Blob Storage Gen2 data using native T-SQL technique. Following are the required steps to configure a remote Azure Blob storage:

1. We need an Azure Subscription.
2. Hierarchical namespace enabled storage gen2 account, blob container and folder.
3. Blob storage Access Key.
4. Install and configure PolyBase Engine feature.
5. Create a database master key
6. Create database scoped credential.
7. Create an external file format.
8. Create an external table.
9. Query the data with T-SQL.

Our Scenario: In our tutorial, we have the following Azure blob storage configuration:

Azure Blob Account: home80
Blob Storage Container: import
Folder inside the container: csvfile
Inside the folder: Two CSV files “Address.CSV” and “CountryRegion.CSV”.

 

 

Step by step:

Step 1: Install and configure PolyBase Engine along with the Hadoop connector.

Step 2: Enable PolyBase Engine and Hadoop connectivity and then restart the SQL Server Service.

EXEC sp_configure

     @configname = 'polybase enabled',

     @configvalue = 1;

RECONFIGURE;

 

EXEC sp_configure

     @configname = 'hadoop connectivity',

     @configvalue = 7;

RECONFIGURE;

Step 3: Create a schema, optional but for better organization:

USE AzureDB

GO

CREATE SCHEMA ext;

 

Step 4: Create a master key on the database, if one does not already exist. This is required to encrypt the credential secret.

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'azure@123';

Step 5: Create a database scoped credential:

 

CREATE DATABASE SCOPED CREDENTIAL AzureBlobCredential

            WITH IDENTITY = 'home80',

            SECRET = 'JcGvud0UIFT4qAlrZSyregc3CoqLkxpB/a3jNYByaukvT0BqT4/TpHTsbjqlOwBEHjevnjbSwPsCBK5WmRJDFA==';

GO

See the following screenshot to obtain the Access Key. Note that the Identity can be anything and it will not be used to authenticate while accessing blob storage container. Here, it is just to satisfy the syntax requirement.

  

Step 6: Create a external data source “AzureBlobSource” as follows:

 

CREATE EXTERNAL DATA SOURCE AzureBlobSource

            WITH (

                        TYPE = HADOOP,

                        LOCATION = 'wasbs://import@home80.blob.core.windows.net',

                        CREDENTIAL = AzureBlobCredential,

                        PUSHDOWN = ON

);

Step 7: Create an external file format “TextFileFormat” as follows:

CREATE EXTERNAL FILE FORMAT TextFileFormat

            WITH ( 

        FORMAT_TYPE = DELIMITEDTEXT,  

        FORMAT_OPTIONS

                        (          

                                    FIELD_TERMINATOR =',',

                                    USE_TYPE_DEFAULT = TRUE,

                                    STRING_DELIMITER = '"'

                                    )

                        );

Step 8: We will be importing two CSV files; thus we need to create two external tables.

 

CREATE EXTERNAL TABLE [ext].[AddressTbl]

            (           [AddressLine1] VARCHAR(100),

                         [City]         VARCHAR(50),

                         [PostalCode]   VARCHAR(25),

                         [ModifiedDate] VARCHAR(32))

            WITH (

                        LOCATION = '/csvfile/Address.csv',

                        DATA_SOURCE = AzureBlobSource,

                        FILE_FORMAT = TextFileFormat,

                        REJECT_TYPE = VALUE,

                        REJECT_VALUE = 5);

GO

 

CREATE EXTERNAL TABLE [ext].[CountryRegionTbl]

            (           [StateProvinceCode] VARCHAR(20),

                         [StateProvinceName] VARCHAR(50),

                         [CountryRegionCode] VARCHAR(25),

                         [CountryRegionName] VARCHAR(25))

            WITH (

                        LOCATION = '/csvfile/CountryRegion.csv',

                        DATA_SOURCE = AzureBlobSource,

                        FILE_FORMAT = TextFileFormat,

                        REJECT_TYPE = VALUE,

                        REJECT_VALUE = 5);

GO

Step 9: Optionally, we can create statistics on the external tables that we have just created in the above step.

 

CREATE STATISTICS [statCity ] ON [ext].[AddressTbl]([City])

CREATE STATISTICS [statPostalCodeCity] ON [ext].[AddressTbl]([PostalCode], [City])

Step 10: We are done and now it is the time to execute some SELECT statement.

 

SELECT AddressLine1,

       City,

       PostalCode,

       CAST(ModifiedDate AS DATETIME) AS ModifiedDate

FROM [ext].[AddressTbl]

WHERE City = 'Los Angeles';

 

SELECT AddressLine1,

       City,

       PostalCode,

       ModifiedDate

FROM [ext].[AddressTbl]

WHERE PostalCode LIKE '91%'

      AND City LIKE 'L%';

Step 11: Here is the out from the above queries:

 Conclusion: While PolyBase is a good data integration mechanism, but it has some limitation when developing solution from on-premises SQL Server. The CSV file must be formatted properly to reduce the chance of row rejection. Note that the PolyBase on an on-premises SQL Server does not support FIRST_ROW and REJECTED_ROW_LOCATION, therefore an extra effort is required for the text file row header and bad data in the CSV file, otherwise query will produce unexpected result or will fail when the REJECT_VALUE is reached.

I hope that this will help you to get started with Querying Azure blob storage data with the PolyBase Engine from your on-premises SQL Server.

Further Reading:

PolyBase features and limitations

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary?view=sql-server-ver15

Install PolyBase on Windows

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-installation?view=sql-server-ver15

Configure PolyBase to access external data in Azure Blob Storage

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-azure-blob-storage?view=sql-server-ver15

Linked Server vs PolyBase – Efficient data Integration and Processing Technique

Are you still using Linked Server while there is a better option? Although using Linked Server is generally simple, it has some well-known severe performance, limitation and security issues. Writing efficient Linked Server queries is very tricky and requires that the developer has a very good understanding of SQL Server’s Database Engine knowledge.

 

PolyBase – as data integration and Processing Technique:

I have experienced, many developers lack a good understanding of query processing techniques. While small scale data processing tasks using Linked Server work, for a heavier workload, Linked Server query suffers significantly from performance issues, extensive resource utilization and often receives “query-timeout”.

 

This article is not a how-to guide for writing efficient Linked Server based query, but rather using an alternative technique known as PolyBase to overcome some known query processing difficulties. Starting from SQL Server 2016, Microsoft has introduced PolyBase. The functionality of PolyBase supports all kind of data sources and it has now becomes a de-facto data integration choice and technique to process remote datasets on Microsoft Data Platform.

 

Here, we will examine and compare the same query utilizing both “Linked Server” and “PolyBase”.

 

Query Testing Scenario:

The following is the local and remote server, where we will be testing each of the techniques:

1. POLY01: We have SQL Server 2019, with a single database named TestDB which has only one table titled “StateProvinceCountryRegion”.
2. WIN1601: We have SQL Server 2014, with the database “AdventureWorks2014”.

 

We will write a query from POLY01 which will join a table on the remote server WIN1601. We will use “Linked Server” and PolyBase to execute the same query to process the same data.

 

Linked Server: Lets create a “Linked Server” named LNKSRV from POLY01 to WIN1601.

 

On WIN1601 server:

(a)   Create a login “ployuser” with password “poly@123”.

(b)   Grant data_reader permission to the polyuser login on “AdventureWorks2014”. (Note we can also grant only SELECT permission to the table “Person.Address”).

 

On POLY01 server:

 

(c)   Execute the following statement to create the Linked Server, LNKSRV.

 

USE [master];

GO

EXEC master.dbo.sp_addlinkedserver

     @server = N'LNKSRV',

     @srvproduct = N'SQLSERVER',

     @provider = N'SQLNCLI',

     @datasrc = N'WIN1601';

GO

EXEC master.dbo.sp_addlinkedsrvlogin

     @rmtsrvname = N'LNKSRV',

     @useself = N'False',

     @locallogin = NULL,

     @rmtuser = N'polyuser',

     @rmtpassword = 'poly@123';

GO

(d)    Execute the following query on POLY01 server to test the Linked Server configuration.

 

USE TestDB

GO

 

SELECT

       a.AddressID,

       a.AddressLine1,

       a.City,                          

       a.StateProvinceID ,

       a.PostalCode

FROM OPENQUERY( [LNKSRV], 'SELECT AddressID, AddressLine1, City, StateProvinceID, PostalCode FROM [AdventureWorks2014].[person].[Address] ' ) AS a

 

Note: Direct specification of remote server as a data source will not work when a table contains a GEOGRAPHY or a GEOMETRY column. For example, the following query will fail with the error mentioned below:

 

SELECT a.AddressID,

       a.AddressLine1,

       a.City,

       a.StateProvinceID,

       a.PostalCode

FROM [LNKSRV].[AdventureWorks2014].[person].[Address] AS a;

 

Msg 7325, Level 16, State 1, Line 2

Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object '"AdventureWorks2014"."person"."Address"'.

 

PolyBase Connection:

On POLY01 Server, the Polybase Service has already been installed and configured, and is running. So the next task is to create a sample database and an external table.

 

(a)    Let’s create a test database:

USE master;

GO

CREATE DATABASE TestDB;

GO

 

(b)     Create a master key on the TestDB database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'poly@123';

 

(c)     Create a database scoped credential:

CREATE DATABASE SCOPED CREDENTIAL polycredential

       WITH IDENTITY = 'polyuser', SECRET = 'poly@123';

 

(d)    Create an external data source:

CREATE EXTERNAL DATA SOURCE ADWSource

          WITH (LOCATION = 'sqlserver://WIN1601',

          PUSHDOWN = ON,

          CREDENTIAL = polycredential);

 

(e)    Create an external table (creating schema is optional):

CREATE SCHEMA ext;

 

CREATE EXTERNAL TABLE [ext].[LinkTbl]

([AddressID]      [INT] NULL,

       [AddressLine1]    [NVARCHAR](60) NULL,

       [AddressLine2]    [NVARCHAR](60) NULL,

       [City]            [NVARCHAR](30) NULL,

       [StateProvinceID] [INT] NULL,

       [PostalCode]      [NVARCHAR](15) NULL)

       WITH (LOCATION = '[AdventureWorks2014].[person].[Address]',

              DATA_SOURCE = [ADWSource]);

 

(f)      Execute the following query on the POLY01 server to test the PolyBase configuration.

 

USE TestDB

GO

 

SELECT a.AddressID,

       a.AddressLine1,

       a.City,

       a.StateProvinceID,

       a.PostalCode

FROM ext.LinkTbl a;

 

Performance comparison Query:

Now that we have configured both the Linked Server and PolyBase, let’s create a table in the TestDB database as “StateProvinceCountryRegion” on POLY01. This table was derived from a view “vStateProvinceCountryRegion” in the “AdventureWorks2014” database. Also, create a clustered index on the StateProvinceID column.

 

To compare the query performance from Linked Server and PolyBase, execute the following code while enabling the “Include Live Query Statistics” in SSMS.

 

-- Query using PolyBase

DBCC DROPCLEANBUFFERS;

USE TestDB;

GO

SELECT a.AddressID,

       a.AddressLine1,

       a.City,

       a.StateProvinceID,

       a.PostalCode,

       b.StateProvinceID,

       b.StateProvinceCode,

       b.StateProvinceName,

       b.CountryRegionCode,

       b.CountryRegionName

FROM ext.LinkTbl a

     LEFT JOIN dbo.StateProvinceCountryRegion b ON a.StateProvinceID = b.StateProvinceID

WHERE b.CountryRegionName = 'Canada'

      AND a.City = 'Calgary';

 

-- Query using Linked Server

DBCC DROPCLEANBUFFERS;

SELECT a.AddressID,

       a.AddressLine1,

       a.City,

       a.StateProvinceID,

       a.PostalCode,

       b.StateProvinceID,

       b.StateProvinceCode,

       b.StateProvinceName,

       b.CountryRegionCode,

       b.CountryRegionName

FROM OPENQUERY([LNKSRV], 'SELECT AddressID, AddressLine1, City, StateProvinceID, PostalCode FROM [AdventureWorks2014].[person].[Address] ') AS a

     LEFT JOIN dbo.StateProvinceCountryRegion b

ON a.StateProvinceID = b.StateProvinceID

WHERE b.CountryRegionName = 'Canada'

      AND a.City = 'Calgary';

 

Linked Server vs PolyBase Query Execution plan and Statistics:

 

Performance Gain:

From the above execution plan, it is crystal clear that the query cost of PolyBase is 2% whereas, whereas it is 98% for Linked Server.

 

As we can see from the query statistics, using PolyBase to query remote data is significantly more efficient. So what is the difference between the two techniques? In a nutshell, the Linked Server query fetches all the records from the target server and then filters it, while the PolyBase query applies the filter condition first and then fetches the record.

 

Conclusion:

If we understand the above execution plan, then I hope that it is now easy to rewrite a Linked Server query to significantly improve performance. If tuning or rewriting a Linked Server query is difficult for whatever reason, then it will be a good idea to start using the PolyBase technique with your next development project.

 

Further Reading:

What is PolyBase?

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15

 

Configure PolyBase to access external data in SQL Server

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-sql-server?view=sql-server-ver15

 

PolyBase Transact-SQL reference

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-t-sql-objects?view=sql-server-ver15