BULK INSERT statement is generally available!
The BULK INSERT statement is generally available in Fabric Data Warehouse. The BULK INSERT statement enables you to ingest parquet or csv data into a table from the specified file stored in Azure Data Lake or Azure Blob storage:
BULK INSERT table_name
FROM file_url_pathThe BULK INSERT statement is very similar to the COPY INTO statement and enables you to load data from external storage with the same data loading performance as the COPY INTO statement.
The key value of BULK INSERT is that it supports traditional SQL Server and Azure SQL syntax, thus facilitating an easy migration of SQL Server/Azure SQL code to the Fabric Data Warehouse without the need for code changes.
Additionally, BULK INSERT supports several traditional data loading options that are used in the traditional SQL Server databases, such as importing non-Unicode files and using text/xml format files. This compatibility ensures that you can migrate your SQL Server code to Fabric Data Warehouse with minimal changes to your ingestion code while retaining your existing ingestion logic without altering the input files.
To demonstrate these features, we will see how to load non-Unicode textual files in the following table:
CREATE TABLE players (name VARCHAR(50), surname VARCHAR(50),
sport VARCHAR(50), age SMALLINT)Import non-Unicode files
The BULK INSERT statement enables you to load the text files that are not encoded using UTF8/UTF16 encoding. You can import the files with various code pages by specifying the CODEPAGE option. Let’s imagine that we need to import the text files encoded as 855 code page that is used for various Cyrillic alphabets, such as Bulgarian or Serbian. The content of this file is shown in the example.
There are non-ASCII characters used for the names of Serbian players, but they are stored in codepage 855 instead of some Unicode encoding. You can easily load this file in Fabric Data Warehouse using the following BULK INSERT statement:
BULK INSERT players
FROM 'https://fabrictutorialdata.dfs.core.windows.net/sampledata/csv/codepages/cp855.txt'
WITH ( CODEPAGE = '855' )This way, you can load existing textual file formats that you used in SQL Server without the need to rewrite the files before ingestion.
Using format file
A format file is a crucial component when using the BULK INSERT statement to map the source data to table columns. Typically generated by the bcp (Bulk Copy Program) tool during export, the format file outlines the mapping rules between the source data and the table schema.
If you have both the source file and the corresponding format file, the BULK INSERT statement allows you to import data the same way you did in your SQL Server environment.
BULK INSERT players
FROM 'https://fabrictutorialdata.dfs.core.windows.net/sampledata/csv/codepages/cpBig5.txt'
WITH (
CODEPAGE = '950',
FIRSTROW = 2,
FORMATFILE = 'https://fabrictutorialdata.dfs.core.windows.net/sampledata/csv/codepages/players.xml'
)This way, you can retain the existing code in SQL Server that uses the BULK INSERT statements and utilize format files. This capability eliminates the need to rewrite these statements, thereby saving significant time and effort.
Conclusion
By leveraging the BULK INSERT statement, you can reuse your existing data ingestion code, ensuring a seamless transition to the Fabric Data Warehouse. This feature enhances the efficiency of the migration process, reducing the potential for errors and wrong results caused by the code rewrite. As a result, your solutions can continue to operate smoothly while taking advantage of the advanced capabilities and scalability offered by the Fabric Data Warehouse. Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community.
