Easily load complex data types with Azure Synapse Analytics

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

The COPY statement provides the most flexible data ingestion experience for data engineers. In the latest release of Azure Synapse Analytics, we have enhanced the COPY command for Synapse SQL by enabling you to directly load complex data types from Parquet files such as Maps and Lists into string columns without using other tools to pre-process the data.


In addition, the command is even simpler supporting auto-schema and compression detection when loading Parquet files. The command will automatically detect the Parquet file schema and create the table prior to the load. 


Simply run the following statements to immediately ingest the following parquet files with complex data types. As you can see, you no longer need to pre-create any SQL tables or inspect the schema of the Parquet files:


--Load the map data type COPY INTO [dbo].[complextypes_map] FROM 'https://loadingsamples.blob.core.windows.net/complextypes/mapExample.parquet' WITH ( FILE_TYPE = 'PARQUET' ); SELECT * from [complextypes_map]; --Load the struct data type COPY INTO [dbo].[complextypes_struct] FROM 'https://loadingsamples.blob.core.windows.net/complextypes/structExample.parquet' WITH ( FILE_TYPE = 'PARQUET' ); SELECT * from [complextypes_struct]; --Load the array data type COPY INTO [dbo].[complextypes_array] FROM 'https://loadingsamples.blob.core.windows.net/complextypes/justSimpleArray.parquet' WITH ( FILE_TYPE = 'PARQUET' ); SELECT * from [complextypes_array]; --Clean up the SQL tables DROP table [complextypes_map]; DROP table [complextypes_array]; DROP table [complextypes_struct];


This capability is currently in preview. Reach out to the following email distribution list to get this feature enabled: sqldwcopypreview@service.microsoft.com.


Visit the following documentation for comprehensive examples and quickstarts using the COPY statement:



REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.