Today you’ll see how to export multiple tables to Parquet files in Azure Data Lake Storage with Azure Synapse Analytics Workspaces using Azure Data Factory.
I will run you through how to export the tables from a Adventure Works LT database to Azure Data Lake Storage using Parquet files. This tutorial is valid for Azure Data Factory in Azure Synapse Analytics Workspaces or standalone service.
Before you begin, in order to best benefit from this tutorial, I suggest that you have a look at my previous blog first here.
Are we doing incremental loads? Not at this stage, but look for this topic in upcoming blog posts.
Modify Parquet Dataset
To start, the first thing you need to do is modify your destination parquet dataset to be more generic by creating a FileName parameter.
Add a parameter
Modify the file name using dynamic content. The file format is FileName_yyyyMMdd.parquet and the folder location is:
One of the most amazing features in Azure Data Factory is that parallelism is enabled by default. Working with multiple data inputs and outputs is really easy using dynamic expressions.
We want to include a lookup and “for each” activity. Inside the “for each” activity, you can move the “ExportToParquet” activity.
In settings I’m using the INFORMATION_SCHEMA.TABLES (you can use sys.tables) to get all the tables that I want to import.
select Table_schema, Table_name from information_schema.tables where table_type ='BASE TABLE' and table_schema='SalesLT'
select Table_schema = schema_name(schema_id), Table_name = name from sys.tables where schema_name(schema_id) ='SalesLT'
This query will retrieve the table schema and table name.
There isn’t much that we need to define here, just a good name and the items that we can loop through (tables in this case).
Next, you’ll modify the “ExportToParquet” activity.
Modify Copy Activity
Now it’s time to modify the copy activity and publish the changes.
Change the query by using dynamic content. If your source column names have spaces, check out this blog post on exporting Parquet Files with Column Names with Spaces.
@concat('select * from ',item().Table_Schema,'.',item().Table_Name)
Change the dataset and include dynamic content for the file name parameter.
Publish the changes
You are ready to publish the changes. However, I suggest debugging first!
Execute and monitor
Finally, after executing the pipeline successfully, you’ll find all the files in your Azure Data Lake in Parquet format.
After that, if you click on the activity, you are able to find the details for each of the tables.
There is one file per table.
To sum up, it only takes a few minutes to start developing enterprise data movement pipelines using native built-in features in Azure Data Factory. I remember a few years ago, I was building complex frameworks in SQL Server Integration Services to handle these scenarios. Luckily, those days are gone!
Next up, in my next blog post, I’ll show you how to consume information in Notebooks and the different options available when you use them.