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

1. ภาพรวม

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

ในโค้ดแล็บนี้ คุณจะได้สำรวจชุดข้อมูล 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 ทั้งหมด ซึ่งจะเรียกว่า PROJECT_ID ในภายหลังใน Codelab นี้

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

คุณจะโหลดชุดข้อมูลวิกิพีเดียในส่วนถัดไป

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

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

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

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

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

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

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

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

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

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

เครื่องเสมือนนี้มีเครื่องมือพัฒนาซอฟต์แวร์ทั้งหมดที่คุณต้องการ โดยมีไดเรกทอรีหลักแบบถาวรขนาด 5 GB และทำงานใน Google Cloud ซึ่งช่วยเพิ่มประสิทธิภาพเครือข่ายและการตรวจสอบสิทธิ์ได้อย่างมาก คุณสามารถทำงานในโค้ดแล็บนี้ได้โดยใช้เพียงเบราว์เซอร์หรือ 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 คุณจะเห็นว่าเครื่องมือนี้รองรับการเติมข้อความอัตโนมัติด้วยการกด Tab ดูข้อมูลเพิ่มเติมได้ที่ภาพรวมเครื่องมือบรรทัดคำสั่ง 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 " " เพื่อระบุว่าใช้ช่องว่างเดียวในการคั่นเขตข้อมูล
• ตั้งค่า --allow_jagged_rows เพื่อรวมแถวที่มีจำนวนคอลัมน์น้อยกว่าและไม่สนใจข้อผิดพลาดขณะโหลดไฟล์ CSV
• ตั้งค่า --quote "" เพื่อระบุว่าสตริงไม่มีเครื่องหมายคำพูด
• ตั้งค่า --max_bad_records 3 เพื่อไม่สนใจข้อผิดพลาดอย่างน้อย 3 รายการขณะแยกวิเคราะห์ไฟล์ CSV

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

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

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

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

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

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

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

1. คลิกเขียนคำค้นหาใหม่

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

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

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

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

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

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

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

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

ค้นหายอดดูหน้าวิกิพีเดีย

ชุดข้อมูล 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. ก่อนอื่น ให้สร้างตารางที่ 2 เพื่อทำการค้นหาโดยการโหลดการดูหน้าเว็บของชั่วโมงถัดไปลงในตารางใหม่

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 ในโลกแห่งความเป็นจริง คุณมีสิทธิ์ค้นหาชุดข้อมูลขนาดเพตะไบต์

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

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