Pengantar SQL di BigQuery

Dokumen ini memberikan ringkasan tentang pernyataan dan dialek SQL yang didukung di BigQuery.

Ringkasan

GoogleSQL adalah Structured Query Language (SQL) sesuai ANSI yang mencakup jenis pernyataan yang didukung berikut ini:

  • Pernyataan kueri, yang juga disebut sebagai pernyataan Data Query Language (DQL), adalah metode utama untuk menganalisis data di BigQuery. Alat tersebut memindai satu atau beberapa tabel atau ekspresi dan menampilkan baris hasil yang dihitung.
  • Pernyataan bahasa prosedural adalah ekstensi prosedural untuk GoogleSQL yang memungkinkan Anda mengeksekusi beberapa pernyataan SQL dalam satu permintaan. Pernyataan prosedural dapat menggunakan variabel dan pernyataan alur kontrol, dan dapat memiliki efek samping.
  • Pernyataan Bahasa Definisi Data (DDL) memungkinkan Anda membuat dan mengubah objek database seperti tabel, tampilan, fungsi, dan kebijakan akses tingkat baris.
  • Pernyataan Bahasa Pengolahan Data (DML) memungkinkan Anda memperbarui, menyisipkan, dan menghapus data dari Tabel BigQuery.
  • Pernyataan Bahasa Kontrol Data (DCL) memungkinkan Anda mengontrol resource sistem BigQuery seperti akses dan kapasitas.
  • Pernyataan Transaction Control Language (TCL) memungkinkan Anda mengelola transaksi untuk modifikasi data.
  • Pernyataan lain menyediakan fungsi tambahan, seperti mengekspor data.

Dialek BigQuery SQL

BigQuery mendukung dialek GoogleSQL, tetapi dialek legacy SQL juga tersedia. Jika baru mengenal BigQuery, Anda harus menggunakan GoogleSQL karena GoogleSQL mendukung berbagai fungsi. Misalnya, fitur seperti pernyataan DDL dan DML hanya didukung menggunakan GoogleSQL. Legacy SQL dipertahankan untuk kompatibilitas mundur dan sebaiknya pelanggan migrate jika mereka menggunakan legacy SQL.

Mengubah dari dialek default

Antarmuka yang Anda gunakan untuk membuat kueri data menentukan dialek kueri yang merupakan default. Untuk beralih ke dialek lain:

Konsol

Dialek default untuk Konsol Google Cloud adalah GoogleSQL. Untuk mengubah dialek tersebut menjadi legacy SQL:

  1. Di Konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, klik tombol More > Query settings.

  3. Di bagian Opsi lanjutan, untuk dialek SQL, klik Legacy, lalu klik Simpan. Tindakan ini akan menetapkan opsi legacy SQL untuk kueri ini. Saat mengklik Tulis kueri baru untuk membuat kueri baru, Anda harus memilih opsi legacy SQL lagi.

SQL

Dialek SQL default adalah GoogleSQL. Anda dapat menetapkan dialek SQL dengan menyertakan awalan #standardSQL atau #legacySQL sebagai bagian dari kueri Anda. Awalan kueri ini tidak peka huruf besar/kecil, harus mendahului kueri, dan harus dipisahkan dari kueri dengan karakter newline. Contoh berikut menetapkan dialek ke legacy SQL dan meng-kueri set data kelahiran:

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

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

  3. Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

bq

Dialek kueri default dalam alat command line bq adalah legacy SQL. Untuk beralih ke dialek GoogleSQL, tambahkan tanda --use_legacy_sql=false atau --nouse_legacy_sql ke pernyataan command line Anda.

Beralih ke dialek GoogleSQL

Untuk menggunakan sintaksis GoogleSQL dalam tugas kueri, setel parameter use_legacy_sql ke false.

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

Menetapkan GoogleSQL sebagai dialek default

Anda dapat menetapkan GoogleSQL sebagai dialek default untuk alat command line dan shell interaktif dengan mengedit file konfigurasi alat command line: .bigqueryrc.

Untuk mengetahui informasi selengkapnya tentang .bigqueryrc, lihat Menetapkan nilai default untuk tanda khusus perintah.

Untuk menetapkan --use_legacy_sql=false di .bigqueryrc:

  1. Buka .bigqueryrc di editor teks. Secara default, .bigqueryrc harus berada di direktori pengguna Anda, misalnya, $HOME/.bigqueryrc.

  2. Tambahkan teks berikut ke file. Contoh ini menetapkan GoogleSQL sebagai sintaksis default untuk kueri dan perintah mk (digunakan saat Anda membuat tampilan). Jika sudah mengonfigurasi nilai default untuk tanda perintah query atau mk, Anda tidak perlu menambahkan [query] atau [mk] lagi.

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

  3. Simpan dan tutup file tersebut.

  4. Jika menggunakan shell interaktif, Anda harus keluar dan memulai ulang agar perubahan dapat diterapkan.

Untuk mengetahui informasi tentang tanda command line yang tersedia, lihat referensi alat command line bq.

C#

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan C# di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery C# API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library C# menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, tetapkan parameter UseLegacySql ke 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

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Go di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Go API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library klien Go menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, tetapkan properti UseLegacySQL dalam konfigurasi kueri ke 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

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Java di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Java API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library klien Java menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, tetapkan parameter useLegacySql ke 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

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Node.js di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Node.js API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library klien Node.js menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, tetapkan parameter useLegacySql ke 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

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan PHP di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery PHP API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library klien PHP menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, tetapkan parameter useLegacySql ke 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

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Python di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Python API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library klien Python menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, tetapkan parameter use_legacy_sql ke 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

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Ruby di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Ruby API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Secara default, library klien Ruby menggunakan GoogleSQL.

Beralih ke dialek legacy SQL

Untuk menggunakan sintaksis legacy SQL dalam tugas kueri, teruskan opsi legacy_sql: true dengan kueri Anda.

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

Langkah selanjutnya