Kueri gabungan Spanner

Kueri gabungan di BigQuery memungkinkan Anda membuat kueri data yang ada di Spanner secara real time tanpa menyalin atau memindahkan data. Anda dapat membuat kueri data Spanner dengan membuat set data eksternal Spanner atau menggunakan fungsi EXTERNAL_QUERY.

Memahami peran dan izin

Saat mengkueri Spanner dari BigQuery, Anda akan menemukan dua jenis peran berbeda yang mengelola akses di berbagai level.

  • Peran IAM: Peran ini mengatur akses ke resourceGoogle Cloud , termasuk instance dan database Spanner. Peran ini menentukan akun utama mana yang dapat mengakses layanan Spanner dan melakukan tindakan di tingkat instance atau database, seperti menghubungkan, membaca data, atau mengelola. Anda mengelola peran Identity and Access Management (IAM) melalui konsol IAM atau Google Cloud CLI. Contohnya mencakup roles/bigquery.connectionUser dan roles/spanner.databaseReader. Untuk mengetahui informasi selengkapnya, lihat peran IAM Spanner dan pemberian izin.

  • Peran database Spanner: Peran ini ditentukan di dalam database Spanner menggunakan pernyataan DDL seperti CREATE ROLE dan GRANT. Izin ini mengontrol akses terperinci ke objek skema tertentu, seperti tabel, kolom, dan tampilan, di dalam database. Hal ini merupakan bagian dari kontrol akses terperinci (FGAC). Anda menggunakan peran database jika organisasi Anda menerapkan FGAC untuk mengelola izin di dalam database.

Menentukan apakah Anda adalah pengguna FGAC

Untuk menentukan izin yang benar untuk diminta, Anda harus menentukan apakah Anda adalah pengguna FGAC. Untuk melakukannya, tanyakan kepada administrator database Spanner Anda apakah akses Anda ke database Spanner dikelola melalui kontrol akses terperinci.

Anda kemungkinan adalah pengguna FGAC jika administrator memberikan izin ke akun Anda dengan menetapkannya ke peran database Spanner tertentu (misalnya, dengan memberikan peran IAM roles/spanner.databaseRoleUser pada resource peran database ke akun Anda). Jika demikian, Anda perlu mengetahui nama peran database yang dapat Anda gunakan. Anda harus mengonfigurasi koneksi BigQuery untuk menggunakan salah satu peran database ini.

Anda kemungkinan bukan pengguna FGAC jika administrator memberikan peran IAM tingkat database yang lebih luas ke akun Anda, seperti roles/spanner.databaseReader. Dalam hal ini, Anda tidak perlu menggunakan peran database tertentu saat menghubungkan.

Membandingkan penerapan peran

Meskipun IAM mengontrol akses ke resource database itu sendiri, peran database Spanner mengontrol izin untuk objek dalam database tersebut.

Untuk menggunakan peran database FGAC, Anda biasanya memerlukan izin berikut:

  • Izin IAM spanner.databases.useRoleBasedAccess, yang sering diberikan melalui peran roles/spanner.fineGrainedAccessUser.
  • Izin untuk menggunakan peran database tertentu, yang diberikan melalui peran roles/spanner.databaseRoleUser dengan IAM Condition.

Untuk mengetahui informasi selengkapnya tentang cara menyiapkan izin ini, lihat Mengonfigurasi FGAC.

Menggunakan set data eksternal

Cara termudah untuk mengkueri tabel Spanner adalah dengan membuat set data eksternal. Setelah membuat set data eksternal, tabel dari database Spanner yang sesuai akan terlihat di BigQuery dan Anda dapat menggunakannya dalam kueri—misalnya, dalam join, union, atau subkueri. Namun, tidak ada data yang dipindahkan dari Spanner ke penyimpanan BigQuery.

Anda tidak perlu membuat koneksi untuk mengkueri data Spanner jika Anda membuat set data eksternal.

Menggunakan fungsi EXTERNAL_QUERY

Seperti database gabungan lainnya, Anda juga dapat membuat kueri data Spanner dengan fungsi EXTERNAL_QUERY. Hal ini mungkin berguna jika Anda ingin memiliki kontrol lebih besar atas parameter koneksi.

Sebelum memulai

  • Pastikan administrator BigQuery telah membuat koneksi Spanner dan membagikannya kepada Anda. Lihat Memilih koneksi yang tepat.
  • Untuk mendapatkan izin yang Anda perlukan untuk membuat kueri instance Spanner, minta administrator untuk memberi Anda peran IAM BigQuery Connection User (roles/bigquery.connectionUser) pada koneksi. Anda juga memerlukan izin yang sesuai di database Spanner yang bergantung pada apakah Anda pengguna FGAC.
    • Jika Anda adalah pengguna kontrol akses terperinci:
      • Anda memerlukan peran IAM yang diperlukan untuk menggunakan FGAC. Peran ini biasanya roles/spanner.fineGrainedAccessUser dan roles/spanner.databaseRoleUser. Peran tersebut digunakan dengan kondisi yang menentukan peran database.
      • Peran database Spanner yang Anda tentukan dalam koneksi harus memiliki hak istimewa SELECT pada semua objek skema yang dirujuk oleh kueri Anda. Administrator database Anda memberikan hak istimewa menggunakan pernyataan DDL GRANT (atau yang setara dengan PostgreSQL).
    • Jika Anda bukan pengguna kontrol akses terperinci, Anda memerlukan peran IAM Spanner Database Reader (roles/spanner.databaseReader) pada database.

    Untuk mengetahui informasi tentang cara memberikan peran IAM, lihat Mengelola akses ke project, folder, dan organisasi.

Memilih koneksi yang tepat

Jika Anda adalah pengguna kontrol akses terperinci Spanner, saat menjalankan kueri gabungan dengan fungsi EXTERNAL_QUERY, Anda harus menggunakan koneksi Spanner yang menentukan peran database. Peran database ini adalah bagian dari konfigurasi FGAC dalam database Spanner, terpisah dari peran IAM Anda. Kemudian, semua kueri yang Anda jalankan dengan koneksi ini akan menggunakan izin yang diberikan ke peran database tersebut.

Jika Anda menggunakan koneksi yang tidak menentukan peran database, Anda harus memiliki peran IAM yang ditunjukkan di bagian Sebelum memulai.

Data kueri

Untuk mengirim kueri gabungan ke Spanner dari kueri GoogleSQL, gunakan fungsi EXTERNAL_QUERY.

Merumuskan kueri Spanner di GoogleSQL atau PostgreSQL, bergantung pada dialek database yang ditentukan.

Contoh berikut membuat kueri gabungan ke database Spanner bernama orders dan menggabungkan hasilnya dengan tabel BigQuery bernama mydataset.customers:

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''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;

Spanner Data Boost

Data Boost adalah fitur serverless yang terkelola sepenuhnya dan menyediakan resource komputasi independen untuk workload Spanner yang didukung. Data Boost memungkinkan Anda menjalankan kueri analisis dan ekspor data dengan efek minimal pada workload yang ada di instance Spanner yang disediakan. Data Boost memungkinkan Anda menjalankan kueri gabungan dengan kapasitas komputasi independen yang terpisah dari instance yang disediakan agar tidak memengaruhi workload yang ada di Spanner. Data Boost paling berguna saat Anda menjalankan kueri ad hoc yang kompleks, atau saat ingin memproses data dalam jumlah besar tanpa memengaruhi workload Spanner yang ada. Menjalankan kueri gabungan dengan Data Boost dapat menyebabkan konsumsi CPU yang jauh lebih rendah, dan dalam beberapa kasus, latensi kueri yang lebih rendah.

Sebelum memulai

Untuk mendapatkan izin yang diperlukan untuk mengaktifkan akses ke Data Boost, minta administrator untuk memberi Anda peran IAM Cloud Spanner Database Reader with DataBoost (roles/spanner.databaseReaderWithDataBoost) di database Spanner. Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses ke project, folder, dan organisasi.

Peran bawaan ini memiliki izin spanner.databases.useDataBoost, yang diperlukan untuk mengaktifkan akses ke Data Boost.

Anda mungkin juga bisa mendapatkan izin ini dengan peran khusus atau peran bawaan lainnya.

Peran IAM roles/spanner.databaseReaderWithDataBoost memberikan izin untuk menggunakan Data Boost. Peran ini diperlukan selain izin dasar yang diperlukan untuk membaca data, seperti roles/spanner.databaseReader untuk pengguna non-FGAC atau izin kontrol akses terperinci yang sesuai.

Penggunaan set data eksternal dengan Spanner selalu menggunakan Data Boost, sehingga memerlukan izin spanner.databases.useDataBoost.

Mengaktifkan Data Boost

Saat menggunakan set data eksternal, Data Boost selalu digunakan dan Anda tidak perlu mengaktifkannya secara manual.

Jika ingin menggunakan Data Boost untuk kueri EXTERNAL_QUERY, Anda harus mengaktifkannya saat membuat koneksi yang digunakan oleh kueri Anda.

Membaca data secara paralel

Spanner dapat membagi kueri tertentu menjadi bagian yang lebih kecil, atau partisi, dan mengambil partisi secara paralel. Untuk mengetahui informasi selengkapnya, termasuk daftar batasan, lihat Membaca data secara paralel di dokumentasi Spanner.

Untuk melihat rencana eksekusi kueri untuk kueri Spanner, lihat Memahami cara Spanner menjalankan kueri.

Saat menjalankan kueri gabungan dengan set data eksternal, opsi "Baca data secara paralel" selalu digunakan.

Untuk mengaktifkan pembacaan paralel saat menggunakan EXTERNAL_QUERY, aktifkan saat Anda membuat koneksi.

Mengelola prioritas eksekusi kueri

Saat menjalankan kueri gabungan dengan fungsi EXTERNAL_QUERY, Anda dapat menetapkan prioritas (high, medium, atau low) untuk setiap kueri dengan menentukan opsi query_execution_priority:

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

Prioritas defaultnya adalah medium.

Kueri dengan prioritas high bersaing dengan traffic transaksi. Kueri dengan prioritas low merupakan upaya terbaik, dan mungkin di-preempt oleh pemuatan latar belakang, misalnya, pencadangan terjadwal.

Saat menjalankan kueri gabungan dengan set data eksternal, semua kueri selalu memiliki prioritas medium.

Melihat skema tabel Spanner

Jika Anda menggunakan set data eksternal, tabel Spanner Anda akan terlihat langsung di BigQuery Studio, dan Anda dapat melihat skemanya.

Namun, Anda juga dapat melihat skema tanpa menentukan set data eksternal. Anda juga dapat menggunakan fungsi EXTERNAL_QUERY untuk mengkueri tabel virtual information_schema guna mengakses metadata database. Contoh berikut menampilkan informasi tentang kolom dalam tabel MyTable:

Database Google SQL 

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

Database PostgreSQL 

SELECT * from EXTERNAL_QUERY(
'my-project.us.postgresql-example-db',
'''SELECT t.column_name, t.data_type, t.is_nullable
   FROM information_schema.columns AS t
   WHERE t.table_schema = 'public' AND t.table_name = 'MyTable'
   ORDER BY t.ordinal_position''');

Untuk mengetahui informasi selengkapnya, lihat referensi skema informasi berikut di dokumentasi Spanner:

Harga

Kueri lintas region

BigQuery mendukung kueri gabungan saat instance Spanner dan set data BigQuery berada di region yang berbeda. Kueri ini dikenai biaya transfer data Spanner tambahan. Untuk mengetahui informasi selengkapnya, lihat Harga Spanner.

Anda akan dikenai biaya untuk transfer data, berdasarkan SKU berikut:

  • Transfer Data Keluar Lintas Zona Intra-Region Jaringan
  • Transfer Data Keluar Jaringan Antar-Region ke Benua yang Sama
  • Transfer Data Keluar Antar-Region Jaringan ke Benua yang Berbeda

Transfer data dikenai biaya berdasarkan region BigQuery tempat Anda menjalankan kueri dan region Spanner terdekat yang memiliki replika baca-tulis atau hanya baca.

Untuk konfigurasi multi-region BigQuery (US atau EU), biaya transfer data dari Spanner ditentukan sebagai berikut:

  • Multi-region BigQuery US: Region Spanner us-central1
  • Multi-region EU BigQuery: Region Spanner europe-west1

Contoh:

  • BigQuery (multi-region US) dan Spanner (us-central1): Biaya berlaku untuk transfer data dalam region yang sama.
  • BigQuery (multi-region US) dan Spanner (us-west4): Biaya berlaku untuk transfer data antar-region dalam benua yang sama.

Pemecahan masalah

Bagian ini membantu Anda memecahkan masalah yang mungkin Anda alami saat mengirim kueri gabungan ke Spanner.

Masalah: Kueri tidak dapat dipartisi root.
Penyelesaian: Jika Anda mengonfigurasi koneksi untuk membaca data secara paralel, operator pertama dalam rencana eksekusi kueri harus berupa penggabungan terdistribusi, atau rencana eksekusi Anda tidak boleh memiliki penggabungan terdistribusi. Untuk mengatasi error ini, lihat rencana eksekusi kueri dan tulis ulang kueri tersebut. Untuk mengetahui informasi selengkapnya, lihat Memahami cara Spanner menjalankan kueri.
Masalah: Batas waktu terlampaui.
Penyelesaian: Pilih opsi untuk membaca data secara paralel dan menulis ulang kueri agar dapat dipartisi root. Untuk mengetahui informasi selengkapnya, lihat Memahami cara Spanner menjalankan kueri.

Langkah berikutnya