Прием данных с помощью pg_azure_storage в Azure Cosmos DB для PostgreSQL

Статья
08/15/2024

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

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

Подготовка базы данных и хранилища BLOB-объектов

Чтобы загрузить данные из Хранилище BLOB-объектов Azure, установите pg_azure_storage расширение PostgreSQL в базе данных:

SELECT * FROM create_extension('azure_storage');

Внимание

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

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

Примечание.

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

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

Существует демонстрация Хранилище BLOB-объектов Azure учетной записи и контейнера, предварительно созданной для этого инструкции. Имя контейнера — 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. Последний фильтрует возвращаемые строки на стороне Хранилище BLOB-объектов Azure.

Примечание.

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

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

Эта 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 по умолчанию
binary	Формат копирования двоичного postgreSQL
text	Файл, содержащий одно текстовое значение (например, большой json или XML)

Загрузка данных с помощью blob_get()

Команда удобна 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 по умолчанию
binary	Формат копирования двоичного postgreSQL
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 — разделитель, 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 - 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');
```
Теперь вы можете перечислить контейнеры с уровнем доступа к частному и большому двоичному объекту для этого хранилища, но только в качестве 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 Разрешить пользователю использовать определенную учетную запись Хранилище 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 непосредственно из Хранилище BLOB-объектов Azure.

Узнайте, как создать панель мониторинга в режиме реального времени с помощью Azure Cosmos DB для PostgreSQL.
Дополнительные сведения о pg_azure_storage.
Сведения о поддержке Postgres COPY.

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

Прием данных с помощью pg_azure_storage в Azure Cosmos DB для PostgreSQL

Подготовка базы данных и хранилища BLOB-объектов

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

Загрузка данных из ABS

Загрузка данных с помощью команды COPY

Загрузка данных с помощью blob_get()

Параметры blob_get()

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

Следующие шаги

Обратная связь

Дополнительные ресурсы