Поделиться через

Загрузка данных с использованием pg_azure_storage в Azure Cosmos DB для PostgreSQL

Область применения: Azure Cosmos DB для PostgreSQL (на базе расширения Citus для PostgreSQL)

В этой статье показано, как использовать расширение PostgreSQL pg_azure_storage для управления данными и загрузки данных в Azure Cosmos DB для PostgreSQL непосредственно из Azure Blob Storage (ABS). ABS — это облачная масштабируемая, устойчивая и безопасная служба хранения. Эти характеристики делают его хорошим выбором для хранения и перемещения существующих данных в облако.

Подготовка базы данных и хранилища блочных данных

Чтобы загрузить данные из облачного хранилища Azure Blob, установите расширение pg_azure_storage PostgreSQL в базе данных.

SELECT * FROM create_extension('azure_storage');

Внимание

Расширение pg_azure_storage доступно только в кластерах Azure Cosmos DB для PostgreSQL, где используется PostgreSQL версии 13 или выше.

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

Примечание.

Если выбрать "Контейнер (анонимный доступ для чтения для контейнеров и BLOB-ов)", это позволит получать файлы из Хранилища BLOB-объектов Azure с помощью общедоступных URL-адресов и перечислять содержимое контейнера без необходимости настраивать ключ учетной записи в pg_azure_storage. Контейнеры, установленные для уровня доступа "Частный (без анонимного доступа)" или "BLOB (анонимный доступ только для чтения BLOB-объектов)", потребуют ключ доступа.

Список содержимого контейнера

Существует демонстрационный аккаунт и контейнер в Azure Blob Storage, которые были предварительно созданы для этого руководства. Имя контейнера — 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 или с помощью prefix параметра blob_list UDF. Вторая фильтрует возвращаемые строки на стороне Azure Blob Storage.

Примечание.

Для перечисления содержимого контейнера требуется ключ учетной записи и доступа или контейнер с включенным анонимным доступом.

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-адреса, предоставленные команде копирования, исходят из хранилища Blob-объектов Azure. Файлы, на которые мы указали, были сжаты в формате gzip, и это также было автоматически обработано для нас.

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

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

В настоящее время расширение поддерживает следующие форматы файлов:

format описание
csv Формат разделенных запятыми значений, используемый PostgreSQL COPY
tsv Значения, разделенные символом табуляции, формат копирования PostgreSQL по умолчанию
бинарный Формат копирования двоичного postgreSQL
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()

В некоторых ситуациях может потребоваться управлять именно тем, что blob_get нужно сделать с помощью decodercompression параметров и options параметров.

Декодатор может иметь значение auto (по умолчанию) или любое из следующих значений:

format описание
csv Формат разделенных запятыми значений, используемый PostgreSQL COPY
tsv Значения, разделенные табуляции, формат копирования PostgreSQL по умолчанию
бинарный Формат копирования двоичного postgreSQL
текст Файл, содержащий одно текстовое значение (например, большой json или XML)

compression может быть либо auto (по умолчанию), none либо gzip.

Наконец, options параметр имеет тип jsonb. Существует четыре вспомогательных функции, которые помогают в создании значений для него. Каждая служебная функция предназначена для декодера, соответствующего его имени.

дешифратор Функция опций
csv options_csv_get
tsv options_tsv
двоичный options_binary
текст options_copy

Просмотрев определения функций, можно увидеть, какие параметры поддерживаются декодером.

options_csv_get — разделитель, null_string, заголовок, цитата, escape, force_not_null, force_null, content_encoding options_tsv — разделитель, null_string, content_encoding options_copy — разделитель, null_string, заголовок, кавычки, escape-force_quote, force_not_null, force_null, content_encoding. options_binary - кодировка_контента

Зная выше, можно отменить записи с значением 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

Доступ к частному хранилищу

  1. Получение имени учетной записи и ключа доступа

    Без ключа доступа мы не будем разрешать перечислять контейнеры, для которых задано значение "Частный" или "Уровень доступа к 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 (сначала необходимо нажать "Показать" рядом с ключом).

    Снимок экрана раздела "Безопасность + сеть. Ключи доступа" на странице Хранилище Blob в портале Azure.

  2. Добавление учетной записи в pg_azure_storage

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

    Теперь вы можете перечислить контейнеры, установленные на уровни доступа Private и 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

  3. Разрешить support пользователю использовать определенную учетную запись хранилища Blob-объектов Azure

    Предоставление разрешения так же просто, как вызов 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 Cosmos DB для PostgreSQL непосредственно из Azure Blob-хранилища.