Export Multiple Tables to Parquet Files in Azure Synapse Analytics

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. 

Pre-requirements 

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: 

• Dlfs 
  • Demos 
    • AdventureWorks 
      • YYYY 
        • YYYYMM 
          • YYYYMMDD 

@{formatDateTime(utcnow(),'yyyy')}/@{formatDateTime(utcnow(),'yyyyMM')}/@{formatDateTime(utcnow(),'yyyyMMdd')}/@{concat(dataset().FileName,'_',formatDateTime(utcnow(),'yyyyMMdd'),'.parquet')} 

Modify Pipeline 

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. 

Lookup Definition 

In settings I’m using the INFORMATION_SCHEMA.TABLES (you can use sys.tables) to get all the tables that I want to import. 

Information_Schema 

select Table_schema, Table_name from information_schema.tables 

where table_type ='BASE TABLE' 

and table_schema='SalesLT' 

Sys.tables 

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. 

ForEachTable Definition 

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). 

@activity('GetTables').output.value 

Next, you’ll modify the “ExportToParquet” activity. 

Modify Copy Activity 

Now it’s time to modify the copy activity and publish the changes. 

Source 

Change the query by using dynamic content. 

@concat('select * from ',item().Table_Schema,'.',item().Table_Name) 

Sink 

Change the dataset and include dynamic content for the file name parameter. 

@concat(item().Table_Schema,'_',item().Table_Name) 

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. 

Final Thoughts 

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! 

What’s Next? 

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. 

Check out these other blog posts:

Build Power BI Reports in Azure Synapse Analytics

Create Azure Custom Reader Role for Data Factory

Azure Data Factory Locks