none
Transfer data from U-SQL managed table to Azure SQL Database table

    Question

  • I have a U-SQL managed table that contains schematized structured data.

    CREATE TABLE [AdlaDb].[dbo].[User]
    (
        UserGuid Guid,
        Postcode string,
        Age int?
        DateOfBirth DateTime?,
    )
    

    And a Azure SQL Database table.

    CREATE TABLE [SqlDb].[dbo].[User]
    (
        UserGuid    uniqueidentifier NOT NULL,
        Postcode    varchar(15) NULL,
        Age         int NULL,
        DateOfBirth Date NULL,
    )
    

    I would like to transfer data from U-SQL managed table to Azure SQLDB table without losing the data types.

    I'm using azure data factory, seems like I cannot

    1. directly query the U-SQL managed table as an input dataset for data factory
    2. do a federated write query to Azure SQLDB

    Hence I'm having an intermediate step where I copy from U-SQL managed table to Azure Blob and then move to Azure SQLDB table. Doing this, I'm losing the data type and having to have type conversion/transformations later again before inserting.

    Is there any better way to transfer data from U-SQL managed table to Azure SQL Database table without losing data type? Or am I missing something?


    Subash.S

    Wednesday, November 30, 2016 5:05 PM

All replies

  • Right now the only way to move the data is through some form of orchestration that dumps the U-SQL table into a format that you then can load into your SQL table using ADF.

    Michael Rys

    Friday, December 16, 2016 7:44 PM
    Moderator
  • Hi Michael, Can we load a Azure SQL DW table through U-SQL? I have my source data in ADL. We need to put some transformations there and then part of the data in Azure SQL DB and rest in Azure SQL DW. So can we leverage U-SQL for the whole transformation and then loading it in Azure SQL DW and Azure SQL DB tables?

    Thursday, February 16, 2017 12:41 PM
  • You could create a U-SQL script that performs the transformation and creates two datasets (csv files or similar, not ADLA tables), one for Azure SQL DB and one for Azure SQL DW. Then Data Factory can copy these datasets to the databases. Data Factory can schedule the whole process for you, including loading of source data into ADL.
    Friday, February 17, 2017 8:42 AM