# Exp 2 - Sqoop : MySQL <-> HDFS / Hive / HBase
# mysql root has no password here, so no -P / --password anywhere
# jdbc url: jdbc:mysql://localhost/company      (needs mysql connector jar in sqoop lib - already there on cloudera)

# ---- 0. mysql table ----
mysql -u root < setup.sql                  # creates db company, table employee (6 rows), emp_copy (empty)
mysql -u root -e "SELECT * FROM company.employee"

# ---- 1. check connection ----
sqoop list-databases --connect jdbc:mysql://localhost --username root
sqoop list-tables --connect jdbc:mysql://localhost/company --username root
sqoop eval --connect jdbc:mysql://localhost/company --username root --query "SELECT COUNT(*) FROM employee"

# ---- 2. import mysql -> hdfs ----
hadoop fs -rm -r -f /home/training/emp        # target dir must NOT exist (or add --delete-target-dir)
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --target-dir /home/training/emp -m 1

hadoop fs -ls /home/training/emp              # _SUCCESS + part-m-00000
hadoop fs -cat /home/training/emp/part-m-00000

# same with 2 mappers -> part-m-00000, part-m-00001 (split on primary key id)
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --target-dir /home/training/emp2 --delete-target-dir -m 2
hadoop fs -ls /home/training/emp2

# ---- 3. only some columns / rows ----
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --columns "id,name,salary" --where "salary > 50000" \
  --target-dir /home/training/emp_high --delete-target-dir -m 1
hadoop fs -cat /home/training/emp_high/part-m-*

# free form query: $CONDITIONS is compulsory, keep the query in single quotes
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --query 'SELECT id, name FROM employee WHERE dept = "IT" AND $CONDITIONS' \
  --split-by id --target-dir /home/training/emp_it --delete-target-dir -m 1
hadoop fs -cat /home/training/emp_it/part-m-*

# other delimiter
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --fields-terminated-by '\t' \
  --target-dir /home/training/emp_tab --delete-target-dir -m 1

# ---- 4. incremental import (only new rows) ----
mysql -u root -e "INSERT INTO company.employee VALUES (7,'Neel','IT',58000),(8,'Tara','Sales',41000)"
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --target-dir /home/training/emp \
  --incremental append --check-column id --last-value 6 -m 1
hadoop fs -ls /home/training/emp              # new part file with only rows 7, 8

# ---- 5. import straight into hive ----
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --hive-import --create-hive-table --hive-table emp_hive -m 1
hive -e "SELECT * FROM emp_hive"
# (run again? drop --create-hive-table, or: hive -e "DROP TABLE emp_hive")

# ---- 6. import into hbase ----
sqoop import --connect jdbc:mysql://localhost/company --username root \
  --table employee --hbase-table emp_hb --column-family info \
  --hbase-row-key id --hbase-create-table -m 1
echo "scan 'emp_hb'" | hbase shell

# ---- 7. all tables at once ----
sqoop import-all-tables --connect jdbc:mysql://localhost/company --username root \
  --warehouse-dir /home/training/company_all -m 1
hadoop fs -ls /home/training/company_all       # one folder per table

# ---- 8. export hdfs -> mysql (table must already exist) ----
sqoop export --connect jdbc:mysql://localhost/company --username root \
  --table emp_copy --export-dir /home/training/emp \
  --input-fields-terminated-by ',' -m 1
mysql -u root -e "SELECT * FROM company.emp_copy"

# export again without duplicate key error -> update existing rows, insert new
sqoop export --connect jdbc:mysql://localhost/company --username root \
  --table emp_copy --export-dir /home/training/emp \
  --input-fields-terminated-by ',' --update-key id --update-mode allowinsert -m 1

# ---- 9. saved job ----
sqoop job --create emp_job -- import --connect jdbc:mysql://localhost/company --username root \
  --table employee --target-dir /home/training/emp_job -m 1
sqoop job --list
sqoop job --exec emp_job

# ---- viva ----
# sqoop = SQL to hadoop. import: rdbms -> hdfs/hive/hbase, export: hdfs -> rdbms
# runs a map-only mapreduce job (no reducer, rows are only copied, no aggregation)
# -m = no. of mappers (default 4). split on --split-by column (default primary key) using min/max of it
# no primary key -> give --split-by col or use -m 1
# $CONDITIONS = placeholder sqoop replaces with each mapper's range (id >= 1 AND id < 4)
# import target dir must not exist ; export target table must exist
# --direct uses mysqldump instead of jdbc (faster)
# common errors: "output directory already exists" -> --delete-target-dir ; "access denied" -> check username
