Hive Join & SubQuery Tutorial พร้อมตัวอย่าง

เข้าร่วมแบบสอบถาม

สามารถเรียกใช้คำสั่ง Join บนสองตารางที่มีอยู่ในระบบได้ รังเพื่อให้เข้าใจแนวคิดเรื่องการเชื่อมต่อตารางได้ชัดเจนยิ่งขึ้น เราจึงสร้างตารางสองตารางขึ้นมาดังนี้:

• sample_joins (เกี่ยวข้องกับรายละเอียดลูกค้า)
• sample_joins1 (เกี่ยวข้องกับรายละเอียดคำสั่งซื้อที่พนักงานสั่งซื้อ)

ขั้นตอน 1) สร้างตาราง “sample_joins” โดยมีคอลัมน์ชื่อ Id, Name, Age, address และ salary ของพนักงาน ภาพหน้าจอด้านล่างแสดงคำสั่ง CREATE TABLE และการยืนยันการสร้างตาราง

ขั้นตอน 2) กำลังโหลดและแสดงข้อมูล ภาพหน้าจอถัดไปแสดงคำสั่งโหลดตามด้วยเนื้อหาของตาราง

จากภาพหน้าจอข้างต้น:

1. กำลังโหลดข้อมูลลงใน Sample_joins จาก Customers.txt
2. กำลังแสดงเนื้อหาตาราง example_joins

ขั้นตอน 3) ขั้นตอนการสร้างตาราง sample_joins1 จากนั้นโหลดและแสดงข้อมูลจากตารางดังกล่าว ดังที่แสดงในภาพหน้าจอด้านล่าง

จากภาพหน้าจอข้างต้น เราสามารถสังเกตได้ดังต่อไปนี้:

1. สร้างตาราง sample_joins1 โดยมีคอลัมน์ Orderid, Date1, Id และ Amount
2. กำลังโหลดข้อมูลลงใน Sample_joins1 จาก orders.txt
3. กำลังแสดงบันทึกที่มีอยู่ใน Sample_joins1

ต่อไป เราจะมาดูประเภทต่างๆ ของการเชื่อมต่อตารางที่เราสร้างขึ้น ก่อนหน้านั้น คุณต้องพิจารณาประเด็นต่อไปนี้เกี่ยวกับการเชื่อมต่อตาราง

ข้อควรระวังบางประการในการใช้คำสั่ง join:

• การเชื่อมต่อแบบเท่ากันเท่านั้นที่อนุญาตในการเชื่อมต่อแบบ JOIN
• สามารถรวมตารางมากกว่าสองตารางในแบบสอบถามเดียวกันได้
• การเชื่อมต่อแบบ LEFT, RIGHT และ FULL OUTER มีไว้เพื่อให้ควบคุมเงื่อนไข ON ได้มากขึ้น ในกรณีที่ไม่มีการจับคู่
• การเชื่อมต่อไม่เป็นไปตามคุณสมบัติการสลับที่
• การรวมจะเชื่อมโยงด้านซ้ายโดยไม่คำนึงว่าจะเป็นการรวม LEFT หรือ RIGHT

ข้อจำกัดเรื่องความเท่าเทียมกันสะท้อนให้เห็นถึง Hive ในรูปแบบที่เป็นมาหลายปีแล้ว ตั้งแต่ Hive เวอร์ชัน 2.2.0 เป็นต้นไป รองรับนิพจน์ที่ซับซ้อนในส่วน ON (HIVE-15211) ดังนั้นเงื่อนไขที่ไม่ใช่ความเท่าเทียมกันจึงได้รับการยอมรับในเวอร์ชันปัจจุบัน สำหรับเวอร์ชันเก่ากว่า เงื่อนไขจะต้องเป็นการทดสอบความเท่าเทียมกัน โดยส่วนอื่นๆ จะถูกย้ายไปอยู่ในส่วน WHERE

การรวมประเภทต่างๆ

การเชื่อมต่อมี 4 ประเภท ได้แก่:

• การเข้าร่วมภายใน
• การรวมภายนอกด้านซ้าย
• การรวมภายนอกด้านขวา
• การรวมภายนอกแบบเต็ม

แต่ละประเภทจะแสดงให้เห็นด้านล่างโดยใช้ตารางสองตารางเดียวกัน ดังนั้นสิ่งเดียวที่เปลี่ยนแปลงระหว่างตัวอย่างคือแถวที่ไม่ตรงกันที่จะยังคงอยู่

การเข้าร่วมภายใน

ข้อมูลที่เหมือนกันในทั้งสองตารางจะถูกดึงมาโดยใช้การเชื่อมต่อภายใน (inner join) นี้ ผลลัพธ์ในภาพหน้าจอด้านล่างจะมีเฉพาะลูกค้าที่มีคำสั่งซื้อที่ตรงกันเท่านั้น

จากภาพหน้าจอข้างต้น เราสามารถสังเกตได้ดังต่อไปนี้:

1. ในที่นี้เรากำลังทำการเชื่อมต่อตารางโดยใช้คำสั่ง JOIN ระหว่างตาราง sample_joins และ sample_joins1 โดยมีเงื่อนไขการจับคู่ (c.Id = o.Id)
2. ผลลัพธ์จะแสดงข้อมูลที่เหมือนกันในทั้งสองตาราง โดยเลือกจากเงื่อนไขที่ระบุไว้ในคำสั่งค้นหา

ค้นหา:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

เข้าร่วมด้านนอกซ้าย

• ไฮฟ์คิวแอล LEFT OUTER JOIN จะส่งคืนแถวทั้งหมดจากตารางด้านซ้าย แม้ว่าจะไม่มีข้อมูลที่ตรงกันในตารางด้านขวา
• หากเงื่อนไข ON ตรงกับข้อมูลศูนย์รายการในตารางด้านขวา การเชื่อมต่อก็ยังคงส่งคืนข้อมูลหนึ่งรายการในผลลัพธ์ โดยมีค่า NULL ในแต่ละคอลัมน์จากตารางด้านขวา

ภาพหน้าจอข้างล่างแสดงให้เห็นว่าลูกค้าทุกคนปรากฏตัว รวมถึงลูกค้าที่ไม่ได้สั่งซื้อสินค้าด้วย

จากภาพหน้าจอข้างต้น เราสามารถสังเกตได้ดังต่อไปนี้:

1. ในที่นี้เรากำลังทำการเชื่อมตารางโดยใช้คำสั่ง “LEFT OUTER JOIN” ระหว่างตาราง sample_joins และ sample_joins1 โดยมีเงื่อนไขการจับคู่ (c.Id = o.Id) ตัวอย่างเช่น ในที่นี้เราใช้รหัสพนักงานเป็นข้อมูลอ้างอิง โดยจะตรวจสอบว่ารหัสพนักงานนั้นเหมือนกันทั้งในตารางด้านขวาและตารางด้านซ้ายหรือไม่ ซึ่งทำหน้าที่เป็นเงื่อนไขการจับคู่
2. ผลลัพธ์จะแสดงระเบียนที่เลือกตามเงื่อนไขที่ระบุในแบบสอบถาม ค่า NULL ในผลลัพธ์ข้างต้นคือคอลัมน์ที่ไม่มีค่าจากตารางด้านขวา ซึ่งก็คือ sample_joins1

ค้นหา:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

เข้าร่วมด้านนอกขวา

• คำสั่ง RIGHT OUTER JOIN ใน HiveQL จะส่งคืนแถวทั้งหมดจากตารางด้านขวา แม้ว่าจะไม่มีข้อมูลที่ตรงกันในตารางด้านซ้ายก็ตาม
• หากเงื่อนไข ON ตรงกับข้อมูลศูนย์รายการในตารางด้านซ้าย การเชื่อมต่อก็ยังคงส่งคืนข้อมูลหนึ่งรายการในผลลัพธ์ โดยมีค่า NULL ในแต่ละคอลัมน์จากตารางด้านซ้าย
• การเชื่อมตารางแบบ RIGHT จะส่งคืนข้อมูลจากตารางด้านขวาและข้อมูลที่ตรงกันจากตารางด้านซ้ายเสมอ หากตารางด้านซ้ายไม่มีค่าที่ตรงกับคอลัมน์นั้น จะส่งคืนค่า NULL ในตำแหน่งนั้น

ภาพหน้าจอด้านล่างแสดงภาพสะท้อนของผลลัพธ์ก่อนหน้า: คำสั่งซื้อทั้งหมดจะปรากฏขึ้น ไม่ว่าจะตรงกันหรือไม่ก็ตาม

จากภาพหน้าจอข้างต้น เราสามารถสังเกตได้ดังต่อไปนี้:

1. ในที่นี้เรากำลังทำการเชื่อมต่อตารางโดยใช้คำสั่ง “RIGHT OUTER JOIN” ระหว่างตาราง sample_joins และ sample_joins1 โดยมีเงื่อนไขการจับคู่ (c.Id = o.Id)
2. ผลลัพธ์จะแสดงระเบียนที่เลือกโดยการตรวจสอบเงื่อนไขที่ระบุไว้ในคำสั่งค้นหา

ค้นหา:

  SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

เข้าร่วมภายนอกเต็มรูปแบบ

คำสั่งนี้จะรวมข้อมูลจากทั้งสองตาราง sample_joins และ sample_joins1 โดยอิงตามเงื่อนไข JOIN ที่ระบุในคำสั่ง SQL

ฟังก์ชันนี้จะส่งคืนข้อมูลทั้งหมดจากทั้งสองตาราง และเติมค่า NULL ลงในคอลัมน์ที่ไม่มีค่าตรงกันในทั้งสองฝั่ง ดังที่แสดงในภาพหน้าจอด้านล่าง

จากภาพหน้าจอข้างต้น เราสามารถสังเกตได้ดังต่อไปนี้:

1. ในที่นี้เรากำลังทำการเชื่อมต่อตารางโดยใช้คำสั่ง “FULL OUTER JOIN” ระหว่างตาราง sample_joins และ sample_joins1 โดยมีเงื่อนไขการจับคู่ (c.Id = o.Id)
2. ผลลัพธ์จะแสดงข้อมูลทั้งหมดที่มีอยู่ในทั้งสองตาราง โดยเลือกข้อมูลตามเงื่อนไขที่ระบุไว้ในคำสั่ง SQL ค่า NULL ในผลลัพธ์นี้แสดงถึงค่าที่หายไปในคอลัมน์ของทั้งสองตาราง

ค้นหา:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

แบบสอบถามย่อย

การเชื่อมตาราง (Join) จะนำตารางมาวางเคียงข้างกัน ส่วนการสืบค้นย่อย (Subquery) ทำงานแตกต่างออกไป คือมันจะซ้อนการสืบค้นหนึ่งไว้ภายในอีกการสืบค้นหนึ่ง เพื่อให้การสืบค้นภายนอกสามารถทำงานกับผลลัพธ์ที่คำนวณไว้แล้วได้

คำสั่งค้นหาที่อยู่ภายในคำสั่งค้นหาหลักเรียกว่า คำสั่งค้นหาย่อย คำสั่งค้นหาหลักจะขึ้นอยู่กับค่าที่ได้จากคำสั่งค้นหาย่อยนั้น

ซับเควรีสามารถแบ่งออกได้เป็นสองประเภท:

• แบบสอบถามย่อยในส่วน FROM
• แบบสอบถามย่อยในส่วน WHERE

ควรใช้เมื่อใด:

• เพื่อให้ได้ค่าเฉพาะที่รวมจากค่าสองคอลัมน์จากตารางที่ต่างกัน
• ความสัมพันธ์ระหว่างค่าในตารางหนึ่งกับค่าในตารางอื่น
• การตรวจสอบเปรียบเทียบค่าในคอลัมน์หนึ่งกับตารางอื่นๆ

ไวยากรณ์:

Subquery in FROM clause
SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main >
Subquery in WHERE clause
SELECT <column names 1, 2…n> From<TableName_Main>WHERE col1 IN (SubQuery);

ตัวอย่าง:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

ในที่นี้ t1 และ t2 คือชื่อตาราง คำสั่งภายในคือซับควอรีที่ดำเนินการกับตาราง t1 โดยที่ a และ b คือคอลัมน์ที่เพิ่มเข้ามาในซับควอรีและกำหนดให้กับ col1 ซึ่ง col1 คือค่าคอลัมน์ที่มีอยู่ในตารางหลัก คอลัมน์ “col1” ในซับควอรีนี้เทียบเท่ากับค่าในคอลัมน์ col1 ของตารางหลัก

การฝังสคริปต์ที่กำหนดเอง

ในขณะที่ซับเควรีปรับเปลี่ยนรูปแบบข้อมูลโดยใช้ HiveQL เพียงอย่างเดียว สคริปต์ที่ฝังอยู่จะส่งแถวข้อมูลไปยังโค้ดที่เขียนขึ้นภายนอก Hive

Hive ช่วยให้สามารถเขียนสคริปต์เฉพาะสำหรับผู้ใช้เพื่อตอบสนองความต้องการของลูกค้าได้ ผู้ใช้สามารถเขียนสคริปต์ map และ reduce ของตนเองสำหรับความต้องการเหล่านั้นได้ สคริปต์เหล่านี้เรียกว่าสคริปต์แบบกำหนดเองที่ฝังอยู่ ตรรกะการเขียนโค้ดถูกกำหนดไว้ในสคริปต์แบบกำหนดเอง และเราสามารถใช้สคริปต์นั้นได้ในระหว่างกระบวนการ ETL

ควรเลือกใช้สคริปต์แบบฝังเมื่อใด:

• ในกรณีที่ข้อกำหนดเฉพาะของลูกค้าทำให้ผู้พัฒนาต้องเขียนและใช้งานสคริปต์ใน Hive
• ในกรณีที่ฟังก์ชันในตัวของ Hive ไม่สามารถใช้งานได้กับความต้องการเฉพาะด้านใดด้านหนึ่ง

ด้วยเหตุนี้ Hive จึงใช้คำสั่ง TRANSFORM เพื่อฝังสคริปต์ทั้ง map และ reducer ไว้ในตัวเดียวกัน

ในสคริปต์แบบกำหนดเองที่ฝังอยู่เหล่านี้ เราต้องคำนึงถึงประเด็นต่อไปนี้:

• คอลัมน์จะถูกแปลงเป็นสตริงและคั่นด้วยแท็บก่อนส่งไปยังสคริปต์ของผู้ใช้
• ผลลัพธ์มาตรฐานจากสคริปต์ของผู้ใช้จะถูกประมวลผลเป็นคอลัมน์สตริงที่คั่นด้วยแท็บ

ตัวอย่างสคริปต์ฝังตัว:

FROM (
	FROM pv_users
	MAP pv_users.userid, pv_users.date
	USING 'map_script'
	AS dt, uid
	CLUSTER BY dt) map_output

INSERT OVERWRITE TABLE pv_users_reduced
	REDUCE map_output.dt, map_output.uid
	USING 'reduce_script'
	AS date, count;

จากสคริปต์ข้างต้น เราสามารถสังเกตได้ดังต่อไปนี้ นี่เป็นเพียงสคริปต์ตัวอย่างเพื่อความเข้าใจเท่านั้น

• pv_users คือตารางผู้ใช้ ซึ่งมีฟิลด์ต่างๆ เช่น userid และ date ตามที่กล่าวไว้ใน map_script
• สคริปต์ตัวลดทอนถูกกำหนดโดยวันที่และจำนวนของตาราง pv_users

คำถามที่พบบ่อย