Administra tablas particionadas

En este documento, se describe cómo administrar tablas particionadas en BigQuery.

Nota: La información que figura en Administra tablas también se aplica a las tablas particionadas.

Obtener metadatos de partición

Puedes obtener información sobre las tablas particionadas de las siguientes maneras:

• Usa la vista INFORMATION_SCHEMA.PARTITIONS (vista previa).
• Usa la metatabla __PARTITIONS_SUMMARY__ (solo para SQL heredado).

Obtén metadatos de partición mediante vistas INFORMATION_SCHEMA

Cuando consultas la vista INFORMATION_SCHEMA.PARTITIONS, los resultados de la consulta contienen una fila por cada partición. Por ejemplo, en la siguiente consulta se enumeran todas las particiones de tabla en el 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.

Obtén metadatos de partición con metatablas

En SQL heredado, puedes obtener metadatos sobre las particiones de tabla si consultas 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__]

Nota: GoogleSQL no es compatible con el separador del decorador de partición ($), por lo que no puedes consultar __PARTITIONS_SUMMARY__ en GoogleSQL.

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 particionada por tiempo.
partition_id	Nombre (fecha) de la partición.
creation_time	El momento en que se creó la partición, expresado como la cantidad de milisegundos transcurridos desde el 1 de enero de 1970 UTC.
last_modified_time	El momento en que se modificó la partición por última vez, en milisegundos, desde el 1 de enero de 1970 UTC.

Como mínimo, para ejecutar un trabajo de consulta que usa la metatabla __PARTITIONS_SUMMARY__, debes tener permisos bigquery.jobs.create y bigquery.tables.getData.

Para obtener más información sobre las funciones de IAM en BigQuery, consulta Control de acceso.

Establece el vencimiento de la partición

Cuando creas una tabla particionada por tiempo de transferencia o por columna de unidad de tiempo, puedes especificar un vencimiento de la partición. Esta configuración especifica por cuánto tiempo BigQuery conserva los datos en cada partición. La configuración se aplica a todas las particiones de la tabla, pero se calcula de manera independiente para cada partición según el tiempo de partición.

La fecha y hora de vencimiento de una partición se calcula a partir del límite de partición en UTC. Por ejemplo, con la partición diaria, el límite de partición es a 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 6:00:00 UTC del día siguiente. Cuando una partición se vence, BigQuery borra los datos de esa partición.

También puedes especificar un vencimiento de partición predeterminada a nivel de conjunto de datos. Si configuras el vencimiento de la partición en una tabla, el valor anula el vencimiento predeterminado de la partición. Si no especificas ningún vencimiento de partición (en la tabla o el conjunto de datos), las particiones nunca vencerán.

Nota: Las tablas particionadas por un rango de números enteros no admiten tiempos de vencimiento de la partición.

Si configuras un vencimiento de tabla, ese valor tiene prioridad sobre el vencimiento de la partición. Por ejemplo, si el vencimiento de la tabla se establece en 5 días y el vencimiento de la partición se configura en 7 días, la tabla y todas sus particiones se borrarán después de 5 días.

En cualquier momento posterior a la creación de la tabla, puedes actualizar el vencimiento de la partición de la tabla. La nueva configuración se aplica a todas las particiones de esa tabla, sin importar cuándo se crearon. Las particiones existentes vencen de inmediato si son anteriores a la hora de vencimiento nueva. Del mismo modo, si los datos se copian o insertan en una tabla particionada por columna de unidad de tiempo, cualquier partición anterior al vencimiento de la partición configurada para la tabla vence de inmediato.

Cuando una partición caduca, los datos en esa partición ya no están disponibles para consultas y no se te cobra por el almacenamiento de esa partición. BigQuery finalmente borra la partición vencida. Hasta ese momento, el recuento de particiones se usará para las cuotas de tablas. Para borrar una partición de inmediato, puedes borrarla de forma manual.

Actualiza el vencimiento de la partición

Para actualizar el vencimiento de la partición de una tabla particionada, sigue estos pasos:

Consola

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

SQL

Usa la declaración ALTER TABLE SET OPTIONS. El siguiente ejemplo actualiza el vencimiento a 5 días. Para quitar el vencimiento de la partición de una tabla, establece partition_expiration_days en NULL.

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

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente sentencia:

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

3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

bq

Ejecuta el comando bq update con la marca --time_partitioning_expiration. Si actualizas una tabla particionada en un proyecto que no es tu proyecto predeterminado, debes agregar el ID del proyecto al nombre del conjunto de datos en el formato siguiente: project_id:dataset.

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

Aquí:

• integer es el ciclo de vida predeterminado (en segundos) de las particiones de la tabla. No hay valor mínimo. La hora de vencimiento se evalúa según la fecha en la que se encuentre la partición más el valor de número entero. Si especificas 0, el vencimiento de la partición se quitará y la partición nunca vencerá. Las particiones sin vencimiento deben borrarse de forma manual.
• unit_time es DAY, HOUR, MONTH o YEAR, según el nivel de detalle de partición de la tabla. Este valor debe coincidir con el nivel de detalle que estableciste cuando creaste la tabla.
• project_id es el ID del proyecto.
• dataset es el nombre del conjunto de datos que contiene la tabla que deseas actualizar.
• table es el nombre de la tabla que deseas actualizar.

Ejemplos:

Ingresa el comando siguiente para actualizar la fecha de vencimiento de las particiones en mydataset.mytable a 5 días (432,000 segundos). mydataset está en tu proyecto predeterminado.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Ingresa el comando siguiente para actualizar la fecha de vencimiento de las particiones en mydataset.mytable a 5 días (432,000 segundos). mydataset se encuentra 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 el vencimiento de la partición en milésimas de segundo. Debido a que se reemplaza todo el recurso de tabla usando el método tables.update, es preferible usar el método tables.patch.

Establece requisitos del filtro de partición

Cuando creas una tabla particionada, puedes solicitar que todas las consultas de la tabla incluyan un filtro de predicado (una cláusula WHERE) que filtre la columna de partición. Esta configuración puede mejorar el rendimiento y reducir los costos, porque BigQuery puede usar el filtro para reducir las particiones que no coinciden con el predicado.

Para obtener información sobre cómo agregar la opción Exigir filtro de partición cuando creas una tabla particionada, consulta Crea tablas particionadas.

Si una tabla particionada tiene la configuración Exigir filtro de partición, cada consulta de la tabla debe incluir al menos un predicado que solo haga referencia a la columna de partición. Las consultas sin un predicado de este tipo muestran 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, visita Consulta tablas particionadas.

Actualiza el requisito del filtro de partición

Si no habilitas la opción Exigir filtro de partición cuando creas la tabla particionada, puedes actualizar la tabla para agregar la opción.

Consola

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

SQL

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

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

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente sentencia:

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

3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

bq

Para actualizar una tabla particionada a fin de solicitar filtros de partición mediante la herramienta de línea de comandos de bq update, ingresa el comando --require_partition_filter y proporciona la marca.

Para actualizar una tabla particionada en un proyecto que no es el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: project_id:dataset.

Por ejemplo:

Para actualizar mypartitionedtable en mydataset en tu proyecto predeterminado, ingresa el siguiente código:

bq update --require_partition_filter mydataset.mytable

Para actualizar mypartitionedtable en mydataset en myotherproject, ingresa el siguiente código:

bq update --require_partition_filter myotherproject:mydataset.mytable

API

Llama al método tables.patch y establece la propiedad requirePartitionFilter en true para exigir filtros de partición. Debido a que se reemplaza todo el recurso de tabla usando el método tables.update, es preferible usar el método tables.patch.

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas 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());
    }
  }
}

Copia una tabla particionada

El proceso para copiar una tabla particionada y una tabla estándar es el mismo. Para obtener más información, consulta Cómo copiar una tabla.

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

• Copiar una tabla particionada en una tabla de destino nueva

  Toda la información de partición se copia con la tabla. La tabla nueva y la anterior tendrán particiones idénticas.

• Copiar una tabla no particionada en una tabla particionada existente

  Esta operación solo se admite para la partición por tiempo de transferencia. BigQuery copia los datos de origen en la partición que representa la fecha actual. Esta operación no es compatible con tablas particionadas por columnas de unidad de tiempo o por rango de números enteros.

• Copiar una tabla particionada en otra tabla particionada

  Las especificaciones de partición para las tablas de origen y destino deben coincidir.

• Copiar una tabla particionada a una tabla no particionada

  La tabla de destino permanece sin particiones.

• Copia varias tablas particionadas

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

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

Cuando copias a una tabla existente, puedes especificar si deseas agregar o reemplazar la tabla de destino.

Copia particiones individuales

Puedes copiar los datos de una o más particiones a otra tabla.

Nota: Los permisos necesarios son los mismos que para copiar una tabla.

Consola

La consola de Google Cloud no admite la copia de particiones.

bq

Usa el comando $date (copia) de la herramienta de línea de comandos bq cp para copiar una partición con un decorador de particiones como $20160201.

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

• -a o --append_table anexan los datos de las particiones de origen a una tabla o partición existente en el conjunto de datos de destino.
• -f o --force reemplaza una tabla o partición existente en el conjunto de datos de destino y no te solicitará confirmación.
• -n o --no_clobber muestran el siguiente mensaje de error si la tabla o partición ya existe 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 solicitarte confirmación para reemplazar la tabla de destino o la de partición.
• --destination_kms_key es la clave de Cloud KMS administrada por el cliente que se usa para encriptar la tabla o partición de destino.

El comando cp no admite las marcas --time_partitioning_field o --time_partitioning_type. No puedes usar un trabajo de copia para convertir una tabla particionada por tiempo de transferencia en una tabla particionada.

--destination_kms_key no se muestra aquí. Para obtener más información, consulta la página sobre cómo proteger datos con claves de Cloud KMS.

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

Opcional: Proporciona la marca --location y configura el valor en tu ubicación.

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

Aquí:

• 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 configurar el valor de la marca como asia-northeast1. Puedes configurar un valor predeterminado para la ubicación con 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 deseas copiar.
• source_partition es el decorador de particiones de la partición de origen.
• destination_table es el nombre de la tabla en el conjunto de datos de destino.
• destination_partition es el decorador de particiones de la partición de destino.

Ejemplos:

Nota: El separador de decorador de partición ($) es una variable especial en la Shell de Unix. Es posible que debas descartar el decorador cuando uses la herramienta de línea de comandos. En los ejemplos siguientes, se descarta el decorador de partición: mydataset.table\$20160519, 'mydataset.table$20160519'.

Copia una partición a una tabla nueva

Ingresa el siguiente comando para copiar la partición del 30 de enero de 2018 desde mydataset.mytable a la nueva tabla, mydataset.mytable2. mydataset está en tu proyecto predeterminado.

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

Copia una partición a una tabla no particionada

Ingresa el siguiente comando para copiar la partición del 30 de enero de 2018 desde mydataset.mytable a una tabla no particionada, mydataset2.mytable2. Se usa la combinación de teclas -a para agregar los datos de la partición a la tabla de destino no particionada. Ambos conjuntos de datos se encuentran en tu proyecto predeterminado.

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

Ingresa el siguiente comando para copiar la partición del 30 de enero de 2018 desde mydataset.mytable a una tabla no particionada, mydataset2.mytable2. Se usa la combinación de teclas -f para reemplazar la tabla de destino no particionada sin solicitarla.

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

Copia una partición a otra tabla particionada

Ingresa el siguiente comando para copiar la partición del 30 de enero de 2018 desde mydataset.mytable a otra tabla particionada, mydataset2.mytable2. Se usa la combinación de teclas -a para agregar los datos de la partición a la tabla de destino. Ya que no se especifica ningún decorador de particiones en la tabla de destino, la clave de partición de origen se conserva y los datos se copian a la partición del 30 de enero de 2018 en la tabla de destino. También puedes especificar un decorador de particiones en la tabla de destino para copiar datos a la 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

Ingresa el siguiente comando para copiar la partición del 30 de enero de 2018 desde mydataset.mytable a la partición del 20 de febrero de 2018 de otra tabla particionada, mydataset2.mytable2. Se usa la combinación de teclas -f para reemplazar la partición del 20 de febrero de 2018 en la tabla de destino sin solicitarla. Si no se utiliza un decorador de particiones, se reemplazan todos los datos en 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$20180220'

Ingresa el siguiente comando para copiar la partición del 30 de enero de 2018 desde 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 solicitarte que los reemplaces.

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

Nota: El comando bq cp con un decorador de particiones funciona en particiones basadas en columnas en las que las particiones de origen y de destino son idénticas. El comando bq cp también funciona en particiones basadas en el tiempo de transferencia, en las que la partición representa la misma unidad de tiempo o una unidad de tiempo más general que contiene la partición de origen. Por ejemplo, si $20180130 es el decorador de partición de origen, los decoradores de partición de destino válidos incluyen $20180130, $201801 y $2018. Para copiar una partición basada en columnas en un decorador de particiones completamente diferente o en una partición de unidad de tiempo con mayor nivel de detalle, usa una declaración INSERT SELECT.

Para copiar varias particiones, especifícalas como 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 un trabajo copy. Opcional: Especifica tu región en la propiedad location de la sección jobReference del recurso de trabajo.

Especifica las siguientes propiedades en la configuración de tu trabajo:

• Ingresa la tabla, la partición y el conjunto de datos de origen en la propiedad sourceTables.
• Ingresa el conjunto de datos y la tabla de destino en la propiedad destinationTable.
• Usa la propiedad writeDisposition para especificar si deseas agregar o reemplazar la tabla o partición de destino.

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

Borra una partición

Puedes borrar una partición individual de una tabla particionada. Sin embargo, no puedes borrar las particiones especiales __NULL__ o __UNPARTITIONED__.

Solo puedes borrar una partición a la vez.

Nota :Los permisos necesarios son los mismos que para borrar una tabla.

Puedes borrar una partición si especificas el decorador de la partición, a menos que sea una de las dos particiones especiales.

Ten en cuenta lo siguiente para borrar una partición en una tabla particionada:

Consola

La consola de Google Cloud no admite que se borren particiones.

SQL

Si una declaración DELETE calificada abarca todas las filas en una partición, BigQuery quita toda la partición. Esta eliminación se realiza sin analizar bytes ni consumir ranuras. En el siguiente ejemplo de una declaración DELETE, se abarca toda la partición de un filtro en la seudocolumna _PARTITIONDATE:

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

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente sentencia:

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

3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

bq

Usa el comando bq rm con la marca --table (o la combinación de teclas -t) y especifica el decorador de particiones para borrar una partición específica.

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

Aquí:

• project_id es el ID del proyecto. Si se omite, se usa tu 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 particiones de la partición que deseas borrar.

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

• Partición por hora: yyyymmddhh. Ejemplo: $2016030100.
• Partición diaria: yyyymmdd. Ejemplo: $20160301.
• Partición mensual: yyyymm. Ejemplo: $201603.
• Partición anual: yyyy. Ejemplo: $2016.
• Partición por rangos de números enteros: Inicio del rango de partición. Ejemplo: $20.

La herramienta de línea de comandos de bq te solicita que confirmes la acción. Para omitir la confirmación, usa la marca --force (o la combinación de teclas -f).

Nota: El separador de decorador de partición ($) es una variable especial en la Shell de Unix. Es posible que debas descartar el decorador cuando uses la herramienta de línea de comandos. En los ejemplos siguientes, se descarta el decorador de partición: mydataset.table\$20160519, 'mydataset.table$20160519'.

Ejemplos:

Borra la partición para el 1 de marzo de 2016 en una tabla particionada por día llamada mydataset.mytable en tu proyecto predeterminado:

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

Borra la partición de marzo de 2016 en una tabla particionada por mes:

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

Borra el rango de números enteros a partir de 20 en una tabla particionada por rangos de números enteros llamada mydataset.mytable:

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

API

Llama al método tables.delete y especifica la tabla y el decorador de particiones con el parámetro tableId.

Seguridad de la tabla particionada

El control de acceso para las tablas particionadas 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.