Cómo crear y usar tablas particionadas

En este documento, se describe el proceso para crear y usar tablas particionadas en BigQuery. Si deseas obtener más información sobre las tablas particionadas por tiempo de transferencia, consulta Cómo crear y usar tablas particionadas por tiempo de transferencia.

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
• Mostrar una lista de tablas particionadas en un conjunto de datos
• Obtener metadatos de tablas particionadas por medio de metatablas

Si deseas obtener más información sobre cómo administrar tablas particionadas, incluso cómo copiarlas, borrarlas y actualizar sus propiedades, consulta 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 o TIMESTAMP. El modo de la columna puede ser REQUIRED o NULLABLE, pero no puede ser REPEATED (basada en arreglo). Además, 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 o para escribir resultados de consultas en tablas particionadas.

Cómo crear tablas particionadas

Puedes crear una tabla particionada en BigQuery de las siguientes maneras:

• Mediante una declaración de DDL de CREATE TABLE con una cláusula PARTITION BY que contenga una partition expression
• De forma manual, mediante el comando bq mk de la herramienta de línea de comandos
• De manera programática, llamando al método de la API tables.insert
• A partir de resultados de consulta
• Cuando cargas datos

Cuando creas una tabla particionada por tiempo basada en columnas en BigQuery, el nombre de la tabla debe ser único por cada conjunto de datos. El nombre de la tabla puede estar conformado de estas maneras:

• Puede contener hasta 1,024 caracteres
• Puede contener letras (mayúsculas o minúsculas), números y guiones bajos

Permisos necesarios

A fin de crear una tabla particionada, debes tener acceso de WRITER a nivel del conjunto de datos o tener asignada una función de IAM a nivel de proyecto que incluya permisos de bigquery.tables.create. Las siguientes funciones de IAM predefinidas a nivel de proyecto incluyen permisos de bigquery.tables.create:

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

Además, debido a que la función de bigquery.user tiene permisos de bigquery.datasets.create, los usuarios asignados a la función bigquery.user pueden crear tablas particionadas en cualquier conjunto de datos que cree el usuario. Cuando un usuario asignado a la función bigquery.user crea un conjunto de datos, se le otorga acceso de OWNER al conjunto de datos. El acceso de OWNER a un conjunto de datos brinda al usuario un control total sobre este y todas las tablas que contenga.

Si deseas obtener más información sobre las funciones de IAM y los permisos en BigQuery, consulta Control de acceso. A fin de obtener más información sobre las funciones a nivel del conjunto de datos, consulta Funciones básicas para conjuntos de datos.

Cómo crear 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 usada 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 mediante la herramienta de línea de comandos
• Especificar un archivo de esquema JSON mediante la herramienta de línea de comandos
• Proporcionar el esquema en un recurso de tabla cuando llamas al método de la API tables.insert

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

Una vez que creas la tabla particionada, puedes hacer lo siguiente:

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

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

IU web

1. Ve a la IU web de BigQuery.

   Ir a la IU web de BigQuery

2. Haz clic en Redactar consulta.

3. Escribe tu declaración de DDL de CREATE TABLE en el área de texto de Nueva consulta.

   La siguiente consulta crea una tabla llamada newtable particionada por la columna DATE transaction_date y tiene un vencimiento de partición de tres días.

    #standardSQL
    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"
    )

4. Haz clic en Mostrar opciones.

5. Para Ubicación de procesamiento, haz clic en Sin especificar y elige la ubicación de tus datos. Puedes dejar la ubicación de procesamiento sin especificar si tus datos están en la ubicación multirregional US o EU. Cuando tus datos están en US o EU, la ubicación de procesamiento se detecta automáticamente.

6. Haz clic en Ejecutar consulta. Cuando se completa la consulta, aparece la tabla en el panel de navegación.

Línea de comandos

Utiliza el comando mk con la marca --table (o el acceso directo -t), la marca --schema y la marca --time_partitioning_field. Puedes proveer la definición del esquema de la tabla de forma intercalada o a través de un archivo de esquema JSON.

Los parámetros opcionales incluyen --expiration, --description, --time_partitioning_expiration, --destination_kms_key y --label. Actualmente, DAY es el único valor admitido para --time_partitioning_type, por lo que esta marca no es necesaria.

Si creas una tabla en otro proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: [PROJECT_ID]:[DATASET]

--destination_kms_key no se muestra aquí. Si deseas obtener más información sobre cómo usar esta marca, consulta llaves de encriptación administradas por el cliente.

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_expiration [INTEGER2] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE]

Aquí:

• [INTEGER1] es el ciclo de vida predeterminado (en segundos) de la tabla. El valor mínimo es 3600 segundos (una hora). El tiempo de vencimiento se evalúa tomando en cuenta la hora actual más el valor de número entero. Si configuras el tiempo de vencimiento de la tabla cuando creas una tabla particionada por tiempo, se ignora la configuración predeterminada de vencimiento de la tabla para el 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 de acceso al archivo de esquema JSON en tu máquina local.
• [COLUMN] es el nombre de la columna TIMESTAMP o DATE que se usa para crear las particiones.
• [INTEGER2] es el ciclo de vida predeterminado (en segundos) de 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 partición posterior al vencimiento de la tabla, prevalece el vencimiento de la tabla.
• [DESCRIPTION] es una descripción de la tabla en citas.
• [KEY:VALUE] es el par clave-valor que representa una etiqueta. Puedes ingresar múltiples etiquetas mediante una lista separada por comas.
• [PROJECT_ID] es el ID de tu proyecto.
• [DATASET] es un conjunto de datos en tu proyecto.
• [TABLE] es el nombre de la tabla particionada que creas.

Cuando especificas el esquema en la línea de comandos, no puedes incluir un tipo de RECORD (STRUCT) o una descripción de la columna ni especificar el modo de la columna. Según la configuración predeterminada, todos los modos son NULLABLE. Para incluir descripciones, modos y tipos de RECORD, proporciona un archivo de esquema JSON en su lugar.

Ejemplos:

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

El esquema está especificado de forma intercalada como: ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING. Se utiliza ts, el campo especificado de TIMESTAMP, para crear las particiones.

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

Ingresa el siguiente comando para crear una tabla particionada llamada mypartitionedtable en myotherproject, no tu proyecto predeterminado. El vencimiento de la partición está configurado en 259,200 segundos (3 días), la descripción está configurada como This is my partitioned table y la etiqueta, como organization:development. El comando utiliza el acceso directo -t en lugar de --table. Este comando no especifica un vencimiento de tabla. Si el conjunto de datos tiene un vencimiento de tabla predeterminado, se aplica. Si no lo tiene, 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 del esquema incluye un campo de TIMESTAMP llamado ts que se usa para crear las particiones.

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

Una vez que se ha creado la tabla particionada, puedes 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 especifique la propiedad timePartitioning y la propiedad schema.

Go

Antes de probar esta muestra, sigue las instrucciones de configuración para Go que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la Documentación de referencia de la API de Go de BigQuery.

Ver en GitHub Comentarios

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

Antes de probar esta muestra, sigue las instrucciones de configuración de Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la Documentación de referencia de la API de Python de BigQuery.

Ver en GitHub Comentarios

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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))

Cómo crear una tabla particionada a partir de un resultado de 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. A fin de realizar los ajustes correspondientes a las zonas horarias, puedes usar un decorador de particiones para escribir datos en una partición según tu zona horaria preferida. Por ejemplo, si estás en la hora estándar del Pacífico (PST), escribe todos los datos que se generen el 1 de mayo de 2016 PST en la partición correspondiente a esa fecha mediante el decorador de particiones adecuado:

[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 en la partición 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:

La siguiente consulta escribe datos en la partición del 1 de febrero de 2018 de la tabla T. La tabla tiene dos columnas: una columna de TIMESTAMP llamada TS y una columna de INT64 llamada a. Debido a que la consulta escribe la marca de tiempo 2018-02-01 en la partición $20180201, el comando la completa correctamente. La consulta se ejecuta en la multirregional US.

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

La siguiente consulta también intenta escribir datos en la tabla T, pero escribe la marca de tiempo 2018-01-31 en la partición $20180201. Esta consulta falla porque el valor que intentas escribir no coincide con la fecha de la partición.

bq --location=US 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 anexar o rectificar (reemplazar) datos en tablas particionadas, consulta Cómo anexar y reemplazar datos en tablas particionadas por tiempo. Si deseas obtener más información acerca de cómo realizar consultas en tablas particionadas, puedes leer Cómo realizar consultas en tablas particionadas.

IU web

No puedes especificar opciones de partición para una tabla de destino cuando realizas consultas en datos con la IU web de BigQuery.

CLI

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. Actualmente, DAY es el único valor admitido para --time_partitioning_type, por lo que esta marca no es necesaria.

Especifica la marca use_legacy_sql=false para utilizar la sintaxis de SQL estándar. Para escribir los resultados de la consulta en una tabla que no esté en tu proyecto predeterminado, agrega el ID del proyecto al nombre del conjunto de datos con el siguiente formato: [PROJECT_ID]:[DATASET].

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] --use_legacy_sql=false '[QUERY]'

Aquí:

• [LOCATION] es el nombre de tu ubicación. La marca --location es opcional si tus datos están en la ubicación multirregional US o EU. Por ejemplo, si usas BigQuery en la región de Tokio, configura 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 de tu 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.
• [QUERY] es una consulta en la sintaxis de SQL estándar. Actualmente, no puedes usar SQL heredado para realizar consultas en las tablas particionadas o escribir resultados de consultas en tablas particionadas.

Ejemplos:

Nota: Estos ejemplos utilizan la marca --location=US, porque estás realizando consultas en un conjunto de datos públicos. Los conjuntos de datos públicos de BigQuery se almacenan en la ubicación multirregional EE.UU. Debido a que los conjuntos de datos públicos se almacenan en EE.UU., no puedes escribir resultados de consultas de datos públicos en una tabla ubicada en otra región ni puedes unir tablas de conjuntos de datos públicos con tablas ubicadas en otras regiones.

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

bq --location=US 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 resultados de consultas en una tabla de destino particionada llamada mypartitionedtable en mydataset. mydataset se encuentra en myotherproject, no en tu proyecto predeterminado. La consulta obtiene datos de una tabla sin particiones: el conjunto de datos públicos sobre accidentes de tráfico fatales de NHTSA. Se usa la columna de TIMESTAMP timestamp_of_crash de la tabla para crear las particiones.

bq --location=US 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 consultas en una tabla particionada permanente, llama al método jobs.insert, configura un trabajo query y, por último, incluye un valor para las propiedades configuration.query.destinationTable y timePartitioning.field.

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

Cómo crear una tabla particionada en el momento de cargar 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. A fin de realizar los ajustes correspondientes a las zonas horarias, puedes usar un decorador de particiones para cargar datos en una partición según tu zona horaria preferida. Por ejemplo, si estás en la hora estándar del Pacífico (PST), carga todos los datos que se generen el 1 de mayo de 2016 PST en la partición correspondiente a esa fecha mediante el decorador de particiones adecuado:

[TABLE_NAME]$20160501

Cuando cargues datos en una partición específica por medio de 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 deberían tener valores que coincidan con la fecha de la partición.

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

API

A fin de definir una configuración de partición en el momento de crear una tabla a través de un trabajo de carga, puedes propagar el mensaje configuration.load.timePartitioning, con la configuración de partición relacionada, incluso configuration.load.timePartitioning.expirationMs, que corresponde a las particiones que vencerán con el paso del tiempo. La propagación de configuration.load.timePartitioning.field define si la tabla se particiona según pseudocolumnas o según columnas de datos del usuario.

Go

Antes de probar esta muestra, sigue las instrucciones de configuración para Go que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la Documentación de referencia de la API de Go de BigQuery.

Ver en GitHub Comentarios

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field:      "date",
	Expiration: 90 * 24 * time.Hour,
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

Python

Antes de probar esta muestra, sigue las instrucciones de configuración de Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la Documentación de referencia de la API de Python de BigQuery.

Ver en GitHub Comentarios

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
table_id = 'us_states_by_date'

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
    bigquery.SchemaField('date', 'DATE')
]
job_config.skip_leading_rows = 1
job_config.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field='date',  # name of column to use for partitioning
    expiration_ms=7776000000)  # 90 days
uri = 'gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv'

load_job = client.load_table_from_uri(
    uri,
    dataset_ref.table(table_id),
    job_config=job_config)  # API request

assert load_job.job_type == 'load'

load_job.result()  # Waits for table load to complete.

table = client.get_table(dataset_ref.table(table_id))
print("Loaded {} rows to table {}".format(table.num_rows, table_id))

Cómo controlar el acceso a las tablas particionadas

No puedes asignar controles de acceso directamente a tablas o particiones. A fin de controlar el acceso a la tabla, puedes configurar controles de acceso al nivel del conjunto de datos o del proyecto.

Los controles de acceso a nivel del conjunto de datos especifican las operaciones que los usuarios, los grupos y las cuentas de servicio tienen permitido realizar en las tablas de ese conjunto de datos específico. Si solo asignas permisos a nivel del conjunto de datos, también debes asignar una función predefinida o básica a nivel del proyecto que proporcione el acceso al proyecto, por ejemplo, bigquery.user.

En lugar de otorgar acceso a conjuntos de datos individuales, puedes asignar funciones de IAM predefinidas a nivel del proyecto, que otorguen permisos para todos los datos de tablas en todos los conjuntos de datos de un proyecto.

También puedes crear funciones de IAM personalizadas. Si creas una función personalizada, los permisos que otorgas dependen de las operaciones de tabla que quieres que el usuario, el grupo o la cuenta de servicio pueda realizar.

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

• Descripción de las funciones en la documentación de IAM
• Controles de acceso de BigQuery
• Cómo controlar el acceso a los conjuntos de datos.

Cómo usar tablas particionadas

Obtener información sobre tablas particionadas

Puedes obtener información sobre las tablas por medio de la IU web de BigQuery, con el comando de CLI bq show o si llamas al método de la API tables.get.

Permisos necesarios

A fin de obtener información sobre las tablas, debes tener asignada la función de READER en el conjunto de datos, o una función de IAM a nivel del proyecto que incluya permisos bigquery.tables.get. Si tienes permisos bigquery.tables.get a nivel del proyecto, puedes obtener información sobre todas las tablas del proyecto. Todas las funciones de IAM predefinidas a nivel del proyecto incluyen permisos bigquery.tables.get, excepto bigquery.user y bigquery.jobUser.

Además, un usuario a quien se le asigna la función bigquery.user tiene permisos bigquery.datasets.create. Esto permite a un usuario con la función bigquery.user obtener información sobre las tablas en cualquier conjunto de datos que el usuario cree. Cuando un usuario asignado a la función bigquery.user crea un conjunto de datos, se le otorga acceso de OWNER al conjunto de datos. El acceso de OWNER a un conjunto de datos brinda al usuario un control total sobre este y todas las tablas que contenga.

Si deseas obtener más información sobre las funciones de IAM y los permisos en BigQuery, consulta Control de acceso. A fin de obtener más información sobre las funciones a nivel del conjunto de datos, consulta Funciones básicas para conjuntos de datos.

Cómo obtener información acerca de una tabla particionada

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

IU web

1. En el panel de navegación, haz clic en el ícono de flecha hacia abajo que se encuentra a la izquierda de tu conjunto de datos para expandirlo, o haz doble clic en el nombre del conjunto de datos. Esto muestra las tablas y vistas del conjunto de datos.

2. Haz clic en el nombre de la tabla.

3. Haz clic en Detalles. La página Table Details (Detalles de la tabla) muestra la descripción y la información de la tabla.

   

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

Línea de comandos

Ejecuta el comando bq show para mostrar toda la información de la tabla. Utiliza la marca --schema para mostrar solo la información sobre el esquema de la tabla. La marca --format puede usarse para controlar el formato de salida.

Si quieres obtener información acerca de una tabla en un proyecto que no sea tu proyecto predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Aquí:

• [PROJECT_ID] es el ID de tu 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 se encuentra 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 se encuentra en myotherproject, no en tu proyecto predeterminado.

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

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

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

API

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

Cómo mostrar una lista de las tablas particionadas en un conjunto de datos

Puedes mostrar la lista de tablas en conjuntos de datos (incluidas las tablas particionadas) por medio de la IU web de BigQuery, el comando de CLI bq ls o si llamas al método de la API tables.list.

Permisos necesarios

Para mostrar una lista de las tablas de un conjunto de datos, debes tener la función de READER asignada en el conjunto de datos, o una función de IAM a nivel del proyecto que incluya permisos de bigquery.tables.list. Si se te otorgan permisos de bigquery.tables.list a nivel del proyecto, puedes mostrar la lista de las tablas en cualquier conjunto de datos del proyecto. Todas las funciones de IAM predefinidas a nivel del proyecto incluyen permisos de bigquery.tables.list, excepto bigquery.jobUser.

Si deseas obtener más información sobre las funciones de IAM y los permisos en BigQuery, consulta Control de acceso. A fin de obtener más información sobre las funciones a nivel del conjunto de datos, consulta Funciones básicas para conjuntos de datos.

Cómo ver una lista de tablas particionadas

Para mostrar la lista de tablas de un conjunto de datos (incluidas las tablas particionadas):

IU web

1. En la IU web, en el panel de navegación, haz clic en el ícono de flecha hacia abajo que se encuentra a la izquierda de tu conjunto de datos para expandirlo, o haz doble clic en el nombre del conjunto de datos. Esto muestra las tablas y vistas del conjunto de datos.

2. Desplázate por la lista para ver las tablas del conjunto de datos. Las tablas y las vistas están identificadas con íconos diferentes.

   

CLI

Ejecuta el comando bq ls. La marca --format puede usarse para controlar el formato de salida. Si quieres obtener la lista de tablas en un proyecto distinto de tu proyecto predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: [PROJECT_ID]:[DATASET].

bq ls --format=pretty [PROJECT_ID]:[DATASET]

Aquí:

• [PROJECT_ID] es el ID de tu proyecto.
• [DATASET] es el nombre del conjunto de datos.

Cuando ejecutas el comando, el campo Type muestra TABLE o VIEW. En el caso de las tablas particionadas, el campo Time Partitioning muestra DAY, la columna usada para crear las particiones y el tiempo de vencimiento de la partición en milisegundos (si existe).

Por ejemplo:

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

Ejemplos:

Ingresa el siguiente comando para mostrar la lista de tablas del conjunto de datos mydataset de tu proyecto predeterminado.

bq ls --format=pretty mydataset

Ingresa el siguiente comando para mostrar la lista de tablas del conjunto de datos mydataset de myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

Para mostrar la lista de tablas con la API, llama al método tables.list.

Cómo mostrar la lista de particiones de tablas particionadas

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 en la IU web de BigQuery, mediante el comando bq query, o si llamas al método jobs.insert y configuras un trabajo de query.

Permisos necesarios

A fin de ejecutar un trabajo de consulta que use la metatabla __PARTITIONS_SUMMARY__, debes tener permisos de bigquery.jobs.create. Las siguientes funciones de IAM predefinidas a nivel del proyecto incluyen los permisos bigquery.jobs.create:

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

También debes tener la función de READER asignada a nivel del conjunto de datos o una función de IAM a nivel del proyecto que incluya permisos de bigquery.tables.getData. Todas las funciones de IAM predefinidas a nivel del proyecto incluyen permisos bigquery.tables.getData, excepto bigquery.user, bigquery.jobUser y bigquery.metadataViewer.

Si deseas obtener más información sobre las funciones de IAM y los permisos en BigQuery, consulta Control de acceso. A fin de obtener más información sobre las funciones a nivel del conjunto de datos, consulta Funciones básicas para conjuntos de datos.

Cómo ver la lista de particiones en una tabla particionada

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

IU web

1. Ve a la IU web de BigQuery.

   Ir a la IU web de BigQuery

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

3. Ingresa el siguiente texto en el cuadro de Nueva consulta para consultar la metatabla __PARTITIONS_SUMMARY__:

   #legacySQL
   SELECT
     partition_id
   FROM
     [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]
   

   Aquí:

   • [DATASET] es el conjunto de datos que contiene la tabla.
   • [TABLE] es el nombre de la tabla.

4. Haz clic en Mostrar opciones.

5. Para Ubicación de procesamiento, haz clic en Sin especificar y elige la ubicación de tus datos. Puedes dejar la ubicación de procesamiento sin especificar si tus datos están en la ubicación multirregional US o EU. Cuando tus datos están en US o EU, la ubicación de procesamiento se detecta automáticamente.

6. Haz clic en Ejecutar consulta.

CLI

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__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional if your data is in the `US` or the `EU` multi-region
    location. For example, if you are using BigQuery in
    the Tokyo region, set the flag's value to `asia-northeast1`. You can
    set a default value for the location using the [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

Llama al método jobs.insert y configura un trabajo de query que realice una consulta en la metatabla __PARTITIONS_SUMMARY__ de la tabla.

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

Cómo obtener los metadatos de la tabla mediante metatablas

El proceso para obtener metadatos sobre una tabla particionada es el mismo que para obtener metadatos sobre una tabla estándar. Si deseas obtener más información, consulta Cómo obtener metadatos de una tabla mediante metatablas.

Cuando consultas la metatabla __TABLES_SUMMARY__, se enumeran todas las tablas. No existe la opción type para distinguir entre tablas particionadas y sin particionar.

Cómo obtener los metadatos de la partición mediante metatablas

La metatabla __PARTITIONS_SUMMARY__ es una tabla especial cuyo contenido representa metadatos sobre particiones de una tabla particionada por tiempo. La metatabla __PARTITIONS_SUMMARY__ es de solo lectura.

Para acceder a los metadatos sobre las particiones de una tabla particionada por tiempo, utiliza la metatabla __PARTITIONS_SUMMARY__ en la declaración SELECT de una consulta. Puedes ejecutar la consulta con la consola, la IU web de BigQuery clásica, el comando bq query de la herramienta de línea de comandos, o si llamas al método de la API jobs.insert y configuras un trabajo de consulta.

Actualmente, SQL estándar no admite el separador del decorador de partición ($), por lo que no puedes realizar consultas en __PARTITIONS_SUMMARY__ en SQL estándar. Una consulta de SQL heredado que use la metatabla __PARTITIONS_SUMMARY__ luce como se muestra a continuación:

    SELECT [COLUMN] FROM [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]

Aquí:

• [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, en milisegundos, 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.

Permisos de metatablas de particiones

A fin de ejecutar un trabajo de consulta que use la metatabla __PARTITIONS_SUMMARY__, debes tener permisos de bigquery.jobs.create. Las siguientes funciones de IAM predefinidas a nivel del proyecto incluyen los permisos bigquery.jobs.create:

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

También debes tener la función de READER asignada a nivel del conjunto de datos o una función de IAM a nivel del proyecto que incluya permisos de bigquery.tables.getData. Todas las funciones de IAM predefinidas a nivel del proyecto incluyen permisos bigquery.tables.getData, excepto bigquery.user, bigquery.jobUser y bigquery.metadataViewer.

Ejemplos de metatablas de particiones

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

IU clásica

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

Línea de comandos

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

El resultado luce de la siguiente manera:

+----------------+------------+----------------+--------------+---------------+--------------------+
|   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 consulta siguiente enumera los momentos en que se modificaron las particiones en mydataset.mytable por última vez.

IU clásica

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

Línea de comandos

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

El resultado luce de la siguiente manera:

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

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

IU clásica

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

Línea de comandos

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 luce de la siguiente manera:

+--------------+----------------------------+
| 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

• Para obtener una descripción general de la compatibilidad con tablas particionadas en BigQuery, consulta Introducción a las tablas particionadas.
• A fin de aprender a crear y usar tablas particionadas por tiempo de transferencia, consulta Cómo crear y usar tablas particionadas por tiempo de transferencia.
• Si deseas aprender a administrar y actualizar tablas particionadas, consulta Cómo administrar tablas particionadas.
• Para obtener información acerca de cómo realizar consultas en tablas particionadas, puedes leer Cómo realizar consultas en tablas particionadas.