none
U-SQL Job to insert data in SQL DW

    Question

  • Hello -

    I have a U-SQL job and want to insert data from a data lake store file into SQL DW. I did tried storing data into Data lake database catalog and that works fine but is there a way I can have my data factory load data from this data lake DB into SQL DW?

    Thanks,

    Shabbir


    Message from MS TechNet

    Wednesday, March 22, 2017 9:49 PM

All replies

  • We use Polybase to import data from Azure Storage Blobs to ASDW.

    https://msdn.microsoft.com/en-us/library/mt143171.aspx

    We call a stored procedure on the ASDW from ADF.

    The stored procedure:

    1. Creates the External Table
    2. Imports the data using CTAS from the External Table
    Thursday, March 23, 2017 12:14 AM
  • Hi,

    You can use Azure Data Factory (ADF) to load data from Azure Data Lake Store (ADLS) into Azure SQL Data Warehouse (SQL DW).  

    https://docs.microsoft.com/en-us/azure/data-factory/data-factory-azure-datalake-connector

    In fact, they have just announced auto table creation in SQL DW when you do this:

    Automatic table schema creation when loading into SQL Data Warehouse

    Introduction: When copying data from On-Premise SQL Server or Azure SQL Database to Azure SQL Data Warehouse using the Copy Wizard, if the table does not exist in the destination SQL Data Warehouse, Azure Data Factory can now automatically create the destination table using schema from source.

    Configuration: From the Copy Wizard, in the Table mapping page, you now have the option to map to existing sink tables or create new ones using source tables’ schema. Proper data type conversion may happen if needed to fix the incompatibility between source and destination stores. Users will be warned in the Schema mapping page, as shown in the second image below, about potential incompatibility issues. Learn more about auto table creation here - 

    https://docs.microsoft.com/en-us/azure/data-factory/data-factory-azure-sql-data-warehouse-connector#auto-table-creation

    Thursday, March 23, 2017 5:58 PM