Esegui query sui dati di Drive

Questo documento descrive come eseguire query sui dati archiviati in una tabella esterna di Google Drive.

BigQuery supporta le query sia sui file personali di Drive sia su quelli condivisi. Per saperne di più su Drive, consulta la formazione e la guida di Google Drive.

Puoi eseguire query sui dati di Drive da un una tabella esterna permanente o da una tabella esterna temporanea che crei quando esegui la query.

Ruoli obbligatori

Per eseguire query sulle tabelle esterne di Drive, assicurati di disporre dei seguenti ruoli:

  • Visualizzatore dati BigQuery (roles/bigquery.dataViewer)
  • Utente BigQuery (roles/bigquery.user)

In base alle tue autorizzazioni, puoi concediti questi ruoli o chiedi all'amministratore per concederteli. Per ulteriori informazioni sulla concessione dei ruoli, consulta Visualizzazione dei ruoli assegnabili sulle risorse.

Per visualizzare le autorizzazioni BigQuery esatte necessarie per eseguire query sulle tabelle esterne, espandi la sezione Autorizzazioni richieste:

Autorizzazioni obbligatorie

Potresti anche riuscire a ottenere queste autorizzazioni con i ruoli personalizzati. o altri ruoli predefiniti.

Autorizzazioni di Drive

Come minimo, per eseguire query su dati esterni su Drive devi avere concesso a View l'accesso al file di Drive collegato alla tabella esterna.

Ambiti per le istanze Compute Engine

Quando crei un'istanza Compute Engine, puoi specificare un elenco di ambiti per l'istanza. Gli ambiti controllano l'accesso dell'istanza a Google Cloud prodotti, tra cui Drive. Le applicazioni in esecuzione sulla VM utilizzano l'account di servizio per le chiamate alle API Google Cloud.

Se configuri un'istanza Compute Engine in modo che venga eseguita come account di servizio, e questo account di servizio accede a una tabella esterna collegata a un'origine dati Drive, devi aggiungere all'istanza il ambito OAuth per Drive (https://www.googleapis.com/auth/drive.readonly).

Per informazioni sull'applicazione di ambiti a un'istanza Compute Engine, vedi Modifica dell'account di servizio e degli ambiti di accesso per un'istanza. Per ulteriori informazioni sugli account di servizio Compute Engine, consulta Account di servizio.

Esegui query sui dati di Drive utilizzando tabelle esterne permanenti

Dopo aver creato una tabella esterna di Drive, puoi eseguire una query utilizzando Sintassi GoogleSQL, come se era una tabella BigQuery standard. Ad esempio, SELECT field1, field2 FROM mydataset.my_drive_table;.

Esegui query sui dati di Drive utilizzando tabelle temporanee

L'esecuzione di query su un'origine dati esterna utilizzando una tabella temporanea è utile per query una tantum e ad hoc su dati esterni o per processi ETL (estrazione, trasformazione e caricamento).

Per eseguire query su un'origine dati esterna senza creare una tabella permanente, fornisci una definizione della tabella per la tabella temporanea e poi utilizzala in un comando o in una chiamata per eseguire query sulla tabella temporanea. Puoi fornire la definizione della tabella in uno dei seguenti modi:

Il file di definizione della tabella o lo schema fornito viene utilizzato per creare la tabella esterna temporanea e la query viene eseguita su questa tabella.

Quando utilizzi una tabella esterna temporanea, non ne crei una in una delle set di dati BigQuery. Poiché la tabella non viene memorizzata in modo permanente in un set di dati, non può essere condivisa con altri.

Creare ed eseguire query su tabelle temporanee

Puoi creare ed eseguire query su una tabella temporanea collegata a un'origine dati esterna mediante lo strumento a riga di comando bq, l'API o le librerie client.

bq

Per eseguire query su una tabella temporanea collegata a un'origine dati esterna, puoi utilizzare Comando bq query con il flag --external_table_definition. Quando utilizzi lo strumento a riga di comando bq per eseguire query su una tabella temporanea collegata a un'origine dati esterna, puoi identificare lo schema della tabella utilizzando:

  • Un file di definizione della tabella (archiviato su dalla macchina locale)
  • Definizione di uno schema in linea
  • Un file dello schema JSON (salvato nella macchina locale)

Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando un file di definizione della tabella, inserisci il seguente comando.

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

Dove:

  • LOCATION è la tua posizione. Il flag --location è facoltativo.
  • TABLE è il nome della tabella temporanea che stai creando.
  • DEFINITION_FILE è il percorso del file di definizione della tabella sul computer locale.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il comando seguente crea e esegue query su una tabella temporanea denominata sales utilizzando un file di definizione della tabella denominato sales_def.

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

Per eseguire query su una tabella temporanea collegata all'origine dati esterna utilizzando una definizione di schema in linea, inserisci il seguente comando.

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

Dove:

  • LOCATION è la tua località. Il flag --location è facoltativo.
  • TABLE è il nome della tabella temporanea che stai creando.
  • SCHEMA è la definizione dello schema incorporato nel formato FIELD:DATA_TYPE,FIELD:DATA_TYPE.
  • SOURCE_FORMAT è CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • DRIVE_URI è il tuo URI di Drive.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue una query su una tabella temporanea denominato sales collegato a un file CSV archiviato in Drive con quanto segue definizione schema: 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'

Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando un file schema JSON, inserisci il seguente comando.

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

Dove:

  • LOCATION è la tua località. Il flag --location è facoltativo.
  • SCHEMA_FILE è il percorso del file dello schema JSON sulla tua macchina locale.
  • SOURCE_FILE è CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • DRIVE_URI è il tuo URI di Drive.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue una query su una tabella temporanea denominato sales collegato a un file CSV archiviato in Drive utilizzando /tmp/sales_schema.json file di schema.

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

Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Prima di provare questo esempio, segui le istruzioni per la configurazione di Java nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare l'autenticazione per le librerie client. 

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

Limitazioni

Una query BigQuery può sovraccaricare Fogli Google, generando un errore come Resources exceeded during query execution: Google Sheets service overloaded. Valuta la possibilità di semplificare il foglio di lavoro, ad esempio riducendo al minimo l'uso delle formule. Per ulteriori informazioni, consulta la tabella esterna limitazioni.

Passaggi successivi