Parquet

Parquet files are a formatted file founded by an Apache project that was started in 2013. The Apache project website at https://parquet.apache.org has a simple definition: “Apache Parquet is an open source, column-oriented data file format designed for efficient data storage and retrieval.” Column-oriented makes parquet an efficient format for analytic workloads, like columnstore indexes in SQL Server. Parquet is a binary formatted file as opposed to CSV or text. It contains metadata inside the file about the columns and data types. Even though parquet uses a binary format, there is documentation on the details, so developers know how to read and write the format. You can read the format details at https://parquet.apache.org/docs/file-format/.

Parquet has become one of the most popular file formats to use for analytics from ground to cloud. Its popularity is one of the reasons we chose to support parquet as a native format for data virtualization in SQL Server 2022. SQL Server 2022 natively supports reading parquet files and writing out SQL data as formatted parquet files.

Delta

Parquet files are static files to read and write as an entire file unit. An open source project was created by several companies called Delta Lake to support a lakehouse of data based on delta tables. Delta tables are effectively a collection of parquet formatted files with transaction log type capabilities through JSON files. I found this interesting article by Databricks on the internals of the “transaction log” for delta at https://databricks.com/blog/2019/08/21/diving-into-delta-lake-unpacking-the-transaction-log.html.

You can read about the Delta Lake project and all resources at https://delta.io. Since Delta Lake is a complete open source project (the GitHub project is at https://github.com/delta-io/delta), you can read all the details of how delta tables are formatted, but most users create and use delta tables through interfaces such as Spark. For example, you can see at https://docs.delta.io/latest/delta-batch.html#-ddlcreatetable how to use T-SQL with Spark to create a new delta table.

SQL Server 2022 supports natively reading delta tables, but you cannot export data in SQL Server to delta tables. You can export SQL data to parquet files. Spark offers functionality to convert parquet to delta should you need to do that.

Using parquet files offers no capabilities to use predicate pushdown or push your “filter” to a file to get only the data you need. Delta tables do offer this type of capability through a concept called a partition column. You will see in this chapter examples of how to use both parquet and delta with SQL Server 2022.

Install and Configure Polybase

Even though REST APIs do not use Polybase services in SQL Server 2022, you must install the Polybase Query Service for External Data feature (or add the feature). You must also enable Polybase setting the sp_configure ‘polybase enabled’ option to 1. If you plan to use CETAS, you will also need to set ‘allow polybase export’ to 1.

Set Up Credentials and Data Sources

The next step, within the context of a user database, is to create a database-scoped credential using the CREATE DATABASE SCOPED CREDENTIAL statement and external data source using the CREATE EXTERNAL DATA SOURCE statement (which requires the credential). You will need to do this for each connector you plan to use. You must first create a master key before you can create a database-scoped credential using the CREATE MASTER KEY statement, but you only need to do this once no matter how many credentials and data sources you want to create in the database. These objects are stored as metadata in system tables in the user database.

Query Files Directly with OPENROWSET( )

With an external data source created, you can directly query files using the OPENROWSET() T-SQL function with the BULK option. File formats such as PARQUET and DELTA don’t require any options for column names or types; CSV and text files will require this.

Create an External Table

You can also create an external table using the CREATE EXTERNAL TABLE statement. The external table requires an external data source and can be mapped directly to files for connectors using LOCATION. You must first create a file format, using the CREATE EXTERNAL FILE FORMAT statement, for use with the external table. The file format can be for a “known” format like ParquetFileFormat or for a CSV file, which will require you to provide more details. The external table can provide all the column names from the source file or a subset. No data is stored for an external table, only metadata in system tables, which is the same for file formats.

You can also use the CREATE EXTERNAL TABLE AS SELECT (CETAS) syntax to create an external table and store the results from a user table in the LOCATION target. It is a method to “export” data to REST API connectors. You can even use CETAS with the source of a query to OPENROWSET().

Once you create an external table, you can query the external table like any other SQL table (and even assign permissions to it). You can join with other user tables or other external tables and execute OPENROWSET() queries. You can use standard T-SQL like INSERT..SELECT or SELECT INTO to take data from external tables and populate user tables (i.e., ingestion).

Prerequisites

Set Up minio for the Exercise

To use minio.exe with SQL Server, TLS is required. In order to use TLS, you must have a valid certificate. For testing purposes, we will generate a self-signed certificate.

This program starts and runs until you quit with Ctrl+C. Your output in the command prompt should look something like this:

MinIO Object Storage Server

Copyright: 2015-2022 MinIO, Inc.

License: GNU AGPLv3 <https://www.gnu.org/licenses/agpl-3.0.html>

Version: RELEASE.2022-07-30T05-21-40Z (go1.18.4 windows/amd64)

Status:         1 Online, 0 Offline.

API: https://<local IP>:9000  https://127.0.0.1:9000

RootUser: <user>

RootPass: <password>

Console: https://<local IP>:9001 https://127.0.0.1:9001

RootUser: <user>

RootPass: <password>

Command-line: https://docs.min.io/docs/minio-client-quickstart-guide

   $ mc.exe alias set myminio https://<local IP>:9000 <user> <password>

Documentation: https://docs.min.io

1. 7.

   Test your connection to minio by using a web browser on the local computer for https://127.0.0.1:9001. You should be presented with a login screen like Figure 7-3. Use the RootUser and RootPass from the preceding minio server output.

    

A screenshot of the minio login window. At the top, minio console multi cloud object storage is written. Below are options for writing usernames and passwords.

1. 8.

   On the left-hand side menu, click Identity and Users. Select Create User. Create a user and password. Select the readwrite policy for the user. Note down this user and password. This is the user and password that you will use for the SECRET value in the creates3creds.sql script later in this exercise.

    
2. 9.

   Select the menu option for Buckets. Select Create Bucket. Use a bucket name of wwi. Leave all the defaults and click Create Bucket. Your screen should now look like Figure 7-4.

    

A screenshot of the minio window. On the left, the buckets option is highlighted to create a new bucket.

Learn to Use REST API to Access Parquet Files on S3

With an S3 provider ready to use, we can now access S3 compatible storage with SQL Server. Follow these steps for the exercise for basics of REST API data virtualization to access parquet files on an S3 object provider. The next section has an exercise to access delta files.

1. 1.

   Copy the WideWorldImporters sample database from https://aka.ms/WideWorldImporters to a local directory (the restore script assumes c:sql_sample_databases).

    
2. 2.

   Edit the restorewwi.sql script to specify the correct path for the backup and where data and log files should go.

    
3. 3.
   Execute the script restorewwi.sql. This script executes the following T-SQL statements:

   USE master;

   GO

   DROP DATABASE IF EXISTS WideWorldImporters;

   GO

   RESTORE DATABASE WideWorldImporters FROM DISK = 'c:sql_sample_databasesWideWorldImporters-Full.bak' with

   MOVE 'WWI_Primary' TO 'c:sql_sample_databasesWideWorldImporters.mdf',

   MOVE 'WWI_UserData' TO 'c:sql_sample_databasesWideWorldImporters_UserData.ndf',

   MOVE 'WWI_Log' TO 'c:sql_sample_databasesWideWorldImporters.ldf',

   MOVE 'WWI_InMemory_Data_1' TO 'c:sql_sample_databasesWideWorldImporters_InMemory_Data_1',

   stats=5;

   GO

   ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR ALL;

   GO
    
4. 4.
   Execute the script enablepolybase.sql to configure instance-level settings to allow Polybase features to execute and export data from Polybase to S3. This script executes the following T-SQL statements:

   EXEC sp_configure 'polybase enabled', 1;

   GO

   RECONFIGURE;

   GO

   EXEC sp_configure 'allow polybase export', 1;

   GO

   RECONFIGURE;

   GO
    
5. 5.
   Edit the script createmasterkey.sql to put in a password. Execute the script to create a master key to protect the database-scoped credential. This script executes the following T-SQL statements:

   USE [WideWorldImporters]

   GO

   IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')

         CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

   GO
    
6. 6.
   Edit the script creates3creds.sql to put in the user and password you created in the minio console for SECRET. Execute the script to create a database-scoped credential. This script executes the following T-SQL statements:

   IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = 's3_wwi_cred')

       DROP DATABASE SCOPED CREDENTIAL s3_wwi_cred;

   GO

   CREATE DATABASE SCOPED CREDENTIAL s3_wwi_cred

   WITH IDENTITY = 'S3 Access Key',

   SECRET = '<user>:<password>';

   GO
    
7. 7.
   Edit the script creates3datasource.sql to substitute in your local IP address for the minio server. Execute the script to create an external data source. This script executes the following T-SQL statements:

   IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 's3_wwi')

         DROP EXTERNAL DATA SOURCE s3_wwi;

   GO

   CREATE EXTERNAL DATA SOURCE s3_wwi

   WITH

   (

    LOCATION = 's3://<your local IP>:9000'

   ,CREDENTIAL = s3_wwi_cred

   );

   GO

   Tip You could put in the bucket name here after the port number so that the data source is focused only on a specific bucket. We will instead use the bucket when specifying location for queries.

    
8. 8.
   Create a file format for parquet by executing the script createparquetfileformat.sql. This script executes the following T-SQL statements:

   USE [WideWorldImporters];

   GO

   IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'ParquetFileFormat')

         DROP EXTERNAL FILE FORMAT ParquetFileFormat;

   CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);

   GO
    
9. 9.
   Let’s first learn how to export data in parquet format to the wwi bucket by executing the script wwi_cetas.sql. In SSMS, select the Include Actual Execution Plan option. This script executes the following T-SQL statements:

   USE [WideWorldImporters];

   GO

   IF OBJECT_ID('wwi_customer_transactions', 'U') IS NOT NULL

         DROP EXTERNAL TABLE wwi_customer_transactions;

   GO

   CREATE EXTERNAL TABLE wwi_customer_transactions

   WITH (

       LOCATION = '/wwi/',

       DATA_SOURCE = s3_wwi,

       FILE_FORMAT = ParquetFileFormat

   )

   AS

   SELECT * FROM Sales.CustomerTransactions;

   GO

   Let’s examine this script. The LOCATION is the bucket name. The LOCATION value can be a bucket name or even a series of folders and even a specific filename. In this case, because the external data source didn’t specify a specific bucket, I need to specify that here. Since I only specified the bucket, the query will place all files in the parent folder. There is no predicate pushdown with parquet files and REST API connectors. Any filtering is done inside SQL Server (even if you specify a WHERE clause).

   The DATA_SOURCE is the external data source we created to connect to the minio server. The file format matches up to parquet. Notice we do not have to specify anything about the format other than just parquet. We can also include column names with this definition, but we don’t need to. Using the AS SELECT part of the statement will create a single parquet file with column names, types, and data.

   In the Messages tab, you should see 97147 rows affected. In the Execution Plan tab you should see a plan like Figure 7-5.

    

A flowchart to create an external table. The steps are as follows. Clustered index scan, compute scalar, put, and select into.

Notice the Put operator, which is used to export data to the s3 connector as a parquet file.

1. 10.

   Use the minio console to browse the wwi bucket and confirm the parquet file has been added. Your results should look like Figure 7-6.

    

A screenshot of the minio window. A parquet file with the last modified information and size is open in the bucket created in w w i.

1. 11.
   Query the new external table by executing the script querywwiexternaldata.sql. This script executes the following T-SQL statements:

   USE [WideWorldImporters];

   GO

   SELECT c.CustomerName, SUM(wct.OutstandingBalance) as total_balance

   FROM wwi_customer_transactions wct

   JOIN Sales.Customers c

   ON wct.CustomerID = c.CustomerID

   GROUP BY c.CustomerName

   ORDER BY total_balance DESC;

   GO

   Notice in this example the script joins the external table (stored in parquet in S3) with a local table in the user database. Your results should display 263 rows. One common use case for an example like this is to build a VIEW on top of this query and only give users access to the view. They will be abstracted from the source of the data, whether it be in SQL Server or a parquet file.

    
2. 12.

   Use SSMS with Object Explorer to see the column definitions for the table created through CETAS. Your results should look like Figure 7-7.

    

A folder screenshot view of a list of columns created through C E T A S. A file titled d b o dot w w i underscore customer underscore transactions is highlighted.

Notice the column names and types match the original table in the database; this demonstrates the power of parquet including metadata.

1. 13.
   Let’s use OPENROWSET() to run an “ad hoc” query from the generated parquet file stored in S3 by executing the script querybyopenrowset.sql. In SSMS, select the Include Actual Execution Plan option. This script executes the following T-SQL statements:

   USE [WideWorldImporters];

   GO

   SELECT *

   FROM OPENROWSET

         (BULK '/wwi/'

         , FORMAT = 'PARQUET'

         , DATA_SOURCE = 's3_wwi')

   as [wwi_customer_transactions_file];

   GO

   It turns out the BULK option is not just about the BULK insert of data; it can also be used to “open a rowset” on a file.

   In the Messages tab you should see 97147 rows affected, which is the same number of rows in the original table, Sales.CustomerTransactions. If you look at the Execution Plan tab, you should see a query execution plan like Figure 7-8.

    

A table titled remote scan. An arrow points to the remote scan value of the estimated number of rows per execution. Below the table is an output list.

Notice that the estimated number of rows is roughly 30% of the total actual rows. This is because the optimizer doesn’t have any statistics from the parquet file itself. This is opposite from wwi_customer_transactions, which was created as an external table. You can manually create statistics on an external table, or if you have the database option AUTO_CREATE_STATISTICS set to ON (the default), statistics will be automatically created when you query the external table. The estimates will be accurate if these statistics are available. OPENROWSET() query performance may or may not be adversely affected without statistics.

the query processor in the engine must bring the results of the parquet file(s) in this bucket parent folder and then filter within the engine to produce the final result.

Learn How to Use Delta Files

This next exercise assumes you have completed all the prerequisites for the previous exercise with parquet. Furthermore, it assumes you have completed all the steps in the previous exercise to restore the WideWorldImporters database, configure Polybase, and create a master key, database-scoped credential, external data source, and external file format. You can leave the previous wwi bucket. You will create a new bucket as part of this exercise.

All new scripts and files specifically for this exercise can be found in the ch07_datavirt_objectstoragedatavirtdelta folder. If you don’t want to go through the exercise to use minio for delta, you can view the results in the notebook querydelta.ipynb.

You have now seen several examples of how to query parquet files and delta tables using the new REST API interfaces for S3. You have also seen some interesting ways to export SQL data or results of OPENROWSET queries into parquet formatted files.