PDT incrementales

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

Un PDT incremental es un PDT que Looker compila agregando datos actualizados a la tabla, en lugar de volver a compilarla por completo:

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

• Tablas agregadas
• PDT nativos de LookML (nativos)
• PDT basados en SQL

La primera vez que ejecutas una consulta en un PDT incremental, Looker compila el PDT completo para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede tardar mucho 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 el PDT incremental se configurará de manera estratégica y tardará menos tiempo.

Tenga en cuenta lo siguiente para los PDT incrementales:

• Las PDT incrementales solo son compatibles con 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 tabla debe definirse con el parámetro sql para que se use como 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 a fin de crear los incrementos para un PDT incremental. La tabla derivada no se puede definir con declaraciones personalizadas del lenguaje de definición de datos (DDL), ya que Looker no podría determinar qué declaraciones DDL se requieren para crear un incremento preciso.
• La tabla de origen de la PDT incremental se debe optimizar 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, claves de clasificación, índices o cualquier estrategia de optimización compatible con tu dialecto. Se recomienda la optimización de la tabla de origen porque Looker consulta cada vez que se actualiza la tabla de origen para determinar los últimos valores 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, es posible que la consulta de Looker sobre los valores más recientes sea lenta y costosa.

Definición de una PDT incremental

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

• increment_key (obligatorio para convertir el PDT en un PDT incremental): Define el período para el que se deben consultar los registros nuevos.
• {% incrementcondition %} Filtro líquido (obligatorio para hacer que un PDT basado en SQL sea un PDT incremental; no se aplica a los PDT basados 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 (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 tardíos, en los que los períodos anteriores pueden tener datos nuevos que no se incluyeron cuando el incremento correspondiente se compiló y agregó a la PDT originalmente.

Consulta la página de documentación sobre parámetros 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á en su totalidad la primera vez que se ejecute una consulta en ella. Luego, el PDT se volverá a compilar en incrementos de un día (increment_key: departure_date), hasta tres días después (increment_offset: 3).

La clave de incremento se basa en la dimensión departure_date, que en realidad es el período de 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 para 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 del PDT incremental determina cuándo aumenta el PDT. El compilador de PDT no modifica el PDT incremental a menos que se active la estrategia de persistencia de la tabla, o bien que el PDT se active de forma manual con la opción Rebuild Derived Tables & Run en la sección Explorar.
• Cuando el PDT aumenta, el compilador PDT determinará cuándo se agregaron previamente 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 principio del incremento de tiempo más reciente de la tabla y, luego, creará el incremento más reciente desde allí.
• Si el PDT tiene un parámetro increment_offset, el compilador también volverá a compilar la cantidad de períodos anteriores especificados en el parámetro increment_offset. Los períodos anteriores comienzan desde el principio del incremento de tiempo más actual (el que se define con el parámetro increment_key).

En las siguientes situaciones de ejemplo, se muestra cómo se actualizan las PDT 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 las siguientes propiedades:

• Clave de incremento: Fecha
• Desplazamiento del incremento: 3
• Estrategia de persistencia: Se activa una vez al mes el primer día del mes.

A continuación, le mostramos 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, el 1 de junio, por ejemplo, la última fila de la tabla se habrá agregado el 1 de mayo.
• Debido a que este PDT tiene una clave de incremento basada en la fecha, el compilador del PDT truncará el 1 de mayo al comienzo del día y volverá a compilar los datos para el 1 de mayo y hasta el día actual, el 1 de junio.
• Además, este 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 de tiempo anteriores (días) antes del 1 de mayo. Como resultado, los datos se vuelven a compilar para el 28, 29 y 30 de abril hasta el día 1 de junio.

En términos de SQL, este es el comando que ejecutará el compilador PDT el 1 de junio para determinar las filas del PDT existente que se debe 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 el compilador de PDT ejecutará 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 un PDT con las siguientes propiedades:

• Estrategia de persistencia: Se activa una vez al día.
• Clave de incremento: mes
• Desplazamiento del incremento: 0

A continuación, le mostramos 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, se habrá agregado la última fila de la tabla el 31 de mayo.
• Debido a que la clave de incremento se basa en el mes, el compilador de PDT se truncará desde el 31 de mayo hasta el principio 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 este PDT no tiene desplazamiento incremental, no se vuelven a compilar períodos anteriores.

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

• El 2 de junio, se habrá agregado la última fila de la tabla el 1 de junio.
• Debido a que el compilador de PDT se truncará al principio del mes de junio y luego se volverán a compilar los datos 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 este PDT no tiene desplazamiento incremental, no se vuelven a compilar períodos anteriores.

Ejemplo 3

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

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

En esta situación, se ilustra una configuración deficiente para un PDT incremental, ya que es un PDT de activación diaria con una compensación de tres meses. Esto significa que se reconstruirán 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 una situación interesante para examinar como una forma de entender cómo funcionan los PDT incrementales.

A continuación, le mostramos 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 se habrá agregado la última fila de la tabla el 31 de mayo.
• Debido a que la clave de incremento se basa en el mes, el compilador de PDT se truncará desde el 31 de mayo hasta el principio del mes y volverá a compilar los datos de todo mayo y hasta el día actual, incluido el 1 de junio.
• Además, este 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 de tiempo anteriores (meses) antes de mayo. Como resultado, los datos se vuelven a compilar desde febrero, marzo, abril y hasta el día 1 de junio actual.

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

• El 2 de junio, se habrá agregado la última fila de la tabla el 1 de junio.
• El compilador de PDT truncará el mes hasta el 1 de junio y volverá a compilar los datos para ese mes, 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 antes de junio. Como resultado, los datos se reconstruyen desde marzo, abril y mayo hasta el día 2 de junio.

Cómo probar un PDT incremental en el modo de desarrollo

Antes de implementar un nuevo PDT incremental en su entorno de producción, puede probarlo para asegurarse de que se compile y aumente. Para probar un PDT incremental en el modo de desarrollo:

1. Cree una exploración para el PDT:

   • En un archivo de modelo asociado, usa el parámetro include para incluir el archivo de vista de PDT en el archivo de modelo.
   • En el mismo archivo de modelo, usa el parámetro explore a fin de crear una exploración para la vista incremental del PDT.

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

2. Abre la sección Explorar del PDT. Para ello, seleccione el botón Ver acciones de archivo y, luego, un nombre de Explorar.

Sugerencia: Una vez que hayas incluido la vista en tu archivo de modelo y hayas creado Explorar, puedes navegar directamente a Explorar desde el archivo de vista o archivo de modelo en tu proyecto de LookML. Para ello, selecciona el menú See file actions ⏷ en la parte superior del archivo LookML.

1. En Explorar, seleccione algunas dimensiones o medidas y haga clic en Ejecutar. Luego, Looker compilará todo el PDT. Si esta es la primera consulta que ejecuta en el PDT incremental, el compilador creará el PDT completo para obtener los datos iniciales. Si la tabla es grande, la compilación inicial puede tardar mucho tiempo, al igual que la compilación de cualquier tabla grande.

2. Puedes verificar que el PDT inicial se compiló de las siguientes maneras:

   • Si tienes el permiso see_logs, puedes verificar que la tabla se haya compilado en el Registro de eventos de PDT. Si no ve la creación de eventos de PDT en el registro de eventos de PDT, verifique la información de estado en la parte superior de la exploración de registros de eventos de PDT. Si dice "Desde la caché", puedes seleccionar Borrar caché y actualizar para obtener información más reciente.
   • De lo contrario, puede ver los comentarios en la pestaña SQL de la barra de Datos de Explorar. La pestaña SQL muestra la consulta y las acciones que se realizarán cuando ejecute la consulta en Explorar. 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 cree la compilación inicial del PDT, solicite una compilación incremental del PDT mediante la opción Volver a compilar las tablas derivadas y ejecutar de Explorar.

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

   • Si tiene el permiso see_logs, puede usar el Registro de eventos de PDT para ver los eventos de create increment complete del PDT incremental. Si no ve este evento en el Registro de eventos de PDT y el estado de la consulta dice "from cache", seleccione Clear Cache & Refresh para obtener información más reciente.
   • Mire los comentarios en la pestaña SQL de la barra Datos de Explorar. En este caso, los comentarios indicarán que la PDT aumentó. Por ejemplo: -- increment persistent derived table e_incremental_pdt to generation 2

5. Una vez que hayas verificado que la PDT se compiló y aumenta de forma correcta, si no deseas mantener la función Explorar dedicada para la PDT, puedes quitar o comentar los parámetros explore y include de tu PDT de tu archivo de modelo.

Después de compilar la PDT en el modo de desarrollo, se usará la misma tabla para producción una vez que implemente sus cambios, a menos que realice más cambios 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 las 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 habiliten la eliminación y la inserción de filas.

En la siguiente tabla, se muestra qué dialectos admiten PDT incrementales en la versión más reciente de Looker:

Nota: Para Databricks, los PDT incrementales solo son compatibles con Databricks 12.1 y versiones posteriores.