Google BigQuery - explore ข้อมูล Pantip ดูแบบง่าย ๆ
Tags: googlecloud, tutorial, datascience, serverless
Published: 2020-06-12 13:15:33

ต่อเนื่องจากบทความคราวก่อนเรื่อง [ส่องกระทู้ชาวพันทิปแบบ real-time ด้วย Google Cloud ฟรี (deploy เสร็จสรรพใน 5 นาที!)](https://dev.to/copypasteengineer/google-cloud-scraping-project-real-time-google-cloud-deploy-5-51ic) เราได้ระบบที่เก็บข้อมูล Pantip ทุก ๆ 10 นาที ลงบน **Google BigQuery** ซึ่งเป็น Data Warehouse บน Google Cloud Platform แล้วนะครับ ในบทความนี้จะขอแนะนำวิธีการ explore ข้อมูลที่เรามีบน BigQuery และทำ Dashboard แบบง่าย ๆ ทำเสร็จภายในไม่กี่นาที โดยใช้เครื่องมือที่<u>มีมาใน Google BigQuery อยู่แล้ว</u> ไม่ต้อง install อะไรเพิ่มเติมทั้งสิ้นครับ

# **เริ่มกันเลย**
ถ้าจะทำตามไปด้วย ให้ไปทำตามขั้นตอนใน [บทความก่อนหน้า](https://dev.to/copypasteengineer/google-cloud-scraping-project-real-time-google-cloud-deploy-5-51ic) ก่อนนะครับ ไม่อย่างนั้นจะไม่มี data ใน BigQuery ทำไม่นานเลยครับมีโค้ดให้เสร็จสรรพ ไม่ต้อง install อะไรลงในเครื่องเลย

พอมี data แล้วก็เริ่มจากไปที่ [หน้า console ของ BigQuery](https://console.cloud.google.com/bigquery) ก็จะเป็นตามภาพด้านล่างนะครับ คือด้านซ้ายล่างจะเป็นส่วนที่ให้ explore Dataset ที่เรามี ส่วนกล่องข้อความตรงกลางเอาไว้ให้เราพิมพ์คำสั่ง SQL เพื่อทำการ query ข้อมูลจาก table ของเรา ออกมาแสดงได้ครับ

![bigquery-looks.png](https://dev-to-uploads.s3.amazonaws.com/i/9daiskfl12z27tzrjc1x.png)

ตัวนี้แหละครับก็คือเครื่องมือหลักที่เราจะใช้เพื่อ explore ข้อมูลกัน โดยการเขียน SQL

เนื่องจาก BigQuery เป็น serverless data warehouse ที่ optimized มาเพื่อสำหรับการทำ analytics โดยเฉพาะครับ ทำให้เราสามารถ <u>process ข้อมูลขนาดใหญ่ได้อย่างรวดเร็ว</u> (ข้อมูลเป็นระดับเป็นสิบ ๆ Gigabytes ก็อาจจะใช้เวลาประมวลผลไม่กี่วินาทีเท่านั้น) และยังราคาไม่แพงมาก ไม่ต้องซื้อ hardware มาตั้งและ maintain เอง Google จะทำการ <u>scale ให้อัตโนมัติครับ</u>

ต่อไป จากทางด้านกล่องซ้ายล่าง กดเลือก Dataset และ Table ที่เรา scrape มา ก็จะเห็นรายละเอียดของ Table นั้น ๆ ครับ

![table-detail-1.png](https://dev-to-uploads.s3.amazonaws.com/i/opfu8d78kq2931fcpbdq.png)
รูปแสดง schema ของ table ตรงนี้ผมแก้จากโค้ดในบทความก่อนเล็กน้อยครับ คือให้มันเก็บข้อมูลมาทุก tag ที่มี แล้วเก็บตัวแปร tags มาด้วย เป็น array type ของ tags ใน schema เลยเป็น `STRING` `REPEATED`

ที่แถบด้านบนก็สามารถคลิกดูรายละเอียดของ Table และดูตัวอย่างข้อมูลได้ครับ
![table-detail-2.png](https://dev-to-uploads.s3.amazonaws.com/i/ixz3wczl6w9d7mhqoyh5.png)
![table-detail-3.png](https://dev-to-uploads.s3.amazonaws.com/i/7dz2luyrfuj5xkjadilo.png)

จะเห็นจากใน detail ว่าข้อมูลผมตอนนี้มีประมาณ 255,000 แถว
ทีนี้ลองมา query เพื่อดู stats ของข้อมูลเพิ่มเติมกันนะครับ

เริ่มจากลองดูช่วงของข้อมูลก่อน ว่ามีข้อมูลตั้งแต่วันไหนถึงวันไหน ก็คือหา min, max ของ field `created_time` ธรรมดาครับ โดยการพิมพ์คำสั่ง SQL ลงในกล่องข้อความตรงกลาง แล้วกปุ่ม `เรียกใช้` ผลลัพธ์ก็จะออกมาเป็นตารางด้านล่างตามภาพนะครับ
```sql
SELECT
  MIN(DATE(created_time)) min_date,
  MAX(DATE(created_time)) max_date,
FROM SCRAPE.PANTIP
```
![query-min-max-time.png](https://dev-to-uploads.s3.amazonaws.com/i/gpespra7wl08opmgya4w.png)

ต่อไปผมอยากรู้ว่า ในช่วง 3 เดือนนี้เนี่ย tag ไหนมีจำนวนกระทู้มากน้อยอย่างไรบ้าง 
ขอไม่ลงรายละเอียด SQL มากนะครับ ไอเดียก็คือ field `tags` เนี่ยมันเป็น array เราต้องใช้ `UNNEST()` ในการ "กระจาย" มันออกมา จากนั้นก็ GROUP BY tag แต่ละ tag แล้ว count ออกมา แล้วก็ให้เรียงลำดับ ORDER ตาม count จากมากไปน้อยครับ
```sql
SELECT
  T AS tag,
  COUNT(*) AS count
FROM SCRAPE.PANTIP P
JOIN UNNEST(P.tags) T
GROUP BY tag
ORDER BY count DESC
LIMIT 10
```
ก็จะได้ผลลัพธ์เป็น tags 10 อันดับแรก ที่มีจำนวนกระทู้มากที่สุดในสามเดือนนี้นะครับ
![query-tag-count.png](https://dev-to-uploads.s3.amazonaws.com/i/pgf3ul7b0kva8x78k0yq.png)
จากภาพ ด้านบนก็จะมีแสดงเวลาที่ใช้ในการคำนวณด้วยนะครับ จะเห็นว่าใช้เวลาไปแค่ 1 วินาทีนิด ๆ เท่านั้น

เหมือนว่าส่วนใหญ่จะเป็นเรื่องความรักนะครับ ไม่ค่อยน่าแปลกใจเท่าไหร่ 555 แล้วก็มี COVID ก็ติดอันดับด้วย

ทีนี้เราลองหยิบมาดูด้วยตาว่า tag COVID นี่เขาคุยอะไรกันนะครับ
```sql
SELECT
  created_time,
  title,
FROM SCRAPE.PANTIP P
WHERE EXISTS(
  SELECT TRUE FROM UNNEST(P.tags) T
  WHERE T = 'โรคติดเชื้อไวรัสโคโรนาสายพันธุ์ใหม่_2019_(COVID-19)'
)
ORDER BY created_time DESC
```
ถ้าเป็นของไม่กี่วันนี้ก็ดูไม่ค่อยมีอะไรนะครับ เป็นประมาณอัพเดตข่าวกันมากกว่า
![query-covid-text.png](https://dev-to-uploads.s3.amazonaws.com/i/836xbow4bcoig7mt8zhi.png)
แต่ถ้า scroll ย้อนกลับไปประมาณเดือน 3 ก็จะเห็นกระทู้ประมาณว่า ถ้าติดโควิทจะทำยังไงดี หรือแชร์วิธีการปฏิบัติตัวต่าง ๆ เป็นสังคมที่ค่อนข้าง active เลยครับ
![query-covid-text-old.png](https://dev-to-uploads.s3.amazonaws.com/i/p2laztcmpdxafse0f1i8.png)

**ทีนี้ถ้าจะ explore ดูข้อมูลโดยการไล่ดูตารางแบบนี้คงจะไม่สะดวกใช่ไหมครับ** ต่อไปเราลองสร้าง graph เพื่อ visualize ข้อมูลออกมาดูกันดีกว่า

ไม่ต้องไปเปิดโปรแกรมไหนเพิ่มเลยครับ BigQuery มีเครื่องมือที่เอาไว้ช่วย visualize และทำ dashboard แบบง่าย ๆ ไว้อยู่แล้ว ชื่อว่า **Google Data Studio** เราสามารถเอา result จากคำสั่ง SQL ที่เรารัน เข้าไปใน Data Studio ได้เลย โดยการคลิกที่ปุ่ม `สำรวจข้อมูล` ด้านบน result แล้วเลือก `สำรวจด้วย Data Studio` ตามในภาพครับ
![sql-to-data-studio.png](https://dev-to-uploads.s3.amazonaws.com/i/v5j66pberh5hu1fbjnqw.png)

ทีนี้เราก็จะเด้งมาที่หน้าของ Data Studio ซึ่งพื้นที่ทางด้านซ้ายนี่จะเป็นที่เอาไว้สร้าง dashboard แบบ drag-and-drop ซึ่งตอนนี้ก็จะค้างข้อมูล "กระทู้ COVID" ที่เรา query เอาไว้เมื่อกี้อยู่ ส่วนด้านขวาคือเป็นเหมือน tools box ครับให้เราสามารถเลือกประเภทของ chart และปรับค่าค่าต่าง ๆ ได้ เช่นตัวแปรที่จะเอามาแสดง จำนวน dimensions สี แสงเงาต่าง ๆ
![sql-to-data-studio.png](https://dev-to-uploads.s3.amazonaws.com/i/gfbpjjhc51ub2mn68psk.png)

ทีนี้สมมุติว่าผมอยากจะ plot จำนวนของกระทู้ (ซึ่งก็คือกระทู้ใน tag COVID ที่เรา query มาเมื่อกี้) อย่างแรกก็เลือกประเภทของ chart เป็น "อนุกรมเวลา" (time series) แล้วก็ให้ "มิติข้อมูล" (dimension) คือแกน X เป็น `created_time` ส่วน "เมตริก" (metric) คือแกน Y เป็น `Record Count` ตามภาพ
![data-studio-setting.png](https://dev-to-uploads.s3.amazonaws.com/i/leqm2tzugerfl0zajktj.png)

พอเลือกเสร็จปุ๊ปก็จะเห็นว่าพื้นที่ dashboard ก็จะโหลดใหม่ได้เป็น time series plot จำนวนของกระทู้กับเวลาตามที่เราต้องการ และเป็น interactive ด้วย คือเราสามารถจะเลื่อน cursor ไปชี้ เพื่อให้แสดงรายละเอียดข้อมูลของ data point ออกมาได้
![data-studio-timeseries.png](https://dev-to-uploads.s3.amazonaws.com/i/7tj03xs7ydd7w782zdgv.png)
จะเห็ฺนว่า data studio ช่วย aggregate ข้อมูลให้เราเป็นระดับ "วัน" อัตโนมัติด้วยนะครับ เพราะ `created_time` นี่เดิมทีเป็นระดับวินาทีเลย ถ้า plot ไปตรง ๆ ก็จะดูไม่รู้เรื่อง แต่ Data Studio คิดส่วนนี้ไว้ให้เราแล้ว เลยไม่ต้องไปแก้เองเลยครับ

จะเห็นว่าด้วยเครื่องมือสองอย่างนี้ ช่วยให้เราทำงานกับข้อมูลขนาดใหญ่ได้รวดเร็วมากเมื่อเทียบกับการโหลดข้อมูลไป process ใน Python แล้วยังสามารถ visualize ได้ง่ายมากอีกด้วย แค่ลาก ๆ วาง ๆ แปปเดียว

แต่ข้อเสียก็คือต้องใช้ SQL คล่องหน่อยครับ และอาจจะไม่สามารถคำนวณบางอย่างที่ซับซ้อน ๆ ได้

# **Demo**
ต่อไปขออนุญาตแนะนำ chart ที่ผมลองทำดูเล่น ๆ นะครับ อาจจะไม่สวยหรือไม่สื่อความหมายเท่าไหร่ แต่คิดว่าน่าจะทำให้เห็นภาพมากขึ้นว่าเราสามารถเอา Data Studio ไปใช้ทำอะไรได้บ้างนะครับ

**1. จำนวนกระทู้แต่ละ tag ตามเวลา**
```sql
SELECT
    created_time,
    T AS tag,
FROM SCRAPE.PANTIP P
JOIN UNNEST(P.tags) AS T
WHERE T IN ('ปัญหาความรัก', 'หุ้น', 'ประสบการณ์ความรัก', 'ปัญหาชีวิต', 'ความรักวัยรุ่น')
```
![demo-1.png](https://dev-to-uploads.s3.amazonaws.com/i/0p7gna978me6gj4i925m.png)

**2. สัดส่วนของแต่ละ tag**
```sql
SELECT
    T AS tag,
    COUNT(*) AS count
FROM SCRAPE.PANTIP P
JOIN UNNEST(P.tags) AS T
WHERE T IN ('ปัญหาความรัก', 'หุ้น', 'ประสบการณ์ความรัก', 'ปัญหาชีวิต', 'ความรักวัยรุ่น')
GROUP BY 1
```
![demo-2.png](https://dev-to-uploads.s3.amazonaws.com/i/vjxkcafztp2nbbt39fs7.png)

**3. ช่วงเวลาที่โพส**
```sql
SELECT
    EXTRACT(HOUR FROM created_time) AS hour_num,
    CONCAT(EXTRACT(HOUR FROM created_time), '.00น.') AS hour,
    T AS tag,
    COUNT(*) AS count
FROM SCRAPE.PANTIP P
JOIN UNNEST(P.tags) AS T
WHERE T IN ('ปัญหาความรัก', 'หุ้น', 'ประสบการณ์ความรัก', 'ปัญหาชีวิต', 'ความรักวัยรุ่น')
GROUP BY 1, 2, 3
```
![demo-3.png](https://dev-to-uploads.s3.amazonaws.com/i/ws0woh1voep1ydjsja59.png)

พอได้ charts ที่เราสนใจออกมาแล้วเราสามารถเอามารวมกัน ให้กลายเป็น dashboard อันหนึ่งได้อีกด้วย ใน Data Studio mode Report
![dashboard.png](https://dev-to-uploads.s3.amazonaws.com/i/o0sp9diibtlpptnm06xm.png)
**ซึ่ง dashboard อันนี้ พอทำเสร็จแล้วก็สามารถที่จะแชร์ให้คนอื่นเข้ามาดู หรือเข้ามาแก้ไขได้ (คล้าย ๆ Google Docs นั่นเอง) และยัง update ตามข้อมูลบน BigQuery อัตโนมัติอีกด้วย ไม่ต้องมานั่งสร้าง dashboard ใหม่ทุกครั้งที่เพิ่มข้อมูล สะดวกมาก ๆ ทีเดียว**

# **Summary**
บทความนี้ก็แนะนำ **Google BigQuery** และ **Data Studio** สำหรับการ explore ข้อมูลคร่าว ๆ นะครับ ขออภัยถ้าเลือกใช้ chart บางอันผิดประเภทไปหน่อย อย่างเช่น pie chart ในข้อ 2 ควรจะใช้เป็น bar chart มากกว่า ถึงจะเปรียบเทียบความแตกต่างได้ชัดเจนกว่า หรืออย่างข้อ 3 ควรจะ normalize ในแต่ละ tag ก่อน เพื่อให้เทียบสัดส่วนได้ง่ายกว่า เป็นต้น แต่เนื่องจากบทความนี้เป็นแค่ demo เล็ก ๆ เท่านั้น เลยเอาแบบที่ง่าย และให้เห็น chart หลาย ๆ ประเภทมากกว่าครับผม

สำหรับตัว BigQuery และ Data Studio นี้ ขอแนะนำมาก ๆ เลยครับ ทำงานได้รวดเร็ว เพราะ dashboard ที่ทำด้านบนเนี่ย ใช้เวลาทำประมาณ<u>ไม่ถึงครึ่งชั่วโมง</u>เท่านั้นครับ สะดวกมาก ๆ แล้วยังมี features ที่น่าสนใจอื่น ๆ อีกเยอะครับ ไว้ในบทความหน้า ๆ จะมาแนะนำกันต่อนะครับ

ถ้ามีเรื่องไหนที่สนใจเพิ่มเติมสามารถ comment เอาไว้ได้นะครับ

FB Page: [Copy Paste Engineer](https://www.facebook.com/CopyPasteEng)
<center>**- ขอบคุณที่อ่านครับ -**</center>