pg_azure_storage extension

项目
04/12/2024

适用对象：PostgreSQL 的 Azure Cosmos DB （由 PostgreSQL 的 Citus 数据库扩展提供支持）

pg_azure_storage 扩展允许将多种文件格式的数据直接从 Azure Blob 存储加载到 Azure Cosmos DB for PostgreSQL 群集。启用扩展还会解锁 COPY 命令的新功能。访问级别为“专用”或“Blob”的容器需要添加专用的访问密钥。

可运行以下命令来创建扩展：

SELECT create_extension('azure_storage');

azure_storage.account_add

函数允许添加对存储帐户的访问权限。

azure_storage.account_add
        (account_name_p text
        ,account_key_p text);

参数

account_name_p

Azure Blob 存储 (ABS) 帐户包含所有 ABS 对象：Blob、文件、队列和表。存储帐户为你的 ABS 提供了一个唯一的命名空间，可以从中国的任何位置通过 HTTPS 访问该命名空间。

account_key_p

Azure Blob 存储 (ABS) 访问密钥类似于存储帐户的根密码。始终要小心保护访问密钥。使用 Azure 密钥保管库安全地管理和轮换密钥。帐户密钥存储在可供 postgres 超级用户、azure_storage_admin 以及授予这些管理员权限的所有角色访问的表中。若要查看存在哪些存储帐户，请使用函数 account_list。

azure_storage.account_remove

函数允许撤销对存储帐户的帐户访问权限。

azure_storage.account_remove
        (account_name_p text);

参数

account_name_p

azure_storage.account_user_add

函数允许将角色的访问权限添加到存储帐户。

azure_storage.account_user_add
        ( account_name_p text
        , user_p regrole);

参数

account_name_p

user_p

由群集上可见的用户创建的角色。

注意

account_user_add、account_add、account_remove、account_user_remove 函数要求为群集中的每个单独节点设置权限。

azure_storage.account_user_remove

函数允许将角色的访问权限从存储帐户中移除。

azure_storage.account_user_remove
        (account_name_p text
        ,user_p regrole);

参数

account_name_p

user_p

由群集上可见的用户创建的角色。

azure_storage.account_list

该函数列出有权访问 Azure Blob 存储的帐户和角色。

azure_storage.account_list
        (OUT account_name text
        ,OUT allowed_users regrole[]
        )
Returns TABLE;

自变量

account_name

allowed_users

列出有权访问 Azure Blob 存储的用户。

返回类型

TABLE

azure_storage.blob_list

函数列出用户容器中可用的 Blob 文件及其属性。

azure_storage.blob_list
        (account_name text
        ,container_name text
        ,prefix text DEFAULT ''::text
        ,OUT path text
        ,OUT bytes bigint
        ,OUT last_modified timestamp with time zone
        ,OUT etag text
        ,OUT content_type text
        ,OUT content_encoding text
        ,OUT content_hash text
        )
Returns SETOF record;

参数

account_name

storage account name 为你的 Azure 存储数据提供了一个唯一的命名空间，可以从中国的任何位置通过 HTTPS 访问该命名空间。

container_name

容器对一组 blob 进行组织，类似于文件系统中的目录。一个存储帐户可以包含无限数量的容器，一个容器可以存储无限数量的 Blob。容器名称必须是有效的 DNS 名称，因为它是用于对容器或其 Blob 寻址的唯一 URI 的一部分。为容器命名时，请遵循以下规则：

容器名称的长度可以是 3 到 63 个字符。
容器名称必须以字母或数字开头，并且只能包含小写字母、数字和短划线 (-) 字符。
容器名称中不允许出现两个或更多个连续的短划线字符。

容器的 URI 类似于：https://myaccount.blob.core.chinacloudapi.cn/mycontainer

前缀

从 Blob 容器返回具有匹配字符串缩写的文件。

path

Azure Blob 目录的完全限定路径。

字节

文件对象的大小（以字节为单位）。

last_modified

上次修改文件内容是何时。

etag

ETag 属性用于更新期间的乐观并发。它不是时间戳，因为有另一个名为 Timestamp 的属性用于存储上次更新记录的时间。例如，如果加载实体并想要更新它，则 ETag 必须与当前存储的内容匹配。设置适当的 ETag 非常重要，因为如果有多个用户正在编辑同一项，你希望他们不会覆盖彼此所做的更改。

content_type

Blob 对象表示一个 blob，它是一个包含不可变的原始数据的类似文件的对象。它们可以读取为文本或二进制数据，也可以转换为 ReadableStream，以便使用其方法处理数据。 Blob 可以表示不一定采用 JavaScript 本机格式的数据。

content_encoding

Azure 存储允许在 Blob 上定义 Content-Encoding 属性。对于压缩内容，可以将该属性设置为 GZIP。浏览器访问内容时，会自动解压缩内容。

content_hash

此哈希值用于验证传输期间 Blob 的完整性。指定此标头时，存储服务会对已到达的哈希值与所发送的哈希值进行比较。如果这两个哈希值不匹配，操作会失败，并显示错误代码 400（错误请求）。

返回类型

SETOF 记录

注意

权限现在，你可以为该存储列出设置为“专用”和“Blob”访问级别的容器，但只能被列为授予了citus user角色的azure_storage_admin。如果创建一个名为 support 的新用户，则默认情况下不允许访问容器内容。

azure_storage.blob_get

函数允许从容器中加载文件内容，并添加了在导入之前对数据的筛选或操作的支持。

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF record;

函数有一个重载版本，其中包含可让你方便地定义输出格式记录的 rec 参数。

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,rec anyelement
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF anyelement;

参数

帐户

存储帐户为你的 Azure 存储数据提供了一个唯一的命名空间，可以从中国的任何位置通过 HTTPS 访问该命名空间。

容器

容器对一组 blob 进行组织，类似于文件系统中的目录。一个存储帐户可以包含无限数量的容器，一个容器可以存储无限数量的 Blob。容器名称必须是有效的 DNS 名称，因为它是用于对容器或其 Blob 寻址的唯一 URI 的一部分。

path

容器中存在的 Blob 名称。

rec

定义记录输出结构。

解码器

指定 Blob 格式解码器可设置为自动 (默认) 或以下任何值

解码器说明

格式	说明
csv	PostgreSQL COPY 使用的逗号分隔值格式
tsv	制表符分隔值，默认 PostgreSQL COPY 格式
binary	二进制 PostgreSQL COPY 格式
text	包含单个文本值的文件（例如大型 JSON 或 XML）

compression

定义压缩格式。可用选项包括 auto、gzip 和 none。使用 auto 选项 (默认) ，根据文件扩展名 (.gz == gzip) 猜测压缩。选项 none 强制忽略扩展，而不尝试解码。另一方面，gzip 强制使用 gzip 解码器 (当拥有包含非标准扩展的 gzip 文件时) 。我们目前不支持该扩展的任何其他压缩格式。

选项

为了处理自定义标头、自定义分隔符、转义字符等，options的工作方式与COPY PostgreSQL 中的命令类似，参数利用 Blob_get 函数。

返回类型

SETOF 记录/任何元素

注意

有四个实用工具函数，在 blob_get 中作为参数调用，可帮助为其生成值。每个实用工具函数都为与其名称匹配的解码器指定。

azure_storage.options_csv_get

该函数充当实用工具函数，作为 blob_get 中的参数，这对于解码 csv 内容很有用。

azure_storage.options_csv_get
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

参数

delimiter

指定分隔文件的每行各列的字符。默认为文本格式的制表符，CSV 格式的逗号。它必须是单个单字节字符。

null_string

指定表示 null 值的字符串。默认值为文本格式的 \N (反斜杠-N)，CSV 格式为未加引号的空字符串。对于不希望将 null 与空字符串区分开来的情况，即使文本格式也首选空字符串。

指定文件包含一个标题行，其中包含文件中每一列的名称。输出时，第一行包含表中的列名。

引号

指定引用数据值时要使用的引号字符。默认为双引号。它必须是单个单字节字符。

转义符

指定在与“引号”值匹配的数据字符之前应显示的字符。默认值与“引号”值相同 (因此，如果引号字符出现在数据中，则将使用双引号)。它必须是单个单字节字符。

force_not_null

不要将指定列的值与 null 字符串匹配。在 null 字符串为空的默认情况下，这意味着空值将读取为零长度字符串而不是 null，即使它们未用引号括起来。

force_null

将指定列的值与 null 字符串匹配（即使已加引号），如果找到匹配项，则将该值设置为 NULL。在 null 字符串为空的默认情况下，它会将带引号的空字符串转换为 NULL。

content_encoding

指定在 encoding_name 中对文件进行编码。如果省略该选项，则使用当前客户端编码。

返回类型

jsonb

azure_storage.options_copy

该函数充当实用工具函数，作为 blob_get 中的参数调用。

azure_storage.options_copy
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_quote text[] DEFAULT NULL::text[]
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

参数

delimiter

指定分隔文件的每行各列的字符。默认为文本格式的制表符，CSV 格式的逗号。它必须是单个单字节字符。

null_string

标头的值开始缓存响应

指定文件包含一个标题行，其中包含文件中每一列的名称。输出时，第一行包含表中的列名。

引号

指定引用数据值时要使用的引号字符。默认为双引号。它必须是单个单字节字符。

转义符

force_quote

强制对每个指定列中的所有非 NULL 值使用引号。始终不对 NULL 输出使用引号。如果指定了 *，则所有列中的非 NULL 值都使用引号。

force_not_null

不要将指定列的值与 null 字符串匹配。在 null 字符串为空的默认情况下，这意味着空值将读取为零长度字符串而不是 null，即使它们未用引号括起来。

force_null

content_encoding

指定在 encoding_name 中对文件进行编码。如果省略该选项，则使用当前客户端编码。

返回类型

jsonb

azure_storage.options_tsv

该函数充当实用工具函数，作为 blob_get 中的参数调用。它可用于解码 tsv 内容。

azure_storage.options_tsv
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

参数

delimiter

指定分隔文件的每行各列的字符。默认为文本格式的制表符，CSV 格式的逗号。它必须是单个单字节字符。

null_string

content_encoding

指定在 encoding_name 中对文件进行编码。如果省略该选项，则使用当前客户端编码。

返回类型

jsonb

azure_storage.options_binary

该函数充当实用工具函数，作为 blob_get 中的参数调用。它可用于解码二进制内容。

azure_storage.options_binary
        (content_encoding text DEFAULT NULL::text)
Returns jsonb;

参数

content_encoding

指定在 encoding_name 中对文件进行编码。如果省略该选项，则使用当前客户端编码。

返回类型

jsonb

注意

权限现在，你可以为该存储列出设置为“专用”和“Blob”访问级别的容器，但只能被列为授予了citus user角色的azure_storage_admin。如果创建了新的用户实名支持，则默认无法访问容器内容。

示例

使用的示例使用示例 Azure 存储帐户 (pgquickstart) 和上传的自定义文件，以添加到不同用例的覆盖范围。我们可以首先创建在所用示例集中使用的表。

CREATE TABLE IF NOT EXISTS public.events
        (
         event_id bigint
        ,event_type text
        ,event_public boolean
        ,repo_id bigint
        ,payload jsonb
        ,repo jsonb
        ,user_id bigint
        ,org jsonb
        ,created_at timestamp without time zone
        );

添加存储帐户的访问密钥 (访问级别 = 专用时必须添加)

此示例演示如何为存储帐户添加访问密钥，以便从 Azure Cosmos DB for Postgres 群集上的会话获取查询访问权限。

SELECT azure_storage.account_add('pgquickstart', 'SECRET_ACCESS_KEY');

提示

在存储帐户中，打开“访问密钥”。复制存储帐户名称，然后从“key1”部分复制密钥（必须先选择密钥旁边的“显示”）。

Azure 门户中 Azure Blob 存储页的“安全 + 网络”>“访问密钥”部分的屏幕截图。

删除存储帐户的访问密钥

此示例演示如何删除存储帐户的访问密钥。此操作将导致删除对容器中专用存储桶中托管的文件的访问权限。

SELECT azure_storage.account_remove('pgquickstart');

为角色添加对 Azure Blob 存储的访问权限

SELECT * FROM azure_storage.account_user_add('pgquickstart', 'support');

列出对 Azure Blob 存储具有访问权限的所有角色

SELECT * FROM azure_storage.account_list();

移除对 Azure Blob 存储具有访问权限的所有角色

SELECT * FROM azure_storage.account_user_remove('pgquickstart', 'support');

列出 `public` 容器中的对象

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer');

列出 `private` 容器中的对象

SELECT * FROM azure_storage.blob_list('pgquickstart','privatecontainer');

注意

必须添加访问密钥。

列出公共容器中具有特定字符串缩写的对象

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer','e');

或者

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer') WHERE path LIKE 'e%';

从容器中的对象读取内容

函数 blob_get 从 Blob 存储中检索文件。为了让 blob_get 了解如何分析数据，可以传递值 (NULL：：table_name，该值的格式与文件相同)。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv.gz'
        , NULL::events)
LIMIT 5;

或者，我们可以明确定义 FROM 子句中的列。

SELECT * FROM azure_storage.blob_get('pgquickstart','publiccontainer','events.csv')
AS res (
         event_id BIGINT
        ,event_type TEXT
        ,event_public BOOLEAN
        ,repo_id BIGINT
        ,payload JSONB
        ,repo JSONB
        ,user_id BIGINT
        ,org JSONB
        ,created_at TIMESTAMP WITHOUT TIME ZONE)
LIMIT 5;

使用解码器选项

此示例演示如何使用 decoder 选项。通常，格式是从文件的扩展名推断出来的，但是当文件内容没有匹配的扩展名时，可以传递解码器参数。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events'
        , NULL::events
        , decoder := 'csv')
LIMIT 5;

将压缩与解码器选项配合使用

该示例演示如何在没有标准 .gz 扩展名的 gzip 压缩文件上强制使用 gzip 压缩。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events-compressed'
        , NULL::events
        , decoder := 'csv'
        , compression := 'gzip')
LIMIT 5;

在从 csv 格式对象加载之前导入筛选过的内容和修改

该示例说明了在将内容加载到 SQL 表之前，可以筛选和修改从容器中的对象导入的内容。

SELECT concat('P-',event_id::text) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
WHERE event_type='PushEvent'
LIMIT 5;

使用标头、自定义分隔符、转义字符的文件中的查询内容

可通过将 azure_storage.options_copy 的结果传递给 options 参数来使用自定义分隔符和转义字符。

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events_pipe.csv'
        ,NULL::events
        ,options := azure_storage.options_csv_get(delimiter := '|' , header := 'true')
        );

容器中某个对象内容的聚合查询

这样，无需导入即可查询数据。

SELECT event_type,COUNT(1) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
GROUP BY event_type
ORDER BY 2 DESC
LIMIT 5;

后续步骤

如何通过 COPY 命令使用 Azure Blob 存储

如何使用 pg_azure_storage 引入数据

Share via

pg_azure_storage extension

azure_storage.account_add

参数

account_name_p

account_key_p

azure_storage.account_remove

参数

account_name_p

azure_storage.account_user_add

参数

account_name_p

user_p

azure_storage.account_user_remove

参数

account_name_p

user_p

azure_storage.account_list

自变量

account_name

allowed_users

返回类型

azure_storage.blob_list

参数

account_name

container_name

前缀

path

字节

last_modified

etag

content_type

content_encoding

content_hash

返回类型

azure_storage.blob_get

参数

帐户

容器

path

rec

解码器

解码器说明

compression

选项

返回类型

azure_storage.options_csv_get

参数

delimiter

null_string

标头的值开始缓存响应

引号

转义符

force_not_null

force_null

content_encoding

返回类型

azure_storage.options_copy

参数

delimiter

null_string

标头的值开始缓存响应

引号

转义符

force_quote

force_not_null

force_null

content_encoding

返回类型

azure_storage.options_tsv

参数

delimiter

null_string

content_encoding

返回类型

azure_storage.options_binary

参数

content_encoding

返回类型

示例

列出 `public` 容器中的对象

列出 `private` 容器中的对象