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

directly query the U-SQL managed table as an input dataset for data factory
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?

score 3 · Answer 1 · answered Nov 30 '16 at 19:24

At this point you have the following option:

Export the U-SQL table into an intermediate format (e.g., CSV) in ADLS or blob storage.
Use ADF to move the file into Azure SQL DB.

I know that the ADF team has a work item to do this for you. I will ask them to reply to this thread as well.

Directly writing into a table from a U-SQL script has a lot of challenges due to the fault-tolerant retry and scale-out processing in U-SQL. This makes atomic writing in parallel into a transacted store a bit more complex (see for example http://www.vldb.org/conf/1996/P460.PDF).

Has being able to pull data from U-SQL managed tables from Data Factory been implemented yet? I would like to iterate over the rows in a U-SQL table but cannot seem to do this without first exporting to a CSV. — iamdave, Oct 16 '18 at 14:52

score 2 · Answer 2 · edited May 23 '17 at 12:10

There is now another option to transfer data from USQL managed table to Azure SQL Database table.

Write out the data from USQL Managed table or from a USQL script to Azure Blob Storage as a text file (.csv, .txt etc..)
And then make use of the public preview feature in Azure SQL Database - BULK INSERT - wrap this into a stored procedure
Add an Stored procedure activity in Azure Data Factory to schedule

Note: There is one thing to be aware of when creating DATABASE SCOPED CREDENTIAL, refer this Stack Overflow question

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

2 Answers2

Linked