Basapuram Kumar created HIVE-27847:
--------------------------------------
Summary: 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
Environment: master
4.0.0-alpha-1
Reporter: Basapuram Kumar
Assignee: Basapuram Kumar
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)
