Membuat kueri data di BigQuery dari dalam JupyterLab

Membuat kueri data di BigQuery dari dalam JupyterLab

Halaman ini menunjukkan cara membuat kueri data yang tersimpan di BigQuery dari dalam antarmuka JupyterLab instance Vertex AI Workbench Anda.

Metode untuk membuat kueri data BigQuery dalam file notebook (IPYNB)

Untuk membuat kueri data BigQuery dari dalam file notebook JupyterLab, Anda dapat menggunakan perintah magic %%bigquery dan library klien BigQuery untuk Python.

Instance Vertex AI Workbench juga menyertakan integrasi BigQuery yang memungkinkan Anda menelusuri dan membuat kueri data dari dalam antarmuka JupyterLab.

Halaman ini menjelaskan cara menggunakan masing-masing metode tersebut.

Sebelum memulai

Jika belum melakukannya, buat instance Vertex AI Workbench.

Peran yang diperlukan

Untuk memastikan akun layanan instance Anda memiliki izin yang diperlukan untuk mengkueri data di BigQuery, minta administrator Anda untuk memberikan peran IAM Service Usage Consumer kepada akun layanan instance Anda (roles/serviceusage.serviceUsageConsumer) di project. Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses.

Administrator Anda mungkin juga dapat memberikan izin yang diperlukan kepada akun layanan instance Anda melalui peran kustom atau peran yang telah ditetapkan.

Open JupyterLab

1. Di konsol Google Cloud, buka halaman Instance.

   Buka Instance

2. Di samping nama instance Vertex AI Workbench, klik Buka JupyterLab.

   Instance Vertex AI Workbench akan membuka JupyterLab.

Menelusuri resource BigQuery

Integrasi BigQuery menyediakan panel untuk menjelajahi resource BigQuery yang dapat Anda akses.

1. Di menu navigasi JupyterLab, klik  BigQuery di Notebooks.

   Panel BigQuery mencantumkan project dan set data yang tersedia, tempat Anda dapat melakukan tugas sebagai berikut:

   • Untuk melihat deskripsi set data, klik dua kali pada nama set data.
   • Untuk menampilkan tabel, tampilan, dan model set data, luaskan set data.
   • Untuk membuka deskripsi ringkasan sebagai tab di JupyterLab, klik dua kali pada tabel, tampilan, atau model.

   Catatan: Pada deskripsi ringkasan untuk tabel, klik tab Preview untuk melihat pratinjau data tabel. Gambar berikut menunjukkan pratinjau tabel international_top_terms yang ditemukan dalam set data google_trends di project bigquery-public-data:

   

Membuat kueri data menggunakan perintah magic %%bigquery

Di bagian ini, Anda akan menulis SQL langsung di sel notebook dan membaca data dari BigQuery ke dalam notebook Python.

Perintah magic yang menggunakan karakter persentase tunggal atau ganda (% atau %%) memungkinkan Anda menggunakan sintaksis minimal untuk berinteraksi dengan BigQuery dalam notebook. Library klien BigQuery untuk Python otomatis diinstal dalam instance Vertex AI Workbench. Di balik layar, perintah magic %%bigquery menggunakan library klien BigQuery agar Python dapat menjalankan kueri yang diberikan, mengonversi hasilnya menjadi DataFrame pandas, menyimpan hasil ke variabel secara opsional, dan kemudian menampilkan hasilnya.

Catatan: Mulai versi 1.26.0 paket Python google-cloud-bigquery, BigQuery Storage API digunakan secara default untuk mendownload hasil dari perintah magic %%bigquery.

1. Untuk membuka file notebook, pilih File > New > Notebook.

2. Dalam dialog Select Kernel, pilih Python 3, lalu klik Select.

   File IPYNB baru Anda akan terbuka.

3. Untuk mendapatkan jumlah region berdasarkan negara dalam set data international_top_terms, masukkan pernyataan berikut:

   %%bigquery
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code,
     country_name
   ORDER BY
     num_regions DESC;

4. Klik  Run cell.

   Outputnya mirip dengan hal berikut ini:

   Query complete after 0.07s: 100%|██████████| 4/4 [00:00<00:00, 1440.60query/s]
   Downloading: 100%|██████████| 41/41 [00:02><00:00, 20.21rows/s]
   ... country_code country_name num_regions 0 TR Turkey 81 1 TH Thailand 77 2 VN Vietnam 63 3 JP Japan 47 4 RO Romania 42 5 NG Nigeria 37 6 IN India 36 7 ID Indonesia 34 8 CO Colombia 33 9 MX Mexico 32 10 BR Brazil 27 11 EG Egypt 27 12 UA Ukraine 27 13 CH Switzerland 26 14 AR Argentina 24 15 FR France 22 16 SE Sweden 21 17 HU Hungary 20 18 IT Italy 20 19 PT Portugal 20 20 NO Norway 19 21 FI Finland 18 22 NZ New Zealand 17 23 PH Philippines 17>

   Catatan: Hasil Anda mungkin berbeda dari yang disebutkan di atas karena set data google_trends yang dikueri akan diperbarui dengan data baru secara berkelanjutan.

5. Pada sel berikutnya (di bawah output dari sel sebelumnya), masukkan perintah berikut untuk menjalankan kueri yang sama, tetapi kali ini simpan hasilnya ke DataFrame pandas baru yang bernama regions_by_country. Anda memberikan nama tersebut menggunakan argumen dengan perintah magic %%bigquery.

   %%bigquery regions_by_country
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code, country_name
   ORDER BY
     num_regions DESC;

   Catatan: Untuk informasi selengkapnya tentang argumen yang tersedia untuk perintah %%bigquery, lihat dokumentasi keajaiban library klien.

6. Klik  Run cell.

7. Di sel berikutnya, masukkan perintah berikut untuk melihat beberapa baris pertama hasil kueri yang baru saja Anda baca:

   regions_by_country.head()
   

8. Klik  Run cell.

   DataFrame regions_by_country pandas siap dipetakan.

Membuat kueri data dengan langsung menggunakan library klien BigQuery

Di bagian ini, Anda akan langsung menggunakan library klien BigQuery untuk Python untuk membaca data ke notebook Python.

Library klien memberi Anda kontrol lebih besar atas kueri dan memungkinkan Anda menggunakan konfigurasi yang lebih kompleks untuk kueri dan tugas. Integrasi library dengan pandas memungkinkan Anda menggabungkan kecanggihan SQL deklaratif dengan kode imperatif (Python) untuk membantu Anda menganalisis, memvisualisasikan, dan mengubah data.

Catatan: Anda dapat menggunakan sejumlah library analisis data, data wrangling, dan visualisasi Python, seperti numpy, pandas, matplotlib, dan banyak lagi. Beberapa library ini dibangun di atas objek DataFrame.

1. Di sel berikutnya, masukkan kode Python berikut untuk mengimpor library klien BigQuery untuk Python dan melakukan inisialisasi klien:

   from google.cloud import bigquery
   
   client = bigquery.Client()
   

   Klien BigQuery digunakan untuk mengirim dan menerima pesan dari BigQuery API.

2. Klik  Run cell.

3. Di sel berikutnya, masukkan kode berikut untuk mengambil persentase istilah teratas harian di top_terms AS yang tumpang-tindih dengan istilah teratas dari beberapa hari sebelumnya. Intinya adalah melihat istilah teratas setiap hari dan mencari tahu berapa persen yang tumpang tindih dengan istilah teratas dari hari sebelumnya, 2 hari sebelumnya, 3 hari sebelumnya, dan seterusnya (untuk semua pasangan tanggal selama rentang waktu sekitar satu bulan).

   sql = """
   WITH
     TopTermsByDate AS (
       SELECT DISTINCT refresh_date AS date, term
       FROM `bigquery-public-data.google_trends.top_terms`
     ),
     DistinctDates AS (
       SELECT DISTINCT date
       FROM TopTermsByDate
     )
   SELECT
     DATE_DIFF(Dates2.date, Date1Terms.date, DAY)
       AS days_apart,
     COUNT(DISTINCT (Dates2.date || Date1Terms.date))
       AS num_date_pairs,
     COUNT(Date1Terms.term) AS num_date1_terms,
     SUM(IF(Date2Terms.term IS NOT NULL, 1, 0))
       AS overlap_terms,
     SAFE_DIVIDE(
       SUM(IF(Date2Terms.term IS NOT NULL, 1, 0)),
       COUNT(Date1Terms.term)
       ) AS pct_overlap_terms
   FROM
     TopTermsByDate AS Date1Terms
   CROSS JOIN
     DistinctDates AS Dates2
   LEFT JOIN
     TopTermsByDate AS Date2Terms
     ON
       Dates2.date = Date2Terms.date
       AND Date1Terms.term = Date2Terms.term
   WHERE
     Date1Terms.date <= Dates2.date
   GROUP BY
     days_apart
   
   ORDER BY
     days_apart;
   """
   pct_overlap_terms_by_days_apart = client.query(sql).to_dataframe()
   
   pct_overlap_terms_by_days_apart.head()

   SQL yang digunakan digabungkan dalam string Python, lalu diteruskan ke metode query() untuk menjalankan kueri. Metode to_dataframe menunggu kueri selesai dan mendownload hasilnya ke DataFrame pandas menggunakan BigQuery Storage API.

4. Klik  Run cell.

   Beberapa baris pertama hasil kueri muncul di bawah sel kode.

      days_apart   num_date_pairs  num_date1_terms overlap_terms   pct_overlap_terms
    0          0             32               800            800            1.000000
    1          1             31               775            203            0.261935
    2          2             30               750             73            0.097333
    3          3             29               725             31            0.042759
    4          4             28               700             23            0.032857
   

   Catatan: Hasil Anda mungkin berbeda dari yang disebutkan di atas karena set data google_trends yang dikueri akan diperbarui dengan data baru secara berkelanjutan.

Untuk mendapatkan informasi lebih lanjut tentang cara menggunakan library klien BigQuery, lihat panduan memulai Menggunakan library klien

Membuat kueri data menggunakan integrasi BigQuery di Vertex AI Workbench

Integrasi BigQuery menyediakan dua metode tambahan untuk membuat kueri data. Metode ini berbeda dengan menggunakan perintah magic %%bigquery.

• In-cell query editor adalah jenis sel yang dapat Anda gunakan dalam file notebook.

• Stand-alone query editor akan terbuka sebagai tab terpisah di JupyterLab.

Dalam sel

Untuk menggunakan editor kueri dalam sel guna membuat kueri data di tabel BigQuery, lakukan langkah-langkah berikut:

1. Di JupyterLab, buka file notebook (IPYNB) atau buat file baru.

2. Untuk membuat editor kueri dalam sel, klik sel, lalu di sebelah kanan sel, klik tombol  Integrasi BigQuery. Atau di sel markdown, masukkan #@BigQuery.

   Integrasi BigQuery mengubah sel menjadi editor kueri dalam sel.

3. Pada baris baru di bawah #@BigQuery, tulis kueri Anda menggunakan pernyataan yang didukung dan dialek SQL BigQuery. Jika error terdeteksi dalam kueri Anda, pesan error akan muncul di sudut kanan atas editor kueri. Jika kueri valid, perkiraan jumlah byte yang akan diproses akan muncul.

4. Klik Submit Query. Hasil kueri Anda akan muncul. Secara default, hasil kueri diberi nomor halaman pada 100 baris per halaman dan dibatasi hingga total 1.000 baris, tetapi Anda dapat mengubah setelan ini di bagian bawah tabel hasil. Di editor kueri, buat kueri yang terbatas hanya untuk data yang Anda perlukan untuk memverifikasi kueri. Anda akan menjalankan kueri ini lagi dalam sel notebook. Di sini, Anda dapat menyesuaikan batas untuk mengambil kumpulan hasil lengkap jika diinginkan.

   Catatan: Teks dan hasil kueri akan tetap ada setelah file notebook ditutup dan dibuka kembali.

5. Anda dapat mengklik Query and load as DataFrame untuk secara otomatis menambahkan sel baru berisi segmen kode yang mengimpor library klien BigQuery untuk Python, serta menjalankan kueri di sel notebook, dan menyimpan hasilnya dalam dataframe pandas bernama df.

Mandiri

Untuk menggunakan editor kueri mandiri guna membuat kueri data dalam tabel BigQuery, selesaikan langkah-langkah berikut:

1. Di JupyterLab, di panel BigQuery in Notebooks, klik kanan pada tabel, dan pilih Query table, atau klik dua kali pada tabel untuk membuka di tab terpisah, lalu klik link Query table.

2. Tulis kueri Anda menggunakan pernyataan yang didukung dan dialek SQL BigQuery. Jika error terdeteksi dalam kueri Anda, pesan error akan muncul di sudut kanan atas editor kueri. Jika kueri valid, perkiraan jumlah byte yang akan diproses akan muncul.

3. Klik Submit Query. Hasil kueri Anda akan muncul. Secara default, hasil kueri diberi nomor halaman pada 100 baris per halaman dan dibatasi hingga total 1.000 baris, tetapi Anda dapat mengubah setelan ini di bagian bawah tabel hasil. Di editor kueri, buat kueri yang terbatas hanya untuk data yang Anda perlukan untuk memverifikasi kueri. Anda akan menjalankan kueri ini lagi dalam sel notebook. Di sini, Anda dapat menyesuaikan batas untuk mengambil kumpulan hasil lengkap jika diinginkan.

4. Anda dapat mengklik Copy code for DataFrame untuk menyalin segmen kode yang mengimpor library klien BigQuery untuk Python, menjalankan kueri dalam sel notebook, dan menyimpan hasilnya di dataframe pandas bernama df. Tempel kode ini ke dalam sel notebook tempat Anda ingin menjalankannya.

Melihat histori kueri dan menggunakan ulang kueri

Untuk melihat histori kueri Anda sebagai tab di JupyterLab, lakukan langkah-langkah berikut:

1. Di menu navigasi JupyterLab, klik  BigQuery di Notebooks untuk membuka panel BigQuery.

2. Di panel BigQuery, scroll ke bawah, lalu klik Query history.

   

   Daftar kueri akan terbuka di tab baru, tempat Anda dapat melakukan tugas seperti berikut:

   • Untuk melihat detail kueri seperti ID pekerjaannya, kapan kueri dijalankan, dan berapa lama waktu yang diperlukan, klik kueri tersebut.
   • Untuk merevisi kueri, menjalankannya lagi, atau menyalinnya ke dalam notebook untuk digunakan di lain waktu, klik Open query in editor.

Catatan: Jika Anda menjalankan kueri setelah membuka tab ini, klik  Refresh untuk menampilkan kueri terbaru.

Langkah selanjutnya

• Untuk melihat contoh cara memvisualisasikan data dari tabel BigQuery, lihat Menjelajahi dan memvisualisasikan data di BigQuery dari dalam JupyterLab.

• Guna mempelajari lebih lanjut cara menulis kueri untuk BigQuery, baca Menjalankan tugas kueri batch dan interaktif.

• Mempelajari cara mengontrol akses ke set data BigQuery.