Sqoop抽取MySQL中tinyint类型数据只有0和1的问题

最新推荐文章于 2022-03-25 15:35:18 发布

DeathGXD

最新推荐文章于 2022-03-25 15:35:18 发布

阅读量1.2k

点赞数

分类专栏： Sqoop 文章标签： sqoop

本文链接：https://blog.csdn.net/gxd520/article/details/103852090

版权

当使用Sqoop从MySQL导入数据到Hive时，tinyint(1)类型的数值会被错误地转换为0和1。原因是MySQL JDBC connector将TINYINT(1)映射为BIT， Sqoop则将其视为Boolean。解决方案是在JDBC连接字符串中添加`tinyInt1isBit=false`，或通过`--map-column-hive`或`--map-column-java`选项显式设置列映射。

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

问题描述

使用Sqoop抽取MySQL数据到Hive时，会发现MySQL中tinyint类型的数据在抽到Hive中只有0和1，其他的数值都被替换了，很诡异。其实Sqoop的官方文档给出了解释并给出了解决方案：
27.2.5. MySQL: Import of TINYINT(1) from MySQL behaves strangely
Problem: Sqoop is treating TINYINT(1) columns as booleans, which is for example causing issues with HIVE import. This is because by default the MySQL JDBC connector maps the TINYINT(1) to java.sql.Types.BIT, which Sqoop by default maps to Boolean.

Solution: A more clean solution is to force MySQL JDBC Connector to stop converting TINYINT(1) to java.sql.Types.BIT by adding tinyInt1isBit=false into your JDBC path (to create something like jdbc:mysql://localhost/test?tinyInt1isBit=false). Another solution would be to explicitly override the column mapping for the datatype TINYINT(1) column. For example, if the column name is foo, then pass the following option to Sqoop during import: --map-column-hive foo=tinyi