Gestionar tablas con particiones

En este documento se describe cómo gestionar tablas con particiones en BigQuery.

Obtener metadatos de particiones

Puede obtener información sobre las tablas con particiones de las siguientes formas:

Obtener metadatos de particiones con vistas INFORMATION_SCHEMA

Cuando consultas la vista INFORMATION_SCHEMA.PARTITIONS, los resultados de la consulta contienen una fila por cada partición. Por ejemplo, la siguiente consulta muestra todas las particiones de la tabla del conjunto de datos llamado mydataset:

SELECT table_name, partition_id, total_rows
FROM `mydataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE partition_id IS NOT NULL

Para obtener más información, consulta INFORMATION_SCHEMA.PARTITIONS.

Obtener metadatos de particiones mediante metatablas

En SQL antiguo, puede obtener metadatos sobre las particiones de una tabla consultando la metatabla __PARTITIONS_SUMMARY__. Las metatablas son tablas de solo lectura que contienen metadatos.

Consulta la metatabla __PARTITIONS_SUMMARY__ de la siguiente manera:

#legacySQL
SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

La metatabla __PARTITIONS_SUMMARY__ tiene las siguientes columnas:

Valor Descripción
project_id Nombre del proyecto.
dataset_id Nombre del conjunto de datos.
table_id Nombre de la tabla con particiones por tiempo.
partition_id Nombre (fecha) de la partición.
creation_time Hora en la que se creó la partición, en milisegundos desde el 1 de enero de 1970 UTC.
last_modified_time La hora en la que se modificó por última vez la partición, en milisegundos desde el 1 de enero de 1970 UTC.

Para ejecutar un trabajo de consulta que utilice la metatabla __PARTITIONS_SUMMARY__, debes tener los permisos bigquery.jobs.create y bigquery.tables.getData.

Para obtener más información sobre los roles de gestión de identidades y accesos en BigQuery, consulta el artículo sobre el control de acceso.

Definir el vencimiento de la partición

Cuando creas una tabla con particiones por hora de ingestión o por columnas de unidades de tiempo, puedes especificar una caducidad de la partición. Esta opción especifica durante cuánto tiempo BigQuery conserva los datos de cada partición. El ajuste se aplica a todas las particiones de la tabla, pero se calcula de forma independiente para cada partición en función de la hora de la partición.

La hora de vencimiento de una partición se calcula a partir del límite de la partición en UTC. Por ejemplo, con la partición diaria, el límite de la partición es la medianoche (00:00:00 UTC). Si el vencimiento de la partición de la tabla es de 6 horas, cada partición vence a las 06:00:00 UTC del día siguiente. Cuando vence una partición, BigQuery elimina los datos de esa partición.

También puede especificar una caducidad de partición predeterminada a nivel del conjunto de datos. Si define la caducidad de las particiones de una tabla, el valor anula la caducidad predeterminada de las particiones. Si no especifica ninguna caducidad de partición (en la tabla o en el conjunto de datos), las particiones nunca caducarán.

Si define una caducidad de la tabla, ese valor tendrá prioridad sobre la caducidad de la partición. Por ejemplo, si la caducidad de la tabla es de 5 días y la de la partición es de 7, la tabla y todas las particiones que contiene se eliminan al cabo de 5 días.

En cualquier momento después de crear una tabla, puede actualizar la caducidad de la partición de la tabla. El nuevo ajuste se aplica a todas las particiones de esa tabla, independientemente de cuándo se hayan creado. Las particiones que ya tengas caducarán inmediatamente si son anteriores a la nueva hora de vencimiento. Del mismo modo, si se copian o insertan datos en una tabla con particiones por columnas de unidad de tiempo, las particiones más antiguas que la caducidad de particiones configurada para la tabla caducarán inmediatamente.

Cuando una partición caduca, BigQuery la elimina. Los datos de la partición se conservan de acuerdo con las políticas de viaje en el tiempo y a prueba de fallos, y se pueden cobrar en función de tu modelo de facturación. Hasta entonces, el número de particiones se tendrá en cuenta para calcular las cuotas de tablas. Para eliminar una partición inmediatamente, puedes eliminarla manualmente.

Actualizar el vencimiento de la partición

Para actualizar el vencimiento de una partición de una tabla con particiones, sigue estos pasos:

Consola

No puedes actualizar el vencimiento de la partición en la Google Cloud consola.

SQL

Usa la instrucción ALTER TABLE SET OPTIONS. En el siguiente ejemplo se actualiza el vencimiento a 5 días. Para quitar la fecha de vencimiento de la partición de una tabla, asigna el valor NULL a partition_expiration_days.

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

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

    ALTER TABLE mydataset.mytable
  SET OPTIONS (
    -- Sets partition expiration to 5 days
    partition_expiration_days = 5);

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

bq

Ejecuta el comando bq update con la marca --time_partitioning_expiration. Si vas a actualizar una tabla particionada en un proyecto que no sea el predeterminado, añade el ID del proyecto al nombre del conjunto de datos con el siguiente formato: project_id:dataset.

bq update \
--time_partitioning_expiration integer_in_seconds \
--time_partitioning_type unit_time \
project_id:dataset.table

Donde:

  • integer es el tiempo de vida predeterminado (en segundos) de las particiones de la tabla. No hay ningún valor mínimo. El tiempo de vencimiento se evalúa como la fecha de la partición más el valor entero. Si especificas 0, se elimina la caducidad de la partición y esta nunca caduca. Las particiones sin fecha de vencimiento deben eliminarse manualmente.
  • unit_time puede ser DAY, HOUR, MONTH o YEAR, en función de la granularidad de la partición de la tabla. Este valor debe coincidir con la granularidad que haya definido al crear la tabla.
  • project_id es el ID del proyecto.
  • dataset es el nombre del conjunto de datos que contiene la tabla que quieres actualizar.
  • table es el nombre de la tabla que vas a actualizar.

Ejemplos:

Introduce el siguiente comando para actualizar el tiempo de vencimiento de las particiones de mydataset.mytable a 5 días (432.000 segundos). mydataset está en tu proyecto predeterminado.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Introduce el siguiente comando para actualizar el tiempo de vencimiento de las particiones de mydataset.mytable a 5 días (432.000 segundos). mydataset está en myotherproject, no en tu proyecto predeterminado.

bq update \
--time_partitioning_expiration 432000 \
myotherproject:mydataset.mytable

API

Llama al método tables.patch y usa la propiedad timePartitioning.expirationMs para actualizar la caducidad de la partición en milisegundos. Como el método tables.update sustituye todo el recurso de la tabla, se recomienda usar el método tables.patch.

Definir los requisitos del filtro de partición

Cuando creas una tabla particionada, puedes requerir que todas las consultas de la tabla incluyan un filtro de predicado (una cláusula WHERE) que filtre por la columna de partición. Este ajuste puede mejorar el rendimiento y reducir los costes, ya que BigQuery puede usar el filtro para eliminar las particiones que no coincidan con el predicado. Este requisito también se aplica a las consultas en vistas y vistas materializadas que hacen referencia a la tabla con particiones.

Para obtener información sobre cómo añadir la opción Requerir filtro de partición al crear una tabla con particiones, consulta el artículo Crear tablas con particiones.

Si una tabla particionada tiene el ajuste Requerir filtro de partición, todas las consultas de esa tabla deben incluir al menos un predicado que solo haga referencia a la columna de partición. Las consultas sin este predicado devuelven el siguiente error:

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Para obtener más información, consulta Consultar tablas particionadas.

Actualizar el requisito de filtro de partición

Si no habilitas la opción Requerir filtro de partición al crear la tabla particionada, puedes actualizar la tabla para añadir la opción.

Consola

No puedes usar la consola de Google Cloud para requerir filtros de partición después de crear una tabla particionada.

SQL

Usa laALTER TABLE SET OPTIONS declaración para actualizar el requisito del filtro de partición. En el siguiente ejemplo se actualiza el requisito a true:

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

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

    ALTER TABLE mydataset.mypartitionedtable
  SET OPTIONS (
    require_partition_filter = true);

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

bq

Para actualizar una tabla particionada para que requiera filtros de partición mediante la herramienta de línea de comandos bq, introduce el comando bq update y proporciona la marca --require_partition_filter.

Para actualizar una tabla particionada en un proyecto que no sea el predeterminado, añade el ID del proyecto al conjunto de datos con el siguiente formato: project_id:dataset.

Por ejemplo:

Para actualizar mypartitionedtable en mydataset en tu proyecto predeterminado, introduce lo siguiente:

bq update --require_partition_filter mydataset.mytable

Para actualizar mypartitionedtable en mydataset en myotherproject, introduce lo siguiente:

bq update --require_partition_filter myotherproject:mydataset.mytable

API

Llama al método tables.patch y asigna el valor true a la propiedad requirePartitionFilter para requerir filtros de partición. Como el método tables.update sustituye todo el recurso de tabla, es preferible usar el método tables.patch.

Java

Antes de probar este ejemplo, sigue las Javainstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Java de BigQuery.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;

// Sample to update require partition filter on a table.
public class UpdateTableRequirePartitionFilter {

  public static void runUpdateTableRequirePartitionFilter() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateTableRequirePartitionFilter(datasetName, tableName);
  }

  public static void updateTableRequirePartitionFilter(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableName);
      table.toBuilder().setRequirePartitionFilter(true).build().update();

      System.out.println("Table require partition filter updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Table require partition filter was not updated \n" + e.toString());
    }
  }
}

Copiar una tabla con particiones

El proceso para copiar una tabla con particiones es el mismo que el que se sigue para copiar una tabla estándar. Para obtener más información, consulta el artículo sobre cómo copiar una tabla.

Cuando copies una tabla particionada, ten en cuenta lo siguiente:

  • Copiar una tabla con particiones en una tabla de destino nueva
    Toda la información de las particiones se copia con la tabla. La tabla nueva y la antigua tendrán las mismas particiones.
  • Copiar una tabla sin particiones en una tabla con particiones
    Esta operación solo se admite en particiones creadas en el momento de la ingestión. BigQuery copia los datos de origen en la partición que representa la fecha actual. Esta operación no se admite en tablas con particiones de columnas de unidades de tiempo o de intervalos de números enteros.
  • Copiar una tabla con particiones en otra tabla con particiones
    Las especificaciones de partición de las tablas de origen y de destino deben coincidir.
  • Copiar una tabla con particiones en una tabla sin particiones
    La tabla de destino no se particiona.
  • Copiar varias tablas con particiones

    Si copias varias tablas de origen en una tabla con particiones en el mismo trabajo, las tablas de origen no pueden contener una mezcla de tablas con y sin particiones.

    Si todas las tablas de origen son tablas particionadas, las especificaciones de partición de todas las tablas de origen deben coincidir con la especificación de partición de la tabla de destino.

  • Copiar una tabla con particiones que tenga una especificación de clústeres

    Si copias los datos en una tabla nueva, toda la información de clustering se copia con la tabla. La tabla nueva y la antigua tendrán el mismo clustering.

    Si copia datos en una tabla ya creada, las especificaciones de clúster de las tablas de origen y de destino deben coincidir.

Cuando copias datos en una tabla, puedes especificar si quieres añadir datos a la tabla de destino o sobrescribirla.

Copiar particiones individuales

Puedes copiar los datos de una o varias particiones en otra tabla.

Consola

La consola de Google Cloud no permite copiar particiones.

bq

Para copiar una partición, usa el comando bq cp (copiar) de la herramienta de línea de comandos bq con un decorador de partición ($date), como $20160201.

Se pueden usar marcas opcionales para controlar la disposición de escritura de la partición de destino:

  • -a o --append_table añade los datos de la partición de origen a una tabla o partición del conjunto de datos de destino.
  • -f o --force sobrescribe una tabla o una partición que ya exista en el conjunto de datos de destino y no te pide confirmación.
  • -n o --no_clobber devuelve el siguiente mensaje de error si la tabla o la partición existen en el conjunto de datos de destino: Table '<var>project_id:dataset.table</var> or <var>table$date</var>' already exists, skipping. Si no se especifica -n, el comportamiento predeterminado es pedirte que elijas si quieres sustituir la tabla o la partición de destino.
  • --destination_kms_key es la clave de Cloud KMS gestionada por el cliente que se usa para encriptar la tabla o la partición de destino.

El comando cp no admite las marcas --time_partitioning_field ni --time_partitioning_type. No puedes usar un trabajo de copia para convertir una tabla con particiones por hora de ingestión en una tabla con particiones.

--destination_kms_key no se muestra aquí. Consulta Proteger datos con claves de Cloud KMS para obtener más información.

Si el conjunto de datos de origen o de destino se encuentra en un proyecto que no es el predeterminado, añade el ID del proyecto a los nombres de los conjuntos de datos con el siguiente formato: project_id:dataset.

(Opcional) Proporcione la marca --location y asigne el valor a su ubicación.

bq --location=location cp \
-a -f -n \
project_id:dataset.source_table$source_partition \
project_id:dataset.destination_table$destination_partition

Donde:

  • location es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes asignar el valor asia-northeast1 a la marca. Puedes definir un valor predeterminado para la ubicación mediante el archivo.bigqueryrc.
  • project_id es el ID del proyecto.
  • dataset es el nombre del conjunto de datos de origen o de destino.
  • source_table es la tabla que vas a copiar.
  • source_partition es el decorador de partición de la partición de origen.
  • destination_table es el nombre de la tabla del conjunto de datos de destino.
  • destination_partition es el decorador de partición de la partición de destino.

Ejemplos:

Copiar una partición en una tabla nueva

Introduce el siguiente comando para copiar la partición del 30 de enero del 2018 de mydataset.mytable en una tabla nueva: mydataset.mytable2. mydataset está en tu proyecto predeterminado.

bq cp -a 'mydataset.mytable$20180130' mydataset.mytable2

Copiar una partición en una tabla sin particiones

Introduce el siguiente comando para copiar la partición del 30 de enero del 2018 de mydataset.mytable en una tabla sin particiones: mydataset2.mytable2. La combinación de teclas -a se usa para añadir los datos de la partición a la tabla de destino sin particiones. Ambos conjuntos de datos están en tu proyecto predeterminado.

bq cp -a 'mydataset.mytable$20180130' mydataset2.mytable2

Introduce el siguiente comando para copiar la partición del 30 de enero del 2018 de mydataset.mytable en una tabla sin particiones: mydataset2.mytable2. La combinación de teclas -f se usa para sobrescribir la tabla de destino sin particiones sin pedir confirmación.

bq --location=US cp -f 'mydataset.mytable$20180130' mydataset2.mytable2

Copiar una partición en otra tabla con particiones

Introduce el siguiente comando para copiar la partición del 30 de enero del 2018 de mydataset.mytable a otra tabla particionada: mydataset2.mytable2. La combinación de teclas -a se usa para añadir los datos de la partición a la tabla de destino. Como no se ha especificado ningún decorador de partición en la tabla de destino, se conserva la clave de partición de origen y los datos se copian en la partición del 30 de enero del 2018 de la tabla de destino. También puedes especificar un decorador de partición en la tabla de destino para copiar datos en una partición específica. mydataset está en tu proyecto predeterminado. mydataset2 está en myotherproject, no en tu proyecto predeterminado.

bq --location=US cp \
-a \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Introduce el siguiente comando para copiar la partición del 30 de enero del 2018 de mydataset.mytable en la partición del 30 de enero del 2018 de otra tabla particionada, mydataset2.mytable2. Se usa la combinación de teclas -f para sobrescribir la partición del 30 de enero del 2018 en la tabla de destino sin que se te pida confirmación. Si no se usa ningún decorador de partición, se sobrescribirán todos los datos de la tabla de destino. mydataset está en tu proyecto predeterminado. mydataset2 está en myotherproject, no en tu proyecto predeterminado.

bq cp \
-f \
'mydataset.mytable$20180130' \
'myotherproject:mydataset2.mytable2$20180130'

Introduce el siguiente comando para copiar la partición del 30 de enero del 2018 de mydataset.mytable a otra tabla particionada: mydataset2.mytable2. mydataset está en tu proyecto predeterminado. mydataset2 está en myotherproject, no en tu proyecto predeterminado. Si hay datos en la tabla de destino, el comportamiento predeterminado es pedirte que los sobrescribas.

bq cp \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Para copiar varias particiones, especifícalas en una lista separada por comas:

bq cp \
'mydataset.mytable$20180130,mydataset.mytable$20180131' \
myotherproject:mydataset.mytable2

API

Llama al método jobs.insert y configura una tarea copy. (Opcional) Especifica tu región en la propiedad location de la sección jobReference del recurso de trabajo.

Especifique las siguientes propiedades en la configuración del trabajo:

  • Introduce el conjunto de datos, la tabla y la partición de origen en la propiedad sourceTables.
  • Introduce el conjunto de datos y la tabla de destino en la propiedad destinationTable.
  • Use la propiedad writeDisposition para especificar si quiere añadir o sobrescribir la tabla o la partición de destino.

Para copiar varias particiones, introduce las particiones de origen (incluidos los nombres del conjunto de datos y de la tabla) en la propiedad sourceTables.

Eliminar una partición

Puede eliminar una partición de una tabla particionada. Sin embargo, no puedes eliminar las particiones especiales __NULL__ o __UNPARTITIONED__.

Solo puedes eliminar una partición a la vez.

Puedes eliminar una partición especificando el decorador de la partición, a menos que sea una de las dos particiones especiales.

Para eliminar una partición de una tabla con particiones, sigue estos pasos:

Consola

La consola de Google Cloud no admite la eliminación de particiones.

SQL

Si una instrucción DELETE que cumple los requisitos cubre todas las filas de una partición, BigQuery elimina toda la partición. Esta eliminación se realiza sin analizar bytes ni consumir ranuras. En el siguiente ejemplo de una instrucción DELETE se cubre toda la partición de un filtro en la pseudocolumna _PARTITIONDATE:

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

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

    DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

bq

Usa el comando bq rm con la marca --table (o el acceso directo -t) y especifica el decorador de partición para eliminar una partición concreta.

bq rm --table project_id:dataset.table$partition

Donde:

  • project_id es el ID del proyecto. Si se omite, se usará el proyecto predeterminado.
  • dataset es el nombre del conjunto de datos que contiene la tabla.
  • table es el nombre de la tabla.
  • partition es el decorador de partición de la partición que vas a eliminar.

Los decoradores de partición tienen el siguiente formato, según el tipo de partición:

  • Partición por horas: yyyymmddhh. Ejemplo: $2016030100
  • Partición diaria: yyyymmdd. Ejemplo: $20160301
  • Partición mensual: yyyymm. Ejemplo: $201603
  • Partición anual: yyyy. Ejemplo: $2016
  • Partición de intervalo de números enteros: inicio del intervalo de partición. Ejemplo: $20

La herramienta de línea de comandos bq te pedirá que confirmes la acción. Para saltarte la confirmación, usa la marca --force (o el acceso directo -f).

Ejemplos:

Elimina la partición del 1 de marzo del 2016 de una tabla con particiones diarias llamada mydataset.mytable en tu proyecto predeterminado:

bq rm --table 'mydataset.mytable$20160301'

Elimina la partición de marzo del 2016 de una tabla con particiones mensuales:

bq rm --table 'mydataset.mytable$201603'

Elimina el intervalo de números enteros que empieza en 20 de una tabla con particiones de intervalo de números enteros llamada mydataset.mytable:

bq rm --table 'mydataset.mytable$20'

API

Llama al método tables.delete y especifica el decorador de tabla y partición mediante el parámetro tableId.

Seguridad de las tablas con particiones

El control de acceso de las tablas con particiones es el mismo que el de las tablas estándar. Para obtener más información, consulta Introducción a los controles de acceso a tablas.