Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-28285

Exception when querying JDBC tables with Hive/DB column types mismatch

    XMLWordPrintableJSON

Details

    Description

      Queries over JDBC tables fail at runtime when the following conditions hold:

      1. there is a mismatch between the Hive type and the database type for some columns
      2. CBO is not used

      CBO may not be used when compiling the query for various reasons:

      • CBO is explicitly disabled (via hive.cbo.enable property)
      • Query explicitly not supported in CBO (e.g., contains DISTRIBUTE BY clause)
      • Problem/bug in compilation that will skip CBO execution

      The examples below demonstrate the problem with Postgres but the problem itself is not database specific (although different errors may pop up depending on the underlying database). Different type mappings may also lead to different errors.

      Map Postgres DATE to Hive TIMESTAMP

      Postgres

      create table date_table (cdate date);
insert into date_table values ('2024-05-29');

      Hive

      CREATE TABLE h_type_table (cdate timestamp)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    "hive.sql.database.type" = "POSTGRES",
    "hive.sql.jdbc.driver" = "org.postgresql.Driver",
    "hive.sql.jdbc.url" = "jdbc:postgresql://...",
    "hive.sql.dbcp.username" = "user",
    "hive.sql.dbcp.password" = "pwd",
    "hive.sql.table" = "date_table"
);

      Hive Result (CBO on)

      2024-05-29 00:00:00

      Error (CBO off)

       java.lang.RuntimeException: java.io.IOException: java.lang.IllegalArgumentException: Cannot create timestamp, parsing error 2024-05-29
	at org.apache.hadoop.hive.ql.exec.FetchTask.executeInner(FetchTask.java:210)
	at org.apache.hadoop.hive.ql.exec.FetchTask.execute(FetchTask.java:95)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:212)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:154)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:149)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:185)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:230)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:257)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd1(CliDriver.java:201)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:127)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:425)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:356)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClientInternal(QTestUtil.java:732)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClient(QTestUtil.java:702)
	at org.apache.hadoop.hive.cli.control.CoreCliDriver.runTest(CoreCliDriver.java:116)
	at org.apache.hadoop.hive.cli.control.CliAdapter.runTest(CliAdapter.java:157)
	at org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver(TestMiniLlapLocalCliDriver.java:62)
Caused by: java.io.IOException: java.lang.IllegalArgumentException: Cannot create timestamp, parsing error 2024-05-29
	at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:628)
	at org.apache.hadoop.hive.ql.exec.FetchOperator.pushRow(FetchOperator.java:535)
	at org.apache.hadoop.hive.ql.exec.FetchTask.executeInner(FetchTask.java:194)
	... 55 more
Caused by: java.lang.IllegalArgumentException: Cannot create timestamp, parsing error 2024-05-29
	at org.apache.hadoop.hive.common.type.Timestamp.valueOf(Timestamp.java:194)
	at org.apache.hive.storage.jdbc.JdbcSerDe.deserialize(JdbcSerDe.java:314)
	at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:609)
	... 57 more
Caused by: java.time.format.DateTimeParseException: Text '2024-05-29' could not be parsed: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO resolved to 2024-05-29 of type java.time.format.Parsed
	at java.time.format.DateTimeFormatter.createError(DateTimeFormatter.java:1920)
	at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1855)
	at java.time.LocalDateTime.parse(LocalDateTime.java:492)
	at org.apache.hadoop.hive.common.type.Timestamp.valueOf(Timestamp.java:188)
	... 59 more
Caused by: java.time.DateTimeException: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO resolved to 2024-05-29 of type java.time.format.Parsed
	at java.time.LocalDateTime.from(LocalDateTime.java:461)
	at java.time.format.Parsed.query(Parsed.java:226)
	at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)
	... 61 more
Caused by: java.time.DateTimeException: Unable to obtain LocalTime from TemporalAccessor: {},ISO resolved to 2024-05-29 of type java.time.format.Parsed
	at java.time.LocalTime.from(LocalTime.java:409)
	at java.time.LocalDateTime.from(LocalDateTime.java:457)
	... 63 more

      Map Postgres NUMERIC to Hive BIGINT

      Postgres

      create table numeric_table (cnumeric numeric);
insert into numeric_table values (60.242);
insert into numeric_table values (90);
insert into numeric_table values (50.1);

      Hive

      CREATE TABLE h_type_table (cnumeric bigint)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES ( ...,    "hive.sql.table" = "numeric_table");
SELECT * FROM h_type_table;

      Hive Results (CBO on)

      60
      90
      50

      Error (CBO off)

      java.lang.RuntimeException: java.io.IOException: java.io.IOException: org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Caught exception while trying to execute query: Error while trying to get column names: Decimal precision out of allowed range [1,38]
	at org.apache.hadoop.hive.ql.exec.FetchTask.executeInner(FetchTask.java:210)
	at org.apache.hadoop.hive.ql.exec.FetchTask.execute(FetchTask.java:95)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:212)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:154)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:149)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:185)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:230)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:257)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd1(CliDriver.java:201)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:127)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:425)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:356)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClientInternal(QTestUtil.java:732)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClient(QTestUtil.java:702)
	at org.apache.hadoop.hive.cli.control.CoreCliDriver.runTest(CoreCliDriver.java:116)
	at org.apache.hadoop.hive.cli.control.CliAdapter.runTest(CliAdapter.java:157)
	at org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver(TestMiniLlapLocalCliDriver.java:62)
Caused by: java.io.IOException: java.io.IOException: org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Caught exception while trying to execute query: Error while trying to get column names: Decimal precision out of allowed range [1,38]
	at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:628)
	at org.apache.hadoop.hive.ql.exec.FetchOperator.pushRow(FetchOperator.java:535)
	at org.apache.hadoop.hive.ql.exec.FetchTask.executeInner(FetchTask.java:194)
	... 55 more
Caused by: java.io.IOException: org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Caught exception while trying to execute query: Error while trying to get column names: Decimal precision out of allowed range [1,38]
	at org.apache.hive.storage.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:85)
	at org.apache.hive.storage.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:35)
	at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:595)
	... 57 more
Caused by: org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Caught exception while trying to execute query: Error while trying to get column names: Decimal precision out of allowed range [1,38]
	at org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.getRecordIterator(GenericJdbcDatabaseAccessor.java:254)
	at org.apache.hive.storage.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:58)
	... 59 more
Caused by: org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Error while trying to get column names: Decimal precision out of allowed range [1,38]
	at org.apache.hive.storage.jdbc.dao.JdbcRecordIterator.<init>(JdbcRecordIterator.java:85)
	at org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.getRecordIterator(GenericJdbcDatabaseAccessor.java:249)
	... 60 more
Caused by: java.lang.IllegalArgumentException: Decimal precision out of allowed range [1,38]
	at org.apache.hadoop.hive.serde2.typeinfo.HiveDecimalUtils.validateParameter(HiveDecimalUtils.java:44)
	at org.apache.hadoop.hive.serde2.typeinfo.DecimalTypeInfo.<init>(DecimalTypeInfo.java:36)
	at org.apache.hadoop.hive.serde2.typeinfo.TypeInfoFactory.createPrimitiveTypeInfo(TypeInfoFactory.java:165)
	at org.apache.hadoop.hive.serde2.typeinfo.TypeInfoFactory.getPrimitiveTypeInfo(TypeInfoFactory.java:117)
	at org.apache.hadoop.hive.serde2.typeinfo.TypeInfoFactory.getDecimalTypeInfo(TypeInfoFactory.java:186)
	at org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.lambda$static$0(GenericJdbcDatabaseAccessor.java:102)
	at org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.getColumnMetadata(GenericJdbcDatabaseAccessor.java:154)
	at org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.getColTypesFromRS(GenericJdbcDatabaseAccessor.java:174)
	at org.apache.hive.storage.jdbc.dao.JdbcRecordIterator.<init>(JdbcRecordIterator.java:72)
	... 61 more

      Map Postgres VARCHAR to Hive INT

      Postgres

      create table varchar_table (cvarchar varchar(20));
insert into varchar_table values ('10');
insert into varchar_table values ('20');
insert into varchar_table values ('30.05');

      Hive

      CREATE TABLE h_type_table (cvarchar bigint)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES ( ...,    "hive.sql.table" = "varchar_table");
SELECT * FROM h_type_table;

      Hive Results (CBO on)

      10
      20
      30

      Error (CBO off)

      java.lang.RuntimeException: java.io.IOException: java.lang.NumberFormatException: For input string: "30.05"
	at org.apache.hadoop.hive.ql.exec.FetchTask.executeInner(FetchTask.java:210)
	at org.apache.hadoop.hive.ql.exec.FetchTask.execute(FetchTask.java:95)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:212)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:154)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:149)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:185)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:230)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:257)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd1(CliDriver.java:201)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:127)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:425)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:356)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClientInternal(QTestUtil.java:732)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClient(QTestUtil.java:702)
	at org.apache.hadoop.hive.cli.control.CoreCliDriver.runTest(CoreCliDriver.java:116)
	at org.apache.hadoop.hive.cli.control.CliAdapter.runTest(CliAdapter.java:157)
	at org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver(TestMiniLlapLocalCliDriver.java:62)
Caused by: java.io.IOException: java.lang.NumberFormatException: For input string: "30.05"
	at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:628)
	at org.apache.hadoop.hive.ql.exec.FetchOperator.pushRow(FetchOperator.java:535)
	at org.apache.hadoop.hive.ql.exec.FetchTask.executeInner(FetchTask.java:194)
	... 55 more
Caused by: java.lang.NumberFormatException: For input string: "30.05"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
	at java.lang.Integer.parseInt(Integer.java:580)
	at java.lang.Integer.valueOf(Integer.java:766)
	at org.apache.hive.storage.jdbc.JdbcSerDe.deserialize(JdbcSerDe.java:242)
	at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:609)
	... 57 more

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. HIVE-27487 NPE in Hive JDBC storage handler

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #5274

          Activity

            People

              zabetak Stamatis Zampetakis
              zabetak Stamatis Zampetakis
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: