GSP413

Ringkasan

BigQuery adalah database analisis yang terkelola sepenuhnya, tanpa pengoperasian (NoOps), dan hemat biaya dari 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 pada analisis data untuk menemukan insight yang bermakna.

Set data yang akan Anda gunakan adalah set data e-commerce yang memiliki jutaan kumpulan data Google Analytics dari Google Merchandise Store. Anda akan mempelajari kolom dan baris yang tersedia untuk mendapatkan insight.

Lab ini berfokus pada cara membuat tabel pelaporan baru menggunakan JOIN dan UNION SQL.

Skenario: Tim pemasaran memberi Anda dan tim data science semua ulasan produk untuk situs e-commerce Anda. Anda bekerja sama dengan mereka untuk membuat data warehouse di BigQuery yang menggabungkan data dari tiga sumber:

• Data e-commerce situs
• Tingkat stok inventaris produk dan lama pengerjaan
• Analisis sentimen ulasan produk

Yang akan Anda lakukan

Di lab ini, Anda akan mempelajari cara melakukan tugas berikut:

• Menjelajahi data e-commerce baru tentang analisis sentimen.
• Menggabungkan set data dan membuat tabel baru.
• Menambahkan data historis dengan union dan karakter pengganti tabel.

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 untuk menyimpan tabel

Untuk memulai, buat set data baru berjudul ecommerce di BigQuery untuk menyimpan tabel.

1. Di panel kiri, klik nama project BigQuery Anda (qwiklabs-gcp-xxxx).

2. Klik ikon tiga titik di samping nama project, lalu pilih Create dataset.

Dialog Create dataset akan terbuka.

3. Tetapkan Dataset ID ke ecommerce, biarkan semua opsi lain tetap pada nilai defaultnya.

4. Klik Create dataset.

Klik Check my progress untuk memverifikasi tujuan. Membuat set data baru untuk menyimpan tabel

Tugas 2. Menjelajahi set data sentimen produk

Tim data science telah menganalisis semua ulasan produk Anda melalui API serta memberikan skor sentimen rata-rata dan besarnya sentimen untuk setiap produk Anda.

Project dengan set data tim pemasaran Anda adalah data-to-insights. Set data publik BigQuery tidak ditampilkan secara default di BigQuery. Kueri di lab ini akan menggunakan set data data-to-insights meskipun Anda tidak dapat melihatnya.

1. Pertama, buat salinan tabel yang dibuatkan oleh tim data science agar Anda dapat membacanya:

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` Catatan: Ini hanya untuk tinjauan Anda. Kueri di lab ini akan menggunakan project data-to-insights.

2. Klik set data ecommerce untuk menampilkan tabel products.

Memeriksa data menggunakan tab Preview dan Schema

1. Buka set data ecommerce > products, lalu klik tab Preview untuk melihat data.

2. Klik tab Schema.

Membuat kueri yang menampilkan 5 produk teratas dengan sentimen paling positif

1. Di Editor Kueri, tulis kueri SQL Anda.

Kemungkinan solusi:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. Revisi kueri agar menampilkan 5 produk teratas dengan sentimen paling negatif dan mengecualikan nilai NULL.

Kemungkinan solusi:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

Apa produk dengan sentimen paling rendah?

Klik Check my progress untuk memverifikasi tujuan. Menjelajahi set data sentimen produk

Tugas 3. Menggabungkan set data untuk menemukan insight

Skenario: Pada awal bulan, tim inventaris memberi tahu Anda bahwa kolom orderedQuantity di set data inventaris produk perlu diperbarui. Mereka perlu bantuan Anda untuk mengkueri total penjualan per produk untuk 01/08/2017 dan membandingkannya dengan jumlah stok saat ini di inventaris untuk melihat produk mana yang perlu diisi ulang stoknya terlebih dahulu.

Menghitung volume penjualan harian berdasarkan productSKU

1. Buat tabel baru di set data ecommerce dengan persyaratan di bawah:

• Beri judul sales_by_sku_20170801
• Ambil data dari data-to-insights.ecommerce.all_sessions_raw
• Sertakan hanya hasil unik
• Tampilkan productSKU
• Tampilkan jumlah total yang dipesan (productQuantity). Petunjuk: Gunakan kondisi SUM() with a IFNULL
• Filter untuk penjualan pada 20170801 saja
• ORDER BY (Urutkan menurut) SKU dengan pesanan terbanyak ditampilkan lebih dahulu

Kemungkinan solusi:

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. Klik tabel sales_by_sku, lalu klik tab Preview.

Berapa banyak SKU produk unik yang terjual?

Jawaban: 462

Selanjutnya, perkaya data penjualan Anda dengan informasi inventaris produk dengan menggabungkan kedua set data.

Menggabungkan data penjualan dan data inventaris

1. Dengan JOIN, perkaya data e-commerce situs dengan kolom berikut dari set data inventaris produk:

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. Lengkapi kueri yang baru ditulis sebagian berikut:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

Kemungkinan solusi:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. Ubah kueri yang telah Anda tulis untuk menyertakan:

• Kolom kalkulasi (total_ordered / stockLevel) dan beri alias "ratio". Petunjuk: Gunakan SAFE_DIVIDE(field1,field2) untuk menghindari error pembagian dengan 0 saat jumlah stok 0.
• Filter hasil agar hanya menyertakan produk yang telah menghabiskan 50% atau lebih inventarisnya pada awal bulan

Kemungkinan solusi:

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

Klik Check my progress untuk memverifikasi tujuan. Menggabungkan set data untuk menemukan insight

Tugas 4. Menambahkan kumpulan data

Tim internasional telah melakukan penjualan di toko pada 02/08/2017, dan Anda ingin mencatatnya ke dalam tabel penjualan harian.

Membuat tabel kosong baru untuk menyimpan penjualan berdasarkan productSKU untuk 02/08/2017

1. Untuk skema ini, tentukan kolom berikut:

• nama tabel ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered sebagai kolom INT64

Kemungkinan solusi:

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. Konfirmasi bahwa sekarang Anda memiliki dua tabel penjualan bertanggal sama—gunakan menu dropdown di samping nama tabel Sales_by_sku dalam hasil tabel, atau refresh browser untuk melihat tabel tersebut tercantum di menu kiri:

3. Masukkan kumpulan data penjualan yang diberikan kepada Anda oleh tim penjualan:

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. Pastikan kumpulan data tersebut muncul dengan melihat pratinjau tabel—klik nama tabel untuk melihat hasil.

Menambahkan data historis secara gabungan

Ada beberapa cara untuk menambahkan data yang memiliki skema sama secara gabungan. Dua cara yang umum adalah menggunakan UNION dan karakter pengganti tabel.

• Union adalah operator SQL yang menggabungkan baris dari kumpulan hasil berbeda.
• Karakter pengganti tabel memungkinkan Anda mengkueri banyak tabel menggunakan pernyataan SQL ringkas. Tabel karakter pengganti hanya tersedia di SQL standar.

1. Tulis kueri UNION yang akan menghasilkan semua kumpulan data dari dua tabel di bawah:

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 Catatan: Perbedaan antara UNION dan UNION ALL adalah UNION tidak akan menyertakan kumpulan data duplikat.

Apa kelemahan memiliki banyak tabel penjualan harian? Anda harus menulis banyak pernyataan UNION yang dirangkai jadi satu.

Solusi yang lebih baik adalah menggunakan filter karakter pengganti tabel dan filter _TABLE_SUFFIX.

2. Tulis kueri yang menggunakan karakter pengganti tabel (*) untuk memilih semua kumpulan data dari ecommerce.sales_by_sku_ untuk tahun 2017.

Kemungkinan solusi:

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. Ubah kueri sebelumnya untuk menambahkan filter guna membatasi hasil sehingga untuk 02/08/2017 saja.

Kemungkinan solusi:

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' Catatan: Opsi lain yang bisa dipertimbangkan adalah membuat Tabel Berpartisi yang dapat menyerap data penjualan harian ke dalam partisi yang benar secara otomatis.

Klik Check my progress untuk memverifikasi tujuan. Menambahkan kumpulan data

Selamat!

Anda telah menjelajahi contoh data e-commerce dengan membuat tabel pelaporan, kemudian memanipulasi tabel virtual menggunakan JOIN dan UNION SQL.

Langkah berikutnya/Pelajari lebih lanjut

• Punya akun Google Analytics dan ingin mengkueri set data Anda sendiri di BigQuery? Ikuti panduan ekspor ini.
• Jika ingin mempelajari lebih lanjut sintaksis SQL untuk JOIN, lihat dokumentasi JOIN BigQuery.
• Coba Google Dataset Search sebagai referensi.
• Pelajari lab lain berikut:
  • Menjelajahi Data NCAA dengan BigQuery
  • Cloud Composer: Menyalin Tabel BigQuery di Berbagai Lokasi

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: 31 Oktober 2023

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.