No Looker, uma tabela derivada é uma consulta em que os resultados são usados como se fosse uma tabela real no banco de dados.
Por exemplo, é possível ter uma tabela de banco de dados chamada orders
com muitas colunas. Você quer computar algumas métricas agregadas no nível do cliente, como quantos pedidos cada cliente fez ou quando cada cliente fez seu primeiro pedido. Usando uma tabela derivada nativa ou uma tabela derivada baseada em SQL, é possível criar uma nova tabela de banco de dados chamada customer_order_summary
que inclua essas métricas.
É possível trabalhar com a tabela derivada customer_order_summary
como se fosse qualquer outra tabela no banco de dados.
Para casos de uso comuns de tabelas derivadas, acesse Livros de receitas do Looker: como aproveitar ao máximo as tabelas derivadas no Looker.
Tabelas derivadas nativas e tabelas derivadas baseadas em SQL
Para criar uma tabela derivada no seu projeto do Looker, use o parâmetro derived_table
em um parâmetro view. No parâmetro derived_table
, é possível definir a consulta para a tabela derivada de duas maneiras:
- Em uma tabela derivada nativa, você define a tabela derivada com uma consulta baseada no LookML.
- Para uma tabela derivada baseada em SQL, você define a tabela derivada com uma consulta SQL.
Por exemplo, os arquivos de visualização a seguir mostram como usar o LookML para criar uma visualização de uma tabela derivada do customer_order_summary
. As duas versões do LookML ilustram como é possível criar tabelas derivadas equivalentes usando o LookML ou o SQL para definir a consulta da tabela derivada:
- A tabela derivada nativa define a consulta com LookML no parâmetro
explore_source
. Neste exemplo, a consulta é baseada em uma visualizaçãoorders
atual, definida em um arquivo separado não mostrado. A consultaexplore_source
na tabela derivada nativa traz os camposcustomer_id
,first_order
etotal_amount
do arquivo de visualizaçãoorders
. - A tabela derivada baseada em SQL define a consulta usando SQL no parâmetro
sql
. Neste exemplo, a consulta SQL é uma consulta direta da tabelaorders
no banco de dados.
view: customer_order_summary { derived_table: { explore_source: orders { column: customer_id { field: orders.customer_id } column: first_order { field: orders.first_order } column: total_amount { field: orders.total_amount } } } dimension: customer_id { type: number primary_key: yes sql: ${TABLE}.customer_id ;; } dimension_group: first_order { type: time timeframes: [date, week, month] sql: ${TABLE}.first_order ;; } dimension: total_amount { type: number value_format: "0.00" sql: ${TABLE}.total_amount ;; } }
view: customer_order_summary { derived_table: { sql: SELECT customer_id, MIN(DATE(time)) AS first_order, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ;; } dimension: customer_id { type: number primary_key: yes sql: ${TABLE}.customer_id ;; } dimension_group: first_order { type: time timeframes: [date, week, month] sql: ${TABLE}.first_order ;; } dimension: total_amount { type: number value_format: "0.00" sql: ${TABLE}.total_amount ;; } }
As duas versões criam uma visualização chamada customer_order_summary
, que é baseada na tabela orders
, com as colunas customer_id
, first_order,
e total_amount
.
Além do parâmetro derived_table
e dos subparâmetros, essa visualização customer_order_summary
funciona como qualquer outro arquivo de visualização. Se você definir a consulta da tabela derivada com o LookML ou com o SQL, é possível criar medidas e dimensões do LookML baseadas nas colunas da tabela derivada.
Depois de definir a tabela derivada, ela pode ser usada como qualquer outra tabela do seu banco de dados.
Tabelas derivadas nativas
As tabelas derivadas nativas são baseadas em consultas que você define usando termos do LookML. Para criar uma tabela derivada nativa, use o parâmetro explore_source
dentro do parâmetro derived_table
de um parâmetro view. Para criar as colunas da tabela derivada nativa, consulte as dimensões ou medições do LookML no seu modelo. Veja o arquivo de visualização em tabela derivada no exemplo anterior.
Em comparação com as tabelas derivadas baseadas em SQL, as tabelas derivadas nativas são muito mais fáceis de ler e entender à medida que você modela os dados.
Consulte a página de documentação Como criar tabelas derivadas nativas para ver detalhes sobre como criar tabelas derivadas nativas.
Tabelas derivadas baseadas em SQL
Para criar uma tabela derivada baseada em SQL, você define uma consulta em termos de SQL, criando colunas na tabela usando uma consulta SQL. Não é possível fazer referência às dimensões e medições do LookML em uma tabela derivada baseada em SQL. Veja o arquivo de visualização em tabela derivada com base em SQL no exemplo anterior.
Geralmente, você define a consulta SQL usando o parâmetro sql
dentro do derived_table
de um parâmetro view.
Um atalho útil para criar consultas baseadas em SQL no Looker é usar o SQL Runner para criar a consulta SQL e transformá-la em uma definição de tabela derivada.
Alguns casos extremos não permitem o uso do parâmetro sql
. Nesses casos, o Looker é compatível com os seguintes parâmetros para definir uma consulta SQL para tabelas derivadas permanentes (PDTs):
create_process
: quando você usa o parâmetrosql
para uma PDT, em segundo plano o Looker une a instrução de linguagem de definição de dados (DDL) do dialeto ao redor da consulta para criar a PDT da consulta SQL.CREATE TABLE
Alguns dialetos não oferecem suporte a uma instrução SQLCREATE TABLE
em uma única etapa. Para esses dialetos, não é possível criar uma PDT com o parâmetrosql
. Em vez disso, é possível usar o parâmetrocreate_process
para criar uma PDT em várias etapas. Consulte a página de documentação do parâmetrocreate_process
para informações e exemplos.sql_create
: se o caso de uso exigir comandos DDL personalizados e o dialeto for compatível com DDL (por exemplo, o BigQuery ML preditivo do Google), você poderá usar o parâmetrosql_create
para criar uma PDT em vez de usar o parâmetrosql
. Para informações e exemplos, consulte a página de documentação dosql_create
.
Se você estiver usando o parâmetro sql
, create_process
ou sql_create
, em todos esses casos, estará definindo a tabela derivada com uma consulta SQL. Portanto, todas elas são consideradas tabelas derivadas baseadas em SQL.
Ao definir uma tabela derivada baseada em SQL, use AS
para atribuir a cada coluna um alias limpo. Isso ocorre porque você precisará fazer referência aos nomes das colunas do conjunto de resultados nas dimensões, como ${TABLE}.first_order
. É por isso que o exemplo anterior usa MIN(DATE(time)) AS first_order
em vez de simplesmente MIN(DATE(time))
.
Tabelas derivadas temporárias e persistentes
Além da distinção entre tabelas derivadas nativas e tabelas derivadas baseadas em SQL, há também uma distinção entre uma tabela derivada temporária, que não é gravada no banco de dados, e uma tabela derivada persistente (PDT), que é gravada em um esquema no seu banco de dados.
As tabelas derivadas nativas e as baseadas em SQL podem ser temporárias ou permanentes.
Tabelas derivadas temporárias
As tabelas derivadas mostradas anteriormente são exemplos de tabelas derivadas temporárias. Elas são temporárias, porque não há uma estratégia de persistência definida no parâmetro derived_table
.
As tabelas derivadas temporárias não são gravadas no banco de dados. Quando um usuário executa uma consulta de exploração que envolve uma ou mais tabelas derivadas, o Looker constrói uma consulta SQL usando uma combinação específica de dialeto do SQL para as tabelas derivadas, além dos campos, mesclagens e valores de filtro solicitados. Se a combinação já foi executada antes e os resultados ainda são válidos no cache, o Looker usa os resultados armazenados em cache. Consulte a página de documentação Consultas de armazenamento em cache para mais informações sobre esse assunto no Looker.
Caso contrário, se o Looker não puder usar resultados armazenados em cache, ele precisará executar uma nova consulta no seu banco de dados sempre que um usuário solicitar dados de uma tabela derivada temporária. Por isso, você deve garantir que suas tabelas derivadas temporárias tenham bom desempenho e não sobrecarregue seu banco de dados. Nos casos em que a consulta leva algum tempo para ser executada, uma PDT costuma ser uma opção melhor.
Dialetos de banco de dados compatíveis com tabelas derivadas temporárias
Para que o Looker ofereça suporte a tabelas derivadas no projeto do Looker, o dialeto do banco de dados também precisa ser compatível. A tabela a seguir mostra quais dialetos são compatíveis com tabelas derivadas na versão mais recente do Looker:
Dialeto | Compatível? |
---|---|
Avalanche Actian | Sim |
Amazon Athena | Sim |
MySQL do Amazon Aurora | Sim |
Amazon Redshift | Sim |
Apache Druid | Sim |
Apache Druid 0.13 ou superior | Sim |
Apache Druid 0.18 ou superior | Sim |
Apache Hive 2.3 ou superior | Sim |
Apache Hive 3.1.2 ou posterior | Sim |
Apache Spark 3 ou mais recente | Sim |
ClickHouse | Sim |
Cloudera Impala 3.1 ou superior | Sim |
Cloudera Impala 3.1+ com driver nativo | Sim |
Cloudera Impala com driver nativo | Sim |
DataVirtuality | Sim |
Databricks | Sim |
Denodo 7 | Sim |
Denodo 8 | Sim |
Dremio | Sim |
Dremio 11 ou superior | Sim |
Exasol | Sim |
Bola de fogo | Sim |
SQL legado do Google BigQuery | Sim |
SQL padrão do Google BigQuery | Sim |
PostgreSQL do Google Cloud | Sim |
Google Cloud SQL | Sim |
Google Spanner (em inglês) | Sim |
Greenplum | Sim |
HyperSQL | Sim |
IBM Netezza | Sim |
MariaDB | Sim |
PostgreSQL do Microsoft Azure | Sim |
Banco de dados SQL do Microsoft Azure | Sim |
Análises do Microsoft Azure Synapse | Sim |
Microsoft SQL Server 2008 ou superior | Sim |
Microsoft SQL Server 2012 ou posterior | Sim |
Microsoft SQL Server 2016 | Sim |
Microsoft SQL Server 2017 ou posterior | Sim |
MongoBI | Sim |
MySQL | Sim |
MySQL 8.0.12 ou mais recente | Sim |
Oracle | Sim |
ADWC da Oracle | Sim |
PostgreSQL 9.5 ou mais recente | Sim |
PostgreSQL anterior à 9.5 | Sim |
PrestoDB | Sim |
PrestoSQL | Sim |
SAP HANA 2 ou posterior | Sim |
SingleStore | Sim |
SingleStore 7 ou superior | Sim |
Snowflake | Sim |
Teradata | Sim |
Trino | Sim |
Vetor | Sim |
Vertica | Sim |
Tabelas derivadas persistentes
Uma tabela derivada persistente (PDT) é uma tabela derivada que é gravada em um esquema de rascunho no seu banco de dados e gerada novamente de acordo com a programação que você especificar com uma estratégia de persistência.
Uma PDT pode ser uma tabela derivada nativa ou uma tabela derivada baseada em SQL.
Requisitos para TDPs
Para usar tabelas derivadas permanentes (PDTs) no seu projeto do Looker, você precisa do seguinte:
- Um dialeto de banco de dados que oferece suporte a TDPs. Consulte a seção Dialetos de banco de dados compatíveis para PDTs mais adiante nesta página para conferir as listas de dialetos que oferecem suporte a tabelas derivadas persistentes baseadas em SQL e tabelas derivadas nativas persistentes.
- Um esquema inicial no banco de dados. Pode ser qualquer esquema em seu banco de dados, mas recomendamos criar um esquema novo que será usado somente para essa finalidade. O administrador do banco de dados precisa configurar o esquema com permissão de gravação para o usuário do banco de dados do Looker.
- Uma conexão do Looker configurada com a opção Ativar PDTs ativada. Isso geralmente é feito quando você configura inicialmente a conexão do Looker (consulte a página de documentação de dialetos do Looker para instruções sobre o dialeto do banco de dados), mas também é possível ativar as PDTs na conexão após a configuração inicial.
Dialetos de banco de dados com suporte para TDPs
Para que o Looker ofereça suporte a tabelas derivadas persistentes (PDTs, na sigla em inglês) no seu projeto do Looker, o dialeto do banco de dados também precisa ser compatível com elas.
Para oferecer suporte a qualquer tipo de TDP (com base em LookML ou SQL), o dialeto precisa oferecer suporte a gravações no banco de dados, entre outros requisitos. Há algumas configurações de bancos de dados somente leitura que não permitem que a persistência funcione (geralmente bancos de dados de réplica de hot-swap do Postgres). Nesses casos, é possível usar tabelas derivadas temporárias.
A tabela a seguir mostra os dialetos com suporte a tabelas derivadas permanentes baseadas em SQL na versão mais recente do Looker:
Dialeto | Compatível? |
---|---|
Avalanche Actian | Sim |
Amazon Athena | Sim |
MySQL do Amazon Aurora | Sim |
Amazon Redshift | Sim |
Apache Druid | Não |
Apache Druid 0.13 ou superior | Não |
Apache Druid 0.18 ou superior | Não |
Apache Hive 2.3 ou superior | Sim |
Apache Hive 3.1.2 ou posterior | Sim |
Apache Spark 3 ou mais recente | Sim |
ClickHouse | Não |
Cloudera Impala 3.1 ou superior | Sim |
Cloudera Impala 3.1+ com driver nativo | Sim |
Cloudera Impala com driver nativo | Sim |
DataVirtuality | Não |
Databricks | Sim |
Denodo 7 | Não |
Denodo 8 | Não |
Dremio | Não |
Dremio 11 ou superior | Não |
Exasol | Sim |
Bola de fogo | Não |
SQL legado do Google BigQuery | Sim |
SQL padrão do Google BigQuery | Sim |
PostgreSQL do Google Cloud | Sim |
Google Cloud SQL | Sim |
Google Spanner (em inglês) | Não |
Greenplum | Sim |
HyperSQL | Não |
IBM Netezza | Sim |
MariaDB | Sim |
PostgreSQL do Microsoft Azure | Sim |
Banco de dados SQL do Microsoft Azure | Sim |
Análises do Microsoft Azure Synapse | Sim |
Microsoft SQL Server 2008 ou superior | Sim |
Microsoft SQL Server 2012 ou posterior | Sim |
Microsoft SQL Server 2016 | Sim |
Microsoft SQL Server 2017 ou posterior | Sim |
MongoBI | Não |
MySQL | Sim |
MySQL 8.0.12 ou mais recente | Sim |
Oracle | Sim |
ADWC da Oracle | Sim |
PostgreSQL 9.5 ou mais recente | Sim |
PostgreSQL anterior à 9.5 | Sim |
PrestoDB | Sim |
PrestoSQL | Sim |
SAP HANA 2 ou posterior | Sim |
SingleStore | Sim |
SingleStore 7 ou superior | Sim |
Snowflake | Sim |
Teradata | Sim |
Trino | Sim |
Vetor | Sim |
Vertica | Sim |
Para oferecer suporte a tabelas derivadas nativas permanentes (que têm consultas baseadas em LookML), o dialeto também precisa oferecer suporte a uma função DDL CREATE TABLE
. Confira uma lista dos dialetos com suporte a tabelas derivadas nativas (baseadas em LookML) persistentes na versão mais recente do Looker:
Dialeto | Compatível? |
---|---|
Avalanche Actian | Sim |
Amazon Athena | Sim |
MySQL do Amazon Aurora | Sim |
Amazon Redshift | Sim |
Apache Druid | Não |
Apache Druid 0.13 ou superior | Não |
Apache Druid 0.18 ou superior | Não |
Apache Hive 2.3 ou superior | Sim |
Apache Hive 3.1.2 ou posterior | Sim |
Apache Spark 3 ou mais recente | Sim |
ClickHouse | Não |
Cloudera Impala 3.1 ou superior | Sim |
Cloudera Impala 3.1+ com driver nativo | Sim |
Cloudera Impala com driver nativo | Sim |
DataVirtuality | Não |
Databricks | Sim |
Denodo 7 | Não |
Denodo 8 | Não |
Dremio | Não |
Dremio 11 ou superior | Não |
Exasol | Sim |
Bola de fogo | Não |
SQL legado do Google BigQuery | Sim |
SQL padrão do Google BigQuery | Sim |
PostgreSQL do Google Cloud | Sim |
Google Cloud SQL | Não |
Google Spanner (em inglês) | Não |
Greenplum | Sim |
HyperSQL | Não |
IBM Netezza | Sim |
MariaDB | Sim |
PostgreSQL do Microsoft Azure | Sim |
Banco de dados SQL do Microsoft Azure | Sim |
Análises do Microsoft Azure Synapse | Sim |
Microsoft SQL Server 2008 ou superior | Sim |
Microsoft SQL Server 2012 ou posterior | Sim |
Microsoft SQL Server 2016 | Sim |
Microsoft SQL Server 2017 ou posterior | Sim |
MongoBI | Não |
MySQL | Sim |
MySQL 8.0.12 ou mais recente | Sim |
Oracle | Sim |
ADWC da Oracle | Sim |
PostgreSQL 9.5 ou mais recente | Sim |
PostgreSQL anterior à 9.5 | Sim |
PrestoDB | Sim |
PrestoSQL | Sim |
SAP HANA 2 ou posterior | Sim |
SingleStore | Sim |
SingleStore 7 ou superior | Sim |
Snowflake | Sim |
Teradata | Sim |
Trino | Sim |
Vetor | Sim |
Vertica | Sim |
Como criar TDPs de forma incremental
Uma PDT incremental é uma tabela derivada persistente (PDT) que o Looker cria anexando dados novos à tabela em vez de recriar a tabela inteira.
Se o dialeto for compatível com TDPs incrementais e a TDP usar uma estratégia de persistência baseada em gatilho (datagroup_trigger
, sql_trigger_value
ou interval_trigger
), será possível definir a TDP como incremental.
Consulte a página de documentação de TDPs incrementais para mais informações.
Dialetos de banco de dados com suporte para TDPs incrementais
Para que o Looker ofereça suporte a TDPs incrementais no seu projeto do Looker, o dialeto do banco de dados também precisa ser compatível com elas. A tabela abaixo mostra quais dialetos oferecem suporte a PDTs incrementais na versão mais recente do Looker:
Dialeto | Compatível? |
---|---|
Avalanche Actian | Não |
Amazon Athena | Não |
MySQL do Amazon Aurora | Não |
Amazon Redshift | Sim |
Apache Druid | Não |
Apache Druid 0.13 ou superior | Não |
Apache Druid 0.18 ou superior | Não |
Apache Hive 2.3 ou superior | Não |
Apache Hive 3.1.2 ou posterior | Não |
Apache Spark 3 ou mais recente | Não |
ClickHouse | Não |
Cloudera Impala 3.1 ou superior | Não |
Cloudera Impala 3.1+ com driver nativo | Não |
Cloudera Impala com driver nativo | Não |
DataVirtuality | Não |
Databricks | Sim |
Denodo 7 | Não |
Denodo 8 | Não |
Dremio | Não |
Dremio 11 ou superior | Não |
Exasol | Não |
Bola de fogo | Não |
SQL legado do Google BigQuery | Não |
SQL padrão do Google BigQuery | Sim |
PostgreSQL do Google Cloud | Sim |
Google Cloud SQL | Não |
Google Spanner (em inglês) | Não |
Greenplum | Sim |
HyperSQL | Não |
IBM Netezza | Não |
MariaDB | Não |
PostgreSQL do Microsoft Azure | Sim |
Banco de dados SQL do Microsoft Azure | Não |
Análises do Microsoft Azure Synapse | Sim |
Microsoft SQL Server 2008 ou superior | Não |
Microsoft SQL Server 2012 ou posterior | Não |
Microsoft SQL Server 2016 | Não |
Microsoft SQL Server 2017 ou posterior | Não |
MongoBI | Não |
MySQL | Sim |
MySQL 8.0.12 ou mais recente | Sim |
Oracle | Não |
ADWC da Oracle | Não |
PostgreSQL 9.5 ou mais recente | Sim |
PostgreSQL anterior à 9.5 | Sim |
PrestoDB | Não |
PrestoSQL | Não |
SAP HANA 2 ou posterior | Não |
SingleStore | Não |
SingleStore 7 ou superior | Não |
Snowflake | Sim |
Teradata | Não |
Trino | Não |
Vetor | Não |
Vertica | Sim |
Como criar TDPs
Para transformar uma tabela derivada em uma tabela derivada persistente (PDT, na sigla em inglês), defina uma estratégia de persistência. Para otimizar a performance, você também precisa adicionar uma estratégia de otimização.
Estratégias de persistência
A persistência de uma tabela derivada pode ser gerenciada pelo Looker ou, para dialetos compatíveis com visualizações materializadas, pelo seu banco de dados usando visualizações materializadas.
Para tornar uma tabela derivada persistente, adicione um dos parâmetros a seguir à definição de derived_table
:
- Parâmetros de persistência gerenciados pelo Looker:
- Parâmetros de persistência gerenciados pelo banco de dados:
Com estratégias de persistência baseadas em gatilho (datagroup_trigger
, sql_trigger_value
e interval_trigger
), o Looker mantém a PDT no banco de dados até que ela seja acionada para recriação. Quando a TDP é acionada, o Looker a recria para substituir a versão anterior. Isso significa que, com as TDPs baseadas em gatilho, os usuários não vão precisar esperar pela criação da TDP para receber respostas a consultas de Análise da PDT.
datagroup_trigger
Os grupos de dados são o método mais flexível de criar persistência. Se você definiu um grupo de dados com sql_trigger
ou interval_trigger
, pode usar o parâmetro datagroup_trigger
para iniciar a recriação das tabelas derivadas permanentes (PDTs, na sigla em inglês).
O Looker mantém a TDP no banco de dados até que o grupo de dados seja acionado. Quando o grupo de dados é acionado, o Looker recria a PDT para substituir a versão anterior. Isso significa que, na maioria dos casos, os usuários não precisarão esperar a criação da PDT. Se um usuário solicitar dados da PDT enquanto ela estiver sendo criada e os resultados da consulta não estiverem no cache, o Looker retornará dados da PDT atual até que a nova PDT seja criada. Para uma visão geral dos grupos de dados, consulte Como armazenar consultas em cache.
Consulte a seção O regenerador do Looker para mais informações sobre como ele cria PDTs.
sql_trigger_value
O parâmetro sql_trigger_value
aciona a nova geração de uma tabela derivada persistente (PDT) com base em uma instrução SQL fornecida por você. Se o resultado da instrução SQL for diferente do valor anterior, a PDT será gerada novamente. Caso contrário, a TDP atual é mantida no banco de dados. Isso significa que, na maioria dos casos, os usuários não precisarão esperar a criação da PDT. Se um usuário solicitar dados da PDT enquanto ela estiver sendo criada e os resultados da consulta não estiverem no cache, o Looker retornará dados da PDT atual até que a nova PDT seja criada.
Consulte a seção O regenerador do Looker para mais informações sobre como ele cria PDTs.
interval_trigger
O parâmetro interval_trigger
aciona a nova geração de uma tabela derivada persistente (PDT) com base em um intervalo de tempo fornecido, como "24 hours"
ou "60 minutes"
. Assim como no parâmetro sql_trigger
, isso significa que a PDT costuma ser pré-criada quando os usuários a consultam. Se um usuário solicitar dados da PDT enquanto ela estiver sendo criada e os resultados da consulta não estiverem no cache, o Looker retornará dados da PDT atual até que a nova PDT seja criada.
persist_for
Outra opção é usar o parâmetro persist_for
para definir por quanto tempo a tabela derivada precisa ser armazenada antes de ser marcada como expirada. Assim, ela não é mais usada em consultas e é descartada do banco de dados.
Uma tabela derivada persistente (PDT) persist_for
é criada quando um usuário executa uma consulta nela pela primeira vez. Em seguida, o Looker mantém a TDP no banco de dados pelo período especificado no parâmetro persist_for
da PDT. Se um usuário consultar a PDT no tempo persist_for
, o Looker usará os resultados armazenados em cache, se possível, ou executará a consulta na PDT.
Após o período persist_for
, o Looker limpa a PDT do banco de dados, e ela será recriada na próxima vez que um usuário a consultar, o que significa que a consulta precisará aguardar a recriação.
As PDTs que usam persist_for
não são recriadas automaticamente pelo regenerador do Looker, exceto no caso de uma cascade de dependência de PDTs. Quando uma tabela persist_for
faz parte de uma cascata de dependências com PDTs baseadas em gatilho (PDTs que usam a estratégia de persistência datagroup_trigger
, interval_trigger
ou sql_trigger_value
), o regenerador monitora e recria a tabela persist_for
para recriar outras tabelas na cascata. Consulte a seção Como o Looker cria tabelas derivadas em cascata nesta página.
materialized_view: yes
As visualizações materializadas permitem usar a funcionalidade do banco de dados para manter tabelas derivadas no projeto do Looker. Se o dialeto do banco de dados aceitar visualizações materializadas e a conexão do Looker estiver configurada com a opção Ativar PDTs ativada, especifique materialized_view: yes
para uma tabela derivada para criar uma visualização materializada. As visualizações materializadas são compatíveis com tabelas derivadas nativas e tabelas derivadas baseadas em SQL.
Semelhante a uma tabela derivada persistente (PDT), uma visualização materializada é um resultado de consulta que é armazenado como uma tabela no esquema inicial do seu banco de dados. A principal diferença entre uma TDP e uma visualização materializada está no modo como as tabelas são atualizadas:
- Para PDTs, a estratégia de persistência é definida no Looker, e a persistência é gerenciada pelo Looker.
- Para visualizações materializadas, o banco de dados é responsável por manter e atualizar os dados na tabela.
Por esse motivo, a funcionalidade de visualização materializada requer conhecimento avançado do seu dialeto e de seus recursos. Na maioria dos casos, o banco de dados atualiza a visualização materializada sempre que detecta novos dados nas tabelas consultadas pela visualização materializada. As visualizações materializadas são ideais para cenários que exigem dados em tempo real.
Consulte a página de documentação do parâmetro materialized_view
para informações sobre suporte a dialetos, requisitos e considerações importantes.
Estratégias de otimização
Como as tabelas derivadas persistentes (PDTs, na sigla em inglês) são armazenadas no seu banco de dados, otimize-as usando as seguintes estratégias, conforme compatível com seu dialeto:
Por exemplo, para adicionar persistência ao exemplo de tabela derivada, configure-o para recompilar quando o grupo de dados orders_datagroup
for acionado e adicionar índices em customer_id
e first_order
, desta forma:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Se você não adicionar um índice (ou um equivalente para seu dialeto), o Looker vai avisar você para melhorar o desempenho da consulta.
Casos de uso das TDPs
As tabelas derivadas persistentes (PDTs, na sigla em inglês) são úteis porque melhoram o desempenho de uma consulta ao manter os resultados em uma tabela.
Como prática recomendada geral, os desenvolvedores devem tentar modelar dados sem usar PDTs até que seja absolutamente necessário.
Em alguns casos, os dados podem ser otimizados de outras formas. Por exemplo, adicionar um índice ou alterar o tipo de dados de uma coluna pode resolver um problema sem a necessidade de criar uma PDT. Analise os planos de execução de consultas lentas usando a ferramenta Explain from SQL Runner (em inglês).
Além de reduzir o tempo de consulta e a carga do banco de dados em consultas executadas com frequência, há vários outros casos de uso para TDPs, incluindo:
Também é possível usar uma TDP para definir uma chave primária quando não houver uma maneira razoável de identificar uma linha exclusiva em uma tabela como chave primária.
Como usar TDPs para testar otimizações
É possível usar as PDTs para testar diferentes indexações, distribuições e outras opções de otimização sem precisar de um grande volume de suporte dos desenvolvedores de DBA ou ETL.
Considere um caso em que você tem uma tabela, mas quer testar índices diferentes. O LookML inicial da visualização pode ser semelhante a este:
view: customer {
sql_table_name: warehouse.customer ;;
}
Para testar estratégias de otimização, use o parâmetro indexes
e adicione índices ao LookML da seguinte forma:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
Consulte a visualização uma vez para gerar a TDP. Em seguida, execute as consultas de teste e compare os resultados. Se os resultados forem favoráveis, peça à equipe de DBA ou ETL para adicionar os índices à tabela original.
Lembre-se de alterar o código de visualização novamente para remover a PDT.
Como usar TDPs para pré-mesclar ou agregar dados
Pode ser útil pré-mesclar ou pré-agregar dados para ajustar a otimização de consultas para grandes volumes ou vários tipos de dados.
Por exemplo, suponha que você queira gerar relatórios sobre clientes por coorte com base em quando eles fizeram o primeiro pedido. Esta consulta pode ser cara para executar várias vezes sempre que os dados forem necessários em tempo real; No entanto, é possível calcular a consulta apenas uma vez e reutilizar os resultados com uma TDP:
view: customer_order_facts {
derived_table: {
sql: SELECT
c.customer_id,
MIN(o.order_date) OVER (PARTITION BY c.customer_id) AS first_order_date,
MAX(o.order_date) OVER (PARTITION BY c.customer_id) AS most_recent_order_date,
COUNT(o.order_id) OVER (PARTITION BY c.customer_id) AS lifetime_orders,
SUM(o.order_value) OVER (PARTITION BY c.customer_id) AS lifetime_value,
RANK() OVER (PARTITION BY c.customer_id ORDER BY o.order_date ASC) AS order_sequence,
o.order_id
FROM warehouse.customer c LEFT JOIN warehouse.order o ON c.customer_id = o.customer_id
;;
sql_trigger_value: SELECT CURRENT_DATE ;;
indexes: [customer_id, order_id, order_sequence, first_order_date]
}
}
Tabelas derivadas em cascata
É possível fazer referência a uma tabela derivada na definição de outra, criando uma cadeia de tabelas derivadas em cascata ou tabelas derivadas persistentes (PDTs) em cascata, conforme o caso. Um exemplo de tabelas derivadas em cascata seria uma tabela, TABLE_D
, que depende de outra tabela, TABLE_C
, enquanto TABLE_C
depende de TABLE_B
e TABLE_B
depende de TABLE_A
.
Sintaxe para fazer referência a uma tabela derivada
Para fazer referência a uma tabela derivada em outra tabela derivada, use esta sintaxe:
`${derived_table_or_view_name.SQL_TABLE_NAME}`
Nesse formato, SQL_TABLE_NAME
é uma string literal. Por exemplo, é possível referenciar a tabela derivada de clean_events
com esta sintaxe:
`${clean_events.SQL_TABLE_NAME}`
Você pode usar essa mesma sintaxe para se referir a uma visualização do LookML. Novamente, nesse caso, SQL_TABLE_NAME
é uma string literal.
No próximo exemplo, a TDP clean_events
é criada com base na tabela events
no banco de dados. A TDP clean_events
exclui linhas indesejadas da tabela do banco de dados events
. Depois, uma segunda TDP é exibida. a TDP de event_summary
é um resumo da TDP de clean_events
. A tabela event_summary
é gerada novamente sempre que novas linhas são adicionadas a clean_events
.
A TDP event_summary
e clean_events
são TDPs em cascata, em que event_summary
depende de clean_events
, já que event_summary
é definido usando a PDT clean_events
. Esse exemplo específico poderia ser feito de forma mais eficiente em uma única PDT, mas é útil para demonstrar referências de tabelas derivadas.
view: clean_events {
derived_table: {
sql:
SELECT *
FROM events
WHERE type NOT IN ('test', 'staff') ;;
datagroup_trigger: events_datagroup
}
}
view: events_summary {
derived_table: {
sql:
SELECT
type,
date,
COUNT(*) AS num_events
FROM
${clean_events.SQL_TABLE_NAME} AS clean_events
GROUP BY
type,
date ;;
datagroup_trigger: events_datagroup
}
}
Embora nem sempre seja obrigatório, quando você se refere a uma tabela derivada dessa maneira, muitas vezes é útil criar um alias para a tabela usando este formato:
${derived_table_or_view_name.SQL_TABLE_NAME} AS derived_table_or_view_name
O exemplo anterior faz isso:
${clean_events.SQL_TABLE_NAME} AS clean_events
É útil usar um alias porque, nos bastidores, as PDTs são nomeadas com códigos longos no seu banco de dados. Em alguns casos, especialmente com cláusulas ON
, é fácil esquecer que você precisa usar a sintaxe ${derived_table_or_view_name.SQL_TABLE_NAME}
para recuperar esse nome longo. Um alias pode ajudar a evitar esse tipo de erro.
Como o Looker cria tabelas derivadas em cascata
No caso de tabelas derivadas temporárias em cascata, se os resultados da consulta de um usuário não estiverem no cache, o Looker vai criar todas as tabelas derivadas necessárias para a consulta. Se você tiver um TABLE_D
com definição que contém uma referência a TABLE_C
, então TABLE_D
depende de TABLE_C
. Isso significa que, se você consultar o TABLE_D
e ela não estiver no cache do Looker, o Looker vai recriar o TABLE_D
. Mas, primeiro, ele precisa recriar TABLE_C
.
Agora, vamos considerar um cenário de tabelas derivadas temporárias em cascata em que TABLE_D
depende de TABLE_C
, que depende de TABLE_B
, que depende de TABLE_A
. Se o Looker não tiver resultados válidos para uma consulta em TABLE_C
no cache, o Looker criará todas as tabelas necessárias para a consulta. Portanto, o Looker criará TABLE_A
, depois TABLE_B
e depois TABLE_C
:
Nesse cenário, a geração de TABLE_A
precisa terminar antes que o Looker comece a gerar o TABLE_B
e assim por diante, até que a TABLE_C
seja concluída e o Looker possa fornecer os resultados da consulta. Como a TABLE_D
não é necessária para responder a essa consulta, o Looker não vai recriar a TABLE_D
no momento.
Consulte a página de documentação do parâmetro datagroup
para ver um exemplo de cenário de PDTs em cascata que usam o mesmo grupo de dados.
A mesma lógica básica se aplica às TDPs: o Looker vai criar qualquer tabela necessária para responder a uma consulta em toda a cadeia de dependências. Mas com as TDPs, muitas vezes as tabelas já existem e não precisam ser recriadas. Com consultas padrão do usuário em PDTs em cascata, o Looker recria as PDTs na cascata somente se não houver uma versão válida das PDTs no banco de dados. Se você quiser forçar a recriação de todas as PDTs em uma cascata, recrie manualmente as tabelas de uma consulta usando uma Análise.
Um ponto lógico importante que é importante entender é que, no caso de uma cascata de TDP, uma TDP dependente é essencialmente consultar a TDP de que ela depende. Isso é significativo especialmente para TDPs que usam a estratégia persist_for
. Normalmente, as TDPs de persist_for
são criadas quando um usuário as consulta, permanecem no banco de dados até que o intervalo persist_for
esteja ativo e não são recriadas até que sejam consultadas pela próxima vez por um usuário. No entanto, se uma TDP de persist_for
fizer parte de uma cascata com TDPs baseadas em gatilho (PDTs que usam a estratégia de persistência datagroup_trigger
, interval_trigger
ou sql_trigger_value
), a TDP de persist_for
será essencialmente consultada sempre que as TDPs dependentes forem recriadas. Nesse caso, a TDP de persist_for
será recriada de acordo com a programação das TDPs dependentes. Isso significa que as TDPs de persist_for
podem ser afetadas pela estratégia de persistência dos dependentes.
Como recriar manualmente tabelas persistentes para uma consulta
Os usuários podem selecionar a opção Recriar tabelas derivadas Run no menu de uma Análise para substituir as configurações de persistência e recriar todas as tabelas derivadas persistentes (PDTs, na sigla em inglês) e tabelas de agregação necessárias para a consulta atual em Explore:
Essa opção só fica visível para usuários com a permissão develop
depois que a consulta "Análise" é carregada.
A ferramenta Recriar tabelas derivadas e A opção Run recria todas as tabelas persistentes (todas as PDTs e tabelas de agregação) necessárias para responder à consulta, independentemente da estratégia de persistência. Isso inclui todas as tabelas de agregação e PDTs na consulta atual, além de todas as tabelas de agregação e PDTs que são referenciadas pelas tabelas de agregação e PDTs na consulta atual.
No caso de PDTs incrementais, a ferramenta Recriar tabelas derivadas e Run aciona o build de um novo incremento. Com PDTs incrementais, um incremento inclui o período especificado no parâmetro increment_key
e também o número de períodos anteriores especificados no parâmetro increment_offset
, se houver. Consulte a página de documentação das TDPs incrementais para ver alguns exemplos de cenários que mostram como as TDPs incrementais são criadas, dependendo da configuração.
No caso de PDTs em cascata, isso significa recriar todas as tabelas derivadas na cascata, começando pelo topo. Esse é o mesmo comportamento de quando você consulta uma tabela em uma cascata de tabelas derivadas temporárias:
Observe o seguinte sobre a recriação manual de tabelas derivadas:
- Para o usuário que iniciou a reconstrução de tabelas derivadas Run, a consulta vai esperar que as tabelas sejam recriadas antes de carregar os resultados. De outros usuários as consultas continuarão usando as tabelas existentes. Após a reconstrução das tabelas persistentes, todos os usuários vão usar essas tabelas. Embora esse processo tenha sido desenvolvido para evitar interromper durante a recriação das tabelas, esses usuários ainda podem ser afetados pela carga adicional do banco de dados. Se o acionamento de uma recriação durante o horário comercial puder sobrecarregar seu banco de dados inaceitável, talvez seja necessário informar aos usuários que eles nunca devem recriar determinadas PDTs ou tabelas de agregação durante esse horário.
Se um usuário estiver no Modo de Desenvolvimento e a Análise for baseada em uma tabela de desenvolvimento, a seção Recriar tabelas derivadas e Run recriará a tabela de desenvolvimento, não a tabela de produção, para a Análise. No entanto, se a Análise no Modo de Desenvolvimento usar a versão de produção de uma tabela derivada, a tabela de produção será recriada. Consulte Tabelas mantidas no Modo de Desenvolvimento para informações sobre tabelas de desenvolvimento e de produção.
No caso das instâncias hospedadas pelo Looker, se a reformulação da tabela derivada levar mais de uma hora, a nova tabela não será recriada, e a sessão do navegador atingirá o tempo limite. Confira a seção Tempos limite e enfileiramento de consultas na página da documentação Configurações do administrador: consultas para saber mais sobre tempos limite que podem afetar os processos do Looker.
Tabelas mantidas no Modo de Desenvolvimento
O Looker tem alguns comportamentos especiais para gerenciar tabelas persistentes no modo de desenvolvimento.
Se você consultar uma tabela persistente no Modo de Desenvolvimento sem mudar a definição dela, o Looker consultará a versão de produção dessa tabela. Se você fizer uma alteração na definição de tabela que afeta os dados ou a forma como a tabela é consultada, uma nova versão de desenvolvimento será criada na próxima vez que você a consultar no Modo de Desenvolvimento. Essa tabela de desenvolvimento permite testar alterações sem incomodar os usuários finais.
O que solicita que o Looker crie uma tabela de desenvolvimento
Quando possível, o Looker usa a tabela de produção atual para responder a consultas, esteja você ou não no Modo de Desenvolvimento. Mas há certos casos em que o Looker não pode usar a tabela de produção para consultas no Modo de Desenvolvimento:
- Se a tabela persistente tiver um parâmetro que restrinja o conjunto de dados para trabalhar mais rapidamente no Modo de Desenvolvimento
- Se você fez alterações na definição da tabela persistente que afetam os dados dela
O Looker vai criar uma tabela de desenvolvimento se você estiver no Modo de Desenvolvimento e consultar uma tabela derivada baseada em SQL definida usando uma cláusula WHERE
condicional com instruções if prod
e if dev
.
Para tabelas persistentes que não têm um parâmetro para restringir o conjunto de dados no Modo de Desenvolvimento, o Looker usa a versão de produção da tabela para responder a consultas no Modo de Desenvolvimento, a menos que você mude a definição da tabela e depois consulte a tabela no Modo de Desenvolvimento. Isso vale para todas as alterações na tabela que afetam os dados nela ou na forma como a tabela é consultada.
Aqui estão alguns exemplos dos tipos de alterações que vão solicitar que o Looker crie uma versão de desenvolvimento de uma tabela persistente (o Looker criará a tabela somente se você a consultar posteriormente depois de fazer essas alterações):
- Alterar a consulta na qual a tabela persistente é baseada, como modificar o parâmetro
explore_source
,sql
,query
,sql_create
oucreate_process
na própria tabela persistente ou em qualquer tabela necessária (no caso de tabelas derivadas em cascata) - Alterar a estratégia de persistência da tabela, como modificar o parâmetro
datagroup_trigger
,sql_trigger_value
,interval_trigger
oupersist_for
da tabela - Mudar o nome do
view
de uma tabela derivada. - Mudar o
increment_key
ou aincrement_offset
de uma PDT incremental - Como alterar o
connection
usado pelo modelo associado
Para alterações que não modifiquem os dados da tabela nem afetam a maneira como o Looker consulta a tabela, o Looker não vai criar uma tabela de desenvolvimento. O parâmetro publish_as_db_view
é um bom exemplo: no Modo de Desenvolvimento, se você mudar apenas a configuração publish_as_db_view
de uma tabela derivada, o Looker não vai precisar recriar a tabela derivada. Portanto, não vai criar uma tabela de desenvolvimento.
Por quanto tempo o Looker mantém as tabelas de desenvolvimento
Seja qual for a estratégia de persistência real da tabela, o Looker trata as tabelas persistentes de desenvolvimento como se elas tivessem uma estratégia de persistência de persist_for: "24 hours"
. O Looker faz isso para garantir que as tabelas de desenvolvimento não sejam mantidas por mais de um dia, já que um desenvolvedor do Looker pode consultar muitas iterações de uma tabela durante o desenvolvimento e sempre que uma nova tabela de desenvolvimento é criada. Para evitar que as tabelas de desenvolvimento sobrecarreguem o banco de dados, o Looker aplica a estratégia persist_for: "24 hours"
para garantir que as tabelas sejam limpas do banco de dados com frequência.
Fora isso, o Looker cria tabelas derivadas permanentes (PDTs) e tabelas de agregação no Modo de Desenvolvimento da mesma forma que cria tabelas persistentes no Modo de Produção.
Se uma tabela de desenvolvimento é mantida no banco de dados quando você implanta alterações em uma PDT ou em uma tabela agregada, o Looker pode usá-la como a tabela de produção. Assim, os usuários não precisam esperar que a tabela seja criada durante a consulta.
Observe que, ao implantar as alterações, a tabela ainda pode precisar ser recriada para ser consultada na produção, dependendo da situação:
- Se mais de 24 horas se passaram desde que você consultou a tabela no Modo de Desenvolvimento, a versão de desenvolvimento dela será marcada como expirada e não será usada para consultas. Verifique se há TDPs não criadas usando o ambiente de desenvolvimento integrado do Looker ou a guia Desenvolvimento da página Tabelas derivadas persistentes. Se você tiver TDPs não criadas, consulte-as no Modo de Desenvolvimento antes de fazer as alterações para que a tabela de desenvolvimento fique disponível para uso na produção.
- Se uma tabela persistente tiver o parâmetro
dev_filters
(para tabelas derivadas nativas) ou a cláusulaWHERE
condicional que usa as instruçõesif prod
eif dev
(para tabelas derivadas baseadas em SQL), a tabela de desenvolvimento não poderá ser usada como a versão de produção, porque a versão de desenvolvimento tem um conjunto de dados abreviado. Se esse for o caso, depois de terminar de desenvolver a tabela e antes de implantar as alterações, você pode comentar o parâmetrodev_filters
ou a cláusula condicionalWHERE
e consultar a tabela no Modo de Desenvolvimento. O Looker vai criar uma versão completa da tabela que pode ser usada para produção quando você implantar as alterações.
Caso contrário, se você implantar as mudanças quando não houver uma tabela de desenvolvimento válida que possa ser usada como a tabela de produção, o Looker a recriará na próxima vez que a tabela for consultada no Modo de Produção (para tabelas persistentes que usam a estratégia persist_for
) ou na próxima vez que o regenerador for executado (para tabelas persistentes que usam datagroup_trigger
, interval_trigger
ou sql_trigger_value
).
Como verificar TDPs não criadas no Modo de Desenvolvimento
Se uma tabela de desenvolvimento é mantida no banco de dados quando você implanta alterações em uma tabela derivada persistente (PDT, na sigla em inglês) ou em uma tabela agregada, o Looker pode usar essa tabela como a de produção. Assim, os usuários não precisam esperar a criação da tabela ao consultá-la. Consulte as seções Por quanto tempo o Looker mantém as tabelas de desenvolvimento e O que solicita que o Looker crie uma tabela de desenvolvimento nesta página para mais detalhes.
Portanto, o ideal é que todas as PDTs sejam criadas quando você implanta na produção, para que as tabelas possam ser usadas imediatamente como versões de produção.
Verifique se há TDPs não criadas no seu projeto no painel Integridade do projeto. Clique no ícone Integridade do projeto no ambiente de desenvolvimento integrado do Looker para abrir o painel Integridade do projeto. Em seguida, clique no botão Validar o status de TDP.
Se houver TDPs não criadas, o painel Integridade do projeto as listará:
Se você tiver a permissão see_pdts
, clique no botão Acessar o gerenciamento de TDP. O Looker vai abrir a guia Desenvolvimento da página Tabelas derivadas persistentes e filtrar os resultados para seu projeto específico do LookML. A partir daí, é possível ver quais TDPs de desenvolvimento foram criadas ou não, além de acessar outras informações de solução de problemas. Consulte a página de documentação Configurações de administrador - Tabelas derivadas persistentes para mais informações.
Depois de identificar uma TDP não criada no seu projeto, você pode criar uma versão de desenvolvimento dela abrindo uma Análise que consulta a tabela e usando a ferramenta Recriar Run no menu "Explore". Consulte a seção Como recriar manualmente tabelas permanentes para uma consulta nesta página.
Compartilhamento e limpeza de tabelas
Em qualquer instância do Looker, o Looker vai compartilhar tabelas persistentes entre os usuários se as tabelas tiverem a mesma definição e a mesma configuração de método de persistência. Além disso, se a definição de uma tabela deixar de existir, o Looker a marcará como expirada.
Isso traz vários benefícios:
- Se você não tiver feito alterações em uma tabela no Modo de Desenvolvimento, suas consultas usarão as tabelas de produção existentes. Isso acontece, a menos que sua tabela seja uma tabela derivada baseada em SQL e definida usando uma cláusula
WHERE
condicional com instruçõesif prod
eif dev
. Se a tabela for definida com uma cláusulaWHERE
condicional, o Looker vai criar uma tabela de desenvolvimento caso você a consulte no Modo de Desenvolvimento. Para tabelas derivadas nativas com o parâmetrodev_filters
, o Looker tem a lógica de usar a tabela de produção para responder a consultas no Modo de Desenvolvimento, a menos que você mude a definição da tabela e depois a consulte no Modo de Desenvolvimento. - Se dois desenvolvedores fizerem a mesma alteração em uma tabela enquanto estiverem no Modo de Desenvolvimento, eles compartilharão a mesma tabela de desenvolvimento.
- Depois de enviar as alterações do Modo de Desenvolvimento para o Modo de Produção, a definição de produção antiga não existe mais. Portanto, a tabela de produção antiga será marcada como expirada e será descartada.
- Se você decidir descartar as alterações do Modo de Desenvolvimento, essa definição de tabela não existirá mais, e as tabelas de desenvolvimento desnecessárias serão marcadas como expiradas e serão descartadas.
Como trabalhar mais rápido no Modo de Desenvolvimento
Há situações em que a tabela derivada persistente (PDT) que você está criando demora muito para ser gerada, o que pode ser demorado, se você estiver testando muitas mudanças no Modo de Desenvolvimento. Nesses casos, solicite que o Looker crie versões menores de uma tabela derivada quando estiver no Modo de Desenvolvimento.
Nas tabelas derivadas nativas, é possível usar o subparâmetro dev_filters
de explore_source
para especificar filtros aplicados somente a versões de desenvolvimento da tabela derivada:
view: e_faa_pdt {
derived_table: {
...
datagroup_trigger: e_faa_shared_datagroup
explore_source: flights {
dev_filters: [flights.event_date: "90 days"]
filters: [flights.event_date: "2 years", flights.airport_name: "Yucca Valley Airport"]
column: id {}
column: airport_name {}
column: event_date {}
}
}
...
}
Este exemplo inclui um parâmetro dev_filters
que filtra os dados para os últimos 90 dias e um parâmetro filters
que filtra os dados para os últimos dois anos e para o aeroporto de Yucca Valley.
O parâmetro dev_filters
atua com o parâmetro filters
para que todos os filtros sejam aplicados à versão de desenvolvimento da tabela. Se dev_filters
e filters
especificarem filtros para a mesma coluna, dev_filters
terá precedência para a versão de desenvolvimento da tabela. Neste exemplo, a versão de desenvolvimento da tabela vai filtrar os dados dos últimos 90 dias para o aeroporto de Yucca Valley.
Para tabelas derivadas baseadas em SQL, o Looker aceita uma cláusula WHERE condicional com diferentes opções para versões de produção (if prod
) e desenvolvimento (if dev
) da tabela:
view: my_view {
derived_table: {
sql:
SELECT
columns
FROM
my_table
WHERE
-- if prod -- date > '2000-01-01'
-- if dev -- date > '2020-01-01'
;;
}
}
Neste exemplo, a consulta incluirá todos os dados a partir de 2000 no Modo de Produção, mas somente os dados a partir de 2020 no Modo de Desenvolvimento. Usar esse recurso estrategicamente para limitar seu conjunto de resultados e aumentar a velocidade da consulta pode facilitar muito a validação das alterações do Modo de Desenvolvimento.
Como o Looker cria PDTs
Depois que uma tabela derivada persistente (PDT) é definida e é executada pela primeira vez ou acionada pelo regenerador para reconstrução de acordo com a estratégia de persistência, o Looker segue estas etapas:
- Use o SQL da tabela derivada para criar uma instrução CREATE TABLE AS SELECT (ou CTAS) e executá-la. Por exemplo, para recriar uma PDT chamada
customer_orders_facts
:CREATE TABLE tmp.customer_orders_facts AS SELECT ... FROM ... WHERE ...
- Emita as instruções para criar os índices quando a tabela for criada
- Renomeie a tabela de LC$.. ("Looker Create") para LR$.. ("Looker Read") para indicar que ela está pronta para uso.
- Descartar qualquer versão mais antiga da tabela que não deveria estar mais em uso
Há algumas implicações importantes:
- O SQL que forma a tabela derivada precisa ser válido dentro de uma instrução CTAS.
- Os aliases de coluna no conjunto de resultados da instrução SELECT precisam ser nomes de coluna válidos.
- Os nomes usados ao especificar a distribuição, as chaves de classificação e os índices precisam ser os nomes de colunas listados na definição do SQL da tabela derivada, não os nomes de campo definidos no LookML.
A ferramenta de regeneração do Looker
O regenerador do Looker verifica o status e inicia as recriações das tabelas com gatilhos persistentes. Uma tabela com acionador permanente é uma tabela derivada persistente (PDT, na sigla em inglês) ou uma tabela agregada que usa um acionador como estratégia de persistência:
- Para tabelas que usam
sql_trigger_value
, o gatilho é uma consulta especificada no parâmetrosql_trigger_value
da tabela. O regenerador do Looker aciona uma recriação da tabela quando o resultado da verificação de consulta do acionador mais recente é diferente do resultado da verificação de consulta do acionador anterior. Por exemplo, se a tabela derivada for mantida com a consulta SQLSELECT CURDATE()
, o regenerador do Looker a recriará na próxima vez que verificar o acionador após a mudança da data. - Para tabelas que usam
interval_trigger
, o acionador é uma duração de tempo especificada no parâmetrointerval_trigger
da tabela. O regenerador do Looker aciona uma recriação da tabela após o período especificado. - Para tabelas que usam
datagroup_trigger
, o acionador pode ser uma consulta especificada no parâmetrosql_trigger
do grupo de dados associado ou um período de tempo especificado no parâmetrointerval_trigger
do grupo de dados.
O regenerador do Looker também inicia recriações para tabelas persistentes que usam o parâmetro persist_for
, mas somente quando a tabela persist_for
é uma cascata de dependência de uma tabela com acionador permanente. Nesse caso, o regenerador do Looker vai iniciar as recriações de uma tabela persist_for
, já que ela é necessária para recriar as outras na cascata. Caso contrário, o regenerador não monitora tabelas persistentes que usam a estratégia persist_for
.
O ciclo do regenerador do Looker começa em um intervalo regular configurado pelo administrador do Looker na configuração Programação de manutenção de PDT e grupo de dados na sua conexão de banco de dados. O padrão é um intervalo de cinco minutos. No entanto, o regenerador do Looker não inicia um novo ciclo até concluir todas as verificações e recriações de PDT do ciclo anterior. Isso significa que, se você tiver builds de PDT de longa duração, o ciclo do regenerador do Looker poderá não ser executado com a frequência definida na configuração Programação de manutenção de PDT e grupos de dados. Outros fatores podem afetar o tempo necessário para recriar suas tabelas, conforme descrito na seção Considerações importantes para a implementação de tabelas persistentes nesta página.
Quando a criação de uma PDT falha, o regenerador pode tentar recriar a tabela no próximo ciclo:
- Se a configuração Repetir builds de PDT com falha estiver ativada na conexão do seu banco de dados, o regenerador do Looker vai tentar recriar a tabela durante o próximo ciclo, mesmo que a condição de acionamento da tabela não seja atendida.
- Se a configuração Repetir builds de TDP com falha estiver desativada, o regenerador do Looker não vai tentar recriar a tabela até que a condição de gatilho da PDT seja atendida.
Se um usuário solicitar dados da tabela mantida enquanto ela estiver sendo criada e os resultados da consulta não estiverem no cache, o Looker verificará se a tabela atual ainda é válida. A tabela anterior pode não ser válida se não for compatível com a nova versão, o que pode acontecer se a nova tabela tiver uma definição diferente, se a nova tabela usar uma conexão de banco de dados diferente ou se a nova tabela tiver sido criada com uma versão diferente do Looker. Se a tabela atual ainda for válida, o Looker vai retornar dados dela até que a nova tabela seja criada. Caso contrário, se a tabela atual não for válida, o Looker vai fornecer os resultados da consulta assim que a nova tabela for recriada.
Considerações importantes para a implementação de tabelas persistentes
Considerando a utilidade das tabelas persistentes (PDTs e tabelas de agregação), é fácil acumular muitas delas na sua instância do Looker. É possível criar um cenário em que o regenerador do Looker precisa criar muitas tabelas ao mesmo tempo. Especialmente com tabelas em cascata ou tabelas de longa duração, é possível criar um cenário em que as tabelas têm um longo atraso antes da recriação ou em que os usuários enfrentam um atraso no recebimento dos resultados da consulta de uma tabela enquanto o banco de dados está trabalhando duro para gerar a tabela.
O regerador do Looker verifica os gatilhos de TDP para saber se ele precisa recriar tabelas mantidas por gatilhos. O ciclo da regeneração é definido em um intervalo regular definido pelo administrador do Looker na configuração Programação de manutenção de PDT e grupo de dados na conexão do banco de dados. O padrão é um intervalo de cinco minutos.
Diversos fatores podem afetar o tempo necessário para recriar suas tabelas:
- Seu administrador do Looker pode ter mudado o intervalo das verificações do acionador do regenerador usando a configuração Programação de manutenção de PDT e grupo de dados na sua conexão de banco de dados.
- O regenerador do Looker não inicia um novo ciclo até concluir todas as verificações e recriações de TDP do último ciclo. Portanto, se você tem builds de PDT de longa duração, o ciclo do regenerador do Looker pode não ser tão frequente quanto a configuração Programação de manutenção de PDT e grupos de dados.
- Por padrão, o regenerador pode iniciar a recriação de uma PDT ou tabela agregada de cada vez por uma conexão. Um administrador do Looker pode ajustar o número permitido de recriações simultâneas pelo regenerador usando o campo Número máximo de conexões do builder de PDT nas configurações de uma conexão.
- Todas as TDPs e tabelas de agregação acionadas pelo mesmo
datagroup
serão recriadas durante o mesmo processo. Isso pode ser uma carga pesada se você tem muitas tabelas usando o grupo de dados, seja diretamente ou como resultado de dependências em cascata.
Além das considerações anteriores, há também algumas situações em que você deve evitar adicionar persistência a uma tabela derivada:
- Quando as tabelas derivadas serão estendidas: cada extensão de uma PDT criará uma nova cópia da tabela no seu banco de dados.
- Quando as tabelas derivadas usam filtros de modelo ou parâmetros líquidos: não há suporte para a persistência de tabelas derivadas que usam filtros de modelo ou parâmetros líquidos.
- Quando tabelas derivadas nativas são criadas com base em Análises que usam atributos do usuário com
access_filters
ousql_always_where
, cópias da tabela são criadas no seu banco de dados para cada possível valor de atributo do usuário especificado. - Quando os dados subjacentes mudam com frequência e o dialeto do banco de dados não é compatível com PDTs incrementais.
- Quando o custo e o tempo envolvidos na criação de PDTs são muito altos.
Dependendo do número e da complexidade das tabelas persistentes na conexão do Looker, a fila pode conter muitas tabelas persistentes que precisam ser verificadas e recriadas a cada ciclo. Por isso, é importante considerar esses fatores ao implementar tabelas derivadas na instância do Looker.
Como gerenciar TDPs em grande escala com uma API
O monitoramento e o gerenciamento de tabelas derivadas permanentes (PDTs) que são atualizadas em programações variadas se tornam cada vez mais complexos à medida que você cria mais PDTs na instância. Considere usar a integração do Apache Airflow do Looker para gerenciar as programações de PDT com outros processos de ETL e ELT.
Como monitorar e solucionar problemas de TDPs
Se você usa tabelas derivadas persistentes (PDTs, na sigla em inglês) e especialmente PDTs em cascata, é útil ver o status das PDTs. Você pode usar a página de administração Persistent Derived Tables do Looker para ver o status das suas TDPs. Consulte a página de documentação Configurações de administrador: tabelas derivadas persistentes para mais informações.
Ao tentar resolver problemas de TDPs:
- Preste atenção especial à distinção entre tabelas de desenvolvimento e tabelas de produção ao investigar o log de eventos de PDT.
- Verificar se nenhuma alteração foi feita no esquema de rascunho em que o Looker armazena tabelas derivadas persistentes. Se houver mudanças, talvez seja necessário atualizar as configurações de Conexão na seção Administrador do Looker e reiniciar o Looker para restaurar a funcionalidade normal da TDP.
- Determine se há problemas com todas as TDPs ou apenas uma. Se houver algum problema com um deles, é provável que seja causado por um erro do LookML ou do SQL.
- Determine se os problemas com a PDT correspondem aos horários em que ela está programada para ser recriada.
- Verifique se todas as consultas
sql_trigger_value
foram avaliadas e se retornam apenas uma linha e coluna. Para TDPs baseadas em SQL, execute no SQL Runner (link em inglês). A aplicação de umLIMIT
protege contra consultas descontroladas. Para mais informações sobre como usar o SQL Runner para depurar tabelas derivadas, consulte Como usar o sql runner para testar tabelas derivadas postagem na Comunidade. - Para PDTs baseadas em SQL, use o SQL Runner para verificar se o SQL da PDT é executado sem erros. Aplique um
LIMIT
no SQL Runner para manter os tempos de consulta razoáveis. - Para tabelas derivadas baseadas em SQL, evite usar expressões de tabela comuns (CTEs, na sigla em inglês). O uso de CTEs com DTs cria instruções
WITH
aninhadas que podem fazer com que as PDTs falhem sem aviso. Em vez disso, use o SQL da CTE para criar uma DT secundária e referenciá-la na sua primeira DT usando a sintaxe${derived_table_or_view_name.SQL_TABLE_NAME}
. - Verifique se as tabelas das quais a TDP com problema depende (se tabelas normais ou PDTs em si) existem e podem ser consultadas.
- Verifique se as tabelas de que a TDP com problema depende não têm bloqueios compartilhados ou exclusivos. Para que o Looker crie uma TDP, ele precisa adquirir um bloqueio exclusivo na tabela a ser atualizada. Isso vai entrar em conflito com outros bloqueios compartilhados ou exclusivos disponíveis no momento. O Looker não poderá atualizar a PDT até que todos os outros bloqueios sejam apagados. O mesmo vale para qualquer bloqueio exclusivo na tabela em que o Looker está criando uma PDT. se houver um bloqueio exclusivo em uma tabela, o Looker não poderá adquirir um bloqueio compartilhado para executar consultas até que o bloqueio exclusivo seja apagado.
- Use o botão Mostrar processos no SQL Runner. Se houver um grande número de processos ativos, isso poderá atrasar os tempos de consulta.
- Monitore os comentários da consulta. Consulte a seção Comentários da consulta sobre PDTs nesta página.
Comentários da consulta sobre TDPs
Os administradores de bancos de dados conseguem diferenciar facilmente consultas normais daquelas que geram tabelas derivadas persistentes (PDTs). O Looker adiciona comentários à instrução CREATE TABLE ... AS SELECT ...
que inclui o modelo e a visualização do LookML da PDT, além de um identificador exclusivo (slug) para a instância do Looker. Se a TDP estiver sendo gerada em nome de um usuário no Modo de Desenvolvimento, os comentários indicarão o ID desse usuário. Os comentários da geração de TDPs seguem este padrão:
-- Building `<view_name>` in dev mode for user `<user_id>` on instance `<instance_slug>`
CREATE TABLE `<table_name>` SELECT ...
-- finished `<view_name>` => `<table_name>`
O comentário da geração da TDP vai aparecer na guia SQL de uma Análise se o Looker tiver que gerar uma TDP para a consulta da Análise. O comentário aparecerá na parte superior da instrução SQL.
Por fim, o comentário da geração de TDPs aparece no campo Mensagem na guia Informações do pop-up Detalhes da consulta para cada consulta na página de administração de Consultas.
Como recriar TDPs após uma falha
Quando uma tabela derivada persistente (PDT, na sigla em inglês) falha, acontece o seguinte quando ela é consultada:
- O Looker vai usar os resultados no cache se a mesma consulta tiver sido executada anteriormente. Consulte a página de documentação Consultas de armazenamento em cache para uma explicação de como isso funciona.
- Se os resultados não estiverem no cache, o Looker extrairá resultados da PDT no banco de dados, caso exista uma versão válida da PDT.
- Se não houver uma TDP válida no banco de dados, o Looker tentará recriá-la.
- Se não for possível recriar a TDP, o Looker retornará um erro em uma consulta. O regenerador do Looker vai tentar recriar a PDT na próxima vez que ela for consultada ou na próxima vez que a estratégia de persistência da PDT acionar uma recriação.
As PDTs em cascading usam a mesma lógica, mas com as TDPs em cascata:
- Uma falha na compilação para uma tabela impede a criação de PDTs na cadeia de dependências.
- Uma TDP dependente está essencialmente consultando a TDP de que ela depende, então a estratégia de persistência de uma tabela pode acionar recriações das TDPs para cima na cadeia.
Voltando ao exemplo anterior de tabelas em cascata, em que TABLE_D
depende de TABLE_C
, que depende de TABLE_B
, que depende de TABLE_A
:
Se TABLE_B
tiver uma falha, todo o comportamento padrão (não em cascata) será aplicado a TABLE_B
: se TABLE_B
for consultado, o Looker tentará usar o cache para retornar resultados, depois tentará usar uma versão anterior da tabela, se possível, tentará recriá-la e, por fim, retornará um erro se TABLE_B
não conseguir recriar. O Looker vai tentar recriar TABLE_B
novamente na próxima consulta da tabela ou quando a estratégia de persistência da tabela acionar uma recriação.
O mesmo se aplica aos dependentes de TABLE_B
. Portanto, se TABLE_B
não puder ser criado e houver uma consulta em TABLE_C
:
- O Looker vai tentar usar o cache para a consulta em
TABLE_C
. - Se os resultados não estiverem no cache, o Looker vai tentar extrair resultados de
TABLE_C
no banco de dados. - Se não houver uma versão válida de
TABLE_C
, o Looker vai tentar recriar aTABLE_C
, que cria uma consulta emTABLE_B
. - O Looker vai tentar recriar o
TABLE_B
, o que falhará se oTABLE_B
não tiver sido corrigido. - Se a
TABLE_B
não puder ser recriada,TABLE_C
não poderá ser recriada, então o Looker retornará um erro para a consulta emTABLE_C
. - O Looker vai tentar recriar a
TABLE_C
de acordo com a estratégia de persistência normal ou na próxima vez que a PDT for consultada, o que inclui a próxima tentativa de criação deTABLE_D
, já queTABLE_D
depende deTABLE_C
.
Depois de resolver o problema com TABLE_B
, TABLE_B
e cada uma das tabelas dependentes vão tentar ser recriadas de acordo com as estratégias de persistência ou na próxima vez que forem consultadas, o que inclui a próxima vez que uma PDT dependente tentar ser recriada. Ou, se uma versão de desenvolvimento das PDTs na cascata tiver sido criada no Modo de Desenvolvimento, as versões de desenvolvimento poderão ser usadas como as novas PDTs de produção. Consulte a seção Tabelas mantidas no Modo de Desenvolvimento nesta página para saber como isso funciona. Também é possível usar uma Análise para executar uma consulta em TABLE_D
e, em seguida, recriar manualmente as PDTs para a consulta, o que forçará a recriação de todas as PDTs que sobem a cascata de dependências.
Como melhorar a performance da TDP
Quando você cria tabelas derivadas persistentes (PDTs, na sigla em inglês), o desempenho pode ser uma preocupação. Especialmente quando a tabela é muito grande, a consulta pode ser lenta, assim como em qualquer tabela grande do seu banco de dados.
Para melhorar o desempenho, filtre os dados ou controle como os dados na PDT são classificados e indexados.
Adicionar filtros para limitar o conjunto de dados
Com conjuntos de dados particularmente grandes, ter muitas linhas desacelera as consultas em uma tabela derivada persistente (PDT). Se você geralmente consulta apenas dados recentes, considere adicionar um filtro à cláusula WHERE
da PDT que limite a tabela a até 90 dias de dados. Dessa forma, somente dados relevantes serão adicionados à tabela sempre que ela for recriada, o que fará com que a execução de consultas seja muito mais rápida. Em seguida, é possível criar uma PDT maior e separada para análise histórica, permitindo consultas rápidas de dados recentes e a capacidade de consultar dados antigos.
Como usar indexes
ou sortkeys
e distribution
Ao criar uma grande tabela derivada persistente (PDT), indexar a tabela (para dialetos como MySQL ou Postgres) ou adicionar chaves de classificação e distribuição (para Redshift) pode ajudar no desempenho.
Geralmente, é melhor adicionar o parâmetro indexes
aos campos de ID ou data.
Para o Redshift, é recomendável adicionar o parâmetro sortkeys
nos campos de ID ou data e o parâmetro distribution
no campo usado para mesclagem.
Configurações recomendadas para melhorar o desempenho
As configurações a seguir controlam como os dados na tabela derivada persistente (PDT) são classificados e indexados. Estas configurações são opcionais, mas altamente recomendadas:
- Para o Redshift e o Aster, use o parâmetro
distribution
para especificar o nome da coluna cujo valor é usado para espalhar os dados por um cluster. Quando duas tabelas são mescladas pela coluna especificada no parâmetrodistribution
, o banco de dados pode encontrar os dados de mesclagem no mesmo nó, minimizando a E/S entre nós. - No Redshift, defina o parâmetro
distribution_style
comoall
para instruir o banco de dados a manter uma cópia completa dos dados em cada nó. Isso é usado com frequência para minimizar a E/S entre nós quando tabelas relativamente pequenas são unidas. Defina esse valor comoeven
para instruir o banco de dados a espalhar os dados uniformemente pelo cluster sem usar uma coluna de distribuição. Esse valor só pode ser especificado quandodistribution
não está especificado. - Para o Redshift, use o parâmetro
sortkeys
. Os valores especificam quais colunas da PDT são usadas para classificar os dados no disco e facilitar a pesquisa. No Redshift, é possível usarsortkeys
ouindexes
, mas não ambos. - Na maioria dos bancos de dados, use o parâmetro
indexes
. Os valores especificam quais colunas da PDT são indexadas. No Redshift, os índices são usados para gerar chaves de classificação intercaladas.