你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn。
Azure Database for PostgreSQL - 灵活服务器中的 Azure 存储扩展
适用于: Azure Database for PostgreSQL 灵活服务器
Microsoft 客户的常见用例是能够在 Azure Blob 存储和 Azure Database for PostgreSQL 灵活服务器实例之间导入和导出数据。 Azure Database for PostgreSQL 灵活服务器中的 Azure 存储扩展(azure_storage
)简化了此用例。
Azure Blob 存储
Azure Blob 存储是适用于云的对象存储解决方案。 Blob 存储最适合存储巨量的非结构化数据。 非结构化数据是不遵循特定数据模型或定义的数据(如文本或二进制数据)。
Blob 存储提供了三种类型的资源的层次结构:
存储帐户是一个管理实体,用于保存 Blob、文件、队列、表或磁盘等项的服务。
在 Azure 中创建存储帐户时,你会获取用于存储资源的唯一命名空间。 该唯一的命名空间构成了 URL 的一部分。 存储帐户名称在 Azure 中的所有现有存储帐户名称中应该是唯一的。
容器位于存储帐户内部。 容器类似于存储 Blob 的文件夹。
可以定义安全策略并将策略分配给容器。 这些策略会级联到容器中的所有 Blob。
一个存储帐户可以包含无限数量的容器。 每个容器可以包含无限数量的 Blob,最大数量为存储帐户大小 500 TB。
将 Blob 放入存储帐户内部的容器后,可以使用以下格式的 URL 来引用 Blob:
protocol://<storage_account_name>/blob.core.windows.net/<container_name>/<blob_name>
。Blob 是驻留在容器中的数据片段。
以下图示显示了这些资源之间的关系。
在 Azure Blob 存储中将数据存储为 blob 的关键优势
Azure Blob 存储可提供以下优势:
- 它是一种可缩放且经济高效的云存储解决方案。 可以使用它存储任何大小的数据,并根据需求纵向扩展或缩减。
- 它还提供了安全层来保护数据,例如静态加密和传输中加密。
- 它会与其他 Azure 服务和合作伙伴应用程序通信。 它是适用于各种用例的通用解决方案,例如备份和灾难恢复、存档和数据分析。
- 它是一种经济高效的解决方案,可用于在云中管理和存储大量数据,而无论组织是小型企业还是大型企业。 只需为所需的存储付费。
将数据从 Azure Blob 存储导入 Azure Database for PostgreSQL 灵活服务器
要从 Azure Blob 存储加载数据,需要将 azure_storage
PostgreSQL 扩展加入允许列表。 然后使用 CREATE EXTENSION
命令在数据库中安装扩展:
CREATE EXTENSION azure_storage;
创建存储帐户时,Azure 会为该帐户生成两个 512 位的存储帐户访问密钥。 可以使用这些密钥通过共享密钥授权来授予对你存储帐户中数据的访问权限。
在可以导入数据之前,需要使用 account_add
方法映射存储帐户。 提供创建帐户时定义的帐户访问密钥。 下面的代码示例映射了存储帐户 mystorageaccount
,并使用字符串 SECRET_ACCESS_KEY
作为访问密钥参数:
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
映射存储后,可以列出存储帐户内容并选择要导入的数据。 以下示例假定已创建名为 mystorageaccount
的存储帐户和名为 mytestblob
的 Blob 容器:
SELECT path, bytes, pg_size_pretty(bytes), content_type
FROM azure_storage.blob_list('mystorageaccount','mytestblob');
可以使用常规 SQL WHERE
子句或 blob_list
方法的 prefix
参数筛选此语句的输出。 列出容器内容需要使用帐户和访问密钥或启用了匿名访问的容器。
最后,可以使用 COPY
语句或 blob_get
函数将数据从 Azure Blob 存储导入现有 Azure Database for PostgreSQL 灵活服务器表。
使用 COPY 语句导入数据
以下示例演示了如何通过 COPY
命令从驻留在同一 mystorageaccount
Azure 存储帐户的 blob 容器 mytestblob
中的 employee.csv 文件导入数据:
创建与源文件架构匹配的目标表:
CREATE TABLE employees ( EmployeeId int PRIMARY KEY, LastName VARCHAR ( 50 ) UNIQUE NOT NULL, FirstName VARCHAR ( 50 ) NOT NULL );
使用
COPY
语句将数据复制到目标表中。 指定第一行为标题。COPY employees FROM 'https://mystorageaccount.blob.core.windows.net/mytestblob/employee.csv' WITH (FORMAT 'csv', header);
使用 blob_get 函数导入数据
blob_get
函数会从 Blob 存储中检索文件。 要确保 blob_get
可以分析数据,可以传递一个类型与文件中的列相对应的值,或显式定义 FROM
子句中的列。
可以使用以下格式的 blob_get
函数:
azure_storage.blob_get(account_name, container_name, path)
下一个示例使用了 blob_get
函数来显示从同一个源到同一目标的相同操作:
INSERT INTO employees
SELECT * FROM azure_storage.blob_get('mystorageaccount','mytestblob','employee.csv',options:= azure_storage.options_csv_get(header=>true)) AS res (
CustomerId int,
LastName varchar(50),
FirstName varchar(50))
COPY
命令和 blob_get
函数支持以下文件扩展名的导入:
文件格式 | 说明 |
---|---|
.csv | PostgreSQL COPY 使用的逗号分隔值格式 |
.tsv | 制表符分隔值,默认的 PostgreSQL COPY 格式 |
binary | 二进制 PostgreSQL COPY 格式 |
text | 包含单个文本值的文件(例如大型 JSON 或 XML) |
将数据从 Azure Database for PostgreSQL 灵活服务器导出到 Azure Blob 存储
要将数据从 Azure Database for PostgreSQL 灵活服务器导出到 Azure Blob 存储,需要将 azure_storage
扩展加入允许列表。 然后使用 CREATE EXTENSION
命令在数据库中安装 azure_storage
PostgreSQL 扩展:
CREATE EXTENSION azure_storage;
创建存储帐户时,Azure 会为该帐户生成两个 512 位的存储帐户访问密钥。 可以使用这些密钥通过共享密钥授权,或通过使用共享密钥签名的共享访问签名 (SAS) 令牌来授权访问存储帐户中的数据。
在可以导入数据之前,需要使用 account_add
方法映射存储帐户。 提供创建帐户时定义的帐户访问密钥。 下面的代码示例映射了存储帐户 mystorageaccount
,并使用字符串 SECRET_ACCESS_KEY
作为访问密钥参数:
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
可以使用 COPY
语句或 blob_put
函数将数据从 Azure Database for PostgreSQL 表导出到 Azure Blob 存储。 以下示例演示了如何通过 COPY
命令将数据从员工表导出到名为 employee2.csv 的新文件。 该文件驻留在同一 mystorageaccount
Azure 存储帐户中的 Blob 容器 mytestblob
中。
COPY employees
TO 'https://mystorageaccount.blob.core.windows.net/mytestblob/employee2.csv'
WITH (FORMAT 'csv');
同样,可以通过 blob_put
函数从员工表导出数据,通过此操作可以更有限地控制导出的数据。 以下示例仅导出表的两列,即 EmployeeId
和 LastName
。 它跳过了 FirstName
列。
SELECT azure_storage.blob_put('mystorageaccount', 'mytestblob', 'employee2.csv', res) FROM (SELECT EmployeeId,LastName FROM employees) res;
COPY
命令和 blob_put
函数支持以下文件扩展名的导出:
文件格式 | 说明 |
---|---|
.csv | PostgreSQL COPY 使用的逗号分隔值格式 |
.tsv | 制表符分隔值,默认的 PostgreSQL COPY 格式 |
binary | 二进制 PostgreSQL COPY 格式 |
text | 包含单个文本值的文件(例如大型 JSON 或 XML) |
列出 Azure 存储中的对象
要列出 Azure Blob 存储中的对象,需要将 azure_storage
扩展加入允许列表。 然后使用 CREATE EXTENSION
命令在数据库中安装 azure_storage
PostgreSQL 扩展:
CREATE EXTENSION azure_storage;
创建存储帐户时,Azure 会为该帐户生成两个 512 位的存储帐户访问密钥。 可以使用这些密钥通过共享密钥授权,或通过使用共享密钥签名的 SAS 令牌来授权访问存储帐户中的数据。
在可以导入数据之前,需要使用 account_add
方法映射存储帐户。 提供创建帐户时定义的帐户访问密钥。 下面的代码示例映射了存储帐户 mystorageaccount
,并使用字符串 SECRET_ACCESS_KEY
作为访问密钥参数:
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
Azure 存储扩展提供了一种 blob_list
方法。 可以使用此方法按以下格式列出 Blob 存储中的对象:
azure_storage.blob_list(account_name, container_name, prefix)
以下示例演示了如何使用 blob_list
方法从名为 mystorageaccount
的存储帐户和名为 mytestbob
的 Blob 容器列出 Azure 存储中的对象。 容器中的文件具有字符串 employee
。
SELECT path, size, last_modified, etag FROM azure_storage.blob_list('mystorageaccount','mytestblob','employee');
向非管理帐户分配权限以访问 Azure 存储中的数据
默认情况下,只有 azure_pg_admin 管理角色才能添加帐户密钥并访问 Azure Database for PostgreSQL 灵活服务器中的存储帐户。
根据权限粒度,可以通过两种方式向非管理 Azure Database for PostgreSQL 灵活服务器用户授予访问 Azure 存储中数据的权限:
将
azure_storage_admin
分配给非管理用户。 此角色随 Azure 存储扩展的安装一起添加。 以下示例将此角色授予名为support
的非管理用户:-- Allow adding/list/removing storage accounts GRANT azure_storage_admin TO support;
调用
account_user_add
函数。 以下示例将权限添加到 Azure Database for PostgreSQL 灵活服务器中的角色support
。 这是一个更有限的权限,因为它只授予用户对名为mystorageaccount
的 Azure 存储帐户的访问权限。SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
Azure Database for PostgreSQL 灵活服务器的管理用户可以在 account_list
函数的输出中获取存储帐户和权限的列表。 此函数显示了具有所定义访问密钥的所有帐户。
SELECT * FROM azure_storage.account_list();
当 Azure Database for PostgreSQL 灵活服务器管理员判定用户不应再具有访问权限时,可以使用 account_user_remove
方法来移除此访问权限。 以下示例从对存储帐户 mystorageaccount
的访问权限中移除了角色 support
:
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
后续步骤
- 如果未看到要使用的扩展,请告诉我们。 若要支持现有请求或提出新反馈请求,请访问我们的反馈论坛。
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈