Friday, May 27, 2022
BigQuery Cloud SQL GCP

Big Query dan Cloud SQL

BigQuery

“Big Query dan Cloud SQL

Pengantar

SQL (Bahasa Kueri Terstruktur) adalah bahasa standar untuk operasi data yang memungkinkan Anda mengajukan pertanyaan dan mendapatkan wawasan dari kumpulan data terstruktur. Ini biasanya digunakan dalam manajemen basis data dan memungkinkan Anda melakukan tugas seperti penulisan catatan transaksi ke dalam basis data relasional dan analisis data skala petabyte.

Membuat BigQuery

  • Buka Navigation menu > BigQuery

Uploading queryable data

  • Klik + ADD DATA -> klik Explore public datasets
  • Di search bar cari “london” -> Enter
  • Pilih London Bicycle Hires -> klik View dataset
  • nanti akan terbentuk project baru -> bigquery-public-data
  • Klik bigquery-public-data > london_bicycles >cycle_hire
  • klik Preview

Perintah SELECT, FROM, dan WHERE pada BigQuery

  • Buka editor
SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;
  • Klik Run
  • Lalu Coba juga command berikut
SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;
  • Klik Run

Perintah GROUP BY, COUNT, AS, dan ORDER BY

GROUP BY

  • Pada Editor jalankan perintah berikut
SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;
  • klik Run

COUNT

  • jalankan perintah berikut
SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;
  • Klik Run

AS

  • Jalankan Perintah berikut
SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;
  • Klik Run

ORDER BY

  • Jalankan Perintah berikut
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name;
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num;
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

Exporting queries as CSV files

  • kembali ke BigQuery
  • Jalankan perintah berikut
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;
  • Klik SAVE RESULTS > CSV(local file) > SAVE
  • rename jadi start_station_data.csv
  • Jalankan lagi perintah berikut
SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;
  • Klik SAVE RESULTS > CSV(local file) > SAVE
  • Rename file jadi end_station_data.csv

Upload CSV files ke Cloud Storage

  • Buka Navigation menu > Cloud Storage > Browser -> klik Create bucket
  • Buat nama qwiklabs-gcp-03-118b93c181c7
  • klik Create
  • Upload ke 2 file csv yang sebelum nya di download

Buat Cloud SQL instance

  • Masuk Navigation menu > SQL > CREATE INSTANCE
  • Isi dengan nama qwiklabs-demo dan password
  • Klik Create Instance

Tambahkan Query Cloud SQL

gcloud sql connect  qwiklabs-demo --user=root
  • Add Database
CREATE DATABASE bike;
  • Tambahkan table
USE bike;
CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);
CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

Upload CSV files to tables

  • Isi Source , Db dan table
  • Klik Import
  • Lakukan upload juga pada end_station_data.csv , Database bike , table london2 -> import
  • Cek di cloud shell
SELECT * FROM london1;
SELECT * FROM london2;

Penutup

Sahabat Blog Learning & Doing demikianlah penjelasan mengenai Big Query dan Cloud SQL. Semoga Bermanfaat . Sampai ketemu lagi di postingan berikut nya.

(Visited 39 times, 1 visits today)
Klik untuk berbagi dengan orang lain
Baca Juga :  Building a VPN Between Google Cloud and AWS with Terraform

Similar Posts