【郑州校区】大数据离线阶段Day7之模块开发----结果导出1． Apache Sqoop

Sqoop是Hadoop和关系数据库服务器之间传送数据的一种工具。它是用来从关系数据库如：MySQL，Oracle到Hadoop的HDFS，并从Hadoop的文件系统导出数据到关系数据库。由Apache软件基金会提供。

Sqoop：“SQL 到 Hadoop 和 Hadoop 到SQL”。

Sqoop工作机制是将导入或导出命令翻译成mapreduce程序来实现。

在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。

sqoop安装

安装sqoop的前提是已经具备java和hadoop的环境。

最新稳定版： 1.4.6

配置文件修改：

cd $SQOOP_HOME/conf

mv sqoop-env-template.sh sqoop-env.sh

vi sqoop-env.sh

export HADOOP_COMMON_HOME=/root/apps/hadoop/

export HADOOP_MAPRED_HOME=/root/apps/hadoop/

export HIVE_HOME=/root/apps/hive

加入mysql的jdbc驱动包

cp  /hive/lib/mysql-connector-java-5.1.28.jar   $SQOOP_HOME/lib/

验证启动

bin/sqoop list-databases --connect jdbc:mysql://localhost:3306/ --username root --password hadoop

本命令会列出所有mysql的数据库。

到这里，整个Sqoop安装工作完成。

2． Sqoop导入

“导入工具”导入单个表从RDBMS到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）。

下面的语法用于将数据导入HDFS。

$ sqoop import (generic-args) (import-args)

Sqoop测试表数据

在mysql中创建数据库userdb，然后执行参考资料中的sql脚本：

创建三张表: emp  emp_add emp_conn。  

2.1． 导入mysql表数据到HDFS

下面的命令用于从MySQL数据库服务器中的emp表导入HDFS。

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--target-dir /sqoopresult \

--table emp --m 1

其中--target-dir可以用来指定导出数据存放至HDFS的目录；

mysql jdbc url 请使用 ip 地址。

为了验证在HDFS导入的数据，请使用以下命令查看导入的数据：

hdfs dfs -cat /sqoopresult/part-m-00000

可以看出它会用逗号,分隔emp表的数据和字段。

1201,gopal,manager,50000,TP 1202,manisha,Proof reader,50000,TP 1203,khalil,php dev,30000,AC 1204,prasanth,php dev,30000,AC 1205,kranthi,admin,20000,TP

2.2． 导入mysql表数据到HIVE

将关系型数据的表结构复制到hive中

bin/sqoop create-hive-table \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--table emp_add \

--username root \

--password hadoop \

--hive-table test.emp_add_sp

其中：

--table emp_add为mysql中的数据库sqoopdb中的表。   

--hive-table emp_add_sp 为hive中新建的表名称。

从关系数据库导入文件到hive中

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--table emp_add \

--hive-table test.emp_add_sp \

--hive-import \

--m 1

2.3． 导入表数据子集

--where        可以指定从关系数据库导入数据时的查询条件。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--where "city ='sec-bad'" \

--target-dir /wherequery \

--table emp_add --m 1

复杂查询条件：

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--target-dir /wherequery12 \

--query 'select id,name,deg from emp WHERE  id>1203 and $CONDITIONS' \

--split-by id \

--fields-terminated-by '\t' \

--m 1

2.4． 增量导入

增量导入是仅导入新添加的表中的行的技术。

--check-column (col)        用来作为判断的列名，如id

--incremental (mode)        append：追加，比如对大于last-value指定的值之后的记录进行追加导入。lastmodified：最后的修改时间，追加last-value指定的日期之后的记录

--last-value (value)        指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

假设新添加的数据转换成emp表如下：

1206, satish p, grp des, 20000, GR

下面的命令用于在EMP表执行增量导入：

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--table emp --m 1 \

--incremental append \

--check-column id \

--last-value 1205

3． Sqoop导出

将数据从HDFS导出到RDBMS数据库导出前，目标表必须存在于目标数据库中。

默认操作是从将文件中的数据使用INSERT语句插入到表中，更新模式下，是生成UPDATE语句更新表数据。

以下是export命令语法：

$ sqoop export (generic-args) (export-args)

3.1． 导出HDFS数据到mysql

数据是在HDFS 中“emp/”目录的emp_data文件中：

1201,gopal,manager,50000,TP

1202,manisha,preader,50000,TP

1203,kalil,php dev,30000,AC

1204,prasanth,php dev,30000,AC

1205,kranthi,admin,20000,TP

1206,satishp,grpdes,20000,GR

首先需要手动创建mysql中的目标表：

mysql> USE sqoopdb;

mysql> CREATE TABLE employee (

   id INT NOT NULL PRIMARY KEY,

   name VARCHAR(20),

   deg VARCHAR(20),

   salary INT,

   dept VARCHAR(10));

然后执行导出命令：

bin/sqoop export \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--table employee \

--export-dir /emp/emp_data

还可以用下面命令指定输入文件的分隔符

--input-fields-terminated-by '\t'

如果运行报错如下:

则需要把localhost更改为ip或者域名。

示例如下，将点击流模型表导出到mysql  

sqoop export \ --connect jdbc:mysql://hdp-node-01:3306/webdb --username root --password root  \ --table click_stream_visit  \ --export-dir /user/hive/warehouse/dw_click.db/click_stream_visit/datestr=2013-09-18 \ --input-fields-terminated-by '\001'

<更多表的导出，可参照修改>

传智播客·黑马程序员郑州校区地址

河南省郑州市 高新区长椿路11号大学科技园（西区）东门8号楼三层

联系电话 0371-56061160/61/62

来校路线  地铁一号线梧桐街站A口出

【郑州校区】大数据离线阶段Day7之模块开发----结果导出1． Apache Sqoop

Sqoop是Hadoop和关系数据库服务器之间传送数据的一种工具。它是用来从关系数据库如：MySQL，Oracle到Hadoop的HDFS，并从Hadoop的文件系统导出数据到关系数据库。由Apache软件基金会提供。

Sqoop：“SQL 到 Hadoop 和 Hadoop 到SQL”。

Sqoop工作机制是将导入或导出命令翻译成mapreduce程序来实现。

在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。

sqoop安装

安装sqoop的前提是已经具备java和hadoop的环境。

最新稳定版： 1.4.6

配置文件修改：

cd $SQOOP_HOME/conf

mv sqoop-env-template.sh sqoop-env.sh

vi sqoop-env.sh

export HADOOP_COMMON_HOME=/root/apps/hadoop/

export HADOOP_MAPRED_HOME=/root/apps/hadoop/

export HIVE_HOME=/root/apps/hive

加入mysql的jdbc驱动包

cp  /hive/lib/mysql-connector-java-5.1.28.jar   $SQOOP_HOME/lib/

验证启动

bin/sqoop list-databases --connect jdbc:mysql://localhost:3306/ --username root --password hadoop

本命令会列出所有mysql的数据库。

到这里，整个Sqoop安装工作完成。

2． Sqoop导入

“导入工具”导入单个表从RDBMS到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）。

下面的语法用于将数据导入HDFS。

$ sqoop import (generic-args) (import-args)

Sqoop测试表数据

在mysql中创建数据库userdb，然后执行参考资料中的sql脚本：

创建三张表: emp  emp_add emp_conn。  

2.1． 导入mysql表数据到HDFS

下面的命令用于从MySQL数据库服务器中的emp表导入HDFS。

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--target-dir /sqoopresult \

--table emp --m 1

其中--target-dir可以用来指定导出数据存放至HDFS的目录；

mysql jdbc url 请使用 ip 地址。

为了验证在HDFS导入的数据，请使用以下命令查看导入的数据：

hdfs dfs -cat /sqoopresult/part-m-00000

可以看出它会用逗号,分隔emp表的数据和字段。

1201,gopal,manager,50000,TP 1202,manisha,Proof reader,50000,TP 1203,khalil,php dev,30000,AC 1204,prasanth,php dev,30000,AC 1205,kranthi,admin,20000,TP

2.2． 导入mysql表数据到HIVE

将关系型数据的表结构复制到hive中

bin/sqoop create-hive-table \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--table emp_add \

--username root \

--password hadoop \

--hive-table test.emp_add_sp

其中：

--table emp_add为mysql中的数据库sqoopdb中的表。   

--hive-table emp_add_sp 为hive中新建的表名称。

从关系数据库导入文件到hive中

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--table emp_add \

--hive-table test.emp_add_sp \

--hive-import \

--m 1

2.3． 导入表数据子集

--where        可以指定从关系数据库导入数据时的查询条件。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--where "city ='sec-bad'" \

--target-dir /wherequery \

--table emp_add --m 1

复杂查询条件：

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--target-dir /wherequery12 \

--query 'select id,name,deg from emp WHERE  id>1203 and $CONDITIONS' \

--split-by id \

--fields-terminated-by '\t' \

--m 1

2.4． 增量导入

增量导入是仅导入新添加的表中的行的技术。

--check-column (col)        用来作为判断的列名，如id

--incremental (mode)        append：追加，比如对大于last-value指定的值之后的记录进行追加导入。lastmodified：最后的修改时间，追加last-value指定的日期之后的记录

--last-value (value)        指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

假设新添加的数据转换成emp表如下：

1206, satish p, grp des, 20000, GR

下面的命令用于在EMP表执行增量导入：

bin/sqoop import \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--table emp --m 1 \

--incremental append \

--check-column id \

--last-value 1205

3． Sqoop导出

将数据从HDFS导出到RDBMS数据库导出前，目标表必须存在于目标数据库中。

默认操作是从将文件中的数据使用INSERT语句插入到表中，更新模式下，是生成UPDATE语句更新表数据。

以下是export命令语法：

$ sqoop export (generic-args) (export-args)

3.1． 导出HDFS数据到mysql

数据是在HDFS 中“emp/”目录的emp_data文件中：

1201,gopal,manager,50000,TP

1202,manisha,preader,50000,TP

1203,kalil,php dev,30000,AC

1204,prasanth,php dev,30000,AC

1205,kranthi,admin,20000,TP

1206,satishp,grpdes,20000,GR

首先需要手动创建mysql中的目标表：

mysql> USE sqoopdb;

mysql> CREATE TABLE employee (

   id INT NOT NULL PRIMARY KEY,

   name VARCHAR(20),

   deg VARCHAR(20),

   salary INT,

   dept VARCHAR(10));

然后执行导出命令：

bin/sqoop export \

--connect jdbc:mysql://node-21:3306/sqoopdb \

--username root \

--password hadoop \

--table employee \

--export-dir /emp/emp_data

还可以用下面命令指定输入文件的分隔符

--input-fields-terminated-by '\t'

如果运行报错如下:

则需要把localhost更改为ip或者域名。

示例如下，将点击流模型表导出到mysql  

sqoop export \ --connect jdbc:mysql://hdp-node-01:3306/webdb --username root --password root  \ --table click_stream_visit  \ --export-dir /user/hive/warehouse/dw_click.db/click_stream_visit/datestr=2013-09-18 \ --input-fields-terminated-by '\001'

<更多表的导出，可参照修改>

传智播客·黑马程序员郑州校区地址

河南省郑州市 高新区长椿路11号大学科技园（西区）东门8号楼三层

联系电话 0371-56061160/61/62

来校路线  地铁一号线梧桐街站A口出