Kueri gabungan Spanner

Sebagai analis data, Anda dapat membuat kueri data di Spanner dari BigQuery menggunakan kueri gabungan.

Federasi BigQuery Spanner memungkinkan BigQuery membuat kueri data yang berada di Spanner secara real time, tanpa menyalin atau memindahkan data.

Anda dapat membuat kueri data Spanner dengan dua cara:

  • Membuat set data eksternal Spanner.
  • Menggunakan fungsi EXTERNAL_QUERY.

Memahami peran dan izin

Saat membuat kueri Spanner dari BigQuery, Anda akan menemukan dua jenis peran berbeda yang mengelola akses di tingkat yang berbeda.

  • Peran IAM: Peran ini mengatur akses ke Google Cloud resource, 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. Peran 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 Anda memberikan izin akun Anda dengan menetapkannya ke peran database Spanner tertentu (misalnya, dengan memberikan peran IAM roles/spanner.databaseRoleUser ke akun Anda pada resource peran database). Jika demikian, Anda harus 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 Anda 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 di 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 kondisi IAM.

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

Menggunakan set data eksternal

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

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

Menggunakan fungsi EXTERNAL_QUERY

Seperti database gabungan lainnya, Anda juga dapat membuat kueri Spanner data dengan EXTERNAL_QUERY fungsi. Hal ini mungkin berguna jika Anda ingin memiliki lebih banyak kontrol 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 adalah pengguna FGAC.
    • Jika Anda adalah pengguna kontrol akses terperinci:
      • Anda memerlukan peran IAM yang diperlukan untuk menggunakan FGAC. Peran ini biasanya adalah roles/spanner.fineGrainedAccessUser dan roles/spanner.databaseRoleUser. Peran ini 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 direferensikan 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) di 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 EXTERNAL_QUERY fungsi.

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 dan dengan demikian 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 operasi baca 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 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 membuat kueri tampilan 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

  • Di sisi BigQuery, harga kueri gabungan standar berlaku.
  • Di sisi Spanner, kueri dikenai harga Spanner.

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:

  • Network Intra-region Cross-Zone Data Transfer Out
  • Network Inter-Region Data Transfer Out to the Same Continent
  • Network Inter-Region Data Transfer Out to a Different Continent

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

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 BigQuery EU: 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 gabungan terdistribusi, atau rencana eksekusi Anda tidak boleh memiliki gabungan 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