将数据从U-SQL托管表传输到Azure SQL数据库表 [英] Transfer data from U-SQL managed table to Azure SQL Database table

问题描述

我有一个U-SQL托管表，其中包含已模式化的结构化数据.

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?,
)

还有一个Azure SQL数据库表.

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,
)

我想在不丢失数据类型的情况下将数据从U-SQL托管表传输到Azure SQLDB表.

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

我正在使用azure数据工厂，看来我不能

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

1. 直接查询U-SQL托管表作为数据工厂的输入数据集
2. 对Azure SQLDB进行联合 write 查询

因此，我要执行一个中间步骤，即从U-SQL托管表复制到Azure Blob，然后再移动到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.

是否有更好的方法将数据从U-SQL托管表传输到Azure SQL数据库表而又不会丢失数据类型?还是我错过了什么?

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?

推荐答案

目前，您可以使用以下选项:

At this point you have the following option:

1. 将U-SQL表导出为ADLS或Blob存储中的中间格式(例如CSV).
2. 使用ADF将文件移至Azure SQL DB.

我知道ADF团队有一个工作项目可以为您完成这项工作.我也会请他们回复此主题.

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.

由于U-SQL中的容错重试和横向扩展处理，从U-SQL脚本直接写入表存在很多挑战.这使得将原子并行写入事务存储中变得更加复杂(例如，请参见 http://www.vldb.org/conf/1996/P460.PDF ).

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).

这篇关于将数据从U-SQL托管表传输到Azure SQL数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！