สรุปคอร์ส : Exploring ​and ​Preparing ​your ​Data with BigQuery จาก Coursera

คอร์สนี้จะเป็นการแนะนำให้เรารู้จักและเตรียม Data ด้วยการใช้ BigQuery รวมถึงปัญหาและสิ่งที่ Data Analyst ต้องเจอ เรื่องการแก้ปัญหา BigData ด้วย Google Cloud Platform (GCP) นอกจากนั้นยังจะได้เรียนรู้เกี่ยวกับ SQL บน BigQuery และการ Transform Data ผ่าน DataPrep

ผู้สอน

Evan Jones : Data Analyst and technical course developer from Google Cloud

คอร์สนี้เหมาะสำหรับใคร ??

1. Data Analyst, Business Analyst, Business Intelligence
2. Data Engineer ที่ทำงานกับฝั่ง Analyst อยู่แล้วในการทำ Data Scalable บน GCP

สิ่งที่ควรรู้มาก่อน

1. ความรู้พื้นฐานเกี่ยวกับ SQL ในการทำ Report & Dashboard

Week 1

Module 1 : Introduction to Data on Google Cloud Platform

เหตุผลที่ GCP ถูกใช้ในงาน Data Analysis

1. Storage มีราคาถูก
2. โฟกัสที่ Query ไม่ใช่ Infrastructure
3. สามารถเพิ่ม Scale ได้อย่างยืดหยุ่น

ลองดูกราฟด้านล่าง จะเห็นว่า Storage ขนาด 1 GB มีราคาถูกลงอย่างรวดเร็วมากๆ

เปรียบเทียบ ​Big ​Data ​On-Premise ​vs ​on ​the ​Cloud

On-Premise

• ขั้นตอนที่ 1 เราต้องมี Hard Drive เพื่อเก็บ Data
• ขั้นตอนที่ 2 ต้องมี CPU แรงๆ เพื่อประมวลผล
• ขั้นตอนที่ 3 ต้องเชื่อมต่อกับ Network สมมุติว่าวันนี้ Data Analyst อยู่ประเทศไทย แต่ Data Center อยู่อีกซีกโลกนึง การที่จะทำงานกับ Data จะทำได้ช้าหน่อย เพราะระยะทางค่อนข้างไกลกันมาก ทำให้มีผลต่อความเร็วของ Network ที่เชื่อมกับ Data Center ผ่าน Server
• ขั้นตอนที่ 4 จะเป็นหน้าที่หลักของ System Admin หรือ Server Technicians ค่อยควบคุมดูแลให้ Server ทำงานได้อย่างปกติ

หากบริษัทที่เราทำงานอยู่กำลังทำงานแบบ On-Premise แต่มีนโยบายที่จะชูเรื่อง Data Analysis หรือทำให้องค์กรเป็น Data driven นั่นหมายความว่า CEO อาจจะสั่งให้เราเก็บ Data มากขึ้น ซึ่งส่งผลทำให้ ขนาดของ Data เพิ่มขึ้นเป็น 10 เท่า หรือ 100 เท่าในอีก 1 ปี สิ่งที่มีผลกระทบที่สุดคือ Hardware ไม่ว่าจะเป็น Storage หรือ CPU ต้องมีการซื้อเพิ่มอยู่ตลอดเวลา บางทีอาจเป็นการเพิ่มจำนวนคอมพิวเตอร์ด้วย นั่นทำให้การใช้ Big Data แบบ On-Premise เริ่มมีปัญหา

On Cloud

เมื่อเปลี่ยนมาใช้ Cloud เราจะโฟกัสกันที่ตัว Query ไม่ใช่เรื่อง Infrastructure กันอีกต่อไป เพราะหากต้องการ Query ข้อมูลก็แค่พิมพ์ SQL Query เข้าไปบน BigQuery ได้เลย Infrastructure นั้น Google เตรียมให้เรียบร้อย

Data center ของ Google มีขนาดใหญ่มาก เพื่อรองรับ mission ของ Google ที่ต้องการให้ทุกคนเข้าถึง Data ที่มีอยู่บนโลกได้ทุกที่ทุกเวลา นั่นหมายความว่าปัญหาของ On-Premise ได้ถูก Google แก้ไขให้แล้ว หาก Storage ไม่เพียงพอ เราสามารถทำการ Scale up ได้เลยบน GCP จากเดิมที่ Data Analyst ทำงานแล้วต้องคอยพะวงเรื่องเหล่านี้ ก็จะได้พุ่งเป้าไปที่การหา insight อย่างเดียว

ลองดูรูปเปรียบเทียบระหว่างก่อน (On-premise) และหลัง (On cloud) จะเห็นได้ว่าเราไม่ต้องกังวลเรื่องของ Hardware อีกต่อไป เพราะการทำงานจะอยู่กับ cloud หากต้องการซื้อพวก infrastructure เพิ่มก็สามารถซื้อบน cloud ได้เลย

GCP ที่สามารถ Scale up/down ได้นั้น เราจะสามารถเพิ่มหรือลด Capacity ลงได้ตามความต้องการของผู้ใช้ได้ทันที ต่างจาก On-Premise ที่จะเสีย Cost ไปโดยเปล่าประโยชน์ในส่วนที่เราไม่ได้ใช้งาน เพราะเราไม่สามารถลดขนาดมันลงได้ตามความต้องการ พูดง่ายๆ ก็คือการใช้ Cloud เป็นการใช้งานตามที่ต้องการได้อย่างอิสระนั่นเอง (Pay as you go)

บริษัทที่เปลี่ยนมาใช้ Cloud

OCADO

เป็นบริษัทที่ขายของออนไลน์ที่ใหญ่มากของ UK ซึ่งเปลี่ยนมาใช้ GCP และ BigQuery ตัว OCADO เองมี Data ของ Transaction ซื้อขายที่ไหลเวียนอยู่มหาศาล โดยต้องการที่จะรวบรวม Data transactions, Data ทั้งหมดเกี่ยวกับสภาพอากาศ รวมถึง Data อื่นๆ ที่กระจัดกระจายอยู่ให้มารวมที่จุดเดียว โดยเรียกว่า “Central analytics reporting warehouse” เพื่อสะดวกต่อการทำงานกับ Data ที่มีอยู่ทั้งหมด ทำให้ทำงานได้อย่างรวดเร็ว ไม่มีปัญหากับงาน Adhoc และที่สำคัญไม่ต้องปวดหัวเรื่อง Infrastructure ด้วย

Spotify

แพลตฟอร์ม Streaming ฟังเพลงที่ใส่ใจกับ User experience ที่ทุกคนรู้จักกันดี ก็เป็นอีกหนึ่งบริษัทที่เปลี่ยนมาใช้ GCP และ BigQuery เช่นกัน โดย Spotify จะทุ่มเวลาทั้งหมดไปกับการหา insights คูลๆ ที่น่าสนใจจากการคิด Query เพื่อดึงข้อมูลออกมาใช้งาน ไม่ต้องมีเรื่อง Hardware failure มาขัดจังหวะ

พื้นฐานเกี่ยวกับ Project

เราจะมาดู 3 ส่วนสำคัญบน GCP Dashboard กันก่อน

1. Project Info

เราจะจัดการทุกอย่างภายใต้ Project นี้

• Navigate and launch cloud tools : เปิดใช้งานเครื่องมือต่างๆ ภายใน Project
• Work collaboratively : เพิ่ม users เข้ามาทำงานร่วมกันใน Project โดยใช้ IAM (Identify and Access Management)
• Authorized Tools and Apps : เปิดสิทธิ์การใช้งานเครื่องมือและ Application ต่างๆ ด้วย API Manager เช่น การใช้งาน Machine learning model flow ต่างๆ

2. Resources

Data Analyst ส่วนใหญ่จะใช้อยู่ 2 อย่าง

2.1 Storage in Google Cloud Storage

เช่น การอัพโหลดไฟล์ต่างๆ เพื่อใช้เก็บ Data ที่ต้องการทำมาวิเคราะห์บน Cloud ไม่ว่าจะเป็น CSV, Json ซึ่งพื้นที่นั้นเป็นแบบ Scalable ขยายได้เรื่อยๆ ตามจำนวนที่ใช้ (เสียเงินเรื่อยๆ)

2.2 Datasets in Google BigQuery

วิเคราะห์และดึง Data ด้วย SQL รวมถึงการสร้าง Dataset เพื่อเก็บ Data ด้วย

3. Billing

1. Storage in Google Cloud Storage	2. Datasets in Google BigQuery
* Billed for Bucket Storage	* Billed for Query processing (การใช้ Performance ในการ Query) * Billed for Table dataset (ขนาดของ Table)

---

Module 2: ​Big ​Data ​Tools ​Overview

Walkthrough ​Data ​Analyst ​Tasks, ​Challenges, ​and ​Introduce ​Google Cloud ​Platform ​Data ​Tools

ก่อนอื่นเราลองมาดูงานหลักๆ ของ Data Analyst โดยทั่วไปกันก่อนว่ามีอะไรบ้าง ตั้งแต่เริ่มจนจบกระบวนการ

1. Ingest data ที่ใช้ในการทำงาน
2. Transform เช่น การทำความสะอาด Data, เพิ่มคอลัมน์และลบคอลัมน์ที่ไม่ต้องการ (ถ้าองค์กรไหนมี Data Engineer ปัญหาเรื่องนี้จะลดลงได้มาก เพราะเค้าสามารถทำ Data pipeline และ​ Transform data ให้เราได้ก่อนเลย กรี๊ดดดด)
3. Store Dataset เพื่อพร้อมใช้งาน ในรูปของ Table ต่างๆ
4. Analyze เพื่อหา insights จาก Data ที่เราเตรียมไว้ เช่น การทำ EDA เบื้องต้น เขียน SQL แจ๋วๆ เพื่อ join data จนถึงการใช้ Model ในการวิเคราะห์
5. Visualize ออกมาในรูปของ Report หรือ Dashboard ซึ่งในส่วนนี้หากมีทักษะ Storytelling ด้วย ก็ยิ่งทำให้การสื่อสาร insights ต่อคนฟังมีประสิทธิภาพมากขึ้น เหมือนเป็นการรวมทั้ง ศาสตร์และศิลป์

แล้ว Challenges ของแต่ละ Task ของ Data Analyst มีอะไรกันบ้าง ??

• Ingest : เราต้องเจอกับขนาด, ความหลากหลาย และความรวดเร็วของ Data
• Transform : ตรงนี้จะกินเวลามากที่สุด ทำให้เราทำงานช้าลง เพราะเสียเวลาไปกับการทำให้ Data พร้อมใช้
• Store : ต้องคอยดู Cost และกังวลเรื่อง Latency การเพิ่มขนาดของ Storage ก็เป็นไปได้ลำบาก (on-premise)
• Analyze : มีปัญหาเรื่องเวลาในการ Query Data, เรื่องขนาดของ Data รวมไปถึงการเก็บ Data แบบของใครของมัน ถ้าคนละ Database ไม่สามารถนำมาทำงานร่วมกันใน Query ครั้งเดียว
• Visualize : มีปัญหาเรื่องขนาดของ Dataset ที่นำมาใช้งาน, ความช้าของการแสดงผลบน Dashboard (บางทีต้อง Present Dashboard ในที่ประชุม รอโหลดแต่ละคลิกก็เกือบ 5 นาทีแล้ว)

เห็นปัญหาเยอะแบบนี้ก็อย่าเพิ่งท้อน้าาาา (T _ T) เพราะว่า Services ต่างๆ บน Google Cloud Platform ช่วยได้ (คอร์สนี้ขายเก่งจริง) ลองศึกษารายละเอียดแต่ละ Tools เพิ่มเติมได้จ้า

BigQuery Public Datasets

หากใครอยากลองใช้ BigQuery แต่ไม่มี Dataset ทาง Google เค้าก็มี public dataset ไว้ให้ลองเล่นกันได้ https://cloud.google.com/bigquery/public-data/ หรือ ลองเข้าไปที่ BigQuery แล้วเลือก + ADD DATA จากนั้นเลือก Explore public datasets

ทีนี้ทุกคนก็จะเจอ public datasets จำนวนมาก แต่ละอันก็น่าสนใจทั้งนั้น

Explore ​9 ​Fundamental ​Google ​BigQuery ​Features

1. Fully-Managed Data Warehouse : ไม่ต้องมานั่งทำ Data Warehouse เอง หมดกังวลเรื่อง infrastructure
2. Reliability : มีความมั่นคงในการใช้งาน อัตราการล่มของ server ค่อนข้างต่ำ เพราะมี Backend เป็น Datacenters ของ Google
3. Economical : จ่ายเท่าที่ใช้ สามารถควบคุมค่าใช้จ่ายได้ง่าย
4. Security : มีระบบ Access control กำหนดสิทธิ์การเข้าถึง Project ในทุกขั้นตอน และมีการเข้ารหัส (encrypted) ระหว่างการรับและส่งข้อมูล
5. Auditable : มี Log เก็บสำหรับทุก Action ที่เกิดขึ้นบน BigQuery สามารถย้อนกลับมาดูได้
6. Scalable : อันนี้ถือเป็น Highlight เลย !! BigQuery สามารถรัน Query พร้อมๆ กันได้ถึง 50 Queries ในเวลาเดียวกัน ไม่ต้องรอให้ Query ก่อนหน้าทำงานเสร็จก่อนก็รันได้จ้า โอ้โหวว
7. Flexible : มีความยืดหยุ่น สามารถ Join ข้อมูลได้จากหลายๆ Datasets
8. Easy-to-use : เป็น SQL ทั่วไป คนที่เคยใช้ SQL สามารถทำงานได้เลย
9. Public Datasets : มี Datasets ฟรีให้ลองเล่นในหลายๆ industry วิธีการก็ตามด้านบนเลย

เปรียบเทียบ GCP Tools ที่ใช้ระหว่าง Data Analysts, Data Scientists และ Data Engineers

Roles	Data Analyst	Data Scientist	Data Engineer
What they do ?	ค้นหาข้อมูลเชิงลึกด้วยการใช้ Queries และสื่อสารมันออกมาด้วยการทำ Visualization	วิเคราะห์ Data และใช้โมเดล โดยใช้หลักสถิติและ Machine Learning	ออกแบบ สร้าง และควบคุมดูแลระบบการไหลของ Data
Background	การวิเคราะห์ข้อมูลด้วย SQL	การวิเคราะห์เชิงสถิติด้วย SQL, R, Python	หลักการทางวิศวคอมพิวเตอร์
GCP Tools Used	* Cloud Storage * BigQuery * Dataprep * Data Studio	* BigQuery * Datalab * Translate API * Machine Learning	* Computer Engine * Cloud Storage * Datastore * Bigtable * Pub/Sub * Dataflow * Dataproc * Dataprep

สำหรับ Data Analysts และ Data Scientists นั้นจะเน้นไปทางวิเคราะห์ข้อมูลและแสดงผล ส่วน Data Engineers จะเน้นไปทางควบคุมระบบการไหลของ Data ทั้งหมดให้อยู่ในสภาพที่ใช้งานได้รวมถึงการทำ ETL (Extract, Transform, Load) อีกด้วย สังเกตจากจำนวน Tools ที่ใช้ (ขอซูฮกมา ณ ที่นี้)

---

Module 3 : Exploring your Data with SQL

ปัจจุบันมีหลายๆ Options ของ GCP ในการ Explore Data เช่น

1. การใช้ BigQuery เราสามารถเขียน SQL เพื่อดู Data ที่เรามีอยู่ได้ ซึ่งเป็นวิธีที่ค่อนข้างยืดหยุ่น แต่ยังไงก็ยังต้องมีพื้นฐานการเขียนภาษา SQL
2. การใช้ Dataprep ที่ทำให้เราเห็น Statistics summary แบบเร็วๆ ได้ในรูปแบบของตาราง (Columns and Rows)
3. การใช้ Data Studio แสดงผลในรูปแบบของกราฟและ Dashboard

BigQuery

ทำไมถึงต้องใช้ BigQuery ? เพราะ SQL เป็นทักษะที่ควรค่าแก่การเรียนรู้ ยิ่งถ้าเป็น Data Analyst ก็เปรียบเสมือนกับสกิลติดตัว แบบ Passive เลยทีเดียว เพราะเมื่อเราทำงานกับ Database เจ้า SQL นี่แหละที่จะเป็นวิธีที่รวดเร็วที่สุดที่เราจะทำงานกับ Data

ขั้นตอนการ Explore Data ด้วย SQL

1. Question : การตั้งคำถามที่ดีจะช่วยให้เราเริ่มต้นได้ดีไปด้วย ลองตั้งคำถามว่า “ช่วยแสดงรายชื่อของบริษัททั้งหมด โดยเรียงจากรายได้น้อยที่สุดไปถึงมากที่สุด”
2. Dataset : อีกสิ่งหนึ่งที่ขาดไม่ได้เลย นั่นก็คือ เราควรรู้ว่าข้อมูลที่เราจะดึงออกมานั้นอยู่ที่ไหน
3. SQL : สกิลการเขียนภาษา SQL นั่นเอง “ช่วยแสดงรายชื่อของบริษัททั้งหมด โดยเรียงจากรายได้น้อยที่สุดไปถึงมากที่สุด” แปลงเป็นภาษา SQL ก็คือ

SELECT name, revenue
FROM dataset
ORDER BY revenue;

เรียนรู้การเขียน SQL ให้มีประสิทธิภาพ

ใช้เครื่องหมาย backtics ” ` ” หรือการกด ALT+ 96 เพื่อ wrap ชื่อตารางในการใช้ standard SQL

การที่เราจะ SELECT ข้อมูลมาใช้ เราต้อง point ให้ชัดว่ามาจาก project, dataset และ table ไหน ดังนั้น syntax จะออกมาเป็น `project.dataset.table`

#standardSQL
SELECT column
FROM `project.dataset.table`

หากไม่ระบุให้ชัดเจน ก็จะโดน BigQuery ด่าแบบนี้

ใช้ LIMIT clause เพื่อจำกัดจำนวนผลลัพธ์

เราจะมาลองใช้ bigquery public dataset ที่ชื่อว่า iris_990_2015 เป็นข้อมูลจำลองผู้เสียภาษี โดยจะมีอยู่ด้วยกันทั้งหมด 294,782 rows

สมมุติวันนี้เราอยากจะดูข้อมูลแค่ 10 rows โดยดูเฉพาะคอลัมน์ที่เป็น totrevenue แถมอีกนิด ! คือให้เรียงตามลำดับจากมากไปน้อยด้วย ลองมาเขียน SQL กัน

SELECT totrevenue
FROM `bigquery-public-data.irs_990.irs_990_2015`
ORDER BY totrevenue DESC
LIMIT 10

ผลลัพธ์ที่ได้ก็จะออกมาแค่คอลัมน์ totrevenue 10 แถว ที่มีค่ามากที่สุด แต่อย่าลืมว่าต่อให้เรา LIMIT เพื่อให้แสดงผลน้อยลง ไม่ได้หมายความว่า cost ที่ Query จะน้อยลงตามไปด้วย สิ่งที่จะทำให้ cost ลดลง คือการใช้ WHERE เพื่อลด scope ให้ Data มีขนาดเล็กลง

ใช้ Function เพื่อปรับ Format ของผลลัพธ์ที่ได้

จากผลลัพธ์ด้านบน จะเห็นว่าอ่านค่อนข้างยาก แทบจะต้องนั่งนับหลักกันเลยว่า รายได้จริงๆ หลักล้านหรือสิบล้าน ทีนี้ใน SQL ก็มีสิ่งที่เรียกว่า Function ซึ่งมีส่วนประกอบ 2 อย่างคือ Function และ Parameters

• Function = เป็น Action ที่ต้องการทำกับ Parameters
• Parameters = เป็น Inputs ที่ใช้

Functions ที่ทำงานกับ String -> FORMAT()

FORMAT (“%’d”, totrevenue)

SELECT FORMAT("%'d",totrevenue)
FROM `bigquery-public-data.irs_990.irs_990_2015`
ORDER BY totrevenue DESC
LIMIT 10

Functions ที่ใช้ในการคำนวน -> SUM(), COUNT(), AVG(), MAX()...

เราสามารถคำนวนตัวเลขง่ายๆ ได้เลย โดยใช้ SQL

SELECT
 SUM(totrevenue) AS total_2015_revenue,
 AVG(totrevenue) AS avg_revenue,
 COUNT(ein) AS nonprofits,
 COUNT(DISTINCT ein) AS nonprofits_distinct,
FROM `bigquery-public-data.irs_990.irs_990_2015`

ใช้ GROUP BY เพื่อ Group Aggregated Data

แต่ถ้าเราต้องการใช้ Dimension เข้ามาเกี่ยวข้อง ต้องเพิ่ม syntax ที่ชื่อ GROUP BY เพื่อใช้ในการ group ตัวเลขเหล่านั้น เช่น ใช้คอลัมน์ ein ที่เป็น Employee Identification Number เพื่อแบ่ง Data ออกมาเป็นแต่ละแถว

SELECT
 ein,
 SUM(totrevenue) AS total_2015_revenue,
 AVG(totrevenue) AS avg_revenue,
 COUNT(ein) AS nonprofits,
 COUNT(DISTINCT ein) AS nonprofits_distinct,
FROM `bigquery-public-data.irs_990.irs_990_2015`
GROUP BY ein

ใช้ HAVING เพื่อ Filter Grouped Data

ยังไม่จบแค่นั้น เรายังสามารถ Filter Data ออกมาได้อีก โดยใช้ HAVING ที่เป็นการ Filter ข้อมูลที่ถูก Group มาแล้ว ต่อจาก GROUP BY เช่น การ Filter ที่คอลัมน์ nonprofits มีค่ามากกว่า 1

SELECT
 ein,
 SUM(totrevenue) AS total_2015_revenue,
 AVG(totrevenue) AS avg_revenue,
 COUNT(ein) AS nonprofits,
 COUNT(DISTINCT ein) AS nonprofits_distinct,
FROM `bigquery-public-data.irs_990.irs_990_2015`
GROUP BY ein
HAVING nonprofits > 1

ใช้ WHERE เพื่อ Filter Data

การใช้ WHERE นั่นจะต่างจากการใช้ HAVING เพราะเป็นการ Filter Data ที่ยังไม่ถูกการ Group เช่น เราจะดึงข้อมูลทุกอย่างของคนที่มี ein = 390123480

SELECT *
FROM `bigquery-public-data.irs_990.irs_990_2015`
WHERE ein = '390123480'

มารู้จัก BigQuery Data types

Numeric คือ Data ที่เป็นตัวเลข จะมีย่อยลงไปอีก 2 ชนิด คือ 1. Integer เป็นจำนวนเต็ม และ 2. Float เป็นจุดทศนิยม

String คือ Data ที่เป็นข้อความ เช่น ‘dog’, ‘cat’, ‘1000-00’ ซึ่งข้อความจะถูก wrap ด้วย Single quote ‘

Dates คือ Data ที่เป็น วันที่และเวลา โดยจะเริ่มตั้งแต่ 0001-01-01 00:00:00 ถึง 9999-12-31 23:59:59.999999

Boolean คือ Data ที่มี 2 ค่า คือ TRUE, FALSE

ใช้ CAST เพื่อเปลี่ยน Data types

CAST()

SELECT CAST("12345" AS INT64)

ผลที่ได้คือ 12345 เป็นตัวเลข

SELECT CAST("2017-08-01" AS DATE)

ผลที่ได้คือ 2017-08-01 เป็นวันที่

SELECT CAST(1111111 AS STRING)

ผลที่ได้คือ “1111111” เป็นข้อความ

มารู้จัก NULL Value กันเถอะ

NULL คือ สิ่งที่เราไม่รู้ จริงๆ แล้ว NULL เป็นค่าที่ Valid ไม่ได้แปลว่าไม่มี เป็นค่าที่บอกว่ายังไม่รู้ว่ามันคืออะไร โดยค่า NULL จะไม่เท่ากับ ” ” หรือ Blank แล้วเราจะจัดการกับค่า NULL ได้ยังไง ? เราสามารถ Filter เพื่อดึงค่า NULL โดยจะมี Syntax IS NULL หรือ ไม่เอาค่า NULL โดยจะเป็น IS NOT NULL

SELECT
  ein,
  street,
  city,
  state,
  zip,
FROM
  `bigquery-public-data.irs_990.irs_990_ein`
WHERE
  state IS NULL
LIMIT
  100

การใช้ Date Functions

การใช้ Filter wildcard ด้วย LIKE

LIKE '%ข้อความ' นั่นจะหมายถึงว่า ให้ดึงข้อมูลอะไรก็ได้ที่อยู่ข้างหน้าข้อความที่เรากำหนด ยกตัวอย่างให้เห็นภาพ

SELECT ein, name 
FROM `bigquery-public-data.irs_990.irs_990_ein`
WHERE name LIKE '%HELP%'
LIMIT 100

จะเห็นได้ชัดว่าใน syntax แอดใช้ LIKE '%HELP%' หมายถึงต้องการให้ดึงชื่อทั้งหมด แล้วมีอะไรอยู่ก่อนหรือหลังคำว่า help ก็ได้ ผลลัพธ์ที่ได้ก็ตามนั้นเบย

สรุป Syntax พื้นฐานของ SQL แบบเข้าใจง่าย

SELECT -> select clause ใช้สำหรับเลือกคอลัมน์ที่ต้องการ
FROM -> from clause ใช้สำหรับเลือกตารางที่มีข้อมูลอยู่
WHERE -> where clause ใช้สำหรับ Filter ข้อมูลด้วยเงื่อนไขต่างๆ เช่น การใช้ wildcard LIKE เป็นต้น
GROUP BY -> group by clause ใช้สำหรับ Group ข้อมูลที่ถูก Aggregate จาก select clause
HAVING -> having clause ใช้สำหรับ Filter ข้อมูลที่ถูก Group แล้ว
ORDER BY -> ใช้สำหรับเรียงข้อมูล โดย default จะเรียงจากน้อยไปมาก (ASC) หรือเรียงจากมากไปน้อย (DESC)
LIMIT -> ใช้กำหนดว่าจะให้แสดงผลข้อมูลจำนวนกี่แถว

---

Module 4 : Google BigQuery Pricing

Walkthrough of a BigQuery Job

ในส่วนนี้จะว่าถึงเรื่องเงินๆ ทองๆ นั่นเอง ลองมาดูกันว่า BigQuery มีการคิดเงินกับผู้ใช้งานยังไงบ้าง โดยทั่วไปแล้ว หน่วยการทำงานของ BigQuery จะเรียกว่า Job โดยแต่ละ Job นั้น Given a Unique ID โดย Web UI, สามารถ Run แบบ concurrently, ทำงานเป็น Tasks และมี history job ได้ถึง 6 เดือน

BigQuery มี Job อยู่ 4 ชนิด

• Query – ชาร์จเงินด้วยจำนวน bytes ที่ถูก process
• Load Data into Table – Free
• Extract to Google Cloud Storage – Free
• Copy Existing Table – Free

โดย 2 องค์ประกอบหลักของ cost ของ BigQuery นั้นเกิดจากจำนวนของ Data ที่ถูก process และ พื้นที่บน Storage ที่ถูกเก็บ Data แบบ Permanently บน Disk

Calculate BigQuery Pricing: Storage, Querying and Streaming Costs

1. Storage

ขึ้นอยู่กับจำนวนข้อมูลในตาราง หากมีการทำ Streaming data แล้วไม่ได้ทำให้เป็น batch upload เช่น การ upload CSV 5GB ในครั้งเดียว แล้วหาก Streaming data ผ่าน API เช่น Add ข้อมูลเพิ่ม 10 records เข้าไปใน BigQuery ทันที ที่มี Data เกิดขึ้น ในกรณีแบบนี้ก็จะถูกชาร์จเงิน

แต่สำหรับ Data ที่มีอายุเก่าพอสมควรแล้ว ราคาการเก็บบน Storage ก็จะน้อยลงไปด้วย ยกตัวอย่างเช่น โดยปกติจะมีการเก็บค่า Storage อยู่ที่ 2 cents ต่อ GB ต่อเดือนสำหรับ Storage แต่หาก Data ไม่มีการเปลี่ยนแปลงเลยภายใน 90 วัน Data กลุ่มนี้จะถูกเรียกว่า long-term storage และราคาต่อเดือนจะถูกลดลงครึ่งนึง จาก 2 cents เหลือ 1 cents ต่อ GB ต่อเดือน (ถูกเฟร่อออออ)

2. Querying

โดยปกติแล้วถ้าเป็นการใช้แบบ Personal จะเป็นการคิดเงินตามการใช้งาน หรือเรียกว่า Pay as you go แต่ถ้าหากเป็นการใช้งานแบบ Organization จะเป็น Flat-rate plans ราคาจะอยู่ที่ $10,000 หรือ $20,000 ต่อเดือน แต่เหนืออื่นใด เราจะโฟกัสการใช้งานตามจำนวน bytes ซึ่งตอนนี้มี Pricing Calculator ให้ทุกคนได้คำนวนกันแบบ online แล้ว ลองคลิกไปดูได้เลย -> Google Cloud Pricing Calculator

และมีข่าวดีเด้ออออ!! ทุกคนจะได้ใช้งานฟรี 1TB ต่อเดือน สำหรับการ Process query และ 10 GB ต่อเดือน สำหรับ การเก็บ Data บน Storage อย่างที่บอกไปแล้วด้านบน หากเราจะ Query Data มาใช้งาน อยากให้ทุกคน optimize query ให้ดีๆ กำหนดเงื่อนไขให้เจาะจง เพื่อลดจำนวน byte ในการ process data จะได้ประหยัดไปด้วย

Query Validator ตัวช่วยคำนวน Cost

Query Validator คือ สิ่งที่บอกเราว่า syntax ของ Query นั้นถูกหรือผิด และยังบอกว่า Query ที่เราจะเขียนจะ process จำนวนเท่าไหร่ เช่น ในภาพด้านล่างเจ้าตัว Query Validator บอกเราว่าจะ process ไป 430.7 MB หากมีการกด Run Query ชุดนี้

Quotas ที่ผู้ใช้ BigQuery ควรรู้

• สามารถ Query พร้อมกันได้ 50 Queries
• Query จะ timeout ถ้ารันนานเกิน 6 ชั่วโมง
• อัพเดทได้ 1,000 ครั้งต่อ Table ต่อวัน
• จะ refer Table ใน 1 Query ได้ไม่เกิน 1,000 ตาราง (Join กันตาหลุดแน่ๆ)
• Max result size 128 MB ที่ถูก compressed แล้ว (ขนาดของโค้ดที่ใช้ในการ Query)

---

Module 5 : Cleaning and Transforming your Data

ว่ากันว่าขั้นตอนการ Clean และ Transform Data นั้นเป็นขั้นตอนที่ใช้เวลามากที่สุด ก่อนที่ Data Analyst จะนำ Data ไปวิเคราะห์และทำ Data Visualization หรือแม้กระทั่งทำ Machine Learning Models เราอาจจะเรียกมันอีกชื่อว่า Data Preparation เราสามารถทำมันได้ทั้งใน SQL บน BigQuery และ Cloud Dataprep

Examine the 5 Principles of Dataset Integrity

วลีที่สาย Data มักจะเคยได้ยินอยู่เสมอก็คือ Garbage in.. Garbage out หากเราไม่ Prepare data ให้ดี ก็เหมือนกับการส่ง Data ขยะเข้าไปตั้งแต่ต้นทาง ทำให้ปลายทางที่ Data ออกมา ก็จะไม่มีประสิทธิภาพยังเป็นขยะเหมือนเดิม ในคอร์สนี้ก็จะบอก 5 กฎเหล็กที่จะทำให้ Dataset ของเรามีประสิทธิภาพในการใช้งานมากขึ้น

Rule	Description	Challenge
Validity	* Data ต้องตอบโจทย์ Business Rules นั้นๆ ได้ * Data มีความเป็น uniqueness (primary key)	* Data มี Fields ที่เป็นช่องว่างๆ ไม่มี primary key * Data Mismatch * Data เกินขอบเขตที่ควรจะเป็น เช่น อายุ 200 ปี
Accuracy	Data ต้องสมเหตุสมผลกับความเป็นจริง	* Data ไม่ตรงกับความเป็นจริง เช่น ประเทศไทย มีจังหวัดชื่อพัทยา
Completeness	Data มีความครบถ้วน สมบูรณ์	* Data ไม่ครบถ้วน มี missing value
Consistency	สามารถหา insights ได้อย่างถูกต้อง	* มี Duplicate records * มีการทำงานทับซ้อนทำให้เห็น Data ไม่เหมือนกัน
Uniformity	สามารถนำเสนอข้อมูลได้อย่างถูกต้อง	* หน่วยวัด อาจจะผิดพลาด เช่น สกุลเงิน

Characterize Dataset Shape and Skew

Dataset Shape

Google ได้ยกตัวอย่าง Dataset Shape อยู่ 4 แบบ

1. Small Dataset คือ Dataset ที่มีจำนวนคอลัมน์และแถว ในปริมาณเท่าๆ กัน และมีจำนวนไม่มาก
2. Taller than Wide คือ Dataset ที่มีจำนวนคอลัมน์น้อย แต่มีจำนวนแถวมาก เช่น มี 3 คอลัมน์ แต่มี 1 ล้านแถว
3. Even Height and Width คือ Dataset ที่มีจำนวนคอลัมน์และแถว ในปริมาณเท่าๆ กัน และมีจำนวนพอดีกับที่ต้องการใช้งาน
4. Wide but short คือ Dataset ที่มีคอลัมน์เป็นจำนวนมาก แต่มีจำนวนแถวน้อย เช่น มี 1,000 คอลัมน์ แต่มี 20 แถว

Dataset Skew

เรียกอีกชื่อหนึ่งคือ Distribution of Values หรือ การกระจายตัวของข้อมูลนั่นเอง โดยปกติแล้วจะมีการกระจายตัวอยู่ 3 แบบ ซึ่งค่าในแกน Y แนวตั้ง จะเป็นตัววัดความถี่ของสิ่งที่อยู่ในแกน X แนวนอน เช่น Dataset ของเราเป็นรายชื่อพนักงานในบริษัท แกนนอนเป็นชื่อคน เช่น นายสมชายมี 4 คน กราฟแท่งของนายสมชายก็จะเด้งสูงขึ้นมา

เสริมอีกนิด ! ในกราฟจะแสดง 3 ค่าด้วยกัน คือ 1. Mean (ค่าเฉลี่ย) 2. Median (ค่าที่อยู่ตรงกลาง หรือ มัธยฐาน) 3. Mode (ค่าที่มีความถี่สูงสุด หรือ ฐานนิยม)

1. Possible Data Values : หรือการกระจายตัวแบบปกติ หางทั้งสองฝั่งมีขนาดเท่ากัน หรือในทางสถิติที่เราเรียนกันมาตั้งแต่เด็กคือแบบระฆังคว่ำนั่นเอง ! โดยที่มีค่า Mean = Median = Mode
2. Skewed Right : หางของกราฟทางขวาจะยาวกว่าหางของกราฟทางซ้าย โดยที่มีค่า Mode < Median < Mean
3. Skewed Left : หางของกราฟทางซ้ายจะยาวกว่าหางของกราฟทางขวา โดยที่มีค่า Mean < Median < Mode

Clean and Transform Data using SQL

Validity ใช้งานได้

• ตั้งค่า Data Type ของแต่ละ Field ให้ถูกต้อง
• ระบุให้ชัดเจนในแต่ละ Fields ว่าสามารถมีค่า NULL หรือห้ามมีค่า NULL (NULLABLE / REQUIRED)
• ตรวจสอบและ Filter Data ที่ต้องการใช้ด้วยการใช้ SQL Conditional (CASE WHEN)
• ต้องมี Primary Keys ในทุกๆ Table เพื่อใช้ในการ Join ข้อมูลมาใช้งานร่วมกัน ก่อนที่จะนำเข้าไปใช้งานบน BigQuery

Accuracy ถูกต้อง

• สร้าง test cases หรือ calculated fields ง่ายๆ เพื่อตรวจสอบความถูกต้องของข้อมูลเบื้องต้น เช่น
  • SQL -> (quantity_ordered * item_price) AS sub_total เพื่อดูว่ามีค่าแปลกๆ ไหม
  • SQL -> COUNT(DISTINCT province) AS unique_province เพื่อดูว่ามีจำนวนจังหวัดสมเหตุสมผลไหม

Completeness ครบถ้วน

• จัดการกับ NULL values เพื่อให้ Data ครบถ้วน
  • SQL -> NULLIF(), IFNULL(), COALESCE()
• เพิ่มรายละเอียดของข้อมูลที่มีอยู่ ผ่านการ UNION หรือ JOIN

Consistency มั่นคง

• เก็บข้อมูล fact table ในที่เดียว และใช้ ID ในการ JOIN เพื่อใช้งาน
• ใช้ String Function เพื่อ clean data
  • SQL -> PASE_DATE(), SUBSTR(), REPLACE()

Uniformity ข้อมูลเป็นรูปแบบเดียวกัน

• มีการทำ Document และ comment ให้ชัดเจน
• ใช้งาน FORMAT() เพื่อทำให้เห็นตัวเลขชัดเจน
• CAST() data types เพื่อให้เป็นรูปแบบเดียวกัน ทั้ง format และ digits
• ใช้ Label อย่างเหมาะสมในการ Visualization

---

Introduction to Cloud ​Dataprep

Dataprep เป็น Tool จากบริษัท Trifacta (Google ซื้อแล้ว) ที่ใช้ในการทำ EDA เบื้องต้น, Clean และ Transform Data รวมถึงสามารถทำ Data pipeline ได้ด้วย

Explore Dataset using Cloud Dataprep

ขั้นตอนแรก : เมื่อเข้ามาที่ Dataprep แล้วให้ทำการ Create Flow เพื่อนำข้อมูลจาก BigQuery มาใช้งาน

ขั้นตอนที่ 2 : ตั้งชื่อ Flow และ Description ให้เรียบร้อย

ขั้นตอนที่ 3 : ทำการ import dataset จาก BigQuery

ขั้นตอนที่ 4 : หลังจาก import dataset เข้ามาเรียบร้อย จะเห็นว่ามีรายละเอียด dataset อยู่ทางขวามือ จากนั้นกด Import & Add to Flow

ขั้นตอนที่ 5 : เมื่อเราได้ Original Dataset แล้ว ต้องทำการสร้าง Recipe เพื่อทำการ Clean และ Transform Data

เท่านี้ เราก็สามารถทำ EDA ( Exploratory Data Analysis) เบื้องต้นได้แล้วว่า Dataset ของเรามีหน้าตาเป็นอย่างไร !!

เมื่อเราคลิกที่กราฟแท่งซ้ายสุดของ channelGrouping เจ้าตัว Dataprep ก็จะ highlight สีเขียวให้เราได้เลยว่า channelGrouping ส่วนใหญ่มีค่าเป็น Referral

Clean and Transform Data using Cloud Dataprep

เราสามารถทำการลบคอลัมน์ที่ไม่ใช้งานออกได้ เพียงแค่คลิกขวาที่ dropdown แล้วเลือก Delete ได้เลย

เจ้า Dataprep เป็น Tool ที่ฉลาดมาก เพียงแค่เราคลิกที่แถบทีเทาใต้ ชื่อคอลัมน์ที่เราสนใจ ซึ่งเจ้าแถบสีเทานี้หมายถึงจำนวน Missing Values ของแต่ละคอลัมน์นั่นเอง หลังจากเราคลิกที่แถบสีเทาแล้ว เมนู Suggestion ด้านขวาจะโผล่มาแนะนำเราว่าสามารถทำอะไรกับมันได้บ้าง หากเราต้องการลบแถวทั้งหมดที่มีค่า totalTransactionRevenue เป็น Null ก็สามารถคลิก Add เพื่อลบได้เลย

หลังจากลบคอลัมน์และแถวที่เราไม่ต้องการออกแล้ว จำนวนคอลัมน์และแถวก็จะถูกอัพเดทให้เราดูด้วย สังเกตจากจำนวนที่เปลี่ยนไปด้านล่าง

---

Course Summary

เป็นยังไงกันบ้างทุกคน แอดรีวิวคอร์ส Exploring ​and ​Preparing ​your ​Data with BigQuery จาก Coursera จบแล้ววว คอร์สนี้จริงๆ แล้วมี Hands-on Labs หลายอันมาก ซึ่งทุกอันจะทำผ่าน Qwiklabs

• Lab1 : Explore your Ecommerce Dataset with SQL in Google BigQuery
• Lab2 : Trobleshoot Common SQL Errors with BigQuery
• Lab3 : Exploree and Create an Ecommerce Analytics Pipeline with Cloud Dataprep
• Lab4 : Creating Data Pipelines with Data Fusion

แอดคิดว่าเพื่อนๆ ที่สนใจเกี่ยวกับ SQL BigQuery น่าจะได้ความรู้ไม่มากก็น้อย โดยเฉพาะคนที่ทำงานเป็นสาย Data Analyst อยู่แล้ว

• Module 1 : Introduction to Data on Google Cloud Platform
• Module 2 : ​Big ​Data ​Tools ​Overview
• Module 3 : Exploring your Data with SQL
• Module 4 : Google BigQuery Pricing
• Module 5 : Cleaning and Transforming your Data

สำหรับใครที่สนใจอยากทำงานสาย Data แอดคิดว่าคอร์สนี้ก็ถือว่าเหมาะสมอยู่เหมือนกัน แต่ถ้ามีความรู้เรื่อง SQL มาก่อนสักนิดนึงก็จะดีมากๆ เลย สุดท้ายนี้ขอบคุณทุกคนที่ติดตามอ่าน แม้ว่าคอนเท้นจะร้างมานานแล้วก็ตามมม สัญญาว่าจะขยันขึ้นแบบเมื่อก่อนน้าาา เรียนจบก็รับ Certificate เท่ๆ ไปอัพลง Linkedin กันโลดดดดด

พูดคุยกันได้ที่เพจ Malonglearn – มาลองเรียน หรือติชมผ่านคอมเม้นด้านล่างเลยจ้า