Sqoop数据库数据导入导出.docx

Sqoop数据库数据导入导出Sqoop是什么?Sqoop:?SQL-to-Hadoop连接传统关系型数据库和Hadoop的桥梁把关系型数据库的数据导入到Hadoop系统(如HDFS,Hbase和Hive中)把数据从Hadoop系统里面抽取并导出到关系型数据库中利用MapReduce加快数据处理速度批处理方式进行数据传输Sqoop优势?高效、可控地利用资源任务并行度,超时时间等?数据类型映射与转换?可自动进行,用户也可自定义支持多种数据库? MySQL OraclePostgreSQL?...Sqoop1架构?Sqoop2架构?Sqoop import??将数据从关系型数据库导入Hadoop中?Sqoop与数据库Server?通信,获取数据库表的元数据信息;?Sqoop启动一个Map-Only的MR作业,利用元数据信息并行将数据写入Hadoop。??Sqoop import使用?sqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--table cities--connnect:?指定JDBC URL??--username/password:mysql数据库的用户名??--table:要读取的数据库表bin/hadoop fs -cat cities/part-m-*1,USA,Palo Alto2,Czech Republic,Brno3,USA,Sunnyvale?Sqoop import示例?sqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \?--password sqoop \--table cities \?--target-dir /etl/input/citiessqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--table cities \?--where country = USA?sqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--table cities \?--as-sequencefile?sqoop import \--connect?jdbc:mysql:///sqoop?\--username sqoop \--password sqoop \--table cities \--num-mappers 10??Sqoop import—导入多个表?sqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--query SELECT normcities.id, \?countries.country, \? ? ? ? normcities.city \FROM normcities \?JOIN countries USING(country_id) \ WHERE $CONDITIONS \--split-by id \ --target-dir cities??Sqoop import增量导入??sqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--table visits \--incremental append \--check-column id \--last-value 1??适用于数据每次被追加到数据库中,而已有数据不变的情况;?仅导入id这一列值大于1的记录。??sqoop job \--create visits \--import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--table visits \--incremental append \--check-column id \--last-value 0运行sqoop作业:sqoop job --exec visits?每次成功运行后,sqoop将最后一条记录的id值保存到metastore中,供下次使用。?sqoop import \--connect?jdbc:mysql:///sqoop?\ --username sqoop \--password sqoop \--table