Sqoop常用操作

该脚本由香打小伙伴整理。
首先保证HDFS和HiveServer2正常运行，集群运行在debugo01,debugo02,debugo03三台主机上。

1. 准备mysql数据

在debugo03的MySQL中新建一个测试数据库，并建测试表employee_salary。

mysql -uroot -p
mysql> create database test_sqoop;
Query OK, 1 row affected (0.00 sec)
mysql> use test_sqoop;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `employee_salary`;
CREATE TABLE `employee_salary` (
  `name` text,
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `salary` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `employee_salary` VALUES ('zhangsan', '1', '5000');
INSERT INTO `employee_salary` VALUES ('lisi', '2', '5500');
commit;

CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON test_sqoop.* TO 'test'@'%';

mysql -uroot -p

mysql> create database test_sqoop;

Query OK, 1 row affected (0.00 sec)

mysql> use test_sqoop;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `employee_salary`;

CREATE TABLE `employee_salary` (

`name` text,

`id` int(8) NOT NULL AUTO_INCREMENT,

`salary` int(8) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `employee_salary` VALUES ('zhangsan', '1', '5000');

INSERT INTO `employee_salary` VALUES ('lisi', '2', '5500');

commit;

CREATE USER 'test'@'%' IDENTIFIED BY 'test';

GRANT ALL PRIVILEGES ON test_sqoop.* TO 'test'@'%';

2. 安装sqoop

yum install sqoop
cp /usr/share/java/mysql-connector-java.jar /usr/lib/sqoop/lib

3. 常用命令

(1) sqoop help

[root@hadoop01 ~]# sqoop help
Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

[root@hadoop01 ~]# sqoop help

Available commands:

codegen Generate code to interact with database records

create-hive-table Import a table definition into Hive

eval Evaluate a SQL statement and display the results

export Export an HDFS directory to a database table

help List available commands

import Import a table from a database to HDFS

import-all-tables Import tables from a database to HDFS

import-mainframe Import datasets from a mainframe server to HDFS

job Work with saved jobs

list-databases List available databases on a server

list-tables List available tables in a database

merge Merge results of incremental imports

metastore Run a standalone Sqoop metastore

version Display version information

(2) 列出所有数据库（可用于测试连接）
一般用于测试连接，得到的结果只是该mysql用户拥有权限的数据库。

sqoop list-databases --connect jdbc:mysql://debugo03 --username test --password test
information_schema
test_sqoop

sqoop list-databases --connect jdbc:mysql://debugo03 --username test --password test

information_schema

test_sqoop

(3) 列出所有表

sqoop list-tables --connect jdbc:mysql://debugo03/test_sqoop --username test --password test
employee_salary

1 2	sqoop list-tables --connect jdbc:mysql://debugo03/test_sqoop --username test --password test employee_salary

(4) 导出mysql表到hdfs
其中的-D mapred.job.queue.name=lesson是用来指定yarn的执行队列。
–m 1用来指定map任务个数为1。

sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --m 1 --target-dir /user/sqoop

1	sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --m 1 --target-dir /user/sqoop

在hdfs上创建测试目录

su - hdfs
hdfs dfs -mkdir /user/sqoop
hdfs dfs -chown sqoop:hadoop /user/sqoop

su - hdfs

hdfs dfs -mkdir /user/sqoop

hdfs dfs -chown sqoop:hadoop /user/sqoop

执行导出

su - sqoop -s /bin/sh
sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --m 1 --target-dir /user/sqoop/employee_salary

1 2	su - sqoop -s /bin/sh sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --m 1 --target-dir /user/sqoop/employee_salary

如果出现下面的错误，请更新/usr/lib/sqoop/mysql-java-connector.jar文件。ISSUE: https://issues.apache.org/jira/browse/SQOOP-1400

ERROR manager.SqlManager: Error reading from database: java
.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@2c176ab7 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@2c1
76ab7 is still active.

ERROR manager.SqlManager: Error reading from database: java

.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@2c176ab7 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@2c1

76ab7 is still active.

查看hdfs数据，与mysql数据库中employee_salary表一致：

ERROR manager.SqlManager: Error reading from database: java
.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@2c176ab7 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@2c1
76ab7 is still active.

ERROR manager.SqlManager: Error reading from database: java

76ab7 is still active.

(5) 导出mysql表全部数据到hive
在hive创建对应的数据库和表

0: jdbc:hive2://debugo02:10000> create database test_sqoop;
No rows affected (0.147 seconds)
0: jdbc:hive2://debugo02:10000> show databases;            
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| sales          |
| test_sqoop     |
+----------------+--+
3 rows selected (0.046 seconds)

0: jdbc:hive2://debugo02:10000> create database test_sqoop;

No rows affected (0.147 seconds)

0: jdbc:hive2://debugo02:10000> show databases;

+----------------+--+

| database_name |

+----------------+--+

| default |

| sales |

| test_sqoop |

+----------------+--+

3 rows selected (0.046 seconds)

使用sqoop创建表

sqoop create-hive-table -D mapred.job.queue.name=work --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --hive-table test_sqoop.employee_salary
OK
Time taken: 1.515 seconds

sqoop create-hive-table -D mapred.job.queue.name=work --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --hive-table test_sqoop.employee_salary

Time taken: 1.515 seconds

使用sqoop将数据从mysql导入hive
需要确认集群中的任何一个节点都可以登录mysql。

sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --hive-import  --hive-table test_sqoop.employee_salary
OK
Time taken: 0.698 seconds

sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --hive-import --hive-table test_sqoop.employee_salary

Time taken: 0.698 seconds

检查结果，成功！

(6) 将数据从hive导入mysql
先删除mysql employee_salary表中的数据

mysql> use test_sqoop;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_test_sqoop |
+----------------------+
| employee_salary      |
+----------------------+
1 row in set (0.00 sec)
mysql> truncate employee_salary;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from employee_salary;
Empty set (0.00 sec)

mysql> use test_sqoop;

Database changed

mysql> show tables;

+----------------------+

| Tables_in_test_sqoop |

+----------------------+

| employee_salary |

+----------------------+

1 row in set (0.00 sec)

mysql> truncate employee_salary;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee_salary;

Empty set (0.00 sec)

导数据

sqoop export -D mapred.job.queue.name=work --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --export-dir /user/hive/warehouse/test_sqoop.db/employee_salary/ --input-fields-terminated-by '\0001'
......
15/04/01 19:40:55 INFO mapreduce.ExportJobBase: Exported 2 records

sqoop export -D mapred.job.queue.name=work --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --export-dir /user/hive/warehouse/test_sqoop.db/employee_salary/ --input-fields-terminated-by '\0001'

......

15/04/01 19:40:55 INFO mapreduce.ExportJobBase: Exported 2 records

.
查看结果，记录已经成功导出到mysql中。

(7) 从mysql表增量导入数据到hive
在mysql表中插入增量数据
插入一条wangwu

mysql> insert into employee_salary values('wangwu',3,6000);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee_salary;
+----------+----+--------+
| name     | id | salary |
+----------+----+--------+
| lisi     |  2 |   5500 |
| zhangsan |  1 |   5000 |
| wangwu   |  3 |   6000 |
+----------+----+--------+
3 rows in set (0.00 sec)

mysql> insert into employee_salary values('wangwu',3,6000);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee_salary;

+----------+----+--------+

| name | id | salary |

+----------+----+--------+

| lisi | 2 | 5500 |

| zhangsan | 1 | 5000 |

| wangwu | 3 | 6000 |

+----------+----+--------+

3 rows in set (0.00 sec)

增量导入

sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --hive-import  --hive-table  
test_sqoop.employee_salary --check-column id --incremental append --last-value 2
OK
Time taken: 0.77 seconds
Loading data to table test_sqoop.employee_salary
Table test_sqoop.employee_salary stats: [numFiles=3, numRows=0, totalSize=42,
 rawDataSize=0]OK
Time taken: 0.625 seconds

sqoop import -D mapred.job.queue.name=lesson --connect jdbc:mysql://debugo03/test_sqoop --username test --password test --table employee_salary --hive-import --hive-table

test_sqoop.employee_salary --check-column id --incremental append --last-value 2

Time taken: 0.77 seconds

Loading data to table test_sqoop.employee_salary

Table test_sqoop.employee_salary stats: [numFiles=3, numRows=0, totalSize=42,

rawDataSize=0]OK

Time taken: 0.625 seconds

查看结果
select * from test_sqoop.employee_salary;

Great article. I’d like to translate the article to http://ingest.tips. Is it okay?

db浆糊 on 2015年4月9日 at 下午5:04 said:

OK, go ahead.
- 赛馬壕 on 2015年5月21日 at 上午8:33 said:
  
  已经发布，内容采用了部分。http://ingest.tips/2015/05/20/a-roundtrip-from-mysql-to-hive/ 再次感谢，也欢迎投稿。
  - db浆糊 on 2015年5月21日 at 上午11:17 said:
    
    OK~

荣誉上榜

精彩大片你懂，htTP://uVU.Cc/ijW6

全都到碗里来！美臀/丝袜/美熟女乱伦精品大合集！！！HTTp://uVU.cc/inRB

这个更刺ｃ激，准备好手纸哦 A 片。。 http://T.CN/RcFeD2C

dddd

ddd

《水手》像父亲的责骂母亲的哭泣永远难忘怀这一句是我最喜欢的，当初听这首歌是因为是他唱的。后来真的爱上了这首歌。每次一到这句话总有一种哭泣冲动。

Sqoop常用操作

1. 准备mysql数据

2. 安装sqoop

3. 常用命令

近期文章

热评文章

文章归档

分类目录

友链

功能