sqoop 和mysql相关操作
数据迁移工具,可以和RDBMS相互迁移数据
需要先将db driver copy to sqoop lib dir
注意：
sqoop是以mapreduce的方式来运行任务的，用hdfs来存储数据的。所以依赖NameNode和ResourceManager，只要机器上配置了这两个就可以正常运行，程序运行时会自动读取环境变量.
// 导出car数据库中的carinfo表到hdfs的user里面
sqoop import –connect jdbc:mysql://192.168.32.1:3306/car –username root –password bill –table carinfo
// 导出到/sqoop/file(hdfs上)
–target-dir /sqoop/file
使用2个map任务来跑
-m 2
使用制表符作为分隔符
–fields-terminated-by ‘\t’
只导出id和name列
–columns “id,name”
带上过滤条件
–where ‘id > 2 and id <= 9’
带上查询语句,加上了query必须要带上这个 $CONDTIONS:动态拼接条件
–query ‘select * from user where id > 100 and $CONDTIONS’
// 根据trande_detail.id来分给不同的map,在map多个的时候需要指定
// 单个map不需要加这个选项
–split-by trande_detail.id
导入数据到hbase
sqoop import –connect jdbc:mysql://192.168.32.201:3306/hive_test –table hive_user –hbase-create-table –hbase-table hbase_user –column-family info –hbase-row-key id –username root –password bill;
sqoop import –connect jdbc:mysql://192.168.32.1:3306/car –table t –hbase-create-table –hbase-table cars –column-family info –hbase-row-key id –username root –password bill;
导入数据到hive
sqoop import –connect jdbc:mysql://192.168.32.201:3306/hive_test –hive-import –hive-overwrite –hive-table hive_user –fields-terminated-by ‘\t’ –table hive_user –username root –password bill;
sqoop import –connect jdbc:mysql://192.168.32.1:3306/car –table t –hive-import –hive-overwrite –hive-table cars –username root –password bill –fields-terminated-by ‘\t’;
案例：
sqoop import –connect jdbc:mysql://192.168.32.1:3306/h1 –username root –password bill –target-dir /sqoop/table/radiotype/all/ –fields-terminated-by ‘\t’ –table radiotype
sqoop import –connect jdbc:mysql://192.168.32.1:3306/h1 –where ‘id >= 3 and id <= 5’ –target-dir /sqoop/table/radiotype/id3-5_03/ –fields-terminated-by ‘\t’ -m 2 –table radiotype –username root –password bill
// 上面 -m 2居然没用，但是改成1又有用，待解决。!
将h1数据库中的radiotype表的记录导出到/sqoop/table/radiotype/assign-columns中，只导出(id,typename,imageurl)字段
sqoop import –connect jdbc:mysql://192.168.32.1:3306/h1 –table radiotype –target-dir /sqoop/table/radiotype/assign-columns/ –columns ‘id,typename,imageurl’ –fields-terminated-by ‘^’ -m 4 –username root –password bill
sqoop import –connect jdbc:mysql://192.168.32.1:3306/h1 –target-dir /sqoop/table/radiotype/custom_sql/ –username root –password bill –fields-terminated-by ‘标’ –query ‘select * from radiotype where id > 1 and $CONDITIONS‘ -m 1
// 使用query时，要带上$CONDITIONS 若是使用多个map需要手动指定–split-by ,否则会报错
sqoop import –connect jdbc:mysql://192.168.32.1/h1 –username root –password bill –query ‘select * from radiotype where id != 3 and $CONDITIONS’ –target-dir /sqoop/table/radiotype/custom_sql_02 –fields-terminated-by ‘^’ -m 4 –split-by radiotype.id
sqoop export –connect jdbc:mysql://192.168.32.1/h1 –table radiotype_export –export-dir /sqoop/table/radiotype/custom_sql/ –username root –password bill –fields-terminated-by ‘标’