Como consultar dados do Google Drive

Nesta página, você aprenderá a usar o BigQuery para consultar dados armazenados no Google Drive.

O BigQuery aceita consultas de arquivos pessoais do Google Drive e arquivos compartilhados. Para mais informações sobre o Google Drive, consulte o Centro de aprendizagem do G Suite.

É possível consultar arquivos no Google Drive nos seguintes formatos:

  • Valores separados por vírgulas (CSV)
  • JSON delimitado por nova linha
  • Avro
  • Planilhas Google

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.

Como recuperar o URI do Google Drive

Para criar uma tabela externa para uma fonte de dados do Google Drive, você precisa fornecer o URI do Google Drive. Para recuperar o URI do Google Drive, faça o seguinte:

  1. Acesse o Google Drive.

  2. Clique com o botão direito do mouse no arquivo e selecione Gerar link compartilhável. O URI deve ser parecido com esse: https://drive.google.com/open?id=file_id.

    Em que:

    • file_id é o código alfanumérico do seu arquivo do Google Drive

Se preferir, abra o arquivo para recuperar o URI. Por exemplo, para recuperar o URI de um arquivo do Planilhas Google:

  1. Acesse Planilhas Google.

  2. Abra sua planilha e copie o URI na barra de endereço do navegador. O URI deve ter o seguinte formato: https://docs.google.com/spreadsheets/d/file_id.

Como ativar o acesso ao Google 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 adicionado na UI, na CLI ou pela API por meio dos mecanismos abaixo:

Console

Siga as etapas de autenticação baseada na Web durante a criação de uma tabela permanente na IU da Web do BigQuery. Quando solicitado, clique em Permitir para que o Google Drive possa acessar as ferramentas de cliente do BigQuery.

IU clássica

Siga as etapas de autenticação baseada na Web durante a criação de uma tabela permanente na IU da Web do BigQuery. Quando solicitado, clique em Permitir para que o Google Drive possa acessar as ferramentas de cliente do BigQuery.

CLI

Para ativar o acesso ao Google Drive:

  1. Digite o comando abaixo para garantir que você tenha a versão mais recente da ferramenta de linha de comando.

    gcloud components update
    
  2. Digite o comando a seguir para autenticar com o Google Drive.

    gcloud auth login --enable-gdrive-access
    

API

Se você está usando a API BigQuery, solicite o escopo do OAuth para o Google Drive além do escopo do BigQuery.

Python

Antes de testar essa 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 para Python.

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

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. Por ela ser permanente, é possível usar controles de acesso no nível do conjunto de dados para compartilhar a tabela com outras pessoas que também têm acesso à fonte de dados externa subjacente, além de 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 em uma fonte de dados externa usando uma tabela temporária é útil no caso de consultas específicas únicas em dados externos ou para extrair, transformar e carregar (ETL, na sigla em inglês) processos.

Como consultar dados do Google Drive usando tabelas externas permanentes

Permissões e escopos necessários

Ao consultar dados externos no Google 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 Cloud IAM incluem as permissões bigquery.tables.create e bigquery.tables.getData:

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

Os seguintes papéis predefinidos do Cloud IAM incluem permissões bigquery.jobs.create:

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

Além disso, se o usuário tiver permissões bigquery.datasets.create e criar um conjunto de dados, ele receberá acesso bigquery.dataOwner ao conjunto. O acesso bigquery.dataOwner permite que o usuário crie 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 os papéis e permissões do Cloud IAM no BigQuery, consulte Papéis e permissões predefinidos.

Permissões do Google 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 para 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 Google 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 Google Drive, será preciso adicionar o escopo do OAuth para o Google Drive (https://www.googleapis.com/auth/drive) a essa 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:

  • Usando o Console do Cloud ou a IU da Web clássica do BigQuery
  • Usando o comando mk da ferramenta de linha de comando
  • Criando um ExternalDataConfiguration ao usar o método de API tables.insert
  • Usando as bibliotecas de cliente

Para consultar uma fonte de dados externa usando uma tabela permanente, crie a 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 os controles de acesso no nível do conjunto de dados para compartilhá-la com outras pessoas que também tenham acesso à fonte de dados externa subjacente.

Existem três maneiras de especificar informações de esquema ao criar uma tabela externa permanente no BigQuery:

  • Se você estiver usando o método da API tables.insert para criar uma tabela externa permanente, criará um recurso de tabela que inclui uma definição de esquema e uma ExternalDataConfiguration. Defina o parâmetro autodetect como true para ativar a autodetecção do esquema para fontes de dados compatíveis.
  • Se você está usando a CLI para criar uma tabela externa permanente, é possível usar um arquivo de definição de tabela, criar e usar seu próprio arquivo de esquema ou inserir o esquema in-line (na linha de comando). Ao criar esse arquivo, é possível ativar a detecção automática do esquema para fontes de dados compatíveis.
  • Se você está usando o console ou a IU clássica da Web do BigQuery para criar uma tabela externa permanente, insira o esquema da tabela manualmente ou use a detecção automática de esquemas para fontes de dados compatíveis.

Para criar uma tabela externa:

Console

  1. Abra a IU da Web do BigQuery no Console do Cloud.
    Acesse o Console do Cloud

  2. Na seção Recursos do painel de navegação, expanda o projeto e selecione um conjunto de dados. Clique em Criar tabela do lado direito da janela.

    Criar tabela

  3. 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 seus dados. Os formatos válidos para dados do Google Drive incluem:

      • Valores separados por vírgulas (CSV)
      • JSON delimitado por nova linha
      • Avro
      • Planilhas Google
  4. (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.

  5. Na página Criar tabela, na seção Destino: *em Nome do conjunto de dados, escolha o conjunto apropriado e, no campo Nome da tabela, insira o nome da tabela que você está criando no BigQuery.

    Selecionar conjunto de dados

    • Verifique se o Tipo de tabela está definido como Tabela externa.
  6. 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 esquemas. A detecção automática não está disponível para exportações do Datastore, do Cloud Firestore e de arquivos Avro. 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: é possível visualizar o esquema de uma tabela existente no formato JSON inserindo o seguinte comando da CLI: bq show --format=prettyjson dataset.table.
      • Use Adicionar campo para inserir manualmente o esquema.
  7. Clique em Criar tabela.

  8. 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 o download, salvar e exportar dados para mais informações.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acesse a IU da Web do BigQuery

  2. No painel de navegação, passe o cursor do mouse sobre um conjunto de dados, clique no ícone de seta para baixo imagem do ícone de seta para baixo e selecione Criar nova tabela.

  3. Na página Criar tabela, na seção Dados de origem:

    • Em Local, selecione Google Drive e no campo fonte, digite o URI do Google Drive. Lembre-se que caracteres curinga não são compatíveis com os URIs do Google Drive.
    • Em Formato do arquivo, selecione o formato dos seus dados. Os formatos válidos para dados do Google Drive incluem:

      • Valores separados por vírgulas (CSV)
      • JSON (delimitado por nova linha)
      • Avro
      • Planilhas Google
  4. Na página Criar tabela, na seção Tabela de destino:

    • Em Nome da tabela, escolha o conjunto de dados apropriado e, no campo correspondente, insira o nome da tabela permanente a ser criada no BigQuery.
    • Verifique se o Tipo de tabela está definido como Tabela externa.
  5. Na seção Esquema, insira as informações:

    • Em arquivos JSON ou CSV, marque a opção Detectar automaticamente para ativar a detecção automática de esquemas. No momento, esse tipo de detecção na IU da Web não é compatível com o Planilhas Google (embora esteja disponível usando a CLI e a API). Além disso, a detecção automática não está disponível para fontes de dados Avro. As informações dos esquemas são recuperadas automaticamente dos arquivos Avro.

    • Em arquivos CSV, JSON ou do Planilhas Google, é possível inserir informações de esquema manualmente:

      • Clique em Editar como texto e insira o esquema da tabela no formato JSON
      • Use Adicionar campo para inserir manualmente o esquema
  6. Selecione os itens aplicáveis na seção Opções e clique em Criar tabela.

  7. Selecione sua conta e clique em Permitir para que as ferramentas de cliente do BigQuery possam acessar o Google Drive.

    Caixa de diálogo de acesso às ferramentas do cliente

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 o download, salvar e exportar dados para mais informações.

CLI

Crie uma tabela na ferramenta de linha de comando do BigQuery usando o comando bq mk. Ao usar a CLI para criar uma tabela vinculada a uma fonte de dados externa, é possível identificar o esquema dela 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 Google 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 para o 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 chamado 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 formato field:data_type,field:data_type.
  • source_format é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • drive_uri é o URI do Google 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 Google armazenado no Google 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 ou GOOGLE_SHEETS.
  • drive_uri é o URI do Google 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 Google 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 o download, salvar e exportar dados para mais informações.

API

Crie uma ExternalDataConfiguration quando utilizar o método de API tables.insert. Especifique a propriedade schema ou configure a propriedade autodetect como true para permitir a detecção automática de esquemas de fontes de dados compatíveis.

Python

Antes de testar essa 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 para Python.

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"
)  # Optionally set range of the sheet to query from.
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an API request.

# Example query to find states starting with "W".
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)
query_job = client.query(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(query_job)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

Como consultar dados do Google 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. Na CLI e na API do BigQuery, é permitido consultar uma fonte de dados externa usando uma tabela temporária.

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, na sigla em inglês).

Permissões exigidas

Ao consultar dados externos no Google 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 contendo uma tabela que aponte 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 Cloud IAM incluem permissões bigquery.tables.getData:

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

Os seguintes papéis predefinidos do Cloud IAM incluem permissões bigquery.jobs.create:

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

Além disso, se o usuário tiver permissões bigquery.datasets.create e criar um conjunto de dados, ele receberá acesso bigquery.dataOwner ao conjunto. 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 os papéis e permissões do Cloud IAM no BigQuery, consulte Papéis e permissões predefinidos.

Permissões do Google 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.

Criação e consulta de tabela temporária

É possível criar e consultar uma tabela temporária vinculada a uma fonte de dados externa usando a CLI, a API ou as bibliotecas de cliente.

CLI

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 CLI para consultar uma tabela temporária vinculada a uma fonte de dados externa, é possível identificar o esquema dela 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 para o 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 do esquema in-line no formato field:data_type,field:data_type.
  • source_format é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • drive_uri é o URI do Google 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 Google 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_format=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_format é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • drive_uri é o URI do Google 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 Google 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

Python

Antes de testar essa 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 para Python.

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")

# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"
)  # Optionally set range of the sheet to query from.
table_id = "us_states"
job_config = bigquery.QueryJobConfig()
job_config.table_definitions = {table_id: external_config}

# Example query to find states starting with "W".
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)
query_job = client.query(sql, job_config=job_config)  # Make an API request.

# Wait for the query to complete.
w_states = list(query_job)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

A pseudocoluna _FILE_NAME

Tabelas baseadas em fontes de dados externas fornecem uma pseudocoluna denominada _FILE_NAME. Essa coluna contém o caminho totalmente qualificado para o arquivo ao qual a linha pertence. Esta coluna só está disponível 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 será necessária se você usar o Cloud Shell ou configurar 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.