ต่อเนื่องจากบทความคราวก่อนเรื่อง ส่องกระทู้ชาวพันทิปแบบ real-time ด้วย Google Cloud ฟรี (deploy เสร็จสรรพใน 5 นาที!) เราได้ระบบที่เก็บข้อมูล Pantip ทุก ๆ 10 นาที ลงบน Google BigQuery ซึ่งเป็น Data Warehouse บน Google Cloud Platform แล้วนะครับ ในบทความนี้จะขอแนะนำวิธีการ explore ข้อมูลที่เรามีบน BigQuery และทำ Dashboard แบบง่าย ๆ ทำเสร็จภายในไม่กี่นาที โดยใช้เครื่องมือที่มีมาใน Google BigQuery อยู่แล้ว ไม่ต้อง install อะไรเพิ่มเติมทั้งสิ้นครับ

เริ่มกันเลย

ถ้าจะทำตามไปด้วย ให้ไปทำตามขั้นตอนใน บทความก่อนหน้า ก่อนนะครับ ไม่อย่างนั้นจะไม่มี data ใน BigQuery ทำไม่นานเลยครับมีโค้ดให้เสร็จสรรพ ไม่ต้อง install อะไรลงในเครื่องเลย

พอมี data แล้วก็เริ่มจากไปที่ หน้า console ของ BigQuery ก็จะเป็นตามภาพด้านล่างนะครับ คือด้านซ้ายล่างจะเป็นส่วนที่ให้ explore Dataset ที่เรามี ส่วนกล่องข้อความตรงกลางเอาไว้ให้เราพิมพ์คำสั่ง SQL เพื่อทำการ query ข้อมูลจาก table ของเรา ออกมาแสดงได้ครับ

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

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

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

รูปแสดง schema ของ table ตรงนี้ผมแก้จากโค้ดในบทความก่อนเล็กน้อยครับ คือให้มันเก็บข้อมูลมาทุก tag ที่มี แล้วเก็บตัวแปร tags มาด้วย เป็น array type ของ tags ใน schema เลยเป็น STRING REPEATED

ที่แถบด้านบนก็สามารถคลิกดูรายละเอียดของ Table และดูตัวอย่างข้อมูลได้ครับ

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

เริ่มจากลองดูช่วงของข้อมูลก่อน ว่ามีข้อมูลตั้งแต่วันไหนถึงวันไหน ก็คือหา min, max ของ field created_time ธรรมดาครับ โดยการพิมพ์คำสั่ง SQL ลงในกล่องข้อความตรงกลาง แล้วกปุ่ม เรียกใช้ ผลลัพธ์ก็จะออกมาเป็นตารางด้านล่างตามภาพนะครับ

SELECT
  MIN(DATE(created_time)) min_date,
  MAX(DATE(created_time)) max_date,
FROM SCRAPE.PANTIP

ต่อไปผมอยากรู้ว่า ในช่วง 3 เดือนนี้เนี่ย tag ไหนมีจำนวนกระทู้มากน้อยอย่างไรบ้าง
ขอไม่ลงรายละเอียด SQL มากนะครับ ไอเดียก็คือ field tags เนี่ยมันเป็น array เราต้องใช้ UNNEST() ในการ "กระจาย" มันออกมา จากนั้นก็ GROUP BY tag แต่ละ tag แล้ว count ออกมา แล้วก็ให้เรียงลำดับ ORDER ตาม count จากมากไปน้อยครับ

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 อันดับแรก ที่มีจำนวนกระทู้มากที่สุดในสามเดือนนี้นะครับ

จากภาพ ด้านบนก็จะมีแสดงเวลาที่ใช้ในการคำนวณด้วยนะครับ จะเห็นว่าใช้เวลาไปแค่ 1 วินาทีนิด ๆ เท่านั้น

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

ทีนี้เราลองหยิบมาดูด้วยตาว่า tag COVID นี่เขาคุยอะไรกันนะครับ

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

ถ้าเป็นของไม่กี่วันนี้ก็ดูไม่ค่อยมีอะไรนะครับ เป็นประมาณอัพเดตข่าวกันมากกว่า

แต่ถ้า scroll ย้อนกลับไปประมาณเดือน 3 ก็จะเห็นกระทู้ประมาณว่า ถ้าติดโควิทจะทำยังไงดี หรือแชร์วิธีการปฏิบัติตัวต่าง ๆ เป็นสังคมที่ค่อนข้าง active เลยครับ

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

ไม่ต้องไปเปิดโปรแกรมไหนเพิ่มเลยครับ BigQuery มีเครื่องมือที่เอาไว้ช่วย visualize และทำ dashboard แบบง่าย ๆ ไว้อยู่แล้ว ชื่อว่า Google Data Studio เราสามารถเอา result จากคำสั่ง SQL ที่เรารัน เข้าไปใน Data Studio ได้เลย โดยการคลิกที่ปุ่ม สำรวจข้อมูล ด้านบน result แล้วเลือก สำรวจด้วย Data Studio ตามในภาพครับ

ทีนี้เราก็จะเด้งมาที่หน้าของ Data Studio ซึ่งพื้นที่ทางด้านซ้ายนี่จะเป็นที่เอาไว้สร้าง dashboard แบบ drag-and-drop ซึ่งตอนนี้ก็จะค้างข้อมูล "กระทู้ COVID" ที่เรา query เอาไว้เมื่อกี้อยู่ ส่วนด้านขวาคือเป็นเหมือน tools box ครับให้เราสามารถเลือกประเภทของ chart และปรับค่าค่าต่าง ๆ ได้ เช่นตัวแปรที่จะเอามาแสดง จำนวน dimensions สี แสงเงาต่าง ๆ

ทีนี้สมมุติว่าผมอยากจะ plot จำนวนของกระทู้ (ซึ่งก็คือกระทู้ใน tag COVID ที่เรา query มาเมื่อกี้) อย่างแรกก็เลือกประเภทของ chart เป็น "อนุกรมเวลา" (time series) แล้วก็ให้ "มิติข้อมูล" (dimension) คือแกน X เป็น created_time ส่วน "เมตริก" (metric) คือแกน Y เป็น Record Count ตามภาพ

พอเลือกเสร็จปุ๊ปก็จะเห็นว่าพื้นที่ dashboard ก็จะโหลดใหม่ได้เป็น time series plot จำนวนของกระทู้กับเวลาตามที่เราต้องการ และเป็น interactive ด้วย คือเราสามารถจะเลื่อน cursor ไปชี้ เพื่อให้แสดงรายละเอียดข้อมูลของ data point ออกมาได้

จะเห็ฺนว่า data studio ช่วย aggregate ข้อมูลให้เราเป็นระดับ "วัน" อัตโนมัติด้วยนะครับ เพราะ created_time นี่เดิมทีเป็นระดับวินาทีเลย ถ้า plot ไปตรง ๆ ก็จะดูไม่รู้เรื่อง แต่ Data Studio คิดส่วนนี้ไว้ให้เราแล้ว เลยไม่ต้องไปแก้เองเลยครับ

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

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

Demo

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

1. จำนวนกระทู้แต่ละ tag ตามเวลา

SELECT
    created_time,
    T AS tag,
FROM SCRAPE.PANTIP P
JOIN UNNEST(P.tags) AS T
WHERE T IN ('ปัญหาความรัก', 'หุ้น', 'ประสบการณ์ความรัก', 'ปัญหาชีวิต', 'ความรักวัยรุ่น')

2. สัดส่วนของแต่ละ tag

SELECT
    T AS tag,
    COUNT(*) AS count
FROM SCRAPE.PANTIP P
JOIN UNNEST(P.tags) AS T
WHERE T IN ('ปัญหาความรัก', 'หุ้น', 'ประสบการณ์ความรัก', 'ปัญหาชีวิต', 'ความรักวัยรุ่น')
GROUP BY 1

3. ช่วงเวลาที่โพส

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

พอได้ charts ที่เราสนใจออกมาแล้วเราสามารถเอามารวมกัน ให้กลายเป็น dashboard อันหนึ่งได้อีกด้วย ใน Data Studio mode Report

ซึ่ง 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 ที่ทำด้านบนเนี่ย ใช้เวลาทำประมาณไม่ถึงครึ่งชั่วโมงเท่านั้นครับ สะดวกมาก ๆ แล้วยังมี features ที่น่าสนใจอื่น ๆ อีกเยอะครับ ไว้ในบทความหน้า ๆ จะมาแนะนำกันต่อนะครับ

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

FB Page: Copy Paste Engineer