โหลดและค้นหาข้อมูลด้วยเครื่องมือบรรทัดคำสั่ง bq สำหรับ BigQuery

1. บทนำ

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

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

  • วิธีใช้เครื่องมือบรรทัดคำสั่ง BQ สำหรับ BigQuery
  • วิธีโหลดไฟล์ข้อมูลในเครื่องลงในตาราง BigQuery

สิ่งที่คุณต้องมี

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

2. ตั้งค่า

เปิดใช้ BigQuery

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

  1. ลงชื่อเข้าใช้ Google Cloud Console แล้วไปที่ BigQuery นอกจากนี้คุณยังเปิด UI เว็บของ BigQuery ได้โดยตรงโดยการป้อน URL ต่อไปนี้ในเบราว์เซอร์ของคุณ
https://console.cloud.google.com/bigquery
  1. ยอมรับข้อกำหนดในการให้บริการ
  2. คุณต้องสร้างโปรเจ็กต์ก่อนจึงจะใช้ BigQuery ได้ ทำตามข้อความแจ้งเพื่อสร้างโปรเจ็กต์ใหม่

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

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

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

Cloud Shell

คุณจะใช้ Cloud Shell ซึ่งเป็นสภาพแวดล้อมบรรทัดคำสั่งที่ทำงานใน Google Cloud

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

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

bce75f34b2c53987.png

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

70f315d7b402b476.png

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

fbe3a0674c982259.png

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

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

  1. เรียกใช้คำสั่งต่อไปนี้ใน 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`
  1. เรียกใช้คำสั่งต่อไปนี้ใน Cloud Shell เพื่อยืนยันว่าคำสั่ง gcloud รู้เกี่ยวกับโปรเจ็กต์ของคุณ
gcloud config list project

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

[core]
project = <PROJECT_ID>

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

gcloud config set project <PROJECT_ID>

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

Updated property [core/project].

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

สร้างชุดข้อมูลเพื่อประกอบตาราง

ชุดข้อมูลคืออะไร

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

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

ใน Cloud Shell ให้ใช้คำสั่ง bq mk เพื่อสร้างชุดข้อมูลที่ชื่อ "bq_load_codelab"

bq mk bq_load_codelab

ดูพร็อพเพอร์ตี้ของชุดข้อมูล

ยืนยันว่าคุณสร้างชุดข้อมูลโดยดูพร็อพเพอร์ตี้ของชุดข้อมูลด้วยคำสั่งแสดง BQ

bq show bq_load_codelab

คุณควรเห็นผลลัพธ์ที่คล้ายกับข้อความต่อไปนี้

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. สร้างไฟล์ข้อมูล

BigQuery สามารถโหลดข้อมูลจากรูปแบบข้อมูลหลายรูปแบบ เช่น JSON ที่คั่นด้วยการขึ้นบรรทัดใหม่, Avro และ CSV โปรดใช้ CSV เพื่อความง่าย

สร้างไฟล์ CSV

สร้างไฟล์ CSV เปล่าใน Cloud Shell

touch customer_transactions.csv

เปิดไฟล์ CSV ในตัวแก้ไขโค้ดใน Cloud Shell โดยเรียกใช้คำสั่งแก้ไข Cloud Shell ซึ่งจะเปิดหน้าต่างเบราว์เซอร์ใหม่ด้วยตัวแก้ไขโค้ดและแผง Cloud Shell

cloudshell edit customer_transactions.csv

ในตัวแก้ไขโค้ด ให้ป้อนค่าที่คั่นด้วยคอมมาเพื่อโหลดลงใน BigQuery

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

บันทึกไฟล์ CSV โดยคลิกไฟล์ > แก้ไข

5. โหลดข้อมูล

ใช้คำสั่งการโหลด BQ เพื่อโหลดไฟล์ CSV ลงในตาราง BigQuery

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

คุณใช้ตัวเลือกต่อไปนี้

  • --source_format=CSV ใช้รูปแบบข้อมูล CSV เมื่อแยกวิเคราะห์ไฟล์ข้อมูล
  • --skip_leading_rows=1 ข้ามบรรทัดแรกในไฟล์ CSV เพราะเป็นแถวส่วนหัว
  • Bq_load_codelab.customer_transactions—the first positional argument— กำหนดตารางที่จะโหลดข้อมูล
  • ./customer_transactions.csv อาร์กิวเมนต์ที่ระบุตำแหน่งที่ 2 เป็นตัวกำหนดไฟล์ที่จะโหลด นอกเหนือจากไฟล์ในเครื่องแล้ว คำสั่งโหลด BQ ยังโหลดไฟล์จาก Cloud Storage ด้วย gs://my_bucket/path/to/file URIs ได้ด้วย
  • สคีมาซึ่งสามารถกำหนดในไฟล์สคีมา JSON หรือเป็นรายการที่คั่นด้วยคอมมา (คุณใช้รายการที่คั่นด้วยคอมมาเพื่อให้เข้าใจง่าย)

คุณใช้สคีมาต่อไปนี้ในตาราง customer_transactions

  • Id:string: ตัวระบุลูกค้า
  • Zip:string: รหัสไปรษณีย์ของสหรัฐอเมริกา
  • Ttime:timestamp: วันที่และเวลาที่ทำธุรกรรม
  • Amount:numeric: จำนวนเงินของธุรกรรม (คอลัมน์ตัวเลขจัดเก็บข้อมูลในรูปแบบทศนิยม ซึ่งมีประโยชน์สำหรับมูลค่าเงิน)
  • Fdbk:float: คะแนนจากแบบสำรวจความคิดเห็นเกี่ยวกับธุรกรรม
  • Sku:string: ตัวระบุสำหรับสินค้าที่ซื้อ

ดูรายละเอียดตาราง

ตรวจสอบว่าตารางโหลดขึ้นมาแล้วโดยแสดงพร็อพเพอร์ตี้ของตาราง

bq show bq_load_codelab.customer_transactions

เอาต์พุต:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. ค้นหาข้อมูล

เมื่อโหลดข้อมูลแล้ว คุณจะค้นหาได้โดยใช้ UI ในเว็บของ BigQuery, คำสั่ง bq หรือ API คำค้นหาจะรวมข้อมูลเข้ากับชุดข้อมูลใดก็ได้ ตราบใดที่สิ่งเหล่านั้นอยู่ในตำแหน่งเดียวกัน) ที่คุณมีสิทธิ์อ่าน

เรียกใช้การค้นหา SQL มาตรฐานที่รวมชุดข้อมูลของคุณกับชุดข้อมูลรหัสไปรษณีย์ของสหรัฐอเมริกา และสรุปธุรกรรมตามรัฐในสหรัฐอเมริกา ใช้คำสั่งการค้นหา BQ เพื่อเรียกใช้คำค้นหา

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

คำสั่งนั้นควรจะแสดงผลดังนี้

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

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

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. ล้างข้อมูล

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

bq rm -r bq_load_codelab

8. ยินดีด้วย

คุณได้อัปโหลดตารางไปยัง BigQuery และค้นหาตารางแล้ว

สิ่งที่คุณได้พูดคุยกัน

  • การใช้เครื่องมือบรรทัดคำสั่ง bq เพื่อโต้ตอบกับ BigQuery
  • การรวมข้อมูลและชุดข้อมูลสาธารณะด้วยการค้นหา BigQuery

ขั้นตอนถัดไป

โปรดดูข้อมูลเพิ่มเติมดังต่อไปนี้