Membuat kueri beberapa tabel menggunakan tabel karakter pengganti

Tabel karakter pengganti memungkinkan Anda membuat kueri beberapa tabel menggunakan pernyataan SQL yang ringkas. Tabel karakter pengganti hanya tersedia di GoogleSQL. Untuk fungsi yang setara di legacy SQL, lihat Fungsi karakter pengganti tabel.

Tabel karakter pengganti mewakili gabungan semua tabel yang cocok dengan ekspresi karakter pengganti. Misalnya, klausa FROM berikut menggunakan ekspresi karakter pengganti gsod* untuk mencocokkan semua tabel dalam set data noaa_gsod yang dimulai dengan string gsod.

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

Setiap baris dalam tabel karakter pengganti berisi kolom khusus, _TABLE_SUFFIX, yang berisi nilai yang cocok dengan karakter pengganti.

Batasan

Kueri tabel karakter pengganti memiliki batasan berikut.

Fungsi tabel karakter pengganti tidak mendukung tampilan. Jika tabel karakter pengganti cocok dengan tampilan apa pun dalam set data, kueri akan menampilkan error meskipun kueri berisi klausa WHERE di kolom semu _TABLE_SUFFIX untuk memfilter tampilan.
Hasil yang disimpan dalam cache tidak didukung untuk kueri terhadap beberapa tabel menggunakan karakter pengganti, meskipun opsi Gunakan Hasil yang Disimpan di Cache dicentang. Jika menjalankan kueri karakter pengganti yang sama beberapa kali, Anda akan ditagih untuk setiap kueri.
Tabel karakter pengganti hanya mendukung penyimpanan BigQuery bawaan. Anda tidak dapat menggunakan karakter pengganti untuk membuat kueri tabel eksternal atau tampilan.
Anda tidak dapat menggunakan kueri karakter pengganti pada tabel dengan partisi yang tidak kompatibel atau campuran antara tabel berpartisi dan tidak berpartisi. Tabel yang dikueri juga harus memiliki spesifikasi pengelompokan yang identik.
Anda dapat menggunakan tabel karakter pengganti dengan tabel berpartisi. Selain itu, pruning partisi dan pruning cluster juga didukung. Namun, tabel yang dikelompokkan tetapi tidak berpartisi tidak mendapatkan manfaat pruning cluster dari penggunaan karakter pengganti.
Kueri yang berisi pernyataan bahasa pengolahan data (DML) tidak dapat menggunakan tabel karakter pengganti sebagai target kueri. Misalnya, tabel karakter pengganti dapat digunakan dalam klausa FROM dari kueri UPDATE, tetapi tabel karakter pengganti tidak dapat digunakan sebagai target operasi UPDATE.
Filter pada kolom semu _TABLE_SUFFIX atau _PARTITIONTIME yang menyertakan fungsi JavaScript yang ditentukan pengguna tidak membatasi jumlah tabel yang dipindai di tabel karakter pengganti.
Kueri karakter pengganti tidak didukung untuk tabel yang dilindungi oleh kunci enkripsi yang dikelola pelanggan (CMEK).
Kueri karakter pengganti tidak didukung untuk tabel yang memiliki tag.
Saat menggunakan tabel karakter pengganti, semua tabel dalam set data yang dimulai dengan nama tabel sebelum * akan dipindai meskipun _TABLE_SUFFIX digunakan bersama dengan REGEXP_CONTAINS dan diberikan ekspresi reguler seperti ^[0-9]{2}$. Contoh:
```
SELECT *
FROM `my_project.my_dataset.my_table_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
```
Jika satu tabel yang dipindai memiliki ketidakcocokan skema (yaitu, kolom dengan nama yang sama memiliki jenis yang berbeda), kueri akan gagal dengan error Tidak dapat membaca kolom jenis X sebagai Kolom Y: nama_kolom. Semua tabel cocok meskipun Anda menggunakan operator kesetaraan =. Misalnya, dalam kueri berikut, tabel my_dataset.my_table_03_backup juga dipindai. Dengan demikian, kueri mungkin gagal karena ketidakcocokan skema. Namun, jika tidak ada ketidakcocokan skema, hasilnya akan berasal dari tabel my_dataset.my_table_03 saja, seperti yang diharapkan.
```
SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
```

Sebelum memulai

Pastikan Anda menggunakan GoogleSQL. Untuk informasi selengkapnya, lihat Mengalihkan dialek SQL.
Jika Anda menggunakan legacy SQL, lihat Fungsi karakter pengganti tabel.
Banyak contoh di halaman ini menggunakan set data publik dari National Oceanic and Atmospheric Administration (NOAA). Untuk informasi selengkapnya tentang data tersebut, lihat Data Cuaca Global Surface Summary of the Day NOAA.

Kapan harus menggunakan tabel karakter pengganti

Tabel karakter pengganti berguna saat set data berisi beberapa tabel dengan nama serupa yang memiliki skema yang kompatibel. Biasanya, set data semacam itu berisi tabel yang masing-masing mewakili data dari satu hari, bulan, atau tahun. Misalnya, set data publik yang dihosting oleh BigQuery, Data Cuaca Global Surface Summary of the Day NOAA, berisi tabel untuk setiap tahun dari tahun 1929 hingga sekarang.

Kueri yang memindai semua ID tabel dari tahun 1929 hingga 1940 akan sangat panjang jika Anda harus menamai kedua belas tabel dalam klausa FROM (sebagian besar tabel dihilangkan dalam contoh ini):

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

Kueri yang sama dan menggunakan tabel karakter pengganti jauh lebih ringkas:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC

Tabel karakter pengganti hanya mendukung penyimpanan BigQuery bawaan. Anda tidak dapat menggunakan karakter pengganti saat membuat kueri tabel eksternal atau tampilan.

Sintaksis tabel karakter pengganti

Sintaksis tabel karakter pengganti:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>: Project ID Cloud Platform. Opsional jika Anda menggunakan project ID default.
<dataset-id>: ID set data BigQuery.
<table-prefix>: String yang umum di semua tabel yang dicocokkan dengan karakter dari karakter pengganti. Awalan tabel bersifat opsional. Menghapus awalan tabel akan cocok dengan semua tabel dalam set data.
* (karakter dari karakter pengganti): Karakter dari karakter pengganti, "*", mewakili satu atau beberapa karakter nama tabel. Karakter dari karakter pengganti hanya dapat muncul sebagai karakter terakhir dari nama tabel karakter pengganti.

Kueri dengan tabel karakter pengganti mendukung kolom semu _TABLE_SUFFIX dalam klausa WHERE. Kolom ini berisi nilai yang cocok dengan karakter dari karakter pengganti, sehingga kueri dapat memfilter tabel mana yang akan diakses. Misalnya, klausa WHERE berikut menggunakan operator perbandingan untuk memfilter tabel yang cocok:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

Untuk informasi selengkapnya tentang kolom semu _TABLE_SUFFIX, lihat Memfilter tabel yang dipilih menggunakan _TABLE_SUFFIX.

Menyertakan nama tabel dengan karakter pengganti dalam tanda kutip terbalik

Nama tabel karakter pengganti berisi karakter khusus (*), artinya Anda harus menyertakan nama tabel karakter pengganti dalam karakter tanda kutip terbalik (`). Misalnya, kueri berikut valid karena menggunakan tanda kutip terbalik:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Kueri berikut TIDAK valid karena tidak menyertakan tanda kutip terbalik dengan benar:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Tanda kutip tidak berfungsi:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Tabel kueri menggunakan tabel karakter pengganti

Tabel karakter pengganti memungkinkan Anda membuat kueri beberapa tabel secara ringkas. Misalnya, set data publik yang dihosting oleh BigQuery, Data Cuaca Global Surface Summary of the Day NOAA, berisi tabel untuk setiap tahun dari 1929 hingga sekarang, yang semuanya memiliki awalan umum gsod diikuti dengan tahun empat digit. Tabel tersebut diberi nama gsod1929, gsod1930, gsod1931, dll.

Untuk membuat kueri grup tabel yang memiliki awalan yang sama, gunakan simbol karakter pengganti tabel (*) setelah awalan tabel dalam pernyataan FROM. Misalnya, kueri berikut menemukan suhu maksimum yang dilaporkan selama tahun 1940-an:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

Memfilter tabel yang dipilih menggunakan _TABLE_SUFFIX

Untuk membatasi kueri agar hanya memindai kumpulan tabel yang ditentukan, gunakan kolom semu _TABLE_SUFFIX dalam klausa WHERE dengan kondisi yang merupakan ekspresi konstanta.

Kolom semu _TABLE_SUFFIX berisi nilai yang cocok dengan karakter pengganti tabel. Misalnya, contoh kueri sebelumnya, yang memindai semua tabel dari tahun 1940-an, menggunakan karakter pengganti tabel untuk mewakili digit terakhir tahun:

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

Kolom semu _TABLE_SUFFIX yang sesuai berisi nilai dalam rentang 0 hingga 9, yang menampilkan tabel gsod1940 hingga gsod1949. Nilai _TABLE_SUFFIX ini dapat digunakan dalam klausa WHERE untuk memfilter tabel tertentu.

Misalnya, untuk memfilter suhu maksimum pada tahun 1940 dan 1944, gunakan nilai 0 dan 4 untuk _TABLE_SUFFIX:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

Penggunaan _TABLE_SUFFIX dapat mengurangi jumlah byte yang dipindai secara signifikan, sehingga membantu mengurangi biaya untuk menjalankan kueri Anda.

Namun, filter pada _TABLE_SUFFIX yang menyertakan kondisi tanpa ekspresi konstan tidak membatasi jumlah tabel yang dipindai dalam tabel karakter pengganti. Misalnya, kueri berikut tidak membatasi tabel yang dipindai untuk tabel karakter pengganti bigquery-public-data.noaa_gsod.gsod19* karena filter tersebut menggunakan nilai dinamis kolom table_id:

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Sebagai contoh lainnya, kueri berikut membatasi pemindaian berdasarkan kondisi filter pertama, _TABLE_SUFFIX BETWEEN '40' and '60', karena merupakan ekspresi konstanta. Namun, kueri berikut tidak membatasi pemindaian berdasarkan kondisi filter kedua, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), karena merupakan ekspresi dinamis:

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Sebagai solusinya, Anda dapat melakukan dua kueri terpisah; misalnya:

Kueri pertama:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%'

Kueri kedua:

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

Contoh kueri ini menggunakan tampilan INFORMATION_SCHEMA.TABLES. Untuk mengetahui informasi selengkapnya tentang tabel INFORMATION_SCHEMA, lihat Mendapatkan metadata tabel menggunakan INFORMATION_SCHEMA.

Memindai rentang tabel menggunakan _TABLE_SUFFIX

Untuk memindai berbagai tabel, gunakan kolom semu _TABLE_SUFFIX beserta klausa BETWEEN. Misalnya, untuk menemukan suhu maksimum yang dilaporkan pada tahun-tahun antara 1929 dan 1935 secara inklusif, gunakan karakter pengganti tabel untuk mewakili dua digit terakhir tahun:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

Memindai rentang tabel berpartisi berdasarkan waktu penyerapan menggunakan _PARTITIONTIME

Untuk memindai berbagai tabel berpartisi berdasarkan waktu penyerapan, gunakan kolom semu _PARTITIONTIME dengan kolom semu _TABLE_SUFFIX. Misalnya, kueri berikut memindai partisi 1 Januari 2017 pada tabel my_dataset.mytable_id1.

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

Membuat kueri semua tabel dalam set data

Untuk memindai semua tabel dalam set data, Anda dapat menggunakan awalan kosong dan karakter pengganti tabel, yang berarti kolom semu _TABLE_SUFFIX berisi nama tabel lengkap. Misalnya, klausa FROM berikut memindai semua tabel dalam set data GSOD:

FROM
  `bigquery-public-data.noaa_gsod.*`

Dengan awalan kosong, kolom semu _TABLE_SUFFIX berisi nama tabel lengkap. Misalnya, kueri berikut setara dengan contoh sebelumnya yang menemukan suhu maksimum antara tahun 1929 dan 1935, tetapi menggunakan nama tabel lengkap dalam klausa WHERE:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

Namun, perhatikan bahwa awalan yang lebih panjang umumnya berperforma lebih baik. Untuk mengetahui informasi selengkapnya, baca Praktik terbaik.

Detail eksekusi kueri

Skema yang digunakan untuk evaluasi kueri

Untuk menjalankan kueri GoogleSQL yang menggunakan tabel karakter pengganti, BigQuery secara otomatis menyimpulkan skema untuk tabel tersebut. BigQuery menggunakan skema untuk tabel yang baru dibuat dan cocok dengan karakter pengganti sebagai skema untuk tabel karakter pengganti. Meskipun Anda membatasi jumlah tabel yang ingin digunakan dari tabel karakter pengganti menggunakan kolom semu _TABLE_SUFFIX dalam klausa WHERE, BigQuery akan menggunakan skema untuk tabel yang baru dibuat dan cocok dengan karakter pengganti.

Jika kolom dari skema yang disimpulkan tidak ada dalam tabel yang cocok, BigQuery akan menampilkan nilai NULL untuk kolom tersebut dalam baris untuk tabel yang tidak memiliki kolom.

Jika skema tidak konsisten di seluruh tabel yang cocok dengan kueri karakter pengganti, BigQuery akan menampilkan error. Hal ini terjadi jika kolom tabel yang cocok memiliki jenis data yang berbeda, atau jika kolom yang tidak ada di semua tabel yang cocok tidak dapat dianggap memiliki nilai null.

Praktik terbaik

Awalan yang lebih panjang umumnya berperforma lebih baik daripada awalan yang lebih pendek. Misalnya, kueri berikut menggunakan awalan panjang (gsod200):

#standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.gsod200*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '0' AND '1'
ORDER BY
max DESC

Kueri berikut umumnya berperforma lebih buruk karena menggunakan awalan kosong:

#standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
ORDER BY
max DESC

Sebaiknya partisi diutamakan daripada sharding karena tabel berpartisi berperforma lebih baik. Sharding akan mengurangi performa sekaligus membuat lebih banyak tabel untuk dikelola. Untuk informasi selengkapnya, lihat Partisi versus sharding.

Untuk praktik terbaik terkait mengontrol biaya di BigQuery, lihat Mengontrol biaya di BigQuery

Langkah selanjutnya

Untuk informasi selengkapnya tentang GoogleSQL, lihat referensi kueri GoogleSQL.