Esta página foi traduzida pela API Cloud Translation.

Crie tabelas externas do Google Drive

Este documento descreve como criar uma tabela externa sobre dados armazenados no Google Drive.

O BigQuery suporta tabelas externas em ficheiros do Drive pessoais e ficheiros partilhados. Para mais informações sobre o Drive, consulte a formação e ajuda do Drive.

Pode criar tabelas externas sobre ficheiros no Drive que tenham os seguintes formatos:

Valores separados por vírgulas (.csv)
JSON delimitado por Newline
Avro
Google Sheets

Antes de começar

Antes de criar uma tabela externa, reúna algumas informações e certifique-se de que tem autorização para criar a tabela.

Obtenha URIs do Drive

Para criar uma tabela externa para uma origem de dados do Google Drive, tem de fornecer o URI do Drive. Pode obter o URI do Drive diretamente a partir do URL dos seus dados do Drive:

Formato do URI

https://docs.google.com/spreadsheets/d/FILE_ID

ou
https://drive.google.com/open?id=FILE_ID

onde FILE_ID é o ID alfanumérico do seu ficheiro do Drive.

Autentique e ative o acesso ao Drive

O acesso aos dados alojados no Drive requer um âmbito do OAuth adicional. Para autenticar no BigQuery e ativar o acesso ao Drive, faça o seguinte:

Consola

Siga os passos de autenticação baseados na Web quando criar uma tabela externa na Google Cloud consola. Quando lhe for pedido, clique em Permitir para conceder às ferramentas de cliente do BigQuery acesso ao Drive.

gcloud

In the Google Cloud console, activate Cloud Shell.

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.
Introduza o seguinte comando para garantir que tem a versão mais recente da CLI Google Cloud.
```
gcloud components update
```
Introduza o seguinte comando para fazer a autenticação com o Drive.
```
gcloud auth login --enable-gdrive-access
```

API

Peça o âmbito do OAuth adequado para o Drive, além do âmbito do BigQuery:

Inicie sessão executando o comando gcloud auth login --enable-gdrive-access.
Obtenha o token de acesso OAuth com o âmbito do Drive que é usado para a sua API executando o comando gcloud auth print-access-token.

Python

Crie um ID de cliente OAuth.
Configure as Credenciais padrão da aplicação (ADC) no seu ambiente local com os âmbitos necessários através dos seguintes passos:
1. Instale a CLI gcloud e, em seguida, inicialize-a executando o seguinte comando:
```
gcloud init
```
2. Crie credenciais de autenticação local para a sua Conta Google:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  Substitua CLIENT_ID_FILE pelo ficheiro que contém o seu ID de cliente OAuth.
  
  Para mais informações, consulte o artigo Credenciais do utilizador fornecidas através da CLI gcloud.

Java

Crie um ID de cliente OAuth.
Configure as Credenciais padrão da aplicação (ADC) no seu ambiente local com os âmbitos necessários através dos seguintes passos:
1. Instale a CLI gcloud e, em seguida, inicialize-a executando o seguinte comando:
```
gcloud init
```
2. Crie credenciais de autenticação local para a sua Conta Google:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  Substitua CLIENT_ID_FILE pelo ficheiro que contém o seu ID de cliente OAuth.
  
  Para mais informações, consulte o artigo Credenciais do utilizador fornecidas através da CLI gcloud.

Funções necessárias

Para criar uma tabela externa, precisa da autorização de gestão de identidade e de acesso (IAM) do BigQuery.bigquery.tables.create

Cada uma das seguintes funções de gestão de identidade e acesso predefinidas inclui esta autorizaçã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 não for um principal em nenhuma destas funções, peça ao seu administrador para lhe conceder acesso ou criar a tabela externa por si.

Para mais informações sobre as funções e as autorizações de gestão de identidades e acessos no BigQuery, consulte o artigo Funções e autorizações predefinidas.

Crie tabelas externas

Pode criar uma tabela permanente associada à sua origem de dados externa:

Usar a Google Cloud consola
Usar o comando mk da ferramenta de linhas de comando bq
Criar um ExternalDataConfiguration quando usa o método da API tables.insert
Usar as bibliotecas de cliente

Para criar uma tabela externa:

Consola

Na Google Cloud consola, abra a página do BigQuery.

Aceda ao BigQuery

No painel Explorador, 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 secção Origem:
- Em Criar tabela a partir de, selecione Drive.
- No campo Selecionar URI do Drive, introduza o URI do Drive. Tenha em atenção que os carateres universais não são suportados para URIs do Drive.
- Em Formato de ficheiro, selecione o formato dos seus dados. Os formatos válidos para dados do Drive incluem:
  - Valores separados por vírgulas (.csv)
  - JSON delimitado por Newline
  - Avro
  - Sheets
  Nota: na consola Google Cloud , a escolha de Avro ou Datastore para a cópia de segurança oculta a opção Detetar automaticamente, porque estes tipos de ficheiros são autodescritivos.
(Opcional) Se escolher o Sheets, na caixa Intervalo da página (opcional), especifique a página e o intervalo de células a consultar. Pode especificar um nome de folha ou pode especificar sheet_name!top_left_cell_id:bottom_right_cell_id para um intervalo de células; por exemplo, "Folha1!A1:B20". Se o Intervalo da folha não for especificado, é usada a primeira folha no ficheiro.
Na página Criar tabela, na secção Destino:
- Para Nome do conjunto de dados, escolha o conjunto de dados adequado e, no campo Nome da tabela, introduza o nome da tabela que está a criar no BigQuery.
- Verifique se o Tipo de tabela está definido como Tabela externa.
Na secção Esquema, introduza a definição do esquema.
- Para ficheiros JSON ou CSV, pode selecionar a opção Deteção automática para ativar a deteção automática do esquema. A deteção automática não está disponível para exportações do Datastore, exportações do Firestore e ficheiros Avro. As informações do esquema para estes tipos de ficheiros são obtidas automaticamente dos dados de origem autodescritivos.
- Introduza as informações do esquema manualmente:
  - Ativando a opção Editar como texto e introduzindo o esquema de tabela como uma matriz JSON. Nota: pode ver o esquema de uma tabela existente no formato JSON introduzindo o seguinte comando na ferramenta de linhas de comando bq: bq show --format=prettyjson DATASET.TABLE.
  - Usando a opção Adicionar campo para introduzir manualmente o esquema.
Clique em Criar tabela.
Se necessário, selecione a sua conta e, em seguida, clique em Permitir para conceder às ferramentas de cliente do BigQuery acesso ao Drive.

Em seguida, pode executar uma consulta na tabela como se fosse uma tabela padrão do BigQuery, sujeita às limitações nas origens de dados externas.

Após a conclusão da consulta, pode transferir os resultados como CSV ou JSON, guardar os resultados como uma tabela ou guardar os resultados no Sheets. Consulte o artigo Transfira, guarde e exporte dados para mais informações.

bq

Cria uma tabela na ferramenta de linhas de comando bq através do comando bq mk. Quando usa a ferramenta de linhas de comando bq para criar uma tabela associada a uma origem de dados externa, pode identificar o esquema da tabela através do seguinte:

Um ficheiro de definição de tabela (armazenado no seu computador local)
Uma definição de esquema inline
Um ficheiro de esquema JSON (armazenado no seu computador local)

Para criar uma tabela permanente associada à sua origem de dados do Drive através de um ficheiro de definição de tabela, introduza o seguinte comando.

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

Onde:

DEFINITION_FILE é o caminho para o ficheiro de definição da tabela na sua máquina local.
DATASET é o nome do conjunto de dados que contém a tabela.
TABLE é o nome da tabela que está a criar.

Por exemplo, o comando seguinte cria uma tabela permanente denominada mytable usando um ficheiro de definição de tabela denominado mytable_def.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

Para criar uma tabela permanente associada à sua origem de dados externa através de uma definição de esquema inline, introduza o seguinte comando.

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Onde:

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 seu URI do Drive.
DATASET é o nome do conjunto de dados que contém a tabela.
TABLE é o nome da tabela que está a criar.

Por exemplo, o comando seguinte cria uma tabela permanente denominada sales associada a um ficheiro do Sheets 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 associada à sua origem de dados externa através de um ficheiro de esquema JSON, introduza o seguinte comando.

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Onde:

SCHEMA_FILE é o caminho para o ficheiro de esquema JSON no seu computador local.
SOURCE_FORMAT é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
DRIVE_URI é o seu URI do Drive.
DATASET é o nome do conjunto de dados que contém a tabela.
TABLE é o nome da tabela que está a criar.

Se o ficheiro de definição de tabela contiver configuração específica do Sheets, pode ignorar as linhas iniciais e especificar um intervalo de páginas definido.

O exemplo seguinte cria uma tabela denominada sales associada a um ficheiro CSV armazenado no Drive através do /tmp/sales_schema.jsonficheiro de esquema.

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

Depois de criar a tabela permanente, pode executar uma consulta na tabela como se fosse uma tabela padrão do BigQuery, sujeita às limitações das origens de dados externas.

API

Crie um ExternalDataConfiguration quando usar o método da API tables.insert. Especifique a propriedade schema ou defina a propriedade autodetect como true para ativar a deteção automática de esquemas para origens de dados suportadas.

Python

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

# 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

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 {

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

      // 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 o artigo Consultar dados do Drive.

A pseudocoluna `_FILE_NAME`

As tabelas baseadas em origens de dados externas fornecem uma pseudocoluna denominada _FILE_NAME. Esta coluna contém o caminho totalmente qualificado para o ficheiro ao qual a linha pertence. Esta coluna está disponível apenas para tabelas que referenciam dados externos armazenados no Cloud Storage e no Google Drive.

O nome da coluna _FILE_NAME está reservado, o que significa que não pode criar uma coluna com esse nome em nenhuma das suas tabelas.

Crie tabelas externas do Google Drive

Antes de começar

Obtenha URIs do Drive

Autentique e ative o acesso ao Drive

Consola

gcloud

API

Python

Java

Funções necessárias

Crie tabelas externas

Consola

bq

API

Python

Java

Consultar tabelas externas

A pseudocoluna _FILE_NAME

A pseudocoluna `_FILE_NAME`