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

1. บทนำ

BigQuery คือฐานข้อมูลการวิเคราะห์แบบ NoOps ที่มีการจัดการอย่างเต็มรูปแบบและมีต้นทุนต่ำของ Google BigQuery ช่วยให้คุณค้นหาข้อมูลขนาดเทราไบต์ได้โดยไม่ต้องมีผู้ดูแลฐานข้อมูลหรือโครงสร้างพื้นฐาน BigQuery ใช้ SQL ที่คุ้นเคยและรูปแบบการเรียกเก็บเงินแบบจ่ายเฉพาะสิ่งที่ใช้ BigQuery ช่วยให้คุณมุ่งเน้นการวิเคราะห์ข้อมูลเพื่อค้นหาข้อมูลเชิงลึกที่มีความหมาย ในโค้ดแล็บนี้ คุณจะได้ใช้เครื่องมือบรรทัดคำสั่ง 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 ทั้งหมด ซึ่งจะเรียกว่า PROJECT_ID ในภายหลังใน Codelab นี้

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

Cloud Shell

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

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

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

bce75f34b2c53987.png

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

70f315d7b402b476.png

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

fbe3a0674c982259.png

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

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 โดยเรียกใช้คำสั่ง cloudshell edit ซึ่งจะเปิดหน้าต่างเบราว์เซอร์ใหม่ที่มีเครื่องมือแก้ไขโค้ดและแผง 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 load เพื่อโหลดไฟล์ 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 load ยังโหลดไฟล์จาก 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 query เพื่อเรียกใช้การค้นหา

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

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

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