Tech Talk Corner Sign up with your email address to be the first to know about new publications

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 

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. 

Export to Parquet

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. 

Retrieve data Azure Synapse Analytics

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

Sink 

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

@concat(item().Table_Schema,'_',item().Table_Name) 
Sink change the dataset

Publish the changes 

You are ready to publish the changes. However, I suggest debugging first! 

Publish changes

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.  

Export Adventure Works to Parquet

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:

comments [ 4 ]
share
No tags 0
4 Responses
  • Marcus
    25 . 01 . 2021

    How do you go around Parquet not accepting spaces in the column names? Are you able to affect that somehow in the pipeline prior the load to parquet?

    • David Alzamendi
      01 . 02 . 2021

      Hi Marcus,

      I wrote about this, thank you for your idea!
      https://www.techtalkcorner.com/parquet-files-column-names-with-spaces/

      You could use some dynamic SQL to get rid of the spaces when running the query on the source system by using aliases. Depending on your source system, you can generate the “select” statement in a previous activity like a lookup activity, and then pass the output of that activity to the Copy Activity to be used as the query for the source system.

      A good example of dynamic SQL with T-SQL.

      DECLARE @s VARCHAR(500)
      DECLARE @tablename VARCHAR(500) ='Store'

      SELECT @s = ISNULL(@s+', ','') + '[' + c.name + ']' + ' as ['+replace(c.name,' ','')+'] '
      FROM sys.all_columns c join sys.tables t
      ON c.object_id = t.object_id
      WHERE t.name = @tablename

      SELECT 'select ' + @s + ' from ' + @tablename

      Let me know if I can expand this answer, thank you for reading the blog post.

  • Rob
    29 . 01 . 2021

    Very helpful David. Thanks. I wonder if you can help – If you want to daily land all tables from an OLTP system into the lake, what are the pros and cons of each of the below structure
    1. Database Name – yyyy – yyyyMM – yyyyMMdd – Files
    2. Database Name – Table Name – Files with date suffix

    • David Alzamendi
      01 . 02 . 2021

      Hi Rob,

      Thank you for reading the article!

      Good question, each option could be valid depending on your scenario, but from my own experience I can highlight a few differences:

      Option 1:
      Pros
      • Easier to organise and navigate
      • A lower number of folders is always better, you know that there is 1 folder per day, so it is easier to consume. You know that if you want to query data from yesterday, you just go to 1 folder.
      • Easier to archive, just imagine that you want to archive 1 year of data, you just move the folder

      Cons
      • You could end up with a large number of files in a single folder
      • Granting access to specific tables is more challenging

      Option 2:
      Pros
      • Easier to grant and deny access to specific tables (folders)
      Cons:
      • You could end up with a really large number of folders, some OLTP systems have many tables, which will be harder to consume and navigate
      • More difficult to join the datasets when using data frames or synapse serverless.

      Regards,

      David

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *