提问人：Jean-Christophe 提问时间：8/25/2023 最后编辑：AswinJean-Christophe 更新时间：8/26/2023 访问量：414
Azure Synapse >无服务器 SQL > UPSERT parquet 文件可以吗？
Azure Synapse > Serverless SQL > UPSERT parquet files is possible?
问：
我正在使用 Azure Synapse 无服务器 SQL 数据库。
我有一个使用 Azure SQL 表作为源和 Azure 存储 gen2 的第一个复制活动，其中我将 .parquet 文件存储为接收器。 从这些 .parquet 文件中，我使用 CETAS 在我的无服务器 SQL DB 中创建外部表（对于上下文：我正在使用多个 Azure SQL 数据库，因此这些外部表将允许我编写跨数据库查询）。换句话说，这个无服务器 SQL 数据库是我的 ODS 数据库。
然后，我有第二个复制活动，用于识别源表中的增量更改（使用源数据库的相应 CHANGETABLE 的SYS_CHANGE_VERSION）。第二个复制活动还会输出 .parquet 文件。
最后，我有 2 个 parquet 文件：1 个包含源表的完整内容 + 1 个包含要插入或更新的内容。无服务器 SQL 数据库中的外部表只是元数据，因此无法对它们执行 DML 操作，所以我的问题是：有没有办法将我的 2 个镶木地板文件“合并”为 1 个文件（当然没有重复），我可以用它来重新创建更新的外部表？
或者，我看到我可以在复制活动接收器中选择复制方法“Upsert”并提供 KeyColumn（我的表的 PK），但它不起作用，说：“Message=INSERT 操作不允许用于这种类型的表。（这看起来很正常，因为关联的接收器数据集指向我的外部表，这是只读的）
复制活动：
关于如何解决这个问题的任何想法？ 谢谢！
答：
0赞
Aswin
8/26/2023
#1
由于 Azure Synapse 无服务器 SQL 数据库中的外部表是只读的，因此不能使用 copy 方法直接更新外部表。Upsert
- 如果完全加载文件和增量文件位于 ADLS 中的同一文件夹中，则在创建外部表时，可以提供 filename 代替。
**
示例外部表脚本：
CREATE EXTERNAL TABLE [dbo].[external_table] (
[PK] nvarchar(4000),
[name] nvarchar(4000),
[ingestion_time] nvarchar(4000)
)
WITH (
LOCATION = '<folder-name>/**',
DATA_SOURCE = <datasource-name>,
FILE_FORMAT = <fileformatname>
)
此脚本将确保合并该文件夹下的所有数据。
- 当初始版本中加载的旧数据被新数据替换时，只需获取最新记录。
例 File1 数据：
PK,name,ingestion_time
1,Karikala,2023-05-01
4,Kalyani,2023-05-01
7,Sindhu,2023-05-01
File2 数据：
PK,name,ingestion_time
1,Aadhi,2023-06-01
在这里，File2 数据具有 PK=1 的更新记录。执行查询时，将显示所有四条记录。因此，在此外部表的顶部创建一个视图，以仅显示每个主键的最新记录。select * from external_table
示例查询：
with cte as(
SELECT *,RANK() over (partition by PK order by [ingestion_time] desc) as Rank FROM [dbo].[external_table])
select PK,name,[ingestion_time] from cte where rank=1
| PK型 | 名字 | ingestion_time |
|---|---|---|
| 1 | 阿迪 | 2023-06-01 |
| 2 | 卡利亚尼 | 2023-05-01 |
| 3 | 信德 | 2023-05-01 |
此查询返回 中每个值具有最高值的行。ingestion_timePKexternal_table
评论
0赞
Jean-Christophe
8/28/2023
非常感谢@Aswin的详细回答！我宁愿避免使用这种解决方案，因为： 1/ 我有 53 个表要跟踪更改（这意味着要在我的 ADLS 中创建 53 个单独的子文件夹） 2/ 这意味着要创建 53 个不同的复制活动（不可能的全局通配符路径） 3/ 每次运行时都需要删除并重新创建 53 个外部表 4/ 每次运行时还需要删除并重新创建 53 个视图
0赞
Jean-Christophe
8/28/2023
但是，我没有想到在外部表中添加“引入时间”列，然后使用视图来过滤最近的行，这是非常聪明的！我将看看我是否无法通过对 parquet 文件使用特定的命名约定来解决 1/ 和 2/，并设置如下所示的通配符路径： LOCATION = '<global-folder-name>/*_my-table-name.parquet' 不确定这是否适用于创建 53 个单独的外部表
0赞
Aswin
8/28/2023
1/ 我有 53 个表要跟踪更改（这意味着要在我的 ADLS 中创建 53 个单独的子文件夹） 2/ 这意味着要创建 53 个不同的复制活动（不可能的全局通配符路径） -- 您可以使用 for-each 活动以 parquet 格式将 53 个表从源复制到 ADLS。是的，您需要为所有 53 个表手动创建外部表脚本。我想，你在方法上也在做同样的事情
1赞
Jean-Christophe
9/1/2023
实际上，这是一个“很高兴拥有”。我的队友将使用创建的视图来运行其他临时查询，他们可能会对列顺序感到失望，因为他们已经熟悉源表。别担心，我会继续寻找，或者让它原封不动:)谢谢！
1赞
Jean-Christophe
9/1/2023
嗨，@Aswin，只是为了让您知道，我是通过在创建外部表时直接对重复行进行排名/过滤来做到这一点的。这样可以避免创建视图，并保留列顺序！再次感谢您的大力支持！
评论