PDTs incrementales

En Looker, las tablas derivadas persistentes (PDTs) se escriben en el esquema Scratch de tu base de datos. Looker conserva y vuelve a compilar un PDT en función de su estrategia de persistencia. Cuando se activa la recompilación de un PDT, Looker recompila toda la tabla de forma predeterminada.

Una PDT incremental es una PDT que Looker crea añadiendo datos nuevos a la tabla en lugar de volver a crearla por completo:

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

• Tablas de agregación
• PDTs basadas en LookML (nativas)
• PDTs basadas en SQL

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

Ten en cuenta lo siguiente en el caso de las PDTs incrementales:

• Los PDT incrementales solo se admiten en los PDT que usan una estrategia de persistencia basada en activadores (datagroup_trigger, sql_trigger_value o interval_trigger). Los PDT incrementales no se admiten en los PDT que usan la estrategia de persistencia persist_for.
• En el caso de las PDTs basadas en SQL, la consulta de la tabla debe definirse con el parámetro sql para que se pueda usar como PDT incremental. Las PDTs 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 puede ver en el ejemplo 1 de esta página, Looker usa un comando INSERT o MERGE para crear los incrementos de un PDT incremental. La tabla derivada no se puede definir mediante instrucciones de lenguaje de definición de datos (DDL) personalizadas, ya que Looker no podría determinar qué instrucciones de DDL se necesitarían para crear un incremento preciso.
• La tabla de origen del PDT incremental debe optimizarse para las consultas basadas en el tiempo. En concreto, la columna basada en el tiempo que se usa para la clave de incremento debe tener una estrategia de optimización, como particiones, claves de ordenación, índices o cualquier otra estrategia de optimización que admita tu dialecto. Es muy recomendable optimizar la tabla de origen, ya que 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 como 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.

Definir un PDT incremental

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

• increment_key (obligatorio para que la PDT sea incremental): define el periodo durante el que se deben consultar los nuevos registros.
• {% incrementcondition %} Filtro de Liquid (obligatorio para convertir un PDT basado en SQL en un PDT incremental; no se aplica a los PDTs basados en LookML): conecta la clave incremental con la columna de tiempo de la base de datos en la que se basa la clave incremental. Para obtener más información, consulta la página de documentación de increment_key.
• increment_offset (opcional): número entero que define el número de periodos anteriores (con la granularidad de la clave de incremento) que se vuelven a compilar en cada compilación incremental. El parámetro increment_offset es útil en el caso de los datos que llegan tarde, en el que los periodos anteriores pueden tener datos nuevos que no se incluyeron cuando se creó y se añadió el incremento correspondiente al PDT.

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

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 creará por completo la primera vez que se ejecute una consulta en ella. Después, el PDT se volverá a generar 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 es el date periodo del grupo de dimensiones departure. Para obtener una descripción general de cómo funcionan los grupos de dimensiones, consulta la página de documentación del parámetro dimension_group. El grupo de dimensiones y el periodo se definen en la vista flights, que es la 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

Los ajustes increment_key y increment_offset de una PDT son independientes de la estrategia de persistencia de la PDT:

• La estrategia de persistencia del PDT incremental solo determina cuándo se incrementa el PDT. El creador de PDTs no modifica el PDT incremental a menos que se active la estrategia de persistencia de la tabla o que el PDT se active manualmente con la opción Recompilar tablas derivadas y ejecutar en una exploración.
• Cuando se incrementa el PDT, el generador de PDT determina cuándo se añadieron los datos más recientes a la tabla, en términos del incremento de tiempo más actual (el periodo definido por el parámetro increment_key). En función de eso, el compilador de PDT truncará los datos hasta el principio del incremento de tiempo más reciente de la tabla y, a partir de ahí, compilará el incremento más reciente.
• Si el PDT tiene un parámetro increment_offset, el creador de PDT también volverá a generar el número de periodos anteriores especificado en el parámetro increment_offset. Los periodos anteriores se remontan al inicio del incremento de tiempo más reciente (el periodo definido por el parámetro increment_key).

En los siguientes ejemplos se ilustra cómo se actualizan las PDTs incrementales mostrando la interacción de increment_key, increment_offset y la estrategia de persistencia.

Ejemplo 1

En este ejemplo se usa un PDT con estas propiedades:

• Clave de incremento: fecha
• Incremento de la compensación: 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 crea automáticamente una vez al mes. Por ejemplo, el 1 de junio, la última fila de la tabla se habrá añadido el 1 de mayo.
• Como este PDT tiene una clave de incremento basada en la fecha, el creador de PDT truncará el 1 de mayo hasta el principio del día y volverá a generar los datos del 1 de mayo hasta el día actual, el 1 de junio.
• Además, este PDT tiene un desfase de incremento de 3. Por lo tanto, el creador de PDT también vuelve a generar los datos de los tres periodos anteriores (días) al 1 de mayo. Como resultado, los datos se vuelven a generar para los días 28, 29 y 30 de abril, y hasta el 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 del PDT que se deben recompilar:

## 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 el creador de PDT ejecutará el 1 de junio para crear 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 un PDT con estas propiedades:

• Estrategia de persistencia: se activa una vez al día.
• Clave de incremento: mes
• Incremento de la diferencia: 0

Así 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á añadido el 31 de mayo.
• Como la clave de incremento se basa en el mes, el creador de PDT truncará los datos desde el 31 de mayo hasta el principio del mes y volverá a generar los datos de todo el mes de mayo y hasta el día actual, incluido el 1 de junio.
• Como este PDT no tiene ningún desfase de incremento, no se reconstruyen los periodos anteriores.

Así se actualizará esta tabla el 2 de junio:

• El 2 de junio, la última fila de la tabla se habrá añadido el 1 de junio.
• Como el creador de PDT volverá al principio del mes de junio y, a continuación, volverá a generar los datos a partir del 1 de junio hasta el día actual, los datos solo se volverán a generar para el 1 y el 2 de junio.
• Como este PDT no tiene ningún desfase de incremento, no se reconstruyen los periodos anteriores.

Ejemplo 3

En este ejemplo se usa un PDT con estas propiedades:

• Clave de incremento: mes
• Incremento de la compensación: 3
• Estrategia de persistencia: se activa una vez al día.

En este caso, la configuración de la PDT incremental no es adecuada, ya que se trata de una PDT que se activa a diario con un desfase de tres meses. Esto significa que se reconstruirán al menos tres meses de datos cada día, lo que sería un uso muy ineficiente de un PDT incremental. Sin embargo, es un caso interesante para examinar cómo funcionan las PDTs incrementales.

Así 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. Por ejemplo, el 1 de junio, la última fila de la tabla se habrá añadido el 31 de mayo.
• Como la clave de incremento se basa en el mes, el creador de PDT truncará los datos desde el 31 de mayo hasta el principio del mes y volverá a generar los datos de todo el mes de mayo y hasta el día actual, incluido el 1 de junio.
• Además, este PDT tiene un desfase de incremento de 3. Esto significa que el creador de PDT también vuelve a generar los datos de los tres periodos anteriores (meses) a mayo. Como resultado, los datos se vuelven a generar desde febrero, marzo y abril hasta el día actual, el 1 de junio.

Así se actualizará esta tabla el 2 de junio:

• El 2 de junio, la última fila de la tabla se habrá añadido el 1 de junio.
• El creador de PDT truncará el mes hasta el 1 de junio y volverá a generar los datos del mes de junio, incluido el 2 de junio.
• Además, debido al desfase de incremento, el generador de PDT volverá a compilar los datos de los tres meses anteriores a junio. Como resultado, los datos se vuelven a generar desde marzo, abril y mayo hasta el día actual, el 2 de junio.

Probar un PDT incremental en el modo Desarrollo

Antes de implementar un nuevo PDT incremental en tu entorno de producción, puedes probarlo para asegurarte de que se compila y se incrementa. Para probar un PDT incremental en el modo 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 un Explorar de la vista del PDT incremental.

    include: "/views/e_faa_pdt.view"
    explore: e_faa_pdt {}
   

2. Abre la exploración del PDT. Para ello, selecciona el botón Ver acciones de archivo y, a continuación, elige un nombre de Exploración.

1. En Exploración, seleccione algunas dimensiones o medidas y haga clic en Ejecutar. A continuación, Looker creará todo el PDT. Si es la primera consulta que ejecutas en el PDT incremental, el creador de PDT creará todo el PDT para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede llevar bastante tiempo, al igual que la compilación de cualquier tabla grande.

2. Puedes verificar que el PDT inicial se ha creado de las siguientes formas:

   • Si tienes el permiso see_logs, puedes comprobar que la tabla se ha creado consultando el registro de eventos de PDT. Si no ve que se creen eventos de PDT en el registro de eventos de PDT, consulte la información de estado en la parte superior de la exploración del registro de eventos de PDT. Si pone "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 Exploración. La pestaña SQL muestra la consulta y las acciones que se llevarán a cabo cuando ejecutes la consulta en Explorar. Por ejemplo, si en la pestaña SQL se indica -- generate derived table e_incremental_pdt,esa será la acción que se llevará a cabo cuando hagas clic en Ejecutar.

3. Una vez que hayas creado la compilación inicial de la PDT, solicita una compilación incremental de la PDT mediante la opción Recompilar tablas derivadas y ejecutar de Explorar.

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

   • Si tienes el permiso see_logs, puedes usar el registro de eventos de PDT para ver los eventos create increment complete del PDT incremental. Si no ve este evento en el registro de eventos de PDT y el estado de la consulta indica que se ha obtenido de la caché, seleccione Borrar caché y actualizar para obtener información más reciente.
   • Consulta los comentarios de la pestaña SQL de la barra Datos de Exploración. En este caso, los comentarios indicarán que se ha incrementado el PDT. Por ejemplo: -- increment persistent derived table e_incremental_pdt to generation 2

5. Una vez que hayas verificado que la PDT se ha creado y se incrementa correctamente, si no quieres conservar la Exploración dedicada a la PDT, puedes quitar o comentar los parámetros explore y include de la PDT en el archivo de modelo.

Una vez que se haya creado la PDT en el modo Desarrollo, se usará la misma tabla en producción cuando implementes los cambios, a menos que hagas más cambios en la definición de la tabla. Para obtener más información, consulta la sección Tablas persistentes en el modo Desarrollo de la página de documentación Tablas derivadas en Looker.

Solucionar problemas de PDTs incrementales

En esta sección se describen algunos problemas habituales que pueden surgir al usar PDTs incrementales, así como los pasos para solucionarlos.

La compilación incremental de PDT falla después de cambiar el 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 o eliminación de columnas, o el cambio del tipo de datos de una columna) pueden provocar que el PDT falle y muestre el siguiente error:

SQL Error in incremental PDT: Query execution failed

Para solucionar este problema, edita la instrucción SELECT del parámetro sql para seleccionar columnas concretas. Por ejemplo, si la cláusula SELECT es SELECT *, cámbiala a SELECT column1, column2, ....

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

Dialectos de bases de datos compatibles con PDTs incrementales

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

En la siguiente tabla se muestra qué dialectos admiten PDTs incrementales en la versión más reciente de Looker (en Databricks, los PDTs incrementales solo se admiten en la versión 12.1 y posteriores de Databricks):