sqoop将Mysql数据导入Hive中

r囧r小猫 2022-08-31 09:49 275阅读 0赞

本实验Sqoop版本：Sqoop 1.4.6.2.6.0.3-8

## 1 mysql创建表 ##

DROP TABLE IF EXISTS \`testsqoop\`;

CREATE TABLE \`testsqoop\` (

\`XH\` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '序号',

\`SSQ\` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '所属区'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

## 2 插入数据 ##

INSERT INTO \`testsqoop\` VALUES ('1', '海珠区');

INSERT INTO \`testsqoop\` VALUES ('10', '白云区');

INSERT INTO \`testsqoop\` VALUES ('11', '黄埔区');

INSERT INTO \`testsqoop\` VALUES ('12', '荔湾区');

INSERT INTO \`testsqoop\` VALUES ('13', '天河区');

INSERT INTO \`testsqoop\` VALUES ('14', '海珠区');

INSERT INTO \`testsqoop\` VALUES ('15', '海珠区');

## 3 执行导入Hive的命令 ##

### 3.1 简单导入 ###

（如果Hive中没有存在对应的hive表，则会依据mysql 的表来创建对应的表，字段属性也跟mysql的一致）

这张表存在的情况下（默认往表中追加数据）

su - hdfs

sqoop import --connect jdbc:mysql://172.16.2.136:3333/wxh --username root --password byxf54root --table  testsqoop --hive-import --hive-table testsqoop -m 1

这张表不存在的情况下（默认会自动创建对应的Hive表并全量将数据加载进去）

\-bash-4.2$ hive

log4j:WARN No such property \[maxFileSize\] in org.apache.log4j.DailyRollingFileAppender.

Logging initialized using configuration in file:/etc/hive/2.6.0.3-8/0/hive-log4j.properties

WARNING: Directory for Hive history file: /home/hive does not exist.   History will not be available during this session.

hive> show databases;

default

fdm

hive> use default;

hive> show tables;

testsqoop

hive> select \* from testsqoop;

1 海珠区

10 白云区

11 黄埔区

12 荔湾区

13 天河区

14 海珠区

15 海珠区

16 海珠区

17 荔湾区

18 海珠区

19 黄埔区

2 黄埔区

hive> show create table testsqoop;

CREATE TABLE \`testsqoop\`(

\`xh\` string,

\`ssq\` string)

COMMENT 'Imported by sqoop on 2021/07/23 20:54:36'

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\\u0001'

LINES TERMINATED BY '\\n'

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION

'hdfs://hadoop-test03/apps/hive/warehouse/testsqoop'

TBLPROPERTIES (

'numFiles'='1',

'numRows'='0',

'rawDataSize'='0',

'totalSize'='271',

'transient\_lastDdlTime'='1627044892')

Time taken: 0.338 seconds, Fetched: 19 row(s)

### 3.2 复杂查询条件导入-where 参数的使用 ###

sqoop import -Dorg.apache.sqoop.splitter.allow\_text\_splitter=true

\--connect jdbc:mysql://192.168.1.2:3306/test

\--username root

\--password 123456

\--table  test1

\--where

" SXQMC='广东省广州市萝岗区' and XZJDMC='九龙镇' and BMC='女' and S\_LAST\_UPDATED > '2018-01-04 03:10:13'  and  S\_LAST\_UPDATED < '2018-01-04 03:21:00' "

\--hive-import

\--hive-table test1

\--hive-drop-import-delims

### 3.3 query 参数的使用 ###

sqoop import

\-Dorg.apache.sqoop.splitter.allow\_text\_splitter=true

\--connect jdbc:mysql://192.168.1.2:3306/test

\--username root

\--password 123456

\--query

" select \* from test1  where SXQMC='广东省广州市番禺区' and BMC='女' and S\_LAST\_UPDATED > '2018-01-04 03:10:13'  and  S\_LAST\_UPDATED < '2018-01-04 03:21:00'  AND \\$CONDITIONS"

\--hive-import

\--hive-table test1

\--hive-drop-import-delims

\--target-dir  /apps/hive/warehouse/test1

\--split-by s\_duid

## 4 相关参数 ##

## 5 问题 ##

### 问题一： ###

出现如下错误提示：No primary key could be found for table t\_gz\_cyyqmcb

17/12/13 10:35:58 ERROR tool.ImportTool: Error during import: No primary key could be found for table t\_gz\_cyyqmcb.

解决方法一：给该表添加主键便可；

解决方法二：加到sqoop 对应的参数 -m 1 ，也就是该命令只在一个mapreduce进程中进行，所以该方法会使得数据抽取的效率变低。