Como ingerir dados com pg_azure_storage no Azure Cosmos DB para PostgreSQL

Artigo
06/23/2023

APLICA-SE A: Azure Cosmos DB para PostgreSQL (com tecnologia da extensão da base de dados Citus para PostgreSQL)

Este artigo mostra como utilizar a extensão pg_azure_storage PostgreSQL para manipular e carregar dados para o Azure Cosmos DB para PostgreSQL diretamente a partir do Armazenamento de Blobs do Azure (ABS). O ABS é um serviço de armazenamento dimensionável, durável e seguro nativo da cloud. Estas características fazem com que seja uma boa opção para armazenar e mover dados existentes para a cloud.

Preparar a base de dados e o armazenamento de blobs

Para carregar dados do Armazenamento de Blobs do Azure, instale a pg_azure_storage extensão PostgreSQL na sua base de dados:

SELECT * FROM create_extension('azure_storage');

Importante

A extensão pg_azure_storage está disponível apenas no Azure Cosmos DB para clusters PostgreSQL com o PostgreSQL 13 e superior.

Preparámos um conjunto de dados de demonstração público para este artigo. Para utilizar o seu próprio conjunto de dados, siga migrar os seus dados no local para o armazenamento na cloud para saber como colocar os seus conjuntos de dados de forma eficiente no Armazenamento de Blobs do Azure.

Nota

Selecionar "Contentor (acesso de leitura anónimo para contentores e blobs)" permite-lhe ingerir ficheiros de Armazenamento de Blobs do Azure através dos respetivos URLs públicos e enumerar os conteúdos do contentor sem a necessidade de configurar uma chave de conta no pg_azure_storage. Os contentores definidos para o nível de acesso "Privado (sem acesso anónimo)" ou "Blob (acesso de leitura anónimo apenas para blobs)" exigirão uma chave de acesso.

Listar conteúdos de contentor

Existe uma demonstração Armazenamento de Blobs do Azure conta e contentor pré-criado para este procedimento. O nome do contentor é githube está na pgquickstart conta. Podemos ver facilmente que ficheiros estão presentes no contentor com a azure_storage.blob_list(account, container) função .

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

Pode filtrar a saída com uma cláusula SQL WHERE normal ou com o prefix parâmetro da blob_list UDF. Este último filtra as linhas devolvidas no lado Armazenamento de Blobs do Azure.

Nota

A listagem de conteúdos de contentor requer uma conta e uma chave de acesso ou um contentor com acesso anónimo ativado.

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

Carregar dados do ABS

Carregar dados com o comando COPY

Comece por criar um esquema de exemplo.

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');

Carregar dados para as tabelas torna-se tão simples como chamar o COPY comando.

-- 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';

Repare como a extensão reconheceu que os URLs fornecidos ao comando copy são de Armazenamento de Blobs do Azure, os ficheiros que apontámos eram comprimidos gzip e que também eram processados automaticamente por nós.

O COPY comando suporta mais parâmetros e formatos. No exemplo acima, o formato e a compressão foram selecionados automaticamente com base nas extensões de ficheiro. No entanto, pode fornecer o formato diretamente semelhante ao comando normal COPY .

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

Atualmente, a extensão suporta os seguintes formatos de ficheiro:

formato	descrição
csv	Formato de valores separados por vírgulas utilizado pelo PostgreSQL COPY
tsv	Valores separados por tabulações, o formato PREdefinido postgreSQL COPY
binary	Formato BINARY PostgreSQL COPY
texto	Um ficheiro com um único valor de texto (por exemplo, JSON ou XML grande)

Carregar dados com blob_get()

O COPY comando é conveniente, mas limitado em flexibilidade. A função COPY utiliza blob_get internamente, que pode utilizar diretamente para manipular dados em cenários mais complexos.

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

Nota

Na consulta acima, o ficheiro é totalmente obtido antes LIMIT 3 de ser aplicado.

Com esta função, pode manipular dados de imediato em consultas complexas e fazer importações como 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

No comando acima, filtremos os dados para contas com os gravatar_id respetivos inícios de sessão presentes e maiúsculos.

Opções para blob_get()

Em algumas situações, poderá ter de controlar exatamente o que blob_get as tentativas de fazer com os decoderparâmetros e compressionoptions .

O descodificador pode ser definido como auto (predefinição) ou qualquer um dos seguintes valores:

formato	descrição
csv	Formato de valores separados por vírgulas utilizado pelo PostgreSQL COPY
tsv	Valores separados por tabulações, o formato PREdefinido postgreSQL COPY
binary	Formato BINARY PostgreSQL COPY
texto	Um ficheiro com um único valor de texto (por exemplo, JSON ou XML grande)

compression pode ser auto (predefinição) none ou gzip.

Por fim, o options parâmetro é do tipo jsonb. Existem quatro funções utilitárias que ajudam a criar valores para o mesmo. Cada função de utilitário é designada para o descodificador que corresponde ao respetivo nome.

descodificador	função options
csv	`options_csv_get`
tsv	`options_tsv`
binary	`options_binary`
texto	`options_copy`

Ao observar as definições de função, pode ver que parâmetros são suportados pelo descodificador.

options_csv_get - delimitador, null_string, cabeçalho, aspas, escape, force_not_null, force_null, content_encoding options_tsv - delimitador, null_string, content_encoding options_copy - delimitador, null_string, cabeçalho, aspas, escape, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Sabendo o que se segue, podemos eliminar as gravações com nulos gravatar_id durante a análise.

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

Aceder ao armazenamento privado

Obter o nome da conta e a chave de acesso

Sem uma chave de acesso, não nos será permitido listar contentores que estão definidos para níveis de acesso Privado ou 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>')
```
Na sua conta de armazenamento, abra as chaves do Access. Copie o nome da conta de armazenamento e copie a chave da secção key1 (primeiro tem de selecionar Mostrar junto à chave).
Adicionar uma conta ao pg_azure_storage
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Agora, pode listar os contentores definidos como níveis de acesso Privado e Blob para esse armazenamento, mas apenas como o citus utilizador, que tem a azure_storage_admin função concedida. Se criar um novo utilizador com o nome support, não será permitido aceder aos conteúdos do contentor por predefinição.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
Permitir que o support utilizador utilize uma conta de Armazenamento de Blobs do Azure específica

Conceder a permissão é tão simples como chamar account_user_add.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
Podemos ver os utilizadores permitidos na saída de account_list, que mostra todas as contas com chaves de acesso definidas.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Se alguma vez decidir que o utilizador já não deve ter acesso. Basta ligar para account_user_remove.
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Passos seguintes

Parabéns, acabou de aprender a carregar dados para o Azure Cosmos DB para PostgreSQL diretamente a partir de Armazenamento de Blobs do Azure.

Saiba como criar um dashboard em tempo real com o Azure Cosmos DB para PostgreSQL.
Saiba mais sobre pg_azure_storage.
Saiba mais sobre o suporte do Postgres COPY.

Como ingerir dados com pg_azure_storage no Azure Cosmos DB para PostgreSQL

Preparar a base de dados e o armazenamento de blobs

Listar conteúdos de contentor

Carregar dados do ABS

Carregar dados com o comando COPY

Carregar dados com blob_get()

Opções para blob_get()

Aceder ao armazenamento privado

Passos seguintes

Recursos adicionais