[
https://issues.apache.org/jira/browse/HIVE-27847?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Shohei Okumiya reassigned HIVE-27847:
-------------------------------------
Assignee: Shohei Okumiya (was: Basapuram Kumar)
> Prevent query Failures on Numeric <-> Timestamp
> ------------------------------------------------
>
> Key: HIVE-27847
> URL: https://issues.apache.org/jira/browse/HIVE-27847
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Affects Versions: 4.0.0-alpha-1, 4.0.0-alpha-2
> Environment: master
> 4.0.0-alpha-1
> Reporter: Basapuram Kumar
> Assignee: Shohei Okumiya
> Priority: Major
> Labels: hive-4.0.1-must, pull-request-available
> Attachments: HIVE-27847.patch
>
>
> In Master/4.0.0-alpha-1 branches, performing the Numeric to Timestamp
> conversion, its failing with the error as
> "{color:#de350b}org.apache.hadoop.hive.ql.exec.UDFArgumentException: Casting
> NUMERIC types to TIMESTAMP is prohibited
> (hive.strict.timestamp.conversion){color}" .
>
> *Repro steps.*
> # Sample data
> {noformat}
> $ hdfs dfs -cat /tmp/tc/t.csv
> 1653209895687,2022-05-22T15:58:15.931+07:00
> 1653209938316,2022-05-22T15:58:58.490+07:00
> 1653209962021,2022-05-22T15:59:22.191+07:00
> 1653210021993,2022-05-22T16:00:22.174+07:00
> 1653209890524,2022-05-22T15:58:10.724+07:00
> 1653210095382,2022-05-22T16:01:35.775+07:00
> 1653210044308,2022-05-22T16:00:44.683+07:00
> 1653210098546,2022-05-22T16:01:38.886+07:00
> 1653210012220,2022-05-22T16:00:12.394+07:00
> 1653210000376,2022-05-22T16:00:00.622+07:00{noformat}
> # table with above data [1]
> {noformat}
> create external table test_ts_conv(begin string, ts string) row format
> delimited fields terminated by ',' stored as TEXTFILE LOCATION '/tmp/tc/';
> desc test_ts_conv;
> | col_name | data_type | comment |
> +-----------+------------+----------+
> | begin | string | |
> | ts | string | |
> +-----------+------------+----------+{noformat}
> # Create table with CTAS
> {noformat}
> 0: jdbc:hive2://char1000.sre.iti.acceldata.de> set
> hive.strict.timestamp.conversion;
> +-----------------------------------------+
> | set |
> +-----------------------------------------+
> | hive.strict.timestamp.conversion=true |
> +-----------------------------------------+
> set to false
> 0: jdbc:hive2://char1000.sre.iti.acceldata.de> set
> hive.strict.timestamp.conversion=false;
> +-----------------------------------------+
> | set |
> +-----------------------------------------+
> | hive.strict.timestamp.conversion=false |
> +-----------------------------------------+
> #Query:
> 0: jdbc:hive2://char1000.sre.iti.acceldata.de>
> CREATE TABLE t_date
> AS
> select
> CAST( CAST( `begin` AS BIGINT) / 1000 AS TIMESTAMP ) `begin`,
> CAST(
> DATE_FORMAT(CAST(regexp_replace(`ts`,'(\\d{4})-(\\d{2})-(\\d{2})T(\\d{2}):(\\d{2}):(\\d{2}).(\\d{3})\\+(\\d{2}):(\\d{2})','$1-$2-$3
> $4:$5:$6.$7') AS TIMESTAMP ),'yyyyMMdd') as BIGINT ) `par_key`
> FROM test_ts_conv;{noformat}
> Error:
> {code:java}
> Caused by: org.apache.hadoop.hive.ql.exec.UDFArgumentException: Casting
> NUMERIC types to TIMESTAMP is prohibited (hive.strict.timestamp.conversion)
> at
> org.apache.hadoop.hive.ql.udf.generic.GenericUDFTimestamp.initialize(GenericUDFTimestamp.java:91)
> at
> org.apache.hadoop.hive.ql.udf.generic.GenericUDF.initializeAndFoldConstants(GenericUDF.java:149)
> at
> org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.initialize(ExprNodeGenericFuncEvaluator.java:184)
> at
> org.apache.hadoop.hive.ql.exec.Operator.initEvaluators(Operator.java:1073)
> at
> org.apache.hadoop.hive.ql.exec.Operator.initEvaluatorsAndReturnStruct(Operator.java:1099)
> at
> org.apache.hadoop.hive.ql.exec.SelectOperator.initializeOp(SelectOperator.java:74)
> at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:360)
> at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:549)
> at
> org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:503)
> at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:369)
> at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:549)
> at
> org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:503)
> at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:369)
> at
> org.apache.hadoop.hive.ql.exec.MapOperator.initializeMapOperator(MapOperator.java:508)
> at
> org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.init(MapRecordProcessor.java:314)
> ... 17 more {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
