Atualização automática de tabelas de diretório para Google Cloud Storage

Este tópico fornece instruções para acionar atualizações de metadados de tabelas de diretório usando mensagens Google Cloud Pub/Sub para eventos do Google Cloud Storage (GCS).

Nota

Para completar as etapas descritas neste tópico, você deve usar uma função que tenha os privilégios CREATE STAGE em um esquema.

Além disso, você deve ter acesso administrativo à Google Cloud Platform (GCP). Se você não for um administrador do GCP, peça a seu administrador do GCP que complete as etapas de Pré-requisitos.

Observe que somente os eventos OBJECT_DELETE e OBJECT_FINALIZE acionam atualizações para tabelas de diretório. A Snowflake recomenda que você envie apenas eventos suportados para tabelas de diretório para reduzir custos, ruído de eventos e latência.

Neste tópico:

Suporte para a plataforma de nuvem

O disparo de atualizações automatizadas de metadados de diretório usando mensagens de eventos do GCS Pub/Sub tem suporte por contas Snowflake hospedadas nas seguintes plataformas de nuvem:

  • Amazon Web Services (AWS)

  • Google Cloud Platform

As instruções para a configuração deste suporte são idênticas para contas em qualquer uma das plataformas de hospedagem em nuvem.

Configuração de acesso seguro ao armazenamento em nuvem

Nota

Se você já tiver configurado o acesso seguro ao bucket GCS que armazena seus arquivos de dados, pode pular esta seção.

Esta seção descreve como configurar um objeto de integração de armazenamento Snowflake para delegar a responsabilidade pela autenticação do armazenamento em nuvem a uma entidade de gerenciamento de identidade e acesso (IAM) do Snowflake.

Esta seção descreve como usar as integrações de armazenamento para permitir que o Snowflake leia e grave dados em um bucket Google Cloud Storage referenciado em um estágio externo (ou seja, o armazenamento em nuvem). As integrações são objetos Snowflake nomeados e de primeira classe que evitam a necessidade de passar credenciais explícitas de provedores de nuvens, tais como chaves secretas ou tokens de acesso; em vez disso, os objetos de integração fazem referência a uma conta do serviço de armazenamento em nuvem. Um administrador em sua organização concede as permissões da conta de serviço na conta de armazenamento em nuvem.

Os administradores também podem restringir os usuários a um conjunto específico de buckets de armazenamento em nuvem (e caminhos opcionais) acessados por estágios externos que utilizam a integração.

Nota

Completar as instruções nesta seção requer acesso ao seu projeto de armazenamento em nuvem como um editor de projeto. Se você não for um editor de projeto, peça a seu administrador do armazenamento em nuvem para realizar estas tarefas.

O diagrama a seguir mostra o fluxo de integração para um estágio do armazenamento em nuvem:

Google Cloud Storage Stage Integration Flow
  1. Um estágio externo (ou seja, o armazenamento em nuvem) faz referência a um objeto de integração de armazenamento em sua definição.

  2. O Snowflake associa automaticamente a integração do armazenamento com uma conta de serviço de armazenamento em nuvem criada para sua conta. O Snowflake cria uma única conta de serviço que é referenciada por todas as integrações de armazenamento GCS em sua conta Snowflake.

  3. Um editor de projeto para seu projeto de armazenamento em nuvem concede permissões à conta de serviço para acessar o bucket referenciado na definição do estágio. Observe que muitos objetos de preparação externos podem fazer referência a diferentes buckets e caminhos e usar a mesma integração para autenticação.

Quando um usuário carrega ou descarrega dados de ou para um estágio, o Snowflake verifica as permissões concedidas à conta de serviço no bucket antes de permitir ou negar o acesso.

Nesta seção:

Etapa 1: Criar uma integração de armazenamento em nuvem no Snowflake

Crie uma integração usando o comando CREATE STORAGE INTEGRATION. Uma integração é um objeto Snowflake que delega a responsabilidade pela autenticação do armazenamento externo em nuvem a uma entidade gerada pelo Snowflake (ou seja, uma conta de serviço de armazenamento em nuvem). Para acessar os buckets de armazenamento em nuvem, o Snowflake cria uma conta de serviço que pode receber permissões para acessar os buckets que armazenam seus arquivos de dados.

Uma única integração de armazenamento pode oferecer suporte a vários estágios externos (ou seja, GCS). A URL na definição do estágio deve estar alinhada com os buckets GCS (e caminhos opcionais) especificados para o parâmetro STORAGE_ALLOWED_LOCATIONS.

Nota

Somente administradores de conta (usuários com a função ACCOUNTADMIN) ou uma função com o privilégio global CREATE INTEGRATION podem executar este comando SQL.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/') ]
Copy

Onde:

  • integration_name é o nome da nova integração.

  • bucket é o nome de um bucket de armazenamento em nuvem que armazena seus arquivos de dados (por exemplo, mybucket). O parâmetro STORAGE_ALLOWED_LOCATIONS obrigatório e o parâmetro STORAGE_BLOCKED_LOCATIONS opcional restringem ou bloqueiam o acesso a estes buckets, respectivamente, quando os estágios que fazem referência a esta integração são criados ou modificados.

  • path é um caminho opcional que pode ser usado para proporcionar um controle granular sobre objetos no bucket.

O exemplo a seguir cria uma integração que limita explicitamente os estágios externos que utilizam a integração para referenciar um de dois buckets ou caminhos. Em uma etapa posterior, criaremos um estágio externo que referencia um desses buckets e caminhos.

Os estágios externos adicionais que também utilizam esta integração podem fazer referência aos buckets e caminhos permitidos:

CREATE STORAGE INTEGRATION gcs_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://mybucket1/path1/', 'gcs://mybucket2/path2/')
  STORAGE_BLOCKED_LOCATIONS = ('gcs://mybucket1/path1/sensitivedata/', 'gcs://mybucket2/path2/sensitivedata/');
Copy

Etapa 2: Recuperar a conta de serviço de armazenamento em nuvem para sua conta Snowflake

Execute o comando DESCRIBE INTEGRATION para recuperar a ID para a conta de serviço de armazenamento em nuvem que foi criada automaticamente para sua conta Snowflake:

DESC STORAGE INTEGRATION <integration_name>;
Copy

Onde:

Por exemplo:

DESC STORAGE INTEGRATION gcs_int;

+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------+
| property                    | property_type | property_value                                                              | property_default |
+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------|
| ENABLED                     | Boolean       | true                                                                        | false            |
| STORAGE_ALLOWED_LOCATIONS   | List          | gcs://mybucket1/path1/,gcs://mybucket2/path2/                               | []               |
| STORAGE_BLOCKED_LOCATIONS   | List          | gcs://mybucket1/path1/sensitivedata/,gcs://mybucket2/path2/sensitivedata/   | []               |
| STORAGE_GCP_SERVICE_ACCOUNT | String        | service-account-id@project1-123456.iam.gserviceaccount.com                  |                  |
+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------+
Copy

A propriedade STORAGE_GCP_SERVICE_ACCOUNT na saída mostra a conta do serviço de armazenamento em nuvem criada para sua conta Snowflake (por exemplo, service-account-id@project1-123456.iam.gserviceaccount.com). Fornecemos uma única conta de serviço de armazenamento em nuvem para toda a sua conta Snowflake. Todas as integrações de armazenamento em nuvem utilizam essa conta de serviço.

Etapa 3: Conceder as permissões da conta de serviço para acessar os objetos do bucket

As seguintes instruções passo a passo descrevem como configurar as permissões de acesso IAM ao Snowflake em seu Console Google Cloud Platform para que você possa usar um bucket de armazenamento em nuvem para carregar e descarregar dados:

Criação de uma função IAM personalizada

Crie uma função personalizada que tenha as permissões necessárias para acessar o bucket e obter objetos.

  1. Acesse o Console Google Cloud Platform como editor de projeto.

  2. No painel inicial, escolha IAM & admin » Roles.

  3. Clique em Create Role.

  4. Digite um nome e uma descrição para a função personalizada.

  5. Clique em Add Permissions.

  6. Filtre a lista de permissões e adicione o seguinte da lista:

    Ação(ões)

    Permissões necessárias

    Somente carregamento de dados

    • storage.buckets.get

    • storage.objects.get

    • storage.objects.list

    Carregamento de dados com opção de limpeza, executando o comando REMOVE no estágio

    • storage.buckets.get

    • storage.objects.delete

    • storage.objects.get

    • storage.objects.list

    Carregamento e descarregamento de dados

    • storage.buckets.get (para calcular custos de transferência de dados)

    • storage.objects.create

    • storage.objects.delete

    • storage.objects.get

    • storage.objects.list

    Apenas descarregamento de dados

    • storage.buckets.get

    • storage.objects.create

    • storage.objects.delete

    • storage.objects.list

  7. Clique em Create.

Atribuição da função personalizada à conta de serviço de armazenamento em nuvem

  1. Acesse o Console Google Cloud Platform como editor de projeto.

  2. No painel inicial, escolha Cloud Storage » Browser:

    Bucket List in Google Cloud Platform Console
  3. Selecione um bucket a ser configurado para acesso.

  4. Clique em SHOW INFO PANEL no canto superior direito. O painel de informações do bucket é exibido.

  5. Clique no botão ADD PRINCIPAL.

  6. No campo New principals, procure o nome da conta de serviço a partir da saída DESCRIBE INTEGRATION da Etapa 2: Recuperar a conta de serviço de armazenamento em nuvem para sua conta Snowflake (neste tópico).

    Bucket Information Panel in Google Cloud Platform Console
  7. No menu suspenso Select a role, selecione Custom » <função>, em que <função> é a função de armazenamento em nuvem personalizada que você criou em Criação de uma função IAM personalizada (neste tópico).

  8. Clique no botão Save. O nome da conta de serviço é adicionado ao menu suspenso da função Storage Object Viewer no painel de informações.

    Storage Object Viewer role list in Google Cloud Platform Console

Concessão de permissões de conta do serviço de armazenamento em nuvem para chaves criptográficas do Cloud Key Management Service

Nota

Esta etapa é necessária apenas se seu bucket GCS estiver criptografado usando uma chave armazenada no Google Cloud Key Management Service (Cloud KMS).

  1. Acesse o Console Google Cloud Platform como editor de projeto.

  2. No painel inicial, escolha Security » Cryptographic keys.

  3. Selecione o chaveiro que é atribuído ao seu bucket GCS.

  4. Clique em SHOW INFO PANEL no canto superior direito. O painel de informações do chaveiro é exibido.

  5. Clique no botão ADD PRINCIPAL.

  6. No campo New principals, procure o nome da conta de serviço a partir da saída DESCRIBE INTEGRATION da Etapa 2: Recuperar a conta de serviço de armazenamento em nuvem para sua conta Snowflake (neste tópico).

  7. A partir do menu suspenso Select a role, selecione a função Cloud KMS CrytoKey Encryptor/Decryptor.

  8. Clique no botão Save. O nome da conta de serviço é adicionado ao menu suspenso da função Cloud KMS CrytoKey Encryptor/Decryptor no painel de informações.

Configuração da automação usando GCS Pub/Sub

Pré-requisitos

As instruções neste tópico consideram que os seguintes itens foram criados e configurados:

Conta GCP
  • Tópico Pub/Sub que recebe mensagens de eventos do bucket GCS. Para obter mais informações, consulte Criação de tópico Pub/Sub (neste tópico).

  • Assinatura que recebe mensagens de eventos do tópico Pub/Sub. Para obter mais informações, consulte Criação de assinatura Pub/Sub (neste tópico).

Para instruções, consulte a documentação Pub/Sub.

Snowflake
  • Tabela de destino no banco de dados Snowflake onde seus dados serão carregados.

Criação de tópico Pub/Sub

Crie um tópico Pub/Sub usando Cloud Shell ou Cloud SDK.

Execute o seguinte comando para criar o tópico e habilitá-lo a escutar a atividade no bucket GCS especificado:

$ gsutil notification create -t <topic> -f json gs://<bucket-name> -e OBJECT_FINALIZE -e OBJECT_DELETE
Copy

Onde:

  • <tópico> é o nome para o tópico.

  • <nome-bucket> é o nome do seu bucket GCS.

Se o tópico já existe, o comando o utiliza; caso contrário, é criado um novo tópico.

Para obter mais informações, consulte Uso de notificações Pub/Sub para armazenamento em nuvem na documentação Pub/Sub.

Criação de assinatura Pub/Sub

Crie uma assinatura com entrega por Pull para o tópico Pub/Sub usando a ferramenta de linha de comando gcloud ou a API Cloud Pub/Sub. Para obter instruções, consulte Gerenciamento de tópicos e assinaturas na documentação do Pub/Sub.

Nota

  • Somente as assinaturas Pub/Sub que utilizam a entrega por Pull padrão têm suporte com o Snowflake. A entrega por Push não tem suporte.

Recuperação da ID da assinatura Pub/Sub

A ID de assinatura de tópico Pub/Sub é usada nestas instruções para permitir o acesso do Snowflake às mensagens de eventos.

  1. Acesse o Console Google Cloud Platform como editor de projeto.

  2. No painel inicial, escolha Big Data » Pub/Sub » Subscriptions.

  3. Copiar a ID na coluna Subscription ID para a assinatura do tópico

Etapa 1: Criar uma integração de notificação no Snowflake

Crie uma integração de notificação usando o comando CREATE NOTIFICATION INTEGRATION. A integração da notificação faz referência à sua assinatura Pub/Sub. O Snowflake associa a integração da notificação com uma conta de serviço GCS criada para sua conta. O Snowflake cria uma única conta de serviço que é referenciada por todas as integrações de notificação GCS em sua conta Snowflake.

Nota

  • Somente administradores de conta (usuários com a função ACCOUNTADMIN) ou uma função com o privilégio global CREATE INTEGRATION podem executar este comando SQL.

  • A conta de serviço GCS para integrações de notificação é diferente da conta de serviço criada para integrações de armazenamento.

  • Uma única integração de notificação oferece suporte para uma única assinatura do Google Cloud Pub/Sub. A referência a uma única assinatura do Pub/Sub em múltiplas integrações de notificação pode resultar na ausência de dados nas tabelas de destino porque as notificações de eventos são divididas entre as integrações de notificação.

CREATE NOTIFICATION INTEGRATION <integration_name>
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_SUBSCRIPTION_NAME = '<subscription_id>';
Copy

Onde:

Por exemplo:

CREATE NOTIFICATION INTEGRATION my_notification_int
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_SUBSCRIPTION_NAME = 'projects/project-1234/subscriptions/sub2';
Copy

Etapa 2: Conceder acesso do Snowflake à assinatura Pub/Sub

  1. Execute o comando DESCRIBE INTEGRATION para recuperar a ID da conta de serviço Snowflake:

    DESC NOTIFICATION INTEGRATION <integration_name>;
    
    Copy

    Onde:

    Por exemplo:

    DESC NOTIFICATION INTEGRATION my_notification_int;
    
    Copy
  2. Registre o nome da conta de serviço na coluna GCP_PUBSUB_SERVICE_ACCOUNT, que tem o seguinte formato:

    <service_account>@<project_id>.iam.gserviceaccount.com
    
    Copy
  3. Acesse o Console Google Cloud Platform como editor de projeto.

  4. No painel inicial, escolha Big Data » Pub/Sub » Subscriptions.

  5. Selecione a assinatura a ser configurada para acesso.

  6. Clique em SHOW INFO PANEL no canto superior direito. O painel de informações da assinatura é exibido.

  7. Clique no botão ADD PRINCIPAL.

  8. No campo New principals, procure pelo nome da conta de serviço que você registrou.

  9. A partir do menu suspenso Select a role, selecione Pub/Sub Subscriber.

  10. Clique no botão Save. O nome da conta de serviço é adicionado ao menu suspenso da função Pub/Sub Subscriber no painel de informações.

  11. Navegue até a página Dashboard no Console Cloud e selecione seu projeto na lista suspensa.

  12. Clique no botão ADD PEOPLE TO THIS PROJECT.

  13. Adicione o nome da conta de serviço que você registrou.

  14. A partir do menu suspenso Select a role, selecione Monitoring Viewer.

  15. Clique no botão Save. O nome da conta de serviço é adicionado à função Monitoring Viewer.

Etapa 3: Criar um estágio com uma tabela de diretório incluída

Crie um estágio externo que faça referência ao seu bucket GCS usando o comando CREATE STAGE. O Snowflake lê seus arquivos de dados preparados nos metadados da tabela de diretório. Você também pode usar um estágio externo já existente.

Nota

  • Para configurar o acesso seguro ao local de armazenamento na nuvem, consulte Configuração de acesso seguro ao armazenamento em nuvem (neste tópico).

  • Para fazer referência a uma integração de armazenamento na instrução CREATE STAGE, a função deve ter o privilégio USAGE para o objeto de integração de armazenamento.

-- External stage
CREATE [ OR REPLACE ] [ TEMPORARY ] STAGE [ IF NOT EXISTS ] <external_stage_name>
      <cloud_storage_access_settings>
    [ FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
    [ directoryTable ]
    [ COPY_OPTIONS = ( copyOptions ) ]
    [ COMMENT = '<string_literal>' ]
Copy

Onde:

directoryTable ::=
  [ DIRECTORY = ( ENABLE = { TRUE | FALSE }
                  [ AUTO_REFRESH = { TRUE | FALSE } ]
                  [ NOTIFICATION_INTEGRATION = '<notification_integration_name>' ] ) ]
Copy

Parâmetros da tabela de diretório (directoryTable)

ENABLE = TRUE | FALSE

Especifica se deve ser adicionada uma tabela de diretório ao estágio. Quando o valor é TRUE, uma tabela de diretório é criada com o estágio.

Padrão: FALSE

AUTO_REFRESH = TRUE | FALSE

Especifica se o Snowflake deve permitir acionar atualizações automáticas dos metadados da tabela de diretório quando os arquivos de dados novos ou atualizados estiverem disponíveis no estágio externo nomeado especificado na configuração de [ WITH ] LOCATION =.

TRUE

O Snowflake permite acionar a atualização automática dos metadados da tabela de diretório.

FALSE

O Snowflake não permite acionar atualizações automáticas dos metadados da tabela de diretório. Você deve atualizar manualmente os metadados da tabela de diretório periodicamente usando ALTER STAGE … REFRESH para sincronizar os metadados com a lista atual de arquivos no caminho do estágio.

Padrão: FALSE

NOTIFICATION_INTEGRATION = '<nome_integração_notificação>'

Especifica o nome da integração de notificação usada para atualizar automaticamente os metadados da tabela de diretório usando notificações Pub/Sub. Uma integração de notificação é um objeto Snowflake que fornece uma interface entre o Snowflake e serviços de enfileiramento de mensagens de terceiros.

O nome da integração deve ser fornecido em letras maiúsculas.

O exemplo a seguir cria um estágio chamado mystage no esquema ativo para a sessão do usuário. O URL de armazenamento em nuvem inclui o caminho files. O estágio faz referência a uma integração de armazenamento chamada my_storage_int.

O parâmetro NOTIFICATION_INTEGRATION faz referência à integração de notificação my_notification_int que você criou na Etapa 1: Criar uma integração de notificação no Snowflake:

USE SCHEMA mydb.public;
Copy
CREATE STAGE mystage
  URL='gcs://mybucket/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (
    ENABLE = true
    AUTO_REFRESH = true
    NOTIFICATION_INTEGRATION = 'MY_NOTIFICATION_INT'
  );
Copy

Nota

  • O local de armazenamento no valor de URL deve terminar em uma barra (/).

  • O nome da integração deve ser fornecido em letras maiúsculas.

Quando arquivos de dados novos ou atualizados são adicionados ao local de armazenamento na nuvem, a notificação de evento informa ao Snowflake para verificá-los nos metadados da tabela de diretório.

Etapa 4: Atualizar manualmente os metadados da tabela de diretório

Atualize os metadados em uma tabela de diretório manualmente usando o comando ALTER STAGE.

Sintaxe

ALTER STAGE [ IF EXISTS ] <name> REFRESH [ SUBPATH = '<relative-path>' ]
Copy

Onde:

REFRESH

Acessa os arquivos de dados preparados referenciados na definição da tabela de diretório e atualiza os metadados da tabela:

  • Novos arquivos no caminho são adicionados aos metadados da tabela.

  • As mudanças nos arquivos do caminho são atualizadas nos metadados da tabela.

  • Os arquivos que não estão mais no caminho são removidos dos metadados da tabela.

Atualmente, é necessário executar este comando cada vez que arquivos são adicionados ao estágio, atualizados ou descartados. Esta etapa sincroniza os metadados com o último conjunto de arquivos associados na definição do estágio para a tabela de diretório.

SUBPATH = '<relative-path>'

Opcionalmente, especifique um caminho relativo para atualizar os metadados de um subconjunto específico dos arquivos de dados.

Exemplos

Atualizar manualmente os metadados da tabela de diretório em um estágio chamado mystage:

ALTER STAGE mystage REFRESH;
Copy

Importante

Se esta etapa não for concluída com sucesso pelo menos uma vez após a criação da tabela de diretório, a consulta da tabela de diretório não retornará nenhum resultado até que um evento de notificação acione os metadados da tabela de diretório para serem atualizados automaticamente pela primeira vez.

Etapa 5: Configurar a segurança

Para cada função adicional que será usada para consultar a tabela de diretório, conceda privilégios suficientes de controle de acesso para os vários objetos (ou seja, bancos de dados, esquemas e estágio) usando GRANT <privilégios>:

Objeto

Privilégio

Notas

Banco de dados

USAGE

Esquema

USAGE

Estágio nomeado

USAGE , READ

Formato de arquivo nomeado

USAGE

Opcional; só é necessário se o estágio que você criou fizer referência a um formato de arquivo nomeado.