Criar tabelas externas no Google Drive

Neste documento, descrevemos como criar uma tabela externa usando dados armazenados no Google Drive.

O BigQuery é compatível com tabelas externas em arquivos pessoais e compartilhados. Veja mais informações em Treinamento e ajuda do Drive.

Você pode criar tabelas externas em arquivos no Drive com os seguintes formatos:

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

Antes de começar

Antes de criar uma tabela externa, colete algumas informações e verifique se você tem permissão para criar a tabela.

Recuperar URIs do Drive

Para criar uma tabela externa para uma fonte de dados do Google Drive, você precisa fornecer o URI do Drive. É possível recuperar o URI do Drive diretamente no URL dos dados do Drive:

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 aos dados hospedados no Drive exige um escopo adicional do OAuth. 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 Google 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 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.

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.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/cloud-platform",
    ]
)

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

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.

GoogleCredentials credentials =
    ServiceAccountCredentials.getApplicationDefault()
        .createScoped(
            ImmutableSet.of(
                "https://www.googleapis.com/auth/bigquery",
                "https://www.googleapis.com/auth/drive"));

// Initialize client that will be used to send requests. This client only needs to be created
// once, and can be reused for multiple requests.
BigQuery bigquery =
    BigQueryOptions.newBuilder().setCredentials(credentials).build().getService();

Funções exigidas

Para criar uma tabela externa, você precisa da permissão bigquery.tables.create do BigQuery Identity and Access Management (IAM).

Cada um dos seguintes papéis predefinidos do Identity and Access Management inclui esta permissão:

Editor de dados do BigQuery (roles/bigquery.dataEditor)
Proprietário de dados do BigQuery (roles/bigquery.dataOwner)
Administrador do BigQuery (roles/bigquery.admin)

Se você não for um principal em qualquer um desses papéis, peça ao administrador para conceder acesso a você ou criar a tabela externa para você.

Para mais informações sobre os papéis e as permissões do Identity and Access Management no BigQuery, consulte Papéis e permissões predefinidos.

Criar tabelas externas

É possível criar uma tabela permanente vinculada à sua fonte de dados externa da seguinte forma:

Use o console do Google Cloud
use o comando mk da ferramenta de linha de comando bq;
Criando um ExternalDataConfiguration ao usar o método de API tables.insert
Como usar bibliotecas de cliente

Para criar uma tabela externa:

Console

No Console do Google Cloud, abra a página do BigQuery.

Ir para o 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
  Observação: no Console do Google Cloud, a escolha do backup do Avro ou Datastore oculta a opção Detectar automaticamente porque esses tipos de arquivo são autodescritivos.
(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: é possível ver o esquema de uma tabela atual no formato JSON ao digitar o seguinte comando na ferramenta de linha de comando bq: bq show --format=prettyjson DATASET.TABLE.
  - Use Adicionar campo para inserir manualmente o esquema.
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 padrão do BigQuery, sujeita às limitações em 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 formato FIELD:DATA_TYPE,FIELD:DATA_TYPE;
SOURCE_FORMAT é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_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 ou GOOGLE_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 padrão do BigQuery, sujeita às limitações das fontes de dados externas.

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

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

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.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# 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]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
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)

results = client.query_and_wait(sql)  # Make an API request.

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

Java

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

// Sample to queries an external data source using a permanent table
public class QueryExternalSheetsPerm {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery =
          BigQueryOptions.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Consultar tabelas externas

Para mais informações, consulte Consultar dados do Drive.

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.