Partisi dan Pengelompokan di BigQuery

1. Pengantar

BigQuery adalah data warehouse perusahaan berbiaya rendah, berskala petabyte, dan terkelola sepenuhnya 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 membuat 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 menyusun data Anda agar sesuai dengan pola akses data umum. Partisi dan pengelompokan adalah kunci untuk memaksimalkan performa dan biaya BigQuery saat membuat kueri selama rentang data tertentu. Hal ini menghasilkan pemindaian data yang lebih sedikit per kueri, dan pemangkasan ditentukan sebelum waktu mulai kueri.

Catatan: Selain UI web BigQuery, Anda dapat menggunakan alat command line bq untuk melakukan operasi pada set data BigQuery. Untuk mengetahui informasi selengkapnya tentang alat command line bq, lihat Alat Command Line bq.

Untuk mengetahui informasi selengkapnya tentang BigQuery, lihat dokumentasi BigQuery.

Yang akan Anda pelajari

• Cara membuat kueri tabel berpartisi dan dikelompokkan
• Membandingkan performa kueri dengan tabel berpartisi dan yang dikelompokkan

Yang Anda butuhkan

Untuk menyelesaikan lab ini, Anda memerlukan:

• Google Chrome versi terbaru
• 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 belum memiliki Akun Google (Gmail atau Google Apps), buat akun.
2. Login ke Konsol Google Cloud Platform ( console.cloud.google.com) dan buat project baru.
3. Jika Anda tidak memiliki project, klik tombol buat project:

Atau, buat project baru dari menu pemilihan project:

4. Masukkan nama project, lalu pilih Buat. Perhatikan project ID, yang merupakan nama unik di semua project Google Cloud.

3. Bekerja dengan set data publik

BigQuery memungkinkan Anda menggunakan set data publik, termasuk BBC News, repositori GitHub, Stack Overflow, dan set data National Oceanic and Atmospheric Administration (NOAA) AS. Anda tidak perlu memuat set data ini ke BigQuery. Anda hanya perlu membuka set data untuk menjelajahi dan membuat kueri di BigQuery. Dalam codelab ini, Anda akan menggunakan set data publik Stack Overflow.

Jelajahi set data Stack Overflow

Set data Stack Overflow berisi informasi tentang postingan, tag, badge, komentar, pengguna, dan lainnya. Untuk menjelajahi set data Stack Overflow di UI web BigQuery, ikuti langkah-langkah berikut:

1. Buka set data Stack Overflow. UI web BigQuery akan terbuka di GCP Console dan menampilkan informasi tentang set data Stackoverflow.
2. Di panel navigasi , pilih bigquery-public-data. Menu diperluas untuk mencantumkan set data publik. Setiap set data terdiri dari satu atau beberapa tabel.
3. Scroll ke bawah, lalu pilih stackoverflow. Menu akan diperluas untuk mencantumkan tabel dalam set data Stack Overflow.
4. Pilih badges untuk melihat skema tabel badge. Catat nama kolom dalam tabel.
5. Di atas Nama kolom, klik Pratinjau untuk melihat data sampel untuk tabel badge.

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

Mengkueri set data Stackoverflow

Menjelajahi set data adalah cara yang baik untuk memahami data yang sedang Anda kerjakan, tetapi mengkueri set data adalah keunggulan BigQuery. Bagian ini mengajarkan cara menjalankan kueri BigQuery. Anda tidak perlu mengetahui SQL apa pun saat ini. Anda dapat menyalin dan menempelkan kueri di bawah.

Untuk menjalankan kueri, selesaikan langkah-langkah berikut:

1. Di dekat kanan atas konsol GCP, pilih Compose new query.
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 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

3. Pilih Run. Kueri ini menampilkan jumlah postingan atau pertanyaan Stack Overflow yang diposting setiap tahun.

Catatan: Kueri ini menggunakan nama tabel lengkap untuk tabel posts_questions, termasuk nama project, nama set data, dan nama tabel: bigquery-public-data.stackoverflow.posts_questions.

4. Membuat Tabel Baru

Di bagian sebelumnya, Anda membuat kueri set data publik yang disediakan BigQuery untuk Anda. Di bagian ini, Anda akan membuat tabel baru di BigQuery dari tabel yang sudah ada. Anda akan membuat tabel baru dengan data yang diambil sampelnya dari tabel posts_questions set data publik Stack Overflow, lalu membuat kueri tabel tersebut.

Membuat Set Data Baru

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

1. Di panel navigasi konsol GCP, pilih nama project yang dibuat sebagai bagian dari penyiapan.
2. Di sisi kanan, di panel detail, pilih Buat set data.

3. Dalam dialog Buat set data, untuk ID Set Data, ketik stackoverflow. Tetap gunakan setelan default untuk semua setelan lainnya, lalu klik OK.

Membuat Tabel Baru dengan Postingan StackOverflow 2018

Setelah membuat set data BigQuery, Anda dapat membuat tabel baru di BigQuery. Untuk membuat tabel dengan data dari tabel yang ada, Anda akan membuat kueri pada set data postingan Stack Overflow 2018 dan menulis hasilnya ke tabel baru, dengan menyelesaikan langkah-langkah berikut:

1. Di dekat kanan atas konsol GCP, pilih Compose new query.

2. 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';

3. Pilih Run. Kueri ini membuat tabel questions_2018 baru dalam set data stackoverflow di project Anda dengan data yang dihasilkan dari menjalankan kueri pada set data BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Catatan: Langkah ini memerlukan waktu ~2 menit untuk membuat tabel baru. Tunggu sebelum melanjutkan ke langkah berikutnya.

Kueri Tabel Baru dengan Postingan Stack Overflow 2018

Setelah membuat tabel BigQuery, mari jalankan kueri untuk menampilkan postingan Stack Overflow dengan pertanyaan dan judul beserta beberapa statistik lainnya seperti jumlah jawaban, komentar, penayangan, dan favorit. Selesaikan langkah berikut:

1. Di dekat kanan atas konsol GCP, pilih Compose new query.
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';

3. Pilih Run. Kueri akan menampilkan pertanyaan Stack Overflow yang dibuat pada bulan Januari 2018 yang diberi tag android beserta pertanyaan dan beberapa statistik lainnya.
4. Secara default, BigQuery meng-cache hasil kueri. Jalankan kueri yang sama dan Anda akan melihat bahwa BigQuery membutuhkan waktu yang jauh lebih singkat untuk menampilkan hasilnya karena menampilkan hasil dari cache.
5. Jalankan kueri yang sama lagi, tetapi kali ini dengan penonaktifan caching BigQuery. Kita akan menonaktifkan cache untuk sisa lab agar adil dalam perbandingan performa terhadap tabel berpartisi dan berklaster, yang akan dijalankan di bagian berikutnya. Di editor kueri, klik Lainnya, lalu pilih Setelan kueri.
6. Di bagian Preferensi cache, hapus centang Gunakan hasil yang di-cache.
7. Di hasil kueri, Anda akan melihat waktu yang diperlukan untuk menyelesaikan kueri dan volume data yang diproses untuk mendapatkan hasilnya.

5. Membuat dan Mengkueri Tabel Berpartisi

Di bagian sebelumnya, Anda telah membuat tabel baru di BigQuery dengan data dari tabel posts_questions menggunakan set data publik Stack Overflow. Kami membuat kueri set data ini dengan penonaktifan caching dan mengamati performa kueri. Di bagian ini, Anda akan membuat tabel berpartisi baru dari tabel posts_questions di 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 akan mempermudah pengelolaan dan kueri data Anda. Anda biasanya dapat membagi tabel besar menjadi beberapa partisi yang lebih kecil menggunakan waktu penyerapan data atau kolom TIMESTAMP/DATE atau kolom INTEGER. Kita akan membuat tabel berpartisi DATE.

Pelajari lebih lanjut tabel berpartisi di sini.

Membuat Tabel Berpartisi Baru dengan Postingan StackOverflow 2018

Untuk membuat tabel berpartisi dengan data dari tabel atau kueri yang ada, Anda akan membuat kueri set data postingan Stack Overflow 2018 dan menulis hasilnya ke tabel baru. Selesaikan langkah-langkah berikut:

1. Di dekat kanan atas konsol GCP, pilih Compose new query.

2. 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';

3. Pilih Run. Kueri ini membuat tabel baru questions_2018_partitioned di set data stackoverflow dalam project Anda dengan data yang dihasilkan dari menjalankan kueri pada set data BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions

Catatan: Langkah ini memerlukan waktu ~2 menit untuk membuat tabel baru. Tunggu sebelum melanjutkan ke langkah berikutnya.

Mengkueri Tabel Berpartisi dengan Postingan Stack Overflow 2018

Setelah 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 lainnya seperti jumlah jawaban, komentar, penayangan, dan favorit. Selesaikan langkah berikut:

1. Di dekat kanan atas konsol GCP, pilih Compose new query.
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';

3. Pilih Jalankan dengan penonaktifan caching BigQuery (lihat bagian sebelumnya untuk menonaktifkan cache BigQuery). Kueri akan menampilkan pertanyaan Stack Overflow yang dibuat pada bulan Januari 2018 yang diberi tag android beserta pertanyaan dan beberapa statistik lainnya.
4. Di hasil kueri, Anda akan melihat waktu yang diperlukan untuk menyelesaikan kueri dan volume data yang diproses untuk mendapatkan hasilnya.

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

6. Membuat dan Membuat Kueri Tabel yang Dikelompokkan

Di bagian sebelumnya, Anda membuat tabel yang dipartisi di BigQuery dengan data dari tabel posts_questions di set data publik Stack Overflow. Kami membuat kueri tabel ini dengan menonaktifkan caching dan mengamati performa kueri dengan tabel yang tidak dipartisi dan tabel yang dipartisi. Di bagian ini, Anda akan membuat tabel berklaster baru dari tabel posts_questions di set data publik Stack Overflow yang sama dan mengamati performa kueri.

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

Pelajari lebih lanjut cara menggunakan tabel yang dikelompokkan di sini.

Membuat Tabel Berkelompok Baru dengan Postingan Stack Overflow 2018

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

1. Di dekat kanan atas konsol GCP, pilih Compose new query.

2. 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';

3. Pilih Run. Kueri ini membuat tabel baru questions_2018_clustered di set data stackoverflow dalam project Anda dengan data yang dihasilkan dari menjalankan kueri pada tabel BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. Tabel baru dipartisi berdasarkan creation_date dan dikelompokkan berdasarkan kolom tag.

Catatan: Langkah ini memerlukan waktu ~2 menit untuk membuat tabel baru. Tunggu sebelum melanjutkan ke langkah berikutnya.

Kueri Tabel Berkelompok dengan Postingan Stack Overflow 2018

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

1. Di dekat kanan atas konsol GCP, pilih Compose new query.
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';

3. Pilih Jalankan dengan penonaktifan caching BigQuery (lihat bagian sebelumnya untuk menonaktifkan cache BigQuery). Kueri akan menampilkan pertanyaan Stack Overflow yang dibuat pada bulan Januari 2018 yang diberi tag android beserta pertanyaan dan beberapa statistik lainnya.
4. Di hasil kueri, Anda akan melihat waktu yang diperlukan untuk menyelesaikan kueri dan volume data yang diproses untuk mendapatkan hasilnya.

Dengan tabel berpartisi dan dikelompokkan, kueri memindai lebih sedikit data daripada tabel berpartisi atau tabel yang tidak berpartisi. Cara data diatur berdasarkan partisi dan pengelompokan meminimalkan jumlah data yang dipindai oleh unit pemroses 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 Hapus set data.
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. Di menu navigasi GCP, pilih IAM & Admin.
2. Di panel navigasi, pilih Setelan.
3. Di panel detail, pastikan bahwa project saat ini adalah project yang Anda buat untuk codelab ini, lalu pilih Shut down.
4. Pada dialog Shut down project, masukkan project ID (bukan nama project) untuk project Anda, lalu 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 kueri tabel berpartisi 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.