Consultar dados do Drive
Nesta documento, descrevemos como usar o BigQuery para consultar dados armazenados no Drive.
O BigQuery aceita consultas de arquivos pessoais do Google Drive e arquivos compartilhados. Para mais informações sobre o Drive, consulte o Centro de aprendizagem do G Suite.
É possível consultar arquivos no Drive nos seguintes formatos:
- Valores separados por vírgula (CSV)
- JSON delimitado por nova linha
- Avro
- Planilhas
Para consultar uma fonte de dados externa do Google Drive, forneça o caminho do URI do Google Drive para seus dados e crie uma tabela externa que faça referência a essa fonte. A tabela usada como referência para a fonte de dados do Google Drive pode ser permanente ou temporária.
Limitação
Uma consulta do BigQuery pode sobrecarregar o Planilhas, resultando em um erro como Resources exceeded during query execution: Google Sheets service
overloaded.
Simplifique sua planilha: como ao minimizar
o uso de fórmulas.
Recuperar o URI do Drive
Para criar uma tabela externa para uma fonte de dados do Drive, você precisa fornecer o URI do Drive. Para recuperar o URI do Drive, consulte Compartilhar um link para o arquivo.
Formato do URI
https://docs.google.com/spreadsheets/d/FILE_ID
ou
https://drive.google.com/open?id=FILE_ID
em que FILE_ID
é o código alfanumérico para seu
arquivo do Drive.
Ativar o acesso ao Drive
O acesso a dados hospedados no Google Drive exige um escopo adicional
do OAuth, tanto durante a definição da origem federada quanto durante a execução da consulta.
Embora não seja ativado por padrão, ele pode ser incluído no Console do Cloud, na ferramenta de linha de comando bq
ou na API por meio dos seguintes mecanismos:
Console
Siga as etapas de autenticação baseada na Web durante a criação de uma tabela permanente no Console do Cloud. Quando solicitado, clique em Permitir para que o Google Drive possa acessar as ferramentas de cliente do BigQuery.
gcloud
Para ativar o acesso ao Drive:
Digite o comando a seguir para garantir que você tenha a versão mais recente da CLI do Google Cloud.
gcloud components update
Digite o comando a seguir para autenticar com o Drive.
gcloud auth login --enable-gdrive-access
API
Se você está usando a API BigQuery, solicite o escopo do OAuth para o Drive, além do escopo do BigQuery.
Python
Antes de testar esta amostra, siga as instruções de configuração para 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.
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.
Tabelas externas permanentes x temporárias
É possível consultar uma fonte de dados externa no BigQuery usando uma tabela permanente ou uma temporária. Uma tabela permanente é criada em um conjunto de dados e vinculada à sua fonte de dados externa. Como a tabela é permanente, é possível usar controles de acesso para compartilhá-la com outras pessoas que também têm acesso à fonte de dados externa subjacente e pode consultá-la a qualquer momento.
Ao consultar uma fonte de dados externa usando uma tabela temporária, você envia um comando que inclui uma consulta e cria uma tabela não permanente vinculada a essa fonte. A tabela temporária não é criada em um dos conjuntos de dados do BigQuery. Como ela não fica armazenada de modo permanente em um conjunto de dados, não é possível compartilhá-la com outras pessoas. A consulta a uma fonte de dados externa usando uma tabela temporária é útil quando você quer consultar dados externos apenas uma vez, com um propósito específico, ou executar processos de extração, transformação e carregamento (ETL).
Consultar dados do Drive usando tabelas externas permanentes
Permissões e escopos exigidos
Ao consultar dados externos no Drive usando uma tabela permanente, você precisa de permissões para executar um job de consulta no nível do projeto ou superior, para criar uma tabela que aponte para os dados externos e para acessar as informações da tabela. Quando seus dados externos são armazenados no Google Drive, também é preciso ter permissões para acessar o arquivo do Google Drive vinculado à sua tabela externa.
Permissões do BigQuery
Para criar e consultar uma tabela externa no BigQuery, é preciso ter, no mínimo, as seguintes permissões.
bigquery.tables.create
bigquery.tables.getData
bigquery.jobs.create
Os seguintes papéis predefinidos do IAM incluem as
permissões bigquery.tables.create
e bigquery.tables.getData
:
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, se um usuário tiver permissões bigquery.datasets.create
ao criar um conjunto de dados, será concedido o acesso bigquery.dataOwner
.
O acesso bigquery.dataOwner
permite que o usuário crie tabelas externas no conjunto de dados, mas ainda são necessárias permissões bigquery.jobs.create
para consultar as informações.
Para mais informações sobre papéis e permissões do IAM no BigQuery, consulte Papéis e permissões predefinidos.
Permissões do Drive
Para consultar dados externos no Google Drive, você precisa ter, no
mínimo, acesso de View
ao arquivo do Google Drive vinculado à tabela externa.
Escopos de instâncias do Compute Engine
Ao criar uma instância do Compute Engine, especifique uma lista de escopos para ela. Os escopos controlam o acesso da instância aos produtos do Google Cloud, incluindo o Drive. Os aplicativos em execução na VM usam a conta do serviço para chamar as APIs do Google Cloud.
Se você configurar uma instância do Compute Engine para ser executada
como uma conta de serviço
e essa conta acessar uma tabela externa vinculada a uma fonte de dados
do Drive, precisará adicionar o
Escopo do OAuth para o Drive
(https://www.googleapis.com/auth/drive.readonly
) para a instância.
Para mais informações sobre como aplicar escopos a uma instância do Compute Engine, consulte Como alterar a conta de serviço e os escopos de acesso de uma instância. Para mais informações sobre as contas de serviço do Compute Engine, acesse Contas de serviço.
Como criar e consultar uma tabela externa permanente
É possível criar uma tabela permanente vinculada à sua fonte de dados externa da seguinte forma:
- Use o Console do Cloud
- Use o comando
mk
da ferramenta de linha de comandobq
. - Criando um
ExternalDataConfiguration
ao usar o método de APItables.insert
- Como usar bibliotecas de cliente
Para consultar uma fonte de dados externa usando uma tabela permanente, crie uma tabela em um conjunto de dados do BigQuery vinculado a essa fonte. Os dados não ficam armazenados na tabela do BigQuery. Como a tabela é permanente, é possível usar controles de acesso para compartilhá-la com outras pessoas que também têm acesso à fonte de dados externa subjacente.
Ao criar uma tabela externa permanente, é possível especificar o esquema das seguintes maneiras:
- Forneça um esquema de tabela explícito
- Use a detecção automática de esquema.
Para criar uma tabela externa:
Console
- No console do Cloud, abra a página do BigQuery.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Expanda a opção
Ações e clique em Abrir.No painel de detalhes, clique em Criar tabela
.Na página Criar tabela, na seção Origem:
Em Criar tabela de, selecione Drive.
No campo Selecionar URI do Drive, insira o URI do Google Drive. Caracteres curinga não são compatíveis com esses URIs.
Em Formato do arquivo, selecione o formato dos dados. Estes são os formatos válidos para dados do Drive:
- Valores separados por vírgula (CSV)
- JSON delimitado por nova linha
- Avro
- Planilhas
(Opcional) Se você escolher o Planilhas Google, na caixa Intervalo da planilha (opcional), especifique os intervalos da planilha e das células a serem consultados. É possível especificar um nome de planilha ou
sheet_name!top_left_cell_id:bottom_right_cell_id
para um intervalo de células; por exemplo, "Planilha1!A1:B20". Se o intervalo da planilha não for especificado, a primeira planilha do arquivo será usada.Na página Criar tabela, na seção Destino:
Em Nome do conjunto de dados, escolha o conjunto de dados apropriado e, no campo Nome da tabela, insira o nome da tabela que você está criando no BigQuery.
Verifique se o Tipo de tabela está definido como Tabela externa.
Na seção Esquema, insira a definição do esquema.
- Em arquivos JSON ou CSV, marque a opção Detectar automaticamente para ativar a detecção automática de esquema. A detecção automática não está disponível para arquivos Avro e exportações do Datastore e do Firestore. As informações de esquema para esses tipos de arquivo são recuperadas automaticamente dos dados de origem autodescritivos.
- Insira as informações do esquema manualmente:
- Ative Editar como texto e insira o esquema da tabela como uma matriz JSON.
Observação: para ver o esquema de uma tabela no formato JSON,
insira o seguinte comando na ferramenta de linha de comando
bq
:bq show --format=prettyjson DATASET.TABLE
. - Use Adicionar campo para inserir manualmente o esquema.
- Ative Editar como texto e insira o esquema da tabela como uma matriz JSON.
Observação: para ver o esquema de uma tabela no formato JSON,
insira o seguinte comando na ferramenta de linha de comando
Clique em Criar tabela.
Se necessário, selecione sua conta e clique em Permitir para que o Google Drive possa acessar as ferramentas de cliente do BigQuery.
Depois disso, é possível fazer uma consulta na tabela como se ela fosse uma tabela nativa do BigQuery, sujeita às limitações nas fontes de dados externas.
Quando sua consulta for concluída, faça o download dos resultados como um arquivo CSV/JSON ou salve-os como uma tabela ou no Planilhas Google. Consulte Fazer download, salvar e exportar dados para mais informações.
bq
Crie uma tabela na ferramenta de linha de comando bq
usando o comando bq mk
. Ao usar a ferramenta de linha de comando bq
para criar uma tabela vinculada a uma fonte de dados externa, identifique o esquema dessa tabela usando:
- um arquivo de definição de tabela (armazenado em sua máquina local);
- uma definição de esquema in-line;
- um arquivo de esquema JSON (armazenado em sua máquina local).
Para criar uma tabela permanente vinculada à sua fonte de dados do Drive usando um arquivo de definição de tabela, digite o seguinte comando:
bq mk \ --external_table_definition=DEFINITION_FILE \ DATASET.TABLE
Em que:
DEFINITION_FILE
é o caminho do arquivo de definição de tabela na sua máquina local;DATASET
é o nome do conjunto de dados que contém a tabela;TABLE
é o nome da tabela que você está criando.
Por exemplo, o comando a seguir cria uma tabela permanente chamada mytable
usando um arquivo de definição de tabela nomeado mytable_def
.
bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable
Para criar uma tabela permanente vinculada à sua fonte de dados externa usando uma definição de esquema in-line, digite o seguinte comando.
bq mk \ --external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE
Em que:
SCHEMA
é a definição do esquema no formatoFIELD:DATA_TYPE,FIELD:DATA_TYPE
;SOURCE_FORMAT
éCSV
,NEWLINE_DELIMITED_JSON
,AVRO
ouGOOGLE_SHEETS
;DRIVE_URI
é o URI do Drive;DATASET
é o nome do conjunto de dados que contém a tabela;TABLE
é o nome da tabela que você está criando.
Por exemplo, o comando a seguir cria uma tabela permanente chamada sales
vinculada a um arquivo do Planilhas armazenado no Drive com a seguinte
definição de esquema: Region:STRING,Quarter:STRING,Total_sales:INTEGER
.
bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales
Para criar uma tabela permanente vinculada à sua fonte de dados externa usando um arquivo de esquema JSON, insira o seguinte comando.
bq mk \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE
Em que:
SCHEMA_FILE
é o caminho para o arquivo de esquema JSON na sua máquina local;SOURCE_FORMAT
éCSV
,NEWLINE_DELIMITED_JSON
,AVRO
ouGOOGLE_SHEETS
;DRIVE_URI
é o URI do Drive;DATASET
é o nome do conjunto de dados que contém a tabela;TABLE
é o nome da tabela que você está criando.
Por exemplo, o comando a seguir cria uma tabela chamada sales
vinculada
a um arquivo CSV armazenado no Drive usando o arquivo de esquema
/tmp/sales_schema.json
.
bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales
Após a criação da tabela permanente, será possível fazer uma consulta como se ela fosse uma tabela nativa do BigQuery, sujeita às limitações das fontes de dados externas.
Quando sua consulta for concluída, faça o download dos resultados como um arquivo CSV/JSON ou salve-os como uma tabela ou no Planilhas Google. Consulte Fazer download, salvar e exportar dados para mais informações.
API
Crie uma ExternalDataConfiguration
quando utilizar o método de API tables.insert
. Especifique a property schema
ou defina a property autodetect
como true
para ativar a detecção automática de esquema para fontes de dados compatíveis.
Python
Antes de testar esta amostra, siga as instruções de configuração para 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.
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.
Consultar dados do Drive usando tabelas temporárias
Para consultar uma fonte de dados externa sem criar uma tabela permanente, execute um comando para combinar:
- um arquivo de definição de tabela com uma consulta;
- uma definição de esquema in-line com uma consulta;
- um arquivo de definição de esquema JSON com uma consulta.
O arquivo de definição de tabela ou esquema fornecido é usado para criar a tabela externa temporária,
e a consulta será executada nela. É possível consultar uma fonte de dados externa usando uma
tabela temporária com a ferramenta de linha de comando bq
e na API.
A tabela externa temporária não é criada em um dos conjuntos de dados do BigQuery. Como ela não fica armazenada permanentemente em um conjunto de dados, não é possível compartilhá-la com outros. A consulta a uma fonte de dados externa usando uma tabela temporária é útil quando você quer consultar dados externos apenas uma vez, com um propósito específico, ou executar processos de Extração, transformação e carregamento (ETL).
Permissões necessárias
Ao consultar dados externos no Drive usando uma tabela temporária, você precisa de permissões para executar um job de consulta no nível do projeto ou superior, acesso ao conjunto de dados que contém a tabela que aponta para os dados externos. Ao consultar dados no Google Drive, você também precisa de permissões para acessar o arquivo do Google Drive que contém seus dados.
Permissões do BigQuery
Para consultar uma tabela externa no BigQuery usando uma tabela temporária, é necessário ter, no mínimo, as seguintes permissões.
bigquery.tables.getData
bigquery.jobs.create
Os seguintes papéis predefinidos do IAM incluem
as permissões bigquery.tables.getData
:
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, se um usuário tiver permissões bigquery.datasets.create
ao criar um conjunto de dados, será concedido o acesso bigquery.dataOwner
.
O acesso bigquery.dataOwner
permite que o usuário crie e acesse tabelas externas no conjunto de dados, mas as permissões bigquery.jobs.create
ainda são necessárias para consultar os dados.
Para mais informações sobre papéis e permissões do IAM no BigQuery, consulte Papéis e permissões predefinidos.
Permissões do Drive
Para consultar dados externos no Google Drive, você precisa ter, no
mínimo, acesso de View
ao arquivo do Google Drive vinculado à tabela externa.
Criar e consultar uma tabela temporária
É possível criar e consultar uma tabela temporária vinculada a uma fonte de dados externa
usando a ferramenta de linha de comando bq
, a API ou as bibliotecas de cliente.
bq
Consulte uma tabela temporária vinculada a uma fonte de dados externa usando o comando bq query
com a sinalização --external_table_definition
. Ao usar
a ferramenta de linha de comando bq
para consultar uma tabela temporária vinculada a uma fonte de dados externa,
é possível identificar o esquema dessa tabela usando:
- um arquivo de definição de tabela (armazenado em sua máquina local);
- uma definição de esquema in-line;
- um arquivo de esquema JSON (armazenado em sua máquina local).
Para consultar uma tabela temporária vinculada à sua fonte de dados externa usando um arquivo de definição de tabela, insira o seguinte comando:
bq --location=LOCATION query \ --external_table_definition=TABLE::DEFINITION_FILE \ 'QUERY'
Em que:
LOCATION
é o local. A sinalização--location
é opcional;TABLE
é o nome da tabela temporária que você está criando;DEFINITION_FILE
é o caminho do arquivo de definição de tabela na sua máquina local;QUERY
é a consulta que você está enviando para a tabela temporária.
Por exemplo, o comando a seguir cria e consulta uma tabela temporária
chamada sales
usando um arquivo de definição de tabela chamado sales_def
.
bq query \
--external_table_definition=sales::sales_def \
'SELECT
Region,Total_sales
FROM
sales'
Para consultar uma tabela temporária vinculada à sua fonte de dados externa usando uma definição de esquema in-line, digite o seguinte comando.
bq --location=LOCATION query \ --external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=DRIVE_URI \ 'QUERY'
Em que:
LOCATION
é o local. A sinalização--location
é opcional;TABLE
é o nome da tabela temporária que você está criando;SCHEMA
é a definição de esquema in-line no formatoFIELD:DATA_TYPE,FIELD:DATA_TYPE
;SOURCE_FORMAT
éCSV
,NEWLINE_DELIMITED_JSON
,AVRO
ouGOOGLE_SHEETS
;DRIVE_URI
é o URI do Drive;QUERY
é a consulta que você está enviando para a tabela temporária.
Por exemplo, o comando a seguir cria e consulta uma tabela temporária
chamada sales
vinculada a um arquivo CSV armazenado no Drive com a
seguinte definição de esquema: Region:STRING,Quarter:STRING,Total_sales:INTEGER
.
bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
Region,Total_sales
FROM
sales'
Para consultar uma tabela temporária vinculada à sua fonte de dados externa usando um arquivo de esquema JSON, insira o seguinte comando.
bq --location=LOCATION query \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \ 'QUERY'
Em que:
LOCATION
é o local. A sinalização--location
é opcional;SCHEMA_FILE
é o caminho para o arquivo de esquema JSON na sua máquina local;SOURCE_FILE
éCSV
,NEWLINE_DELIMITED_JSON
,AVRO
ouGOOGLE_SHEETS
;DRIVE_URI
é o URI do Drive;QUERY
é a consulta que você está enviando para a tabela temporária.
Por exemplo, o comando a seguir cria e consulta uma tabela temporária
chamada sales
vinculada a um arquivo CSV armazenado no Drive usando o
arquivo de esquema /tmp/sales_schema.json
.
bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
Total_sales
FROM
sales'
API
Crie uma configuração de job de consulta. Consulte Como consultar dados para informações sobre como chamar
jobs.query
ejobs.insert
.Especifique a fonte de dados externa criando um
ExternalDataConfiguration
.
Python
Antes de testar esta amostra, siga as instruções de configuração para 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.
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.
A pseudocoluna _FILE_NAME
Tabelas baseadas em fontes de dados externas fornecem uma pseudocoluna denominada _FILE_NAME
. Ela contém o caminho totalmente qualificado para o arquivo ao qual a linha pertence. Essa coluna está disponível apenas para tabelas que fazem referência a dados externos armazenados no Cloud Storage e no Google Drive.
O nome de coluna _FILE_NAME
é reservado. Por isso, não crie nenhuma coluna com esse nome nas tabelas. Para selecionar o valor de _FILE_NAME
, use um alias. A consulta de exemplo abaixo mostra a seleção de _FILE_NAME
ao atribuir o alias fn
na pseudocoluna.
bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
name,
_FILE_NAME AS fn
FROM
`dataset.table_name`
WHERE
name contains "Alex"'
Em que:
- project_id é um ID de projeto válido (essa sinalização não é necessária se você usa o Cloud Shell ou configurou um projeto padrão no SDK do Cloud);
- dataset é o nome do conjunto de dados que armazena a tabela externa permanente;
- table_name é o nome da tabela externa permanente.
Quando a consulta tem um predicado de filtro na pseudocoluna _FILE_NAME
, o BigQuery tenta ignorar a leitura de arquivos que não satisfazem esse filtro. Recomendações semelhantes às
consultas de tabelas particionadas por tempo de processamento usando pseudocolunas
aplicam-se para criar predicados de consulta com a pseudocoluna _FILE_NAME
.