Esegui query su tabelle esterne 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 di Drive personali sia su quelli condivisi. Per saperne di più su Drive, consulta Formazione e 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 Limitazioni delle tabelle esterne.

Ruoli obbligatori

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

  • BigQuery Data Viewer (roles/bigquery.dataViewer)
  • Utente BigQuery (roles/bigquery.user)

A seconda delle tue autorizzazioni, puoi concederti questi ruoli o chiedere all'amministratore di concederteli. Per ulteriori informazioni sulla concessione dei ruoli, consulta Visualizzazione dei ruoli assegnabili sulle risorse.

Per vedere quali sono esattamente le autorizzazioni BigQuery richieste per eseguire query sulle tabelle esterne, espandi la sezione Autorizzazioni obbligatorie:

Autorizzazioni obbligatorie

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

Autorizzazioni di Drive

Come minimo, per eseguire query su dati esterni in Drive devi disporre dell'accesso View al file di Drive collegato alla tabella esterna.

Ambiti per le istanze di 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, incluso Drive. Le applicazioni in esecuzione sulla VM utilizzano il service account per chiamare le API di Google Cloud .

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

Per informazioni sull'applicazione degli ambiti a un'istanza Compute Engine, consulta Modifica del account di servizio e degli ambiti di accesso per un'istanza. Per ulteriori informazioni sui service account di Compute Engine, consulta Service account.

Esegui query sui dati di Drive utilizzando tabelle esterne permanenti

Dopo aver creato una tabella esterna di Drive, puoi eseguire query utilizzando la sintassi 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 ad hoc una tantum su dati esterni o per processi di estrazione, trasformazione e caricamento (ETL).

Per eseguire query su un'origine dati esterna senza creare una tabella permanente, fornisci una definizione di tabella per la tabella temporanea, quindi utilizza questa definizione di tabella in un comando o 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, su cui viene eseguita la query.

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.

Crea ed esegui query su tabelle temporanee

Puoi creare ed 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

Puoi eseguire 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 (archiviato sulla tua macchina locale)
  • Una definizione dello schema in linea
  • Un file di schema JSON (archiviato sul tuo computer 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 località. Il flag --location è facoltativo.
  • TABLE è il nome della tabella temporanea che stai creando.
  • DEFINITION_FILE è il percorso del file di definizione della tabella sulla tua macchina locale.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il seguente comando crea ed esegue una 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 una 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 in linea nel formato FIELD:DATA_TYPE,FIELD:DATA_TYPE.
  • SOURCE_FORMAT è CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • DRIVE_URI è l'URI di Drive.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il seguente comando crea ed esegue una query su una tabella temporanea denominata sales collegata a un file CSV archiviato su Drive con la seguente definizione di 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 alla tua origine dati esterna utilizzando un file di 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 di schema JSON sulla tua macchina locale.
  • SOURCE_FILE è CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • DRIVE_URI è l'URI di Drive.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue query su una tabella temporanea denominata sales collegata a un file CSV archiviato in Drive utilizzando il file di 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 di Python nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, 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 di configurazione di Java nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configura 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.

Può trattarsi di un errore temporaneo che può essere risolto eseguendo nuovamente la query. Se l'errore persiste dopo aver eseguito nuovamente la query, prova a semplificare il foglio di lavoro, ad esempio riducendo al minimo l'utilizzo delle formule. Per saperne di più, consulta le limitazioni delle tabelle esterne.

Stringa di errore: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials

Per risolvere questo errore:

  • Verifica di avere accesso in visualizzazione al file di Drive collegato alla tabella esterna.
  • Se la versione dello strumento a riga di comando bq è 2.1.12 o precedente, utilizza il flag --enable-gdrive-access.
  • Verifica che a te o al account di servizio che esegue la query siano stati concessi i ruoli necessari per eseguire query sulle tabelle esterne di Google Drive.

Passaggi successivi