sqoop(3):import之mysql/sqlserver/pgsql/OracleTohive

1.配置

&＃xff08;1&＃xff09;往/etc/profile最后加入 

export HADOOP_CLASSPATH&＃61;$HADOOP_CLASSPATH:/opt/modules/hive-1.2.1/lib/*

然后刷新配置

source /etc/profile

&＃xff08;2&＃xff09; 把hive-site复制到sqoop中&＃xff01;

 cp ../hive-1.2.1/conf/hive-site.xml  conf/

2.命令&＃xff1a;

bin/sqoop import \
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/sqoop \
--username root \
--password 123456 \
--table tohdfs \
--direct \
--delete-target-dir \
--hive-import \
--hive-database student \
--hive-table stu_info \
--num-mappers 1 \
--fields-terminated-by &＃39;\t&＃39;
二、Sqlserver To Hive

1.环境

&＃xff08;1&＃xff09;CDH5.15.2&＃xff08;sqoop1&＃xff09;

&＃xff08;2&＃xff09;centos7.4

&＃xff08;3&＃xff09;sqlserver 2017

2.配置环境

&＃xff08;1&＃xff09;下载连接sql server的jar包&＃xff0c;下载路径

https://docs.microsoft.com/zh-cn/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view&＃61;sql-server-2017

或者从maven仓库下载对应版本&＃xff0c;以mssql-jdbc-6.1.0.jre8.jar为例

&＃xff08;2&＃xff09;放到cdh的sqoop的lib目录下

/opt/cloudera/parcels/CDH/lib/sqoop/lib

3.执行导入hive语句

sqoop import \
--connect &＃39;jdbc:sqlserver://ip:1433;database&＃61;master&＃39; \
--username &＃39;sa&＃39; \
--password &＃39;[Root_123]&＃39; \
--table &＃39;student2&＃39; \
--direct \
--delete-target-dir \
--hive-import \
--hive-database test2 \
--hive-table student2_ms \
--num-mappers 1 \
--fields-terminated-by &＃39;\t&＃39;
三、postgresql To Hive

1.环境

&＃xff08;1&＃xff09;postgresql

&＃xff08;2&＃xff09;cdh5.15.2

2.下载postgresql的jdbc驱动包postgresql-42.2.2.jar

3.postgresql数据库信息

&＃xff08;1&＃xff09;端口&＃xff08;默认&＃xff09;&＃xff1a;5432 

4.创建pgsql数据表student

SELECT * FROM "student"
1    zhangsan    20    female
2    lisi    34    male
3    wangwu    35    male
4    wangwu3    35    male

5.切换到hdfs用户&＃xff0c;运行sqoop命令

su – hdfs

备注&＃xff1a;如果使用root用户&＃xff0c;则会报错

Caused by: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.AccessControlException): Permission denied: user&＃61;root, access&＃61;WRITE, inode&＃61;"/user":hdfs:supergroup:drwxr-xr-x        at org.apache.hadoop.hdfs.server.namenode.DefaultAuthorizationProvider.checkFsPermission

    原因&＃xff1a;使用root用户运行命令&＃xff0c;但是root用户在hdfs上不是万能用户&＃xff0c;hdfs上管理员权限是hdfs用户。所以&＃xff0c;需要切换到hdfs用户再运行命令。

6. 将jar&＃xff08;postgresql-42.2.2.jar&＃xff09;包放到cm的sqoop的lib下

/opt/cloudera/parcels/CDH/lib/sqoop/lib/postgresql-42.2.2.jar

7.sqoop导入到hive

sqoop import --connect jdbc:postgresql://ip:5432/bigdata --username postgres --password 123456 --table student --hive-import --hive-overwrite --hive-database test --hive-table student2  --lines-terminated-by "\n" --fields-terminated-by "\t" --m 2
四、oracle To Hive

1.环境

&＃xff08;1&＃xff09;oracle

&＃xff08;2&＃xff09;cdh5.15.2

2.导入hive命令

sqoop import --connect jdbc:oracle:thin:&＃64;ip:1521:orcl --username&＃61;username --password&＃61;Root_123 \
--table STUDENT3 \
--delete-target-dir \
--hive-import \
--hive-database test2 \
--hive-table student2_or \
-m 1 \
--fields-terminated-by &＃39;\t&＃39;

3.报错

&＃xff08;1&＃xff09;描述&＃xff1a;当导入STUDENT这张表的时候&＃xff0c;报错

Error: java.io.IOException: SQLException in nextKeyValueat org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277)at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:562)at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:793)at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)at java.security.AccessController.doPrivileged(Native Method)at javax.security.auth.Subject.doAs(Subject.java:422)at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1924)at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "CLASS": invalid identifierat oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)... 12 more

&＃xff08;2&＃xff09;原因&＃xff1a;因为这张表是使用Navicat Premium创建的&＃xff0c;而Navicat创建oracle表的时候&＃xff0c;默认列名用双引号引着&＃xff0c;而oracle默认是不区分大小写的&＃xff0c;所以导致报错&＃xff0c;如果把双引号去掉&＃xff0c;即可。即用Navicat创建的时候是:

CREATE TABLE "STUDENT"(
"id" NUMBER(10,0) NOT NULL ENABLE,
"name" VARCHAR2(20 CHAR) NOT NULL ENABLE,
PRIMARY KEY (ID)
)


&＃xff08;3&＃xff09;解决办法&＃xff1a;把“”冒号去掉&＃xff0c;即可

CREATE TABLE STUDENT(
id NUMBER(10,0) NOT NULL ENABLE,
name VARCHAR2(20 CHAR) NOT NULL ENABLE,
PRIMARY KEY (ID)
)

五、总结

1.参考资料&＃xff1a;官网

http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.15.2/SqoopUserGuide.html