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:
First use hive and Athena.
