Introduzione a SQL in BigQuery

GoogleSQL in BigQuery ti consente di analizzare set di dati di grandi dimensioni, gestire gli oggetti del database e controllare l'accesso nel tuo data warehouse. I dialetti SQL e i tipi di istruzioni supportati ti consentono di creare in modo efficace query complesse e di utilizzare l'intera suite di funzionalità di manipolazione e amministrazione dei dati in BigQuery.

GoogleSQL è uno Structured Query Language (SQL) conforme ad ANSI che supporta i seguenti tipi di istruzioni:

  • Le istruzioni di query, note anche come istruzioni Data Query Language (DQL), sono il metodo principale per analizzare i dati in BigQuery. Analizzano una o più tabelle o espressioni e restituiscono le righe dei risultati calcolati. Le istruzioni di query possono includere la sintassi pipe.
  • Le istruzioni del linguaggio procedurale sono estensioni procedurali di GoogleSQL che consentono di eseguire più istruzioni SQL in un'unica richiesta. Le istruzioni procedurali possono utilizzare variabili e istruzioni di controllo di flusso e possono avere effetti collaterali.

  • Le istruzioni Data Definition Language (DDL) consentono di creare e modificare oggetti come i seguenti:

    • Set di dati
    • Tabelle, inclusi i relativi schemi e tipi di colonne
    • Cloni e snapshot delle tabelle
    • Visualizzazioni
    • Funzioni
    • Indici
    • Impegni, prenotazioni e assegnazioni di capacità
    • Policy di accesso a livello di riga

  • Le istruzioni DML (Data Manipulation Language) consentono di aggiornare, inserire ed eliminare dati dalle tabelle BigQuery.

  • Le istruzioni Data Control Language (DCL) consentono di controllare le risorse di sistema di BigQuery, come l'accesso e la capacità.

  • Le istruzioni Transaction Control Language (TCL) consentono di gestire le transazioni per le modifiche ai dati.

  • Le istruzioni LOAD e le istruzioni EXPORT ti consentono di gestire i dati in entrata e in uscita da BigQuery.

Dialetti SQL di BigQuery

BigQuery supporta il dialetto GoogleSQL, che è il dialetto consigliato per tutti i nuovi progetti. È disponibile anche un dialetto SQL precedente con alcune limitazioni. Ti consigliamo di eseguire la migrazione da SQL precedente a GoogleSQL.

Modifica del dialetto predefinito

L'interfaccia che utilizzi per eseguire query sui dati determina il dialetto di query predefinito. Per passare a un altro dialetto:

Console

Il dialetto predefinito per la console Google Cloud è GoogleSQL. Per cambiare il dialetto in SQL precedente:

  1. Nella console Google Cloud , vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di query, fai clic su Modifica > Impostazioni query.

  3. Nella sezione Opzioni avanzate, per Dialetto SQL, fai clic su Legacy, quindi su Salva. In questo modo, viene impostata l'opzione SQL precedente per questa query. Quando fai clic su add_box Query SQL per creare una nuova query, devi selezionare di nuovo l'opzione SQL precedente.

SQL

Il dialetto SQL predefinito è GoogleSQL. Puoi impostare il dialetto SQL includendo il prefisso #standardSQL o #legacySQL nella query. Questi prefissi di query non sono sensibili alle maiuscole, devono precedere la query ed essere separati dalla query da un carattere di nuova riga. L'esempio seguente imposta il dialetto su SQL precedente ed esegue una query sul set di dati sulla natalità:

  1. Nella console Google Cloud , vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

    #legacySQL
SELECT
  weight_pounds, state, year, gestation_weeks
FROM
  [bigquery-public-data:samples.natality]
ORDER BY
  weight_pounds DESC
LIMIT
  10;

  3. Fai clic su Esegui.

Per saperne di più su come eseguire le query, consulta Esegui una query interattiva.

bq

Il dialetto di query predefinito nello strumento a riga di comando bq è SQL precedente. Per passare al dialetto GoogleSQL, aggiungi il flag --use_legacy_sql=false o --nouse_legacy_sql all'istruzione della riga di comando.

Passa al dialetto GoogleSQL

Per utilizzare la sintassi GoogleSQL in un job di query, imposta il parametro use_legacy_sql su false.

  bq query \
  --use_legacy_sql=false \
  'SELECT
    word
  FROM
    `bigquery-public-data.samples.shakespeare`'

Imposta GoogleSQL come dialetto predefinito

Puoi impostare GoogleSQL come dialetto predefinito per lo strumento a riga di comando e la shell interattiva modificando il file di configurazione dello strumento a riga di comando: .bigqueryrc.

Per ulteriori informazioni su .bigqueryrc, consulta Impostazione dei valori predefiniti per i flag specifici dei comandi.

Per impostare --use_legacy_sql=false in .bigqueryrc:

  1. Apri .bigqueryrc in un editor di testo. Per impostazione predefinita, .bigqueryrc dovrebbe trovarsi nella directory utente, ad esempio $HOME/.bigqueryrc.

  2. Aggiungi il testo seguente al file. Questo esempio imposta GoogleSQL come sintassi predefinita per le query e per il comando mk (utilizzato quando crei una vista). Se hai già configurato i valori predefiniti per i flag dei comandi query o mk, non è necessario aggiungere di nuovo [query] o [mk].

    [query]
--use_legacy_sql=false
[mk]
--use_legacy_sql=false

  3. Salva e chiudi il file.

  4. Se utilizzi la shell interattiva, devi uscire e riavviarla per applicare le modifiche.

Per informazioni sui flag della riga di comando disponibili, consulta il riferimento allo strumento a riga di comando bq.

C#

Prima di provare questo esempio, segui le istruzioni di configurazione di C# nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery C#.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configura l'autenticazione per le librerie client.

Per impostazione predefinita, la libreria C# utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, imposta il parametro UseLegacySql su true.


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryLegacy
{
    public void QueryLegacy(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string query = @"
            SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013]
            WHERE state = 'TX'
            LIMIT 100";
        BigQueryJob job = client.CreateQueryJob(
            sql: query,
            parameters: null,
            options: new QueryOptions { UseLegacySql = true });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

Vai

Prima di provare questo esempio, segui le istruzioni di configurazione di Go nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Go.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configura l'autenticazione per le librerie client.

Per impostazione predefinita, la libreria client Go utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, imposta la proprietà UseLegacySQL all'interno della configurazione della query su true.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryLegacy demonstrates running a query using Legacy SQL.
func queryLegacy(w io.Writer, projectID, sqlString string) error {
	// projectID := "my-project-id"
	// sqlString = "SELECT 3 as somenum"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	q := client.Query(sqlString)
	q.UseLegacySQL = true

	// Run the query and process the returned row iterator.
	it, err := q.Read(ctx)
	if err != nil {
		return fmt.Errorf("query.Read(): %w", err)
	}
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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.

Per impostazione predefinita, la libreria client Java utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, imposta il parametro useLegacySql su true.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class RunLegacyQuery {

  public static void main(String[] args) {
    runLegacyQuery();
  }

  public static void runLegacyQuery() {
    try {
      // 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.getDefaultInstance().getService();

      // To use legacy SQL syntax, set useLegacySql to true.
      String query =
          "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();

      // Execute the query.
      TableResult result = bigquery.query(queryConfig);

      // Print the results.
      result.iterateAll().forEach(rows -> rows.forEach(row -> System.out.println(row.getValue())));

      System.out.println("Legacy query ran successfully");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Legacy query did not run \n" + e.toString());
    }
  }
}

Node.js

Prima di provare questo esempio, segui le istruzioni di configurazione di Node.js nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configura l'autenticazione per le librerie client.

Per impostazione predefinita, la libreria client Node.js utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, imposta il parametro useLegacySql su true.

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryLegacy() {
  // Queries the U.S. given names dataset for the state of Texas using legacy SQL.

  const query =
    'SELECT word FROM [bigquery-public-data:samples.shakespeare] LIMIT 10;';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    useLegacySql: true,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

PHP

Prima di provare questo esempio, segui le istruzioni di configurazione di PHP nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery PHP.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configura l'autenticazione per le librerie client.

Per impostazione predefinita, la libreria client PHP utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, imposta il parametro useLegacySql su true.

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Query using legacy sql
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 */
function query_legacy(string $projectId): void
{
    $query = 'SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus';

    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $jobConfig = $bigQuery->query($query)->useLegacySql(true);

    $queryResults = $bigQuery->runQuery($jobConfig);

    $i = 0;
    foreach ($queryResults as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, json_encode($value));
        }
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
}

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.

Per impostazione predefinita, la libreria client Python utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, imposta il parametro use_legacy_sql su True.

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = (
    "SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013] "
    'WHERE state = "TX" '
    "LIMIT 100"
)

# Set use_legacy_sql to True to use legacy SQL syntax.
job_config = bigquery.QueryJobConfig(use_legacy_sql=True)

# Start the query and waits for query job to complete, passing in the extra configuration.
results = client.query_and_wait(
    query, job_config=job_config
)  # Make an API request.

print("The query data:")
for row in results:
    print(row)

Ruby

Prima di provare questo esempio, segui le istruzioni di configurazione di Ruby nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Ruby.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, vedi Configura l'autenticazione per le librerie client.

Per impostazione predefinita, la libreria client Ruby utilizza GoogleSQL.

Passa al dialetto SQL precedente

Per utilizzare la sintassi SQL precedente in un job di query, passa l'opzione legacy_sql: true con la query.

require "google/cloud/bigquery"

def query_legacy
  bigquery = Google::Cloud::Bigquery.new
  sql = "SELECT name FROM [bigquery-public-data:usa_names.usa_1910_2013] " \
        "WHERE state = 'TX' " \
        "LIMIT 100"

  results = bigquery.query sql, legacy_sql: true do |config|
    # Location must match that of the dataset(s) referenced in the query.
    config.location = "US"
  end

  results.each do |row|
    puts row.inspect
  end
end

Risolvi i problemi relativi alle query

Per informazioni sulla risoluzione dei problemi durante l'esecuzione delle query, consulta Risolvi i problemi relativi alle query.

Passaggi successivi