[BigQuery Tips] มาระบุ Schemas ด้วย JSON ให้ BigQuery ไม่หลอนเวลาเราอัปไฟล์ CSV, Google Sheets ตอนสร้างเทเบิล

google bigquery config schema template

เมื่อคุณต้องสร้างเทเบิลบน BigQuery โดยใช้ข้อมูลจากไฟล์ CSV หรือข้อมูลที่วางไว้บน Google Sheets

ในหลายครั้ง ระบบ upload table ของ BigQuery ก็ฉลาดเพราะมันสามารถ auto detect ลักษณะข้อมูลเราได้ว่าคอลัมน์นี้เป็น String, Integer ได้ถูกต้อง ทว่ามันก็ detect ได้เพี้ยนหลอนในบ่อยครั้งเช่นกัน จนเราพบว่าถ้าเราเขียนระบุให้มันโดยใช้ JSON กำหนดไปเลยมันสบายใจกว่ากันเยอะ

ว่าแล้วก็เลยเอาสคริปต์ที่เราใช้บ่อยๆ ในมาแปะในบล็อกนี้ด้วยซะเลย

• ขั้นตอนการสร้างเทเบิลบน BigQuery ด้วยการ Upload CSV, Google Sheets

การสร้างเทเบิลบน BigQuery เราสามารถเข้ามาที่จุดสามจุดทางเมนูซ้ายมือแล้วเลือก Create Table

เมื่อกดสร้างเทเบิลปุ๊บ จะมีหน้าต่างโผล่ขึ้นมาทางขวา
กรอบที่ [1] ให้เราเลือกได้ว่าจะ Create table from ช่องทางไหน

• Drive: ผ่าน Google Sheets, CSV บน Google Drive
• Upload: ผ่านไฟล์ CSV, Parquet, Avro ฯลฯ

กรอบที่ [2] คือระบุ schemas, dataset, ชื่อ table

กรอบที่ [3] คือการกำหนด attribute ของคอลัมน์ว่าเป็นไทป์อะไรบ้าง ไฮไลท์ของเราอยู่ตรงนี้นี่ล่ะ
ให้เราติ๊กช่อง Edit As Text เพื่อเตรียมใช้เทมเพลตที่เราจะพูดต่อด้านล่าง

• Template ของโค้ด JSON ที่จะใช้

↓ ถ้าเราใส่ << กขคง >> ในช่องด้านล่าง มันแปลว่าให้ variable ตรงนี้ตั้งชื่อเป็นอะไรแล้วแต่ใจเลยได้นะ

[

{
"name": "<< column1_name >>" ,
"type": "<< column1_type >>"
}
,

{
"name": "<< column2_name >>" ,
"type": "<< column2_type >>"
}

]

• คำอธิบายข้อมูลต่าง ๆ ใน Template ที่ต้องระบุ

“name”: ชื่อคอลัมน์ (บังคับต้องใส่)
“type”: ประเภท Data Type ของคอลัมน์ (บังคับต้องใส่) สามารถใส่ค่าได้หลายอย่างเช่น

• INT64
• FLOAT64
• STRING
• DATE
• TIMESTAMP
• BOOL

ใครที่สนใจอยากอ่านเรื่อง Data Type เพิ่มเติม สามารถไปอ่านได้ในเอกสารนี้ คลิก 🌶️

“mode:” ลักษณะคอลัมน์ ว่าคอลัมน์นี้เป็น NULL ได้มั้ย (ไม่จำเป็นต้องใส่ก็ได้ ซึ่งถ้าเราไม่ระบุ มันจะบังคับว่าคอลัมน์นี้ห้ามเป็น NULL)

🌶️ อ่านเรื่อง MODE เพิ่มเติมได้ในเอกสารนี้ คลิก 🌶️

• ตัวอย่างจริง

[{
  "name": "transaction_id",
  "type": "STRING"
      
}, 

{
  "name": "transaction_date",
  "type": "STRING"
}, 

{
  "name": "channel",
  "type": "STRING"
},
 
{
  "name": "funds_name",
  "type": "STRING"
},
 
{
  "name": "NAV",
  "type": "FLOAT64" 
},

 {
  "name": "fund_type",
  "type": "STRING",
  "mode": "NULLABLE"
},

 {
  "name": "amount",
  "type": "FLOAT64"
},
 
{
  "name": "unit",
  "type": "STRING",
  "mode": "FLOAT64"
} ]

• ข้อดีของการระบุ schemas ด้วยตัวเอง

• ไม่เสียเวลา ถ้ามัน auto detect attribute คอลัมน์ให้เราผิด แล้วเราต้องมานั่งกำหนดให้มันอีกรอบ เน้นช้าแต่ชัวร์
• อ่านหัวคอลัมน์ไม่ออก เหมือนจะเป็น bug ของ Google เพราะเราเคย upload file CSV ซึ่งคอลัมน์ทั้งหมดเป็น text ไม่มีตัวเลขเลย ระบบ auto detect ของ BigQuery ก็หลอนไปเลย อ่านหัวคอลัมน์เราไม่ได้ซะงั้นแถมเปลี่ยนชื่อ header เรากลายเป็น string_field_0 ไปงั้นแทน เอ๊าาา สุดท้ายก็ต้องใช้ท่าเขียน JSON ระบุสคีม่าให้มันเข้าไปใหม่อยู่ดี (ขอบคุณกระทู้ StackOverflow ที่ช่วยบอกบุญวิธีแก้ไข)

วิธีแก้ Error: BIGQUERY_API_ERR คิวรี่เทเบิลที่ต่อกับ Google Sheets ไม่ได้ !

หลายคนอาจไม่ได้ใช้หน้าเว็บ BigQuery ในการคิวรี่เทเบิลตรงๆ อาจจะมีต่อโปรแกรมนอกตัวอื่น เพื่อให้คิวรี่ดูผลลัพธ์ดาต้าได้สะดวกขึ้น เช่นเราใช้โปรแกรม DBeaver แล้วเอาไฟล์ key มาต่อ ซึ่ง DBeaver อำนวยความสะดวกเราในการดูข้อมูลที่มีคอลัมน์เยอะๆ ได้สะดวกกว่าคิวรี่บนเว็บ BigQuery มากๆ

แต่การสร้างเทเบิลที่ต่อตรงแบบนี้มักมีปัญหาเวลาเราคิวรี่ SELECT * FROM จากโปรแกรมนอกตัวอื่น มันจะขึ้งว่า BIGQUERY_API_ERROR

วิธีแก้กันเหนียวอีกขั้นจึงเป็นการ สร้างเทเบิลหน้ากากอีกอันมาครอบเทเบิลตัว Google Sheets นี้อีกที

• ในตัวอย่างนี้เราตั้งชื่อเทเบิลหน้ากากโดยลงท้ายว่า _mini_master
• ส่วนเทเบิลที่ต่อตรงกับ Google Sheets เราลงท้ายชื่อเทเบิลว่า _gsheet

เทมเพลต:

 CREATE OR REPLACE TABLE   `project_id.dataset.table_name_mini_master` as
 (
 SELECT * FROM 
  `project_id.dataset.table_name_gsheet`);

ตัวอย่างโค้ด:

`projectid.dataset.exclude_author_channel_name_list_gsheet_mini_master` AS 
 (
 SELECT * FROM 
  `projectid.dataset.exclude_author_channel_name_list_gsheet`
);

💢 หมายเหตุ: อย่าลืมอัปเดตเทเบิลหน้ากากนี้ทุกครั้งที่แก้ GG Sheets นะ💢

แต่ทุกครั้งที่ข้อมูลใน Google Sheets มีการเปลี่ยนแปลง เราต้องมารันคิวรี่เพื่ออัปเดตเทเบิล mini_master เช่นกัน

• ถ้าชอบโพสนี้ สนับสนุนคนเขียนบน ko-fi ได้น้า 👍

หากคุณชอบคอนเทนต์ของพวกเรา อยากเป็นสายซัพ สนับสนุน ช่วยค่าขนมและกาแฟ เป็นครั้งคราว สามารถซัพพอร์ตที่ ko-fi ได้จ้า (www.ko-fi.com/gleegmjournal) คลิก

• บล็อกหัวข้ออื่น ๆ ที่คุณอาจสนใจ

🔑 แลก goodreads, MyAnimeList, letterboxd ติดตามเพจ Facebook กันได้ใน About Me

🐼 [pandas Tips] การดึง Text ใน dataframe โดยการใช้แพทเทิร์น Regex extract ใน pandas

📊[Excel/Google Sheets Tips] รวมสูตรคลีนลิงค์เว็บ URL ด้วย regex (ลบ/ดึงตัวอักษรใน URL)

📊 [Excel Tips] Find and Replace ดอกจัน (*) ใน Excel ต้องกรอกว่า ~* นะ

📖 รีวิว Effective Pandas โดย Matt Harrison ตำราสำหรับคนโฟกัส python pandas โดยเฉพาะ

📊 [pandas Tips] มาระเบิดคอลัมน์ที่ Value เป็นลิสต์ a,b,c ไปขึ้นบรรทัดใหม่กัน ! Let’s Explode a list to new row on pandas/BigQuery

💻 รีวิว Datacamp เว็บไซต์เรียน Data แบบจับมือพิมพ์

✒ บันทึกเรียน Datacamp Course Journal ประจำปี 2025 | 2024 | 2023

✒ [Tableau Tips] Start-End Date Calculation to auto compare MOM on parameter

💻 [SQL Tips] Clean Date Format inconsistency using Regex in Presto SQL

📊 บล็อกหัวข้อ Data Analytics สารพัดวงการ Data และเรื่อง Techๆ | 🏀 กีฬา คลิก |
📺 รีวิว Anime อนิเมะ |🎧 รีวิว Music ดนตรี | 🎬 รีวิว Film Series หนัง ซีรีส์ | 🪴 สารบัญรีวิวทุกประเภท All Reviews

• References

• https://docs.cloud.google.com/bigquery/docs/schemas#specifying_a_json_schema_file

regex url excel