適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器中的 Azure 儲存體延伸模組

發行項
08/15/2024

適用於：適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

Microsoft 客戶的常見使用案例是能夠在 Azure Blob 記憶體與適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體之間匯入和匯出資料。適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的 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 儲存體匯入至適用於 PostgreSQL 的 Azure 資料庫彈性伺服器

若要從 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 儲存器匯入現有的適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料表。

使用 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)

將資料從適用於 PostgreSQL 的 Azure 資料庫彈性伺服器匯出至 Azure Blob 儲存體

若要將資料從適用於 PostgreSQL 的 Azure 資料庫彈性伺服器匯出至 Azure Blob 儲存體，您需要將 azure_storage 延伸模組加入允許清單。然後使用 CREATE EXTENSION 命令，在資料庫中安裝 azure_storage PostgreSQL 延伸模組：

CREATE EXTENSION azure_storage;

您建立儲存體帳戶時，Azure 會為該帳戶產生兩個 512 位元儲存體帳戶存取金鑰。您可以透過共用金鑰授權，或透過使用共用金鑰簽章的共用存取簽章 (SAS) 權杖，來使用這些金鑰以授權存取儲存體帳戶中的資料。

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

最後，您可以使用 COPY 陳述式或 blob_put 函式，將資料從適用於 PostgreSQL 的 Azure 資料庫資料表匯出至 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 權杖，用這些金鑰授與儲存體帳戶資料的存取權。

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

Azure 儲存體延伸模組會提供一個 blob_list 方法。您可以使用此方法，以下列格式列出 Blob 儲存體中的物件：

azure_storage.blob_list(account_name, container_name, prefix)

下列範例會顯示從名為 mystorageaccount 的儲存體帳戶，以及名為 mytestbob 的 Blob 容器，使用 blob_list 方法在 Azure 儲存體中列出物件。容器中的檔案具有字串 employee。

SELECT path, size, last_modified, etag FROM azure_storage.blob_list('mystorageaccount','mytestblob','employee');

將權限指派給非管理帳戶，以從 Azure 儲存體存取資料

根據預設，只有 azure_pg_admin 系統管理角色可以新增帳戶金鑰，並存取適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的儲存體帳戶。

您可以根據權限粒度，以兩種方式將 Azure 儲存體中的資料存取權授與適用於 PostgreSQL 的非系統管理 Azure 資料庫彈性伺服器使用者：

將 azure_storage_admin 指派給非系統管理使用者。會隨著 Azure 儲存體延伸模組的安裝而新增此角色。下列範例會將此角色授與名為 support 的非系統管理使用者：
```
-- Allow adding/list/removing storage accounts
GRANT azure_storage_admin TO support;
```
呼叫 account_user_add 函式。下列範例會將權限新增至適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的角色 support。這是更有限的權限，因為它只會讓使用者存取名為 mystorageaccount 的 Azure 儲存體帳戶。
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```

適用於 PostgreSQL 的 Azure 資料庫彈性伺服器的系統管理使用者可以在 account_list 函式的輸出中取得儲存體帳戶和權限的清單。此函式會顯示已定義存取金鑰的所有帳戶。

SELECT * FROM azure_storage.account_list();

當「適用於 PostgreSQL 的 Azure 資料庫」彈性伺服器系統管理員決定使用者不該再有存取權時，系統管理員可以使用 account_user_remove 方法或函式來移除此存取權。下列範例會從儲存體帳戶 mystorageaccount 的存取權中移除角色 support：

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

下一步

如果您未看見想要使用的延伸模組，請讓我們知道。請在我們的意見反應論壇中投票表決現有的要求，或建立新的意見反應要求。

共用方式為