如何从Synapse SQL无服务器读取Azure事件中心捕获的AVRO文件

方法1：用OPENROWSET直接读取AVRO文件 #

Synapse SQL无服务器原生支持通过OPENROWSET读取AVRO格式，无需提前创建外部表，直接写查询即可访问存储账户内的文件：

SELECT *
FROM OPENROWSET(
    BULK 'https://<你的存储账户名>.blob.core.windows.net/<容器名>/<Event Hubs捕获路径>/*.avro',
    FORMAT = 'AVRO'
) AS [result]

如果AVRO包含Event Hubs自带的嵌套结构（比如Body二进制字段、系统属性），可以针对性解析：

SELECT
    -- 解析Body（假设原始数据是JSON格式）
    JSON_VALUE(CAST(result.Body AS NVARCHAR(MAX)), '$.deviceId') AS deviceId,
    JSON_VALUE(CAST(result.Body AS NVARCHAR(MAX)), '$.temperature') AS temperature,
    -- 读取Event Hubs系统属性
    result.EnqueuedTimeUtc,
    result.SequenceNumber,
    result.PartitionId
FROM OPENROWSET(
    BULK 'https://<你的存储账户名>.blob.core.windows.net/<容器名>/<Event Hubs捕获路径>/*.avro',
    FORMAT = 'AVRO'
) AS [result]

方法2：创建外部表（CET）作为数据包装器 #

如果需要固定的表结构来统一访问，可按以下步骤创建外部表：

1. 创建指向存储账户的外部数据源

CREATE EXTERNAL DATA SOURCE EventHubsCaptureStorage
WITH (
    LOCATION = 'https://<你的存储账户名>.blob.core.windows.net/<容器名>',
    CREDENTIAL = <你的数据库范围凭据> -- 存储私有需配置，公开可省略
);

2. 创建AVRO格式的外部文件格式

CREATE EXTERNAL FILE FORMAT AvroCaptureFormat
WITH (
    FORMAT_TYPE = AVRO,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec' -- Event Hubs捕获默认用Snappy压缩
);

3. 创建外部表映射AVRO结构

CREATE EXTERNAL TABLE EventHubsCapturedData (
    Body VARBINARY(MAX),
    EnqueuedTimeUtc DATETIME2,
    SequenceNumber BIGINT,
    Offset VARCHAR(50),
    PartitionId VARCHAR(10),
    Properties NVARCHAR(MAX)
)
WITH (
    LOCATION = '<Event Hubs捕获路径>', -- 示例：'myeventhubnamespace/myhub/PartitionId=*/year=*/month=*/day=*/hour=*'
    DATA_SOURCE = EventHubsCaptureStorage,
    FILE_FORMAT = AvroCaptureFormat
);

之后即可像查询普通表一样访问：

SELECT
    JSON_VALUE(CAST(Body AS NVARCHAR(MAX)), '$.humidity') AS humidity,
    EnqueuedTimeUtc,
    PartitionId
FROM EventHubsCapturedData;

关键注意事项 #

• 给Synapse SQL无服务器工作区的MSI分配存储账户的Storage Blob Data Reader权限，确保访问权限正常。
• Event Hubs捕获的AVRO文件路径按{命名空间}/{事件中心}/{PartitionId}/{Year}/{Month}/{Day}/{Hour}/{Minute}分层，查询时可用通配符*匹配多时间分区或多分区数据。
• 若Body是非JSON的二进制数据，需根据实际格式用对应函数转换（比如CAST转字符串、自定义解析逻辑）。

内容的提问来源于stack exchange，提问作者Mauro Minella