Membuat kueri tabel yang dikelompokkan

Saat Anda membuat tabel yang dikelompokkan di BigQuery, data tabel akan diatur secara otomatis berdasarkan isi satu atau beberapa kolom dalam skema tabel. Kolom yang Anda tentukan digunakan untuk menempatkan data terkait. Saat Anda mengelompokkan tabel menggunakan beberapa kolom, urutan kolom yang Anda tentukan bersifat penting. Urutan kolom yang ditentukan menentukan tata urutan data.

Untuk mengoptimalkan performa saat Anda menjalankan kueri terhadap tabel yang dikelompokkan, gunakan ekspresi yang memfilter kolom yang dikelompokkan atau di beberapa kolom yang dikelompokkan sesuai urutan penetapan kolom yang dikelompokkan. Kueri yang memfilter kolom yang dikelompokkan umumnya berperforma lebih baik daripada kueri yang hanya memfilter kolom yang tidak dikelompokkan.

BigQuery mengurutkan data dalam tabel yang dikelompokkan berdasarkan pada nilai kolom pengelompokan dan mengelolanya menjadi blok.

Saat Anda mengirimkan kueri yang berisi filter pada kolom yang dikelompokkan, BigQuery menggunakan informasi pengelompokan untuk menentukan secara efisien apakah suatu blok berisi data yang relevan dengan kueri. Hal ini memungkinkan BigQuery memindai blok yang relevan saja, yaitu proses yang disebut dengan pemangkasan blok.

Anda dapat membuat kueri tabel yang dikelompokkan dengan:

  • Menggunakan konsol Google Cloud
  • Menggunakan perintah bq query alat command line bq
  • Memanggil metode API jobs.insert dan mengonfigurasi tugas kueri
  • Menggunakan library klien

Saat ini, Anda hanya dapat menggunakan GoogleSQL dengan tabel yang dikelompokkan.

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. 

import (
	"context"
	"fmt"
	"io"

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

// queryClusteredTable demonstrates querying a table that has a clustering specification.
func queryClusteredTable(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	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(fmt.Sprintf(`
	SELECT
	  COUNT(1) as transactions,
	  SUM(amount) as total_paid,
	  COUNT(DISTINCT destination) as distinct_recipients
    FROM
	  `+"`%s.%s`"+`
	 WHERE
	    timestamp > TIMESTAMP('2015-01-01')
		AND origin = @wallet`, datasetID, tableID))
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "wallet",
			Value: "wallet00001866cb7e0f09a890",
		},
	}
	// 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

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. 

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

  public static void runQueryClusteredTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    queryClusteredTable(projectId, datasetName, tableName);
  }

  public static void queryClusteredTable(String projectId, String datasetName, String tableName) {
    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();

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query =
          "SELECT word, word_count\n"
              + "FROM "
              + sourceTable
              + "\n"
              // Optimize query performance by filtering the clustered columns in sort order
              + "WHERE corpus = 'romeoandjuliet'\n"
              + "AND word_count >= 1";

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query clustered table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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. 

from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`"
cluster_fields = ["corpus"]

job_config = bigquery.QueryJobConfig(
    clustering_fields=cluster_fields, destination=table_id
)

# Start the query, passing in the extra configuration.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the job to complete.

table = client.get_table(table_id)  # Make an API request.
if table.clustering_fields == cluster_fields:
    print(
        "The destination table is written using the cluster_fields configuration."
    )

Izin yang diperlukan

Untuk menjalankan tugas kueri, Anda memerlukan izin Identity and Access Management (IAM) bigquery.jobs.create pada project yang menjalankan tugas kueri.

Setiap peran IAM bawaan berikut mencakup izin yang Anda perlukan untuk menjalankan tugas kueri:

  • roles/bigquery.admin
  • roles/bigquery.jobUser
  • roles/bigquery.user

Anda juga memerlukan izin bigquery.tables.getData pada semua tabel dan tampilan yang dirujuk oleh kueri Anda. Selain itu, saat membuat kueri tampilan, Anda memerlukan izin ini pada semua tabel dan tampilan yang mendasarinya. Namun, jika menggunakan tampilan yang diotorisasi atau set data yang diotorisasi, Anda tidak memerlukan akses ke data sumber yang mendasarinya.

Setiap peran IAM yang telah ditetapkan berikut mencakup izin yang Anda perlukan pada semua tabel dan tampilan yang dirujuk oleh kueri:

  • roles/bigquery.admin
  • roles/bigquery.dataOwner
  • roles/bigquery.dataEditor
  • roles/bigquery.dataViewer

Untuk informasi selengkapnya tentang peran IAM di BigQuery, lihat Peran dan izin bawaan.

Praktik terbaik

Untuk mendapatkan performa terbaik dari kueri terhadap tabel yang dikelompokkan, gunakan praktik terbaik berikut.

Untuk konteks, tabel contoh yang digunakan dalam contoh praktik terbaik adalah tabel yang dikelompokkan dan dibuat menggunakan pernyataan DDL. Pernyataan DDL akan membuat tabel bernama ClusteredSalesData. Tabel dikelompokkan berdasarkan kolom berikut: customer_id, product_id, order_id, dalam tata urutan tersebut.

CREATE TABLE
  `mydataset.ClusteredSalesData`
PARTITION BY
  DATE(timestamp)
CLUSTER BY
  customer_id,
  product_id,
  order_id AS
SELECT
  *
FROM
  `mydataset.SalesData`

Memfilter kolom yang dikelompokkan menurut tata urutan

Saat Anda menentukan filter, gunakan ekspresi yang memfilter kolom yang dikelompokkan dalam tata urutan. Tata urutan adalah urutan kolom yang diberikan dalam klausa CLUSTER BY. Untuk mendapatkan manfaat pengelompokan, sertakan semua kolom yang dikelompokkan atau subset kolom dalam tata urutan kiri ke kanan, dimulai dengan kolom pertama. Misalnya, jika tata urutan kolom adalah A, B, dan C, sebuah kueri yang memfilter A dan B mungkin mendapat manfaat dari pengelompokan, tetapi kueri yang memfilter B dan C tidak. Urutan nama kolom di dalam ekspresi filter tidak memengaruhi performa.

Contoh berikut membuat kueri ke tabel yang dikelompokkan ClusteredSalesData yang dibuat dalam contoh sebelumnya. Kueri ini mencakup ekspresi filter yang memfilter customer_id, lalu product_id. Kueri ini mengoptimalkan performa dengan memfilter kolom yang dikelompokkan dalam tata urutan, yaitu urutan kolom yang diberikan dalam klausa CLUSTER BY.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000
  AND product_id LIKE 'gcp_analytics%'

Kueri berikut tidak memfilter kolom yang dikelompokkan dalam tata urutan. Akibatnya, performa kueri tidak optimal. Kueri ini memfilter product_id, lalu order_id (melewati customer_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  product_id LIKE 'gcp_analytics%'
  AND order_id = 20000

Jangan gunakan kolom yang dikelompokkan dalam ekspresi filter yang kompleks

Jika Anda menggunakan kolom yang dikelompokkan dalam ekspresi filter yang kompleks, performa kueri tidak akan dioptimalkan karena pemangkasan blok tidak dapat diterapkan.

Misalnya, kueri berikut tidak akan memangkas blok karena kolom yang dikelompokkan, customer_id, digunakan dalam fungsi di ekspresi filter.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  CAST(customer_id AS STRING) = "10000"

Untuk mengoptimalkan performa kueri dengan memangkas blok, gunakan ekspresi filter sederhana seperti berikut. Dalam contoh ini, filter sederhana diterapkan ke kolom yang dikelompokkan, yaitu customer_id.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000

Jangan membandingkan kolom yang dikelompokkan dengan kolom lain

Jika ekspresi filter membandingkan kolom yang dikelompokkan dengan kolom lain (baik kolom yang dikelompokkan maupun kolom yang tidak dikelompokkan), performa kueri tidak akan dioptimalkan karena pemangkasan blok tidak dapat diterapkan.

Kueri berikut tidak memangkas blok karena ekspresi filter membandingkan kolom yang dikelompokkan, customer_id, dengan kolom lain, order_id.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = order_id

Keamanan tabel

Untuk mengontrol akses ke tabel di BigQuery, lihat Pengantar kontrol akses tabel.

Langkah selanjutnya