Let's start by configuring Enzo Server so that you can access Azure Storage, and read Parquet files using SQL commands. This step requires that you have installed Enzo Server and that the Enzo service is running. To begin, start Enzo Manager (the management application for Enzo Server).

Download Parquet and AzureStorage Adapters

From Enzo Manager, open the Adapter Marketplace (under the Tools menu). Find the Parquet adapter and click on the Install button. A screen may appear asking you to enter a license key; click on Generate free 30-day license to generate a trial license key and click OK. The Parquet adapter is now queued for installation. Find the Azure Adapter from the list of adapters, and click on the Install button. Once both adapters are queued for installation, click on Close & Install/Update.

At this point, both adapters have been downloaded. A window will ask you to restart Enzo Server. Once Enzo Server has restarted, you will see both Azure Storage and Parquet adapters listed in Enzo Manager.

Enzo Marketplace - Parquet and AzureStorage adapters

Create and Locate Azure Storage Keys

If you already have an Azure subscription and already have an Azure Storage Account with Access Keys, you can skipt his section. Before you can configure the AzureStorage adapter, you will need to have a Microsoft Azure subscription. If you do not have an Azure account, you can sign-up by visiting the https://portal.azure.com website. Once you have an Azure subscription, follow the steps from the Azure documentation to create an Azure Storage service.

Once you have created the Azure Storage service in Azure, locate the Access Keys of your Storage Account. They can be found in the Azure Portal. Follow the Microsoft documentation for instructions on how to create and find the Access Keys that are needed in the following section.

Configure the AzureStorage Adapter

Back in Enzo Manager, click on the AzureStorage adapter, and click on the New Config... button. This screen will require the following information:

• Config Name: Enter a friendly name for this setting (ex: default)
• account: The name of the Azure Storage Account you created above
• key: The Access Key for the Azure Storage Account
• useSSL: Check the box to use an encrypted connection
• retries: Enter 1 for the retry count

Check the box indicating this will be the default setting, and click OK. The following screen should now show your settings for the AzureStorage adapter.

Make sure the configuration setting is marked as the Default setting. A star (*) should be visible next the configuration name specified. If you do not see the star, click on the Set as Default button.

Sample AzureStorage configuration setting in Enzo Manager

Configure the Parquet Adapter

Click on the Parquet adapter, and click on the New Config... button. This screen will require the following information:

• Config Name: Enter a friendly name for this setting (ex: config1)
• defaultFilePath: Leave this setting empty
• schemaFile: Leave this setting empty

Check the box indicating this will be the default setting, and click OK. The following screen should now show your settings for the AzureStorage adapter.

Sample Parquet configuration setting in Enzo Manager

This lab doesn't require default settings for the Parquet adapter, so the above settings can be left empty.

Download the following Zip file (https://www.enzounified.com/downloads/userdataparquet.zip) and unzip its content so that all the files can be found under you local C:\ drive under the c:\tmp\parquet\userdata\ directory (you will need to create the directory structure as needed).

To test the Parquet adapter, go back to the Data Explorer window and run the following command:

SELECT * FROM Parquet.rows WHERE fileName='c:\tmp\parquet\userdata\userdata*'

The fileName parameter allows the use of wildcards; when a wildcard is identified, the Parquet adapter will execute the query on all the files that match the wildcard and return a combined resultset. This is referred to as automatic "sharding". You can also query a single file by providing the filename without wildcard (for example: userdata1.parquet).

Reading multiple files in parallel (sharding) is available with a paid edition of Enzo Server.

In this section, we are going to upload the Parquet files into the Azure Storage container created in Step 1 using SQL commands. You can also upload the files manually from the Azure Portal; however using SQL commands allows you to automate data movement in/out of Azure.

Execute the following commands from the Data Explorer window (see Step 1 on how to open Data Explorer in Enzo Manager). This uploads the parquet files in Azure.

EXEC AzureStorage.SaveFileBlob 'parquet', 'userdata1.parquet', 'c:\tmp\parquet\userdata\userdata1.parquet'
EXEC AzureStorage.SaveFileBlob 'parquet', 'userdata2.parquet', 'c:\tmp\parquet\userdata\userdata2.parquet'
EXEC AzureStorage.SaveFileBlob 'parquet', 'userdata3.parquet', 'c:\tmp\parquet\userdata\userdata3.parquet'
EXEC AzureStorage.SaveFileBlob 'parquet', 'userdata4.parquet', 'c:\tmp\parquet\userdata\userdata4.parquet'
EXEC AzureStorage.SaveFileBlob 'parquet', 'userdata5.parquet', 'c:\tmp\parquet\userdata\userdata5.parquet'
EXEC AzureStorage.SaveFileBlob 'parquet', 'headersonly.parquet', 'c:\tmp\parquet\userdata\headersonly.parquet'

To verify the files have been downloaded successfully, execute the following command:

SELECT * FROM AzureStorage.Blobs WHERE container='parquet'

Upload Parquet files in an Azure Storage Container

Now that the Parquet files are in Azure, let's use SQL Server Management Studio (SSMS) to query the files directly. As we saw previously, it is possible to query Parquet files stored in a local directory; in this section we will query Parquet files stored in Azure.

Let's first connect to Enzo Server using SSMS.

For instructions on how to connect to Enzo Server from SSMS, please see the Quick Start section in the Enzo Server online documentation.

Connect to Enzo Server from SSMS

At this point, SSMS is connected to Enzo Server; you can query Enzo Server using SQL commands. Let's query the Parquet files as we did previously, except that the connection will be made to Azure. To retrieve data from Parquet files in Azure, execute the following command from SSMS:

SELECT * FROM parquet.rows WHERE 
	filename='azurestorage@parquet://userdata?.parquet'
	AND country='Italy'

You can also access Parquet files stored in S3 buckets; this requires installing the S3 adapter. The SQL command looks similar: SELECT * FROM Parquet.Rows WHERE filename='s3@bucket://filename.parquet'

Reading multiple files in parallel (sharding) is available with a paid edition of Enzo Server.

Query Parquet Files in Azure from SSMS

Now that we can query Parquet files stored in Azure, let's import the data into a SQL Server table. We first need to setup a Linked Server from SQL Server to Enzo Server so that we can import the data into SQL Server directly.

To configure a Linked Server to Enzo Server, see the online documentation here. Once completed, open a new Query Window in SSMS, and connect directly to SQL Server (not Enzo Server), and create a test database with a target table:

CREATE DATABASE testload
GO
USE testload
GO
CREATE TABLE tbl ([registration_dttm] datetime, [id] int, first_name nvarchar(100), 
	last_name nvarchar(100), email nvarchar(255), gender nvarchar(25), 
	ip_address nvarchar(50), cc nvarchar(50), country nvarchar(50), birthdate nvarchar(50), 
	salary decimal(10,2), title nvarchar(50), comments nvarchar(255))

To explore a simpler and more consistent way of importing data into any database platform, including the ability to only import changes into the target table, see Enzo DataZen.

The simplest way to import data into SQL Server is to use an EXEC command against Enzo Server. The following command will import all the rows returned found in the Parquet files in Azure into the local SQL Server table. Note: this command assumes that the Linked Server connection is called 'localhost,9550'.

INSERT INTO tbl 
EXEC [localhost,9550].BSC.parquet.readrows  
	null,
	'azurestorage@parquet://userdata?.parquet'

SELECT * FROM tbl 

Import Parquet Files in SQL Server with Enzo Server

At this point, all available records found in Parquet files has been loaded into a local SQL Server table, which can be queries using the full power of the SQL language.