Sqoop操作练习

导入：
传统关系型数据库---->大数据平台的
import
mysql------>hadoop
--connect               指定关系型数据库链接url           mysql:jdbc://hadoop02:3306/
--username          指定数据库的用户名
--password          指定数据库的密码
--table             指定要导出数据的mysql数据库表   关系型数据库中的表名
                    需要导出数据的mysql的表名
-m                  指定MapTask的个数
--target-dir        指定导入数据在HDFS上的存储目录
--fields-terminated-by  指定每条记录中字段之间的分隔符
--where             指定查询SQL的where条件
--query             指定查询SQL
--columns               指定查询列  指定的是mysql中需要导出的列
 
 
列出MySQL数据有哪些数据库：
sqoop list-databases \
--connect jdbc:mysql://hadoop02:3306/ \
--username root \
--password 123456
 
list-databases  列出mysql数据库中的所有库信息
 
列出MySQL中的某个数据库有哪些数据表：
sqoop list-tables \
--connect jdbc:mysql://hadoop02:3306/mysql \
--username root \
--password 123456
 
创建一张跟mysql中的help_keyword表一样的hive表hk：
sqoop create-hive-table \
--connect jdbc:mysql://hadoop02:3306/mysql \
--username root \
--password root \
--table help_keyword \
--hive-table hk
 
 
常用的数据导入：
mysql-->hdfs、hive、hbase
import
1）mysql---hdfs
导入MySQL表中数据到HDFS中：
 
// 普通导入：导入mysql库中的help_keyword的数据到HDFS上的默认路径：/user/hadoop/help_keyword
sqoop import \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root \
--password 123456  \
--table help_topic  \
-m 1 
mysql数据库的表   help_topic  ----  hdfs上
 
默认目录：/user/hadoop/help_topic（表名）
    _SUCCESS   成功标志文件
    part-m-00000 结果文件  maptask输出的
默认分割符  ，    
 
// 导入： 指定分隔符和导入路径
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456 \
--table help_keyword  \
--target-dir /user/data01/mydata/help_keyword  \
--fields-terminated-by '\t'  \
-m 1
 
 
// 导入数据：带where条件
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456   \
--where "name='AT'" \
--table help_keyword   \
--target-dir /user/sqoop/data01/myoutport1  \
-m 1
 
相当于：select * from help_keyword where name='AT';
 
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456   \
--where "help_keyword_id>200" \
--table help_keyword   \
--target-dir /user/sqoop/data01/myoutport2  \
-m 1
 
select * from help_keyword where help_keyword_id>200;
 
 
// 查询指定列
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456   \
--columns "name" \
--where "help_keyword_id>200" \
--table help_keyword  \
--target-dir /user/sqoop/data01/myoutport3  \
-m 1
selct name from help_keyword where name = "string"
 
 
// 导入：指定自定义查询SQL
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/  \
--username root  \
--password 123456   \
--target-dir /user/sqoop/data01/myimport4  \
--query 'select * from mysql.help_keyword where help_keyword_id > 200 and $CONDITIONS' \
--split-by  help_keyword_id \
--fields-terminated-by '\t'  \
-m 4
  
注意：
--query和--where   --columns  不能一起使用  一起使用会报错的
--split-by    指定多个maptask数据划分的  建
-m   maptask的个数
当maptask超过一个的时候，指定maptask的切分的列
每一个maptask在进行数据分配的时候 根据指定的列分配
 
每一个maptask的任务是顺序  平均分配的
201-452   252条数据  4个maptask
maptask0   201-263
maptask1   264-326
maptask2   327-389
maptask3   390-452    
报错：
 Encountered IOException running import job: java.io.IOException: Query [select * from mysql.help_keyword where help_keyword_id > 200] must contain '$CONDITIONS' in WHERE clause.
 sql语句中必须包含$CONDITIONS
 $CONDITIONS sqoop命令的语法要求  没有任何实际意义的
 where过滤条件中  最后添加  and $CONDITIONS
 
 
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456   \
--target-dir /user/hadoop/myimport33_2  \
--query "select * from help_keyword where $CONDITIONS"  \
--split-by  help_keyword_id \
--fields-terminated-by '\t'  \
-m 2
 
在以上需要按照自定义SQL语句导出数据到HDFS的情况下：
1、引号问题，要么外层使用单引号，内层使用双引号，$CONDITIONS的$符号不用转义， 要么外层使用双引号，那么内层使用单引号，然后$CONDITIONS的$符号需要转义
2、自定义的SQL语句中必须带有WHERE \$CONDITIONS
 
 
 
2）mysql---->hive中
注意：在hive中创建出来表  但是hive库需要自己手动创建
导入MySQL数据库中的表数据到Hive中：
 
// 普通导入：数据存储在默认的default hive库中，表名就是对应的mysql的表名：
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456   \
--table help_topic   \
--hive-import \
-m 1
 
--table   指定关系型数据库中的表
--hive-import  将数据导入到hive中
 
默认的会将数据导入到hive的default数据库下  表名同mysql中的表名
 
第一步：导入mysql.help_keyword的数据到hdfs的默认路径  /user/用户/help_keyword
第二步：自动仿造mysql.help_keyword去创建一张hive表, 
    创建在默认的default库中  表名同mysql中的表名
第三步：把临时目录中的数据导入到hive表的数据目录下
    load data inpath 
     
 
 
// 指定行分隔符和列分隔符，指定hive-import，指定覆盖导入，指定自动创建hive表，指定表名，指定删除中间结果数据目录
sqoop import  \
--connect jdbc:mysql://hadoop02:3306/mysql  \
--username root  \
--password 123456  \
--table help_keyword  \
--fields-terminated-by "\t"  \
--lines-terminated-by "\n"  \
--hive-import  \
--hive-overwrite  \
--create-hive-table  \
--delete-target-dir \
--hive-database  weibo \
--hive-table new_help_keyword
 
hive-import 当前这个导入命令。 sqoop会自动给创建hive的表。 但是不会自动创建不存在的库
--hive-overwrite   覆盖导入hive表
--create-hive-table   创建hive表
--hive-database  指定hive表的数据库  数据库一定是hive已经存在的  不会自动创建
--hive-table  指定hive的表名  不需要创建  自动创建的
 
注意：hive的表会自动创建  库不会自动创建   手动创建
 
 
 
另外一种写法：
sqoop import  \
--connect jdbc:mysql://hadoop02:3306/  \
--username root  \
--password 123456  \
--table mysql.help_keyword  \
--fields-terminated-by "\t"  \
--lines-terminated-by "\n"  \
--hive-import  \
--hive-overwrite  \
--create-hive-table  \ 
--hive-table  mydb_test.new_help_keyword01  \
--delete-target-dir
 
--hive-overwrite  覆盖导入  
 
 
 
// 增量导入
1-200   201-400
将mysql中新增的数据导入到大数据平台
在原来的数据基础上  进行追加数据导入
条件：
有一个标识增量的建  通常主键
 
sqoop import   \
--connect jdbc:mysql://hadoop02:3306/mysql   \
--username root  \
--password 123456   \
--table help_keyword  \
--target-dir /user/hadoop/myimport_add  \
--incremental  append  \
--check-column  help_keyword_id \
--last-value 200  \
-m 1
 
参数：
sqoop数据导入hive/hdfs的时候  有两种导入方式：
    1）全量数据导入  mysql的数据每次全部导入
    2）增量数据导入  每一次只导入新增的数据
 
Incremental import arguments:
   --check-column <column>        Source column to check for incremental
                                  change
    指定用于标识增量的键   用于检验
    help_keyword_id
   --incremental <import-type>    Define an incremental import of type
                                  'append' or 'lastmodified'
    增量导入的方式   id
    append   追加   *****
    lastmodified  最后一次修改的时间或建
   --last-value <value>           Last imported value in the incremental
                                 check column
    指定上一次的最后最后一个增量的建的值   500
    这次导入则是从这个值的下一个值开始导入
     
 
 
 
#########################################################
3)导入mysql数据到hbase：
 
sqoop import \
--connect jdbc:mysql://hadoop02:3306/mysql \
--username root \
--password root \
--table help_keyword \
--hbase-table new_help_keyword \
--column-family person \
--hbase-row-key help_keyword_id
注意：hbase中的表需要手动创建的  不会自动创建的
--hbase-table  指定hbase的表名的
--column-family   指定hbase的列族的
--hbase-row-key   指定导入到hbase的rowkey的建
 
 
 
导出：
    hadoop（hdfs  hive   hbase）------>mysql中
 
 
    很不幸：  可以从HDFS导出到MySQL，
            也可以从Hive导出数据到MySQL,   
            但是没有一种直接的方式可以让HBase的数据导出到MySQL
            面试问题：如何将habse的数据导出mysql中
 
    思考：
 
 
    HDFS  和 Hive ，  HBase 不可以？
 
    HBase的SQL客户端： hive  +  phoenix
 
    hbase表中的 key  的值不确定  和  很多。
 
    想办法，还是可以实现把hbase中的数据导出mysql
 
 
导出的需求很少： 本身数据量是很大的。 MySQL
 
所有的计算任务的处理结果，有时候是需要被导出到MySQL中的。
 
 
注意：导出的RDBMS的表必须自己预先创建，不会自动创建
 
create database sqoopdb default character set utf8 COLLATE utf8_general_ci; 
use sqoopdb;
CREATE TABLE sqoopfur ( 
   id INT, 
   name VARCHAR(60)
);
 
// 导出HDFS数据到MySQL：
sqoop export \
--connect jdbc:mysql://hadoop02:3306/sqoopdb  \
--username root \
--password 123456 \
--table sqoopfur \
--export-dir /user/hadoop/myimport_add \
--fields-terminated-by ','
 
 
--export-dir   指定hdfs需要导出的数据的文件夹
fields-terminated-by '\t'   指定的是hdfs上文件的分隔符
 
注意：不会创建mysql上的数据库 和表的需要手动创建
 
 
// 导出hive数据到MySQL：等同于hdfs---》mysql的
sqoop export \
--connect jdbc:mysql://hadoop02:3306/sqoopdb \
--username root \
--password 123456 \
--table uv_info \
--export-dir /user/hive/warehouse/uv/dt=2011-08-03 \
--input-fields-terminated-by '\t'
 
--export-dir   指定的是hive的表在hdfs的存储目录
hive---mysql本质  hdfs---mysql中
 
 
 
一些其他操作：
 
列出mysql数据库中的所有数据库
sqoop list-databases \
--connect jdbc:mysql://hadoop02:3306/ \
-username root \
-password root
 
连接mysql并列出数据库中的表
sqoop list-tables \
--connect jdbc:mysql://hadoop02:3306/jdbc_test1704 \
-username root \
-password root
 
将关系型数据的表结构复制到hive中
sqoop create-hive-table \
--connect jdbc:mysql://hadoop02:3306/jdbc_test1704 \
--table student \
--username root \
--password root \
--hive-table mydb_test.student  \
--fields-terminated-by "\t"  \
--lines-terminated-by "\n"