Data ingestion is one of the first tasks data engineers go through before being able to leverage SQL analytics capabilities within Azure Synapse Analytics. We are thrilled to announce a new COPY statement which will enable you to seamlessly load data in a simple and flexible fashion without any compromise to performance.
Previously, the bulk loading experience with SQL DW using external tables was oftentimes cumbersome and error prone given the number of steps required to set up the process. Data engineers had to create and manage 5 separate database objects for high throughput data ingestion. In addition to this complex process, using external tables for data ingestion also presented a few limitations which include the lack of standard CSV parsing and requiring high privileged users to load data posing security concerns for many enterprise customers.
With the new COPY statement, loading limitations are removed so that you can immediately ingest data and quickly begin deriving insights from your data. The COPY statement enables you to seamlessly and flexibly load data by providing functionality to:
- Allow lower privileged users to load without needing strict CONTROL permissions on the data warehouse
- Leverage only a single T-SQL statement without having to create any additional database objects
- Leverage a finer permission model without exposing storage account keys using Share Access Signatures (SAS)
- Specify a different storage account for the ERRORFILE location (REJECTED_ROW_LOCATION)
- Customize default values for each target column and specify source data fields to load into specific target columns
- Specify a custom row terminator for CSV files
- Escape string, field, and row delimiters for CSV files
- Leverage SQL Server Date formats for CSV files
Here is the COPY statement in its simplest form:
COPY INTO dbo.[FactOnlineSales] FROM ‘https://contosoretaildw.blob.core.windows.net/contosoretaildw-tables/FactOnlineSales/’
The COPY statement is currently public preview - learn more here.
