Migrating tables from azure SQL database to Azure data lake

Question

I want to completely migrate all tables from azure SQL database to azure data lake. How can I do that. Please give some scenario so that I can move completely to azure data lake. Thanks in advance.

And also can i do this by SSIS?

Answer 1

If you don't want to schedule it, and interested in moving all of your tables in Azure Data Lake Store, then you can query your tables directly from U-SQL and output it through U-SQL Outputters in ADL Store.

Here is one tutorial to do this: http://eatcodelive.com/2015/11/21/querying-azure-sql-database-from-an-azure-data-lake-analytics-u-sql-script/

Answer 2

Take a look at Azure Data Factory. It's like SSIS in Azure. See https://azure.microsoft.com/en-us/documentation/articles/data-factory-introduction/

See https://azure.microsoft.com/en-us/documentation/articles/data-factory-azure-datalake-connector/ for a tutorial to move data to Azure Data Lake.

Answer 3

In addition to using Azure Data Factory and U-SQL, you can use also use sqoop running on HDI clusters - https://azure.microsoft.com/en-gb/documentation/articles/data-lake-store-data-transfer-sql-sqoop/.

You cannot use SSIS right now. We are working on enabling that support. When we have it enabled, we will update this thread as well as have appropriate documentation published.

Thanks, Sachin Sheth Program Manager, Azure Data Lake

Answer 4

You have a number of options, as indicated by the number of answers here, but it kind of depends on what format you want the data to end up in and what kind of workloads you want to do. Your current data is in a database meaning it is highly structured with strong data-typing, plus the option of indexing and relationships. You lose some or most of these if you move to another format so decide what format do you want to end up in? eg database table, flat file, external table, U-SQL table. U-SQL (IMHO) is best suited to analytics workloads where you want to mix relational data with non-relational or process non-relational data only, ie basically do things you can't do with ordinary T-SQL, using either c# expressions or custom outputters, processors etc at scale.

Let's work some of these approaches through:

1. Leave the data where it is in the Azure DB and use the ability of Azure Data Lake Analytics (ADLA) and U-SQL to "query data where it lives". You could even use federated tables (aka external tables) or create a view.

Pros: The data is not moved from the original database and can be joined with other data in the lake using U-SQL.

Cons: Additional code must be written

DROP VIEW IF EXISTS dbo.viewOnAzureDB;

CREATE VIEW dbo.viewOnAzureDB
AS
SELECT col1,
       col2,
       col3,
       col4
FROM EXTERNAL yourExternalDataSource LOCATION "dbo.yourTable";

2. Use Azure Data Factory to move the data from native table format in the Azure DB to flat file format in the Data Lake.

Pros: Straightforward to use Copy Wizard to copy data from Azure DB to flat file in the lake. Cons: You lose the strong data-typing, indexing and relationships you get from the database table.

3. U-SQL tables Create internal tables within Azure Data Lake using U-SQL.

Pros: U-SQL tables can be indexed and partitioned and have strong data-typing.

Cons: Additional code must be written, no relationships

// U-SQL CTAS
CREATE TABLE dbo.CTASOnAzureDBTable
(
    INDEX cdx_CTASOnAzureDBTable
    CLUSTERED(col1)
    DISTRIBUTED BY
    ROUND ROBIN
)
AS
SELECT *
FROM dbo.viewOnAzureDB;

// U-SQL table
CREATE TABLE IF NOT EXISTS dbo.CTASOnAzureDBTable2
(
    col1 int,
    col2 DateTime?, // ? means nullable,
    col3 string,
    col4 bool,

    INDEX cdx_CTASOnAzureDBTable2
    CLUSTERED(col1)
    DISTRIBUTED BY
    ROUND ROBIN
);

I hope that helps you think through some of the options, and maybe think about exactly why you are moving to data lake and what you want from it.