PDT incrementales

En Looker, las tablas derivadas persistentes (PDT) se escriben en el esquema inicial de tu base de datos. Looker conserva y vuelve a compilar una PDT según su estrategia de persistencia. Cuando se activa una PDT para volver a compilar, Looker vuelve a compilar toda la tabla de forma predeterminada.

Una PDT incremental es una PDT que Looker compila agregando datos nuevos a la tabla en lugar de volver a compilarla por completo:

Una tabla grande con las tres filas inferiores destacadas para mostrar una pequeña cantidad de filas nuevas que se están agregando.

Si tu dialecto admite PDT incrementales, puedes convertir los siguientes tipos de PDT en PDT incrementales:

La primera vez que ejecutas una consulta en un PDT incremental, Looker compila todo el PDT para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede tardar bastante, al igual que la compilación de cualquier tabla grande. Una vez que se compile la tabla inicial, las compilaciones posteriores serán incrementales y tardarán menos tiempo, si el PDT incremental se configura de forma estratégica.

Ten en cuenta lo siguiente para las PDT incrementales:

  • Las PDT incrementales solo se admiten para las PDT que usan una estrategia de persistencia basada en activadores (datagroup_trigger, sql_trigger_value o interval_trigger). Las PDT incrementales no son compatibles con las PDT que usan la estrategia de persistencia persist_for.
  • Para las PDT basadas en SQL, la consulta de la tabla debe definirse con el parámetro sql para usar como una PDT incremental. Las PDT basadas en SQL que se definen con los parámetros sql_create o create_process no se pueden compilar de forma incremental. Como puedes ver en el Ejemplo 1 de esta página, Looker usa un comando INSERT o MERGE para crear los incrementos de una PDT incremental. La tabla derivada no se puede definir con declaraciones de lenguaje de definición de datos (DDL) personalizadas, ya que Looker no podría determinar qué declaraciones DDL se necesitarían para crear un incremento preciso.
  • La tabla de origen del PDT incremental debe estar optimizada para las consultas basadas en el tiempo. Específicamente, la columna basada en el tiempo que se usa para la clave de incremento debe tener una estrategia de optimización, como partición, sortkeys, índices o cualquier estrategia de optimización que admita tu dialecto. Se recomienda la optimización de la tabla fuente porque cada vez que se actualiza la tabla incremental, Looker consulta la tabla de origen para determinar los valores más recientes de la columna basada en el tiempo que se usa para la clave de incremento. Si la tabla de origen no está optimizada para estas consultas, la consulta de Looker para los valores más recientes puede ser lenta y costosa.

Define una PDT incremental

Puedes usar los siguientes parámetros para convertir un PDT en un PDT incremental:

  • increment_key (obligatorio para que la PDT sea una PDT incremental): Define el período durante el que se deben consultar los registros nuevos.
  • Filtro líquido {% incrementcondition %} (obligatorio para convertir un PDT basado en SQL en un PDT incremental; no se aplica a los PDT basados en LookML): Conecta la clave de incremento a la columna de hora de la base de datos en la que se basa. Consulta la página de documentación de increment_key para obtener más información.
  • increment_offset (opcional): Es un número entero que define la cantidad de períodos anteriores (en el nivel de detalle de la clave de incremento) que se vuelven a compilar para cada compilación incremental. El parámetro increment_offset es útil en el caso de los datos que llegan tarde, en los que los períodos anteriores pueden tener datos nuevos que no se incluyeron cuando se compiló y adjuntó originalmente el incremento correspondiente al PDT.

Consulta la página de documentación del parámetro increment_key para ver ejemplos que muestran cómo crear PDT incrementales a partir de tablas derivadas nativas persistentes, tablas derivadas persistentes basadas en SQL y tablas agregadas.

Este es un ejemplo simple de un archivo de vista que define un PDT incremental basado en LookML:

view: flights_lookml_incremental_pdt {
  derived_table: {
    indexes: ["id"]
    increment_key: "departure_date"
    increment_offset: 3
    datagroup_trigger: flights_default_datagroup
    distribution_style: all
    explore_source: flights {
      column: id {}
      column: carrier {}
      column: departure_date {}
    }
  }

  dimension: id {
    type: number
  }
  dimension: carrier {
    type: string
  }
   dimension: departure_date {
    type: date
  }
}

Esta tabla se compilará por completo la primera vez que se ejecute una consulta en ella. Después de eso, la PDT se volverá a compilar en incrementos de un día (increment_key: departure_date) hasta tres días (increment_offset: 3).

La clave de incremento se basa en la dimensión departure_date, que en realidad es el período date del grupo de dimensiones departure. Consulta la página de documentación del parámetro dimension_group para obtener una descripción general del funcionamiento de los grupos de dimensiones. El grupo de dimensiones y el período se definen en la vista flights, que es el explore_source de este PDT. A continuación, se muestra cómo se define el grupo de dimensiones departure en el archivo de vista flights:

...
  dimension_group: departure {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.dep_time ;;
  }
...

Interacción de los parámetros de incremento y la estrategia de persistencia

La configuración de increment_key y increment_offset de una PDT es independiente de la estrategia de persistencia de la PDT:

  • La estrategia de persistencia de la PDT incremental solo determina cuándo se incrementa la PDT. El generador de PDT no modifica la PDT incremental a menos que se active la estrategia de persistencia de la tabla, o a menos que la PDT se active manualmente con Rebuild Derived Tables & Run en una exploración.
  • Cuando el PDT aumente, el compilador de PDT determinará cuándo se agregaron los datos más recientes a la tabla, en términos del incremento de tiempo más reciente (el período que define el parámetro increment_key). En función de eso, el compilador de PDT truncará los datos al comienzo del incremento de tiempo más reciente en la tabla y, luego, compilará el último incremento a partir de allí.
  • Si el PDT tiene un parámetro increment_offset, el compilador de PDT también volverá a compilar la cantidad de períodos anteriores especificados en el parámetro increment_offset. Los períodos anteriores se remontan desde el comienzo del incremento de tiempo más reciente (el período que define el parámetro increment_key).

En las siguientes situaciones de ejemplo, se ilustra cómo se actualizan las PDT incrementales, ya que se muestra la interacción de increment_key, increment_offset y la estrategia de persistencia.

Ejemplo 1

En este ejemplo, se usa un PDT con las siguientes propiedades:

  • Clave incremento: date
  • Incremento de desplazamiento: 3
  • Estrategia de persistencia: Se activa una vez al mes, el primer día del mes.

Esta tabla se actualizará de la siguiente manera:

  • Una estrategia de persistencia mensual significa que la tabla se compila automáticamente una vez al mes. Esto significa que, por ejemplo, el 1 de junio, la última fila de la tabla se agregará el 1 de mayo.
  • Debido a que esta PDT tiene una clave de incremento basada en la fecha, el compilador de PDT truncará el 1 de mayo al comienzo del día y volverá a compilar los datos del 1 de mayo y hasta el día actual, el 1 de junio.
  • Además, este PDT tiene un desfase de incremento de 3. Por lo tanto, el compilador de PDT también vuelve a compilar los datos de los tres períodos anteriores (días) antes del 1 de mayo. El resultado es que se vuelven a crear los datos del 28, 29 y 30 de abril y hasta el día de hoy, 1 de junio.

En términos de SQL, este es el comando que ejecutará el compilador de PDT el 1 de junio para determinar las filas del PDT existente que se deben volver a compilar:

## Example SQL for BigQuery:
SELECT FORMAT_TIMESTAMP('%F %T',TIMESTAMP_ADD(MAX(pdt_name),INTERVAL -3 DAY))

## Example SQL for other dialects:
SELECT CAST(DATE_ADD(MAX(pdt_name),INTERVAL -3 DAY) AS CHAR)

Este es el comando SQL que ejecutará el compilador de PDT el 1 de junio para compilar el último incremento:

## Example SQL for BigQuery:

MERGE INTO [pdt_name] USING (SELECT [columns]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM'))
   AS tmp_name ON FALSE
WHEN NOT MATCHED BY SOURCE AND created_date >= TIMESTAMP('4/28/21 12:00:00 AM')
   THEN DELETE
WHEN NOT MATCHED THEN INSERT [columns]

## Example SQL for other dialects:

START TRANSACTION;
DELETE FROM [pdt_name]
   WHERE created_date >= TIMESTAMP('4/28/21 12:00:00 AM');
INSERT INTO [pdt_name]
   SELECT [columns]
   FROM [source_table]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM');
COMMIT;

Ejemplo 2

En este ejemplo, se usa una PDT con las siguientes propiedades:

  • Estrategia de persistencia: Se activa una vez al día.
  • Clave de incremento: month
  • Incrementar desplazamiento: 0

A continuación, se muestra cómo se actualizará esta tabla el 1 de junio:

  • La estrategia de persistencia diaria implica que la tabla se crea automáticamente una vez al día. El 1 de junio, la última fila de la tabla se habrá agregado el 31 de mayo.
  • Debido a que la clave de incremento se basa en el mes, el compilador de PDT truncará desde el 31 de mayo hasta el comienzo del mes y volverá a compilar los datos de todo mayo y hasta el día actual, incluido el 1 de junio.
  • Debido a que esta PDT no tiene compensación de incremento, no se reconstruyen períodos anteriores.

A continuación, se muestra cómo se actualizará esta tabla el 2 de junio:

  • El 2 de junio, la última fila de la tabla se habrá agregado el 1 de junio.
  • Debido a que el compilador de PDT truncará los datos hasta el comienzo del mes de junio y, luego, volverá a compilarlos a partir del 1 de junio y hasta el día actual, los datos se volverán a compilar solo para el 1 y el 2 de junio.
  • Como esta PDT no tiene un desplazamiento de incremento, no se vuelven a compilar períodos anteriores.

Ejemplo 3

En este ejemplo, se usa una PDT con las siguientes propiedades:

  • Clave de incremento: month
  • Incrementar desplazamiento: 3
  • Estrategia de persistencia: Se activa una vez al día.

Este escenario ilustra una configuración deficiente para una PDT incremental, ya que se trata de una PDT de activación diaria con una compensación de tres meses. Esto significa que se volverán a crear al menos tres meses de datos todos los días, lo que sería un uso muy ineficiente de un PDT incremental. Sin embargo, es un escenario interesante para examinar como una forma de entender cómo funcionan las PDT incrementales.

Esta tabla se actualizará el 1 de junio de la siguiente manera:

  • La estrategia de persistencia diaria implica que la tabla se compila automáticamente una vez al día. Por ejemplo, el 1 de junio, la última fila de la tabla se habrá agregado el 31 de mayo.
  • Debido a que la clave de incremento se basa en el mes, el compilador de PDT truncará desde el 31 de mayo hasta el comienzo del mes y volverá a compilar los datos para todo mayo y hasta el día actual, incluido el 1 de junio.
  • Además, esta PDT tiene un desplazamiento de incremento de 3. Esto significa que el compilador de PDT también vuelve a compilar los datos de los tres períodos (meses) anteriores a mayo. El resultado es que los datos se vuelven a compilar desde febrero, marzo, abril y hasta el día actual, 1 de junio.

A continuación, se muestra cómo se actualizará esta tabla el 2 de junio:

  • El 2 de junio, la última fila de la tabla se habrá agregado el 1 de junio.
  • El compilador de PDT truncará el mes hasta el 1 de junio y volverá a compilar los datos del mes de junio, incluido el 2 de junio.
  • Además, debido a la compensación de incremento, el compilador de PDT volverá a compilar los datos de los tres meses anteriores a junio. Como resultado, los datos se vuelven a compilar desde marzo, abril y mayo, y hasta el día de hoy, el 2 de junio.

Prueba una PDT incremental en Modo de desarrollo

Antes de implementar una nueva PDT incremental en tu entorno de producción, puedes probarla para asegurarte de que se compile y se incremente. Para probar un PDT incremental en el modo de desarrollo, sigue estos pasos:

  1. Crea una exploración para el PDT:

    • En un archivo de modelo asociado, usa el parámetro include para incluir el archivo de vista del PDT en el archivo de modelo.
    • En el mismo archivo de modelo, usa el parámetro explore para crear una exploración para la vista de PDT incremental.
     include: "/views/e_faa_pdt.view"
 explore: e_faa_pdt {}

  2. Abre la función Explorar para el PDT. Para ello, selecciona el botón Ver acciones del archivo y, luego, elige un nombre de Explorar.

  1. En Explorar, selecciona algunas dimensiones o mediciones y haz clic en Ejecutar. Luego, Looker compilará todo el PDT. Si esta es la primera consulta que ejecutas en la PDT incremental, el compilador de PDT compilará la PDT completa para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede tardar bastante, al igual que la compilación de cualquier tabla grande.

  2. Puedes verificar que se haya compilado el PDT inicial de las siguientes maneras:

    • Si tienes el permiso see_logs, puedes verificar que la tabla se compiló en el Registro de eventos de PDT. Si no ves los eventos de creación de PDT en el registro de eventos de PDT, verifica la información del estado en la parte superior de la exploración del registro de eventos de PDT. Si dice "de la caché", puedes seleccionar Borrar caché y actualizar para obtener información más reciente.
    • De lo contrario, puedes consultar los comentarios en la pestaña SQL de la barra Datos de Explorar. En la pestaña SQL, se muestra la consulta y las acciones que se realizarán cuando la ejecutes en Explorar. Por ejemplo, si los comentarios de la pestaña SQL dicen -- generate derived table e_incremental_pdt, esa es la acción que se realizará cuando hagas clic en Ejecutar.

  3. Una vez que crees la compilación inicial de la PDT, solicita una compilación incremental de la PDT con la opción Rebuild Derived Tables & Run de Explorar.

  4. Puedes usar los mismos métodos que antes para verificar que la PDT se compile de forma incremental:

    • Si tienes el permiso see_logs, puedes usar el Registro de eventos de PDT para ver los eventos create increment complete de la PDT incremental. Si no ves este evento en el registro de eventos de PDT y el estado de la consulta dice “from cache”, selecciona Borrar caché y Actualiza la página para ver la información más reciente.
    • Observa los comentarios en la pestaña SQL de la barra Datos de la exploración. En este caso, los comentarios indicarán que se incrementó el PDT. Por ejemplo: -- increment persistent derived table e_incremental_pdt to generation 2

  5. Una vez que hayas verificado que la PDT se compila y se incrementa correctamente, si no quieres conservar la exploración dedicada para la PDT, puedes quitar o marcar como comentario los parámetros explore y include de la PDT desde tu archivo de modelo.

Después de compilar la PDT en Modo de desarrollo, la misma tabla se usará para la producción una vez que implementes los cambios, a menos que realices más modificaciones en la definición de la tabla. Consulta la sección Tablas persistentes en Modo de desarrollo de la página de documentación Tablas derivadas en Looker para obtener más información.

Dialectos de base de datos admitidos para PDT incrementales

Para que Looker admita PDT incrementales en tu proyecto de Looker, el dialecto de la base de datos debe ser compatible con los comandos del lenguaje de definición de datos (DDL) que permiten borrar e insertar filas.

En la siguiente tabla, se muestra qué dialectos admiten PDT incrementales en la versión más reciente de Looker (para Databricks, las PDT incrementales solo son compatibles con Databricks 12.1 y versiones posteriores):

Dialecto ¿Es compatible?
Actian Avalanche
No
Amazon Athena
No
Amazon Aurora MySQL
No
Amazon Redshift
Sí
Apache Druid
No
Apache Druid 0.13 y versiones posteriores
No
Apache Druid 0.18 y versiones posteriores
No
Apache Hive 2.3 y versiones posteriores
No
Apache Hive 3.1.2 y versiones posteriores
No
Apache Spark 3 y versiones posteriores
No
ClickHouse
No
Cloudera Impala 3.1 y versiones posteriores
No
Cloudera Impala 3.1 y versiones posteriores con controlador nativo
No
Cloudera Impala con controlador nativo
No
DataVirtuality
No
Databricks
Sí
Denodo 7
No
Denodo 8
No
Dremio
No
Dremio 11 y versiones posteriores
No
Exasol
No
Firebolt
No
SQL heredado de Google BigQuery
No
SQL estándar de Google BigQuery
Sí
PostgreSQL de Google Cloud
Sí
Google Cloud SQL
No
Google Spanner
No
Greenplum
Sí
HyperSQL
No
IBM Netezza
No
MariaDB
No
PostgreSQL de Microsoft Azure
Sí
Base de datos de Microsoft Azure SQL
No
Microsoft Azure Synapse Analytics
Sí
Microsoft SQL Server 2008 y versiones posteriores
No
Microsoft SQL Server 2012 y versiones posteriores
No
Microsoft SQL Server 2016
No
Microsoft SQL Server 2017 y versiones posteriores
No
MongoBI
No
MySQL
Sí
MySQL 8.0.12 y versiones posteriores
Sí
Oracle
No
Oracle ADWC
No
PostgreSQL 9.5 y versiones posteriores
Sí
PostgreSQL anterior a 9.5
Sí
PrestoDB
No
PrestoSQL
No
SAP HANA 2 y versiones posteriores
No
SingleStore
No
SingleStore 7 y versiones posteriores
No
Snowflake
Sí
Teradata
No
Trino
No
Vector
No
Vertica
Sí