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

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.

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>/') ]

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

Onde:

• integration_name é o nome da integração que você criou em Etapa 1: Criar uma integração de armazenamento em nuvem no Snowflake (neste tópico).

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:

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.

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

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.

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

Onde:

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

• subscription_id é o nome da assinatura que você registrou em Recuperação da ID da assinatura Pub/Sub.

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

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:

   • integration_name é o nome da integração que você criou em Etapa 1: Criar uma integração de armazenamento no Snowflake.

   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.

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

Onde:

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

Copy

CREATE STAGE mystage
  URL='gcs://mybucket/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (
    ENABLE = true
    AUTO_REFRESH = true
    NOTIFICATION_INTEGRATION = 'MY_NOTIFICATION_INT'
  );

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.

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

ALTER STAGE mystage REFRESH;

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>: