How to bulkload Azure SQLDB from ADLS

user_11049287 0
Viewed 1595

I am aware that in ADF copy activity can be used to load data from ADLS to Azure SQL DB. Is there any possibility of bulk loading. For example, ADLS --> Synapse have to option of PolyBase for bulk loading.

Is there any efficient way to load huge number of records from ADLS to Azure SQL DB.

Thanks Madhan

azure-data-factory azure-sql-database azure
2 Answers

You can use either BULK INSERT or OPENROWSET to get data from blob storage into Azure SQL Database. A simple example with OPENROWSET:

SELECT * 
FROM OPENROWSET (
    BULK 'someFolder/somecsv.csv',
    DATA_SOURCE = 'yourDataSource',
    FORMAT = 'CSV',
    FORMATFILE = 'yourFormatFile.fmt',
    FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
    ) AS yourFile;

A simple example with BULK INSERT:

BULK INSERT yourTable
FROM 'someFolder/somecsv.csv'
WITH ( 
    DATA_SOURCE = 'yourDataSource',
    FORMAT = 'CSV'
    );

There is some setup to be done first, ie you have to use the CREATE EXTERNAL DATA SOURCE statement, but I find it a very effective way of getting data in Azure SQL DB without the overhead of setting up an ADF pipeline. It's especially good for ad hoc loads.

This article talks the steps through in more detail:

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-access-to-data-in-azure-blob-storage?view=sql-server-ver15

user_15275040

Data Factory has the good performance for big data transferring, ref: Copy performance and scalability achievable using ADF. You could follow this document to improve the copy performance for the huge number of records in ADLS. I think it may be better than BULK INSERT.

We can not use BULK INSERT (Transact-SQL) directly in Data Factory. But we can using bulk copy for ADLS to Azure SQL database. Data Factory gives us the tutorial and example.

Ref here: Bulk copy from files to database:

  • This article describes a solution template that you can use to copy data in bulk from Azure Data Lake Storage Gen2 to Azure Synapse Analytics / Azure SQL Database.

Hope it's helpful.

user_105492810
Related