Partisi dan Pengelompokan di BigQuery

1. Pengantar

BigQuery adalah data warehouse perusahaan yang terkelola sepenuhnya, berskala petabyte, dan rendah biaya untuk analisis. BigQuery bersifat serverless. Anda tidak perlu menyiapkan dan mengelola cluster.

Set data BigQuery berada di project GCP dan berisi satu atau beberapa tabel. Anda dapat melakukan kueri {i>dataset<i} ini dengan SQL.

Dalam codelab ini, Anda akan menggunakan UI web BigQuery di GCP Console untuk memahami partisi dan pengelompokan di BigQuery. Partisi dan pengelompokan tabel BigQuery membantu menata data Anda agar sesuai dengan pola akses data umum. Partisi dan pengelompokan adalah kunci untuk sepenuhnya memaksimalkan performa dan biaya BigQuery saat membuat kueri pada rentang data tertentu. Ini menghasilkan pemindaian lebih sedikit data per kueri, dan {i>pruning<i} ditentukan sebelum waktu mulai kueri.

Untuk mengetahui informasi lebih lanjut tentang BigQuery, baca dokumentasi BigQuery.

Yang akan Anda pelajari

  • Cara membuat dan mengkueri tabel yang dipartisi dan dikelompokkan
  • Membandingkan performa kueri dengan tabel yang dipartisi dan dikelompokkan

Yang Anda butuhkan

Untuk menyelesaikan lab ini, Anda memerlukan:

  • Versi terbaru Google Chrome
  • Akun penagihan Google Cloud Platform

2. Mempersiapkan

Untuk menggunakan BigQuery, Anda perlu membuat project GCP atau memilih project yang sudah ada.

Membuat project

Untuk membuat project baru, ikuti langkah-langkah berikut:

  1. Jika Anda belum memiliki Akun Google (Gmail atau Aplikasi Google), buat akun.
  2. Login ke konsol Google Cloud Platform ( console.cloud.google.com) dan buat project baru.
  3. Jika Anda belum memiliki project, klik tombol buat project:

870a3cbd6541ee86.png

Jika tidak, buat project baru dari menu pemilihan project:

f6dff3437a20cf2.png

  1. Masukkan nama project, lalu pilih Buat. Perlu diperhatikan bahwa project ID adalah nama unik di semua project Google Cloud.

1884405a64ce5765.pngS

3. Bekerja dengan {i>dataset<i} publik

BigQuery memungkinkan Anda bekerja dengan {i>dataset<i} publik, termasuk BBC News, repositori GitHub, Stack Overflow, dan {i>dataset<i} US National Oceanic and Atmospheric Administration (NOAA). Anda tidak perlu memuat {i>dataset<i} ini ke dalam BigQuery. Anda hanya perlu membuka {i>dataset<i} untuk menjelajahi dan melakukan kueri di BigQuery. Dalam codelab ini, Anda akan menggunakan set data publik Stack Overflow.

Menjelajahi set data Stack Overflow

{i>Dataset<i} Stack Overflow berisi informasi tentang postingan, tag, lencana, komentar, pengguna, dan banyak lagi. Untuk menjelajahi {i>dataset<i} Stack Overflow di UI web BigQuery, ikuti langkah-langkah berikut:

  1. Buka set data Stack Overflow. UI web BigQuery terbuka di Konsol GCP dan menampilkan informasi tentang set data Stackoverflow.
  2. Di panel navigasi , pilih bigquery-public-data. Menu akan meluas untuk menampilkan {i>dataset<i} publik. Setiap {i>dataset<i} terdiri dari satu tabel atau lebih.
  3. Scroll ke bawah, lalu pilih stackoverflow. Menu akan meluas untuk mencantumkan tabel di {i>dataset<i} Stack Overflow.
  4. Pilih badge guna melihat skema untuk tabel badge. Perhatikan nama kolom dalam tabel.
  5. Di atas nama Kolom, klik Pratinjau guna melihat contoh data untuk tabel badge.

Untuk informasi lebih lanjut tentang semua set data publik yang tersedia di BigQuery, lihat Set Data Publik Google BigQuery.

Mengkueri set data Stackoverflow

Menjelajahi {i>dataset<i} adalah cara yang baik untuk memahami data yang sedang Anda kerjakan, tetapi membuat kueri {i>dataset<i} adalah kelebihan BigQuery. Bagian ini menjelaskan cara menjalankan kueri BigQuery. Anda tidak perlu memahami SQL apa pun pada tahap ini. Anda dapat menyalin dan menempelkan kueri di bawah.

Untuk menjalankan kueri, selesaikan langkah-langkah berikut:

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.
  2. Di area teks Query editor, salin dan tempel kueri SQL berikut. BigQuery memvalidasi kueri dan UI web menampilkan tanda centang hijau di bawah area teks untuk menunjukkan bahwa sintaksis tersebut valid.
SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10
  1. Pilih Run. Kueri tersebut menampilkan jumlah postingan atau pertanyaan di Stack Overflow yang diposting setiap tahun.

4. Membuat Tabel Baru

Di bagian sebelumnya, Anda telah mengkueri {i>dataset<i} publik yang disediakan BigQuery untuk Anda. Di bagian ini, Anda akan membuat tabel baru di BigQuery dari tabel yang ada. Anda akan membuat tabel baru dengan sampel data yang diambil dari tabel posts_questions set data publik Stack Overflow, lalu membuat kueri tabel.

Membuat Set Data Baru

Untuk membuat dan memuat data tabel ke BigQuery, pertama-tama buat set data BigQuery untuk menyimpan data dengan menyelesaikan langkah-langkah berikut:

  1. Di panel navigasi GCP Console, pilih nama project yang dibuat sebagai bagian dari penyiapan.
  2. Di sisi kanan, di panel detail, pilih Create dataset.

acc6378c49622323.png

  1. Pada dialog Create dataset, untuk Dataset ID, ketik stackoverflow. Tetap gunakan setelan default untuk semua setelan lainnya, lalu klik OK.

7a2dfd8bcb8f259a.pngS

Membuat Tabel Baru dengan Postingan StackOverflow 2018

Sekarang setelah Anda membuat {i>dataset<i} BigQuery, Anda dapat membuat tabel baru di BigQuery. Untuk membuat tabel dengan data dari tabel yang ada, Anda akan melakukan kueri {i>dataset<i} posting Stack Overflow 2018 dan menulis hasilnya ke tabel baru, dengan menyelesaikan langkah-langkah berikut:

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.

9ca55f544e8da8bd.pngS

  1. Di area teks Query editor, salin dan tempel kueri SQL berikut untuk membuat tabel baru, yang merupakan pernyataan DDL.
CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Pilih Run. Kueri ini membuat tabel baru questions_2018 dalam set data stackoverflow di project Anda dengan data yang dihasilkan dari menjalankan kueri di set data Stack Overflow BigQuery bigquery-public-data.stackoverflow.posts_questions.

Membuat Kueri Tabel Baru dengan Postingan Stack Overflow 2018

Sekarang setelah Anda membuat tabel BigQuery, mari kita jalankan kueri untuk menampilkan postingan Stack Overflow dengan pertanyaan dan judul bersama dengan beberapa statistik lain seperti jumlah jawaban, komentar, tampilan, dan favorit. Selesaikan langkah berikut:

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.
  2. Di area teks Query editor, salin dan tempel kueri SQL berikut
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Pilih Run. Kueri tersebut menampilkan pertanyaan Stack Overflow yang dibuat pada bulan Januari 2018 yang diberi tag sebagai android bersama dengan pertanyaan itu dan beberapa statistik lainnya.
  2. Secara default, BigQuery menyimpan hasil kueri dalam cache. Jalankan kueri yang sama dan Anda akan melihat BigQuery memerlukan waktu lebih sedikit untuk menampilkan hasilnya karena BigQuery menampilkan hasil dari cache.
  3. Jalankan lagi kueri yang sama, tetapi kali ini cache BigQuery dinonaktifkan. Kami akan menonaktifkan cache di seluruh lab agar perbandingan performa dapat dibandingkan dengan tabel yang dipartisi dan dikelompokkan. Proses ini akan dijalankan di bagian berikutnya. Di editor kueri, klik Lainnya dan pilih Setelan kueri. Setelan kueri
  4. Di bagian Cache preferences, hapus centang Gunakan hasil yang di-cache. Opsi hasil dalam cache
  5. Di hasil kueri, Anda akan melihat waktu yang diperlukan hingga kueri selesai dan volume data yang diproses untuk mendapatkan hasil.

f197b022b4276338.png

5. Membuat dan Membuat Kueri Tabel yang Dipartisi

Di bagian sebelumnya, Anda membuat tabel baru di BigQuery dengan data dari tabel posts_questions menggunakan set data publik Stack Overflow. Kami telah mengkueri set data ini dengan penonaktifan cache dan mengamati performa kueri. Di bagian ini, Anda akan membuat tabel berpartisi baru dari tabel posts_questions set data publik Stack Overflow yang sama dan mengamati performa kueri.

Tabel berpartisi adalah tabel khusus yang dibagi menjadi beberapa segmen, yang disebut partisi, yang memudahkan pengelolaan dan kueri data Anda. Anda biasanya dapat membagi tabel besar menjadi banyak partisi yang lebih kecil menggunakan waktu penyerapan data atau kolom TIMESTAMP/DATE atau kolom INTEGER. Kita akan membuat tabel berpartisi DATE.

Pelajari tabel berpartisi lebih lanjut di sini.

Buat Tabel Berpartisi Baru dengan Postingan StackOverflow 2018

Untuk membuat tabel berpartisi dengan data dari tabel atau kueri yang ada, Anda akan melakukan kueri {i>dataset<i} postingan Stackoverflow 2018 dan menulis hasilnya ke tabel baru, selesaikan langkah-langkah berikut:

b9d0ca4df0881f58.png

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.

9ca55f544e8da8bd.pngS

  1. Di area teks Query editor, salin dan tempel kueri SQL berikut untuk membuat tabel baru, yang merupakan pernyataan DDL.
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Pilih Run. Kueri ini membuat tabel baru questions_2018_partitioned dalam set data stackoverflow di project Anda dengan data yang dihasilkan dari menjalankan kueri di set data Stack Overflow BigQuery bigquery-public-data.stackoverflow.posts_questions

Mengkueri Tabel yang Dipartisi dengan Postingan Stack Overflow 2018

Setelah Anda membuat tabel berpartisi BigQuery, mari jalankan kueri yang sama, kali ini pada tabel berpartisi, untuk menampilkan postingan Stack Overflow dengan pertanyaan dan judul beserta beberapa statistik lain seperti jumlah jawaban, komentar, tampilan, dan favorit. Selesaikan langkah berikut:

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.
  2. Di area teks Query editor, salin dan tempel kueri SQL berikut
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Pilih Run dengan cache BigQuery dinonaktifkan (lihat bagian sebelumnya untuk menonaktifkan cache BigQuery). Kueri tersebut menampilkan pertanyaan Stack Overflow yang dibuat pada bulan Januari 2018 yang diberi tag sebagai android bersama dengan pertanyaan itu dan beberapa statistik lainnya.
  2. Di hasil kueri, Anda akan melihat waktu yang diperlukan hingga kueri selesai dan volume data yang diproses untuk mendapatkan hasil.

ef01144374069823.png

Anda akan melihat bahwa performa kueri dengan tabel berpartisi lebih baik daripada tabel tanpa partisi karena BigQuery memangkas partisi, yaitu memindai hanya partisi yang diperlukan yang memproses lebih sedikit data dan berjalan lebih cepat. Tindakan ini akan mengoptimalkan biaya dan performa kueri.

6. Membuat dan Membuat Kueri Tabel Cluster

Di bagian sebelumnya, Anda membuat tabel berpartisi dalam BigQuery dengan data dari tabel posts_questions di set data publik Stack Overflow. Kami telah membuat kueri tabel ini dengan penonaktifan cache dan mengamati performa kueri dengan tabel yang tidak dipartisi dan dipartisi. Di bagian ini, Anda akan membuat tabel yang dikelompokkan baru dari tabel posts_questions set data publik Stack Overflow yang sama dan mengamati performa kueri.

Saat tabel dikelompokkan di BigQuery, data tabel secara otomatis diatur berdasarkan isi satu atau beberapa kolom dalam skema tabel. Kolom yang Anda tentukan digunakan untuk memindahkan data terkait. Ketika data ditulis ke tabel yang dikelompokkan, BigQuery akan mengurutkan data menggunakan nilai dalam kolom pengelompokan. Nilai-nilai ini digunakan untuk mengatur data menjadi beberapa blok dalam penyimpanan BigQuery. Urutan kolom yang dikelompokkan menentukan tata urutan data. Ketika data baru ditambahkan ke tabel atau partisi tertentu, BigQuery akan melakukan pengelompokan ulang secara otomatis di latar belakang untuk memulihkan properti sortir di tabel atau partisi.

Pelajari lebih lanjut cara menggunakan tabel yang dikelompokkan di sini.

Membuat Tabel Cluster Baru dengan Postingan Stack Overflow 2018

Di bagian ini, Anda akan membuat tabel baru yang dipartisi di creation_date dan dikelompokkan di kolom tags berdasarkan pola akses kueri. Untuk membuat tabel yang dikelompokkan dengan data dari tabel atau kueri yang ada, Anda akan melakukan kueri pada tabel postingan Stack Overflow 2018 dan menulis hasilnya ke tabel baru, dengan menyelesaikan langkah-langkah berikut:

e7d9acc0dc3b9d79.png

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.

9ca55f544e8da8bd.pngS

  1. Di area teks Query editor, salin dan tempel kueri SQL berikut untuk membuat tabel baru, yang merupakan pernyataan DDL.
#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Pilih Run. Kueri tersebut membuat tabel baru questions_2018_clustered dalam set data stackoverflow di project Anda dengan data yang dihasilkan dari menjalankan kueri pada tabel Stack Overflow BigQuerybigquery-public-data.stackoverflow.posts_questions. Tabel baru dipartisi pada creation_date dan dikelompokkan di kolom tag.

Membuat Kueri Tabel Cluster dengan Postingan Stack Overflow 2018

Setelah membuat tabel BigQuery yang dikelompokkan, mari jalankan kembali kueri yang sama, kali ini pada tabel yang dipartisi dan dikelompokkan, untuk menampilkan postingan Stack Overflow dengan pertanyaan dan judul beserta beberapa statistik lain seperti jumlah jawaban, komentar, tampilan, dan favorit. Selesaikan langkah berikut:

  1. Di dekat kanan atas konsol GCP, pilih Tulis kueri baru.
  2. Di area teks Query editor, salin dan tempel kueri SQL berikut
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Pilih Run dengan cache BigQuery dinonaktifkan (lihat bagian sebelumnya untuk menonaktifkan cache BigQuery). Kueri tersebut menampilkan pertanyaan Stack Overflow yang dibuat pada bulan Januari 2018 yang diberi tag sebagai android bersama dengan pertanyaan itu dan beberapa statistik lainnya.
  2. Di hasil kueri, Anda akan melihat waktu yang diperlukan hingga kueri selesai dan volume data yang diproses untuk mendapatkan hasil.

85e3c30d6fb3d547.pngS

Dengan tabel yang dipartisi dan dikelompokkan, kueri memindai lebih sedikit data daripada tabel yang dipartisi atau tabel yang tidak dipartisi. Cara data diatur dengan membuat partisi dan pengelompokan meminimalkan jumlah data yang dipindai oleh pekerja slot sehingga meningkatkan performa kueri dan mengoptimalkan biaya.

7. Pembersihan

Kecuali jika Anda berencana untuk terus menggunakan set data stackoverflow, Anda harus menghapusnya dan menghapus project yang Anda buat untuk codelab ini.

Menghapus set data BigQuery

Untuk menghapus set data BigQuery, lakukan langkah-langkah berikut:

  1. Pilih set data stackoverflow dari panel navigasi sebelah kiri di BigQuery .
  2. Di panel detail, pilih Delete dataset. 67b0f5cb740cb2ec.pngS
  3. Pada dialog Delete dataset, masukkan stackoverflow lalu pilih Delete untuk mengonfirmasi bahwa Anda ingin menghapus set data.

Menghapus project

Untuk menghapus project GCP yang Anda buat untuk codelab ini, lakukan langkah-langkah berikut:

  1. Pada menu navigasi GCP, pilih IAM & Admin.
  2. Di panel navigasi, pilih Setelan.
  3. Di panel detail, pastikan project Anda saat ini adalah project yang Anda buat untuk codelab ini, lalu pilih Shut down.
  4. Dalam dialog Shut down project, masukkan project ID (bukan nama project) untuk project Anda dan pilih Shut down untuk mengonfirmasi.

Selamat! Anda sekarang telah mempelajari

  • Cara menggunakan UI web BigQuery untuk membuat tabel baru dari tabel yang ada
  • Cara membuat dan mengkueri tabel yang dipartisi dan dikelompokkan
  • Cara partisi dan pengelompokan mengoptimalkan performa dan biaya kueri

Perhatikan bahwa Anda tidak perlu menyiapkan atau mengelola cluster untuk bekerja dengan set data.