Understanding the COPY INTO Statement in Azure Synapse Analytics

Azure Synapse Analytics, formerly known as Azure SQL Data Warehouse, is a cloud-based analytics service that allows users to analyze and transform data across multiple sources. One of the most powerful features of Azure Synapse Analytics is its ability to copy data from various sources into a target location using the COPY INTO statement.

In this article, we will delve into the world of Azure Synapse Analytics and explore one of the most common issues users face when using the COPY INTO statement. Specifically, we’ll examine why the COPY INTO statement might not be doing anything when copying data from an Azure Data Lake Gen2 file to a table in Azure Synapse Warehouse.

Understanding the COPY INTO Statement

The COPY INTO statement is used to copy data from a source location into a target location. The syntax of the statement looks like this:

COPY INTO <table_name> 
FROM '<source_file_path>' 
WITH (
    FILE_TYPE = 'csv',
    CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET=''),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='\r\n',
    ENCODING = 'UTF8',
    FIRSTROW = 2
)

As we can see, the COPY INTO statement has several optional parameters that control how the data is copied. In this example, we’ll focus on the parameters that might be causing issues.

Understanding Unix-style and Windows-style Line Endings

When working with files, it’s essential to understand the difference between Unix-style and Windows-style line endings. Unix-style line endings are represented by a single character (\n), while Windows-style line endings are represented by two characters (\r\n).

In many operating systems, including Linux and macOS, files have Unix-style line endings. However, on Windows, files often have Windows-style line endings.

Verifying Line Endings with vi

To verify if your file has Unix-style or Windows-style line endings, you can use the vi editor in binary mode. Here’s how:

vi -b -c 'set list' <file>

This will open the file in vi with the list option enabled, which highlights each character on a new line.

Checking Line Endings with the COPY INTO Statement

To check if your file has Unix-style or Windows-style line endings using the COPY INTO statement, you can modify the ROWTERMINATOR parameter to specify the line ending style. Here are two possible ways to do this:

1. Specify Unix-style Line Endings: To confirm that it’s actually reading the file by making it parse the header, remove the FIRSTROW = 2 option and then upload the file to the Data Lake and try COPY INTO again, without ROWTERMINATOR='\r\n'. This will ensure that the engine uses Unix-style line endings (\n) as the terminator.

   COPY INTO dbo.SALES_CUTOMER_D 
   FROM 'https://acoount_name/test-file-system/SALES_CUSTOMER_D_0.csv'
   WITH (
       FILE_TYPE = 'csv',
       CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET=''),
       FIELDQUOTE = '"',
       FIELDTERMINATOR=',',
       ROWTERMINATOR = '\n',  // Specify Unix-style line endings
       ENCODING = 'UTF8',
       FIRSTROW = NULL
   )
   

2. Specify Windows-style Line Endings: To see if the issue is caused by Unix-style line endings, you can tell COPY what line endings are in your file by specifying ROWTERMINATOR='0x0A'. This will ensure that the engine uses Windows-style line endings (\r\n) as the terminator.

   COPY INTO dbo.SALES_CUTOMER_D 
   FROM 'https://acoount_name/test-file-system/SALES_CUSTOMER_D_0.csv'
   WITH (
       FILE_TYPE = 'csv',
       CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET=''),
       FIELDQUOTE = '"',
       FIELDTERMINATOR=',',
       ROWTERMINATOR = '\r\n',  // Specify Windows-style line endings
       ENCODING = 'UTF8',
       FIRSTROW = NULL
   )
   

3. Convert to Windows-style Line Endings: If your file has Unix-style line endings, you can convert it to Windows-style line endings using the unix2dos command.

   unix2dos <csv_file>
   

   This will convert all Unix-style line endings (\n) to Windows-style line endings (\r\n). Then, upload the file to the Data Lake and try COPY INTO again without ROWTERMINATOR='\r\n'.

Conclusion

In this article, we’ve explored one of the most common issues users face when using the COPY INTO statement in Azure Synapse Analytics. We’ve examined why the COPY INTO statement might not be doing anything and how to troubleshoot the issue.

By verifying the line endings of your file and adjusting the ROWTERMINATOR parameter accordingly, you can ensure that your data is copied correctly into Azure Synapse Warehouse.

Related Articles

• Understanding Azure Data Lake Gen2
• A Little Gotcha: Understanding RowTerminator in Copy Into Statement
• How to Convert Unix-style Line Endings to Windows-style Line Endings

Last modified on 2023-11-08