extensão pg_azure_storage

APLICA-SE AO: Azure Cosmos DB for PostgreSQL (da plataforma da extensão de dados Citus para PostgreSQL)

A extensão pg_azure_storage permite que você carregue dados em vários formatos de arquivo diretamente do armazenamento de blobs do Azure para o cluster do Azure Cosmos DB for PostgreSQL. Habilitar a extensão também desbloqueia novos recursos do comando COPY . Contêineres com nível de acesso “Privado” ou “Blob” exigem a adição da chave de acesso privada.

Você pode criar a extensão executando:

SELECT create_extension('azure_storage');

azure_storage.account_add

A função permite adicionar acesso a uma conta de armazenamento.

azure_storage.account_add
        (account_name_p text
        ,account_key_p text);

Argumentos

account_name_p

Uma conta de armazenamento de blobs do Azure (ABS) contém todos os seus objetos do ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que é acessível a partir de qualquer lugar do mundo através de HTTPS.

account_key_p

Suas chaves de acesso ao armazenamento de blobs (ABS) do Azure são semelhantes a uma senha raiz para sua conta de armazenamento. Sempre tenha cuidado para proteger as suas chaves de acesso. Utilize o Azure Key Vault para gerenciar e girar suas chaves com segurança. A chave da conta é armazenada em uma tabela que pode ser acessada pelo superusuário do postgres, azure_storage_admin e por todas as funções que receberam essas permissões de administrador. Para ver quais contas de armazenamento existem, use a função account_list.

azure_storage.account_remove

A função permite revogar o acesso da conta à conta de armazenamento.

azure_storage.account_remove
        (account_name_p text);

Argumentos

account_name_p

A conta de armazenamento de blobs do Azure (ABS) contém todos os objetos do ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que é acessível a partir de qualquer lugar do mundo através de HTTPS.

azure_storage.account_user_add

A função permite adicionar acesso para uma função a uma conta de armazenamento.

azure_storage.account_user_add
        ( account_name_p text
        , user_p regrole);

Argumentos

account_name_p

Uma conta de armazenamento de blobs do Azure (ABS) contém todos os seus objetos do ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que é acessível a partir de qualquer lugar do mundo através de HTTPS.

user_p

A função criada pelo usuário visível no cluster.

Observação

As funções account_user_add,account_add,account_remove,account_user_remove exigem a configuração de permissões para cada nó individual no cluster.

azure_storage.account_user_remove

A função permite remover o acesso de uma função para uma conta de armazenamento.

azure_storage.account_user_remove
        (account_name_p text
        ,user_p regrole);

Argumentos

account_name_p

Uma conta de armazenamento de blobs do Azure (ABS) contém todos os seus objetos do ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que é acessível a partir de qualquer lugar do mundo através de HTTPS.

user_p

A função criada pelo usuário visível no cluster.

azure_storage.account_list

A função lista a conta e a função com acesso ao armazenamento de blobs do Azure.

azure_storage.account_list
        (OUT account_name text
        ,OUT allowed_users regrole[]
        )
Returns TABLE;

Argumentos

account_name

A conta de armazenamento de blobs do Azure (ABS) contém todos os objetos do ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que é acessível a partir de qualquer lugar do mundo através de HTTPS.

allowed_users

Lista os usuários que têm acesso ao armazenamento de blobs do Azure.

Tipo de retorno

TABLE

azure_storage.blob_list

A função lista os arquivos de blob disponíveis dentro de um contêiner do usuário com suas propriedades.

azure_storage.blob_list
        (account_name text
        ,container_name text
        ,prefix text DEFAULT ''::text
        ,OUT path text
        ,OUT bytes bigint
        ,OUT last_modified timestamp with time zone
        ,OUT etag text
        ,OUT content_type text
        ,OUT content_encoding text
        ,OUT content_hash text
        )
Returns SETOF record;

Argumentos

account_name

O storage account name fornece um namespace exclusivo para seus dados de armazenamento do Azure que pode ser acessado de qualquer lugar do mundo por HTTPS.

container_name

Um contêiner organiza um conjunto de blobs, semelhantes a um diretório em um sistema de arquivos. Uma conta de armazenamento pode incluir um número ilimitado de contêineres e um contêiner pode armazenar um número ilimitado de blobs. Um nome de contêiner deve ser um nome DNS válido, pois ele faz parte do URI exclusivo usado para endereçar o contêiner ou seus blobs. Siga estas regras ao nomear um contêiner:

• O comprimento dos nomes do contêiner pode ser entre 3 e 63  aracteres.
• Os nomes de contêiner devem começar com uma letra ou número e podem conter apenas letras minúsculas, números e o caractere traço (-).
• Dois ou mais caracteres de traço consecutivos não são permitidos em nomes de contêineres.

O URI de um contêiner é semelhante a: https://myaccount.blob.core.windows.net/mycontainer

prefixo

Retorna o arquivo do contêiner de blobs com as iniciais de cadeia de caracteres correspondentes.

caminho

Caminho qualificado completo do diretório de blobs do Azure.

bytes

Tamanho do objeto do arquivo em bytes.

last_modified

Quando o conteúdo do arquivo foi modificado pela última vez.

etag

Uma propriedade ETag é usada para uma simultaneidade otimista durante as atualizações. Não é um carimbo de data/hora, pois há outra propriedade chamada Carimbo de Data/Hora que armazena a última vez que um registro foi atualizado. Por exemplo, se você carregar uma entidade e quiser atualizá-la, a ETag deverá corresponder ao que está armazenado atualmente. Definir a ETag apropriada é importante porque, se você tiver vários usuários editando o mesmo item, não desejará que eles substituam as alterações uns dos outros.

content_type

O objeto Blob representa um blob, que é um objeto semelhante a um arquivo de dados brutos imutáveis. Eles podem ser lidos como texto ou dados binários, ou convertidos em um ReadableStream para que seus métodos possam ser usados para processar os dados. Os blobs podem representar dados que não estão necessariamente em um formato nativo do JavaScript.

content_encoding

O Armazenamento do Microsoft Azure permite que você defina a propriedade Codificação do Conteúdo em um blob. Para conteúdo compactado, você pode definir a propriedade como GZIP. Quando o navegador acessa o conteúdo, ele o descompacta automaticamente.

content_hash

O hash é usado para verificar a integridade do blob durante o transporte. Quando esse cabeçalho é especificado, o serviço de armazenamento verifica o hash do conteúdo que chegou com o valor enviado. Se os dois hashes não corresponderem, a operação falhará com o código de erro 400 (Solicitação inválida).

Tipo de retorno

Registro SETOF

Observação

Permissões: agora você pode listar os contêineres definidos como níveis de acesso Privado e de Blob para esse armazenamento, mas apenas como citus user, que tem a função azure_storage_admin concedida a ele. Se você criar um novo usuário chamado support, ele não poderá acessar o conteúdo do contêiner por padrão.

azure_storage.blob_get

A função permite carregar o conteúdo do arquivo \ arquivos de dentro do contêiner, com suporte adicional na filtragem ou manipulação dos dados, antes da importação.

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF record;

Há uma versão sobrecarregada da função, contendo o parâmetro rec que permite definir convenientemente o registro do formato de saída.

azure_storage.blob_get
        (account_name text
        ,container_name text
        ,path text
        ,rec anyelement
        ,decoder text DEFAULT 'auto'::text
        ,compression text DEFAULT 'auto'::text
        ,options jsonb DEFAULT NULL::jsonb
        )
RETURNS SETOF anyelement;

Argumentos

account

A conta de armazenamento fornece um namespace exclusivo para seus dados de Armazenamento do Microsoft Azure que pode ser acessado de qualquer lugar do mundo por HTTPS.

contêiner

Um contêiner organiza um conjunto de blobs, semelhantes a um diretório em um sistema de arquivos. Uma conta de armazenamento pode incluir um número ilimitado de contêineres e um contêiner pode armazenar um número ilimitado de blobs. Um nome de contêiner deve ser um nome DNS válido, pois ele faz parte do URI exclusivo usado para endereçar o contêiner ou seus blobs.

caminho

Nome do blob existente no contêiner.

rec

Defina a estrutura de saída do registro.

decodificador

Especifique o Decodificador do formato de blob que pode ser definido como automático (padrão) ou qualquer um dos seguintes valores

descrição do decodificador

Formato	Descrição
csv	Formato de valores separados por vírgula usado pelo PostgreSQL COPY
tsv	Valores separados por tabulação, o formato PostgreSQL COPY
binary	Formato PostgreSQL COPY binário
text	Um arquivo que contém um único valor de texto (por exemplo, JSON ou XML grande)

compactação

Define o formato da compactação. As opções disponíveis são auto, gzip & none. O uso da opção auto (padrão), adivinha a compactação baseada na extensão do arquivo (.gz == gzip). A opção none força que a extensão seja ignorada e que não haja tentativa de decodificação. Enquanto o gzip força o uso do decodificador gzip (para quando você tem um arquivo gzip com uma extensão não padrão). Atualmente, não damos suporte para nenhum outro formato de compactação para a extensão.

opções

Para lidar com cabeçalhos personalizados, separadores personalizados, caracteres de escape, etc., options funciona de maneira semelhante ao comando COPY no PostgreSQL, o parâmetro utiliza a função blob_get.

Tipo de retorno

Registro SETOF/anyelement

Observação

Existem quatro funções utilitárias, chamadas como um parâmetro dentro de blob_get que ajudam a criar valores para ele. Cada função de utilitário é designada para o decodificador que corresponde ao nome.

azure_storage.options_csv_get

A função atua como uma função utilitária chamada como um parâmetro dentro de blob_get, que é útil para decodificar o conteúdo csv.

azure_storage.options_csv_get
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Argumentos

delimiter

Especifica o caractere que separa as colunas dentro de cada linha (linha) do arquivo. O padrão é um caractere de tabulação no formato de texto, uma vírgula no formato CSV. Ele deve ser um único caractere de um byte.

null_string

Especifica a cadeia de caracteres que representa um valor nulo. O padrão é\ N (barra invertida-N) em formato de texto e uma cadeia de caracteres vazia sem cotação no formato CSV. Talvez você prefira uma cadeia de caracteres vazia, mesmo em formato de texto, para os casos em que não deseja distinguir nulos de cadeias de caracteres vazias.

header

Especifica que o arquivo contém uma linha de cabeçalho com os nomes de cada coluna no arquivo. Na saída, a primeira linha contém os nomes das colunas da tabela.

cotação

Especifica o caractere em cotação a ser usado quando um valor de dados está em cotação. O padrão é cotas duplas. Ele deve ser um único caractere de um byte.

escape

Especifica o caractere que deve aparecer antes de um caractere de dados que corresponde ao valor da COTAÇÃO. O padrão é o mesmo que o valor da COTAÇÃO (para que o caractere em cotação seja duplicado se aparecer nos dados). Ele deve ser um único caractere de um byte.

force_not_null

Não corresponde aos valores das colunas especificadas em relação à cadeia de caracteres nula. No caso padrão em que a cadeia de caracteres nula está vazia, isso significa que os valores vazios são lidos como cadeias de caracteres de comprimento zero em vez de nulas, mesmo quando não estão em cotação.

force_null

Combine os valores das colunas especificadas com a cadeia de caracteres nula, mesmo que ela esteja em cotação, e se uma correspondência for encontrada, defina o valor como NULO. No caso padrão em que a cadeia de caracteres nula está vazia, ela converte uma cadeia de caracteres vazia em cotação em NULA.

content_encoding

Especifica que o arquivo está codificado no encoding_name. Se a opção for omitida, a codificação do cliente atual será usada.

Tipo de retorno

jsonb

azure_storage.options_copy

A função atua como uma função utilitária chamada como parâmetro dentro de blob_get.

azure_storage.options_copy
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,header boolean DEFAULT NULL::boolean
        ,quote text DEFAULT NULL::text
        ,escape text DEFAULT NULL::text
        ,force_quote text[] DEFAULT NULL::text[]
        ,force_not_null text[] DEFAULT NULL::text[]
        ,force_null text[] DEFAULT NULL::text[]
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Argumentos

delimiter

Especifica o caractere que separa as colunas dentro de cada linha (linha) do arquivo. O padrão é um caractere de tabulação no formato de texto, uma vírgula no formato CSV. Ele deve ser um único caractere de um byte.

null_string

Especifica a cadeia de caracteres que representa um valor nulo. O padrão é\ N (barra invertida-N) em formato de texto e uma cadeia de caracteres vazia sem cotação no formato CSV. Talvez você prefira uma cadeia de caracteres vazia, mesmo em formato de texto, para os casos em que não deseja distinguir nulos de cadeias de caracteres vazias.

header

Especifica que o arquivo contém uma linha de cabeçalho com os nomes de cada coluna no arquivo. Na saída, a primeira linha contém os nomes das colunas da tabela.

cotação

Especifica o caractere em cotação a ser usado quando um valor de dados está em cotação. O padrão é cotas duplas. Ele deve ser um único caractere de um byte.

escape

Especifica o caractere que deve aparecer antes de um caractere de dados que corresponde ao valor da COTAÇÃO. O padrão é o mesmo que o valor da COTAÇÃO (para que o caractere em cotação seja duplicado se aparecer nos dados). Ele deve ser um único caractere de um byte.

force_quote

Força a cotação a ser utilizada para todos os valores não NULOS em cada coluna especificada. A saída NULA nunca é cotada. Se * for especificado, valores não NULOS serão cotados em todas as colunas.

force_not_null

Não corresponde aos valores das colunas especificadas em relação à cadeia de caracteres nula. No caso padrão em que a cadeia de caracteres nula está vazia, isso significa que os valores vazios são lidos como cadeias de caracteres de comprimento zero em vez de nulas, mesmo quando não estão em cotação.

force_null

Combine os valores das colunas especificadas com a cadeia de caracteres nula, mesmo que ela esteja em cotação, e se uma correspondência for encontrada, defina o valor como NULO. No caso padrão em que a cadeia de caracteres nula está vazia, ela converte uma cadeia de caracteres vazia em cotação em NULA.

content_encoding

Especifica que o arquivo está codificado no encoding_name. Se a opção for omitida, a codificação do cliente atual será usada.

Tipo de retorno

jsonb

azure_storage.options_tsv

A função atua como uma função utilitária chamada como parâmetro dentro de blob_get. É útil para decodificar o conteúdo tsv.

azure_storage.options_tsv
        (delimiter text DEFAULT NULL::text
        ,null_string text DEFAULT NULL::text
        ,content_encoding text DEFAULT NULL::text
        )
Returns jsonb;

Argumentos

delimiter

Especifica o caractere que separa as colunas dentro de cada linha (linha) do arquivo. O padrão é um caractere de tabulação no formato de texto, uma vírgula no formato CSV. Ele deve ser um único caractere de um byte.

null_string

Especifica a cadeia de caracteres que representa um valor nulo. O padrão é\ N (barra invertida-N) em formato de texto e uma cadeia de caracteres vazia sem cotação no formato CSV. Talvez você prefira uma cadeia de caracteres vazia, mesmo em formato de texto, para os casos em que não deseja distinguir nulos de cadeias de caracteres vazias.

content_encoding

Especifica que o arquivo está codificado no encoding_name. Se a opção for omitida, a codificação do cliente atual será usada.

Tipo de retorno

jsonb

azure_storage.options_binary

A função atua como uma função utilitária chamada como parâmetro dentro de blob_get. É útil para decodificar o conteúdo binário.

azure_storage.options_binary
        (content_encoding text DEFAULT NULL::text)
Returns jsonb;

Argumentos

content_encoding

Especifica que o arquivo está codificado no encoding_name. Se essa opção for omitida, a codificação do cliente atual será usada.

Tipo de retorno

jsonb

Observação

Permissões: agora você pode listar os contêineres definidos como níveis de acesso Privado e de Blob para esse armazenamento, mas apenas como citus user, que tem a função azure_storage_admin concedida a ele. Se você criar um novo usuário nomeado suporte, ele não poderá acessar o conteúdo do contêiner por padrão.

Exemplos

Os exemplos usados fazem uso da amostra da conta de armazenamento do Azure (pgquickstart) com arquivos personalizados carregados para adicionar à cobertura de diferentes casos de uso. Podemos começar criando uma tabela usada em todo o conjunto de exemplos utilizados.

CREATE TABLE IF NOT EXISTS public.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 without time zone
        );

Adicionando a chave de acesso da conta de armazenamento (obrigatório para o nível de acesso = privado)

O exemplo ilustra a adição da chave de acesso da conta de armazenamento para obter acesso para consulta de uma sessão no cluster do Azure Cosmos DB for Postgres.

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

Dica

Em sua conta de armazenamento, abra Chaves de acesso. Copie o nome da conta de armazenamento e copie a chave da seção key1 (você precisa selecionar Mostrar ao lado da chave primeiro).

Removendo a chave de acesso da conta de armazenamento

O exemplo ilustra a remoção da chave de acesso de uma conta de armazenamento. Essa ação resultaria na remoção do acesso aos arquivos hospedados em um bucket privado no contêiner.

SELECT azure_storage.account_remove('pgquickstart');

Adicionar acesso a uma função ao Armazenamento de Blobs do Azure

SELECT * FROM azure_storage.account_user_add('pgquickstart', 'support');

Listar todas as funções com acesso no Armazenamento de Blobs do Azure

SELECT * FROM azure_storage.account_list();

Removendo as funções com acesso no Armazenamento de Blobs do Azure

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

Listar os objetos dentro de um contêiner public

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer');

Listar os objetos dentro de um contêiner private

SELECT * FROM azure_storage.blob_list('pgquickstart','privatecontainer');

Observação

A adição da chave de acesso é obrigatória.

Liste os objetos com iniciais de cadeias de caracteres específicas dentro do contêiner público

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer','e');

Como alternativa

SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer') WHERE path LIKE 'e%';

Leia o conteúdo de um objeto em um contêiner

A função blob_get recupera um arquivo do armazenamento de blobs. Para que blob_get saiba como analisar os dados, você pode passar um valor (NULL: :table_name), que tenha o mesmo formato do arquivo.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv.gz'
        , NULL::events)
LIMIT 5;

Alternativamente, podemos definir explicitamente as colunas na cláusula FROM.

SELECT * FROM azure_storage.blob_get('pgquickstart','publiccontainer','events.csv')
AS res (
         event_id BIGINT
        ,event_type TEXT
        ,event_public BOOLEAN
        ,repo_id BIGINT
        ,payload JSONB
        ,repo JSONB
        ,user_id BIGINT
        ,org JSONB
        ,created_at TIMESTAMP WITHOUT TIME ZONE)
LIMIT 5;

Use a opção de decodificador

O exemplo ilustra o uso da opção decoder. Normalmente, o formato é inferido a partir da extensão do arquivo, mas quando o conteúdo do arquivo não tem uma extensão correspondente, você pode passar o argumento do decodificador.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events'
        , NULL::events
        , decoder := 'csv')
LIMIT 5;

Usar a compactação com opção de decodificador

O exemplo mostra como impor o uso da compactação gzip em um arquivo compactado gzip sem uma extensão padrão .gz.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events-compressed'
        , NULL::events
        , decoder := 'csv'
        , compression := 'gzip')
LIMIT 5;

Importe o conteúdo filtrado e modifique antes de carregar do objeto de formato csv

O exemplo ilustra a possibilidade de filtrar e modificar o conteúdo que está sendo importado do objeto no contêiner antes de carregá-lo em uma tabela SQL.

SELECT concat('P-',event_id::text) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
WHERE event_type='PushEvent'
LIMIT 5;

Consulta o conteúdo do arquivo com cabeçalhos, separadores personalizados, caracteres de escape

Você pode usar separadores personalizados e caracteres de escape passando o resultado de azure_storage.options_copy para o argumento options.

SELECT * FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events_pipe.csv'
        ,NULL::events
        ,options := azure_storage.options_csv_get(delimiter := '|' , header := 'true')
        );

Consulta de agregação sobre o conteúdo de um objeto no contêiner

Dessa forma, você pode consultar dados sem importá-los.

SELECT event_type,COUNT(1) FROM azure_storage.blob_get
        ('pgquickstart'
        ,'publiccontainer'
        ,'events.csv'
        , NULL::events)
GROUP BY event_type
ORDER BY 2 DESC
LIMIT 5;

Próximas etapas

Como usar o Armazenamento de Blobs do Azure com o comando COPY

Como ingerir dados usando pg_azure_storage