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

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

แบบสอบถามแบบรวมสามารถทำได้บนสองตารางที่มีอยู่ใน Hive เพื่อความเข้าใจในการเข้าร่วม Concepts ชัดเจนว่าเรากำลังสร้างตารางสองตารางตรงนี้

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

ขั้นตอน 1) การสร้างตาราง “sample_joins” ด้วยชื่อคอลัมน์ ID, ชื่อ, อายุ, ที่อยู่ และเงินเดือนของพนักงาน

ขั้นตอน 2) กำลังโหลดและแสดงข้อมูล

จากภาพหน้าจอด้านบน

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

ขั้นตอน 3) การสร้างตาราง Sample_joins1 และการโหลด การแสดงข้อมูล

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

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

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

บางประเด็นที่ควรสังเกตในการเข้าร่วม:

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

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

ข้อต่อมี 4 ประเภท ได้แก่

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

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

บันทึกที่ใช้ร่วมกันกับทั้งสองตารางจะถูกดึงข้อมูลโดย Inner Join นี้

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

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

ค้นหา:

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 Clause ตรงกับศูนย์ระเบียนในตารางด้านขวา การรวมยังคงส่งคืนระเบียนในผลลัพธ์โดยมี NULL ในแต่ละคอลัมน์จากตารางด้านขวา

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

ที่นี่เรากำลังทำการสืบค้นแบบรวมโดยใช้คีย์เวิร์ด “LEFT OUTER JOIN” ระหว่างตาราง Sample_joins และ Sample_joins1 โดยมีเงื่อนไขที่ตรงกันเป็น (c.Id= o.Id)ตัวอย่างเช่น ที่นี่เราใช้รหัสพนักงานเป็นข้อมูลอ้างอิง โดยจะตรวจสอบว่ารหัสนั้นเหมือนกันทั้งทางขวาและทางซ้ายของตารางหรือไม่ มันทำหน้าที่เป็นเงื่อนไขที่ตรงกัน
ผลลัพธ์ที่แสดงบันทึกทั่วไปที่มีอยู่ในทั้งสองตารางโดยการตรวจสอบเงื่อนไขที่กล่าวถึงในแบบสอบถาม ค่า 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 ส่งคืนแถวทั้งหมดจากตารางด้านขวาแม้ว่าจะไม่มีรายการที่ตรงกันในตารางด้านซ้าย
ถ้า ON Clause ตรงกับศูนย์ระเบียนในตารางด้านซ้าย การรวมยังคงส่งคืนระเบียนในผลลัพธ์โดยมี NULL ในแต่ละคอลัมน์จากตารางด้านซ้าย
RIGHT รวมจะส่งกลับระเบียนจากตารางด้านขวาและระเบียนที่ตรงกันจากตารางด้านซ้ายเสมอ หากตารางด้านซ้ายไม่มีค่าที่สอดคล้องกับคอลัมน์ ตารางนั้นจะส่งกลับค่า NULL ในตำแหน่งนั้น

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

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

สอบถาม:

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

การรวมภายนอกแบบเต็ม:

โดยจะรวมบันทึกของทั้งตาราง example_joins และ example_joins1 ตามเงื่อนไข JOIN ที่กำหนดในแบบสอบถาม

ส่งคืนระเบียนทั้งหมดจากทั้งสองตารางและเติมค่า NULL สำหรับคอลัมน์ที่ไม่มีค่าที่ตรงกันทั้งสองด้าน

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

ที่นี่ เรากำลังทำการสืบค้นแบบรวมโดยใช้คีย์เวิร์ด “FULL OUTER JOIN” ระหว่างตาราง example_joins และ example_joins1 โดยมีเงื่อนไขที่ตรงกันเป็น (c.Id= o.Id)
ผลลัพธ์ที่แสดงบันทึกทั้งหมดที่มีอยู่ในทั้งสองตารางโดยการตรวจสอบเงื่อนไขที่ระบุในแบบสอบถาม ค่า 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)

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

แบบสอบถามที่มีอยู่ภายในแบบสอบถามเรียกว่าแบบสอบถามย่อย แบบสอบถามหลักจะขึ้นอยู่กับค่าที่ส่งคืนโดยแบบสอบถามย่อย

แบบสอบถามย่อยสามารถแบ่งได้เป็นสองประเภท

แบบสอบถามย่อยในส่วนคำสั่ง 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 เป็นชื่อตาราง อันที่มีสีคือ Subquery ดำเนินการบนตาราง t1 โดยที่ a และ b คือคอลัมน์ที่ถูกเพิ่มในแบบสอบถามย่อยและกำหนดให้กับ col1 Col1 คือค่าคอลัมน์ที่มีอยู่ในตารางหลัก คอลัมน์ “col1” นี้อยู่ในแบบสอบถามย่อยเทียบเท่ากับแบบสอบถามตารางหลักในคอลัมน์ col1

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

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

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

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

สำหรับสิ่งนี้ใน Hive นั้นจะใช้ส่วนคำสั่ง TRANSFORM เพื่อฝังทั้งสคริปต์แผนที่และตัวลดขนาด

ในสคริปต์แบบกำหนดเองที่ฝังไว้นี้ เราจะต้องปฏิบัติตามจุดต่อไปนี้

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

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

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 คือตารางผู้ใช้ซึ่งมีฟิลด์เช่น ID ผู้ใช้และวันที่ตามที่กล่าวไว้ใน map_script
สคริปต์ตัวลดที่กำหนดตามวันที่และจำนวนตาราง pv_users

ที่คุณอาจชอบ:

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

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

บทความที่เกี่ยวข้อง

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

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