Présentation de SQL dans BigQuery

Ce document offre un aperçu des instructions et des dialectes SQL compatibles avec BigQuery.

Présentation

GoogleSQL est un langage de requête structuré (SQL) conforme à la norme ANSI, qui inclut les types d'instructions compatibles suivants :

Dialectes BigQuery SQL

BigQuery est compatible avec le dialecte GoogleSQL, mais un ancien dialecte SQL est également disponible. Si vous débutez sur BigQuery, vous devez utiliser le langage GoogleSQL, car il offre un large éventail de fonctionnalités. Par exemple, des fonctionnalités telles que les instructions LDD et LMD ne sont compatibles qu'avec le langage GoogleSQL. L'ancien SQL est conservé pour assurer la rétrocompatibilité. Nous vous recommandons d'effectuer la migration si vous utilisez l'ancien SQL.

Changer le dialecte par défaut

L'interface que vous utilisez pour interroger vos données détermine le dialecte de requête par défaut. Pour basculer d'un dialecte à l'autre :

Console

Le dialecte par défaut de la console Google Cloud est le dialecte GoogleSQL. Pour remplacer le dialecte par l'ancien SQL, procédez comme suit :

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, cliquez sur le bouton Plus > Paramètres de requête.

  3. Dans la section Options avancées, pour Dialecte SQL, cliquez sur Ancien, puis sur Enregistrer. Cela définit l'ancien dialecte SQL comme dialecte à utiliser pour cette requête. Si vous cliquez sur Saisir une nouvelle requête pour rédiger une autre requête, vous devrez à nouveau définir l'option d'ancien SQL.

SQL

Le dialecte SQL par défaut est le dialecte GoogleSQL. Vous pouvez définir le dialecte SQL en incluant le préfixe #standardSQL ou #legacySQL dans votre requête. Ces préfixes de requête ne sont pas sensibles à la casse. Ils doivent précéder la requête et en être séparés par un caractère de retour à la ligne. L'exemple suivant définit le dialecte en ancien SQL et interroge l'ensemble de données de natalité :

  1. Dans Google Cloud Console, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

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

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Le dialecte de requête par défaut de l'outil de ligne de commande bq est l'ancien dialecte SQL. Pour basculer vers le dialecte GoogleSQL, ajoutez l'option --use_legacy_sql=false ou --nouse_legacy_sql à votre instruction de ligne de commande.

Passer au dialecte GoogleSQL

Pour utiliser la syntaxe GoogleSQL dans un job de requête, définissez le paramètre use_legacy_sql sur false.

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

Définissez GoogleSQL comme dialecte par défaut.

Vous pouvez définir le dialecte GoogleSQL comme dialecte par défaut pour l'outil de ligne de commande et le shell interactif en modifiant le fichier de configuration de l'outil de ligne de commande : .bigqueryrc.

Pour plus d'informations sur .bigqueryrc, consultez la section Définir des valeurs par défaut pour les indicateurs spécifiques aux commandes.

Pour définir --use_legacy_sql=false dans .bigqueryrc, procédez comme suit :

  1. Ouvrez .bigqueryrc dans un éditeur de texte. Par défaut, .bigqueryrc devrait figurer dans votre répertoire utilisateur, par exemple $HOME/.bigqueryrc.

  2. Ajoutez ce qui suit au fichier. Cet exemple définit le langage GoogleSQL comme syntaxe par défaut pour les requêtes et pour la commande mk (utilisée lors de la création d'une vue). Si vous avez déjà configuré des valeurs par défaut pour les indicateurs de commande query ou mk, il n'est pas nécessaire d'ajouter à nouveau [query] ou [mk].

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

  3. Enregistrez et fermez le fichier.

  4. Si vous utilisez le shell interactif, vous devez en sortir et la redémarrer pour que les modifications soient appliquées.

Pour plus d'informations sur les options de ligne de commande disponibles, consultez la documentation de référence sur l'outil de ligne de commande bq.

C#

Avant d'essayer cet exemple, suivez les instructions de configuration pour C# du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour C#.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque C# utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, définissez le paramètre UseLegacySql sur 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"]}");
        }
    }
}

Go

Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Go.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque Go utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, définissez la propriété UseLegacySQL dans la configuration de la requête sur 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

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque Java utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, définissez le paramètre useLegacySql sur 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

Avant d'essayer cet exemple, suivez les instructions de configuration pour Node.js du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Node.js.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque cliente Node.js utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, définissez le paramètre useLegacySql sur 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

Avant d'essayer cet exemple, suivez les instructions de configuration pour PHP du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour PHP.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque cliente PHP utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, définissez le paramètre useLegacySql sur 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

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque cliente Python utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, définissez le paramètre use_legacy_sql sur 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

Avant d'essayer cet exemple, suivez les instructions de configuration pour Ruby du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Ruby.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Par défaut, la bibliothèque cliente Ruby utilise le dialecte GoogleSQL.

Passer à l'ancien dialecte SQL

Pour utiliser l'ancienne syntaxe SQL dans une tâche de requête, transmettez l'option legacy_sql: true avec votre requête.

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

Étapes suivantes