[
https://issues.apache.org/jira/browse/SQOOP-463?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13230263#comment-13230263
]
Jarek Jarcec Cecho edited comment on SQOOP-463 at 3/16/12 8:11 AM:
-------------------------------------------------------------------
I personally believe that mapping TINYINT(1) to java.sql.Types.BIT might be a
bug in MySQL JDBC connector. As far as I know, the length field in numeric
types in MySQL is only used for setting default display width and it's not
related to actual data size. So that column with TINYINT(1) might contain
higher values than 1. I was able to verify this piece of information in MySQL
manual (see [1]) and live in running server (see [2]).
[1] http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html
[2] MySQL calls:
mysql> create table pokus(col tinyint(1));
Query OK, 0 rows affected (0.06 sec)
mysql> describe pokus;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into pokus values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pokus values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pokus values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pokus values(10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pokus;
+------+
| col |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
+------+
4 rows in set (0.00 sec)
was (Author: jarcec):
I personally believe that mapping TINYINT(1) to java.sql.Types.BIT might be
a bug in MySQL JDBC connector. As far as I know, the length field in numeric
types in MySQL is only used for setting default display width and it's not
related to actual data size. So that column with TINYINT(1) might contains
higher values than 1. I was able to verify this piece of information in MySQL
manual (see [1]) and live in running server (see [2]).
[1] http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html
[2] MySQL calls:
mysql> create table pokus(col tinyint(1));
Query OK, 0 rows affected (0.06 sec)
mysql> describe pokus;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into pokus values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pokus values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pokus values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pokus values(10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pokus;
+------+
| col |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
+------+
4 rows in set (0.00 sec)
> Sqoop User Guide's troubleshooting section should explain how to override the
> column mapping when importing a table from MySQL into Hive
> ----------------------------------------------------------------------------------------------------------------------------------------
>
> Key: SQOOP-463
> URL: https://issues.apache.org/jira/browse/SQOOP-463
> Project: Sqoop
> Issue Type: Improvement
> Components: docs
> Affects Versions: 1.4.1-incubating
> Reporter: Kathleen Ting
> Assignee: Kathleen Ting
> Attachments: SQOOP-463-1.patch, SQOOP-463.patch
>
>
> When importing a table from MySQL into Hive where one of the columns is of
> datatype TINYINT(1), Sqoop will automatically map this to the Hive datatype
> boolean. This is because the MySQL JDBC connector maps the TINYINT(1)
> datatype to java.sql.Types.BIT, which Sqoop by default maps to the Hive type
> Boolean. Consequently, if you have values such as 1 or 0 in this column, they
> will fail to parse correctly in Hive, instead appearing as all NULL values.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
