ค้นหาชุดข้อมูล Wikipedia ใน BigQuery

1. ภาพรวม

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

ใน Codelab นี้ คุณจะได้สำรวจชุดข้อมูล Wikipedia โดยใช้ BigQuery

สิ่งที่คุณจะได้เรียนรู้

• วิธีใช้ BigQuery
• วิธีโหลดชุดข้อมูลในชีวิตจริงลงใน BigQuery
• วิธีเขียนการค้นหาเพื่อรับข้อมูลเชิงลึกเกี่ยวกับชุดข้อมูลขนาดใหญ่

สิ่งที่ต้องมี

• โปรเจ็กต์ Google Cloud
• เบราว์เซอร์ เช่น Google Chrome หรือ Firefox

แบบสำรวจ

คุณจะใช้บทแนะนำนี้อย่างไร

อ่านอย่างเดียว อ่านและทำแบบฝึกหัด

โปรดให้คะแนนประสบการณ์การใช้งาน Google Cloud ของคุณ

มือใหม่ ระดับกลาง ผู้ชำนาญ

2. การตั้งค่าและข้อกำหนด

เปิดใช้ BigQuery

หากยังไม่มีบัญชี Google คุณต้องสร้างบัญชี

1. ลงชื่อเข้าใช้ Google Cloud Console แล้วไปที่ BigQuery นอกจากนี้คุณยังเปิด UI เว็บของ BigQuery ได้โดยตรงโดยการป้อน URL ต่อไปนี้ในเบราว์เซอร์ของคุณ

https://console.cloud.google.com/bigquery

2. ยอมรับข้อกำหนดในการให้บริการ
3. คุณต้องสร้างโปรเจ็กต์ก่อนจึงจะใช้ BigQuery ได้ ทำตามข้อความแจ้งเพื่อสร้างโปรเจ็กต์ใหม่

เลือกชื่อโปรเจ็กต์และจดรหัสโปรเจ็กต์ไว้

รหัสโปรเจ็กต์คือชื่อที่ไม่ซ้ำกันในโปรเจ็กต์ Google Cloud ทั้งหมด และจะมีการอ้างอิงใน Codelab ว่า PROJECT_ID ในภายหลัง

Codelab นี้ใช้ทรัพยากร BigQuery ภายในขีดจำกัดแซนด์บ็อกซ์ของ BigQuery ไม่จำเป็นต้องมีบัญชีสำหรับการเรียกเก็บเงิน หากต้องการนำขีดจำกัดแซนด์บ็อกซ์ออกในภายหลัง คุณสามารถเพิ่มบัญชีสำหรับการเรียกเก็บเงินได้โดยลงชื่อสมัครใช้ช่วงทดลองใช้ฟรีของ Google Cloud

คุณโหลดชุดข้อมูล Wikipedia ในส่วนถัดไป

3. สร้างชุดข้อมูล

ก่อนอื่นให้สร้างชุดข้อมูลใหม่ในโปรเจ็กต์ ชุดข้อมูลประกอบด้วยหลายตาราง

1. หากต้องการสร้างชุดข้อมูล ให้คลิกชื่อโปรเจ็กต์ใต้แผงทรัพยากร แล้วคลิกสร้างชุดข้อมูล ดังนี้

2. ป้อน lab เป็นรหัสชุดข้อมูล

3. คลิกสร้างชุดข้อมูลเพื่อสร้างชุดข้อมูลที่ว่างเปล่า

4. โหลดข้อมูลด้วยโปรแกรมบรรทัดคำสั่ง BQ

เปิดใช้งาน Cloud Shell

1. คลิกเปิดใช้งาน Cloud Shell จาก Cloud Console

หากคุณไม่เคยเริ่มต้นใช้งาน Cloud Shell มาก่อน คุณจะเห็นหน้าจอตรงกลาง (ครึ่งหน้าล่าง) ซึ่งอธิบายว่านี่คืออะไร หากเป็นเช่นนั้น ให้คลิกดำเนินการต่อ (คุณจะไม่เห็นการดำเนินการนี้อีก) หน้าจอแบบครั้งเดียวมีลักษณะดังนี้

การจัดสรรและเชื่อมต่อกับ Cloud Shell ใช้เวลาเพียงไม่กี่นาที

เครื่องเสมือนนี้เต็มไปด้วยเครื่องมือการพัฒนาทั้งหมดที่คุณต้องการ โดยมีไดเรกทอรีหลักขนาด 5 GB ที่ทำงานอย่างต่อเนื่องใน Google Cloud ซึ่งจะช่วยเพิ่มประสิทธิภาพของเครือข่ายและการตรวจสอบสิทธิ์ได้อย่างมาก งานส่วนใหญ่ใน Codelab นี้สามารถทำได้โดยใช้เบราว์เซอร์หรือ Chromebook เท่านั้น

เมื่อเชื่อมต่อกับ Cloud Shell คุณควรเห็นว่าได้รับการตรวจสอบสิทธิ์แล้ว และโปรเจ็กต์ได้รับการตั้งค่าเป็นรหัสโปรเจ็กต์แล้ว

2. เรียกใช้คำสั่งต่อไปนี้ใน Cloud Shell เพื่อยืนยันว่าคุณได้รับการตรวจสอบสิทธิ์แล้ว

gcloud auth list

เอาต์พุตจากคำสั่ง

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

หมายเหตุ: เครื่องมือบรรทัดคำสั่ง gcloud เป็นเครื่องมือบรรทัดคำสั่งแบบรวมที่มีประสิทธิภาพใน Google Cloud โดยจะติดตั้งมาล่วงหน้าใน Cloud Shell คุณจะเห็นการรองรับการใช้งานแท็บเสร็จสมบูรณ์ โปรดดูข้อมูลเพิ่มเติมที่หัวข้อภาพรวมของเครื่องมือบรรทัดคำสั่ง gcloud

3. เรียกใช้คำสั่งต่อไปนี้ใน Cloud Shell เพื่อยืนยันว่าคำสั่ง gcloud รู้เกี่ยวกับโปรเจ็กต์ของคุณ

gcloud config list project

เอาต์พุตจากคำสั่ง

[core]
project = <PROJECT_ID>

หากไม่ใช่ ให้ตั้งคำสั่งด้วยคำสั่งนี้

gcloud config set project <PROJECT_ID>

เอาต์พุตจากคำสั่ง

Updated property [core/project].

โหลดข้อมูลลงใน BigQuery

เพื่อความสะดวก เราได้เผยแพร่ข้อมูลบางส่วนของวันที่ 10 เมษายน 2019 จากชุดข้อมูลการดูหน้าเว็บ Wikimedia บน Google Cloud Storage ที่ gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz ไฟล์ข้อมูลเป็นไฟล์ CSV ของ GZip คุณจะโหลดไฟล์นี้ได้โดยตรงโดยใช้ยูทิลิตีบรรทัดคำสั่ง bq คุณยังอธิบายสคีมาของไฟล์เป็นส่วนหนึ่งของคำสั่งโหลด

คุณจะดูรหัสโปรเจ็กต์ได้จากคอนโซล API หรือจากบรรทัดคำสั่ง: "gcloud config list" แล้วมองหาค่าของ "project"

นอกจากนี้ เมื่อเรียกใช้ bq เป็นครั้งแรก ระบบอาจขอให้คุณเลือกโปรเจ็กต์เริ่มต้น เมื่อเลือกแล้ว คุณสามารถเรียกใช้คำสั่งด้านบนอีกครั้งเพื่อโหลดข้อมูลได้

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

การโหลดข้อมูลอาจใช้เวลาสักครู่ โปรดรอให้การดำเนินการเสร็จสมบูรณ์

คุณใช้ตัวเลือกขั้นสูง 2-3 รายการเพื่อโหลดไฟล์การดูหน้าเว็บ ดังนี้

• ตั้งค่า --source_format CSV เพื่อระบุว่าควรแยกวิเคราะห์ไฟล์เป็นไฟล์ CSV ขั้นตอนนี้เป็นแบบไม่บังคับเนื่องจาก CSV เป็นรูปแบบเริ่มต้น
• ตั้งค่า --field_delimiter " " เพื่อระบุว่ามีการใช้การเว้นวรรค 1 ช่องเพื่อคั่นช่อง
• ตั้งค่า --allow_jagged_rows ให้รวมแถวที่มีจำนวนคอลัมน์น้อยลงและไม่สนใจข้อผิดพลาดขณะโหลดไฟล์ CSV
• ตั้งค่า --quote "" เพื่อระบุว่าสตริงไม่มีเครื่องหมายคำพูด
• ตั้งค่า --max_bad_records 3 ให้ละเว้นข้อผิดพลาดไม่เกิน 3 รายการขณะแยกวิเคราะห์ไฟล์ CSV

ดูข้อมูลเพิ่มเติมเกี่ยวกับบรรทัดคำสั่ง bq ได้ในเอกสารประกอบ

5. แสดงตัวอย่างชุดข้อมูล

ในคอนโซล BigQuery ให้เปิดตารางที่คุณเพิ่งโหลดขึ้นมา

1. ขยายโปรเจ็กต์
2. ขยายชุดข้อมูล
3. เลือกตาราง

คุณดูสคีมาของตารางได้ในแท็บสคีมา 4. ดูจำนวนข้อมูลในตารางโดยไปที่แท็บรายละเอียด ดังนี้

5. เปิดแท็บแสดงตัวอย่างเพื่อดูแถวที่เลือกจากตาราง

6. เขียนคำค้นหา

1. คลิกเขียนคำค้นหาใหม่ แล้วทำดังนี้

ซึ่งตัวแก้ไขคำค้นหาจะปรากฏขึ้น ดังนี้

2. หาจำนวนการดู Wikimedia ทั้งหมดระหว่างวันที่ 10 เมษายน 2019 ตั้งแต่เวลา 14:00 ถึง 15:00 น. โดยเขียนคำค้นหานี้

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. คลิกเรียกใช้

ผลลัพธ์จะแสดงที่ด้านล่างในไม่กี่วินาที ทั้งยังบอกปริมาณข้อมูลที่ได้รับการประมวลผลด้วย

การค้นหานี้ประมวลผล 123.9MB แม้ว่าตารางจะมีขนาด 691.4MB BigQuery จะประมวลผลเฉพาะไบต์จากคอลัมน์ที่ใช้ในการค้นหาเท่านั้น ดังนั้นจำนวนข้อมูลทั้งหมดที่ประมวลผลจึงอาจน้อยกว่าขนาดตารางอย่างมาก เมื่อใช้การจัดคลัสเตอร์และการแบ่งพาร์ติชัน คุณจะลดจำนวนข้อมูลที่ประมวลผลได้มากยิ่งขึ้น

คุณสามารถประมวลผล/ค้นหาข้อมูลสูงสุด 1 TB ได้ฟรีในแต่ละเดือน โปรดดูรายละเอียดเพิ่มเติมในหน้าราคาของ BigQuery

7. ข้อความค้นหาขั้นสูงเพิ่มเติม

ดูการดูหน้าเว็บ Wikipedia

ชุดข้อมูล Wikimedia มีการดูหน้าเว็บสำหรับโครงการ Wikimedia ทั้งหมด (รวมถึง Wikipedia, Wiktionary, Wikibooks และ Wikiquotes) จำกัดข้อความค้นหาให้เหลือเพียงหน้า Wikipedia ภาษาอังกฤษโดยเพิ่มคำสั่ง WHERE ดังนี้

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

โปรดสังเกตว่าการค้นหาคอลัมน์ wiki เพิ่มเติมทำให้ปริมาณข้อมูลที่ประมวลผลเพิ่มขึ้นจาก 124 MB เป็น 204 MB

BigQuery รองรับวลี SQL ที่คุ้นเคยหลายรายการ เช่น CONTAINS, GROUP BY, ORDER BY และฟังก์ชันการรวมข้อมูลหลายรายการ นอกจากนี้ คุณยังสามารถใช้นิพจน์ทั่วไปเพื่อค้นหาช่องข้อความได้อีกด้วย ลองใช้:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

ดูข้อมูลเพิ่มเติมเกี่ยวกับไวยากรณ์ BigQuery ในเอกสารอ้างอิงการค้นหา BigQuery

ค้นหาในหลายตาราง

คุณเลือกช่วงของตารางเพื่อสร้างกลุ่มได้โดยใช้ตารางไวลด์การ์ด

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

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. ในตัวแก้ไขคำค้นหา ให้ค้นหาทั้ง 2 ตารางที่โหลดไว้ด้วยการค้นหาตารางด้วย "pageviews_2019" นำหน้า:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

คุณสามารถกรองตารางอย่างเฉพาะเจาะจงมากขึ้นโดยใช้คอลัมน์สมมติ _TABLE_SUFFIX การค้นหานี้จำกัดไว้เฉพาะตารางที่ตรงกับวันที่ 10 เมษายน

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. กำลังล้างข้อมูล

(ไม่บังคับ) ลบชุดข้อมูลที่คุณสร้างด้วยคำสั่ง bq rm ใช้แฟล็ก -r เพื่อนำตารางที่มีอยู่ออก

bq rm -r lab

9. ยินดีด้วย

คุณใช้ BigQuery และ SQL เพื่อค้นหาชุดข้อมูลการดูหน้าเว็บของ Wikipedia ในชีวิตจริง คุณมีอำนาจในการค้นหาชุดข้อมูลระดับเพตะไบต์

ดูข้อมูลเพิ่มเติม

• ลองใช้ Subreddit ของ BigQuery เพื่อดูวิธีที่คนอื่นๆ ใช้ BigQuery ในปัจจุบัน
• ค้นหาชุดข้อมูลสาธารณะที่มีอยู่ใน BigQuery
• ดูวิธีโหลดข้อมูลลงใน BigQuery