Примеры расширения службы хранилища Azure в Базе данных Azure для PostgreSQL

Ниже приведен список примеров, которые помогут вам узнать, как использовать расширение службы хранилища Azure.

Создание учетной записи хранения Azure и заполнение ее данными

Создайте учетную запись Службы хранилища Azure. Чтобы создать учетную запись хранения Azure, если у вас еще нет учетной записи хранения, настройте значения <resource_group>, <location><account_name>и <container_name>выполните следующую команду Azure CLI:

random_suffix=$(tr -dc 'a-z0-9' </dev/urandom | head -c8)
resource_group="resource-group-$random_suffix"
location="eastus2"
storage_account="storageaccount$random_suffix"
blob_container="container-$random_suffix"
az group create --name $resource_group --location $location
az storage account create --resource-group $resource_group --name $storage_account --location $location --sku Standard_LRS --kind BlobStorage --public-network-access enabled --access-tier hot
echo "Take note of the storage account name, which you'll have to replace in subsequent examples, whenever you find a reference to <account_name>:"
echo $storage_account
echo "Take note of the container name, which you'll have to replace in subsequent examples, whenever you find a reference to <container_name>:"
echo $blob_container

Создайте контейнер BLOB-объектов. Чтобы создать контейнер BLOB-объектов, выполните следующую команду Azure CLI:
```
az storage container create --account-name $storage_account --name $blob_container -o tsv
```
Получите один из двух ключей доступа, назначенных учетной записи хранения. Скопируйте значение access_key, так как необходимо передать его в качестве аргумента в azure_storage.account_add на следующем шаге. Чтобы получить первый из двух ключей доступа, выполните следующую команду Azure CLI:
```
access_key=$(az storage account keys list --resource-group $resource_group --account-name $storage_account --query [0].value)
echo "Following is the value of your access key:"
echo $access_key
```

Скачайте файл с набором данных, который используется в примерах, и отправьте его в контейнер BLOB-объектов. Чтобы скачать файл с набором данных, выполните следующую команду Azure CLI:

mkdir --parents azure_storage_examples
cd azure_storage_examples
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/storage_extension_sample.parquet
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "storage_extension_sample.parquet" --account-key $access_key --overwrite --output none --only-show-errors
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/parquet_without_extension
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "parquet_without_extension" --account-key $access_key --overwrite --output none --only-show-errors
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/storage_extension_sample.csv
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "storage_extension_sample.csv" --account-key $access_key --overwrite --output none --only-show-errors
curl -L -O https://github.com/Azure-Samples/azure-postgresql-storage-extension/raw/main/csv_without_extension
az storage blob upload-batch --account-name $storage_account --destination $blob_container --source . --pattern "csv_without_extension" --account-key $access_key --overwrite --output none --only-show-errors

Замечание

Вы можете перечислить контейнеры или большие двоичные объекты, хранящиеся в них для определенной учетной записи хранения, но только если пользователю Или роли PostgreSQL предоставлено разрешение на ссылку на эту учетную запись хранения с помощью azure_storage.account_user_add. azure_storage_admin Члены роли предоставляются этим привилегиям для всех учетных записей хранения Azure, добавленных с помощью azure_storage.account_add. По умолчанию только члены azure_pg_admin предоставляются azure_storage_admin роли.

Создание таблицы, в которой загружаются данные

Создадим таблицу, в которую мы импортируем содержимое файлов, которые мы отправили в учетную запись хранения. Для этого подключитесь к экземпляру гибкого сервера Базы данных Azure для PostgreSQL с помощью PostgreSQL для Visual Studio Code (предварительная версия),psql, PgAdmin или клиента вашего предпочтения и выполните следующую инструкцию:

CREATE TABLE IF NOT EXISTS sample_data (
    id BIGINT PRIMARY KEY,
    sample_text TEXT,
    sample_integer INTEGER,
    sample_timestamp TIMESTAMP
);

Подготовка расширения к использованию

Прежде чем продолжить, убедитесь, что вы:

Добавление ключа доступа учетной записи хранения

В этом примере показано, как добавить ссылку на учетную запись хранения вместе с ключом доступа этой учетной записи хранения, которая требуется для доступа к содержимому с помощью функциональных возможностей, предоставляемых azure_storage расширением в экземпляре гибкого сервера Базы данных Azure для PostgreSQL.

<account_name> необходимо задать имя учетной записи хранения. Если вы использовали предыдущие скрипты, это значение должно соответствовать значению, заданному переменной среды storage_account в этих сценариях.

Аналогичным образом необходимо задать значение, <access_key> которое вы извлекли из учетной записи хранения.

SELECT azure_storage.account_add('<account_name>', '<access_key>');

Подсказка

Если вы хотите получить имя учетной записи хранения и один из ключей доступа на портале Azure, найдите учетную запись хранения, в меню ресурсов выберите ключи доступа, скопируйте имя учетной записи хранения и скопируйте ключ из разделаkey1 (сначала нажмите кнопку "Показать рядом с ключом").

Предоставление доступа пользователю или роли в справочнике по хранилищу BLOB-объектов Azure

В этом примере показано, как предоставить пользователю или роли <regular_user>доступ, чтобы такой пользователь PostgreSQL использовал azure_storage расширение для доступа к BLOB-объектам, хранящимся в контейнерах, размещенных в учетной записи хранения Azure.

<regular_user> необходимо задать имя существующего пользователя или роли.

SELECT * FROM azure_storage.account_user_add('<account_name>', '<regular_user>');

Вывод списка всех больших двоичных объектов в контейнере

В этом примере показано, как перечислить все существующие большие двоичные объекты в контейнере <container_name> учетной записи <account_name>хранения.

<container_name> необходимо задать имя контейнера BLOB-объектов. Если вы использовали предыдущие сценарии, это значение должно соответствовать значению, заданному переменной среды blob_container в этих сценариях.

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>');

Вывод списка больших двоичных объектов с определенным префиксом имени

В этом примере показано, как перечислить все существующие большие двоичные объекты в контейнере <container_name> учетной записи <account_name>хранения, имя большого двоичного объекта которого начинается с <blob_name_prefix>.

<blob_name_prefix> необходимо задать любое префикс, которое необходимо, чтобы большие двоичные объекты, перечисленные в их именах. Если вы хотите вернуть все большие двоичные объекты, можно задать для этого параметра пустую строку или даже не указать значение для этого параметра, в этом случае значение по умолчанию используется для пустой строки.

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>','<blob_name_prefix>');

Кроме того, можно использовать следующий синтаксис:

SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>') WHERE path LIKE '<blob_name_prefix>%';

Импорт данных с помощью инструкции COPY FROM

В следующем примере показано импорт данных из большого двоичного объекта, который storage_extension_sample.parquet находится в контейнере <container_name> BLOB-объектов в учетной записи <account_name>хранения Azure, с помощью COPY команды:

Создайте таблицу, которая соответствует схеме исходного файла:

CREATE TABLE IF NOT EXISTS sample_data (
    id BIGINT PRIMARY KEY,
    sample_text TEXT,
    sample_integer INTEGER,
    sample_timestamp TIMESTAMP
);

Используйте инструкцию для копирования данных в целевую COPY таблицу. Формат выводится как Parquet из расширения файла.
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample.parquet';
```
Используйте инструкцию для копирования данных в целевую COPY таблицу. Так как формат кодирования не может быть выведен из расширения файла, он явно указан с помощью FORMAT параметра.
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/parquet_without_extension'
WITH (FORMAT 'parquet');
```
Используйте инструкцию для копирования данных в целевую COPY таблицу. Формат кодирования можно вывести из расширения файла. Однако наличие заголовков столбцов в первой строке необходимо явно настроить с помощью HEADERS параметра.
```
TRUNCATE TABLE sample_data;
COPY sample_data
FROM 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample.csv'
WITH (HEADERS);
```
Выполните следующую SELECT инструкцию, чтобы убедиться, что данные загружаются в таблицу.
```
SELECT *
FROM sample_data
LIMIT 100;
```

Экспорт данных с помощью инструкции COPY TO

В следующих примерах показано экспорт данных из таблицы sample_data, в несколько больших двоичных объектов с разными именами и характеристиками, такими как формат кодирования, все из которых находятся в контейнере <container_name> БОЛЬШИХ двоичных объектов в учетной записи <account_name>хранения Azure с помощью COPY команды:

Создайте таблицу, которая соответствует схеме исходного файла:

CREATE TABLE IF NOT EXISTS sample_data (
    id BIGINT PRIMARY KEY,
    sample_text TEXT,
    sample_integer INTEGER,
    sample_timestamp TIMESTAMP
);

Загрузите данные в таблицу. Выполните инструкции INSERT, чтобы заполнить его несколькими синтетическими строками, или использовать данные импорта с помощью примера инструкции COPY FROM , чтобы заполнить его содержимым примера набора данных.
Используйте инструкцию COPY для копирования данных из целевой таблицы. Укажите, что формат кодирования должен быть parquet.
```
COPY sample_data
TO 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample_exported.parquet'
WITH (FORMAT 'parquet');
```
Используйте инструкцию COPY для копирования данных из целевой таблицы. Укажите, что формат кодирования должен быть CSV- и первая строка результирующего файла содержит заголовки столбцов.
```
COPY sample_data
TO 'https://<account_name>.blob.core.windows.net/<container_name>/storage_extension_sample_exported.csv'
WITH (FORMAT 'csv', HEADERS);
```
Выполните следующую SELECT инструкцию, чтобы убедиться, что большой двоичный объект существует в учетной записи хранения.
```
SELECT * FROM azure_storage.blob_list('<account_name>','<container_name>') WHERE path LIKE 'storage_extension_sample_exported%';
```

Чтение содержимого из большого двоичного объекта

Функция blob_get извлекает содержимое одного конкретного большого двоичного объекта в контейнере <account_name> хранилища, на который ссылается <container_name> ссылка. Чтобы blob_get узнать, как проанализировать данные, которые можно передать в форме NULL::table_name, где table_name ссылается на таблицу, схема которой соответствует считываемой большой двоичный объект. В примере она ссылается на таблицу sample_data , созданную в самом начале.

<blob_name> необходимо задать полный путь к большому двоичному объекту, содержимое которого требуется прочитать.

В этом случае декодатор, который должен использоваться для анализа большого двоичного объекта, выводится из .parquet расширения файла.

SELECT * FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.parquet'
        , NULL::sample_data)
LIMIT 5;

Кроме того, можно явно определить схему результата с помощью AS предложения после функции blob_get .

SELECT * FROM azure_storage.blob_get('<account_name>','<container_name>','storage_extension_sample.parquet')
AS res (
        id BIGINT PRIMARY KEY,
        sample_text TEXT,
        sample_integer INTEGER,
        sample_timestamp TIMESTAMP)
LIMIT 5;

Чтение, фильтрация и изменение содержимого из большого двоичного объекта

В этом примере показана возможность фильтрации и изменения содержимого, импортированного из большого двоичного объекта, перед загрузкой этого в таблицу SQL.

SELECT concat('P-',id::text) FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.parquet'
        , NULL::sample_data)
WHERE sample_integer=780
LIMIT 5;

Чтение содержимого из файла с настраиваемыми параметрами (заголовки, разделители столбцов, escape-символы)

В этом примере показано, как использовать пользовательские разделители и escape-символы, передав результат options_copy аргументу options .

SELECT * FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.csv'
        ,NULL::sample_data
        ,options := azure_storage.options_csv_get(header := 'true')
        );

Использование параметра декодировщика

В этом примере показано использование decoder параметра. Если параметр декодера отсутствует, он выводится из расширения файла. Но если имя файла не имеет расширения или когда это расширение имени файла не соответствует одному, связанному с декодером, который должен использоваться для правильного анализа содержимого файла, можно явно передать аргумент декодатора.

SELECT * FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'parquet_without_extension'
        , NULL::sample_data
        , decoder := 'parquet')
LIMIT 5;

Агрегирование вычислений по содержимому большого двоичного объекта

В этом примере показано, как выполнять операции агрегирования по данным, хранящимся в контейнере БОЛЬШИХ двоичных объектов, без необходимости импортировать содержимое большого двоичного объекта в таблицы PostgreSQL.

SELECT sample_integer, COUNT(*) FROM azure_storage.blob_get
        ('<account_name>'
        ,'<container_name>'
        ,'storage_extension_sample.parquet'
        , NULL::sample_data)
GROUP BY sample_integer
ORDER BY 2 DESC
LIMIT 5;

Запись содержимого в большой двоичный объект

Функция blob_put состоит из содержимого одного конкретного большого двоичного объекта (sample_data_copy.parquet в данном случае) и отправляет его в указанный контейнер <container_name><account_name> хранилища. В этом примере используется blob_get для создания набора из пяти строк, которые затем передаются blob_put в агрегатную функцию, которая отправляет их в виде большого двоичного объекта с именем sample_data_copy.parquet.

Формат кодирования выводится как parquet, основанный на расширении .parquetфайла.

SELECT azure_storage.blob_put
        ('<account_name>'
        ,'<container_name>'
        ,'sample_data_copy.parquet'
        , top_5_sample_data)
FROM (SELECT * FROM sample_data LIMIT 5) AS top_5_sample_data;

Формат кодирования выводится в формате CSV на основе расширения .csvфайла.

SELECT azure_storage.blob_put
        ('<account_name>'
        ,'<container_name>'
        ,'sample_data_copy.csv'
        , top_5_sample_data)
FROM (SELECT * FROM sample_data LIMIT 5) AS top_5_sample_data;

Формат кодирования не может быть выведен, так как файл не имеет расширения, поэтому он явно настроен как parquet. Кроме того, для алгоритма сжатия задано значение zstd.

SELECT azure_storage.blob_put
        ('<account_name>'
        ,'<container_name>'
        ,'sample_parquet_data_copy_without_extension_with_zstd_compression'
        , top_5_sample_data
        ,'parquet'
        ,'zstd')
FROM (SELECT * FROM sample_data LIMIT 5) AS top_5_sample_data;

Список всех ссылок на учетные записи хранения Azure

В этом примере показано, как узнать, какие учетные записи azure_storage хранения Azure могут ссылаться в этой базе данных вместе с типом проверки подлинности, используемой для доступа к каждой учетной записи хранения, и какие пользователи или роли предоставляют разрешения через функцию azure_storage.account_user_add , чтобы получить доступ к этой учетной записи хранения Azure через функциональные возможности, предоставляемые расширением.

SELECT * FROM azure_storage.account_list();

Отзыв доступа от пользователя или роли в справочнике по хранилищу BLOB-объектов Azure

В этом примере показано, как отозвать доступ от имени пользователя или роли <regular_user>, чтобы такой пользователь PostgreSQL не смог использовать azure_storage расширение для доступа к BLOB-объектам, хранящимся в контейнерах, размещенных в учетной записи хранения Azure.