Escribir resultados de consultas
En este documento se describe cómo escribir los resultados de una consulta en tablas temporales o permanentes.
Tablas temporales y permanentes
BigQuery guarda todos los resultados de las consultas en una tabla, que puede ser permanente o temporal.
BigQuery usa tablas temporales para almacenar en caché los resultados de las consultas que no se escriben en una tabla permanente. Las tablas se crean en un conjunto de datos especial y se les asigna un nombre aleatorio. También puedes crear tablas temporales para tu propio uso en consultas de varias instrucciones y sesiones. No se te cobrará por las tablas de resultados de consultas almacenadas en caché temporales. Se te cobrará por las tablas temporales que no sean resultados de consultas almacenados en caché.
Una vez que finaliza una consulta, la tabla temporal se mantiene durante un máximo de 24 horas. Para ver la estructura y los datos de la tabla, ve a la consola de BigQuery, haz clic en Historial personal y elige la consulta que ha creado la tabla temporal. A continuación, en la fila Tabla de destino, haga clic en Tabla temporal.
El acceso a los datos de la tabla temporal está restringido al usuario o a la cuenta de servicio que ha creado el trabajo de consulta.
No puedes compartir tablas temporales y no se muestran con ninguno de los métodos estándar de lista u otros métodos de manipulación de tablas. Si necesitas compartir los resultados de tu consulta, escríbelos en una tabla permanente, descárgalos o compártelos a través de Hojas de cálculo de Google o Google Drive.
Las tablas temporales se crean en la misma región que la tabla o las tablas que se están consultando.
Una tabla permanente puede ser nueva o ya existir en un conjunto de datos al que tengas acceso. Si escribes los resultados de una consulta en una tabla nueva, se te cobrará por almacenar los datos. Cuando escribes los resultados de una consulta en una tabla permanente, las tablas que consultas deben estar en la misma ubicación que el conjunto de datos que contiene la tabla de destino.
No puedes guardar los resultados de una consulta en una tabla temporal si la política de organización restringida por dominio está habilitada. Como solución alternativa, inhabilite temporalmente la política de organización con restricción de dominio, ejecute la consulta y, a continuación, vuelva a habilitar la política. También puedes guardar los resultados de la consulta en una tabla de destino.
Permisos obligatorios
Para poder escribir los resultados de una consulta en una tabla, como mínimo debes tener los siguientes permisos:
- Permisos de
bigquery.tables.create
para crear una tabla bigquery.tables.updateData
para escribir datos en una tabla nueva, sobrescribir una tabla o añadir datos a una tablabigquery.jobs.create
para ejecutar una tarea de consulta
Puede que se necesiten permisos adicionales, como bigquery.tables.getData
, para acceder a los datos que estás consultando.
Los siguientes roles de gestión de identidades y accesos predefinidos incluyen los permisos bigquery.tables.create
y bigquery.tables.updateData
:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
Los siguientes roles de gestión de identidades y accesos predefinidos incluyen los permisos de bigquery.jobs.create
:
bigquery.user
bigquery.jobUser
bigquery.admin
Además, si un usuario tiene permisos de bigquery.datasets.create
, cuando cree un conjunto de datos, se le concederá acceso de bigquery.dataOwner
a él.
bigquery.dataOwner
permite al usuario crear y actualizar tablas en el conjunto de datos.
Para obtener más información sobre los roles y permisos de IAM en BigQuery, consulta el artículo sobre funciones y permisos predefinidos.
Escribir los resultados de una consulta en una tabla permanente
Cuando escribes los resultados de una consulta en una tabla permanente, puedes crear una tabla, añadir los resultados a una tabla o sobrescribir una tabla.
Escribir resultados de consultas
Sigue este procedimiento para escribir los resultados de la consulta en una tabla permanente. Para controlar los costes, puede previsualizar los datos antes de ejecutar la consulta.
Consola
Abre la página de BigQuery en la Google Cloud consola.
En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.
Introduce una consulta de SQL válida.
Haz clic en Más y, a continuación, selecciona Configuración de la consulta.
Selecciona la opción Definir una tabla de destino para los resultados de las consultas.
En la sección Destino, seleccione el Conjunto de datos en el que quiera crear la tabla y, a continuación, elija un ID de tabla.
En la sección Preferencia de escritura de la tabla de destino, elija una de las siguientes opciones:
- Escribir si está vacía: escribe los resultados de la consulta en la tabla solo si está vacía.
- Añadir a tabla: añade los resultados de la consulta a una tabla.
- Sobrescribir tabla: sobrescribe una tabla con el mismo nombre con los resultados de la consulta.
Opcional: En Ubicación de los datos, elija su ubicación.
Para actualizar la configuración de la consulta, haz clic en Guardar.
Haz clic en Ejecutar. De esta forma, se crea una tarea de consulta que escribe los resultados de la consulta en la tabla que ha especificado.
Si olvidas especificar una tabla de destino antes de ejecutar la consulta, puedes copiar la tabla de resultados almacenada en caché en una tabla permanente haciendo clic en el botón Guardar resultados situado encima del editor.
SQL
En el siguiente ejemplo se usa la instrucción CREATE TABLE
para crear la tabla trips
a partir de los datos de la tabla pública bikeshare_trips
:
En la Google Cloud consola, ve a la página BigQuery.
En el editor de consultas, introduce la siguiente instrucción:
CREATE TABLE mydataset.trips AS ( SELECT bike_id, start_time, duration_minutes FROM bigquery-public-data.austin_bikeshare.bikeshare_trips );
Haz clic en
Ejecutar.
Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.
Para obtener más información, consulta el artículo Crear una tabla a partir de otra tabla.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Introduce el comando
bq query
y especifica la marca--destination_table
para crear una tabla permanente basada en los resultados de la consulta. Especifica la marcause_legacy_sql=false
para usar la sintaxis de GoogleSQL. Para escribir los resultados de la consulta en una tabla que no esté en tu proyecto predeterminado, añade el ID del proyecto al nombre del conjunto de datos con el siguiente formato:project_id:dataset
.Opcional: Proporciona la marca
--location
y asigna el valor a tu ubicación.Para controlar la disposición de escritura de una tabla de destino, especifique una de las siguientes marcas opcionales:
--append_table
: si la tabla de destino existe, los resultados de la consulta se añaden a ella.--replace
: Si la tabla de destino ya existe, se sobrescribe con los resultados de la consulta.bq --location=location query \ --destination_table project_id:dataset.table \ --use_legacy_sql=false 'query'
Haz los cambios siguientes:
location
es el nombre de la ubicación que se usa para procesar la consulta. La marca--location
es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes asignar el valorasia-northeast1
a la marca. Puedes definir un valor predeterminado para la ubicación mediante el archivo.bigqueryrc
.project_id
es el ID del proyecto.dataset
es el nombre del conjunto de datos que contiene la tabla en la que se escriben los resultados de la consulta.table
es el nombre de la tabla en la que se escriben los resultados de la consulta.query
es una consulta con sintaxis de GoogleSQL.Si no se especifica ninguna marca de disposición de escritura, el comportamiento predeterminado es escribir los resultados en la tabla solo si está vacía. Si la tabla existe y no está vacía, se devuelve el siguiente error:
BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1: Already Exists: Table project_id:dataset.table
.Ejemplos:
Introduce el siguiente comando para escribir los resultados de la consulta en una tabla de destino llamada
mytable
enmydataset
. El conjunto de datos está en tu proyecto predeterminado. Como no se ha especificado ninguna marca de disposición de escritura en el comando, la tabla debe ser nueva o estar vacía. De lo contrario, se devuelve un errorAlready exists
. La consulta obtiene datos del conjunto de datos público USA Name Data.bq query \ --destination_table mydataset.mytable \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
Introduce el siguiente comando para usar los resultados de la consulta y sobrescribir una tabla de destino llamada
mytable
enmydataset
. El conjunto de datos está en tu proyecto predeterminado. El comando usa la marca--replace
para sobrescribir la tabla de destino.bq query \ --destination_table mydataset.mytable \ --replace \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
Introduce el siguiente comando para añadir los resultados de la consulta a una tabla de destino llamada
mytable
enmydataset
. El conjunto de datos está enmy-other-project
, no en tu proyecto predeterminado. El comando usa la marca--append_table
para añadir los resultados de la consulta a la tabla de destino.bq query \ --append_table \ --use_legacy_sql=false \ --destination_table my-other-project:mydataset.mytable \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
El resultado de cada uno de estos ejemplos es el siguiente. Para mejorar la legibilidad, se ha truncado parte del resultado.
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
API
Para guardar los resultados de una consulta en una tabla permanente, llama al método jobs.insert
, configura una tarea query
e incluye un valor para la propiedad destinationTable
. Para controlar la disposición de escritura de una tabla de destino, configura la propiedad writeDisposition
.
Para controlar la ubicación de procesamiento de la tarea de consulta, especifica la propiedad location
en la sección jobReference
del recurso Job.
Go
Antes de probar este ejemplo, sigue las Goinstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Go de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Java
Antes de probar este ejemplo, sigue las Javainstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Java de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
TableId que quieras a la tabla de destino en una QueryJobConfiguration.
Node.js
Antes de probar este ejemplo, sigue las Node.jsinstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Node.js de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Python
Antes de probar este ejemplo, sigue las Pythoninstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Python de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Para guardar los resultados de una consulta en una tabla permanente, crea un objeto QueryJobConfig y asigna el objeto destination al objeto TableReference que quieras. Transfiere la configuración de la tarea al método query.Escribir resultados de consultas grandes
Normalmente, las consultas tienen un tamaño de respuesta máximo. Si tienes previsto ejecutar una consulta que puede devolver resultados de mayor tamaño, puedes hacer lo siguiente:
- En GoogleSQL, especifica una tabla de destino para los resultados de la consulta.
- En SQL antiguo, especifique una tabla de destino y defina la opción
allowLargeResults
.
Si especificas una tabla de destino para los resultados de consultas de gran tamaño, se te cobrará por almacenar los datos.
Limitaciones
En SQL antiguo, la escritura de resultados de gran tamaño está sujeta a estas limitaciones:
- Debes especificar una tabla de destino.
- No puede especificar una cláusula
ORDER BY
,TOP
oLIMIT
de nivel superior. Si lo haces, se anula la ventaja de usarallowLargeResults
, ya que la salida de la consulta ya no se puede calcular en paralelo. - Las funciones de ventana solo pueden devolver resultados de consulta grandes si se usan junto con una cláusula
PARTITION BY
.
Escribir resultados grandes con SQL antiguo
Para escribir conjuntos de resultados de gran tamaño con SQL antiguo, sigue estos pasos:
Consola
En la Google Cloud consola, abre la página de BigQuery.
Haz clic en Redactar nueva consulta.
Introduce una consulta de SQL válida en el área de texto Editor de consultas. Usa el prefijo
#legacySQL
o asegúrate de que la opción Usar SQL antiguo esté marcada en la configuración de la consulta.Haz clic en Más y, a continuación, selecciona Configuración de la consulta.
En Destino, marque Definir una tabla de destino para los resultados de las consultas.
En Conjunto de datos, elija el conjunto de datos en el que se almacenará la tabla.
En el campo Table Id (ID de tabla), introduce el nombre de la tabla.
Si vas a escribir un conjunto de resultados de gran tamaño en una tabla, puedes usar las opciones de Preferencia de escritura en tabla de destino para controlar la disposición de escritura de la tabla de destino:
- Escribir si está vacía: escribe los resultados de la consulta en la tabla solo si está vacía.
- Añadir a tabla: añade los resultados de la consulta a una tabla.
- Sobrescribir tabla: sobrescribe una tabla con el mismo nombre usando los resultados de la consulta.
En Tamaño de los resultados, marca Permitir resultados grandes (sin límite de tamaño).
Opcional: En Ubicación de los datos, elige la ubicación de tus datos.
Haz clic en Guardar para actualizar la configuración de la consulta.
Haz clic en Ejecutar. De esta forma, se crea una tarea de consulta que escribe el conjunto de resultados de gran tamaño en la tabla que hayas especificado.
bq
Usa la marca --allow_large_results
con la marca --destination_table
para crear una tabla de destino que contenga el conjunto de resultados de gran tamaño. Como la opción --allow_large_results
solo se aplica al SQL antiguo, también debes especificar la marca --use_legacy_sql=true
. Para escribir los resultados de la consulta en una tabla que no esté en tu proyecto predeterminado, añade el ID del proyecto al nombre del conjunto de datos con el siguiente formato: PROJECT_ID:DATASET
.
Proporcione la marca --location
y asigne el valor a su ubicación.
Para controlar la disposición de escritura de una tabla de destino, especifica una de las siguientes marcas opcionales:
--append_table
: si la tabla de destino existe, los resultados de la consulta se añaden a ella.--replace
: Si la tabla de destino ya existe, se sobrescribe con los resultados de la consulta.
bq --location=location query \ --destination_table PROJECT_ID:DATASET.TABLE \ --use_legacy_sql=true \ --allow_large_results "QUERY"
Haz los cambios siguientes:
LOCATION
es el nombre de la ubicación que se usa para procesar la consulta. La marca--location
es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes asignar el valorasia-northeast1
a la marca. Puedes definir 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 contiene la tabla en la que se escriben los resultados de la consulta.TABLE
es el nombre de la tabla en la que se escriben los resultados de la consulta.QUERY
es una consulta con sintaxis de SQL antiguo.
Ejemplos:
Introduce el siguiente comando para escribir resultados de consultas de gran tamaño en una tabla de destino llamada mytable
en mydataset
. El conjunto de datos está en tu proyecto predeterminado. Como no se ha especificado ninguna marca de disposición de escritura en el comando, la tabla debe ser nueva o estar vacía. De lo contrario, se devuelve un error Already exists
. La consulta obtiene datos del conjunto de datos público USA Name Data.
Esta consulta se usa únicamente con fines de ejemplo. El conjunto de resultados devuelto no supera el tamaño máximo de respuesta.
bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
Introduce el siguiente comando para usar los resultados de una consulta grande y sobrescribir una tabla de destino llamada mytable
en mydataset
. El conjunto de datos está en myotherproject
, no en tu proyecto predeterminado. El comando usa la marca --replace
para sobrescribir la tabla de destino.
bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
Introduce el siguiente comando para añadir resultados de consultas grandes a una tabla de destino llamada mytable
en mydataset
. El conjunto de datos está en myotherproject
,
no en tu proyecto predeterminado. El comando usa la marca --append_table
para añadir los resultados de la consulta a la tabla de destino.
bq query \
--destination_table myotherproject:mydataset.mytable \
--append_table \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
API
Para escribir resultados de gran tamaño en una tabla de destino, llama al método jobs.insert
, configura un trabajo query
y asigna el valor true
a la propiedad allowLargeResults
.
Especifica la tabla de destino mediante la propiedad destinationTable
. Para controlar la disposición de escritura de una tabla de destino, configura la propiedad writeDisposition
.
Especifica tu ubicación en la propiedad location
de la sección jobReference
del recurso de empleo.
Go
Antes de probar este ejemplo, sigue las Goinstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Go de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Java
Para habilitar los resultados de gran tamaño, asigna el valor true
a allow large results y el valor TableId que quieras a destination table en un QueryJobConfiguration.
Antes de probar este ejemplo, sigue las Javainstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Java de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Node.js
Antes de probar este ejemplo, sigue las Node.jsinstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Node.js de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Python
Antes de probar este ejemplo, sigue las Pythoninstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Python de BigQuery.
Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente.
Descargar y guardar los resultados de las consultas desde la consola de Google Cloud
Después de ejecutar una consulta de SQL mediante la consola Google Cloud , puedes guardar los resultados en otra ubicación. Puedes usar la Google Cloud consola para descargar los resultados de las consultas en un archivo local, Hojas de cálculo de Google o Google Drive. Si primero ordenas los resultados de la consulta por columna, el orden se conservará en los datos descargados. La herramienta de línea de comandos bq ni la API admiten guardar los resultados en un archivo local, en Hojas de cálculo de Google ni en Google Drive.
Limitaciones
La descarga y el guardado de resultados de consultas están sujetos a las siguientes limitaciones:
- Solo puedes descargar los resultados de las consultas de forma local en formato CSV o JSON delimitado por líneas nuevas.
- No puedes guardar los resultados de consultas que contengan datos anidados y repetidos en Hojas de cálculo de Google.
- Para guardar los resultados de una consulta en Google Drive mediante la consola Google Cloud , el conjunto de resultados debe ser de 1 GB o menos. Si los resultados son más grandes, puedes guardarlos en una tabla.
- Cuando guardes los resultados de una consulta en un archivo CSV local, el tamaño máximo de descarga será de 10 MB.
El tamaño máximo de descarga se basa en el tamaño de cada fila devuelta en la respuesta del método
tabledata.list
y puede variar en función del esquema de los resultados de la consulta. Por lo tanto, el tamaño del archivo CSV descargado puede variar y ser inferior al límite de tamaño máximo de descarga. - Solo puedes guardar los resultados de las consultas en Google Drive en formato CSV o JSON delimitado por líneas nuevas.
Siguientes pasos
- Consulta cómo exportar una tabla a un archivo JSON mediante programación.
- Consulta información sobre las cuotas de los trabajos de consulta.
- Consulta los precios de almacenamiento de BigQuery.