Sqoop 数据从HDFS导入到mysql

来源：互联网发布：钱江大数据交易中心编辑：程序博客网时间：2024/04/28 12:43

1 下载安装sqoop，在hadoop集群的任一一个节点上执行

tar fvxz sqoop-1.3.0-cdh3u5.tar.gz

mv sqoop-1.3.0-cdh3u5/ sqoop

拷贝相关的jar包

[kyo@hadoop1 ~]$ cp hadoop/hadoop-core-0.20.2-cdh3u5.jar /home/kyo/sqoop/lib/

[kyo@hadoop1 ~]$ cp mysql-connector-java-5.1.22-bin.jar/home/kyo/sqoop/lib/

以便sqoop与hadoop，mysql通信

设置相关环境变量，等下执行sqoop命令的时候如果报错，按照提示再设置也来得急

export HADOOP_HOME=/home/kyo/hadoop/

2 在mysql服务器上给sqoop程序提供一个可以用来连接的账号sqoop，密码sqoop

mysql> grant all privileges on *.* to'sqoop'@'%' identified by 'sqoop';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在test库里面建立测试表test，并少量填充数据

mysql> use test

Database changed

mysql> create table test (a int);

Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values (1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into test values (2);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (3);

Query OK, 1 row affected (0.00 sec)

3 测试sqoop连接mysql

本例中mysql与haoop集群在一起（ip都是192.168.0.110，按照您实际情况填写，用户名密码都是刚才设定的）

hadoop1$/home/kyo/sqoop/bin/sqooplist-databases --connect jdbc:mysql://192.168.0.110:3306/ --username sqoop--password sqoop

12/12/16 15:13:45 WARN tool.BaseSqoopTool:Setting your password on the command-line is insecure. Consider using -Pinstead.

12/12/16 15:13:45 INFOmanager.MySQLManager: Preparing to use a MySQL streaming resultset.

information_schema

mysql

test

4 从hdfs导数据到mysql

先在mysql里面删除刚才填充的数据mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> truncate test;Query OK, 0 rows affected (0.02 sec)
执行下面的命令把数据从hdfs导入到mysql里面去（hdfs://192.168.0.110:9000/user/kyo/test/ 在hdfs上存放mysql数据的目录）

hadoop1$/home/kyo/sqoop/bin/sqoopexport --connect jdbc:mysql://192.168.0.110:3306/test--username sqoop --password sqoop --table test --export-dirhdfs://192.168.0.110:9000/user/kyo/test/

5.分隔符和按列导入命令

sqoop export --connect jdbc:mysql://192.168.0.110:3306/test --update-key "id,name" --update-mode allowinsert --username sqoop --password sqoop --table test123 --fields-terminated-by '\t' --columns "id,name,age" --export-dir hdfs：://192.168.0.110:9000/user/kyo/test/

转至 http://f.dataguru.cn/thread-40151-1-1.html

0 0