Big Query dan Cloud SQL

“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

• login ke cloud SQL via cloud shell

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

• Pada Cloud SQL klik Import

• 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.