Ambari #05 การดึงข้อมูลเข้าจาก MySQL เข้าสู่ Hive ด้วย Sqoop

Apache Hive เป็นระบบ Data Warehouse ซึ่งสร้างอยู่บน Hadoop ใช้สำหรับการวิเคราะห์ข้อมูล โดยจุดเด่นคือการใช้คำสั่งภาษา SQL ในการเรียกข้อมูล ทั้งที่อยู่ในรูปแบบของ Database และไฟล์บน Hadoop ได้ เหมาะสำหรับการเก็บข้อมูลขนาดใหญ่มากๆ เช่น การเก็บข้อมูลที่ Rotate ออกจากฐานข้อมูลหลักอย่าง MySQL ก็นำมาเก็บไว้ใน MySQL เพื่อใช้วิเคราะห์ต่อไป

ในการดึงข้อมูลจาก MySQL จะแบ่งเป็น 2 ส่วน คือ

  1. Full Backup คือการนำข้อมูลทั้งหมดจาก MySQL มาเก็บไว้ใน Apache Hive
  2. Incremental Backup คือการดึงเฉพาะข้อมูลที่เพิ่มขึ้นมาเก็บไว้ใน Apache Hive

แต่ในบทความนี้ จะแนะนำการทำแบบ Incremental Backup ซึ่งในครั้งแรกจะทำ Full Backup และครั้งต่อๆไป จะทำ Incremental Backup เลย

Incremental Backup

sqoop ออกแบบมาให้สามารถ Run คำสั่งให้ทำงานทันทีก็ได้ หรือ จะสร้างเป็น Job เอาไว้ เพื่อใช้งานภายหลังก็ได้ ในที่นี้จะสร้างแบบ Job เพราะให้สะดวกในการทำ Incremental Backup
การสร้าง Sqoop Job ใช้คำสั่ง

 sqoop job --create JobName -- import ...

ในส่วนที่จะบอก sqoop ให้ทำงานแบบ incremental backup ต้องตามด้วย Options

--incremental append 
--check-column your_primary_key

ในตัวอย่างต่อไปนี้ จะสร้าง sqoop job ชื่อ maillog-incremental
ใช้คำสั่งต่อไปนี้ เพื่อดึงข้อมูลทั้งหมดจาก your.mysql.server ใน Database: mailscanner จาก Table: maillog โดยสมมุติใช้ username/password เป็น root/123456
แล้วนำมาใส่ใน Hive Database: mailscanner
เพิ่มเติมคือ ให้เก็บไว้ในรูปแบบ Paquet File
และ กำหนดให้ใช้ Field: timestamp เป็น Key เพื่อแบ่งงานเพื่อดึงข้อมูลแบบ Parallel ในที่นี้กำหนดให้แบ่งออกเป็น 10 ส่วนพร้อมๆกัน

UPDATE: sqoop job จะไม่ยอมให้ใส่ password ตรงๆ แต่จะต้องสร้างไฟล์ password เก็บไว้ใน hdfs และต้องเป็น mode 400 ให้ทำคำสั่งต่อไปนี้ก่อน

echo -n "123456" > mysqlpassword.txt
hdfs dfs -put mysqlpassword.txt mysqlpassword.txt
hdfs dfs -chmod 400 mysqlpassword.txt

แล้วจึงสร้าง sqoop job ด้วยคำสั่งต่อไปนี้

sqoop job --create maillog-incremental 
-- 
import 
--connect jdbc:mysql://your.mysql.server:3306/mailscanner 
--username root 
--password-file /user/hdfs/mysqlpassword.txt 
--table maillog 
--hive-database mailscanner 
--as-parquetfile 
--hive-import 
--split-by timestamp 
-m 10 
--incremental append 
--check-column timestamp

เมื่อสร้าง sqoop job เสร็จแล้ว สามารถเรียกดูได้ด้วยคำสั่ง

sqoop job --list

ดูรายละเอียดการทำงานได้ด้วย คำสั่ง

sqoop job --show maillog-incremental

และ สั่งให้ sqoop job ทำงานด้วยคำสั่ง

sqoop job --exec maillog-incremental

เมื่อทำการ Run ระบบก็จะแบ่งงานออกเป็น 10 ส่วนให้เครื่องใน Cluster ช่วยกัน Import เข้า Hive

ในการสั่งานครั้งแรก ระบบจะดึงข้อมูลทั้งหมดมาก่อน เช่น ในตัวอย่างนี้ ดึงมา 12 ล้าน record

แต่เมื่อสั่งอีกครั้ง ด้วยคำสั่ง

sqoop job --exec maillog-incremental

จะดึงเฉพาะส่วนที่เพิ่งเพิ่มเข้าม (233 records ตามภาพ)

ต่อไป ก็ตั้งเป็น cron ไว้เพื่อทำให้ทำงานทุกๆ 1 ชั่วโมงก็ได้