Consultar dados do Drive

Este documento descreve como consultar dados armazenados numa tabela externa do Google Drive.

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

Pode consultar dados do Drive a partir de uma tabela externa permanente ou de uma tabela externa temporária que cria quando executa a consulta.

Limitações

Para ver as limitações relacionadas com tabelas externas, consulte as limitações das tabelas externas.

Funções necessárias

Para consultar tabelas externas do Drive, certifique-se de que tem as seguintes funções:

  • Visualizador de dados do BigQuery (roles/bigquery.dataViewer)
  • Utilizador do BigQuery (roles/bigquery.user)

Consoante as suas autorizações, pode atribuir estas funções a si próprio ou pedir ao seu administrador para as atribuir. Para mais informações sobre a concessão de funções, consulte o artigo Ver as funções atribuíveis aos recursos.

Para ver as autorizações exatas do BigQuery necessárias para consultar tabelas externas, expanda a secção Autorizações necessárias:

Autorizações necessárias

Também pode conseguir estas autorizações com funções personalizadas ou outras funções predefinidas.

Autorizações do Drive

No mínimo, para consultar dados externos no Drive, tem de ter acesso View ao ficheiro do Drive associado à tabela externa.

Âmbitos para instâncias do Compute Engine

Quando cria uma instância do Compute Engine, pode especificar uma lista de âmbitos para a instância. Os âmbitos controlam o acesso da instância aos Google Cloud produtos, incluindo o Drive. As aplicações em execução na VM usam a conta de serviço para chamar as Google Cloud APIs.

Se configurar uma instância do Compute Engine para ser executada como uma conta de serviço e essa conta de serviço aceder a uma tabela externa associada a uma origem de dados do Drive, tem de adicionar o âmbito do OAuth para o Drive (https://www.googleapis.com/auth/drive.readonly) à instância.

Para ver informações sobre a aplicação de âmbitos a uma instância do Compute Engine, consulte Alterar a conta de serviço e os âmbitos de acesso de uma instância. Para mais informações sobre as contas de serviço do Compute Engine, consulte o artigo Contas de serviço.

Consulte dados do Drive através de tabelas externas permanentes

Depois de criar uma tabela externa do Drive, pode consultá-la através da sintaxe do GoogleSQL, tal como se fosse uma tabela padrão do BigQuery. Por exemplo, SELECT field1, field2 FROM mydataset.my_drive_table;.

Consulte dados do Drive através de tabelas temporárias

A consulta de uma origem de dados externa através de uma tabela temporária é útil para consultas únicas e ad hoc sobre dados externos ou para processos de extração, transformação e carregamento (ETL).

Para consultar uma origem de dados externa sem criar uma tabela permanente, fornece uma definição de tabela para a tabela temporária e, em seguida, usa essa definição de tabela num comando ou numa chamada para consultar a tabela temporária. Pode fornecer a definição da tabela de qualquer uma das seguintes formas:

O ficheiro de definição da tabela ou o esquema fornecido é usado para criar a tabela externa temporária e a consulta é executada na tabela externa temporária.

Quando usa uma tabela externa temporária, não cria uma tabela num dos seus conjuntos de dados do BigQuery. Uma vez que a tabela não está armazenada permanentemente num conjunto de dados, não pode ser partilhada com outras pessoas.

Crie e consulte tabelas temporárias

Pode criar e consultar uma tabela temporária associada a uma origem de dados externa através da ferramenta de linha de comandos bq, da API ou das bibliotecas de cliente.

bq

Consulta uma tabela temporária associada a uma origem de dados externa através do comando bq query com a flag --external_table_definition. Quando usa a ferramenta de linhas de comando bq para consultar uma tabela temporária 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 consultar uma tabela temporária associada à sua origem de dados externa através de um ficheiro de definição de tabela, introduza o seguinte comando.

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

Onde:

  • LOCATION é a sua localização. A flag --location é opcional.
  • TABLE é o nome da tabela temporária que está a criar.
  • DEFINITION_FILE é o caminho para o ficheiro de definição da tabela na sua máquina local.
  • QUERY é a consulta que está a enviar para a tabela temporária.

Por exemplo, o seguinte comando cria e consulta uma tabela temporária denominada sales através de um ficheiro de definição de tabela denominado sales_def.

bq query \
--external_table_definition=sales::sales_def \
'SELECT
   Region,Total_sales
 FROM
   sales'

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

bq --location=LOCATION query \
--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=DRIVE_URI \
'QUERY'

Onde:

  • LOCATION é a sua localização. A flag --location é opcional.
  • TABLE é o nome da tabela temporária que está a criar.
  • SCHEMA é a definição do esquema inline 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.
  • QUERY é a consulta que está a enviar para a tabela temporária.

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

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \
'QUERY'

Onde:

  • LOCATION é a sua localização. A flag --location é opcional.
  • SCHEMA_FILE é o caminho para o ficheiro de esquema JSON no seu computador local.
  • SOURCE_FILE é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • DRIVE_URI é o seu URI do Drive.
  • QUERY é a consulta que está a enviar para a tabela temporária.

Por exemplo, o seguinte comando cria e consulta uma tabela temporária denominada sales associada a um ficheiro CSV armazenado no Drive através do ficheiro 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 experimentar este exemplo, siga as Pythoninstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Python BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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.
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)

# 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(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)
    )
)

Java

Antes de experimentar este exemplo, siga as Javainstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Java BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

// Sample to queries an external data source using a temporary table
public class QueryExternalSheetsTemp {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    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 WHERE name LIKE 'W%%'", tableName);
    queryExternalSheetsTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsTemp(
      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();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

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

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

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

Resolução de problemas

Error string: Resources exceeded during query execution: Google Sheets service overloaded.

Este pode ser um erro temporário que pode ser corrigido executando novamente a consulta. Se o erro persistir após a nova execução de uma consulta, pondere simplificar a folha de cálculo, por exemplo, minimizando a utilização de fórmulas. Para mais informações, consulte as limitações das tabelas externas.

O que se segue?