HIVE_BAD_DATA: Field #event_time's type TIMESTAMP in ORC is incompatible with type varchar defined in table schema

Viewed 305

Recently, we archive hive data to s3(data format is orc), but when query the data by athena got some error.

when I execute select * from ta_event_10 limit 1, Athena query got error, the error like this:

HIVE_BAD_DATA: Field #event_time's type TIMESTAMP in ORC is incompatible with type varchar defined in table schema

In addition, some sql can run but result is not correct, such as select "#event_time" from ta_event_10 limit 10.

hive show create table:

    CREATE TABLE `ta.ta_event_10`(
  `#user_id` bigint,
  `#event_name` string,
  `#event_time` timestamp,
  `#app_id` string,
  `#account_id` string,
  `#distinct_id` string,
  `#server_time` timestamp,
  `#kafka_offset` bigint,
  `#device_id` string,
  `version_code` double,
  `#province` string,
  `#device_model` string,
  `#city` string,
  `channel` string,
  `launchsource` string,
  `flavor` string,
  `#app_version` string,
  `#os` string,
  `#country_code` string,
  `#ip` string,
  `#os_version` string,
  `#manufacturer` string,
  `#country` string,
  `version_name` string,
  `vpn` string,
  `parallelversion` string,
  `segmentid` string,
  `user_dimen` double,
  `packagename` string,
  `packageversion` string,
  `installsource` string,
  `packagelabel` string,
  `isrunning` string,
  `payment_platform` string,
  `buy_status` string,
  `productid` string,
  `orderid` string,
  `#zone_offset` double)
PARTITIONED BY (
  `$part_event` string,
  `$part_date` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://ta2/user/hive/warehouse/ta.db/ta_event_10'
TBLPROPERTIES (
  'presto_query_id'='20200113_034929_06651_2dhiw',
  'presto_version'='316-ta',
  'transient_lastDdlTime'='1578887369')

athena show create table:

    CREATE EXTERNAL TABLE `ta_event_10`(
  `#user_id` bigint, 
  `#event_name` string, 
  `#event_time` timestamp, 
  `#app_id` string, 
  `#account_id` string, 
  `#distinct_id` string, 
  `#server_time` timestamp, 
  `#kafka_offset` bigint, 
  `#device_id` string, 
  `version_code` double, 
  `#province` string, 
  `#device_model` string, 
  `#city` string, 
  `channel` string, 
  `launchsource` string, 
  `flavor` string, 
  `#app_version` string, 
  `#os` string, 
  `#country_code` string, 
  `#ip` string, 
  `#os_version` string, 
  `#manufacturer` string, 
  `#country` string, 
  `version_name` string, 
  `vpn` string, 
  `parallelversion` string, 
  `segmentid` string, 
  `user_dimen` double, 
  `packagename` string, 
  `packageversion` string, 
  `installsource` string, 
  `packagelabel` string, 
  `isrunning` string, 
  `payment_platform` string, 
  `buy_status` string, 
  `productid` string, 
  `orderid` string, 
  `#zone_offset` double)
PARTITIONED BY ( 
  `$part_event` string, 
  `$part_date` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  's3://test-ta-export/ta_event_10'
TBLPROPERTIES (
  'transient_lastDdlTime'='1583763184')

"#event_time" field both are timestamp type.

I look up one orc file metadata by orc-metadata, them look like this:

enter image description here

First use hive and Athena.

0 Answers
Related