Crear tablas

En Dataform, una tabla es uno de los tipos de objetos que conforman un flujo de trabajo. Puedes crear tablas que hagan referencia a datos de las fuentes de datos declaradas para tu flujo de trabajo o de otras tablas de tu flujo de trabajo. Dataform compila tus definiciones de tablas en SQL en tiempo real. Cuando activas la ejecución, Dataform ejecuta el código SQL y crea las tablas definidas en BigQuery.

Puedes crear los siguientes tipos de tablas en un archivo type: "table" SQLX:

También puedes definir particiones y clústeres de tablas.

Para mantener un registro del propósito de una tabla o su relación con otras tablas en tu flujo de trabajo, puedes agregar documentación a la tabla o a sus columnas seleccionadas.

Para probar los datos de una tabla con condiciones específicas, puedes crear consultas de prueba de calidad de los datos llamadas aserciones. Dataform ejecuta aserciones cada vez que actualiza tu flujo de trabajo y te avisa si alguna falla.

Para anular el esquema, la base de datos y el nombre predeterminados de una tabla seleccionada, puedes anular la configuración de la tabla.

Para inhabilitar la creación de tablas o ejecutar una instrucción de SQL antes o después de crear una tabla, puedes configurar acciones adicionales.

Para organizar tus tablas en BigQuery después de ejecutarlas, puedes agregar etiquetas de BigQuery. Para obtener más información, consulta Introducción a las etiquetas.

Para restringir el acceso a los datos a nivel de la columna de la tabla, puedes agregar etiquetas de política de BigQuery. Para obtener más información, consulta Introducción al control de acceso a nivel de columna.

Para conservar las políticas de datos y las etiquetas de administración de datos (versión preliminar) de BigQuery que aplican los sistemas externos en las ejecuciones de tablas, puedes conservar los metadatos de los controles de acceso.

Además de definir tablas en un archivo SQLX type: "table", puedes crear tablas vacías definiendo una consulta en SQL personalizada en un archivo SQLX type: "operations". Es posible que quieras crear una tabla vacía para que otro servicio pueda propagarla con datos.

Antes de comenzar

  1. En la consola de Google Cloud , ve a la página Dataform.

    Ir a Dataform

  2. Crea e inicializa un espacio de trabajo de desarrollo en tu repositorio.

  3. Opcional: Declara una fuente de datos.

Roles obligatorios

Para obtener los permisos que necesitas para completar las tareas de este documento, pídele a tu administrador que te otorgue los siguientes roles de IAM:

Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

También puedes obtener los permisos necesarios a través de roles personalizados o cualquier otro rol predefinido.

Para conservar los metadatos de los controles de acceso, la cuenta de servicio que ejecuta tus flujos de trabajo de Dataform debe tener permisos de IAM específicos según la tarea:

  • Extraer metadatos: Para leer la vista INFORMATION_SCHEMA.COLUMNS, la cuenta de servicio debe tener permisos de lectura en el conjunto de datos o el esquema de la tabla. Estos permisos se incluyen en los siguientes roles de IAM:

  • Aplica etiquetas de administración de datos: Para adjuntar y administrar etiquetas de administración de datos en tablas y vistas, la cuenta de servicio necesita los permisos bigquery.tables.createTagBinding y bigquery.tables.deleteTagBinding. Estos permisos se incluyen en los siguientes roles predefinidos de IAM:

  • Aplicar políticas de datos: Para crear y actualizar políticas de datos (incluidas las políticas sin etiquetas), la cuenta de servicio necesita los permisos bigquery.dataPolicies.create y bigquery.dataPolicies.update. Estos permisos se incluyen en los siguientes roles predefinidos de IAM:

Crea una tabla

En esta sección, se muestra cómo crear tablas con Dataform core en Dataform.

Acerca de las definiciones de tablas

Para definir una tabla, debes definir el tipo de tabla y escribir una instrucción SELECT en un archivo type: "table" SQLX. Luego, Dataform compila tu código de Dataform Core en SQL, ejecuta el código de SQL y crea las tablas definidas en BigQuery.

En una instrucción SELECT principal de Dataform, defines la estructura de la tabla y haces referencia a otros objetos de tu flujo de trabajo.

Además de definir tablas en un archivo SQLX type: "table", puedes crear tablas vacías definiendo una consulta en SQL personalizada en un archivo SQLX type: "operations". Para obtener más información, consulta Crea una tabla vacía.

Cómo hacer referencia a dependencias con ref

Para hacer referencia a una acción de flujo de trabajo en una declaración SELECT y agregarla automáticamente como una dependencia, usa la función ref. Dataform ejecuta las dependencias antes de las tablas que dependen de ellas para verificar el orden correcto de la canalización.

La función ref es una función integrada de Dataform Core que es fundamental para la administración de dependencias en Dataform. La función ref te permite hacer referencia a los siguientes objetos definidos en tu flujo de trabajo de Dataform y depender de ellos automáticamente, en lugar de codificar de forma rígida el esquema y los nombres de las tablas:

Dataform usa la función ref para compilar un árbol de dependencia de todas las tablas que se crearán o actualizarán.

Después de la compilación, Dataform agrega sentencias estándar a la sentencia de SQL, como CREATE, REPLACE, INSERT o MERGE.

En la siguiente muestra de código, se muestra una definición de tabla con el uso de la función ref:

config { type: "table" }

SELECT
  order_date AS date,
  order_id AS order_id,
  order_status AS order_status,
  SUM(item_count) AS item_count,
  SUM(amount) AS revenue

FROM ${ref("store_clean")}

GROUP BY 1, 2

En la función ref, proporcionas el nombre de la declaración de la tabla o de la fuente de datos de la que quieres depender. Por lo general, es el nombre del archivo SQLX en el que se define esa declaración de tabla o fuente de datos.

Si se anula el nombre de una tabla, usa el nombre anulado en la función ref. Por ejemplo, haz referencia a una tabla con config { name: "overridden_name" } como ref("overridden_name"). Para obtener más información, consulta Anula la configuración de la tabla y Haz referencia a una tabla con un nombre de tabla anulado.

Cuando tienes varias tablas con el mismo nombre en diferentes esquemas, puedes hacer referencia a una tabla específica proporcionando dos argumentos a la función ref: el nombre del esquema y el nombre de la tabla.

En la siguiente muestra de código, se indica la función ref con dos argumentos para especificar una tabla dentro de un esquema específico:

config { type: "table" }
SELECT * FROM ${ref("schema", "store_clean")}

También puedes agregar dependencias de tablas de forma manual al bloque config para tablas, aserciones, declaraciones de fuentes de datos o operaciones de SQL personalizadas a las que no se hace referencia en una función ref en la sentencia SELECT. Dataform ejecuta estas dependencias antes de las tablas dependientes.

En el siguiente muestra de código, se muestra una dependencia de tabla en el bloque config:

config { dependencies: [ "unreferenced_table" ] }
SELECT * FROM ...

Para obtener más información sobre la administración de dependencias en tu flujo de trabajo, consulta Establece dependencias.

Cómo hacer referencia a otras tablas con resolve

La función resolve te permite hacer referencia a una declaración de tabla o fuente de datos en una sentencia SELECT, como la función ref, pero no agrega la referencia como una dependencia. Esto significa que el objeto al que se hace referencia con la función resolve no afecta la ejecución de la tabla que usa la función resolve.

Para obtener más información sobre las funciones principales integradas de Dataform, consulta la referencia principal de Dataform.

Crea un archivo SQLX para la definición de tablas

Almacena los archivos SQLX de definición de tablas en el directorio definitions/. Para crear un archivo .sqlx nuevo en el directorio definitions/, sigue estos pasos:

  1. En la consola de Google Cloud , ve a la página Dataform.

    Ir a Dataform

  2. Para abrir un repositorio, haz clic en su nombre.

  3. Para abrir un espacio de trabajo de desarrollo, haz clic en su nombre.

  4. En el panel Archivos, junto a definitions/, haz clic en Más.

  5. Haz clic en Crear archivo.

  6. En el campo Agregar una ruta de archivo, ingresa el nombre del archivo seguido de .sqlx después de definitions/. Por ejemplo, definitions/my-table.sqlx.

    Los nombres de archivo solo pueden incluir números, letras, guiones y guiones bajos.

  7. Haz clic en Crear archivo.

Define el tipo de tabla

Para crear una nueva definición de tipo de tabla, sigue estos pasos:

  1. En tu espacio de trabajo de desarrollo, en el panel Archivos, expande el directorio definitions/.
  2. Selecciona el archivo SQLX de definición de tabla que deseas editar.

  3. En el archivo, ingresa el siguiente fragmento de código:

    config { type: "TABLE_TYPE" }

    Reemplaza TABLE_TYPE por uno de los siguientes tipos de tablas:

    • table
    • incremental
    • view

  4. Opcional: Para definir una vista materializada, ingresa la propiedad materialized en type: "view" con el siguiente formato:

    config {
  type: "view",
  materialized: true
}

    Para obtener más información, consulta ITableConfig.

  5. Opcional: Haz clic en Formato.

Define la estructura y las dependencias de la tabla

Para escribir una sentencia SELECT de definición de tabla y definir la estructura y las dependencias de la tabla, sigue estos pasos:

  1. En tu espacio de trabajo de desarrollo, en el panel Archivos, expande el directorio definitions/.
  2. Selecciona el archivo SQLX de definición de tabla que deseas editar.
  3. Debajo del bloque config, escribe una sentencia SELECT.
  4. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica una definición de tabla con una sentencia SELECT y la función ref:

config { type: "table" }
SELECT
  customers.id AS id,
  customers.first_name AS first_name,
  customers.last_name AS last_name,
  customers.email AS email,
  customers.country AS country,
  COUNT(orders.id) AS order_count,
  SUM(orders.amount) AS total_spent
FROM
  dataform-samples.dataform_sample.crm_customers AS customers
  LEFT JOIN ${ref('order_stats')} orders
    ON customers.id = orders.customer_id

WHERE
  customers.id IS NOT NULL
  AND customers.first_name <> 'Internal account'
  AND country IN ('UK', 'US', 'FR', 'ES', 'NG', 'JP')

GROUP BY 1, 2, 3, 4, 5

Cómo agregar dependencias de tablas manuales

Para agregar dependencias de tablas a las que no se hace referencia en la sentencia SELECT, pero que deben ejecutarse antes de la tabla actual, sigue estos pasos:

  1. En tu espacio de trabajo de desarrollo, en el panel Archivos, expande el directorio definitions/.
  2. Selecciona el archivo SQLX de definición de tabla que deseas editar.

  3. En el bloque config de la tabla, ingresa el siguiente fragmento de código:

    dependencies: [ "DEPENDENCY_TABLE", ]

    Reemplaza DEPENDENCY_TABLE por el nombre del archivo de la tabla que deseas agregar como dependencia. Puedes ingresar varios nombres de archivo.

  4. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se muestran dos tablas agregadas como dependencias de tablas manuales al bloque config de un archivo de definición de tabla:

config { dependencies: [ "some_table", "some_other_table" ] }

Anula la configuración de la tabla

Puedes anular el esquema, la base de datos y el nombre predeterminados de una tabla seleccionada.

De forma predeterminada, una tabla sigue la configuración del esquema y la base de datos que estableciste en workflow_settings.yaml. El nombre de una tabla es el mismo que el del archivo SQLX de definición de la tabla.

Para anular el esquema y el nombre de una tabla seleccionada, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.

  2. En el panel Archivos, expande definitions/.

  3. Abre un archivo de definición de tablas SQLX.

  4. En el bloque config, ingresa el siguiente fragmento de código:

     {
   schema: "OVERRIDDEN_SCHEMA",
   database: "OVERRIDDEN_DATABASE",
   name: "OVERRIDDEN_NAME"
 }

    Reemplaza lo siguiente:

    • OVERRIDDEN_SCHEMA: Es el conjunto de datos de BigQuery en el que deseas crear la tabla.

    • OVERRIDDEN_DATABASE: Es el ID del proyecto de BigQuery en el que deseas crear la tabla.

    • OVERRIDDEN_NAME: Es el nombre de la tabla, que es diferente del nombre de archivo de la definición de la tabla en SQLX.

  5. Opcional: Haz clic en Formato.

Para obtener más información, consulta Haz referencia a una tabla con un nombre de tabla anulado.

Crea tablas administradas de Apache Iceberg

Usa Dataform para crear tablas de BigQuery en formato de tabla de Iceberg. Estas tablas se conocen como tablas administradas de Iceberg. Para obtener más información sobre los roles obligatorios y otras tareas de configuración, consulta los pasos de Antes de comenzar para los flujos de trabajo de tablas administradas por Iceberg.

Crea la definición de la tabla de Iceberg

Para crear las tablas, define la configuración de Iceberg en el bloque bigquery de un archivo de definición de tablas.

Para definir las tablas, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Abre un archivo SQLX de definición de tablas.

  4. En el bloque config, agrega un bloque bigquery que contenga un bloque iceberg con el siguiente formato:

    config {
  type: "table",
  name: "my_table",
  uniqueKey: ["uniquekey"],
  bigquery: {
    iceberg: {
      bucket_name: "BUCKET_NAME"
    }
  }
}

    Reemplaza BUCKET_NAME por el nombre del bucket de Cloud Storage en el que se almacenan los datos de la tabla.

    Otras propiedades son opcionales y tienen valores predeterminados si no se especifican. Para obtener más información, consulta las propiedades de iceberg.

    Puedes proporcionar los valores para cada tabla o establecer valores predeterminados a nivel del flujo de trabajo en workflow_settings.yaml. Puedes anular los valores predeterminados a nivel del flujo de trabajo configurando de forma explícita las propiedades en el bloque iceberg.

Crea particiones y clústeres de tablas

En esta sección, se muestra cómo usar Dataform Core para crear particiones y clústeres de tablas. BigQuery admite tablas particionadas y agrupamiento en clústeres de tablas. Para obtener más información, consulta Introducción a las tablas particionadas y Crea y usa tablas agrupadas en clústeres.

Crea una partición de tabla

Para crear una partición de tabla, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Abre un archivo SQLX de definición de tablas.

  4. En el bloque config, agrega el bloque bigquery debajo de la declaración del tipo de tabla con el siguiente formato:

    config {
  type: "table",
  bigquery: {
  }
}

  5. En el bloque bigquery, ingresa el siguiente fragmento de código:

        partitionBy: "PARTITION_EXPRESSION"

    Reemplaza PARTITION_EXPRESSION por una expresión para particionar la tabla.

  6. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica cómo particionar una tabla por hora en un archivo SQLX de definición de tabla:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATETIME_TRUNC(<timestamp_column>, HOUR)"
  }
}

En la siguiente muestra de código, se muestra cómo particionar una tabla por un valor de número entero en un archivo SQLX de definición de tabla:

config {
  type: "table",
  bigquery: {
    partitionBy: "RANGE_BUCKET(<integer_column>, GENERATE_ARRAY(0, 1000000, 1000))"
  }
}

Cómo establecer un filtro de partición

Para establecer un filtro de partición, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Abre un archivo SQLX de definición de tabla particionada.

  4. En el bloque bigquery, ingresa el siguiente fragmento de código:

    requirePartitionFilter : true

  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica un filtro de partición establecido en el bloque bigquery de un archivo SQLX de tabla particionada:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    requirePartitionFilter : true
  }
}
SELECT CURRENT_TIMESTAMP() AS ts

Para obtener más información sobre el filtro de partición en BigQuery, consulta Configura el atributo de filtro de partición requerido en una tabla particionada.

Establece un período de retención para las particiones

Para controlar la retención de todas las particiones en una tabla particionada, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Abre un archivo SQLX de definición de tabla particionada.

  4. En el bloque bigquery, ingresa el siguiente fragmento de código:

    partitionExpirationDays: NUMBER_OF_DAYS

    Reemplaza NUMBER_OF_DAYS por la cantidad de días que deseas conservar las particiones.

  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se muestra un período de retención para las particiones establecido en 14 días en el bloque bigquery de un archivo SQLX de tabla particionada:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    partitionExpirationDays: 14,
  }
}
SELECT CURRENT_TIMESTAMP() AS ts

Crea un clúster de tablas

Para crear un clúster de tablas, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Abre un archivo SQLX de definición de tablas.

  4. En el bloque bigquery, ingresa el siguiente fragmento de código:

        clusterBy: ["CLUSTER_COLUMN"]

    Reemplaza CLUSTER_COLUMN por el nombre de la columna según la cual deseas agrupar la tabla. Para obtener más información, consulta clustering_column_list.

  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica una tabla particionada agrupada por las columnas name y revenue:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    clusterBy: ["name", "revenue"]
  }
}
SELECT CURRENT_TIMESTAMP() as ts, name, revenue

Configura una tabla incremental

En esta sección, se muestra cómo usar Dataform Core para configurar una tabla incremental.

Acerca de las tablas incrementales

Dataform actualiza las tablas de manera diferente según el tipo de tabla. Durante cada ejecución de una tabla o vista, Dataform vuelve a compilar toda la tabla o vista desde cero.

Cuando defines una tabla incremental, Dataform la compila desde cero solo la primera vez. Durante las ejecuciones posteriores, Dataform solo inserta o combina filas nuevas en la tabla incremental según las condiciones que configures.

Dataform inserta filas nuevas solo en las columnas que ya existen en la tabla incremental. Si realizas cambios en la consulta de definición de la tabla incremental, por ejemplo, agregando una columna nueva, debes decidir si debes volver a compilar la tabla desde cero. Para volver a compilar la tabla, la próxima vez que actives una ejecución de tabla, selecciona la opción Ejecutar con actualización completa. Para conocer otras opciones, consulta Cómo cambiar el esquema de una tabla incremental sin una actualización completa.

Estos son algunos casos de uso comunes de las tablas incrementales:

Optimización del rendimiento
Para algunos tipos de datos, como los registros web o los datos de estadísticas, es posible que solo quieras procesar los registros nuevos en lugar de volver a procesar toda la tabla.
Reducción de la latencia
Puedes usar tablas incrementales para ejecutar flujos de trabajo de forma rápida, pero frecuente, lo que reduce la latencia downstream de las tablas de salida.
Instantáneas diarias
Puedes configurar una tabla incremental para crear instantáneas diarias de los datos de la tabla, por ejemplo, para el análisis longitudinal de la configuración del usuario almacenada en una base de datos de producción.

Procesa un subconjunto de filas en una tabla incremental

Para determinar un subconjunto de filas que Dataform procesará durante cada ejecución, agrega una cláusula WHERE condicional al archivo de definición de SQLX de la tabla incremental. En la cláusula WHERE, puedes especificar una condición incremental y una no incremental. Dataform aplica la condición incremental durante la ejecución de la tabla sin una actualización completa y la condición no incremental durante la ejecución con una actualización completa.

Para configurar una tabla incremental, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Abre un archivo SQLX de definición de tablas incrementales.

  4. Ingresa una cláusula WHERE con el siguiente formato:

    config { type: "incremental" }

SELECT_STATEMENT

${when(incremental(), `WHERE INCREMENTAL_CONDITION`, `WHERE NON_INCREMENTAL_CONDITION`) }

    Reemplaza lo siguiente:

    • SELECT_STATEMENT: la sentencia SELECT que define tu tabla.
    • INCREMENTAL_CONDITION: Es la condición que especificas en la cláusula WHERE para seleccionar las filas que Dataform procesará durante la ejecución de la tabla sin una actualización completa.
    • NON_INCREMENTAL_CONDITION: Es la condición que especificas en la cláusula WHERE para seleccionar las filas que Dataform procesará durante la ejecución de la tabla con una actualización completa.

  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica una tabla incremental que procesa de forma incremental las filas de la tabla productiondb.logs:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(),
   `WHERE date > (SELECT MAX(date) FROM ${self()}) AND country = "UK"`,
   `WHERE country = "UK"`)}

En la siguiente muestra de código, se indica una tabla incremental que crea una instantánea de la tabla productiondb.customers:

config { type: "incremental" }

SELECT CURRENT_DATE() AS snapshot_date, customer_id, name, account_settings FROM ${ref("productiondb", "customers")}

${when(incremental(), `WHERE snapshot_date > (SELECT MAX(snapshot_date) FROM ${self()})`) }

Combina filas en una tabla incremental

Para verificar que una tabla incremental contenga solo una fila correspondiente a una combinación de columnas seleccionada, establece las columnas seleccionadas como uniqueKey para combinar las filas que tengan el mismo valor de uniqueKey. Cuando se actualiza la tabla, Dataform combina las filas con el mismo valor de uniqueKey en lugar de agregarlas.

Para configurar la combinación en una tabla incremental, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Selecciona un archivo SQLX de definición de tabla incremental

  4. En el bloque config, configura las columnas seleccionadas como uniqueKey en el siguiente formato:

    uniqueKey: ["COLUMN_NAME"]

    Reemplaza COLUMN_NAME por el nombre de una columna seleccionada.

  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica una tabla incremental con la columna transaction_id establecida como uniqueKey para verificar que siempre contenga una fila:

config {
  type: "incremental",
  uniqueKey: ["transaction_id"]
}

SELECT timestamp, action FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Cómo filtrar filas en una tabla incremental

En una tabla particionada incremental, para evitar que Dataform analice toda la tabla en busca de filas que coincidan, configura updatePartitionFilter para que solo considere un subconjunto de registros.

En la siguiente muestra de código, se indica una tabla particionada incremental con combinación configurada por las propiedades updatePartitionFilter y uniqueKey, con dos tipos distintos de filtrado:

  • Eliminación de destino con la propiedad updatePartitionFilter: limita las particiones analizadas en la tabla de destino durante una operación MERGE para encontrar filas que coincidan con uniqueKey.
  • Reducción de origen con una cláusula WHERE: limita las filas que se recuperan de la tabla de origen durante una ejecución incremental. Esto se logra con la función ${when(incremental(), ...)}.
config {
  type: "incremental",
  uniqueKey: ["transaction_id"],
  bigquery: {
    partitionBy: "DATE(timestamp)",
    updatePartitionFilter:
        "timestamp >= timestamp_sub(current_timestamp(), interval 24 hour)"
  }
}

SELECT timestamp, action FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Evita los análisis completos de la tabla cuando realices la transferencia de datos desde una tabla particionada

Cuando creas una tabla incremental que hace referencia a una tabla particionada, te recomendamos que compiles la consulta de la tabla para evitar análisis completos de la tabla particionada durante cada actualización incremental.

Puedes limitar la cantidad de particiones que analiza BigQuery para actualizar la tabla incremental con una expresión constante en la consulta de la tabla. Para convertir un valor de la tabla particionada en una expresión constante, usa secuencias de comandos de BigQuery para declarar el valor como una variable en el bloque pre_operations. Luego, usa la variable como una expresión constante en una cláusula WHERE en la consulta SELECT.

Con esta configuración, Dataform actualiza la tabla incremental en función de las particiones más recientes de la tabla particionada a la que se hace referencia, sin analizar toda la tabla.

Para configurar una tabla incremental que haga referencia a una tabla particionada y evite los análisis de tabla completa, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Selecciona un archivo SQLX de definición de tabla incremental
  4. En el bloque pre_operations, declara una variable con secuencias de comandos de BigQuery.
  5. Filtra la instrucción SELECT que define la tabla con una cláusula WHERE que hace referencia a la variable declarada.
  6. Opcional: Haz clic en Formato.

En el siguiente muestra de código, se muestra una tabla incremental en la que la tabla raw_events a la que se hace referencia está particionada por event_timestamp:

config {
  type: "incremental",
}

pre_operations {
  DECLARE event_timestamp_checkpoint DEFAULT (
    ${when(incremental(),
    `SELECT max(event_timestamp) FROM ${self()}`,
    `SELECT timestamp("2000-01-01")`)}
  )
}

SELECT
  *
FROM
  ${ref("raw_events")}
WHERE event_timestamp > event_timestamp_checkpoint

En la muestra de código anterior, la variable event_timestamp_checkpoint se define en el bloque pre_operations. Luego, la variable event_timestamp_checkpoint se usa como una expresión constante en la cláusula WHERE.

Cómo recompilar una tabla incremental desde cero con una actualización completa

Puedes forzar la recompilación de una tabla incremental desde cero con la interfaz de línea de comandos con la opción --full-refresh o la opción Ejecutar con actualización completa cuando activas una ejecución de flujo de trabajo.

Cuando seleccionas la opción de actualización completa, en tu espacio de trabajo de desarrollo o con la CLI de Dataform, Dataform ignora el parámetro ${when(incremental(), ... } durante la ejecución y vuelve a crear la tabla con una sentencia CREATE OR REPLACE.

Cómo proteger una tabla incremental de la actualización completa

Para proteger una tabla incremental de que se vuelva a compilar desde cero y de una posible pérdida de datos, puedes establecer la tabla incremental como protected. Es posible que desees evitar que se vuelva a compilar una tabla incremental si tu fuente de datos es temporal.

Para marcar una tabla incremental como protected, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Selecciona un archivo SQLX de definición de tabla incremental.
  4. En el bloque config, ingresa protected: true.
  5. Opcional: Haz clic en Formato.

En el siguiente muestra de código, se muestra una tabla incremental marcada como protected:

config {
  type: "incremental",
  protected: true
}
SELECT ...

Cambia un esquema de tabla incremental sin una actualización completa

Puedes modificar un esquema de tabla incremental en la consulta SELECT sin actualizar por completo la tabla con la propiedad de configuración onSchemaChange. Esta propiedad te permite agregar columnas nuevas a una tabla o quitar columnas existentes de ella, y conservar los datos históricos. Este enfoque ayuda a evitar la pérdida de datos y las actualizaciones manuales en BigQuery.

Para usar esta función, debes instalar Dataform Core 3.0.11 o una versión posterior.

Después de configurar la propiedad onSchemaChange, Dataform verifica la consulta SELECT para determinar si se agregaron o quitaron columnas. Luego, Dataform realiza la acción especificada por el valor de la propiedad onSchemaChange.

Para establecer la propiedad onSchemaChange, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Selecciona un archivo SQLX de definición de tabla incremental.

  4. En el bloque config, ingresa el siguiente fragmento de código:

    onSchemaChange: "ON_CHANGE_ACTION"

    Reemplaza ON_CHANGE_ACTION por una de las siguientes acciones:

    • IGNORE (predeterminado): Ignora las columnas agregadas y muestra un error para las columnas faltantes. Si no se establece onSchemaChange, este es el comportamiento predeterminado cuando cambia el esquema.
    • FAIL: Detiene la acción si Dataform detecta algún cambio en el esquema, lo que ayuda a mantenerlo coherente.
    • EXTEND: Agrega columnas nuevas de la consulta a la tabla incremental y agrega valores de NULL para los registros anteriores. Muestra un error si se quita una columna o falta en el esquema original de la consulta. Puedes usar este parámetro de configuración para agregar columnas nuevas a tus tablas incrementales durante el entorno de ejecución.

    • SYNCHRONIZE: Agrega columnas nuevas de la consulta a la tabla incremental y agrega valores de NULL para los registros anteriores. Quita las columnas que estaban en el esquema original, pero que ahora faltan en la consulta actual.

  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica una tabla incremental con la acción EXTEND establecida para la propiedad onSchemaChange:

config {
    type: "incremental",
    onSchemaChange: "EXTEND",
}
SELECT ...

Agrega documentación de la tabla

En esta sección, se muestra cómo agregar descripciones de una tabla, sus columnas y sus registros a un archivo SQLX principal de Dataform.

Puedes agregar descripciones de tablas, columnas y registros a todos los tipos de tablas en Dataform: tablas, tablas incrementales y vistas.

Te recomendamos que documentes lo siguiente:

  • El propósito de la tabla.
  • El contenido o la función de las columnas o los registros de la tabla
  • La relación de la tabla y otras acciones de tu flujo de trabajo, por ejemplo, las tablas o vistas que dependen de la tabla actual.
  • Son las aserciones aplicadas a la tabla.
  • Son las operaciones previas o posteriores que se aplican a la tabla.
  • El propietario de la tabla, es decir, el usuario que la creó. Esta información puede ser útil si varios miembros del equipo trabajan en un flujo de trabajo.

Agrega metadatos para Knowledge Catalog

Los metadatos estándar de BigQuery (por ejemplo, conjuntos de datos, tablas y vistas) están disponibles automáticamente en Knowledge Catalog.

También puedes definir metadatos personalizados para tus tablas y vistas directamente en el bloque de configuración de tus archivos .sqlx. Cuando se completa correctamente una acción, Dataform inicia automáticamente una sincronización de metadatos con Knowledge Catalog. Este proceso de enriquecimiento actualiza Knowledge Catalog con los metadatos semánticos definidos en tu configuración de SQLX.

Usa la clave de metadatos para especificar información del Knowledge Catalog. El proceso de enriquecimiento admite las siguientes construcciones de metadatos:

  • Descripción general: documentación y texto de resumen de la entrada. Se requiere la versión 3.0.37 o posterior de Dataform Core.
  • Aspectos genéricos: Detalles semánticos, como información del sistema y el tipo de tabla. Requiere la versión 3.0.52 o posterior de Dataform Core.

En el siguiente ejemplo de configuración, se muestra cómo agregar una descripción general y aspectos de metadatos genéricos a una configuración de tabla para Knowledge Catalog:

config {
  type: "table",
  metadata: {
    overview: "This table provides standardized trip data.",
    extraProperties: {
        generic: {
              system: "BigQuery",
              type: "table"
        }
      }
  }
}

Para verificar el estado de una actualización de metadatos, consulta Cómo inspeccionar los registros de ejecución del espacio de trabajo para los flujos de trabajo de Dataform o Cómo ver ejecuciones manuales anteriores para las canalizaciones de BigQuery.

Para verificar los metadatos sincronizados, puedes buscar el recurso en Knowledge Catalog. Para obtener más información, consulta Busca recursos.

Agrega una descripción de la tabla

Para agregar una descripción a una tabla en un archivo SQLX, sigue estos pasos:

  1. En la consola de Google Cloud , ve a la página Dataform.

    Ir a Dataform

  2. Selecciona un repositorio.

  3. Selecciona un espacio de trabajo de desarrollo.

  4. En el panel Archivos, haz clic en el archivo SQLX de definición de tabla que deseas editar.

  5. En el bloque config del archivo, ingresa la descripción de la tabla en el siguiente formato:

    description: "Description of the table",

  6. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se muestra una descripción de la tabla agregada al bloque config de un archivo de definición de tabla SQLX:

config {
  type: "table",
  description: "Description of the table",
 }

Agrega descripciones de columnas y registros

Para agregar descripciones de columnas y registros individuales a un archivo SQLX, sigue estos pasos:

  1. En el bloque config del archivo de definición de la tabla, ingresa columns: {}.

  2. Dentro de columns: {}, ingresa las descripciones de las columnas con el siguiente formato:

    column_name: "Description of the column",

  3. Dentro de columns: {}, ingresa las descripciones de los registros con el siguiente formato:

      record_name: {
      description: "Description of the record",
      columns: {
        record_column_name: "Description of the record column"
      }
}

  4. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indican las descripciones de tablas, columnas y registros en el bloque config de un archivo de definición de tablas de SQLX:

config {
  type: "table",
  description: "Description of the table.",
  columns: {
    column1_name: "Description of the first column",
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    record_name: {
      description: "Description of the record.",
      columns: {
       record_column1_name: "Description of the first record column",
       record_column2_name: "Description of the second record column",
      }
    }
  }
}
SELECT
  "first_column_value" AS column_1_name,
  "second_column_value" AS column_2_name,
  "third_column_value" AS column_3_name,
  STRUCT("first" AS record_column1_name,
    "second" AS record_column2_name) AS record_name

Reutiliza la documentación de columnas con includes

Puedes volver a usar las descripciones de las columnas en Dataform en todo tu flujo de trabajo de SQL con inclusiones de JavaScript. Te recomendamos que reutilices la documentación de las columnas si tienes varias columnas con el mismo nombre y la misma descripción en tu flujo de trabajo de SQL.

Puedes definir una constante con la descripción de una sola columna o una constante con un conjunto de descripciones de columnas para reutilizar las descripciones de todas las columnas de una tabla. Para obtener más información sobre cómo crear y usar inclusiones en Dataform, consulta Reutiliza código en un solo repositorio con inclusiones.

En la siguiente muestra de código, se indican varias constantes con descripciones de columnas individuales definidas en el archivo de JavaScript includes/docs.js:


// filename is includes/docs.js

const user_id = `A unique identifier for a user`;
const age = `The age of a user`;
const creation_date = `The date this user signed up`;
const user_tenure = `The number of years since the user's creation date`;
const badge_count = `The all-time number of badges the user has received`;
const questions_and_answer_count = `The all-time number of questions and answers the user has created`;
const question_count = `The all-time number of questions the user has created`;
const answer_count = `The all-time number of answers the user has created`;
const last_badge_received_at = `The time the user received their most recent badge`;
const last_posted_at = `The time the user last posted a question or answer`;
const last_question_posted_at = `The time the user last posted an answer`;
const last_answer_posted_at = `The time the user last posted a question`;

module.exports = {
   user_id,
   age,
   creation_date,
   user_tenure,
   badge_count,
   questions_and_answer_count,
   question_count,
   answer_count,
   last_badge_received_at,
   last_posted_at,
   last_question_posted_at,
   last_answer_posted_at,
};

En la siguiente muestra de código, se indican las constantes user_id y age, definidas en includes/docs.js, que se usan en el archivo de definición de tabla SQLX definitions/my_table.sqlx para generar documentación de las columnas seleccionadas en la tabla:

config {
  type: "table",
  description: "Table description.",
  columns: {
    user_id: docs.user_id,
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    age: docs.age,
  }
}

SELECT ...

En la siguiente muestra de código, se muestra una constante con un conjunto de descripciones de columnas definidas en el archivo JavaScript includes/docs.js:


// filename is includes/docs.js

const columns = {
    user_id = `A unique identifier for a user`,
    age = `The age of a user`,
    creation_date = `The date this user signed up`,
    user_tenure = `The number of years since the user's creation date`,
    badge_count = `The all-time number of badges the user has received`,
    questions_and_answer_count = `The all-time number of questions and answers the user has created`,
    question_count = `The all-time number of questions the user has created`,
    answer_count = `The all-time number of answers the user has created`,
    last_badge_received_at = `The time the user received their most recent badge`,
    last_posted_at = `The time the user last posted a question or answer`,
    last_question_posted_at = `The time the user last posted an answer`,
    last_answer_posted_at = `The time the user last posted a question`,
}


module.exports = {
  columns
};

En el siguiente muestra de código, se muestra la constante columns, que se define en includes/table_docs.js y se usa en el archivo de definición de la tabla definitions/my_table.sqlx de SQLX para generar documentación para todas las columnas de la tabla:

config { type: "table",
description: "My table description",
columns: docs.columns
}

SELECT 1 AS one

Agrega etiquetas de BigQuery

En esta sección, se muestra cómo agregar etiquetas a las tablas en Dataform.

BigQuery admite la adición de etiquetas a los recursos. Para obtener más información sobre las etiquetas en BigQuery, consulta Introducción a las etiquetas.

Para agregar una etiqueta de BigQuery a una tabla en Dataform, agrega la etiqueta al bloque bigquery en el bloque config del archivo SQLX de definición de la tabla.

Para agregar una etiqueta de BigQuery a un archivo de definición de tabla, sigue estos pasos:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Selecciona un archivo de definición de tabla SQLX.

  4. En el bloque config, agrega una etiqueta con el siguiente formato:

    bigquery: {
    labels: {
      LABEL1: "VALUE_OF_LABEL1"
    }
  }

    Reemplaza lo siguiente:

    • LABEL1: El nombre de tu etiqueta
    • VALUE_OF_LABEL1: El valor de tu etiqueta

  5. Opcional: Para agregar una etiqueta con un nombre que contenga caracteres especiales, ingresa el nombre de la etiqueta entre comillas ("").

  6. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indican las etiquetas department:shipping y cost-center:logistics agregadas al bloque bigquery en un archivo SQLX de definición de tabla particionada:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    labels: {
      department: "shipping",
      "cost-center": "logistics"
    }
  }
}

SELECT CURRENT_TIMESTAMP() AS ts

Conserva los metadatos de los controles de acceso

Cuando Dataform ejecuta acciones de flujo de trabajo que vuelven a crear objetos, por ejemplo, tablas estándar, vistas o tablas incrementales durante una actualización completa, suele generar sentencias CREATE OR REPLACE en BigQuery para actualizar el esquema y los datos del objeto. De forma predeterminada en BigQuery, las operaciones de reemplazo de tablas descartan automáticamente los metadatos de control de acceso a nivel de columna existentes, como las políticas de datos y las etiquetas de administración de datos (versión preliminar) aplicadas por sistemas externos de administración de datos.

Puedes configurar Dataform para que conserve automáticamente las políticas de datos de BigQuery existentes, incluidas las políticas de datos sin etiquetas, y las etiquetas de administración de datos en todas las ejecuciones de flujos de trabajo habilitando la opción preserveGovernanceControls. Cuando esta opción está habilitada, Dataform realiza la siguiente secuencia durante la ejecución:

  1. Crea una copia de seguridad de los metadatos de columna existentes desde la vista INFORMATION_SCHEMA.COLUMNS.
  2. Crea una tabla de etapa de pruebas vacía intermedia.
  3. Vuelve a aplicar las políticas de seguridad a la tabla intermedia.
  4. Completa la tabla con datos.
  5. Realiza un intercambio atómico de la tabla para reemplazar la tabla original.

Habilita la conservación automatizada de la administración para una tabla

Para habilitar la conservación automatizada de las políticas de datos y las etiquetas de administración de datos en una tabla individual, una tabla incremental o una vista, establece el parámetro preserveGovernanceControls en true en el bloque config del archivo de definición de SQLX del objeto.

Para configurar la conservación de la administración para una tabla individual, haz lo siguiente:

  1. Ve a tu espacio de trabajo de desarrollo.
  2. En el panel Archivos, expande definitions/.
  3. Selecciona un archivo de definición de tabla SQLX.
  4. En el bloque config, ingresa preserveGovernanceControls: true.
  5. Opcional: Haz clic en Formato.

En la siguiente muestra de código, se indica una definición de tabla con la preservación de administración habilitada:

config {
  type: "table",
  preserveGovernanceControls: true
}

SELECT
  customer_id,
  email,
  phone_number
FROM
  ${ref("raw_customers")}

Habilita la preservación de la administración automatizada para todas las tablas

Puedes habilitar la preservación de la administración automatizada como comportamiento predeterminado en todo el proyecto para todas las tablas, tablas incrementales y vistas de tu repositorio configurando el parámetro preserveGovernanceControls como true en el archivo workflow_settings.yaml de tu repositorio.

Para establecer el valor predeterminado en todo el proyecto, agrega el parámetro preserveGovernanceControls a tu archivo workflow_settings.yaml, como se muestra en el siguiente ejemplo:

defaultProject: my-gcp-project-id
defaultDataset: my_dataform_dataset
defaultLocation: US
defaultAssertionDataset: my_dataform_assertions
dataformCoreVersion: 3.0.62
preserveGovernanceControls: true

Si habilitas la conservación de la administración de forma global en el archivo workflow_settings.yaml, puedes anular este comportamiento y, luego, inhabilitar la conservación para una tabla específica estableciendo el parámetro preserveGovernanceControls en false en el bloque config de esa tabla individual.

Vuelve a aplicar las políticas de acceso de fila con operaciones posteriores

Si bien habilitar la preservación de la administración protege de forma automática los metadatos a nivel de columna, como las políticas de datos de BigQuery y las etiquetas de administración de datos, no restablece de forma automática las políticas de acceso a las filas. Dado que el reemplazo de objetos descarta las reglas de seguridad a nivel de la fila existentes, puedes volver a aplicar las políticas de acceso a las filas después de crear la tabla definiendo instrucciones DDL de SQL dentro del bloque post_operations de tu archivo de definición de tabla SQLX.

En la siguiente muestra de código, se indica cómo volver a aplicar una política de acceso de filas en el bloque post_operations de una tabla:

config {
  type: "table",
}

SELECT
  bike_id,
  start_time,
  duration
FROM
  ${ref("raw_trips")}

post_operations {
  CREATE ROW ACCESS POLICY my_policy
  ON ${self()}
  GRANT TO ('serviceAccount:sa-name@example-gcp-project.iam.gserviceaccount.com')
  FILTER USING (bike_id LIKE 'US-%');
}

Limitaciones

Cuando uses la capacidad de conservación de la gobernanza, ten en cuenta las siguientes limitaciones:

  • Eliminaciones de columnas del esquema. Si se quita una columna de la definición de la tabla SQLX, Dataform ignorará las políticas de datos y las etiquetas asociadas a la columna borrada durante la fase de restauración.
  • Cambios de nombre de columnas de esquema. Si cambias el nombre de una columna existente en la definición de la tabla de SQLX, Dataform no puede asignar las asociaciones de etiquetas o políticas antiguas al nombre de la columna nueva. Como resultado, las etiquetas y políticas existentes en esas columnas se pierden o se intercambian, y debes volver a aplicar las etiquetas y políticas de forma manual a las columnas con el nombre cambiado.
  • Cambios en la partición y el agrupamiento en clústeres. No se admite cambiar la partición o el agrupamiento en clústeres de una tabla existente que usa preserveGovernanceControls en su lugar. BigQuery rechaza el reemplazo. Para resolver este problema, descarta la tabla y vuelve a ejecutar el flujo de trabajo. Debes volver a aplicar de forma manual las políticas de datos a nivel de columna y las etiquetas de administración de datos a la tabla que se volvió a crear.
  • Eliminaciones por vencimiento de particiones. Quitar la propiedad partitionExpirationDays de la configuración no quita el vencimiento de la partición de una tabla existente. Para quitar el vencimiento de la partición de una tabla existente, debes establecer la opción de forma explícita en el valor que seleccionaste o modificar la tabla de forma manual.
  • Tipos de tablas. Después de una ejecución exitosa, la tabla se informa como una clonación de tabla (table_type = 'CLONE' en INFORMATION_SCHEMA.TABLES). Se comporta como una tabla estándar, y la tabla base que se muestra en sus metadatos de clonación ya no existe.
  • Metadatos a nivel de la tabla y políticas de acceso a las filas. Las etiquetas a nivel de la tabla y las políticas de acceso a nivel de las filas no se conservan entre ejecuciones. Puedes volver a aplicar las políticas de acceso a las filas después de crear la tabla. Para ello, define las declaraciones de DDL de SQL dentro del bloque post_operations del archivo de definición de tablas de SQLX.
  • Intercambios de tablas no transaccionales Las operaciones de DDL de BigQuery no son transaccionales. Si la ejecución de un flujo de trabajo falla a mitad de la secuencia de cambio de nombre atómico, es posible que se requiera intervención manual si la tabla de destino se deja en un estado intermedio, por ejemplo, si se cambia el nombre de la tabla de forma permanente a TABLE_NAME_old o se deja como df_tmp_intermediate.
  • Etiquetas de política. La conservación automatizada de la administración no admite etiquetas de política de BigQuery. En su lugar, agrega etiquetas de administración de datos (versión preliminar) y políticas de datos de BigQuery a tus columnas para usar la integración de SQL integrada.

Consideraciones sobre el costo de almacenamiento

Cuando preserveGovernanceControls está habilitado, Dataform vuelve a compilar la tabla a través de una tabla de etapa de pruebas temporal en cada ejecución (o en cada actualización completa para las tablas incrementales). Al igual que con cualquier recreación de tabla en BigQuery, la versión anterior de los datos de la tabla se conserva en el viaje en el tiempo durante el período configurado del conjunto de datos, que es de siete días de forma predeterminada, seguido de un período de seguridad ante fallas de siete días.

La facturación del almacenamiento lógico es el modelo de facturación predeterminado en BigQuery, mientras que la facturación del almacenamiento físico es un parámetro de configuración opcional que se configura por conjunto de datos. Si tu conjunto de datos usa la facturación de almacenamiento físico, estos datos retenidos de viaje en el tiempo y de seguridad ante fallas se facturan además de la tabla activa. Como resultado, las tablas grandes que se vuelven a compilar con frecuencia pueden aumentar de forma notable tus costos de almacenamiento.

Para reducir este costo, haz lo siguiente:

¿Qué sigue?