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 una tabella esterna permanente o da una tabella esterna temporanea che crei quando esegui la query.

Limitazioni

Per le limitazioni relative alle tabelle esterne, consulta le limitazioni delle tabelle esterne.

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)

A seconda delle tue autorizzazioni, puoi assegnare questi ruoli a te stesso o chiedere all'amministratore di assegnarteli. 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 ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

Autorizzazioni di Drive

Per eseguire query sui dati esterni in Drive, devi disporre almeno del ruolo View per accedere 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 ai prodotti Google Cloud, 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 degli ambiti a un'istanza Compute Engine, consulta Modificare l'account di servizio e gli 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 query utilizzando la sintassi di GoogleSQL, come se fosse 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 crei una tabella in uno dei tuoi set di dati BigQuery. Poiché la tabella non viene archiviata in modo permanente in un set di dati, non può essere condivisa con altri.

Creare tabelle temporanee ed eseguire query su di esse

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

bq

Esegui una query su una tabella temporanea collegata a un'origine dati esterna utilizzando il 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 (salvato sul tuo computer locale)
  • Una definizione di schema in linea
  • Un file dello schema JSON (salvato nella macchina locale)

Per eseguire 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 posizione. Il flag --location è facoltativo.
  • TABLE è il nome della tabella temporanea che stai creando.
  • SCHEMA è la definizione dello schema in linea 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 seguente comando crea e esegue query su una tabella temporanea denominata sales collegata a un file CSV archiviato in Drive con la seguente definizione dello 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 posizione. 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 seguente comando crea e esegue query su una tabella temporanea chiamata sales collegata a un file CSV archiviato in Drive utilizzando il file dello schema /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

Prima di provare questo esempio, segui le istruzioni di configurazione Python riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Python.

Per autenticarti in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare 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 di configurazione Java riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Java.

Per autenticarti in BigQuery, configura le 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());
    }
  }
}

Risoluzione dei problemi

Stringa di errore: Resources exceeded during query execution: Google Sheets service overloaded.

Potrebbe trattarsi di un errore transitorio che può essere corretto eseguendo di nuovo la query. Se l'errore persiste dopo la riesecuzione di una query, valuta la possibilità di semplificare il foglio di lavoro, ad esempio riducendo al minimo l'uso delle formule. Per ulteriori informazioni, consulta limitazioni delle tabelle esterne.

Passaggi successivi