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: firstname.lastname@example.org.
Visit the following documentation for comprehensive examples and quickstarts using the COPY statement:
- Quickstart: Bulk load data using the COPY statement
- Quickstart: Examples using the COPY statement and its supported authentication methods
- Quickstart: Creating the COPY statement using the rich Synapse Studio UI (Workspace preview)