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:
