Como gravar resultados de consulta
Neste documento, descrevemos como gravar resultados de consulta em tabelas temporárias ou permanentes.
Tabelas temporárias e permanentes
O BigQuery salva todos os resultados de consulta em uma tabela, que pode ser permanente ou temporária.
O BigQuery usa tabelas temporárias para armazenar em cache os resultados da consulta que não são gravados em uma tabela permanente. As tabelas são criadas em um conjunto de dados especial e nomeadas aleatoriamente. Também é possível criar tabelas temporárias para seu próprio uso em consultas de várias instruções e sessões.
Após o término de uma consulta, a tabela temporária existirá por até 24 horas. Para ver os dados e a estrutura da tabela, acesse o console do BigQuery, clique em Histórico pessoal e escolha a consulta que criou a tabela temporária. Depois, na linha Tabela de destino, clique em Tabela temporária.
O acesso aos dados da tabela temporária é restrito ao usuário ou à conta de serviço que criou o job de consulta.
Não é possível compartilhar tabelas temporárias, e elas não são visíveis usando listas padrões ou outros métodos de manipulação de tabela. Tabelas temporárias são criadas na mesma região que a tabela ou as tabelas que estão sendo consultadas.
Uma tabela permanente pode ser uma tabela nova ou atual em qualquer conjunto de dados a que você tenha acesso. Se você gravar os resultados da consulta em uma tabela nova, será cobrado pelo armazenamento dos dados. Quando você grava os resultados da consulta em uma tabela permanente, as tabelas consultadas precisam estar no mesmo local que o conjunto de dados que contém a tabela de destino.
Permissões necessárias
Para gravar resultados de consulta em uma tabela, é necessário ter, no mínimo, as seguintes permissões:
bigquery.tables.create
para criar uma nova tabelabigquery.tables.updateData
para gravar dados em uma nova tabela, substituir uma tabela ou anexar dados a uma tabelabigquery.jobs.create
para executar um job de consulta
Outras permissões, como bigquery.tables.getData
, podem ser necessárias para acessar os dados que você está consultando.
Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.tables.create
e bigquery.tables.updateData
:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.jobs.create
:
bigquery.user
bigquery.jobUser
bigquery.admin
Além disso, quando um usuário tem permissões bigquery.datasets.create
e cria um conjunto de dados, ele recebe o acesso bigquery.dataOwner
ao conjunto.
O acesso bigquery.dataOwner
permite que o usuário crie e atualize tabelas no conjunto de dados.
Para mais informações sobre papéis e permissões do IAM no BigQuery, consulte Papéis e permissões predefinidos.
Gravar os resultados da consulta em uma tabela permanente
Ao gravar resultados de consulta em uma tabela permanente, é possível criar uma tabela nova, anexar os resultados a uma atual ou substituí-la.
Como gravar resultados de consulta
Use o procedimento a seguir para gravar os resultados da consulta em uma tabela permanente. Para ajudar a controlar custos, é possível visualizar os dados antes de executar a consulta.
Console
Abra a página do BigQuery no console do Google Cloud.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Insira uma consulta SQL válida.
Clique em Mais e selecione Configurações de consulta.
Selecione a opção Definir uma tabela de destino para os resultados da consulta.
Na seção Destino, selecione o Conjunto de dados em que você quer criar a tabela e escolha um ID de tabela.
Na seção Preferência de gravação na tabela de destino, escolha uma das seguintes opções:
- Gravar apenas se a tabela estiver vazia: grava os resultados da consulta na tabela apenas se ela estiver vazia.
- Anexar à tabela: anexa os resultados da consulta a uma tabela atual.
- Substituir tabela: usa os resultados da consulta para substituir uma tabela atual com o mesmo nome.
Opcional: em Local dos dados, escolha o local.
Para atualizar as configurações de consulta, clique em Salvar.
Clique em Executar. Isso cria um job de consulta que grava os resultados da consulta na tabela que você especificou.
Como alternativa, se você esquecer de especificar uma tabela de destino antes de executar a consulta, clique no botão Salvar resultados acima do editor para copiar a tabela de resultados em cache para uma tabela permanente.
SQL
O exemplo a seguir usa a
instrução CREATE TABLE
para criar a tabela trips
a partir de dados na tabela pública
bikeshare_trips
:
No Console do Google Cloud, acesse a página BigQuery.
No editor de consultas, digite a seguinte instrução:
CREATE TABLE mydataset.trips AS ( SELECT bike_id, start_time, duration_minutes FROM bigquery-public-data.austin_bikeshare.bikeshare_trips );
Clique em
Executar.
Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.
Para mais informações, consulte Como criar uma nova tabela a partir de uma existente.
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.
Insira o comando
bq query
e especifique a sinalização--destination_table
para criar uma tabela permanente com base nos resultados da consulta. Especifique a sinalizaçãouse_legacy_sql=false
para usar a sintaxe do GoogleSQL. Para gravar os resultados da consulta em uma tabela que não esteja em seu projeto padrão, adicione o ID do projeto ao nome do conjunto de dados no seguinte formato:project_id:dataset
.Opcional: forneça a sinalização
--location
e defina o valor para seu local.Para controlar a disposição de gravação de uma tabela de destino, especifique uma das seguintes sinalizações opcionais:
--append_table
: se a tabela de destino existir, os resultados da consulta serão anexados a ela.--replace
: se a tabela de destino existir, ela será substituída pelos resultados da consulta.bq --location=location query \ --destination_table project_id:dataset.table \ --use_legacy_sql=false 'query'
Substitua:
location
é o nome do local usado para processar a consulta. A sinalização--location
é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, é possível definir o valor da sinalização comoasia-northeast1
. É possível definir um valor padrão para o local usando o arquivo.bigqueryrc
.project_id
é o ID do projeto.dataset
é o nome do conjunto de dados que contém a tabela na qual você está gravando os resultados da consulta.table
é o nome da tabela na qual você está gravando os resultados da consulta.query
é uma consulta na sintaxe do GoogleSQL.Se nenhuma sinalização de disposição de gravação for especificada, o comportamento padrão será gravar os resultados na tabela somente se ela estiver vazia. Se a tabela existir e não estiver vazia, o seguinte erro será retornado: "BigQuery error in query operation: Error processing job
project_id:bqjob_123abc456789_00000e1234f_1': Already Exists: Table project_id:dataset.table
".Exemplos:
Digite o comando a seguir para gravar resultados de consulta em uma tabela de destino chamada
mytable
emmydataset
. O conjunto de dados está no projeto padrão. Como nenhuma sinalização de disposição de gravação está especificada no comando, a tabela precisa ser nova ou estar vazia. Caso contrário, é retornado um erroAlready exists
. A consulta recupera dados do conjunto de dados público USA Name Data (link em inglês).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'
Digite o comando a seguir para usar resultados da consulta e substituir uma tabela de destino chamada
mytable
emmydataset
. O conjunto de dados está no projeto padrão. O comando usa a sinalização--replace
para substituir a tabela 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'
Digite o comando a seguir para anexar resultados de consulta a uma tabela de destino chamada
mytable
emmydataset
. O conjunto de dados está emmy-other-project
, e não no projeto padrão. O comando usa a sinalização--append_table
para anexar os resultados da consulta na tabela 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'
A saída de cada um desses exemplos será semelhante ao seguinte. Para facilitar a leitura, ocultamos parte do resultado.
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
API
Para salvar resultados de consulta em uma tabela permanente, chame o método jobs.insert
, configure um job query
e inclua um valor para a property destinationTable
. Para controlar a disposição de gravação de uma tabela de destino atual, configure a property writeDisposition
.
Para controlar o local de processamento do job de consulta, especifique a property location
na seção jobReference
do recurso do job.
Go
Antes de testar esta amostra, siga as instruções de configuração do Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Go.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Java
Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Para salvar os resultados de consulta em uma tabela permanente, defina a tabela de destino como o TableId desejado em uma QueryJobConfiguration.
Node.js
Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Node.js.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Python
Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Para salvar resultados de consulta em uma tabela permanente, crie um QueryJobConfig e defina o destino como a TableReference desejada. Transmita a configuração do job para o método de consulta.Gravar resultados extensos de consulta
Normalmente, as consultas têm um tamanho máximo de resposta. Se você planeja executar uma consulta que possa retornar resultados maiores, é possível realizar uma das seguintes ações:
- No GoogleSQL, especifique uma tabela de destino para os resultados da consulta.
- especificar uma tabela de destino e definir a opção
allowLargeResults
no SQL legado.
Não há cobrança pelo armazenamento dos dados ao especificar uma tabela de destino para resultados de consulta extensos.
Limitações
No SQL legado, a gravação de resultados extensos está sujeita a estas limitações:
- É necessário especificar uma tabela de destino.
- Não é possível especificar uma cláusula de nível superior
ORDER BY
,TOP
ouLIMIT
. Se você fizer isso, o benefício de usarallowLargeResults
será negado, porque a saída da consulta não poderá mais ser computada em paralelo. - As funções de janela somente poderão retornar resultados de consulta extensos se usadas em conjunto com uma cláusula
PARTITION BY
.
Como gravar resultados extensos usando SQL legado
Para gravar conjuntos de resultados extensos usando SQL legado:
Console
No Console do Google Cloud, abra a página do BigQuery.
Clique em Escrever nova consulta.
Insira uma consulta SQL válida na área de texto do Editor de consultas. Use o prefixo
#legacySQL
ou verifique se você marcou Usar SQL legado nas configurações da consulta.Clique em Mais e selecione Configurações de consulta.
Em Destino, marque Definir uma tabela de destino para os resultados da consulta.
Em Conjunto de dados, selecione o conjunto de dados que armazenará a tabela.
No campo ID da tabela, insira um nome de tabela.
Se você estiver gravando um conjunto de resultados extensos em uma tabela atual, use as opções de Preferência de gravação na tabela de destino para controlar a disposição de gravação da tabela de destino:
- Write if empty: grava os resultados da consulta na tabela apenas se ela estiver vazia.
- Anexar à tabela: anexa os resultados da consulta a uma tabela atual.
- Overwrite table: usa os resultados da consulta para substituir uma tabela atual com o mesmo nome.
Em Tamanho dos resultados, marque Permitir resultados extensos (sem limite de tamanho).
Opcional: em Local dos dados, escolha o local dos dados.
Clique em Salvar para atualizar as configurações da consulta.
Clique em Executar. Isso cria um job de consulta que grava os resultados extensos definidos na tabela que você especificou.
bq
Use a sinalização --allow_large_results
com a sinalização --destination_table
para criar uma tabela de destino que armazene o conjunto de resultados extensos. Como a opção --allow_large_results
só se aplica ao SQL legado, é preciso especificar também a sinalização --use_legacy_sql=true
. Para gravar os resultados da consulta em uma tabela que não esteja no projeto padrão, adicione o ID do projeto ao nome do conjunto de dados no seguinte formato:
PROJECT_ID:DATASET
.
Forneça a sinalização --location
e defina o valor como seu local.
Para controlar a disposição de gravação de uma tabela de destino atual, especifique uma das seguintes sinalizações opcionais:
--append_table
: se a tabela de destino existir, os resultados da consulta serão anexados a ela.--replace
: se a tabela de destino existir, ela será substituída pelos resultados da consulta.
bq --location=location query \ --destination_table PROJECT_ID:DATASET.TABLE \ --use_legacy_sql=true \ --allow_large_results "QUERY"
Substitua:
LOCATION
é o nome do local usado para processar a consulta. A sinalização--location
é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, é possível definir o valor da sinalização comoasia-northeast1
. É possível definir um valor padrão para o local usando o arquivo.bigqueryrc
.PROJECT_ID
é o ID do projeto.DATASET
é o nome do conjunto de dados que contém a tabela na qual você está gravando os resultados da consulta.TABLE
é o nome da tabela na qual você está gravando os resultados da consulta.QUERY
é uma consulta na sintaxe do SQL legado.
Exemplos:
Digite o comando a seguir para gravar resultados de consulta extensos em uma tabela de destino chamada mytable
em mydataset
. O conjunto de dados está no projeto padrão. Como nenhuma sinalização de disposição de gravação está especificada no comando, a tabela precisa ser nova ou estar vazia. Caso contrário, é retornado um erro Already exists
. A consulta recupera dados do conjunto de dados público USA Name Data.
Essa consulta é usada apenas para fins de exemplo. O conjunto de resultados retornado não excede o tamanho máximo de resposta.
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"
Digite o comando a seguir para usar resultados de consulta extensos e substituir uma tabela de destino chamada mytable
em mydataset
. O conjunto de dados está em myotherproject
, e não no projeto padrão. O comando usa a sinalização --replace
para substituir a tabela 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"
Digite o comando a seguir para anexar resultados de consulta extensos em uma tabela de destino chamada mytable
em mydataset
. O conjunto de dados está em myotherproject
, e não no projeto padrão. O comando usa a sinalização --append_table
para anexar os resultados da consulta na tabela 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 gravar resultados extensos em uma tabela de destino, chame o método jobs.insert
, configure um job query
e defina a property allowLargeResults
como true
.
Especifique a tabela de destino usando a property destinationTable
. Para controlar a disposição de gravação de uma tabela de destino atual, configure a property writeDisposition
.
Especifique seu local na property location
da seção jobReference
do recurso do job.
Go
Antes de testar esta amostra, siga as instruções de configuração do Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Go.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Java
Para ativar resultados extensos, defina
Permitir resultados extensos
como true
e configure a tabela de
destino
como o
TableId desejado
em uma
QueryJobConfiguration.
Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Node.js
Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Node.js.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Python
Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.
Como fazer o download e salvar resultados de consulta no console do Google Cloud
Depois de executar uma consulta SQL usando o console do Google Cloud, salve os resultados em outro local. Use o console do Google Cloud para fazer o download dos resultados da consulta em um arquivo local, no Planilhas Google ou no Drive. Se você classificar primeiro os resultados da consulta por coluna, a ordem será preservada nos dados transferidos. Não é possível salvar os resultados em um arquivo local, no Planilhas Google ou no Google Drive usando a ferramenta de linha de comando bq ou a API.
Limitações
As seguintes limitações podem ser aplicadas ao fazer o download dos resultados da consulta e salvá-los:
- Só é possível fazer o download dos resultados da consulta localmente no formato CSV ou JSON delimitado por linhas novas.
- Não é possível salvar resultados de consulta que contenham dados aninhados e repetidos no Planilhas Google.
- Para salvar os resultados da consulta no Google Drive usando o console do Google Cloud, o conjunto de resultados precisa ter 1 GB ou menos. Se os resultados forem maiores, salve-os em uma tabela.
- Ao salvar os resultados da consulta em um arquivo CSV local, o tamanho máximo do download é de
10 MB.
O tamanho máximo do download é baseado no tamanho de cada linha retornada na
resposta do método
tabledata.list
e pode variar de acordo com o esquema dos resultados da consulta. Como resultado, o tamanho do arquivo CSV salvo pode variar e pode ser menor que o limite máximo de tamanho de download. - Só é possível salvar os resultados da consulta no Google Drive em formato CSV ou JSON delimitado por linhas novas.
A seguir
- Saiba como exportar uma tabela para um arquivo JSON de maneira programática.
- Saiba mais sobre cotas para jobs de consulta.
- Saiba mais sobre os preços de armazenamento do BigQuery.