PDT incrementales

En Looker, las tablas derivadas persistentes (PDT) se escriben en el esquema temporal 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 que se vuelva a compilar, Looker vuelve a compilar toda la tabla de forma predeterminada.

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

Una tabla grande con las tres filas inferiores destacadas para mostrar que se agregan algunas filas nuevas a la tabla.

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

La primera vez que ejecutas una consulta en una PDT incremental, Looker compila toda la PDT para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede tardar mucho tiempo, como ocurriría con cualquier tabla grande. Una vez que se compila la tabla inicial, las compilaciones posteriores serán incrementales y tardarán menos tiempo, si la 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 se admiten para las PDT que usan la estrategia de persistencia persist_for.
  • En el caso de las PDT basadas en SQL, la consulta de la tabla se debe definir con el parámetro sql para que se use como una PDT incremental. Las PDT basadas en SQL que se definen con el parámetro sql_create o el parámetro 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 de DDL se necesitarían para crear un incremento preciso.
  • La tabla de origen de la PDT incremental debe estar optimizada para 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, claves de ordenamiento, índices o cualquier estrategia de optimización que sea compatible con tu dialecto. Se recomienda encarecidamente optimizar la tabla de origen 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 obtener los valores más recientes puede ser lenta y costosa.
  • Las funciones analíticas de SQL no se admiten directamente en las PDT incrementales. Para usar una función analítica con una PDT incremental, compila la PDT incremental como una tabla base o intermedia sin la función analítica y, luego, haz referencia a esa PDT incremental en una tabla derivada o PDT independiente que aplique la función analítica.

Define una PDT incremental

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

  • increment_key (obligatorio para que la PDT sea incremental): Define el período durante el cual se deben consultar los registros nuevos.
  • Filtro de Liquid {% incrementcondition %} (obligatorio para que una PDT basada en SQL sea una PDT incremental; no se aplica a las PDT basadas en LookML): Conecta la clave de incremento a la columna de tiempo de la base de datos en la que se basa la clave de incremento. 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 (con 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 creó originalmente el incremento correspondiente y se agregó a la 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 persistentes nativas, tablas derivadas persistentes basadas en SQL y tablas de datos agregados.

A continuación, se muestra un ejemplo sencillo de un archivo de vista que define una PDT incremental basada 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á en su totalidad 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), con un retroceso de 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 de cómo funcionan los grupos de dimensiones). El grupo de dimensiones y el período se definen en la vista flights, que es la explore_source de esta PDT. Así es como 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 determina solo cuándo se incrementa la PDT. El compilador de PDT no modifica la PDT incremental, a menos que se active la estrategia de persistencia de la tabla o que la PDT se active de forma manual con la opción Recompilar tablas derivadas y ejecutar en una exploración.
  • Cuando la PDT se incremente, 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 actual (el período que define el parámetro increment_key). En función de eso, el compilador de PDT truncará los datos hasta el comienzo del incremento de tiempo más reciente en la tabla y, luego, compilará el incremento más reciente a partir de ahí.
  • Si la 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 al 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 una PDT con las siguientes propiedades:

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

A continuación, se muestra cómo se actualizará esta tabla:

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

En términos de SQL, este es el comando que el compilador de PDT ejecutará el 1 de junio para determinar las filas de la 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 de SQL que el compilador de PDT ejecutará el 1 de junio para compilar el incremento más reciente:

## 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: mes
  • Increment offset: 0

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

  • La estrategia de persistencia diaria significa que la tabla se compila automáticamente una vez al día. El 1 de junio, la última fila de la tabla se habrá agregado el 31 de mayo.
  • Como la clave de incremento se basa en el mes, el compilador de PDT truncará los datos 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 un desplazamiento de incremento, no se vuelven a compilar los 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, los volverá a compilar 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.
  • Debido a que esta PDT no tiene un desplazamiento de incremento, no se vuelven a compilar los períodos anteriores.

Ejemplo 3

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

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

Esta situación ilustra una configuración deficiente para una PDT incremental, ya que se trata de una PDT que se activa diariamente con un desplazamiento de tres meses. Esto significa que se volverán a compilar al menos tres meses de datos todos los días, lo que sería un uso muy ineficiente de una PDT incremental. Sin embargo, es un caso interesante para analizar como una forma de comprender cómo funcionan las PDT incrementales.

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

  • La estrategia de persistencia diaria significa 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.
  • Como la clave de incremento se basa en el mes, el compilador de PDT truncará los datos 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.
  • 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 anteriores (meses) antes de mayo. El resultado es que los datos se vuelven a compilar desde febrero, marzo, abril y hasta el día actual, el 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 al desplazamiento 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, mayo y hasta el día actual, 2 de junio.

Prueba una PDT incremental en el modo de desarrollo

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

  1. Crea una exploración para la PDT:

    • En un archivo de modelo asociado, usa el parámetro include para incluir el archivo de vista de la 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 la PDT incremental.
     include: "/views/e_faa_pdt.view"
     explore: e_faa_pdt {}
    
  2. Abre la exploración de la PDT. Para ello, selecciona el botón See file actions y, luego, selecciona un nombre de exploración.

  1. En la exploración, selecciona algunas dimensiones o mediciones y haz clic en Ejecutar. Luego, Looker compilará toda la PDT. Si esta es la primera consulta que ejecutas en la PDT incremental, el compilador de PDT compilará toda la PDT para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede tardar mucho tiempo, como ocurriría con cualquier tabla grande.

  2. Puedes verificar que la PDT inicial se haya creado de las siguientes maneras:

    • Si tienes el permiso see_logs, puedes verificar que la tabla se haya creado consultando 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 de estado en la parte superior de la exploración del registro de eventos de PDT. Si dice "from cache", puedes seleccionar Clear Cache & Refresh para obtener información más reciente.
    • De lo contrario, puedes consultar los comentarios en la pestaña SQL de la barra Datos de la exploración. En la pestaña SQL, se muestran la consulta y las acciones que se realizarán cuando la ejecutes en la exploración. Por ejemplo, si los comentarios en 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 la exploración.

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

    • Si tienes el permiso see_logs, puedes usar el registro de eventos de PDT para ver los eventos de 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 "desde la caché", selecciona Borrar caché y actualizar para obtener 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 creó 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 de tu archivo de modelo.

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

Soluciona problemas de las PDT incrementales

En esta sección, se describen algunos problemas habituales que puedes encontrar cuando usas PDT incrementales, así como los pasos para solucionarlos.

La PDT incremental no se compila después de un cambio de esquema

Si tu PDT incremental es una tabla derivada basada en SQL y el parámetro sql incluye un comodín como SELECT *, los cambios en el esquema de la base de datos subyacente (como la adición de columnas, la eliminación de columnas o el cambio del tipo de datos de las columnas) pueden hacer que la PDT falle con el siguiente error:

SQL Error in incremental PDT: Query execution failed

Para resolver este problema, edita la sentencia SELECT en el parámetro sql para seleccionar columnas individuales. Por ejemplo, si tu cláusula select es SELECT *, cámbiala a SELECT column1, column2, ....

Si tu esquema cambia y quieres volver a compilar tu PDT incremental desde cero, usa la llamada a la API start_pdt_build y, luego, incluye el parámetro full_force_incremental.

Las funciones analíticas muestran resultados inesperados en una PDT incremental

No se admite el uso directo de funciones analíticas de SQL en una PDT incremental. Debido a que las compilaciones incrementales solo procesan las filas del incremento de tiempo más reciente y cualquier increment_offset configurado, definir una función analítica directamente en una PDT incremental (como en un parámetro derived_column o sql) puede generar valores faltantes o cálculos inesperados en los límites de la partición cuando la tabla se incrementa.

Para usar una función analítica con una PDT incremental, define la PDT incremental como una PDT base o intermedia sin la función analítica y, luego, haz referencia a esa PDT incremental en una tabla derivada independiente o una PDT normal que aplique la función analítica.

Dialectos de bases de datos compatibles con las PDT incrementales

Para que Looker admita PDT incrementales en tu proyecto de Looker, el dialecto de tu base de datos debe admitir comandos de lenguaje de definición de datos (DDL) que permitan borrar e insertar filas.

En la siguiente tabla, se muestran los dialectos que admiten PDT incrementales en la versión más reciente de Looker (para Databricks, las PDT incrementales solo se admiten en la versión 12.1 y posteriores de Databricks):

Dialecto ¿Es compatible?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica