如何在 Azure Cosmos DB for PostgreSQL 中使用 pg_azure_storage 來內嵌資料

發行項
03/28/2024

適用於： Azure Cosmos DB for PostgreSQL (由 PostgreSQL 的超大規模 (Citus) 資料庫延伸模組提供)

本文說明如何使用 pg_azure_storage PostgreSQL 延伸模組，直接從 Azure Blob 儲存體 (ABS) 操作並將資料載入至 Azure Cosmos DB for PostgreSQL。 ABS 是雲端原生可調整、持久且安全的儲存體服務。這些特性有利您選擇將現有資料儲存並移至雲端。

準備資料庫和 Blob 儲存體

若要從 Azure Blob 儲存體載入資料，請在資料庫中安裝 pg_azure_storage PostgreSQL 延伸模組：

SELECT * FROM create_extension('azure_storage');

重要

pg_azure_storage 延伸模組僅適用於執行 PostgreSQL 13 和更新版本的 PostgreSQL 叢集所用的 Azure Cosmos DB。

我們已為本文準備好公用示範資料集。若要使用您自己的資料集，請遵循將內部部署資料移轉至雲端儲存體，以了解如何有效率地將資料集放入 Azure Blob 儲存體。

注意

選取「容器 (容器和 Blob 的匿名讀取存取權)」可讓您使用其公用 URL 從 Azure Blob 儲存體內嵌檔案，並列舉容器內容，而不需要在 pg_azure_storage 中設定帳戶金鑰。設定為存取層級的容器「私人 (無匿名存取)」或「Blob (僅適用於 blob 的匿名讀取存取權)」將需要存取金鑰。

列出容器內容

此操作說明有預先建立的 Azure Blob 儲存體帳戶和容器示範。容器名稱為 github，且位於 pgquickstart 帳戶中。我們可以輕鬆地使用 azure_storage.blob_list(account, container) 函式來查看容器中的檔案。

SELECT path, bytes, pg_size_pretty(bytes), content_type
  FROM azure_storage.blob_list('pgquickstart','github');

-[ RECORD 1 ]--+-------------------
path           | events.csv.gz
bytes          | 41691786
pg_size_pretty | 40 MB
content_type   | application/x-gzip
-[ RECORD 2 ]--+-------------------
path           | users.csv.gz
bytes          | 5382831
pg_size_pretty | 5257 kB
content_type   | application/x-gzip

您可以使用一般 SQL WHERE 子句，或使用 blob_list UDF 的 prefix 參數來篩選輸出。後者會篩選 Azure Blob 儲存體端傳回的資料列。

注意

列出容器內容需要帳戶和存取金鑰，或已啟用匿名存取的容器。

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

-[ RECORD 1 ]----+---------------------------------
path             | events.csv.gz
bytes            | 41691786
last_modified    | 2022-10-12 18:49:51+00
etag             | 0x8DAAC828B970928
content_type     | application/x-gzip
content_encoding |
content_hash     | 473b6ad25b7c88ff6e0a628889466aed

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

-[ RECORD 1 ]----+---------------------------------
path             | events.csv.gz
bytes            | 41691786
last_modified    | 2022-10-12 18:49:51+00
etag             | 0x8DAAC828B970928
content_type     | application/x-gzip
content_encoding |
content_hash     | 473b6ad25b7c88ff6e0a628889466aed

從 ABS 載入資料

使用 COPY 命令載入資料

從建立範例結構描述開始。

CREATE TABLE github_users
(
	user_id bigint,
	url text,
	login text,
	avatar_url text,
	gravatar_id text,
	display_login text
);

CREATE TABLE github_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
);

CREATE INDEX event_type_index ON github_events (event_type);
CREATE INDEX payload_index ON github_events USING GIN (payload jsonb_path_ops);

SELECT create_distributed_table('github_users', 'user_id');
SELECT create_distributed_table('github_events', 'user_id');

將資料載入資料表變得像呼叫 COPY 命令一樣簡單。

-- download users and store in table

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz';

-- download events and store in table

COPY github_events
FROM 'https://pgquickstart.blob.core.windows.net/github/events.csv.gz';

請注意，延伸模組如何辨識提供給複製命令的 URL 來自 Azure Blob 儲存體，我們指向的檔案是 gzip 壓縮的，而且也會自動為我們處理。

COPY 命令支援更多參數與格式。在上述範例中，會根據副檔名自動選取格式和壓縮。不過，您可以直接提供與一般 COPY 命令類似的格式。

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz'
WITH (FORMAT 'csv');

目前延伸模組支援下列檔案格式：

format	description
csv	PostgreSQL COPY 使用的逗號分隔值格式
tsv	定位字元分隔值，預設的 PostgreSQL COPY 格式
binary	二進位 PostgreSQL COPY 格式
text	包含單一文字值的檔案 (例如，大型 JSON 或 XML)

使用 blob_get() 載入資料

COPY 命令很方便，但彈性有限。內部 COPY 會使用 blob_get 函式，您可以在更複雜的案例中直接用來操控資料。

SELECT *
  FROM azure_storage.blob_get(
         'pgquickstart', 'github',
         'users.csv.gz', NULL::github_users
       )
 LIMIT 3;

-[ RECORD 1 ]-+--------------------------------------------
user_id       | 21
url           | https://api.github.com/users/technoweenie
login         | technoweenie
avatar_url    | https://avatars.githubusercontent.com/u/21?
gravatar_id   |
display_login | technoweenie
-[ RECORD 2 ]-+--------------------------------------------
user_id       | 22
url           | https://api.github.com/users/macournoyer
login         | macournoyer
avatar_url    | https://avatars.githubusercontent.com/u/22?
gravatar_id   |
display_login | macournoyer
-[ RECORD 3 ]-+--------------------------------------------
user_id       | 38
url           | https://api.github.com/users/atmos
login         | atmos
avatar_url    | https://avatars.githubusercontent.com/u/38?
gravatar_id   |
display_login | atmos

注意

在上述查詢中，會在套用 LIMIT 3 之前，完全擷取檔案。

透過此函式，您可以在複雜的查詢中即時操控資料，並以 INSERT FROM SELECT 進行匯入。

INSERT INTO github_users
     SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
       FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users)
      WHERE gravatar_id IS NOT NULL;

INSERT 0 264308

在上述命令中，我們已將資料篩選為出現 gravatar_id 的帳戶，並即時對其登入使用大寫。

blob_get() 的選項

在某些情況下，您可能需要使用decoder, compression 和 options 參數，完全控制 blob_get 嘗試執行的動作。

解碼器可設為 auto (預設) 或下列任何一個值：

format	description
csv	PostgreSQL COPY 使用的逗號分隔值格式
tsv	定位字元分隔值，預設的 PostgreSQL COPY 格式
binary	二進位 PostgreSQL COPY 格式
text	包含單一文字值的檔案 (例如，大型 JSON 或 XML)

compression 可以是 auto (預設)、none 或 gzip。

最後，options 參數類型是 jsonb。有四個公用程式函式可協助為其建置值。每個公用程式函式都會指定用於符合其名稱的解碼器。

解碼器	選項函式
csv	`options_csv_get`
tsv	`options_tsv`
binary	`options_binary`
text	`options_copy`

藉由查看函式定義，您可以看到解碼器支援哪些參數。

options_csv_get - delimiter、null_string、header、quote、escape、force_not_null、force_null、content_encoding options_tsv - delimiter、null_string、content_encoding options_copy - delimiter、null_string、header、quote、escape、force_quote、force_not_null、force_null、content_encoding。 options_binary - content_encoding

了解上述內容，我們可以在剖析期間捨棄具有 Null gravatar_id 的錄製內容。

INSERT INTO github_users
     SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
       FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users,
                                    options := azure_storage.options_csv_get(force_not_null := ARRAY['gravatar_id']));

INSERT 0 264308

存取私人儲存體

取得您的帳戶名稱和存取金鑰

如果沒有存取金鑰，我們就無法列出設定為私人或 Blob 存取層級的容器。
```
SELECT * FROM azure_storage.blob_list('mystorageaccount','privdatasets');
```
```
ERROR:  azure_storage: missing account access key
HINT:  Use SELECT azure_storage.account_add('<account name>', '<access key>')
```
在您的儲存體帳戶中，開啟 [存取金鑰]。複製儲存體帳戶名稱，並從 [key1] 區段中複製金鑰 (您必須先選取金鑰旁的 [顯示])。
將帳戶新增至 pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
現在，您可以列出該儲存體設為「私人」和「Blob 存取層級」的容器，但僅做為 citus 使用者，其已授與 azure_storage_admin 角色。如果您建立名為 support 的新使用者，則預設不允許存取容器內容。
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
允許 support 使用者使用特定的 Azure Blob 儲存體帳戶

授與權限就像呼叫 account_user_add 一樣簡單。
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
我們可以在 account_list 的輸出中看到允許的使用者，其中會顯示已定義存取金鑰的所有帳戶。
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
如果您已決定，使用者應該不再具有存取權。只要呼叫 account_user_remove 即可。
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

下一步

恭喜，您剛了解如何從 Azure Blob 儲存體直接將資料載入 Azure Cosmos DB for PostgreSQL。

了解如何使用 Azure Cosmos DB for PostgreSQL 建立即時儀表板。
深入瞭解 pg_azure_storage。
瞭解 Postgres COPY support 支援。

共用方式為