Crea y usa tablas particionadas por columnas de unidad de tiempo

En este documento, se describe cómo crear y usar tablas particionadas por una columna de DATE, TIMESTAMP o DATETIME. Si deseas obtener más información sobre las tablas particionadas por tiempo de transferencia, consulta la página Crea y usa tablas particionadas por tiempo de transferencia. Para obtener información sobre las tablas particionadas con rangos de números enteros, consulta Crea y usa tablas particionadas con rangos de números enteros.

Después de crear una tabla particionada, puedes hacer lo siguiente:

  • Controlar el acceso a los datos de tu tabla
  • Obtener información acerca de tus tablas particionadas
  • Ver una lista de las tablas particionadas en un conjunto de datos
  • Obtener metadatos de tablas particionadas por medio de metatablas

Para obtener más información sobre cómo administrar tablas particionadas, incluidos cómo copiarlas, borrarlas y actualizar sus propiedades, consulta esta página sobre cómo administrar tablas particionadas.

Limitaciones

Las tablas particionadas están sujetas a las siguientes limitaciones:

  • La columna de partición debe consistir en una columna escalar de DATE, TIMESTAMP o DATETIME. El modo de la columna puede ser REQUIRED o NULLABLE, pero no REPEATED (basada en arreglo).
  • La columna de partición debe ser un campo de nivel superior. No puedes usar un campo de hoja de un RECORD (STRUCT) como columna de partición.
  • No puedes usar SQL heredado para realizar consultas en las tablas particionadas ni para escribir resultados de consultas en ellas.

Crea tablas particionadas

Puedes crear una tabla particionada de las siguientes maneras:

  • Mediante Cloud Console
  • Mediante una declaración CREATE TABLE del lenguaje de definición de datos (DDL) con una cláusula PARTITION BY que contenga una partition expression
  • Con el comando bq mk de la herramienta de línea de comandos de bq
  • De manera programática, con una llamada al método tables.insert de la API
  • Desde resultados de consultas
  • Cuando cargas datos

Nombres de las tablas

Cuando creas una tabla en BigQuery, el nombre de la tabla debe ser único en cada conjunto de datos. El nombre de la tabla puede contener lo siguiente:

  • Hasta 1,024 caracteres
  • Caracteres Unicode en la categoría L (letra), M (marca), N (número), Pc (conector, incluido el guion bajo), Pd (raya) y Zs (espacio) Para obtener más información, consulta la Categoría general.

Por ejemplo, los siguientes son nombres de tabla válidos: table-01, ग्राहक, 00_お客様, étudiant.

Permisos necesarios

Como mínimo, para crear una tabla, debes tener los siguientes permisos:

  • Permisos bigquery.tables.create para crear la tabla
  • bigquery.tables.updateData para escribir datos en la tabla mediante un trabajo de carga, de consulta o de copia
  • bigquery.jobs.create para ejecutar un trabajo de consulta, de carga o de copia que escriba datos en la tabla

Es posible que se necesiten permisos adicionales, como bigquery.tables.getData, para acceder a los datos que escribes en la tabla.

En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.tables.create y bigquery.tables.updateData:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Las siguientes funciones predefinidas de IAM incluyen los permisos bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Además, si un usuario tiene permisos bigquery.datasets.create, se le otorga el acceso bigquery.dataOwner cuando crea un conjunto de datos. Con el acceso bigquery.dataOwner, el usuario puede crear y actualizar tablas en el conjunto de datos.

Para obtener más información sobre las funciones y los permisos de IAM en BigQuery, consulta Funciones y permisos predefinidos.

Partición diaria en comparación con particiones por hora, mensual o anual

Cuando usas una columna TIMESTAMP, DATETIME o DATE para particionar datos, puedes crear particiones con un nivel de detalle por día, hora, mes o año en función de tus datos y necesidades.

La partición diaria es el tipo de partición predeterminada y, cuando se usa con agrupamiento en clústeres, funciona bien para la mayoría de los casos de uso de BigQuery. En particular, la partición diaria es la mejor opción cuando tus datos se distribuyen en un amplio rango de fechas o si se agregan de forma continua en el tiempo. Si los datos abarcan un amplio rango de fechas, la partición diaria te permite mantenerte por debajo de los límites de partición de tu tabla.

Elige la partición por hora si las tablas tienen un gran volumen de datos que abarca un período corto (por lo general, menos de seis meses de valores de marca de tiempo). Con la partición por hora, puedes abordar los datos a nivel de detalle por hora, por ejemplo, cuando agregas, truncas o borras datos de una partición particular.

Elige la partición mensual o anual si tus tablas tienen una cantidad de datos relativamente pequeña por cada día, pero abarcan un período amplio. Esta opción de partición también se recomienda si el flujo de trabajo requiere actualizar o agregar con frecuencia filas que abarquen un período amplio (por ejemplo, más de 500 fechas). Usa particiones mensuales o anuales junto con el agrupamiento en clústeres en tu columna de partición de marca de tiempo, fecha o de fecha y hora para lograr el mejor rendimiento en estos casos. Consulta Partición de unidades de tiempo con agrupamiento en clústeres para obtener más detalles y ejemplos.

Crea una tabla particionada vacía con una definición de esquema

No puedes crear una tabla particionada vacía que no tenga una definición de esquema. Se requiere el esquema a fin de identificar la columna que se usó para crear las particiones.

Cuando creas una tabla particionada vacía con una definición de esquema, puedes hacer lo siguiente:

  • Proporcionar el esquema intercalado con la herramienta de línea de comandos de bq
  • Especificar un archivo de esquema JSON mediante la herramienta de línea de comandos de bq
  • Proporcionar el esquema en un recurso de tabla cuando llames al método tables.insert de la API

Si quieres obtener más información acerca de cómo especificar un esquema de tabla, consulta Especifica un esquema.

Después de crear la tabla particionada, puedes hacer lo siguiente:

  • Cargar datos en la tabla
  • Escribir resultados de consultas
  • Copiar datos en la tabla

Para crear una tabla particionada vacía con una definición de esquema, sigue estos pasos:

Console

  1. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

  2. En el panel de detalles, haz clic en Crear tabla (Create table).

  3. En la sección Fuente del panel Crear tabla, sigue estos pasos:

    • En Crear tabla a partir de, selecciona Tabla vacía.

  4. En la sección Destino, sigue estos pasos:

    • Para el Nombre del conjunto de datos, selecciona el conjunto de datos apropiado y en el campo Nombre de la tabla, escribe el nombre de la tabla que creaste.
    • Verifica que Tipo de tabla esté configurado como Tabla nativa.

  5. En la sección Esquema, ingresa la definición del esquema. Ingresa la información del esquema de forma manual mediante una de las siguientes acciones:

    • Habilita Editar como texto y, luego, ingresa el esquema de la tabla como un arreglo JSON.

    • Haz clic en Agregar campo y, luego, ingresa la información del esquema.

  6. Para la Configuración de partición y agrupamiento en clústeres, haz clic en Sin particionar, selecciona Particionar por campo y elige la columna DATE, TIMESTAMP o DATETIME. Esta opción no está disponible si el esquema no contiene una columna DATE, TIMESTAMP o DATETIME.

  7. Para el Filtro de partición, haz clic en la casilla Exigir filtro de partición a fin de solicitar a los usuarios que incluyan una cláusula WHERE que especifique las particiones que deben consultarse (opcional). Exigir un filtro de partición puede reducir los costos y mejorar el rendimiento. Para obtener más información, lee la sección Consulta tablas particionadas.

  8. Si deseas usar una clave de Cloud Key Management Service, haz clic en Opciones avanzadas y selecciona Clave administrada por el cliente en Encriptación. Si dejas establecida la configuración Clave administrada por Google, BigQuery encripta los datos en reposo.

  9. Haz clic en Crear tabla.

SQL

Las declaraciones del lenguaje de definición de datos (DDL) te permiten crear y modificar tablas y vistas con la sintaxis de consulta de SQL estándar.

Obtén más información en Usa instrucciones del lenguaje de definición de datos.

Sigue estos pasos para crear una tabla particionada con una declaración DDL en Cloud Console:

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. Haz clic en Redactar consulta nueva.

  3. Escribe tu declaración DDL CREATE TABLE en el área de texto del Editor de consultas.

    La siguiente consulta crea una tabla llamada newtable, con particiones diarias basadas en la columna transaction_date DATE y un vencimiento de partición de tres días.

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_date DATE)
 PARTITION BY
   transaction_date
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_date" )

    Para solicitar un filtro de partición cuando se consulta la tabla, incluye require_partition_filter=true en la cláusula OPTIONS.

    En la siguiente consulta, se crea la misma tabla, pero se usan particiones por hora. Ten en cuenta el uso de TIMESTAMP_TRUNC para definir la marca de tiempo en la marca de hora:

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, HOUR)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

    En la siguiente consulta, se crea la misma tabla, pero particionada por mes. Ten en cuenta el uso de TIMESTAMP_TRUNC para separar la marca de tiempo en la marca de mes:

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, MONTH)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

    Con la siguiente consulta, se crea la misma tabla, pero particionada por año. Ten en cuenta el uso de TIMESTAMP_TRUNC para definir la marca de tiempo en la marca de año:

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, YEAR)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

  4. Haz clic en Ejecutar. Cuando se completa la consulta, la tabla aparece en la lista de tablas de ese conjunto de datos.

bq

Usa el comando bq mk con la marca --table (o el acceso directo -t), la marca --schema y la marca --time_partitioning_field. Puedes proporcionar la definición del esquema de la tabla de forma intercalada o especificando un archivo de esquema JSON.

Los parámetros opcionales incluyen --expiration, --description, --time_partitioning_expiration, --destination_kms_key, --require_partition_filter, --time_partitioning_type y --label.

Si creas una tabla en otro proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el formato siguiente: project_id:dataset.

--destination_kms_key no se muestra aquí. Para obtener más información sobre el uso de esta marca, consulta Protege datos con claves de Cloud KMS.

Ingresa el siguiente comando para crear una tabla particionada vacía con una definición de esquema:

bq mk --table \
--expiration integer1 \
--schema schema \
--time_partitioning_field column \
--time_partitioning_type unit_time \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

Reemplaza lo siguiente:

  • integer1 es el ciclo de vida predeterminado (en segundos) de la tabla. El valor mínimo es 3,600 segundos (una hora). La hora de vencimiento se evalúa según la hora UTC actual más el valor de número entero. Si configuras el tiempo de vencimiento de la tabla cuando creas una tabla particionada por el tiempo, se ignora la configuración de vencimiento de la tabla predeterminada del conjunto de datos. Si configuras este valor, la tabla y todas las particiones se borran después del lapso especificado.
  • schema es una definición de esquema intercalado en el formato field:data_type, field:data_type o la ruta al archivo de esquema JSON en tu máquina local.
  • column es el nombre de la columna TIMESTAMP, DATETIME o DATE que se usa para crear las particiones.
  • unit_time es DAY, HOUR, MONTH o YEAR, según el nivel de detalle de partición de unidad de tiempo. El valor predeterminado es DAY si no se especifica time_partitioning_type.
  • integer2 es la duración predeterminada (en segundos) para las particiones de la tabla. No hay valor mínimo. El tiempo de vencimiento se evalúa según la fecha de la partición más el valor de número entero. El vencimiento de la partición es independiente del vencimiento de la tabla, pero no lo anula. Si configuras un vencimiento de la partición que expire después del vencimiento de la tabla, prevalece el vencimiento de la tabla.
  • description es una descripción de la tabla entre comillas.
  • key:value es el par clave-valor que representa una etiqueta. Puedes ingresar varias etiquetas mediante una lista separada por comas.
  • project_id es el ID del proyecto.
  • dataset es un conjunto de datos en tu proyecto.
  • table es el nombre de la tabla particionada que creas.

Para obtener más información sobre el comando bq mk, consulta bq mk.

Cuando especificas el esquema con la herramienta de línea de comandos de bq, no puedes incluir un tipo RECORD (STRUCT) ni una descripción de columna, y tampoco puedes especificar el modo de la columna. Todos los modos están establecidos como NULLABLE de forma predeterminada. Para incluir descripciones, modos y tipos RECORD, proporciona un archivo de esquema JSON en su lugar.

Ejemplos:

Ingresa el siguiente comando para crear una tabla particionada por hora llamada mypartitionedtable en mydataset en tu proyecto predeterminado. El vencimiento de la partición se establece en 86,400 segundos (1 día), el vencimiento de la tabla se establece en 2,592,000 (1 mes de 30 días), la descripción se establece en This is my partitioned table y la etiqueta en organization:development. El comando usa el acceso directo -t en lugar de --table.

Se usa la marca --require_partition_filter a fin de solicitar a los usuarios que incluyan una cláusula WHERE que especifique las particiones que deben consultarse. Exigir un filtro de partición puede reducir los costos y mejorar el rendimiento. Para obtener más información, lee Consulta tablas particionadas.

El esquema está especificado de forma intercalada como: ts:TIMESTAMP,column1:STRING,column2:INTEGER,column4:STRING. El campo TIMESTAMP especificado ts se usa para particionar los datos por hora. Para la partición por hora, usa una columna TIMESTAMP o DATETIME, en lugar de una columna DATE.

bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,column4:STRING' \
--time_partitioning_field ts \
--time_partitioning_type HOUR \
--time_partitioning_expiration 86400  \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable

Ingresa el siguiente comando para crear una tabla particionada por día llamada mypartitionedtable en myotherproject, no en tu proyecto predeterminado. El vencimiento de la partición se establece en 259,200 segundos (3 días), la descripción se establece como This is my partitioned table y la etiqueta como organization:development. El comando usa el acceso directo -t en lugar de --table. El comando no especifica un vencimiento de tabla. Si el conjunto de datos tiene un vencimiento de tabla predeterminado, se aplica. Si el conjunto de datos no tiene un vencimiento predeterminado, la tabla no vencerá nunca, pero las particiones vencerán en 3 días.

El esquema se especifica en un archivo JSON local: /tmp/myschema.json. La definición de esquema incluye un campo TIMESTAMP llamado ts que se usa para particionar los datos por día.

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_type DAY \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable

Después de crear la tabla, puedes usar la herramienta de línea de comandos de bq para actualizar el vencimiento de la tabla, el vencimiento de la partición, la descripción y las etiquetas de la tabla particionada.

API

Llama al método tables.insert con un recurso de tabla definido que especifica las propiedades timePartitioning y schema.

Go

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

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTablePartitioned demonstrates creating a table and specifying a time partitioning configuration.
func createTablePartitioned(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "post_abbr", Type: bigquery.IntegerFieldType},
		{Name: "date", Type: bigquery.DateFieldType},
	}
	metadata := &bigquery.TableMetadata{
		TimePartitioning: &bigquery.TimePartitioning{
			Field:      "date",
			Expiration: 90 * 24 * time.Hour,
		},
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metadata); err != nil {
		return err
	}
	return nil
}

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java que se encuentran 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. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;

public class CreatePartitionedTable {

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

  public static void createPartitionedTable(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();

      TableId tableId = TableId.of(datasetName, tableName);

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Schema schema =
          Schema.of(
              Field.of("stringField", StandardSQLTypeName.STRING),
              Field.of("booleanField", StandardSQLTypeName.BOOL),
              Field.of("dateField", StandardSQLTypeName.DATE));

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Partitioned table was not created. \n" + e.toString());
    }
  }
}

Node.js

Antes de probar este ejemplo, sigue las instrucciones de configuración para Node.js que se encuentran 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 Node.js de BigQuery. 

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createTablePartitioned() {
  // Creates a new partitioned table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  const schema = 'Name:string, Post_Abbr:string, Date:date';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
    timePartitioning: {
      type: 'DAY',
      expirationMS: '7776000000',
      field: 'date',
    },
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);
  console.log(`Table ${table.id} created with partitioning: `);
  console.log(table.metadata.timePartitioning);
}

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la Guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

Crea tablas particionadas a partir de los resultados de la consulta

Para crear una tabla particionada a partir de un resultado de consulta, escribe los resultados en una tabla de destino nueva. Puedes crear una tabla particionada si realizas consultas tanto en una tabla particionada como en una tabla sin particiones. No puedes transformar una tabla estándar existente en una tabla particionada a partir de los resultados de una consulta.

Cuando creas una tabla particionada a partir del resultado de una consulta, debes usar SQL estándar. Por el momento, SQL heredado no es compatible con las consultas en tablas particionadas o la escritura de resultados de consultas en tablas particionadas.

Los decoradores de particiones te permiten escribir los resultados de la consulta en una partición específica. Por ejemplo, para escribir los resultados en la partición del 1 de mayo de 2016, usa el siguiente decorador de partición:

table_name$20160501

Cuando escribas resultados de consultas en una partición específica por medio de un decorador de particiones, los datos que se escriben deben concordar con el esquema de partición de la tabla. Todas las filas escritas en la partición deberían tener valores que coincidan con la fecha de la partición.

Por ejemplo:

En la siguiente consulta, se recuperan datos del 1 de febrero de 2018 y se escriben en la partición $20180201 de la tabla mytable. La tabla tiene dos columnas: una TIMESTAMP llamada TS y una INT64 llamada a.

bq query \
--nouse_legacy_sql  \
--destination_table=mytable$20180201 \
'SELECT
   TIMESTAMP("2018-02-01") AS TS,
   2 AS a'

En la siguiente consulta, se recuperan datos del 31 de enero de 2018 y se intenta escribirlos en la partición $20180201 de mytable. Esta consulta falla porque los datos que intentas escribir no están dentro de la fecha de la partición.

bq query \
--nouse_legacy_sql  \
--destination_table=T$20180201 \
'SELECT
   TIMESTAMP("2018-01-31") as TS,
   2 as a'

Si deseas obtener más información acerca de cómo agregar o rectificar (reemplazar) datos en tablas particionadas, consulta Anexa y reemplaza datos de tablas particionadas. Si deseas obtener más información acerca de cómo consultar tablas particionadas, lee Consulta tablas particionadas.

Crea una tabla particionada a partir de un resultado de consulta

Sigue estos pasos para crear una tabla particionada a partir de un resultado de consulta:

Console

No puedes especificar opciones de partición para una tabla de destino cuando consultas datos con Cloud Console.

bq

Ingresa el comando bq query, especifica la marca --destination_table para crear una tabla permanente basada en los resultados de la consulta y especifica la marca --time_partitioning_field a fin de crear una tabla de destino particionada.

Especifica la marca use_legacy_sql=false para usar la sintaxis de SQL estándar. Para escribir los resultados de las consultas en una tabla que no se encuentra en tu proyecto predeterminado, agrega el ID del proyecto al nombre del conjunto de datos con el siguiente formato: project_id:dataset.

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

Ingresa el siguiente comando para crear una tabla de destino particionada nueva a partir de un resultado de consulta:

bq --location=location query \
--destination_table project_id:dataset.table \
--time_partitioning_field column \
--time_partitioning_type unit_time
--use_legacy_sql=false \
'query'

Reemplaza lo siguiente:

  • location es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, configura el valor de la marca como asia-northeast1. Puedes establecer 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 que contendrá la tabla particionada nueva.
  • table es el nombre de la tabla particionada que creas a partir de los resultados de la consulta.
  • column es el nombre de la columna TIMESTAMP o DATE que se usa para crear las particiones.
  • unit_time es DAY, HOUR, MONTH o YEAR, según el nivel de detalle de partición de unidad de tiempo. El valor predeterminado es DAY si no se especifica time_partitioning_type.
  • query es una consulta en la sintaxis de SQL estándar. No puedes usar SQL heredado para consultar tablas particionadas o escribir resultados de consultas en ellas.

Ejemplos:

Ingresa el siguiente comando para escribir los resultados de la consulta en una tabla de destino particionada llamada mypartitionedtable en mydataset. mydataset está en tu proyecto predeterminado. La consulta recupera datos de una tabla no particionada: el conjunto de datos públicos sobre accidentes de tráfico fatales de NHTSA. Se usa la columna timestamp_of_crash TIMESTAMP de la tabla para crear las particiones.

bq query \
--destination_table mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

Ingresa el siguiente comando para escribir los resultados de la consulta en una tabla de destino particionada llamada mypartitionedtable en mydataset. mydataset está en myotherproject, no en tu proyecto predeterminado. La consulta recupera datos de una tabla no particionada: el conjunto de datos públicos sobre accidentes de tráfico fatales de NHTSA. Se usa la columna TIMESTAMP timestamp_of_crash de la tabla para crear las particiones.

bq query \
--destination_table myotherproject:mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

API

A fin de guardar los resultados de la consulta en una tabla particionada permanente, llama al método jobs.insert, configura un trabajo query y, luego, incluye un valor para las propiedades destinationTable y timePartitioning.

Especifica tu ubicación en la propiedad location en la sección jobReference del recurso de trabajo.

Crea una tabla particionada cuando cargues datos

Puedes crear una tabla particionada si especificas opciones de partición cuando cargas datos en una tabla nueva. No es necesario crear una tabla particionada vacía antes de cargarle datos. Puedes crear una tabla particionada y cargar tus datos al mismo tiempo.

Cuando cargas datos en BigQuery, puedes proporcionar el esquema de tabla o, si el formato de datos es compatible, puedes usar la detección automática de esquemas.

Los decoradores de particiones te permiten cargar datos en una partición específica. Por ejemplo, para cargar todos los datos generados el 1 de mayo de 2016 en la partición 20160501, usa el decorador de partición siguiente:

table_name$20160501

Cuando cargues datos en una partición específica mediante un decorador de particiones, los datos que se carguen en la partición deben concordar con el esquema de partición de la tabla. Todas las filas escritas en la partición deben tener valores que coincidan con la fecha de la partición.

Para obtener más información, consulta Introducción a la carga de datos en BigQuery.

Partición por unidad de tiempo con agrupamiento en clústeres

La partición de unidad de tiempo se puede usar con el agrupamiento en clústeres. Una tabla particionada por unidad de tiempo con agrupamiento en clústeres primero divide sus datos en función de los límites de unidad de tiempo (día, hora, mes o año) de la columna de partición. Luego, dentro de cada límite de partición, los datos se agrupan en clústeres según las columnas de agrupamiento en clústeres.

A modo de ejemplo, este comando crea una tabla con una columna particionada diaria y un clúster.

    bq mk --time_partitioning_type=DAY \
    --time_partitioning_field=ts_column \
    --clustering_fields=column1,column2 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

Cuando recuperes el formato de la tabla, verás que la partición por marca de tiempo y el agrupamiento en clústeres diarios están activos:

    bq show --format=prettyjson mydataset.mytable2
    ...
      "clustering": {
        "fields": [
          "column1",
          "column2"
        ]
      },
    ...
      "timePartitioning": {
        "field": "ts_column",
        "type": "DAY"
      },
    ...

Si ejecutas el límite de la cantidad de particiones por tabla o si tienes muy pocos datos distribuidos en muchas particiones y si los mutas muy a menudo, considera usar una partición de unidad de tiempo mayor con agrupamiento de clústeres en la misma columna de partición. Esta es la forma recomendada de usar tablas particionadas para permanecer dentro de los límites de partición.

Por ejemplo, este comando crea una tabla particionada y agrupada por día en una misma columna:

    bq mk --time_partitioning_type=DAY \
    --time_partitioning_field=ts_column \
    --clustering_fields=ts_column,column1 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

Este es otro ejemplo para la tabla anterior, pero con un intervalo de partición de unidad de tiempo mayor:

    bq mk --time_partitioning_type=MONTH \
    --time_partitioning_field=ts_column \
    --clustering_fields=ts_column,column1 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

Controla el acceso a las tablas particionadas

Para configurar el acceso a las tablas y vistas, puedes otorgar una función de IAM a una entidad en los siguientes niveles, ordenados según el rango de recursos permitidos (de mayor a menor):

El acceso con cualquier recurso protegido por IAM es aditivo. Por ejemplo, si una entidad no tiene acceso en un nivel alto, como un proyecto, podrías otorgar acceso a la entidad a nivel del conjunto de datos. Luego, la entidad tendrá acceso a las tablas y vistas del conjunto de datos. Del mismo modo, si la entidad no tiene acceso en el nivel alto o en el de conjunto de datos, puedes otorgar acceso a la entidad a nivel de tabla o de vista.

Si otorgas funciones de IAM en un nivel superior en la jerarquía de recursos de Google Cloud, como el nivel de proyecto, de carpeta o de organización, la entidad tiene acceso a un amplio conjunto de recursos. Por ejemplo, cuando se otorga una función a una entidad en el nivel de proyecto, se le brindan permisos que se aplican a todos los conjuntos de datos del proyecto.

Si se otorga una función a nivel de conjunto de datos, se especifican las operaciones que una entidad puede realizar en las tablas y vistas de ese conjunto de datos específico, incluso si la entidad no tiene acceso a un nivel superior. Para obtener información sobre la configuración de los controles de acceso a nivel de conjunto de datos, consulta Controla el acceso a los conjuntos de datos.

Cuando se otorga una función a nivel de tabla o vista, se especifican las operaciones que una entidad puede realizar en las tablas y vistas específicas, incluso si la entidad no tiene acceso a un nivel superior. Para obtener información sobre la configuración de los controles de acceso a nivel de tabla, consulta Controla el acceso a las tablas y vistas.

También puedes crear funciones de IAM personalizadas. Si creas una función personalizada, los permisos que otorgas dependerán de las operaciones específicas que deseas que la entidad pueda realizar.

No puedes establecer un permiso de denegación en ningún recurso protegido por IAM.

Para obtener más información acerca de las funciones y permisos, consulta los siguientes documentos:

Usa tablas particionadas

Obtén información sobre tablas particionadas

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

  • Usa Cloud Console.
  • Usa el comando bq show en la herramienta de línea de comandos de bq.
  • Llama al método tables.get de la API.
  • Usa las bibliotecas cliente.

Permisos necesarios

Como mínimo, para obtener información sobre las tablas, debes tener permisos bigquery.tables.get. En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.tables.get:

  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

Además, si un usuario tiene permisos bigquery.datasets.create, se le otorga el acceso bigquery.dataOwner cuando crea un conjunto de datos. El acceso bigquery.dataOwner le otorga al usuario la capacidad de recuperar los metadatos de la tabla.

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

Obtén información acerca de una tabla particionada

Para ver información sobre una tabla particionada, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. En el panel Explorador, expande tu proyecto y conjunto de datos y, luego, selecciona la tabla.

  3. En el panel de detalles, haz clic en Detalles. En esta pestaña, se muestra la descripción y la información de la tabla.

    Detalles de la tabla

  4. Haz clic en la pestaña Esquema para ver la definición del esquema de la tabla. Ten en cuenta que las tablas particionadas no incluyen la seudocolumna _PARTITIONTIME.

bq

Emite el comando bq show para mostrar toda la información de la tabla. Usa la marca --schema para mostrar solo la información del esquema de la tabla. La marca --format se puede usar para controlar el resultado.

Si obtienes información sobre una tabla en un proyecto que no sea tu proyecto predeterminado, agrega el ID del proyecto al conjunto de datos en el formato siguiente: project_id:dataset.

bq show --schema --format=prettyjson project_id:dataset.table

Reemplaza lo siguiente:

  • project_id es el ID del proyecto.
  • dataset es el nombre del conjunto de datos.
  • table es el nombre de la tabla.

Ejemplos:

Ingresa el siguiente comando para mostrar toda la información sobre mytable en mydataset. mydataset está en tu proyecto predeterminado.

bq show --format=prettyjson mydataset.mytable

Ingresa el siguiente comando para mostrar toda la información sobre mytable en mydataset. mydataset está en myotherproject, no en tu proyecto predeterminado.

bq show --format=prettyjson myotherproject:mydataset.mytable

El resultado debe verse de la siguiente manera:

{
  "creationTime": "1563236533535",
  "description": "This is my partitioned table",
  "etag": "/ABcDEo7f8GHijKL2mnOpQr==",
  "expirationTime": "1565828533000",
  "id": "myproject:mydataset.mypartitionedtable",
  "kind": "bigquery#table",
  "labels": {
    "org": "dev"
  },
  "lastModifiedTime": "1563236533576",
  "location": "US",
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "requirePartitionFilter": true,
  "schema": {
    "fields": [
      {
        "name": "ts",
        "type": "TIMESTAMP"
      },
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      },
      {
        "name": "column3",
        "type": "STRING"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable",
  "tableReference": {
    "datasetId": "mydataset",
    "projectId": "myproject",
    "tableId": "mypartitionedtable"
  },
  "timePartitioning": {
    "expirationMs": "86400000",
    "field": "ts",
    "requirePartitionFilter": true,
    "type": "DAY"
  },
  "type": "TABLE"
}

Ingresa el comando siguiente para mostrar solo la información del esquema sobre mytable en mydataset. mydataset está en myotherproject, no en tu proyecto predeterminado.

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

El resultado debería ser similar a lo siguiente:

[
  {
    "name": "ts",
    "type": "TIMESTAMP"
  },
  {
    "name": "column1",
    "type": "STRING"
  },
  {
    "name": "column2",
    "type": "INTEGER"
  },
  {
    "name": "column3",
    "type": "STRING"
  }
]

API

Llama al método bigquery.tables.get y proporciona los parámetros relevantes.

Obtén una lista de las tablas particionadas en un conjunto de datos

Puedes enumerar las tablas en los conjuntos de datos (incluidas las tablas particionadas) de las siguientes maneras:

  • Usa Cloud Console.
  • Usa el comando bq ls en la herramienta de línea de comandos de bq.
  • Llama al método tables.list de la API.
  • Usa las bibliotecas cliente.

Permisos necesarios

Como mínimo, para enumerar las tablas en un conjunto de datos, debes tener permisos bigquery.tables.list. En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.tables.list:

  • bigquery.user
  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

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

Obtén una lista de tablas particionadas

Para ver una lista de las tablas de un conjunto de datos (incluidas las tablas particionadas), haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

  3. Desplázate por la lista para ver las tablas en el conjunto de datos. Las tablas, las tablas particionadas, los modelos y las vistas se identifican mediante íconos diferentes.

bq

Ejecuta el comando bq ls. Se puede usar la marca --format para controlar el resultado. Si enumeras tablas en un proyecto que no es el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: project_id:dataset.

bq ls --format=pretty project_id:dataset

Reemplaza lo siguiente:

  • project_id es el ID del proyecto.
  • dataset es el nombre del conjunto de datos.

Cuando ejecutas el comando, el campo Type muestra TABLE o VIEW. Para las tablas particionadas, en el campo Time Partitioning se muestra DAY, la columna que se usa a fin de crear las particiones y el vencimiento de la partición en milisegundos, si se especifica.

Por ejemplo:

+-------------------------+-------+----------------------+---------------------------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning                                 |
+-------------------------+-------+----------------------+---------------------------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

Ejemplos:

Ingresa el comando siguiente para crear una lista de las tablas en el conjunto de datos mydataset en tu proyecto predeterminado.

bq ls --format=pretty mydataset

Ingresa el siguiente comando para enumerar las tablas en el conjunto de datos mydataset en myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

Para enumerar tablas con la API, realiza una llamada al método tables.list.

Muestra una lista de particiones de tablas

A fin de mostrar la lista de particiones de una tabla particionada, puedes realizar una consulta en la metatabla __PARTITIONS_SUMMARY__ con SQL heredado.

Puedes ejecutar la consulta con Cloud Console, con el comando bq query o con una llamada al método jobs.insert y la configuración de un trabajo de query.

Permisos necesarios

Como mínimo, para ejecutar un trabajo de consulta que usa la metatabla __PARTITIONS_SUMMARY__, debes tener permisos bigquery.jobs.create. En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

También debes tener permisos bigquery.tables.getData. En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.tables.getData:

  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

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

Visualiza la lista de particiones en una tabla particionada

Puedes enumerar las particiones de una tabla particionada mediante SQL heredado. Para hacerlo, sigue estos pasos:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. Haz clic en el botón Redactar nueva consulta.

  3. Para consultar la metatabla __PARTITIONS_SUMMARY__, ingresa el siguiente texto en el Editor de consultas:

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

    Reemplaza lo siguiente:

    • dataset es el conjunto de datos que contiene la tabla.
    • table es el nombre de la tabla.

  4. Haz clic en Ejecutar.

bq

Ingresa la siguiente consulta con el comando bq query:

bq --location=location query \
--use_legacy_sql=true \
'SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]'

Reemplaza lo siguiente:

  • location es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, establece el valor de la marca en asia-northeast1. Puedes establecer un valor predeterminado para la ubicación con el archivo .bigqueryrc.
  • dataset es el conjunto de datos que contiene la tabla.
  • table es el nombre de la tabla.

API

Llama al método jobs.insert y configura un trabajo query que consulte la metatabla __PARTITIONS_SUMMARY__ de la tabla.

Obtén metadatos de tabla particionada con metatablas

Puedes obtener información sobre las tablas particionadas con tablas especiales llamadas metatablas. Las metatablas contienen metadatos como la lista de tablas y vistas en un conjunto de datos. Las metatablas son de solo lectura.

En este momento, no puedes usar el servicio INFORMATION_SCHEMA para obtener metadatos de tabla particionada.

Obtén metadatos de partición con metatablas

La metatabla __PARTITIONS_SUMMARY__ es una tabla especial cuyos contenidos representan metadatos de particiones en una tabla particionada por tiempo. La metatabla __PARTITIONS_SUMMARY__ es de solo lectura.

Para acceder a los metadatos sobre particiones en una tabla particionada por tiempo, usa la metatabla __PARTITIONS_SUMMARY__ en la declaración SELECT de una consulta. Puedes ejecutar la consulta de las siguientes maneras:

  • Usa Cloud Console.
  • Usar el comando bq query de la herramienta de línea de comandos de bq
  • Con una llamada al método jobs.insert de la API y la configuración de un trabajo query
  • Usar bibliotecas cliente

Por ahora, SQL estándar no es compatible con el separador de decorador de partición ($), por lo que no puedes consultar __PARTITIONS_SUMMARY__ con SQL estándar. Una consulta de SQL heredado que usa la metatabla __PARTITIONS_SUMMARY__ se ve de la siguiente manera:

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

En el ejemplo anterior, se ilustra lo siguiente:

  • dataset es el nombre del conjunto de datos.
  • table es el nombre de la tabla particionada por tiempo.
  • column es una de las siguientes opciones:
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, expresado como la cantidad de milisegundos transcurridos desde el 1 de enero de 1970 UTC.

Permisos sobre la metatabla de particiones

Como mínimo, para ejecutar un trabajo de consulta que usa la metatabla __PARTITIONS_SUMMARY__, debes tener permisos bigquery.jobs.create. En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

También debes tener permisos bigquery.tables.getData. En las siguientes funciones predefinidas de IAM, se incluyen los permisos bigquery.tables.getData:

  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

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

Ejemplos de metatablas de partición

La siguiente consulta recupera todos los metadatos de partición de una tabla particionada por tiempo llamada mydataset.mytable.

Console 

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

El resultado será similar al siguiente:

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

La siguiente consulta muestra cuándo se modificaron por última vez las particiones en mydataset.mytable.

Console

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

El resultado será similar al siguiente:

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

Para mostrar el campo last_modified_time en un formato legible, usa la función FORMAT_UTC_USEC. Por ejemplo:

Console

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

El resultado será similar al siguiente:

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

Próximos pasos