GSP414

Ringkasan

BigQuery adalah database analisis berbiaya rendah dan NoOps yang dikelola sepenuhnya oleh Google. Dengan BigQuery, Anda dapat mengkueri data berskala terabyte tanpa perlu mengelola infrastruktur atau memiliki administrator database. BigQuery menggunakan SQL dan dapat memanfaatkan model bayar sesuai penggunaan. Dengan BigQuery, Anda dapat berfokus menganalisis data untuk menemukan insight yang bermakna.

Di lab ini, Anda akan mempelajari cara mengkueri dan membuat tabel berpartisi di BigQuery untuk meningkatkan performa kueri dan mengurangi penggunaan resource. Data di lab ini merupakan set data e-commerce yang memiliki jutaan data Google Analytics untuk Google Merchandise Store yang dimuat ke BigQuery.

Yang akan Anda lakukan

Di lab ini, Anda akan mempelajari cara:

• Mengkueri tabel berpartisi.
• Membuat tabel berpartisi Anda sendiri.

Penyiapan dan persyaratan

Sebelum mengklik tombol Mulai Lab

Baca petunjuk ini. Lab memiliki timer dan Anda tidak dapat menjedanya. Timer, yang dimulai saat Anda mengklik Start Lab, akan menampilkan durasi ketersediaan resource Google Cloud untuk Anda.

Lab praktik ini dapat Anda gunakan untuk melakukan sendiri aktivitas lab di lingkungan cloud sungguhan, bukan di lingkungan demo atau simulasi. Untuk mengakses lab ini, Anda akan diberi kredensial baru yang bersifat sementara dan dapat digunakan untuk login serta mengakses Google Cloud selama durasi lab.

Untuk menyelesaikan lab ini, Anda memerlukan:

• Akses ke browser internet standar (disarankan browser Chrome).

Catatan: Gunakan jendela Samaran atau browser pribadi untuk menjalankan lab ini. Hal ini akan mencegah konflik antara akun pribadi Anda dan akun Siswa yang dapat menyebabkan tagihan ekstra pada akun pribadi Anda.

• Waktu untuk menyelesaikan lab. Ingat, setelah dimulai, lab tidak dapat dijeda.

Catatan: Jika Anda sudah memiliki project atau akun pribadi Google Cloud, jangan menggunakannya untuk lab ini agar terhindar dari tagihan ekstra pada akun Anda.

Cara memulai lab dan login ke Google Cloud Console

1. Klik tombol Start Lab. Jika Anda perlu membayar lab, jendela pop-up akan terbuka untuk memilih metode pembayaran. Di sebelah kiri adalah panel Lab Details dengan info berikut:

   • Tombol Open Google Cloud console
   • Waktu tersisa
   • Kredensial sementara yang harus Anda gunakan untuk lab ini
   • Informasi lain, jika diperlukan, untuk menyelesaikan lab ini

2. Klik Open Google Cloud console (atau klik kanan dan pilih Open Link in Incognito Window jika Anda menjalankan browser Chrome).

   Lab akan menjalankan resource, lalu membuka tab lain yang menampilkan halaman Sign in.

   Tips: Atur tab di jendela terpisah secara berdampingan.

   Catatan: Jika Anda melihat dialog Choose an account, klik Use Another Account.

3. Jika perlu, salin Username di bawah dan tempel ke dialog Sign in.

   {{{user_0.username | "Username"}}}

   Anda juga dapat menemukan Username di panel Lab Details.

4. Klik Next.

5. Salin Password di bawah dan tempel ke dialog Welcome.

   {{{user_0.password | "Password"}}}

   Anda juga dapat menemukan Password di panel Lab Details.

6. Klik Next.

   Penting: Anda harus menggunakan kredensial yang diberikan lab. Jangan menggunakan kredensial akun Google Cloud Anda. Catatan: Menggunakan akun Google Cloud sendiri untuk lab ini dapat dikenai biaya tambahan.

7. Klik halaman berikutnya:

   • Setujui persyaratan dan ketentuan.
   • Jangan tambahkan opsi pemulihan atau autentikasi 2 langkah (karena ini akun sementara).
   • Jangan mendaftar uji coba gratis.

Setelah beberapa saat, Konsol Google Cloud akan terbuka di tab ini.

Catatan: Untuk melihat menu dengan daftar produk dan layanan Google Cloud, klik Navigation menu di kiri atas.

Membuka konsol BigQuery

1. Di Google Cloud Console, pilih Navigation menu > BigQuery.

Kotak pesan Welcome to BigQuery in the Cloud Console akan terbuka. Kotak pesan ini menyediakan link ke panduan memulai dan catatan rilis.

2. Klik Done.

Konsol BigQuery terbuka.

Tugas 1. Membuat set data baru

1. Pertama, Anda akan membuat set data untuk menyimpan tabel Anda.

2. Di panel Explorer, di dekat project ID Anda, klik View actions, lalu klik Create dataset.

3. Tetapkan Dataset ID ke ecommerce.

Gunakan nilai default untuk opsi lainnya (Data Location, Default table Expiration).

4. Klik Create dataset.

Klik Check my progress untuk memverifikasi tujuan.

Membuat set data bernama e-commerce

Tugas 2. Membuat tabel dengan partisi tanggal

Tabel berpartisi adalah tabel yang dibagi menjadi beberapa segmen yang disebut partisi, yang akan mempermudah pengelolaan dan kueri data Anda. Dengan membagi tabel besar menjadi partisi yang lebih kecil, Anda dapat meningkatkan performa kueri dan mengontrol biaya dengan mengurangi jumlah byte yang dibaca oleh kueri.

Sekarang, buat tabel baru dan ikat kolom tanggal atau stempel waktu sebagai partisi. Sebelum melakukannya, mari kita pelajari data di tabel yang tidak berpartisi.

Mengkueri analisis halaman web untuk sampel pengunjung pada tahun 2017

1. Klik + Compose new query dan tambahkan kueri di bawah ini:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

Sebelum menjalankan kueri, perhatikan jumlah total data yang akan diproses seperti yang ditunjukkan di sebelah ikon validator kueri: "Kueri ini akan memproses 1,74 GB data saat dijalankan".

2. Klik Run.

Kueri akan menampilkan 5 hasil.

Mengkueri analisis halaman web untuk sampel pengunjung pada tahun 2018

Mari kita ubah kueri untuk melihat pengunjung tahun 2018 sekarang.

1. Klik COMPOSE NEW QUERY untuk menghapus Query Editor, lalu tambahkan kueri baru ini. Perhatikan bahwa parameter WHERE date berubah jadi 20180708:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

Query Validator memberi tahu Anda berapa banyak data yang akan diproses kueri ini.

2. Klik Run.

Perhatikan bahwa kueri masih memproses 1,74 GB meskipun menampilkan 0 hasil. Mengapa demikian? Mesin kueri perlu memindai semua data di set data untuk melihat apakah data tersebut memenuhi kondisi pencocokan tanggal di klausa WHERE. Mesin tersebut harus memeriksa setiap data untuk membandingkan tanggal dengan kondisi ‘20180708'.

Sebagai tambahan, LIMIT 5 tidak mengurangi jumlah total data yang diproses, yang merupakan miskonsepsi umum.

Kasus penggunaan umum untuk tabel berpartisi tanggal

Memindai seluruh set data setiap saat untuk membandingkan baris dengan kondisi WHERE adalah tindakan yang sia-sia. Hal ini terutama berlaku jika Anda hanya memperhatikan data dalam periode waktu tertentu seperti:

• Semua transaksi selama setahun terakhir
• Semua interaksi pengunjung dalam 7 hari terakhir
• Semua produk yang terjual bulan lalu

Daripada memindai seluruh set data dan memfilter kolom tanggal seperti yang dilakukan pada kueri sebelumnya, sekarang siapkan tabel yang berpartisi tanggal. Hal ini memungkinkan Anda untuk sepenuhnya mengabaikan pemindaian data di partisi tertentu jika tidak relevan dengan kueri kami.

Membuat tabel berpartisi baru berdasarkan tanggal

1. Klik COMPOSE NEW QUERY, tambahkan kueri bawah, lalu klik Run:

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

Di kueri ini, perhatikan opsi baru - PARTITION BY a field. Dua opsi yang tersedia untuk partisi adalah DATE dan TIMESTAMP. Fungsi PARSE_DATE digunakan pada kolom tanggal (disimpan sebagai string) guna mengubahnya dengan jenis DATE yang tepat untuk partisi.

2. Klik set data ecommerce, lalu pilih tabel partiton_by_day baru:

3. Klik tab Detail.

Pastikan Anda melihat:

• Partitioned by: Day
• Partitioning on: date_formatted

Catatan: Partisi dalam tabel yang dipartisi di akun lab Anda akan secara otomatis habis masa berlakunya 60 hari setelah nilai di kolom tanggal Anda. Akun Google Cloud pribadi Anda dengan penagihan diaktifkan memungkinkan Anda memiliki tabel berpartisi yang tidak memiliki tanggal habis masa berlaku. Untuk tujuan lab ini, kueri yang tersisa akan dijalankan terhadap tabel berpartisi yang sudah dibuat.

Klik Check my progress untuk memverifikasi tujuan.

Membuat tabel berpartisi baru berdasarkan tanggal

Tugas 3. Meninjau hasil dari kueri tabel berpartisi

1. Jalankan kueri di bawah ini, dan catat total byte yang akan diproses:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

Kali ini, 25 KB atau 0,025 MB sedang diproses, yang merupakan sebagian kecil dari apa yang telah Anda kueri.

2. Sekarang jalankan kueri di bawah, dan catat total byte yang akan diproses:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

Anda akan melihat This query will process 0 B when run.

Tugas 4. Membuat tabel berpartisi yang secara otomatis habis masa berlakunya

Tabel berpartisi yang secara otomatis habis masa berlakunya digunakan untuk mematuhi undang-undang privasi data, dan dapat digunakan untuk menghindari penyimpanan yang tidak diperlukan (yang akan dikenai biaya di lingkungan produksi). Jika Anda ingin membuat rolling window data, tambahkan tanggal habis masa berlaku sehingga partisi tersebut menghilang setelah Anda selesai menggunakannya.

Mempelajari tabel data cuaca NOAA yang tersedia

1. Pada menu kiri, di Explorer, klik + Add dan pilih Public datasets.

2. Cari GSOD NOAA lalu pilih set data.

3. Klik View Dataset.

4. Scroll tabel di set data noaa_gsod (yang di-sharding secara manual dan tidak dipartisi):

Tujuan Anda adalah membuat tabel yang:

• Mengkueri data cuaca dari tahun 2018 dan seterusnya
• Memfilter untuk hanya menyertakan hari dengan curah hujan (hujan, salju, dll.)
• Hanya menyimpan setiap partisi data selama 90 hari sejak tanggal partisi (rolling window)

1. Pertama, salin dan tempel kueri bawah ini:

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10 Catatan: Karakter pengganti tabel * digunakan di klausa FROM untuk membatasi jumlah tabel yang tertera dalam filter TABLE_SUFFIX. Catatan: Meskipun LIMIT 10 ditambahkan, hal ini tetap tidak mengurangi jumlah total data yang dipindai (sekitar 1,83 GB) karena belum ada partisi.

2. Klik Run.

3. Pastikan tanggal diformat dengan tepat dan kolom curah hujan menunjukkan nilai yang bukan nol.

Tugas 5. Giliran Anda: membuat tabel berpartisi

• Ubah kueri sebelumnya untuk membuat tabel dengan spesifikasi di bawah ini:

  • Nama tabel: ecommerce.days_with_rain
  • Gunakan kolom tanggal sebagai PARTITION BY Anda
  • Untuk OPTIONS, tentukan partition_expiration_days = 60
  • Tambahkan deskripsi tabel = "weather stations with precipitation, partitioned by day"

Kueri Anda akan terlihat seperti ini:

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2018'

Klik Check my progress untuk memverifikasi tujuan.

Giliran Anda: Membuat Tabel Berpartisi

Pastikan masa berlaku partisi data berfungsi

Untuk memastikan Anda hanya menyimpan data dari 60 hari yang lalu hingga hari ini, jalankan kueri DATE_DIFF untuk melihat usia partisi, yang akan habis masa berlakunya setelah 60 hari.

Di bawah ini terdapat kueri yang melacak rata-rata curah hujan untuk stasiun cuaca NOAA di Wakayama, Jepang yang memiliki curah hujan signifikan.

• Tambahkan kueri ini dan jalankan:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

Tugas 6. Mengonfirmasi bahwa partition_age terlama berusia 60 hari atau kurang.

Update klausa ORDER BY untuk menunjukkan partisi terlama terlebih dahulu.

• Tambahkan kueri ini dan jalankan:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Catatan: Hasil akan bervariasi jika Anda menjalankan kembali kueri tersebut di masa mendatang, karena data cuaca dan partisi Anda terus diperbarui.

Selamat!

Anda telah berhasil membuat dan mengkueri tabel berpartisi di BigQuery.

Langkah berikutnya/Pelajari lebih lanjut

• Jika Anda ingin mengetahui cara membuat tabel berpartisi berdasarkan waktu penyerapan yang tidak terikat dengan kolom tanggal atau stempel waktu tertentu, lihat dokumentasi Partisi BigQuery dan contohnya.
• Punya akun Google Analytics dan ingin melakukan kueri set data Anda sendiri di BigQuery? Ikuti panduan ekspor ini.
• Pelajari BigQuery lainnya ini:
  • Pemecahan Masalah dan Menyelesaikan Kendala Penggabungan Data
  • Bekerja dengan JSON, Array, dan Struct di BigQuery
  • Memprediksi Tarif Taksi dengan Model Perkiraan BigQuery ML

Sertifikasi dan pelatihan Google Cloud

...membantu Anda mengoptimalkan teknologi Google Cloud. Kelas kami mencakup keterampilan teknis dan praktik terbaik untuk membantu Anda memahami dengan cepat dan melanjutkan proses pembelajaran. Kami menawarkan pelatihan tingkat dasar hingga lanjutan dengan opsi on demand, live, dan virtual untuk menyesuaikan dengan jadwal Anda yang sibuk. Sertifikasi membantu Anda memvalidasi dan membuktikan keterampilan serta keahlian Anda dalam teknologi Google Cloud.

Manual Terakhir Diperbarui: 3 Februari 2024

Lab Terakhir Diuji: 1 Januari 2024

Hak cipta 2024 Google LLC Semua hak dilindungi undang-undang. Google dan logo Google adalah merek dagang dari Google LLC. Semua nama perusahaan dan produk lain mungkin adalah merek dagang masing-masing perusahaan yang bersangkutan.