Aplique recomendações de partição e cluster

Neste documento, mostramos como aplicar recomendações de partição e cluster às tabelas do BigQuery.

Limitações

O recomendador de particionamento e clustering não oferece suporte a tabelas do BigQuery com SQL legado. Ao gerar uma recomendação, o recomendador exclui todas as consultas de SQL legadas na análise. Além disso, aplicar recomendações de partição nas tabelas do BigQuery com SQL legado interrompe os fluxos de trabalho do SQL legado nessa tabela.

Antes de aplicar recomendações de partição, migre os fluxos de trabalho do SQL legado para o GoogleSQL.

O recomendador de particionamento e clustering não é compatível com recursos armazenados nas seguintes regiões:

  • europe-central2, europe-west8, europe-west9, europe-west12, europe-north1, europe-southwest1
  • us-east1, us-east5, us-south1
  • me-central1, me-central2, me-west1
  • australia-southeast2
  • southamerica-west1

Antes de começar

  1. Verifique se a API Recommender está ativada.
  2. Verifique se você tem as permissões necessárias do Identity and Access Management (IAM).

Aplicar recomendações de clusters

É possível aplicar recomendações de cluster aplicando clusters a uma cópia da tabela original, aplicando-as diretamente à tabela original ou usando visualizações materializadas.

Aplicar recomendações de cluster a uma tabela copiada

Ao aplicar recomendações de cluster a uma tabela do BigQuery, primeiro copie a tabela original e aplique a recomendação à tabela copiada. Esse método garante que os dados originais sejam preservados se você precisar reverter a alteração para a configuração do clustering.

É possível usar esse método para aplicar recomendações de cluster a tabelas não particionadas e particionadas.

  1. No console do Google Cloud, acesse a página do BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, crie uma tabela vazia com os mesmos metadados (incluindo as especificações de clustering) da tabela original usando o operador LIKE:

    CREATE TABLE DATASET.COPIED_TABLE
    LIKE DATASET.ORIGINAL_TABLE
    

    Substitua:

    • DATASET: o nome do conjunto de dados que contém a tabela. Por exemplo, mydataset
    • COPIED_TABLE: um nome para a tabela copiada, por exemplo, copy_mytable
    • ORIGINAL_TABLE: o nome da tabela original, por exemplo, mytable
  3. No console do Google Cloud, abra o editor do Cloud Shell.

    Ativar o Cloud Shell

  4. No editor do Cloud Shell, atualize a especificação de cluster da tabela copiada para corresponder ao cluster recomendado usando o comando bq update:

     bq update --clustering_fields=CLUSTER_COLUMN DATASET.COPIED_TABLE 

    Substitua CLUSTER_COLUMN pela coluna em que você está agrupando, por exemplo, mycolumn.

    Também é possível chamar o método de API tables.update ou tables.patch para modificar a especificação de clustering.

  5. No editor de consultas, recupere o esquema da tabela com a configuração de particionamento e clustering da tabela original, se houver algum particionamento ou clustering. É possível recuperar o esquema na visualização INFORMATION_SCHEMA.TABLES da tabela original:

    SELECT
      ddl
    FROM
      DATASET.INFORMATION_SCHEMA.TABLES
    WHERE
      table_name = 'DATASET.ORIGINAL_TABLE;'
    

    A saída é a instrução de linguagem de definição de dados (DDL, na sigla em inglês) completa de ORIGINAL_TABLE, incluindo a cláusula PARTITION BY. Para mais informações sobre os argumentos na saída DDL, consulte a instrução CREATE TABLE.

    A saída DDL indica o tipo de particionamento na tabela original:

    Tipo de particionamento Exemplo de saída
    Não particionado A cláusula PARTITION BY está ausente.
    Particionado por coluna da tabela PARTITION BY c0
    PARTITION BY DATE(c0)
    PARTITION BY DATETIME_TRUNC(c0, MONTH)
    Particionado por tempo de processamento PARTITION BY _PARTITIONDATE
    PARTITION BY DATETIME_TRUNC(_PARTITIONTIME, MONTH)
  6. Processar dados na tabela copiada. O processo usado é baseado no tipo de partição.

    • Se a tabela original não estiver particionada ou particionada por uma coluna da tabela, faça o processamento dos dados da tabela original na tabela copiada:
      INSERT INTO DATASET.COPIED_TABLE
      SELECT * FROM DATASET.ORIGINAL_TABLE
      
    • Se a tabela original for particionada por tempo de processamento, siga estas etapas:

      1. Recupere a lista de colunas para formar a expressão de ingestão de dados usando a visualização INFORMATION_SCHEMA.COLUMNS:

        SELECT
        ARRAY_TO_STRING((
        SELECT
          ARRAY(
          SELECT
            column_name
          FROM
            DATASET.INFORMATION_SCHEMA.COLUMNS
          WHERE
            table_name = 'ORIGINAL_TABLE')), ", ")
        

        A saída é uma lista separada por vírgulas de nomes de colunas.

      2. Faça a ingestão dos dados da tabela original na tabela de cópia:

        INSERT DATASET.COPIED_TABLE (COLUMN_NAMES, _PARTITIONTIME)
        SELECT *, _PARTITIONTIME FROM DATASET.ORIGINAL_TABLE
        

        Substitua COLUMN_NAMES pela lista de colunas que foram a saída da etapa anterior, separadas por vírgulas, por exemplo, col1, col2, col3.

    Agora você tem uma tabela de cópia em cluster com os mesmos dados da tabela original. Nas próximas etapas, você vai substituir a tabela original por uma nova em cluster.

  7. Renomear a tabela original como uma tabela de backup:

    ALTER TABLE DATASET.ORIGINAL_TABLE
    RENAME TO DATASET.BACKUP_TABLE
    

    Substitua BACKUP_TABLE por um nome para sua tabela de backup, por exemplo, backup_mytable.

  8. Renomeie a tabela de cópia como a tabela original:

    ALTER TABLE DATASET.COPIED_TABLE
    RENAME TO DATASET.ORIGINAL_TABLE
    

    Sua tabela original está agrupada de acordo com a recomendação do cluster.

Recomendamos que você analise a tabela em cluster para garantir que todas as funções dela funcionem conforme o esperado. Muitas funções de tabela provavelmente estão vinculadas ao ID da tabela, e não ao nome da tabela. Portanto, é recomendável revisar as seguintes funções antes de continuar:

Se surgirem problemas, migre manualmente os artefatos afetados para a nova tabela.

Depois de analisar a tabela em cluster, você tem a opção de excluí-la com o seguinte comando:
    DROP TABLE DATASET.BACKUP_TABLE
    

Aplique recomendações de clusters diretamente

É possível aplicar as recomendações do cluster diretamente a uma tabela atual do BigQuery. Esse método é mais rápido do que aplicar recomendações a uma tabela copiada, mas não preserva uma tabela de backup.

Siga estas etapas para aplicar uma nova especificação de clustering a tabelas não particionadas ou particionadas.

  1. Na ferramenta bq, atualize a especificação de clustering da tabela para corresponder ao novo clustering:

     bq update --clustering_fields=CLUSTER_COLUMN DATASET.ORIGINAL_TABLE 

    Substitua:

    • CLUSTER_COLUMN: a coluna que você está agrupando, por exemplo, mycolumn.
    • DATASET: o nome do conjunto de dados que contém a tabela. Por exemplo, mydataset
    • ORIGINAL_TABLE: o nome da tabela original, por exemplo, mytable

    Também é possível chamar o método de API tables.update ou tables.patch para modificar a especificação de clustering.

  2. Para agrupar todas as linhas de acordo com a nova especificação de clustering, execute a seguinte instrução UPDATE:

    UPDATE DATASET.ORIGINAL_TABLE SET CLUSTER_COLUMN=CLUSTER_COLUMN WHERE true
    

Aplique recomendações de clusters usando visualizações materializadas

É possível criar uma visualização materializada da tabela para armazenar dados da tabela original com a recomendação aplicada. Usar visualizações materializadas para aplicar recomendações garante que os dados em cluster sejam atualizados usando atualizações automáticas. Há considerações de preço ao consultar, manter e armazenar visualizações materializadas. Para saber como criar uma visualização materializada em cluster, consulte Visualizações materializadas em cluster.

Aplicar recomendações de partição

É possível aplicar recomendações de partição aplicando partições a uma cópia da tabela original.

Aplicar recomendações de partição a uma tabela copiada

Ao aplicar recomendações de partição a uma tabela do BigQuery, primeiro copie a tabela original e aplique a recomendação à tabela copiada. Essa abordagem garante que os dados originais sejam preservados se você precisar reverter uma partição.

O procedimento a seguir usa um exemplo de recomendação para particionar uma tabela pela unidade de tempo da partição DAY.

  1. Crie uma tabela copiada usando as recomendações de partição:

    CREATE TABLE DATASET.COPIED_TABLE
    PARTITION BY DATE_TRUNC(PARTITION_COLUMN, DAY)
    AS SELECT * FROM DATASET.ORIGINAL_TABLE
    

    Substitua:

    • DATASET: o nome do conjunto de dados que contém a tabela. Por exemplo, mydataset
    • COPIED_TABLE: um nome para a tabela copiada, por exemplo, copy_mytable
    • PARTITION_COLUMN: a coluna que você está particionando, por exemplo, mycolumn.

    Para mais informações sobre como criar tabelas particionadas, consulte Como criar tabelas particionadas.

  2. Renomear a tabela original como uma tabela de backup:

    ALTER TABLE DATASET.ORIGINAL_TABLE
    RENAME TO DATASET.BACKUP_TABLE
    

    Substitua BACKUP_TABLE por um nome para sua tabela de backup, por exemplo, backup_mytable.

  3. Renomeie a tabela de cópia como a tabela original:

    ALTER TABLE DATASET.COPIED_TABLE
    RENAME TO DATASET.ORIGINAL_TABLE
    

    Sua tabela original agora está particionada de acordo com a recomendação do cluster.

Recomendamos que você revise a tabela particionada para garantir que todas as funções dela funcionem conforme o esperado. Muitas funções de tabela provavelmente estão vinculadas ao ID da tabela, e não ao nome da tabela. Portanto, é recomendável revisar as seguintes funções antes de continuar:

Se surgirem problemas, migre manualmente os artefatos afetados para a nova tabela.

Depois de revisar a tabela particionada, você tem a opção de excluí-la com o seguinte comando:
    DROP TABLE DATASET.BACKUP_TABLE
    

Preços

Quando você aplica uma recomendação a uma tabela, é possível que haja os seguintes custos:

  • Custos de processamento. Ao aplicar uma recomendação, você executa uma consulta de linguagem de definição de dados (DDL, na sigla em inglês) ou linguagem de manipulação de dados (DML, na sigla em inglês) no seu projeto do BigQuery.
  • Custos de armazenamento. Caso use o método de cópia de uma tabela, você usará armazenamento extra para a tabela copiada (ou de backup).

As cobranças padrão de processamento e armazenamento são aplicáveis dependendo da conta de faturamento associada ao projeto. Para mais informações, consulte preços do BigQuery.