Esecuzione di 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 sui file di Drive personali e sui file condivisi. Per saperne di più su Drive, consulta la sezione Formazione e assistenza 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:

  • 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 assegnarli a te. Per saperne di più 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 sui 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 di Compute Engine, puoi specificare un elenco di ambiti per l'istanza. Gli ambiti controllano l'accesso dell'istanza ai Google Cloud prodotti, incluso Drive. Le applicazioni in esecuzione sulla VM usano il service account per chiamare le Google Cloud API.

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

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

Esecuzione di 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;.

Esecuzione di 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 sui dati esterni o per processi di estrazione, trasformazione e caricamento (ETL) processi.

Per eseguire query su un'origine dati esterna senza creare una tabella permanente, fornisci una definizione della tabella temporanea e poi la utilizzi 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 sulla tabella esterna temporanea.

Quando utilizzi una tabella esterna temporanea, non crei una tabella in uno dei tuoi set di dati BigQuery. Poiché la tabella non è archiviata in modo permanente in un set di dati, non può essere condivisa con altri utenti.

Creazione ed esecuzione di 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

Esegui 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 (memorizzato sulla macchina locale)
  • Una definizione dello schema in linea
  • Un file di schema JSON (memorizzato sulla 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 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 macchina locale.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il seguente comando crea ed 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 dello 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 è il tuo URI di Drive.
  • QUERY è la query che stai inviando alla tabella temporanea.

Ad esempio, il seguente comando crea ed 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 query su una tabella temporanea collegata all'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 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 ed esegue query su una tabella temporanea denominata sales collegata a un file CSV archiviato in Drive utilizzando il /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 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'APIPython di BigQuery.

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 Java nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell' API Java di BigQuery.

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

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

Questo può essere un errore transitorio che può essere corretto rieseguendo la query. Se l'errore persiste dopo aver rieseguito una query, valuta la possibilità di semplificare il foglio di lavoro, ad esempio riducendo al minimo l'utilizzo delle formule. Per saperne di più, consulta Limitazioni delle tabelle esterne.

Error string: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials

Per risolvere questo errore:

  • Verifica di disporre dell'accesso come visualizzatore 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 di aver concesso a te o al account di servizio che esegue la query i ruoli necessari per eseguire query sulle tabelle esterne di Google Drive.

Passaggi successivi