[BigQuery Tips] มาระบุ Schemas ด้วย JSON ให้ BigQuery ไม่หลอนเวลาเราอัปไฟล์ CSV, GG 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 ที่ช่วยบอกบุญวิธีแก้ไข)

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

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

☕🙌หากคุณชอบบทความบนเว็บเรา สนับสนุนเราผ่าน ko-fi ได้จ้า คลิก

🐼 [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