Fungsi dan operator SQL yang dioptimalkan

BigQuery BI Engine mengoptimalkan fungsi dan operator GoogleSQL berikut saat menghubungkan alat business intelligence (BI) ke BigQuery menggunakan kueri atau tabel virtual kustom. Kueri yang menggunakan fungsi dan operator yang tidak ada dalam daftar ini mungkin berjalan lebih lambat.

Untuk mengetahui daftar fitur SQL yang tidak didukung, lihat bagian Fitur yang tidak didukung.

Untuk mengetahui informasi selengkapnya tentang penggunaan tabel virtual dan kueri kustom di Looker Studio, lihat Terhubung ke BigQuery. Untuk mengetahui informasi selengkapnya tentang cara menghubungkan alat BI lainnya ke BigQuery, lihat Ringkasan antarmuka SQL BigQuery BI Engine.

Untuk mengetahui daftar semua fungsi GoogleSQL, lihat Fungsi GoogleSQL. Untuk mengetahui daftar semua operator GoogleSQL, lihat Operator GoogleSQL.

Operator yang dioptimalkan

  • ORDER BY
  • LIMIT count [OFFSET skip_rows]
  • UNION ALL
  • HAVING
  • GROUP BY
  • WHERE
  • INNER, LEFT, dan RIGHT JOIN (dengan batas ukuran yang lebih kecil)

Fungsi yang dioptimalkan

Perbandingan

  • =
  • <>
  • <
  • <=
  • >
  • >=
  • BETWEEN
  • IN
  • LEAST
  • GREATEST
  • CASE
  • COALESCE
  • IF
  • ISNULL
  • IS [NOT] NULL
  • IS [NOT] TRUE
  • IS [NOT] FALSE
  • IFNULL
  • NULLIF

Logis

  • NOT
  • AND
  • OR

Matematika

  • - (Minus unary)
  • +
  • - (Minus biner)
  • *
  • /
  • IEEE_DIVIDE
  • DIV
  • MOD
  • ROUND
  • TRUNC
  • FLOOR
  • CEIL
  • ABS
  • SIGN
  • SQRT
  • EXP
  • POW
  • IS_INF
  • IS_NAN
  • SIN
  • SINH
  • ASIN
  • ASINH
  • COS
  • COSH
  • ACOS
  • ACOSH
  • TAN
  • TANH
  • ATAN
  • ATAN2
  • ATANH
  • LOG
  • LN
  • LOG10

Bitwise

  • BIT_COUNT
  • ~ (Bitwise NOT)
  • & (Bitwise AND)
  • | (Bitwise OR)
  • ^ (Bitwise XOR)
  • << (Bitwise left-shift)
  • >> (Bitwise right-shift)

String dan byte

  • ASCII
  • BYTE_LENGTH
  • CHAR_LENGTH
  • CHR
  • CODE_POINTS_TO_BYTES
  • CODE_POINTS_TO_STRING
  • CONCAT
  • ENDS_WITH
  • FORMAT
  • FROM_BASE32
  • FROM_BASE64
  • FROM_HEX
  • INITCAP
  • INSTR
  • JSON_EXTRACT_SCALAR
  • JSON_EXTRACT
  • LEFT
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • NORMALIZE
  • NORMALIZE_AND_CASEFOLD
  • REGEXP_CONTAINS
  • REGEXP_EXTRACT
  • REGEXP_INSTR
  • REGEXP_REPLACE
  • REPEAT
  • REPLACE
  • REVERSE
  • RIGHT
  • RPAD
  • RTRIM
  • SAFE_CONVERT_BYTES_TO_STRING
  • SOUNDEX
  • STARTS_WITH
  • STRING_LIKE
  • STRPOS
  • SUBSTR
  • TO_BASE32
  • TO_BASE64
  • TO_HEX
  • TRANSLATE
  • TRIM
  • UNICODE
  • UPPER

Stempel waktu, tanggal, dan waktu

  • Fungsi stempel waktu
    • TIMESTAMP_MICROS
    • TIMESTAMP_MILLIS
    • TIMESTAMP_SECONDS
    • TIMESTAMP
    • STRING(timestamp_expression,[timezone])
    • UNIX_SECONDS
    • UNIX_MILLIS
    • UNIX_MICROS
    • FORMAT_TIMESTAMP
    • PARSE_TIMESTAMP
    • TIMESTAMP_ADD
    • TIMESTAMP_SUB
    • TIMESTAMP_DIFF
    • EXTRACT
    • DATE
    • TIME
    • DATETIME
    • TIMESTAMP_TRUNC
  • Fungsi tanggal
    • DATE(year, month, day)
    • DATE_FROM_UNIX_DATE
    • UNIX_DATE
    • FORMAT_DATE
    • PARSE_DATE
    • DATE_ADD
    • DATE_SUB
    • DATE_DIFF
    • EXTRACT (from DATE)
    • DATE_TRUNC
    • CURRENT_TIMESTAMP
  • Fungsi waktu
    • EXTRACT
    • FORMAT_TIME
    • PARSE_TIME
    • TIME_ADD
    • TIME_SUB
    • TIME_DIFF
    • TIME_TRUNC
    • CURRENT_TIME
  • Fungsi tanggal dan waktu
    • DATETIME
    • EXTRACT
    • FORMAT_DATETIME
    • PARSE_DATETIME
    • DATETIME_ADD
    • DATETIME_SUB
    • DATETIME_DIFF
    • DATETIME_TRUNC
    • CURRENT_DATETIME

Hashing

  • FARM_FINGERPRINT
  • MD5
  • SHA1
  • SHA256
  • SHA512

Jaringan

  • NET_IP_IN_NET
  • NET_MAKE_NET
  • NET.HOST
  • NET.REG_DOMAIN
  • NET.PUBLIC_SUFFIX
  • NET.IP_FROM_STRING
  • NET.IP_TO_STRING
  • NET.IP_NET_MASK
  • NET.IP_TRUNC
  • NET.IPV4_FROM_INT64
  • NET.IPV4_TO_INT64

Geografi

  • Konstruktor
    • ST_GEOGPOINT
    • ST_MAKELINE
    • ST_MAKEPOLYGON
    • ST_MAKEPOLYGONORIENTED
  • Parser
    • ST_GEOGFROMGEOJSON
    • ST_GEOGFROMTEXT
    • ST_GEOGFROMWKB
    • ST_GEOGFROMWKB
    • ST_GEOGPOINTFROMGEOHASH
  • Formatter
    • ST_ASBINARY
    • ST_ASGEOJSON
    • ST_ASKML
    • ST_GEOHASH
  • Transformasi
    • ST_BOUNDARY
    • ST_CENTROID
    • ST_CLOSESTPOINT
    • ST_CONVEXHULL
    • ST_DIFFERENCE
    • ST_EXTERIORRING
    • ST_INTERSECTION
    • ST_SIMPLIFY
    • ST_SNAPTOGRID
  • Pengakses
    • ST_DIMENSION
    • ST_ENDPOINT
    • ST_GEOMETRYTYPE
    • ST_ISCOLLECTION
    • ST_ISEMPTY
    • ST_NPOINTS
    • ST_NUMGEOMETRIES
    • ST_NUMPOINTS
    • ST_POINTN
    • ST_STARTPOINT
    • ST_X
    • ST_Y
  • Predikat
    • ST_CONTAINS
    • ST_COVEREDBY
    • ST_COVERS
    • ST_DISJOINT
    • ST_DWITHIN
    • ST_EQUALS
    • ST_INTERSECTS
    • ST_INTERSECTSBOX
    • ST_TOUCHES
    • ST_WITHIN
  • Ukuran
    • ST_ANGLE
    • ST_AREA
    • ST_AZIMUTH
    • ST_DISTANCE
    • ST_LENGTH
    • ST_MAXDISTANCE
    • ST_PERIMETER

Fungsi SAFE

  • Matematika
    • MOD
    • ROUND
    • ABS
    • SQRT
    • POW
    • SINH
    • ASIN
    • COSH
    • ACOS
    • ACOSH
    • ATANH
    • LN
    • LOG
    • LOG10
    • FLOOR
    • CEIL
    • DIV
    • EXP
    • ROUND
    • SAFE_NEGATE
    • SAFE_ADD
    • SAFE_SUBTRACT
    • SAFE_MULTIPLY
    • SAFE_DIVIDE
  • String
    • REGEXP_CONTAINS
    • REGEXP_EXTRACT
    • REGEXP_REPLACE
    • FORMAT
    • SUBSTR
    • ASCII
    • SAFE_CONVERT_BYTES_TO_STRING
    • INSTR
    • LEFT
    • RIGHT
    • TRANSLATE
    • INITCAP
  • Stempel waktu, tanggal, dan waktu
    • DATE_FROM_UNIX_DATE
    • PARSE_DATE
    • PARSE_DATETIME
    • PARSE_TIME
    • PARSE_TIMESTAMP
    • FORMAT_TIMESTAMP
    • TIMESTAMP_SECONDS
    • TIMESTAMP_MILLIS
    • TIMESTAMP_MICROS
  • Jaringan
    • NET.SAFE_IP_FROM_STRING
  • Lainnya
    • SAFE_CAST

Fungsi agregasi

  • SUM
  • COUNT
  • COUNTIF
  • ANY_VALUE
  • AVG
  • MAX
  • MIN
  • HLL_COUNT.INIT
  • HLL_COUNT.MERGE
  • HLL_COUNT.MERGE_PARTIAL
  • Klausul GROUP BY
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • LOGICAL_AND
  • LOGICAL_OR
  • DISJUNCTION_AGG
  • STRING_AGG
  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • VAR_POP
  • VAR_SAMP
  • VARIANCE
  • APPROX_COUNT_DISTINCT

Fungsi jendela

Fungsi jendela, juga dikenal sebagai fungsi analisis, memiliki batasan berikut saat dipercepat oleh BigQuery BI Engine:

  • Tahap input dipercepat oleh BigQuery BI Engine jika tidak memiliki fungsi jendela. Dalam hal ini, tampilan INFORMATION_SCHEMA.JOBS melaporkan bi_engine_statistics.acceleration_mode sebagai FULL_INPUT.
  • Tahap input kueri dengan fungsi jendela di tahap inputnya dipercepat oleh BI Engine, tetapi tidak boleh memiliki batasan yang dijelaskan di bagian Batasan fungsi Jendela BI Engine. Dalam hal ini, tahap input atau kueri lengkap dijalankan di BI Engine. Dalam hal ini, tampilan INFORMATION_SCHEMA.JOBS melaporkan bi_engine_statistics.acceleration_mode sebagai FULL_INPUT atau FULL_QUERY.

Untuk mengetahui informasi selengkapnya tentang kolom BiEngineStatistics, lihat Referensi tugas.

Batasan fungsi Jendela BI Engine

Kueri dengan fungsi jendela hanya dijalankan di BI Engine jika semua kondisi berikut terpenuhi:

  • Kueri memindai tepat satu tabel.
    • Tabel tidak dipartisi.
    • Tabel ini memiliki kurang dari 5 juta baris.
  • Kueri tidak memiliki operator JOIN.
  • Ukuran tabel yang dipindai dikalikan dengan jumlah operator fungsi jendela tidak melebihi 300 MB. Dua fungsi jendela dengan klausa OVER yang identik dan input langsung yang sama dapat memiliki operator fungsi jendela yang sama. Contoh:
    • Kueri SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table hanya memiliki satu operator fungsi jendela.
    • Kueri SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table memiliki dua operator fungsi jendela karena kedua fungsi tersebut tidak memiliki klausa OVER yang sama.
    • Kueri SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) memiliki dua operator fungsi jendela karena kedua fungsi memiliki input langsung yang berbeda meskipun klausa OVER-nya terlihat sama.
  • Fungsi jendela yang direferensikan tercantum dalam daftar fungsi yang didukung berikut.

Daftar fungsi jendela yang didukung:

  • ANY_VALUE
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • CORR
  • COUNT
  • COUNTIF
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • ROW_NUMBER
  • ST_CLUSTERDBSCAN
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • STRING_AGG
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

Jika fungsi jendela tidak didukung, Anda mungkin melihat error berikut:

Fungsi analisis tidak kompatibel dengan operator lain atau inputnya terlalu besar

Fungsi lainnya

  • CAST
  • HLL_COUNT.EXTRACT
  • Operator titik untuk kolom STRUCT
  • ARRAY_LENGTH

Batasan

Batasan dan pembatasan berikut berlaku untuk dukungan operator dan fungsi SQL BI Engine.

Fitur BI Engine yang tidak didukung

Akselerasi BI Engine tidak tersedia untuk fitur berikut:

  • UDF JavaScript
  • Tabel eksternal
  • Membuat kueri data JSON native - Pesan error: Jenis native JSON tidak didukung
  • Menulis hasil pada tabel BigQuery permanen
  • Tabel yang berisi upsert melalui change data capture BigQuery
  • Transaksi
  • Kueri yang menampilkan lebih dari 1 GB data. Untuk aplikasi yang sensitif terhadap latensi, sebaiknya gunakan ukuran respons kurang dari 1 MB.

Dukungan Looker Studio terbatas

Fitur berikut memiliki dukungan terbatas di Looker Studio:

  • UDF SQL
  • Membuat kueri kolom ARRAY

Solusi untuk fitur yang tidak didukung

Meskipun beberapa fitur SQL tidak didukung di BigQuery BI Engine, ada solusi yang tersedia:

  1. Menulis kueri di BigQuery.
  2. Simpan hasil kueri ke tabel.
  3. Jadwalkan kueri Anda untuk memperbarui tabel secara rutin. Kecepatan refresh per jam atau hari adalah cara yang paling efektif, karena pembaruan setiap menit akan menolak cache BI Engine lebih sering.
  4. Referensikan tabel ini dalam kueri Anda yang mementingkan performa.