Convert plain parquet files to Delta Lake format using Apache Spark in Azure Synapse Analytics

Published Jun 10 2020 12:18 PM 5,036 Views
Microsoft

Classic big data processing typically use read & append pattern. You can read immutable files and append new data as new file that you can add to the existing data sets. It is hard to modify existing data

Apache Delta format enables you to have big data sets that you can modify. Delta format is based on standard set of parquet files, but it keeps track about added and deleted file. If you need to modify data in one parquet file, Delta format will just record that file as invalidated and create new file with modified content that is included in data set.

 

If you have Apache Spark, you can easily convert your existing parquet files or set of files into delta format. Let’s imagine that we have a folder on Azure storage with one or more .parquet files, representing a file data set, as shown on the following picture:

JovanPop_4-1591814657980.png

 

 

Apache Spark enables you to modify this location and add metadata files that will convert this single parquet file to a set of files. Since Apache Spark is built-in into Azure Synapse Analytics, you can use Synapse Analytics Studio to make this conversion.

 

You can open Synapse Studio for Azure Synapse Analytics and create new Apache Spark notebook where you can convert this folder with parquet file to a folder with Delta format using the following PySpark code:

 

from delta.tables import *

deltaTable = DeltaTable.convertToDelta(spark, "parquet.`abfss://delta@deltaformatdemostorage.dfs.core.windows.net/tpch1gb/supplier`")

 

Conversion of plain parquet folder to Delta format is very quick because this command just creates some metadata files that describe locations of the files:

JovanPop_5-1591814657983.png

 

 

As a result, you will get the additional _delta_log file/folder in your Azure storage folder:

JovanPop_6-1591814657995.png

 

Once you complete the conversion you can create Delta table in Apache Spark for Azure Synapse using the command similar to the following Spark SQL example:

 

%%sql

CREATE TABLE supplier USING DELTA LOCATION 'abfss://delta@deltaformatdemostorage.dfs.core.windows.net/tpch1gb/supplier'

 

From this point, you can use Apache Spark to read, insert, update, and delete data from your supplier table. Example of Spark SQL query that reads data is

 

JovanPop_7-1591814658003.png

 

 

You can also update data in Delta format files by executing something like the following PySpark code:

 

from delta.tables import *

deltaTable = DeltaTable.forPath(spark, "delta@deltaformatdemostorage.dfs.core.windows.net/tpch1gb/supplier")

deltaTable.update("s_acctbal > 100", { "s_acctbal": "2*s_acctbal" } )

 

With a simple conversion you can convert your read/append only data set to file structure where you can easily update data.

Conclusion

Azure Synapse Analytics is limitless data analytics solution that enables you to use various engines such as Apache Spark or Synapse SQL to analyze and process files on Azure storage. In this article you have learned hot to leverage Apache Spark engine in Azure Synapse to make your read-only file sets fully updateable.

1 Comment
Occasional Visitor

Does the update to delta format table also updates the underlying record on the parquet file? 

%3CLINGO-SUB%20id%3D%22lingo-sub-1454662%22%20slang%3D%22en-US%22%3EConvert%20plain%20parquet%20files%20to%20Delta%20Lake%20format%20using%20Apache%20Spark%20in%20Azure%20Synapse%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1454662%22%20slang%3D%22en-US%22%3E%3CP%3EClassic%20big%20data%20processing%20typically%20use%20read%20%26amp%3B%20append%20pattern.%20You%20can%20read%20immutable%20files%20and%20append%20new%20data%20as%20new%20file%20that%20you%20can%20add%20to%20the%20existing%20data%20sets.%20It%20is%20hard%20to%20modify%20existing%20data%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdelta.io%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EApache%20Delta%20format%3C%2FA%3E%20enables%20you%20to%20have%20big%20data%20sets%20that%20you%20can%20modify.%20Delta%20format%20is%20based%20on%20standard%20set%20of%20%3CA%20href%3D%22https%3A%2F%2Fparquet.apache.org%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Eparquet%3C%2FA%3E%20files%2C%20but%20it%20keeps%20track%20about%20added%20and%20deleted%20file.%20If%20you%20need%20to%20modify%20data%20in%20one%20parquet%20file%2C%20Delta%20format%20will%20just%20record%20that%20file%20as%20invalidated%20and%20create%20new%20file%20with%20modified%20content%20that%20is%20included%20in%20data%20set.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Apache%20Spark%2C%20you%20can%20easily%20convert%20your%20existing%20parquet%20files%20or%20set%20of%20files%20into%20delta%20format.%20Let%E2%80%99s%20imagine%20that%20we%20have%20a%20folder%20on%20Azure%20storage%20with%20one%20or%20more%20%3CSTRONG%3E.parquet%3C%2FSTRONG%3E%20files%2C%20representing%20a%20file%20data%20set%2C%20as%20shown%20on%20the%20following%20picture%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_4-1591814657980.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197960iA308B3B9EBF4B4FB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22JovanPop_4-1591814657980.png%22%20alt%3D%22JovanPop_4-1591814657980.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EApache%20Spark%20enables%20you%20to%20modify%20this%20location%20and%20add%20metadata%20files%20that%20will%20convert%20this%20single%20parquet%20file%20to%20a%20set%20of%20files.%20Since%20Apache%20Spark%20is%20built-in%20into%20Azure%20Synapse%20Analytics%2C%20you%20can%20use%20Synapse%20Analytics%20Studio%20to%20make%20this%20conversion.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20open%20Synapse%20Studio%20for%20Azure%20Synapse%20Analytics%20and%20create%20new%20Apache%20Spark%20notebook%20where%20you%20can%20convert%20this%20folder%20with%20parquet%20file%20to%20a%20folder%20with%20Delta%20format%20using%20the%20following%20PySpark%20code%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Efrom%20delta.tables%20import%20*%0A%0AdeltaTable%20%3D%20DeltaTable.convertToDelta(spark%2C%20%22parquet.%60abfss%3A%2F%2Fdelta%40deltaformatdemostorage.dfs.core.windows.net%2Ftpch1gb%2Fsupplier%60%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConversion%20of%20plain%20parquet%20folder%20to%20Delta%20format%20is%20very%20quick%20because%20this%20command%20just%20creates%20some%20metadata%20files%20that%20describe%20locations%20of%20the%20files%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_5-1591814657983.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197961i080318BE05C2A162%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22JovanPop_5-1591814657983.png%22%20alt%3D%22JovanPop_5-1591814657983.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20result%2C%20you%20will%20get%20the%20additional%20_delta_log%20file%2Ffolder%20in%20your%20Azure%20storage%20folder%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_6-1591814657995.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197962i863BE75E02F15E2D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22JovanPop_6-1591814657995.png%22%20alt%3D%22JovanPop_6-1591814657995.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20you%20complete%20the%20conversion%20you%20can%20create%20Delta%20table%20in%20Apache%20Spark%20for%20Azure%20Synapse%20using%20the%20command%20similar%20to%20the%20following%20Spark%20SQL%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%25%25sql%0A%0ACREATE%26nbsp%3BTABLE%26nbsp%3Bsupplier%26nbsp%3BUSING%26nbsp%3BDELTA%26nbsp%3BLOCATION%26nbsp%3B'abfss%3A%2F%2Fdelta%40deltaformatdemostorage.dfs.core.windows.net%2Ftpch1gb%2Fsupplier'%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20this%20point%2C%20you%20can%20use%20Apache%20Spark%20to%20read%2C%20insert%2C%20update%2C%20and%20delete%20data%20from%20your%20supplier%20table.%20Example%20of%20Spark%20SQL%20query%20that%20reads%20data%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_7-1591814658003.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197963i806CA20036BA3CDD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22JovanPop_7-1591814658003.png%22%20alt%3D%22JovanPop_7-1591814658003.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20update%20data%20in%20Delta%20format%20files%20by%20executing%20something%20like%20the%20following%20PySpark%20code%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Efrom%20delta.tables%20import%20*%0A%0AdeltaTable%20%3D%20DeltaTable.forPath(spark%2C%20%22delta%40deltaformatdemostorage.dfs.core.windows.net%2Ftpch1gb%2Fsupplier%22)%0A%0AdeltaTable.update(%22s_acctbal%20%26gt%3B%20100%22%2C%20%7B%20%22s_acctbal%22%3A%20%222*s_acctbal%22%20%7D%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20a%20simple%20conversion%20you%20can%20convert%20your%20read%2Fappend%20only%20data%20set%20to%20file%20structure%20where%20you%20can%20easily%20update%20data.%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1172179729%22%20id%3D%22toc-hId-1172179758%22%3EConclusion%3C%2FH3%3E%0A%3CP%3EAzure%20Synapse%20Analytics%20is%20limitless%20data%20analytics%20solution%20that%20enables%20you%20to%20use%20various%20engines%20such%20as%20Apache%20Spark%20or%20Synapse%20SQL%20to%20analyze%20and%20process%20files%20on%20Azure%20storage.%20In%20this%20article%20you%20have%20learned%20hot%20to%20leverage%20Apache%20Spark%20engine%20in%20Azure%20Synapse%20to%20make%20your%20read-only%20file%20sets%20fully%20updateable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1454662%22%20slang%3D%22en-US%22%3E%3CP%3EApache%20Spark%20in%20Azure%20Synapse%20Analytics%20service%20enables%20you%20to%20easily%20convert%20your%20parquet%20folders%20to%20Delta%20Lake%20format%20that%20enables%20you%20to%20update%20and%20delete%20the%20records%20in%20the%20file%20sets.%20Here%20you%20will%20see%20how%20easily%20you%20can%20use%20Synapse%20Studio%20and%20Apache%20Spark%20to%20generate%20Delta%20Lake%20format%20file%20structure.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1454662%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20Spark%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Studio%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jun 10 2020 12:18 PM
Updated by: