SQL-Dialekte wechseln

In diesem Thema wird beschrieben, wie Sie für die Abfrage von BigQuery-Daten den Abfragedialekt festlegen. Sie können entweder Standard-SQL oder Legacy-SQL verwenden.

Die ersten Schritte zum Abfragen von Daten mit der BigQuery-Web-UI sind in der Kurzanleitung zur Verwendung der Web-UI beschrieben.

Informationen zum Einstieg in die Datenabfrage mit dem bq-Befehlszeilentool finden Sie in der Kurzanleitung zum bq-Befehlszeilentool.

Standarddialekt ändern

Anhand der Schnittstelle, die Sie zum Abfragen der Daten verwenden, wird der Standarddialekt für Abfragen festgelegt:

  • In der Cloud Console und den Clientbibliotheken ist Standard-SQL die Standardeinstellung.
  • In der klassischen BigQuery-Web-UI, dem bq-Befehlszeilentool und der REST API ist Legacy-SQL die Standardeinstellung.

So wechseln Sie zu einem anderen Dialekt:

Console

Der Standarddialekt für die Cloud Console ist Standard-SQL. So ändern Sie den Dialekt in Legacy-SQL:

  1. Klicken Sie ggf. auf Neue Abfrage erstellen, um das Fenster mit dem Abfrageeditor zu öffnen.

  2. Klicken Sie unterhalb des Abfrageeditors auf Mehr > Abfrageeinstellungen.

  3. Klicken Sie auf Erweiterte Optionen.

  4. Klicken Sie im Abschnitt Zusätzliche Einstellungen unter SQL-Dialekt auf Legacy. Dadurch wird für diese Abfrage die Option "Legacy-SQL" aktiviert. Wenn Sie auf Neue Abfrage erstellen klicken, um eine neue Abfrage zu erstellen, müssen Sie noch einmal die Option "Legacy-SQL" auswählen.

Klassische UI

Der Standarddialekt für die klassische BigQuery-Web-UI ist Legacy-SQL. So ändern Sie den Dialekt in Standard-SQL:

  1. Klicken Sie in der klassischen Web-UI auf Abfrage erstellen.

  2. Klicken Sie auf Optionen anzeigen.

  3. Entfernen Sie unter SQL-Dialekt bei Legacy-SQL verwenden das Häkchen. Dadurch wird für diese Abfrage die Option "Standard-SQL" aktiviert. Wenn Sie den Abfrageeditor schließen und wieder öffnen, müssen Sie die Option "Legacy-SQL" noch einmal deaktivieren.

bq

Der Standard-Suchanfragendialekt im bq-Befehlszeilentool ist Legacy-SQL. Fügen Sie der Befehlszeilenanweisung das Flag --use_legacy_sql=false oder --nouse_legacy_sql hinzu, um zu Standard-SQL zu wechseln.

Beispiel:

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

C#

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von C# in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery C# API.

Zur Ausführung einer Abfrage wird standardmäßig Standard-SQL verwendet.


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

public class BigQueryQuery
{
    public void Query(
        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 { UseQueryCache = false });
        // Wait for the job to complete.
        job.PollUntilCompleted();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

Legacy-SQL verwenden

Zur Verwendung von Legacy-SQL beim Ausführen von Abfragen legen Sie für den Parameter "UseLegacySql" den Wert "true" fest.


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.PollUntilCompleted();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

Go

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Go in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Go API.

Standardmäßig verwendet die Go-Clientbibliothek Standard-SQL.

import (
	"context"
	"fmt"
	"io"

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

// queryBasic demonstrates issuing a query and reading results.
func queryBasic(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	q := client.Query(
		"SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " +
			"WHERE state = \"TX\" " +
			"LIMIT 100")
	// Location must match that of the dataset(s) referenced in the query.
	q.Location = "US"
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	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
}

Legacy-SQL verwenden

Verwenden Sie in der Abfragekonfiguration das Attribut UseLegacySQL, um zu Legacy-SQL zurückzuwechseln.

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: %v", err)
	}
	defer client.Close()

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

	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

Standardmäßig verwendet die Java-Clientbibliothek Standard-SQL.

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Legacy-SQL verwenden

Legen Sie den Parameter useLegacySql auf true fest, um in einem Abfragejob die Legacy-SQL-Syntax zu verwenden.

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 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

Standardmäßig verwendet die Node.js-Clientbibliothek Standard-SQL.

// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function query() {
  // Queries the U.S. given names dataset for the state of Texas.

  const query = `SELECT name
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 100`;

  // 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',
  };

  // 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));
}

Legacy-SQL verwenden

Legen Sie den Parameter useLegacySql auf true fest, um in einem Abfragejob die Legacy-SQL-Syntax zu verwenden.

// 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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von PHP in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery PHP API.

Standardmäßig verwendet die PHP-Clientbibliothek Standard-SQL.

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $query = 'SELECT id, view_count FROM `bigquery-public-data.stackoverflow.posts_questions`';

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

$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
$queryResults = $job->queryResults();

$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);

Legacy-SQL verwenden

Legen Sie den Parameter useLegacySql auf true fest, um in einem Abfragejob die Legacy-SQL-Syntax zu verwenden.

Python

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

Standardmäßig verwendet die Python-Clientbibliothek Standard-SQL.


from google.cloud import bigquery

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

query = """
    SELECT name, SUM(number) as total_people
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'TX'
    GROUP BY name, state
    ORDER BY total_people DESC
    LIMIT 20
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print("name={}, count={}".format(row[0], row["total_people"]))

Legacy-SQL verwenden

Legen Sie den Parameter use_legacy_sql auf True fest, um in einem Abfragejob die Legacy-SQL-Syntax zu verwenden.

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, passing in the extra configuration.
query_job = client.query(query, job_config=job_config)  # Make an API request.

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

Ruby

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Ruby in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Ruby API.

Standardmäßig verwendet die Ruby-Clientbibliothek Standard-SQL.

require "google/cloud/bigquery"

def query
  bigquery = Google::Cloud::Bigquery.new
  sql = "SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " +
        "WHERE state = 'TX' " +
        "LIMIT 100"

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

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

In der UI ein Abfragepräfix verwenden

Sie können den SQL-Dialekt auch in der Cloud Console oder in der klassischen Web-UI festlegen. Hierzu fügen Sie in die Abfrage ein SQL-Dialektpräfix ein.

Mit den folgenden Präfixen können Sie den SQL-Dialekt festlegen:

Präfix Beschreibung
#legacySQL Führt die Abfrage in Legacy-SQL aus
#standardSQL Führt die Abfrage in Standard-SQL aus

Wenn Sie in der Cloud Console ein Abfragepräfix verwenden, ist die Option SQL-Dialekt in den Abfrageeinstellungen deaktiviert.

Wenn Sie in der klassischen Web-UI ein Abfragepräfix verwenden, muss das Präfix der Option SQL-Dialekt entsprechen. Beispiel: Wenn Sie in der klassischen Web-UI das Häkchen für die Option Legacy SQL verwenden entfernen, können Sie in Ihrer Abfrage nicht das Präfix #legacySQL verwenden.

Wenn Sie zum Beispiel die folgende Abfrage kopieren und in die Cloud Console einfügen, führt BigQuery die Abfrage unter Verwendung von Legacy-SQL aus und ignoriert die Standardeinstellung. Die Standardeinstellung für die Cloud Console ist Standard-SQL.

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

Für die Abfragepräfixe #legacySQL und #standardSQL gilt:

  • Groß-/Kleinschreibung muss NICHT beachtet werden
  • Sie müssen der Abfrage vorangestellt sein
  • Sie müssen durch ein Zeilenvorschubzeichen von der Abfrage getrennt sein

Einige Drittanbietertools unterstützen dieses Präfix möglicherweise nicht, wenn sie beispielsweise den Abfragetext ändern, bevor er an BigQuery gesendet wird.

Standard-SQL als Standard für das Befehlszeilentool festlegen

Sie können Standard-SQL als Standardsyntax für das Befehlszeilentool und die interaktive Shell festlegen. Dazu bearbeiten Sie die Konfigurationsdatei .bigqueryrc des Befehlszeilentools.

Weitere Informationen zu .bigqueryrc finden Sie unter Standardwerte für Befehlszeilen-Flags festlegen.

So legen Sie --use_legacy_sql=false in .bigqueryrc fest:

  1. Öffnen Sie .bigqueryrc in einem Texteditor. Standardmäßig sollte sich .bigqueryrc in Ihrem Nutzerverzeichnis befinden, z. B. $HOME/.bigqueryrc.

  2. Fügen Sie der Datei den folgenden Text hinzu. In diesem Beispiel wird Standard-SQL als Standardsyntax für Abfragen und für den Befehl mk festgelegt, der beim Erstellen einer Datenansicht verwendet wird. Wenn Sie bereits Standardwerte für die Befehls-Flags query oder mk konfiguriert haben, müssen Sie [query] oder [mk] nicht noch einmal hinzufügen.

    [query]
    --use_legacy_sql=false
    
    [mk]
    --use_legacy_sql=false
    
  3. Speichern und schließen Sie die Datei.

  4. Wenn Sie die interaktive Shell verwenden, müssen Sie die Anwendung beenden und neu starten, damit die Änderungen wirksam werden.

Informationen zu verfügbaren Befehlszeilen-Flags finden Sie in der Referenz zum bq-Befehlszeilentool.