Mengelola indeks penelusuran

Indeks penelusuran adalah struktur data yang dirancang untuk memungkinkan penelusuran yang sangat efisien dengan fungsi SEARCH. Sama seperti indeks yang dapat Anda temukan di bagian belakang buku, indeks penelusuran untuk kolom data string berfungsi seperti tabel tambahan yang memiliki satu kolom untuk kata-kata unik dan kolom lain untuk bagian data tempat kata tersebut muncul.

Membuat indeks penelusuran

Untuk membuat indeks penelusuran, gunakan pernyataan DDL CREATE SEARCH INDEX. Anda dapat membuat indeks penelusuran pada jenis kolom berikut:

  • STRING
  • ARRAY<STRING>
  • STRUCT yang berisi setidaknya satu kolom bertingkat dari jenis STRING atau ARRAY<STRING>
  • JSON

Saat membuat indeks penelusuran, Anda dapat menentukan jenis text analyzer yang akan digunakan. Penganalisis teks mengontrol cara data ditokenkan untuk pengindeksan dan penelusuran. Defaultnya adalah LOG_ANALYZER. Penganalisis ini berfungsi dengan baik untuk log yang dihasilkan mesin dan memiliki aturan khusus seputar token yang biasa ditemukan dalam data kemampuan observasi, seperti alamat IP atau email. Gunakan NO_OP_ANALYZER jika Anda telah melakukan pra-pemrosesan data yang ingin dicocokkan persis. PATTERN_ANALYZER mengekstrak token dari teks menggunakan ekspresi reguler.

Contoh berikut membuat indeks penelusuran pada kolom a dan c dari simple_table.

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, c);

Saat Anda membuat indeks penelusuran di ALL COLUMNS, semua data STRING atau JSON dalam tabel akan diindeks. Jika tabel tidak berisi data tersebut, misalnya jika semua kolom berisi bilangan bulat, pembuatan indeks akan gagal. Saat Anda menentukan kolom STRUCT yang akan diindeks, semua subkolom bertingkat akan diindeks.

Pada contoh berikut, indeks penelusuran dibuat di a, c.e, dan c.f.g, serta menggunakan penganalisis teks NO_OP_ANALYZER.

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS (analyzer = 'NO_OP_ANALYZER');

Karena indeks penelusuran dibuat di ALL COLUMNS, setiap kolom yang ditambahkan ke tabel akan otomatis diindeks jika berisi data STRING.

Memahami pemuatan ulang indeks

Indeks penelusuran dikelola sepenuhnya oleh BigQuery dan otomatis dimuat ulang saat tabel berubah. Perubahan skema pada tabel berikut dapat memicu pemuatan ulang penuh:

  • Kolom baru yang dapat diindeks ditambahkan ke tabel dengan indeks penelusuran di ALL COLUMNS.
  • Kolom yang diindeks diperbarui karena perubahan skema tabel.

Jika Anda menghapus satu-satunya kolom yang diindeks dalam tabel atau mengganti nama tabel itu sendiri, indeks penelusuran akan otomatis dihapus.

Indeks penelusuran didesain untuk tabel besar. Jika Anda membuat indeks penelusuran pada tabel yang berukuran lebih kecil dari 10 GB, indeks tidak akan terisi. Demikian pula, jika Anda menghapus data dari tabel yang diindeks dan ukuran tabel di bawah 10 GB, indeks akan dinonaktifkan untuk sementara. Dalam hal ini, kueri penelusuran tidak menggunakan indeks dan kode IndexUnusedReason adalah BASE_TABLE_TOO_SMALL. Hal ini terjadi terlepas dari apakah Anda menggunakan pemesanan sendiri untuk tugas pengelolaan indeks Anda atau tidak. Jika ukuran tabel yang diindeks melebihi 10 GB, indeksnya akan otomatis terisi. Anda tidak akan dikenai biaya untuk penyimpanan hingga indeks penelusuran terisi dan aktif. Kueri yang menggunakan fungsi SEARCH selalu menampilkan hasil yang benar meskipun beberapa data belum diindeks.

Mendapatkan informasi tentang indeks penelusuran

Anda dapat memverifikasi keberadaan dan kesiapan indeks penelusuran dengan membuat kueri INFORMATION_SCHEMA. Ada dua tampilan yang berisi metadata pada indeks penelusuran. Tampilan INFORMATION_SCHEMA.SEARCH_INDEXES memiliki informasi tentang setiap indeks penelusuran yang dibuat pada set data. Tampilan INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS memiliki informasi yang mengindeks kolom setiap tabel dalam set data.

Contoh berikut menunjukkan semua indeks penelusuran aktif pada tabel dalam set data my_dataset, yang terletak di project my_project. Contoh ini mencakup nama indeks, pernyataan DDL yang digunakan untuk membuatnya, persentase cakupannya, dan penganalisis teksnya. Jika tabel dasar yang diindeks kurang dari 10 GB, maka indeksnya tidak terisi, dan dalam hal ini coverage_percentage adalah 0.

SELECT table_name, index_name, ddl, coverage_percentage, analyzer
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE index_status = 'ACTIVE';

Hasilnya akan terlihat seperti berikut:

+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| table_name  | index_name  | ddl                                                                                  | coverage_percentage | analyzer       |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names)      | 0                   | NO_OP_ANALYZER |
| large_table | logs_index  | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100                 | LOG_ANALYZER   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

Contoh berikut membuat indeks penelusuran di semua kolom my_table.

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS);

Kueri berikut mengekstrak informasi tentang kolom yang diindeks. index_field_path menunjukkan kolom mana pada kolom yang diindeks. Hal ini berbeda dengan index_column_name hanya dalam kasus STRUCT, yang memberikan jalur lengkap ke kolom terindeks. Dalam contoh ini, kolom c berisi kolom ARRAY<STRING> e dan STRUCT lain yang disebut f dan berisi kolom STRING g, yang masing-masing diindeks.

SELECT table_name, index_name, index_column_name, index_field_path
FROM my_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS

Hasilnya serupa dengan berikut ini:

+------------+------------+-------------------+------------------+
| table_name | index_name | index_column_name | index_field_path |
+------------+------------+-------------------+------------------+
| my_table   | my_index   | a                 | a                |
| my_table   | my_index   | c                 | c.e              |
| my_table   | my_index   | c                 | c.f.g            |
+------------+------------+-------------------+------------------+

Kueri berikut menggabungkan tampilan INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS dengan tampilan INFORMATION_SCHEMA.SEARCH_INDEXES dan INFORMATION_SCHEMA.COLUMNS untuk menyertakan status indeks penelusuran dan jenis data setiap kolom:

SELECT
  index_columns_view.index_catalog AS project_name,
  index_columns_view.index_SCHEMA AS dataset_name,
  indexes_view.TABLE_NAME AS table_name,
  indexes_view.INDEX_NAME AS index_name,
  indexes_view.INDEX_STATUS AS status,
  index_columns_view.INDEX_COLUMN_NAME AS column_name,
  index_columns_view.INDEX_FIELD_PATH AS field_path,
  columns_view.DATA_TYPE AS data_type
FROM
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES indexes_view
INNER JOIN
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS index_columns_view
  ON
    indexes_view.TABLE_NAME = index_columns_view.TABLE_NAME
    AND indexes_view.INDEX_NAME = index_columns_view.INDEX_NAME
LEFT OUTER JOIN
  mydataset.INFORMATION_SCHEMA.COLUMNS columns_view
  ON
    indexes_view.INDEX_CATALOG = columns_view.TABLE_CATALOG
    AND indexes_view.INDEX_SCHEMA = columns_view.TABLE_SCHEMA
    AND index_columns_view.TABLE_NAME = columns_view.TABLE_NAME
    AND index_columns_view.INDEX_COLUMN_NAME = columns_view.COLUMN_NAME
ORDER BY
  project_name,
  dataset_name,
  table_name,
  column_name;

Hasilnya serupa dengan berikut ini: 

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| project    | dataset    | table    | index_name | status | column_name | field_path | data_type                                                     |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| my_project | my_dataset | my_table | my_index   | ACTIVE | a           | a          | STRING                                                        |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.e        | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.f.g      | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

Opsi pengelolaan indeks

Untuk membuat indeks dan meminta BigQuery mengelolanya, Anda memiliki dua opsi:

Menggunakan slot bersama

Jika Anda belum mengonfigurasi project agar menggunakan pemesanan khusus untuk pengindeksan, pengelolaan indeks akan ditangani dalam gabungan slot bersama yang gratis dan tunduk pada batasan berikut.

Jika Anda menambahkan data ke tabel yang menyebabkan ukuran total tabel yang diindeks melebihi batas organisasi Anda, BigQuery akan menjeda pengelolaan indeks untuk semua tabel yang diindeks. Jika hal ini terjadi, kolom index_status di tampilan INFORMATION_SCHEMA.SEARCH_INDEXES akan menampilkan PENDING DISABLEMENT dan indeks diantrekan untuk dihapus. Meskipun dalam proses penonaktifan indeks, indeks masih digunakan dalam kueri dan Anda akan dikenai biaya untuk penyimpanan indeks. Setelah indeks dihapus, kolom index_status akan menunjukkan indeks sebagai TEMPORARILY DISABLED. Dalam status ini, kueri tidak menggunakan indeks, dan Anda tidak akan dikenai biaya untuk penyimpanan indeks. Dalam hal ini, kode IndexUnusedReason adalah BASE_TABLE_TOO_LARGE.

Jika Anda menghapus data dari tabel dan ukuran total tabel yang diindeks di bawah batas per organisasi, pengelolaan indeks akan dilanjutkan untuk semua tabel yang diindeks. Kolom index_status di tampilan INFORMATION_SCHEMA.SEARCH_INDEXES adalah ACTIVE, kueri dapat menggunakan indeks, dan Anda akan dikenai biaya untuk penyimpanan indeks.

BigQuery tidak menjamin ketersediaan kapasitas dari gabungan slot bersama atau throughput pengindeksan yang Anda lihat. Untuk aplikasi produksi, Anda dapat menggunakan slot khusus untuk pemrosesan indeks.

Menggunakan pemesanan Anda sendiri

Alih-alih menggunakan gabungan slot bersama default, Anda dapat menetapkan pemesanan sendiri untuk mengindeks tabel. Menggunakan reservasi Anda sendiri memastikan performa tugas pengelolaan indeks yang dapat diprediksi dan konsisten, seperti pembuatan, refresh, dan pengoptimalan latar belakang.

  • Tidak ada batas ukuran tabel saat tugas pengindeksan berjalan di pemesanan Anda.
  • Menggunakan pemesanan Anda sendiri memberi Anda fleksibilitas dalam pengelolaan indeks. Jika perlu membuat indeks yang sangat besar atau membuat update besar pada tabel yang diindeks, Anda dapat menambahkan lebih banyak slot ke penetapan untuk sementara.

Untuk mengindeks tabel dalam project dengan reservasi yang ditetapkan, buat reservasi di region tempat tabel Anda berada. Kemudian, tetapkan project ke reservasi dengan job_type yang ditetapkan ke BACKGROUND:

SQL

Gunakan pernyataan DDL CREATE ASSIGNMENT.

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

    Ganti kode berikut:

    • ADMIN_PROJECT_ID: project ID dari project administrasi yang memiliki resource pemesanan
    • LOCATION: lokasi pemesanan
    • RESERVATION_NAME: nama pemesanan

    • ASSIGNMENT_ID: ID tugas

      ID harus unik untuk project dan lokasi, diawali dan diakhiri dengan huruf kecil atau angka, dan hanya berisi huruf kecil, angka, dan tanda hubung.

    • PROJECT_ID: ID project yang berisi tabel yang akan diindeks. Project ini ditetapkan ke pemesanan.

  3. Klik Run.

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

bq

Gunakan perintah bq mk:

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation_assignment \
    --reservation_id=RESERVATION_NAME \
    --assignee_id=PROJECT_ID \
    --job_type=BACKGROUND \
    --assignee_type=PROJECT

Ganti kode berikut:

  • ADMIN_PROJECT_ID: project ID dari project administrasi yang memiliki resource pemesanan
  • LOCATION: lokasi pemesanan
  • RESERVATION_NAME: nama pemesanan
  • PROJECT_ID: ID project yang akan ditetapkan ke pemesanan ini

Melihat tugas pengindeksan

Tugas pengindeksan baru dibuat setiap kali indeks dibuat atau diperbarui pada satu tabel. Untuk melihat informasi tentang tugas, buat kueri tampilan INFORMATION_SCHEMA.JOBS*. Anda dapat memfilter tugas pengindeksan dengan menetapkan job_type IS NULL AND SEARCH(job_id, '`search_index`') dalam klausa WHERE kueri Anda. Contoh berikut mencantumkan lima tugas pengindeksan terbaru dalam project my_project:

SELECT *
FROM
 region-us.INFORMATION_SCHEMA.JOBS
WHERE
  project_id  = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
ORDER BY
 creation_time DESC
LIMIT 5;

Memilih ukuran pemesanan Anda

Untuk memilih jumlah slot yang tepat untuk pemesanan, Anda harus mempertimbangkan kapan tugas pengelolaan indeks dijalankan, jumlah slot yang digunakan, dan seperti apa penggunaan Anda dari waktu ke waktu. BigQuery memicu tugas pengelolaan indeks dalam situasi berikut:

  • Anda membuat indeks pada tabel.
  • Data diubah dalam tabel yang diindeks.
  • Skema tabel berubah dan hal ini memengaruhi kolom mana yang akan diindeks.
  • Data dan metadata indeks dioptimalkan atau diperbarui secara berkala.

Jumlah slot yang Anda perlukan untuk tugas pengelolaan indeks pada tabel bergantung pada faktor-faktor berikut:

  • Ukuran tabel
  • Laju penyerapan data ke tabel
  • Tingkat pernyataan DML yang diterapkan pada tabel
  • Penundaan yang dapat diterima untuk membangun dan mempertahankan indeks
  • Kompleksitas indeks, biasanya ditentukan oleh atribut data, seperti jumlah istilah duplikat
Estimasi Awal

Estimasi berikut dapat membantu Anda memperkirakan jumlah slot yang diperlukan oleh pemesanan Anda. Karena sifat workload pengindeksan yang sangat bervariasi, Anda harus mengevaluasi ulang persyaratan setelah mulai mengindeks data.

  • Data yang ada: Dengan pemesanan 1.000 slot, tabel yang sudah ada di BigQuery dapat diindeks dengan kecepatan rata-rata hingga 4 GiB per detik, yaitu sekitar 336 TiB per hari.
  • Data yang baru diserap: Pengindeksan biasanya memerlukan lebih banyak resource pada data yang baru diserap, karena tabel dan indeksnya menjalani beberapa tahap pengoptimalan transformatif. Pengindeksan data yang baru diserap rata-rata menghabiskan resource tiga kali lipat dibandingkan pengindeksan pengisian ulang awal dari data yang sama.
  • Data yang jarang dimodifikasi: Tabel yang diindeks dengan sedikit atau tanpa modifikasi data memerlukan resource yang jauh lebih sedikit untuk pengelolaan indeks yang berkelanjutan. Titik awal yang direkomendasikan adalah mempertahankan 1/5 slot yang diperlukan untuk pengindeksan pengisian ulang awal data yang sama, dan tidak kurang dari 250 slot.
  • Progres pengindeksan diskalakan dengan perkiraan secara linear sesuai ukuran pemesanan. Namun, sebaiknya jangan gunakan reservasi yang lebih kecil dari 250 slot untuk pengindeksan karena dapat menyebabkan inefisiensi yang dapat memperlambat progres pengindeksan.
  • Estimasi ini dapat berubah jika fitur, pengoptimalan, dan penggunaan yang sebenarnya bervariasi.
  • Jika total ukuran tabel organisasi Anda melebihi batas pengindeksan region, Anda harus mempertahankan pemesanan bukan nol yang ditetapkan untuk pengindeksan. Jika tidak, pengindeksan mungkin akan kembali ke tingkat default, sehingga menyebabkan penghapusan semua indeks yang tidak diinginkan.
Memantau Penggunaan dan Progres

Cara terbaik untuk menilai jumlah slot yang Anda perlukan untuk menjalankan tugas pengelolaan indeks secara efisien adalah dengan memantau penggunaan slot dan menyesuaikan ukuran pemesanan sebagaimana mestinya. Kueri berikut menghasilkan penggunaan slot harian untuk tugas pengelolaan indeks. Hanya 30 hari terakhir yang disertakan dalam region us-west1:

SELECT
  TIMESTAMP_TRUNC(job.creation_time, DAY) AS usage_date,
  -- Aggregate total_slots_ms used for index-management jobs in a day and divide
  -- by the number of milliseconds in a day. This value is most accurate for
  -- days with consistent slot usage.
  SAFE_DIVIDE(SUM(job.total_slot_ms), (1000 * 60 * 60 * 24)) AS average_daily_slot_usage
FROM
  `region-us-west1`.INFORMATION_SCHEMA.JOBS job
WHERE
  project_id = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
GROUP BY
  usage_date
ORDER BY
  usage_date DESC
limit 30;

Jika slot tidak cukup untuk menjalankan tugas pengelolaan indeks, indeks mungkin menjadi tidak sinkron dengan tabelnya dan tugas pengindeksan mungkin gagal. Dalam hal ini, BigQuery membangun ulang indeks dari awal. Untuk menghindari indeks yang tidak sinkron, pastikan Anda memiliki slot yang cukup untuk mendukung pembaruan indeks dari penyerapan dan pengoptimalan data. Untuk informasi selengkapnya tentang pemantauan penggunaan slot, lihat diagram resource admin.

Praktik terbaik

  • Indeks penelusuran didesain untuk tabel besar. Performa indeks penelusuran meningkat seiring ukuran tabel.
  • Jangan mengindeks kolom yang hanya berisi sedikit nilai unik.
  • Jangan mengindeks kolom yang tidak pernah Anda inginkan untuk memanggil fungsi SEARCH.
  • Hati-hati saat membuat indeks penelusuran di ALL COLUMNS. Setiap kali Anda menambahkan kolom yang berisi data STRING atau JSON, kolom tersebut akan diindeks.
  • Anda harus menggunakan pemesanan Anda sendiri untuk pengelolaan indeks dalam aplikasi produksi. Jika Anda memilih menggunakan gabungan slot bersama default untuk tugas pengelolaan indeks, batas ukuran per organisasi akan berlaku.

Menghapus indeks penelusuran

Jika tidak lagi memerlukan indeks penelusuran atau ingin mengubah kolom mana yang akan diindeks pada tabel, Anda dapat menghapus indeks yang saat ini ada di tabel tersebut. Untuk melakukannya, gunakan pernyataan DDL DROP SEARCH INDEX.

Jika tabel yang diindeks dihapus, indeksnya dihapus secara otomatis.

Contoh:

DROP SEARCH INDEX my_index ON dataset.simple_table;

Langkah selanjutnya