解决sqoop导数据时，由于两个数据端的字段名不一致从而导致split-by报错 Unknown column ‘id‘ in ‘where clause‘ 问题（sqoop原理详解）

炼金术士辛吉德

已于 2024-01-19 10:04:23 修改

阅读量1.3k

点赞数 28

文章标签： sqoop hadoop hive mysql 大数据数据仓库

于 2024-01-17 15:06:30 首次发布

本文链接：https://blog.csdn.net/weixin_46574678/article/details/135627683

版权

文章讲述了在将MySQL数据库中的数据迁移到Hive时，如何处理因字段名不一致导致的SQL语法错误。通过理解sqoop的底层原理和split-by参数，提出使用boundary-query参数来避免查询边界时的字段别名问题。

摘要由CSDN通过智能技术生成

项目场景：

采用sqoop向hive导入数据时，–boundary-query解决由于两个数据端的字段名不一致从而导致split-by报错 Unknown column ‘id‘ in ‘where clause‘ 问题

在工作中将本地mysql数据库中PB级业务存量数据使sqoop抽到集群hive上时，hive中的数据表字段名称由于业务需要与本地mysql数据库中的字段名称不一样，因此在使用sqoop进行抽取需AS别名来进行字段匹配，但是由于为使用分区处理而添加了命令行参数 -m 和 –split-by，而使用 –split-by 来对数据切分块时使用的是id字段，那么问题来了，两端id名不一致，比如一端叫id另一端叫emp_id，就会报错 Unknown column ‘id‘ in ‘where clause‘ 。

mysql数据表结构如下：（字段名为id）
在这里插入图片描述
而hive上的数据表结构里：id的字段名为emp_id:

sqoop脚本如下：

sqoop import --connect 'jdbc:mysql://192.168.194.185:3306/local_test?dontTrackOpenResources=true&defaultFetchSize=3000&useCursorFetch=true' \
--username 'username' \
--password 'password' \
--driver com.mysql.cj.jdbc.Driver \
--query '
SELECT id AS emp_id, 
name  AS  emp_name ,
deptId  AS  cmp_deptid  ,
salary  AS  emp_salary  ,
FROM test
WHERE id > 0 AND id < 10001 AND $CONDITIONS'  \
--hcatalog-database my_hive  \
--hcatalog-table tb_test \
--split-by id  -m 8

问题描述

采用以上 --split-by id -m 8 参数进行数据分块时，报错：

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'id' in 'where clause' 在这里插入图片描述

sqoop import --connect 'jdbc:mysql://192.168.194.185:3306/local_test?dontTrackOpenResources=true&defaultFetchSize=3000&useCursorFetch=true' \
--username 'username' \
--password 'password' \
--driver com.mysql.cj.jdbc.Driver \
--query '
SELECT id AS emp_id, 
name  AS  emp_name ,
deptId  AS  cmp_deptid  ,
salary  AS  emp_salary  ,
FROM test
WHERE id > 0 AND id < 10001 AND $CONDITIONS'  cei_\
--hcatalog-database my_hive  \
--hcatalog-table tb_test \
--split-by emp_id  -m 8

使用如上脚本，将 --split-by id -m 8 改为 --split-by emp_id -m 8 ，依然会报错：
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'emp_id' in 'where clause'
在这里插入图片描述
那么问题出在哪里呢？在查阅资料了解了sqoop 的底层原理和运行机制之后就不难找到问题原因了，请看下文sqoop原理详解以及 --split-by -m 参数机制

原理详解（了解即可，可略过）：

首先来了解一下sqoop：
Apache Sqoop（SQL-to-Hadoop）项目旨在协助 RDBMS 与 Hadoop 之间进行高效的大数据交流。用户可以在 Sqoop 的帮助下，轻松地把关系型数据库(如mysql、Oracle)的数据导入到 Hadoop 与其相关的系统 (如HBase和Hive)中；同时也可以把数据从 Hadoop 系统里抽取并导出到关系型数据库里。
简单来说Sqoop就是一个桥梁，连接了关系型数据库与Hadoop，用于将批量数据在关系型数据库与hadoop间并行高效传输。

sqoop的大致流程：

（1）读取要导入数据的表结构，生成运行类，默认是QueryResult，打成jar包，然后提交给Hadoop；

（2）设置好job，主要也就是设置好各个参数
在这里插入图片描述
（3）这里就由Hadoop来执行MapReduce来执行Import命令
① 首先要对数据进行切分，也就是DataSplit，DataDrivenDBInputFormat.getSplits(JobContext job)
② 切分好范围后，写入范围，以便读取DataDrivenDBInputFormat.write(DataOutput output)，这里是lowerBoundQuery and upperBoundQuery
③ 读取以上②写入的范围DataDrivenDBInputFormat.readFields(DataInput input)
④ 然后创建RecordReader从数据库中读取数据DataDrivenDBInputFormat.createRecordReader(InputSplit split,TaskAttemptContext context)
⑤ 创建MAP，MapTextImportMapper.setup(Context context)
⑥ RecordReader一行一行从关系型数据库中读取数据，设置好Map的Key和Value，交给MapDBRecordReader.nextKeyValue()
⑦ 运行MAP，mapTextImportMapper.map(LongWritable key, SqoopRecord val, Context context),最后生成的Key是行数据，由QueryResult生成，Value是NullWritable.get()

原因分析(现在进入正题) ：

首先我们要认识到的是sqoop在数据导入阶段是基于Map-Reduce运行的，使得多个map来并发执行抽取的操作，从而提高数据抽取的效率，那么既然是map-reduce那么必然涉及到划分与聚合，此时划分的关键就是 –split-by -m 参数来控制：

参数	含义说明
-m(或–num-mappers)	指定map task数，默认是四个，当指定为1时，可以不用设置split-by参数；不指定num-mappers时，默认为4，当不指定或者-m大于1时，需要指定split-by参数；
–split-by	–split-by指定了split column，即用哪列作为切分；在执行并行操作时(多个map task)，Sqoop需要知道以什么列split数据

因此Sqoop在执行MapReduce进行import时，会根据不同的split-by参数值（列名）来进行切分,不同类型的列切分方式不同。下面以int型column来说明：
1、先查出split column的最小值和最大值MIN(id),MAX(id)，也就是边界
在这里插入图片描述
2、然后根据map task数对(max-min)之间的数据进行均匀的范围切分，将切分出来的区域分配到不同map中。每个map中再处理数据库中获取的一行一行的值，写入到Hive中。

例如 id 作为split column,其最小值是0、最大值1000，如果设置4个map数，每个map task执行的查询语句类似于：SELECT * FROM test WHERE id > 0 AND id < 1001，每个task里切分后分到的id的值分别是 (0, 250), (250, 500), (500, 750), (750, 1001)，如下：

org.apache.sqoop.mapreduce.db.DBRecordReader: Executing query: SELECT *  FROM test
WHERE id > 0 AND id < 1001 AND ( id >= 0 ) AND ( id < 250 )
org.apache.sqoop.mapreduce.db.DBRecordReader: Executing query: SELECT *  FROM test
WHERE id > 0 AND id < 1001 AND ( id >= 250 ) AND ( id < 500 )
org.apache.sqoop.mapreduce.db.DBRecordReader: Executing query: SELECT *  FROM test
WHERE id > 0 AND id < 1001 AND ( id >= 500 ) AND ( id < 750 )
org.apache.sqoop.mapreduce.db.DBRecordReader: Executing query: SELECT *  FROM test
WHERE id > 0 AND id < 1001 AND ( id >= 750 ) AND ( id < 1001 )

上面的四条query会被分到4个map task 里面跑，后每个map各自获取各自SQL中的数据导入到hive；

铺垫结束，正片开始：
好了，现在了解了整个–split-by流程，那么就好分析为什么会报错了；
执行如下sqoop语句时：

sqoop import --connect 'jdbc:mysql://192.168.194.185:3306/local_test?dontTrackOpenResources=true&defaultFetchSize=3000&useCursorFetch=true' \
--username 'username' \
--password 'password' \
--driver com.mysql.cj.jdbc.Driver \
--query '
SELECT id AS emp_id, 
name  AS  emp_name ,
deptId  AS  cmp_deptid  ,
salary  AS  emp_salary  ,
FROM test
WHERE id > 0 AND id < 10001 AND $CONDITIONS'  cei_\
--hcatalog-database my_hive  \
--hcatalog-table tb_test \
--split-by id  -m 8

报错如下：
在这里插入图片描述

为什么会说字段列表”中的未知列“ id” 呢？因为是 –split-by id ，可以看到在sqoop去查找id的最大最小边界时是从select from 子查询中查找id，而在FROM子查询中已经将id起了别名emp_id，所以在查找边界的时候就会找不到 id 字段 ,自然就会报未知的id列。

那么可能有小伙伴就会说，那参数改成 –split-by emp_id 不就行了？
改成如下sqoop语句再执行一遍试试：

sqoop import --connect 'jdbc:mysql://192.168.194.185:3306/local_test?dontTrackOpenResources=true&defaultFetchSize=3000&useCursorFetch=true' \
--username 'username' \
--password 'password' \
--driver com.mysql.cj.jdbc.Driver \
--query '
SELECT id AS emp_id, 
name  AS  emp_name ,
deptId  AS  cmp_deptid  ,
salary  AS  emp_salary  ,
FROM test
WHERE id > 0 AND id < 10001 AND $CONDITIONS'  cei_\
--hcatalog-database my_hive  \
--hcatalog-table tb_test \
--split-by id  -m 8

然而依然报错如下：
在这里插入图片描述
从截图可以看出在改成 –split-by emp_id后，成功找到了最大最小边界，并且成功切片，然后Submitted application到yarn执行mapreduce，而且开始执行每个map task，进入map阶段；

但是！！！不要以为这样就成功了！接下来就又开始一连串的报错发生，如下截图所示！！
在这里插入图片描述

一共8个map，每一个map task 都报错：
在这里插入图片描述
为什么会出现这样的问题呢？
我们到 Yarn Job History Server UI 里查看log：

最终查到详细日志报错部分详细如下：
在这里插入图片描述
经过分析，我们这个map 分到的split为 emp_id >= 1 AND emp_id < 1251 ，所以每个map会按照 WHERE id > 00000 AND id < 10001 AND ( emp_id >= 1 ) AND ( emp_id < 1251 ) 去执行属于自己的任务，于是emp_id 是别名，无法在WHERE条件内使用，于是就报错了；

TIPS: 由于sql语句执行顺序的原因，where 优先于select ，所以where条件内不能包含
字段别名；（sql执行顺序可以去翻阅一下资料）

综上所述，都是id字段起别名惹的祸！！！使用了split-by在查找边界最大最小值的时候，split搞出的事情！！！

解决方案：

方法一：
要么就修改表结构把两端数据库的表字段都搞成一致的，要id都id，要emp_id都emp_id；要么就别使用 --split-by 参数并且把 -m设置成1，即只有一个map运行，缺点是不能并行map录入数据，问题就解决了，是不是很简单粗暴？这样是可以，但是由于公司或者业务需求，两端的字段必须不一致那么就没办法这样了。

还有几点友情提示：

–split-by
最好使用自增主键及数字类型的字段，对非数字类型的字段如字符串类型或者其它类型的字段可以说基本无法支持，效果极差！！如果随意使用会导致数据倾斜，甚至数据重复或缺少！！

方法二（压轴出场，绝对好使）：

添加参数：

sqoop import \
--boundary-query "SELECT min(id), max(id) from table"
--split-by id -m 8

修正后的sqoop语句如下：

sqoop import --connect 'jdbc:mysql://192.168.194.185:3306/local_test?dontTrackOpenResources=true&defaultFetchSize=3000&useCursorFetch=true' \
--username 'username' \
--password 'password' \
--driver com.mysql.cj.jdbc.Driver \
--query '
SELECT id AS emp_id, 
name  AS  emp_name ,
deptId  AS  cmp_deptid  ,
salary  AS  emp_salary  ,
FROM test
WHERE id > 0 AND id < 10001 AND $CONDITIONS'  \
--hcatalog-database my_hive  \
--hcatalog-table tb_test \
--split-by id  -m 8 \
--boundary-query "select min(id),max(id) FROM test WHERE id > 0 AND id < 10001"

这个参数是干啥的呢？
之前–split-by id 将根据map数将数据默认找到最大最小值进行切分。

现在默认的边界查询是这样的：
先根据 –boundary-query 找到最大最小值，也就是边界，那么 --split-by id 的时候就不会再去找最大最小值了。
也就是说如果你提前知道 id 从 min(id) 开始，以 max(id) 结束，那么计算 min() 和 max() 操作就没有意义了，所以切分也是使用的是id，即WHERE id > 00000 AND id < 10001 AND ( id >= 1 ) AND ( id < 1251 )
那么每个map里的切分如下：

WHERE id > 0 AND id < 1001 AND ( id >= 0 ) AND ( id < 1250 )
WHERE id > 0 AND id < 1001 AND ( id >= 1250 ) AND ( id < 2500 )
。。。。

而且同时还可以在–boundary-query 参数里指定任何只要返回 min(id) 和 max(id) 的select查询语句。

总结：

其实从上面我们可以知道，事情的祸因就是在 --split-by 时默认查询边界最大和最小值后，要么因为别名不知道最大最小值在哪，要么知道在哪但是将别名emp_id放到了where里，对sql语句造成了干扰，归根结底就是找边界时搞出的事；所以我们把找边界和sql语句分开不就行了，使用–boundary-query先找出边界互不干扰，问题就解决了。

参考链接：
https://blogs.apache.org/sqoop/entry/apache_sqoop_highlights_of_sqoop
http://www.slideshare.net/cloudera/apache-sqoop-a-data-transfer-tool-for-hadoop
http://www.slideshare.net/Hadoop_Summit/new-data-transfer-tools-for-hadoop
https://www.cnblogs.com/edisonchou/p/4440216.html
https://blog.csdn.net/huahuaxiaoshao/article/details/90273148