Consultar dados do Drive

Neste documento, descrevemos como consultar dados armazenados em uma tabela externa do Google Drive.

O BigQuery aceita consultas de arquivos pessoais do Google Drive e arquivos compartilhados. Confira mais informações em Treinamento e ajuda do Google Drive.

É possível consultar os dados do Drive em uma tabela externa permanente ou tabela externa temporária que é criada quando você executa a consulta.

Funções exigidas

Para consultar tabelas externas do Drive, verifique se você tem os seguintes papéis:

  • Leitor de dados do BigQuery (roles/bigquery.dataViewer)
  • Usuário do BigQuery (roles/bigquery.user)

Dependendo das suas permissões, é possível conceder esses papéis a você mesmo ou pedir ao administrador para concedê-los. Para mais informações sobre como conceder papéis, consulte Como visualizar os papéis atribuíveis em recursos.

Para conferir as permissões exatas do BigQuery necessárias para consultar tabelas externas, expanda a seção Permissões necessárias:

Permissões necessárias

Essas permissões também podem ser concedidas com papéis personalizados ou outros papéis 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. Acesse Contas de serviço para saber mais sobre esse tipo de conta do Compute Engine.

Consultar dados do Drive usando tabelas externas permanentes

Depois de criar uma tabela externa do Drive, você poderá consultá-la usando a sintaxe do Google SQL, como se fosse uma tabela padrão do BigQuery. Por exemplo, SELECT field1, field2 FROM mydataset.my_drive_table;.

Consultar dados do Drive usando tabelas temporárias

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).

Para consultar uma fonte de dados externa sem criar uma tabela permanente, forneça uma definição de tabela para a tabela temporária e use-a em um comando ou uma chamada para consultar a tabela temporária. É possível fornecer a definição da tabela de uma destas maneiras:

O arquivo de definição de tabela ou esquema fornecido é usado para criar a tabela externa temporária, e a consulta será executada nela.

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.

Criar e consultar tabelas temporárias

É 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 formato FIELD:DATA_TYPE,FIELD:DATA_TYPE;
  • SOURCE_FORMAT é CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_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 ou GOOGLE_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

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.
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 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. 

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());
    }
  }
}

Limitações

Uma consulta do BigQuery pode sobrecarregar o Planilhas Google, 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. Para mais informações, consulte Limitações de tabela externa.

A seguir