了解 Azure 存储扩展
Woodgrove Bank 决定使用 Azure Blob 存储将其历史数据文件安全地存储在云中。 他们要求能够从其存储帐户的专用容器中存储的 CSV 文件中批量加载其历史数据。
Microsoft 开发了 pg_azure_storage
扩展,以处理 Azure Blob 存储中托管的文件中存储的数据。 使用该扩展,你可以直接从 Azure Cosmos DB for PostgreSQL 数据库对 blob 存储帐户中的文件进行操作。 使用该扩展可以执行以下操作:
- 直接从数据库安全连接到 Azure Blob 存储帐户。
- 从公开共享的 Blob 存储资源中提取数据。
- 列出 Blob 存储容器中的文件。
- 检索文件中的数据。
- 使用 COPY PostgreSQL 命令将 CSV 文件数据直接导入表中。
将文件迁移到存储帐户
需要先将文件添加或迁移到 Azure 存储帐户,然后才能利用此扩展的功能。 通过将文件移动到 Azure Blob 存储,可使用云原生安全存储服务。
加载 pg_azure_storage 扩展
开始之前,需使用 CREATE EXTENSION
或 create_extension()
函数加载该扩展:
-- Install the extension in the database
SELECT create_extension('azure_storage');
在数据库中创建和使用扩展时从扩展名中删除 pg_
,将其缩写为 azure_storage
。
授予对 Blob 存储帐户的访问权限
Woodgrove Bank 的 Azure 云工程师创建了名为 stlearnpostgresql
的存储帐户供你使用,并将一些示例文件添加到了名为 historical-data
的专用容器中。 pg_azure_storage
扩展包含用于将存储帐户连接到数据库的 account_add()
方法。 通过添加帐户,可访问安全或专用容器中的文件。 若要连接专用容器,必须提供存储帐户名称和访问密钥,其语法如下:
SELECT azure_storage.account_add('{STORAGE_ACCOUNT_NAME}', '{STORAGE_ACCOUNT_ACCESS_KEY}');
要从数据库访问 Woodgrove Bank 的存储帐户,需检索其名称和访问密钥。 可以从 Azure 门户中与存储帐户关联的“访问密钥”页面获取此信息。
在运行以下查询时提供存储帐户名称,访问密钥将授予 pg_azure_storage
扩展对帐户的访问权限:
SELECT azure_storage.account_add('stlearnpostgresql', '4zzZGx4sUk8TBVnHnoPYt0G7A3w9/DJJBCfdxeeb+VDrR8P3bSwWA3lYsTvO1HwYzrt4XQ37iIEL+AStWuQ/uA==');
仅当使用“专用(无匿名访问)”访问级别的容器中的数据时,才需要提供访问密钥。 假设你想要从访问级别设置为“容器(容器和 blob 的匿名读取访问)”的容器中拉取数据。在这种情况下,可以使用文件的公共 URL 从 Azure Blob 存储中引入文件,并枚举容器内容,而无需在 pg_azure_storage
扩展中配置帐户密钥。
在 blob 存储中列出文件
连接到存储帐户后,可以对存储帐户执行多项操作,包括在命名容器中列出 Blob。 若要更好地了解哪些示例文件已上传到 historical-data
容器,可以运行以下查询:
SELECT path, content_type, pg_size_pretty(bytes)
FROM azure_storage.blob_list('stlearnpostgresql', 'historical-data');
blob_list()
函数将存储帐户和容器名称作为输入,并在容器中输出 blob 列表:
path | content_type | pg_size_pretty
------------+--------------+----------------
events.csv | text/csv | 17 MB
users.csv | text/csv | 29 MB
除了每个文件的路径、内容类型和大小(以字节为单位),你还可以使用 blob_list()
函数查看上次修改日期、内容编码和内容哈希。
从 blob 批量加载数据
Woodgrove 表示希望将历史数据存储在其 Azure Blob 存储帐户的 CSV 文件中。 然后,应将数据直接加载到 Azure Cosmos DB for PostgreSQL 数据库的分布式表中。 为了从 Blob 存储中的文件大容量加载数据,pg_azure_storage
扩展了本机 PostgreSQL COPY
命令,使其能够处理 Azure Blob 存储资源 URL。 此功能默认处于启用状态,你可以使用 pg_azure_storage.enable_copy_command
设置对其进行管理。
通过使用 COPY
命令,可以运行以下内容,以将用户数据引入 payment_users
表中:
-- Bulk load data from the user.csv file in Blob Storage into the payment_users table
COPY payment_users FROM 'https://stlearnpostgresql.blob.core.windows.net/historical-data/users.csv';
运行带有 pg_azure_storage
的 COPY
命令时要注意的一个重要区别是,不能在 COPY
之前使用 \
,并且行必须以分号结尾。 之所以有此要求是因为在使用 pg_azure_storage
扩展时 COPY
命令在协调器节点上运行。 在 psql
客户端中运行命令时使用 \COPY
版本。
COPY
命令的输出将指定复制到表中的行数,例如 COPY 264197
。