Fungsi kueri gabungan

GoogleSQL untuk BigQuery mendukung fungsi kueri gabungan berikut.

Daftar fungsi

Nama Ringkasan
EXTERNAL_QUERY Menjalankan kueri pada database eksternal dan menampilkan hasilnya sebagai tabel sementara.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

Deskripsi

Menjalankan kueri pada database eksternal dan menampilkan hasilnya sebagai tabel sementara. Jenis data database eksternal dikonversi menjadi jenis data GoogleSQL dalam tabel hasil sementara dengan pemetaan jenis data ini.

  • external_database_query: Kueri yang akan dijalankan pada database eksternal.

  • connection_id: ID resource koneksi. Resource koneksi berisi setelan untuk koneksi antara database eksternal dan BigQuery. Jika Anda tidak memiliki project default yang dikonfigurasi, tambahkan project ID ke ID koneksi dalam format berikut:

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    Ganti kode berikut:

    • PROJECT_ID: Project ID.
    • LOCATION: Lokasi koneksi.
    • CONNECTION_ID: ID koneksi.

    Misalnya, projects/example-project/locations/us/connections/sql-bq. Untuk mengetahui informasi selengkapnya, lihat Membuat resource koneksi.

+ options: String opsional dari peta format JSON dengan key-value pair dari nama opsi dan nilai (keduanya peka huruf besar/kecil).

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

Catatan tambahan:

  • Fungsi EXTERNAL_QUERY biasanya digunakan dalam klausa FROM.
  • Anda dapat menggunakan fungsi EXTERNAL_QUERY() untuk mengakses metadata tentang database eksternal.
  • EXTERNAL_QUERY() tidak akan mengikuti urutan hasil kueri eksternal, meskipun kueri eksternal Anda menyertakan ORDER BY.

Jenis Data yang Ditampilkan

Tabel BigQuery

Contoh

Misalnya Anda memerlukan tanggal pesanan pertama agar setiap pelanggan disertakan dalam laporan. Data ini sekarang tidak ada di BigQuery, tetapi tersedia di database PostgreSQL operasional Anda di . Contoh kueri gabungan berikut melakukannya dan mencakup 3 bagian:

  1. Jalankan kueri eksternal SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id di database PostgreSQL operasional untuk mendapatkan tanggal pesanan pertama bagi setiap pelanggan melalui fungsi EXTERNAL_QUERY().
  2. Gabungkan tabel hasil kueri eksternal dengan tabel pelanggan di BigQuery menurut customer_id.
  3. Pilih informasi pelanggan dan tanggal pesanan pertama.
SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT customer_id, MIN(order_date) AS first_order_date
       FROM orders
       GROUP BY customer_id'''
  ) AS rq
  ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Anda dapat menggunakan fungsi EXTERNAL_QUERY() untuk mengkueri tabel information_schema guna mengakses metadata database, seperti mencantumkan semua tabel dalam database atau menampilkan skema tabel. Contoh kueri information_schema berikut berfungsi di MySQL dan PostgreSQL.

-- List all tables in a database.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM information_schema.tables'''
  );

-- List all columns in a table.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM information_schema.columns WHERE table_name='x';'''
  );

EXTERNAL_QUERY() tidak akan menerima urutan hasil kueri eksternal, meskipun kueri eksternal Anda menyertakan ORDER BY. Contoh kueri berikut mengurutkan baris berdasarkan ID pelanggan dalam database eksternal, tetapi BigQuery tidak akan menampilkan baris hasil dalam urutan tersebut.

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

Pemetaan tipe data

Saat Anda menjalankan kueri gabungan, data dari database eksternal akan dikonversi ke jenis GoogleSQL. Berikut adalah pemetaan jenis data dari MySQL ke BigQuery dan PostgreSQL ke BigQuery.

Hal-hal yang perlu diketahui tentang pemetaan:

  • Sebagian besar jenis data MySQL dapat dicocokkan dengan jenis data BigQuery yang sama, dengan beberapa pengecualian seperti decimal, timestamp, dan time.
  • PostgreSQL mendukung banyak jenis data non-standar yang tidak didukung di BigQuery, misalnya money, path, uuid, boxer, dan lainnya.
  • Jenis data numerik di MySQL dan PostgreSQL akan dipetakan ke nilai NUMERIC BigQuery secara default. Rentang nilai NUMERIC BigQuery lebih kecil daripada di MySQL dan PostgreSQL. Hal ini juga dapat dipetakan keBIGNUMERIC, FLOAT64, atau STRING dengan "default_type_for_decimal_columns" dalam opsi EXTERNAL_QUERY.

Penanganan error

Jika kueri eksternal Anda berisi jenis data yang tidak didukung di BigQuery, kueri akan langsung gagal. Anda dapat mentransmisikan jenis data yang tidak didukung ke jenis data MySQL / PostgreSQL lain yang didukung. Lihat jenis data yang tidak didukung untuk informasi cara melakukan transmisi lebih lanjut.

Pemetaan jenis MySQL ke BigQuery

Jenis MySQL Deskripsi MySQL Jenis BigQuery Perbedaan jenis
Bilangan Bulat
INT 4 byte, 2^32 - 1 INT64
TINYINT 1 byte, 2^8 - 1 INT64
SMALLINT 2 byte, 2^16 - 1 INT64
MEDIUMINT 3 byte, 2^24 - 1 INT64
BIGINT 8 byte, 2^64 - 1 INT64
UNSIGNED BIGINT 8 byte, 2^64 - 1 NUMERIC
Numerik persis
DECIMAL (M,D) Desimal diwakili oleh (M,D) dengan M adalah jumlah digit dan D adalah jumlah desimal. M <= 65 NUMERIC, BIGNUMERIC, FLOAT64, atau STRING

 DECIMAL (M,D) akan dipetakan ke NUMERIC secara default, atau dapat dipetakan ke BIGNUMERIC, FLOAT64, atau STRING dengan default_type_for_decimal_columns.
Perkiraan numerik
FLOAT (M,D) 4 byte, M <= 23 FLOAT64
DOUBLE (M,D) 8 byte, M <= 53 FLOAT64
Tanggal dan waktu
TIMESTAMP '1970-01-01 00:00:01'UTC hingga '2038-01-19 03:14:07' UTC. TIMESTAMP TIMESTAMP MySQL diambil sebagai zona waktu UTC di mana pun pengguna memanggil BigQuery
DATETIME '1000-01-01 00:00:00' hingga '9999-12-31 23:59:59' DATETIME
DATE '1000-01-01' hingga '9999-12-31'. DATE
TIME Waktu dalam format 'HH:MM:SS'
'-838:59:59' hingga '838:59:59'.		 WAKTU
 Rentang TIME BigQuery lebih kecil, dari 00:00:00 hingga 23:59:59
YEAR INT64
Karakter dan string
ENUM objek string dengan nilai yang dipilih dari daftar nilai yang diizinkan STRING
CHAR (M) String panjang tetap antara 1 hingga 255 karakter STRING
VARCHAR (M) String dengan panjang variabel antara 1 hingga 255 karakter. STRING
TEXT Kolom dengan panjang maksimum 65.535 karakter. STRING
TINYTEXT Kolom TEXT dengan panjang maksimum 255 karakter. STRING
MEDIUMTEXT Kolom TEXT dengan panjang maksimum 16.777.215 karakter. STRING
LONGTEXT Kolom TEXT dengan panjang maksimum 4.294.967.295 karakter. STRING
Biner
BLOB Objek besar biner dengan panjang maksimum 65.535 karakter. BYTES
MEDIUM_BLOB BLOB dengan panjang maksimum 16.777.215 karakter. BYTES
LONG_BLOB BLOB dengan panjang maksimum 4.294.967.295 karakter. BYTES
TINY_BLOB BLOB dengan panjang maksimum 255 karakter. BYTES
BINARY String biner dengan panjang tetap antara 1 hingga 255 karakter. BYTES
VARBINARY String biner dengan panjang variabel antara 1 hingga 255 karakter. BYTES
Lainnya
SET ketika mendeklarasikan kolom SET, tentukan beberapa nilai. Kemudian INSERT setiap kumpulan nilai yang telah ditentukan sebelumnya ke dalam kolom ini STRING
GEOMETRY GEOGRAPHY NOT YET SUPPORTED
BIT INT64 NOT YET SUPPORTED

Pemetaan jenis PostgreSQL ke BigQuery

Nama Deskripsi Jenis BigQuery Perbedaan jenis
Bilangan Bulat
smallint 2 byte, -32768 hingga +32767 INT64
smallserial Lihat smallint INT64
bilangan bulat 4 byte, -2147483648 hingga +2147483647 INT64
serial Lihat integer INT64
bigint 8 byte, -9223372036854775808 hingga 9223372036854775807 INT64
bigserial Lihat bigint INT64
Numerik persis
numerik [ (p, s) ] Presisi hingga 1.000. NUMERIC, BIGNUMERIC, FLOAT64, atau STRING numerik [ (p, s) ] akan dipetakan ke NUMERIC secara default, atau dapat dipetakan ke BIGNUMERIC, FLOAT64, atau STRING dengan default_type_for_decimal_columns.
Desimal [ (p, s) ] Lihat numerik NUMERIC Lihat numerik
uang 8 byte, skala 2 digit, -92233720368547758.08 hingga +92233720368547758.07 NOT SUPPORTED
Perkiraan numerik
real 4 byte, angka floating point presisi tunggal FLOAT64
presisi ganda 8 byte, angka floating point presisi ganda FLOAT64
Tanggal dan waktu
tanggal tanggal kalender (tahun, bulan, hari) DATE
waktu [ (p) ] [ tanpa zona waktu ] waktu (tanpa zona waktu) TIME
waktu [ (p) ] dengan zona waktu waktu dalam sehari, termasuk zona waktu NOT SUPPORTED
stempel waktu [ (p) ] [ tanpa zona waktu ] tanggal dan waktu (tanpa zona waktu) DATETIME
stempel waktu [ (p) ] dengan zona waktu tanggal dan waktu, termasuk zona waktu TIMESTAMP TIMESTAMP PostgreSQL diambil sebagai zona waktu UTC di mana pun pengguna memanggil BigQuery
interval Durasi waktu NOT SUPPORTED
Karakter dan string
karakter [ (n) ] string karakter dengan panjang tetap STRING
variasi karakter [ (n) ] string karakter dengan panjang variabel STRING
teks string karakter dengan panjang variabel STRING
Biner
bytea data biner ("array byte") BYTES
bit [ (n) ] string bit dengan panjang tetap BYTES
bit bervariasi [ (n) ] string bit dengan panjang variabel BYTES
Lainnya
boolean Boolean logis (true/false) BOOL
inet Alamat host IPv4 atau IPv6 NOT SUPPORTED
jalur jalur geometris pada bidang NOT SUPPORTED
pg_lsn Nomor Urutan Log PostgreSQL NOT SUPPORTED
point titik geometris pada bidang NOT SUPPORTED
polygon jalur geometris tertutup pada bidang NOT SUPPORTED
tsquery kueri penelusuran teks NOT SUPPORTED
tsvector dokumen penelusuran teks NOT SUPPORTED
txid_snapshot snapshot ID transaksi tingkat pengguna NOT SUPPORTED
uuid ID unik universal NOT SUPPORTED
xml data XML STRING
box kotak persegi panjang pada bidang NOT SUPPORTED
cidr Alamat jaringan IPv4 atau IPv6 NOT SUPPORTED
lingkaran lingkaran pada bidang NOT SUPPORTED
interval [ kolom ] [ (p) ] rentang waktu NOT SUPPORTED
json data JSON tekstual STRING
jsonb data JSON biner, terdekomposisi NOT SUPPORTED
baris garis tak terbatas pada bidang NOT SUPPORTED
lseg ruas garis pada bidang NOT SUPPORTED
macaddr Alamat MAC (Media Access Control) NOT SUPPORTED
macaddr8 Alamat MAC (Media Access Control) (format EUI-64) NOT SUPPORTED

Jenis data MySQL dan PostgreSQL yang tidak didukung

Jika kueri eksternal Anda berisi jenis data yang tidak didukung di BigQuery, kueri akan langsung gagal. Anda dapat mentransmisikan jenis data yang tidak didukung ke jenis data MySQL / PostgreSQL lain yang didukung.

  • Jenis data MySQL yang tidak didukung
    • Pesan error: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • Jenis yang tidak didukung: GEOMETRY, BIT
    • Penyelesaian:Transmisikan jenis data yang tidak didukung ke STRING.
    • Contoh: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); Perintah ini mentransmisikan jenis data yang tidak didukung GEOMETRY ke STRING.
  • Jenis data PostgreSQL yang tidak didukung
    • Pesan error: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Jenis yang tidak didukung: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • Penyelesaian:Transmisikan jenis data yang tidak didukung ke STRING.
    • Contoh: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); Perintah ini mentransmisikan jenis data yang tidak didukung money ke string.

Pemetaan jenis Spanner ke BigQuery

Saat Anda menjalankan kueri gabungan Spanner, data dari Spanner akan dikonversi ke jenis GoogleSQL.

Jenis Spanner GoogleSQL Jenis Spanner PostgreSQL Jenis BigQuery
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - Tidak didukung untuk kueri gabungan Spanner
TIMESTAMP timestamptz TIMESTAMP dengan nanodetik terpotong

* Nilai numerik PostgreSQL dengan presisi yang lebih besar daripada presisi yang didukung BigQuery dibulatkan. Nilai yang lebih besar dari nilai maksimum akan menghasilkan error Invalid NUMERIC value.

Jika kueri eksternal Anda berisi jenis data yang tidak didukung untuk kueri gabungan, kueri tersebut akan langsung gagal. Anda dapat mentransmisikan jenis data yang tidak didukung ke jenis data yang didukung.