Administra datos de tablas particionadas

En este documento, se describe cómo administrar datos de tablas particionadas en BigQuery. Puedes trabajar con datos de tablas particionadas si sigues estas acciones:

  • Cargar datos en una tabla particionada
  • Examinar (o previsualizar) datos de tablas particionadas
  • Consultar datos de la tabla particionada
  • Agregar o reemplazar datos de la tabla particionada
  • Modificar datos de la tabla particionada mediante declaraciones de lenguaje de manipulación de datos
  • Copiar datos de la tabla particionada
  • Transmitir datos en tablas particionadas
  • Exportar datos de la tabla particionada

Si deseas obtener más información sobre cómo administrar esquemas de tablas, consulta Modifica esquemas de tablas.

Carga datos en una tabla particionada

Puedes crear una tabla particionada cuando cargas datos o puedes crear una tabla particionada vacía y cargar los datos más tarde. Cuando cargas datos en una tabla particionada, puedes usar la detección automática de esquemas para formatos de datos admitidos o especificar el esquema.

Para obtener más información sobre la carga de datos, consulta la documentación sobre el formato y la ubicación de tus datos de origen:

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. Si deseas 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.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import java.time.Duration;
import java.time.temporal.ChronoUnit;
import java.util.UUID;

public class LoadPartitionedTable {

  public static void runLoadPartitionedTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadPartitionedTable(datasetName, tableName, sourceUri);
  }

  public static void loadPartitionedTable(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      // Configure time partitioning. For full list of options, see:
      // https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TimePartitioning
      TimePartitioning partitioning =
          TimePartitioning.newBuilder(TimePartitioning.Type.DAY)
              .setField("date")
              .setExpirationMs(Duration.of(90, ChronoUnit.DAYS).toMillis())
              .build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into time partitioned table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println(
          "Data not loaded into time partitioned table during load job \n" + e.toString());
    }
  }
}

Explora los datos de una tabla

Puedes explorar los datos de una tabla particionada de las siguientes maneras:

  • Mediante Cloud Console o la IU web clásica de BigQuery
  • Mediante el comando bq head de la herramienta de línea de comandos
  • Mediante una llamada tabledata.list al método de la API
  • Mediante bibliotecas cliente

Permisos necesarios

Como mínimo, para examinar los datos de la tabla y la partición, debes contar con permisos bigquery.tables.getData. Las siguientes funciones predefinidas de Cloud IAM incluyen los permisos bigquery.tables.getData:

  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • 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 permite al usuario explorar los datos de las tablas y particiones en el conjunto de datos.

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

Explora los datos de tablas particionadas

Para explorar los datos de tablas particionadas, sigue estos pasos:

Console

  1. En el panel de navegación de Cloud Console, haz clic en un conjunto de datos para enumerar sus tablas y vistas.

  2. Haz clic en una tabla particionada de la lista.

  3. Haz clic en la pestaña Details (Detalles).

    Detalles de la tabla

  4. Observa el valor de Number of rows (Número de filas). Es posible que necesites este valor para controlar el punto de partida de tus resultados mediante la CLI o la API.

    Cantidad de filas

  5. Haz clic en la pestaña Vista previa. Verás un conjunto de datos de muestra. Ten en cuenta que no puedes obtener una vista previa de particiones individuales mediante Cloud Console.

IU clásica

  1. En la IU web de BigQuery, en el panel de navegación, haz clic en la flecha azul a la izquierda de tu conjunto de datos para expandirlo o haz doble clic en el nombre del conjunto de datos. De esta manera, se mostrarán las tablas y vistas en el conjunto de datos.

  2. Haz clic en una tabla particionada de la lista.

  3. Haz clic en Detalles y anota el valor de Número de filas. Es posible que necesites este valor para controlar el punto de partida de tus resultados mediante la CLI o la API.

  4. Haz clic en Preview (Vista previa). Verás un conjunto de datos de muestra. Ten en cuenta que no puedes obtener una vista previa de particiones individuales mediante la IU web de BigQuery.

    Vista previa de la tabla

CLI

Emite el comando bq head con la marca --max_rows para enumerar todos los campos en una cantidad determinada de filas de la tabla. Si no se especifica --max_rows, el valor predeterminado es 100. Especifica una partición que desees explorar mediante el decorador de particiones, por ejemplo, $20180224.

Debido a que el comando bq head no crea un trabajo de consulta, los comandos bq head no aparecen en tu historial de consultas y no se te cobra por ellos.

Para explorar un subconjunto de campos en la tabla (incluidos los campos anidados y repetidos), usa la marca --selected_fields y, luego, ingresa los campos como una lista separada por comas.

Para especificar la cantidad de filas que se deben omitir antes de mostrar los datos de la tabla, usa la marca --start_row=integer (o la combinación de teclas -s). El valor predeterminado es 0. Puedes recuperar la cantidad de filas de una tabla si usas el comando bq show para recuperar la información de la tabla.

Si la tabla que exploras está en un proyecto que no es el predeterminado, debes agregar el ID del proyecto al comando con el siguiente formato: project_id:dataset.table.

bq head \
--max_rows integer1 \
--start_row integer2 \
--selected_fields "fields" \
project_id:dataset.table$partition

Donde:

  • integer1 es el número de filas que se mostrará.
  • integer2 es el número de filas que se deben omitir antes de mostrar los datos.
  • fields es una lista de campos separados por comas.
  • project_id es el ID del proyecto.
  • dataset es el nombre del conjunto de datos que contiene la tabla.
  • table es el nombre de la tabla que se debe explorar.
  • $partition es el decorador de particiones.

Ejemplos:

Ingresa el siguiente comando para enumerar todos los campos en las primeras 10 filas en mydataset.mytable en la partición "2018-02-24". mydataset está en tu proyecto predeterminado.

bq head --max_rows=10 mydataset.mytable$20180224

Ingresa el siguiente comando para enumerar todos los campos en las primeras 100 filas en mydataset.mytable en la partición "2016-09-01". mydataset está en myotherproject, no en tu proyecto predeterminado.

bq head --format=prettyjson 'mydataset.mycolumntable2$20160901'

Ingresa el siguiente comando para mostrar solo field1 y field2 en mydataset.mytable en la partición "2016-09-01". El comando usa la marca - -start_row para saltar a la fila 100. mydataset.mytable está en tu proyecto predeterminado.

bq head \
--format=prettyjson \
--start_row 100 \
--selected_fields "state_number" \
'mydataset.mycolumntable2$20160901'

API

Explora los datos de una tabla mediante una llamada a tabledata.list. Especifica el nombre de la tabla y el decorador de partición en el parámetro tableId.

Configura estos parámetros opcionales para controlar la salida:

  • maxResults: Es la cantidad máxima de resultados que se mostrarán.
  • selectedFields: la lista de campos separados por comas que se mostrará. Si no se especifica, se muestran todos los resultados.
  • startIndex: índice basado en cero de la primera fila que debe leerse.

Los valores se muestran unidos en un objeto JSON que debes analizar, como se describe en la documentación de referencia de tabledata.list.

Consulta datos de tablas particionadas

Después de cargar tus datos en BigQuery, puedes consultar los datos en tus tablas. BigQuery admite dos tipos de consultas:

BigQuery ejecuta consultas interactivas de forma predeterminada, lo que significa que la consulta se ejecuta lo antes posible.

BigQuery también ofrece consultas por lotes. BigQuery pone en cola cada consulta por lotes en tu nombre y, luego, inicia la consulta en cuanto los recursos inactivos estén disponibles, lo que, por lo general, toma unos minutos.

Puedes ejecutar consultas interactivas y por lotes con las siguientes opciones:

Para obtener más información, visita Consulta tablas particionadas.

Anexa y reemplaza datos de tablas particionadas

Puedes reemplazar datos de la tabla particionada mediante una operación de carga o consulta. Puedes agregar datos adicionales a una tabla particionada existente si realizas una operación de adición de carga o si anexas resultados de consulta.

Permisos necesarios

Como mínimo, para reemplazar una partición existente o anexar datos a ella, debes tener los siguientes permisos:

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.jobs.create

Es posible que se necesiten permisos adicionales, como bigquery.tables.getData, para acceder a los datos que usas a fin de anexar o reemplazar.

Las siguientes funciones predefinidas de Cloud IAM incluyen los permisos bigquery.tables.updateDatabigquery.tables.create:

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

Las siguientes funciones predefinidas de Cloud 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. El acceso bigquery.dataOwner permite al usuario anexar y reemplazar tablas y particiones en el conjunto de datos.

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

Usa un trabajo de carga

Puedes anexar o reemplazar particiones mediante el comando bq load o una llamada al método jobs.insert y la configuración de un trabajo de load. Cloud Console y la IU web clásica de BigQuery no admiten reemplazar una partición o anexar datos a ella en un trabajo de carga.

Cuando anexes o reemplaces datos en una partición específica mediante un trabajo de carga, ten en cuenta esta información:

  • Cuando cargas datos desde Cloud Storage, el depósito debe estar en la misma ubicación que el conjunto de datos de BigQuery.
  • Los datos que cargas deben cumplir 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.
  • Debido a que las particiones en una tabla particionada comparten el esquema de la tabla, el reemplazo de los datos en una partición no reemplazará el esquema de la tabla. En su lugar, el esquema de los datos nuevos debe ser compatible con el esquema de la tabla. Para obtener más información sobre la actualización del esquema de la tabla en el trabajo de carga, consulta la sección sobre cómo administrar esquemas de tabla.
  • Cuando anexes datos a una tabla particionada por tiempo de transferencia, si no especificas un decorador de partición, se usa la partición actual.

Para anexar o reemplazar los datos de la tabla particionada con un trabajo de carga, especifica una tabla de destino y un decorador de partición y establece la marca de disposición de escritura en:

Opción de CLI Opción de API Descripción
--noreplace WRITE_APPEND Anexa los datos a una partición existente. Si no se especifica ninguna opción de disposición de escritura, la acción predeterminada es agregar los datos a la partición.
--replace WRITE_TRUNCATE Reemplaza (rectifica) una partición.

Un decorador de particiones representa una fecha específica y tiene este formato:

$YYYYMMDD

Por ejemplo, con el comando siguiente, se reemplazan los datos en la partición completa para la fecha 1 de enero de 2016 (20160101) en una tabla particionada llamada mydataset.table1. Los datos JSON se cargan desde un depósito de Cloud Storage.

bq load \
--replace \
--source_format=NEWLINE_DELIMITED_JSON \
'mydataset.table1$20160101' \
gs://mybucket/myfile.json

Usa un trabajo de consulta

Puedes anexar o reemplazar particiones mediante el comando bq query o una llamada al método jobs.insert y la configuración de un trabajo de query. Cloud Console y la IU web clásica de BigQuery no admiten reemplazar una partición o anexar datos a ella en un trabajo de consulta.

Cuando uses un trabajo de consulta anexar o reemplazar una partición, ten en cuenta esta información:

  • Las tablas que consultas deben estar en la misma ubicación que la tabla que anexas o reemplazas.
  • Cuando anexes o reemplaces una partición en una tabla particionada en tiempo de transferencia, puedes usar la sintaxis de SQL heredada o la sintaxis de SQL estándar.
  • Cuando anexes o reemplaces una partición de una tabla particionada, tu consulta debe usar la sintaxis de SQL estándar. Por el momento, el SQL heredado no es compatible para la consulta de tablas particionadas ni con el fin de escribir resultados de consultas en tablas particionadas.
  • Cuando escribas los resultados de la consulta en una partición, los datos que se escriben deben ajustarse al 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.
  • Cuando anexes datos a una tabla particionada por tiempo de transferencia, si no especificas un decorador de partición, se usa la partición actual.

Para anexar o reemplazar una partición mediante los resultados de la consulta, especifica una tabla de destino con un decorador de partición y establece la disposición de escritura en:

Opción de CLI Opción de API Descripción
--append_table WRITE_APPEND Agrega los resultados de la consulta a una partición existente.
--replace WRITE_TRUNCATE Reemplaza (rectifica) una partición mediante los resultados de la consulta.

Por ejemplo, con el comando siguiente, se rectifican los datos para la partición del 1 de marzo de 2016 (20160301) de table1 mediante los resultados de consulta.

bq query \
--use_legacy_sql=false \
--replace \
--destination_table 'mydataset.table1$20160301' \
'SELECT
  column1,
  column2
FROM
  mydataset.mytable'

Si la tabla de destino existe y no está particionada, se muestra el siguiente error: BigQuery error in query operation: Error processing job 'project_id job_id' Incompatible table partitioning specification. Expects partitioning specification interval (type:day), but input partitioning specification is none`.

Si deseas obtener más información sobre cómo usar los resultados de la consulta para anexar o reemplazar datos, consulta Escribe resultados de consultas.

Modifica datos de tablas particionadas mediante Declaraciones DML

Puedes modificar los datos de una tabla particionada mediante Declaraciones DML en el dialecto SQL estándar. Las Declaraciones DML te permiten realizar actualizaciones, inserciones y eliminaciones de filas de forma masiva. Para obtener ejemplos sobre el uso de DML con tablas particionadas, consulta Actualiza datos de tablas particionadas con DML.

El dialecto SQL heredado no admite Declaraciones DML. Para actualizar o borrar datos mediante SQL heredado, debes borrar la tabla particionada y, luego, volver a crearla con datos nuevos. De forma alternativa, puedes escribir una consulta que modifique los datos y escribir los resultados de la consulta en una tabla particionada nueva.

Copia datos de tablas particionadas

Puedes copiar una tabla particionada de las siguientes maneras:

  • Mediante Cloud Console o la IU web clásica de BigQuery
  • Usar el comando bq cp de la herramienta de línea de comandos
  • Mediante llamadas al método de API jobs.insert y la configuración de un trabajo de copia
  • Usar bibliotecas cliente

Para obtener más información sobre la copia de tablas, consulta Copia una tabla.

Puedes copiar una o más particiones mediante el comando bq cp de la herramienta de línea de comandos o una llamada al método de la API jobs.insert y la configuración de un trabajo de copy. En la actualidad, la copia de particiones no es compatible con Cloud Console o la IU web clásica de BigQuery.

Para obtener más información sobre la copia de particiones, consulta Copia particiones.

Transmite datos en tablas particionadas

Para transmitir datos a una partición específica, usa el decorador de particiones cuando especifiques el tableId de la tabla a la que transmites los datos. Por ejemplo, con el siguiente comando, se transmite una sola fila a una partición para la fecha del 1 de enero de 2017 ($20170101) en una tabla particionada llamada mydataset.mytable:

echo '{"a":1, "b":2}' | bq insert 'mydataset.mytable$20170101'

Este comando se usa para demostrar el uso del decorador de partición. El comando bq insert está creado solo para pruebas. Para transmitir datos a BigQuery, usa el método de API tabledata.insertAll. Para obtener más información sobre la transmisión de datos a particiones, consulta Transmite a tablas particionadas.

Durante una transmisión mediante un decorador de particiones, puedes transmitir a particiones dentro de los últimos 30 días pasados y 5 días en el futuro en relación con la fecha actual, según la hora UTC actual. Si quieres escribir en particiones para fechas fuera de estos límites permitidos, puedes usar trabajos de carga o consulta.

Si especificas una tabla particionada por tiempo como la tabla de destino cuando transmites datos, cada partición tendrá un búfer de transmisión. El búfer de transmisión se conserva cuando realizas un trabajo de carga, consulta o copia que reemplaza una partición mediante la configuración de la propiedad writeDisposition como WRITE_TRUNCATE. Si deseas quitar el búfer de transmisión, verifica que esté vacío mediante una llamada a tables.get en la partición.

Exporta datos de tablas

En la exportación de todos los datos de una tabla particionada se realiza el mismo proceso que en la exportación de datos de una tabla no particionada. Para obtener más información, consulta Exporta datos de tablas. Para exportar datos de una partición individual, agrega el decorador de particiones, $date, al nombre de la tabla. Por ejemplo: mytable$20160201.

También puedes exportar datos de las particiones __NULL__ y __UNPARTITIONED__ si agregas los nombres de las particiones al nombre de la tabla. Por ejemplo, mytable$__NULL__ o mytable$__UNPARTITIONED__.

Puedes exportar datos de tablas particionadas en formato CSV, JSON o Avro. Por ahora, debes exportar datos a un depósito de Cloud Storage. No se admite la exportación a tu máquina local; sin embargo, puedes descargar y guardar los resultados de la consulta con Cloud Console o con la IU web clásica de BigQuery.

Próximos pasos

Para obtener más información sobre cómo trabajar con tablas particionadas, consulta esta documentación: