pg_azure_storage extensão

APLICA-SE A: Azure Cosmos DB para PostgreSQL (alimentado pela extensão de banco de dados Citus para PostgreSQL)

A extensão pg_azure_storage permite carregar dados em vários formatos de arquivo diretamente do armazenamento de blobs do Azure para seu cluster do Azure Cosmos DB for PostgreSQL. Ativar a extensão também desbloqueia novos recursos do comando COPY . Contêineres com nível de acesso "Privado" ou "Blob" requer a adição de chave de acesso privada.

Você pode criar a extensão executando:

SELECT create_extension('azure_storage');

azure_storage.account_add

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 blob do Azure (ABS) contém todos os seus objetos ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que pode ser acessado de qualquer lugar do mundo por HTTPS.

account_key_p

Suas chaves de acesso de armazenamento de blob do Azure (ABS) são semelhantes a uma senha de root para sua conta de armazenamento. Tenha sempre o cuidado de proteger as suas chaves de acesso. Use o Azure Key Vault para gerenciar e girar suas chaves com segurança. A chave da conta é armazenada em uma tabela acessível pelo superusuário do postgres, azure_storage_admin e todas as funções concedidas a essas permissões de administrador. Para ver quais contas de armazenamento existem, use a função account_list.

azure_storage.account_remove

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 seus objetos ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que pode ser acessado de qualquer lugar do mundo por 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 blob do Azure (ABS) contém todos os seus objetos ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que pode ser acessado de qualquer lugar do mundo por HTTPS.

user_p

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

Nota

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

azure_storage.account_user_remove

A função permite remover o acesso de uma função a 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 blob do Azure (ABS) contém todos os seus objetos ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que pode ser acessado de qualquer lugar do mundo por HTTPS.

user_p

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

azure_storage.lista_de_contas

A função lista a conta & função que tem acesso ao armazenamento de blob 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 seus objetos ABS: blobs, arquivos, filas e tabelas. A conta de armazenamento fornece um namespace exclusivo para seu ABS que pode ser acessado de qualquer lugar do mundo por HTTPS.

allowed_users

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

Tipo de retorno

TABELA

azure_storage.blob_list

A função lista os arquivos de blob disponíveis dentro de um contêiner de 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 contentor organiza um conjunto de blobs, de forma semelhante a um diretório num sistema de ficheiros. Uma conta de armazenamento pode incluir um número ilimitado de contentores, e um contentor pode armazenar um número ilimitado de blobs. Um nome de contêiner deve ser um nome DNS válido, pois faz parte do URI exclusivo usado para endereçar o contêiner ou seus blobs. Siga estas regras ao nomear um contêiner:

• Os nomes dos contêineres podem ter entre 3 e 63 caracteres.
• 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êiner.

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

prefixo

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

path

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

bytes

Tamanho do objeto de arquivo em bytes.

last_modified

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

etag

Uma propriedade ETag é usada para simultaneidade otimista durante as atualizações. Não é um carimbo de data/hora, pois há outra propriedade chamada Timestamp que armazena a última vez que um registro foi atualizado. Por exemplo, se você carregar uma entidade e quiser atualizá-la, o ETag deverá corresponder ao que está armazenado no momento. Definir a ETag apropriada é importante porque, se você tiver vários usuários editando o mesmo item, não deseja 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 de JavaScript.

content_encoding

O Armazenamento do Azure permite definir a propriedade Content-Encoding em um blob. Para conteúdo compactado, você pode definir a propriedade como GZIP. Quando o navegador acessa o conteúdo, ele descompacta automaticamente o conteúdo.

content_hash

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

Tipo de retorno

CONJUNTO de recordes

Nota

Permissões Agora você pode listar contêineres definidos como níveis de acesso Privado e Blob para esse armazenamento, mas apenas como o citus user, que tem a azure_storage_admin função concedida a ele. Se você criar um novo usuário chamado support, ele não terá permissão para 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 de 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

conta

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

contentor

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

path

Nome do blob existente no contêiner.

rec

Defina a estrutura de saída do registro.

descodificador

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

Descrição do descodificador

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

compressão

Define o formato de compactação. As opções disponíveis são auto, gzip & none. O uso da auto opção (padrão), adivinha a compressão com base na extensão do arquivo (.gz == gzip). A opção none força a ignorar a extensão e não tentar decodificar. Enquanto o gzip força o uso do decodificador gzip (para quando você tem um arquivo compactado com uma extensão não padrão). Atualmente, não suportamos nenhum outro formato de compressão para a extensão.

options

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

Tipo de retorno

SETOF Record / qualquerelemento

Nota

Existem quatro funções de utilidade, chamadas como um parâmetro dentro blob_get que ajudam a construir valores para ele. Cada função do utilitário é designada para o descodificador correspondente ao seu nome.

azure_storage.options_csv_get

A função atua como uma função de utilidade chamada como um parâmetro dentro 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

delimitador

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. 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) no formato de texto e uma cadeia de caracteres vazia sem aspas no formato CSV. Você pode preferir uma cadeia de caracteres vazia, mesmo em formato de texto, para casos em que não deseja distinguir nulos de cadeias de caracteres vazias.

cabeçalho

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.

citação

Especifica o caractere de cotação a ser usado quando um valor de dados é cotado. O padrão é aspas duplas. Deve ser um único caractere de um byte.

escape

Especifica o caractere que deve aparecer antes de um caractere de dados que corresponda ao valor QUOTE. O padrão é o mesmo que o valor QUOTE (para que o caractere de cotação seja dobrado se aparecer nos dados). Deve ser um único caractere de um byte.

force_not_null

Não corresponda aos valores das colunas especificadas com a 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 nulos, mesmo quando não estão entre aspas.

force_null

Corresponda os valores das colunas especificadas com a cadeia de caracteres nula, mesmo que ela tenha sido citada, e se uma correspondência for encontrada, defina o valor como NULL. No caso padrão em que a cadeia de caracteres nula está vazia, ela converte uma cadeia de caracteres vazia entre aspas em NULL.

content_encoding

Especifica que o arquivo é 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 de utilidade chamada como um parâmetro dentro 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

delimitador

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. 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) no formato de texto e uma cadeia de caracteres vazia sem aspas no formato CSV. Você pode preferir uma cadeia de caracteres vazia, mesmo em formato de texto, para casos em que não deseja distinguir nulos de cadeias de caracteres vazias.

cabeçalho

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.

citação

Especifica o caractere de cotação a ser usado quando um valor de dados é cotado. O padrão é aspas duplas. Deve ser um único caractere de um byte.

escape

Especifica o caractere que deve aparecer antes de um caractere de dados que corresponda ao valor QUOTE. O padrão é o mesmo que o valor QUOTE (para que o caractere de cotação seja dobrado se aparecer nos dados). Deve ser um único caractere de um byte.

force_quote

Força a cotação a ser usada para todos os valores não-NULL em cada coluna especificada. A saída NULL nunca é citada. Se * for especificado, valores não NULL serão citados em todas as colunas.

force_not_null

Não corresponda aos valores das colunas especificadas com a 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 nulos, mesmo quando não estão entre aspas.

force_null

Corresponda os valores das colunas especificadas com a cadeia de caracteres nula, mesmo que ela tenha sido citada, e se uma correspondência for encontrada, defina o valor como NULL. No caso padrão em que a cadeia de caracteres nula está vazia, ela converte uma cadeia de caracteres vazia entre aspas em NULL.

content_encoding

Especifica que o arquivo é 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 de utilidade chamada como um parâmetro dentro 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

delimitador

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. 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) no formato de texto e uma cadeia de caracteres vazia sem aspas no formato CSV. Você pode preferir uma cadeia de caracteres vazia, mesmo em formato de texto, para casos em que não deseja distinguir nulos de cadeias de caracteres vazias.

content_encoding

Especifica que o arquivo é 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 de utilidade chamada como um parâmetro dentro 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 é codificado no encoding_name. Se essa opção for omitida, a codificação do cliente atual será usada.

Tipo de Retorno

jsonb

Nota

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

Exemplos

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

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

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

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

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

Gorjeta

Na sua conta de armazenamento, abra as chaves de acesso. Copie o nome da conta de armazenamento e copie a seção Chave da chave1 (você deve 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 a arquivos hospedados em bucket privado no contêiner.

SELECT azure_storage.account_remove('pgquickstart');

Adicionando acesso para uma função ao armazenamento de Blob do Azure

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

Listar todas as funções com acesso no armazenamento de Blob do Azure

SELECT * FROM azure_storage.account_list();

Removendo as funções com acesso no armazenamento de Blob do Azure

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

Listar os objetos dentro de um public contêiner

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

Listar os objetos dentro de um private contêiner

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

Nota

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

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

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

Alternativamente

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

Ler conteúdo de um objeto em um contêiner

A blob_get função recupera um arquivo do armazenamento de blob. Para que blob_get saiba como analisar os dados, você pode passar um valor (NULL::table_name), que tem 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 FROM cláusula.

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;

Utilizar a opção de descodificador

O exemplo ilustra o uso da decoder opção. 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;

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

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

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

Importar conteúdo filtrado & modificar antes de carregar do objeto de formato csv

O exemplo ilustra a possibilidade de filtrar & 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;

Consultar conteúdo de 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 options argumento.

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 no 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óximos passos

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

Como ingerir dados usando pg_azure_storage