hive record inserted but then get a error

Question

I create a table in hive:

CREATE TABLE `test3`.`shop_dim`  ( 
    `shop_id`               bigint, 
    `shop_name`             string, 
    `shop_company_id`       bigint, 
    `shop_url1`             string, 
    `shop_url2`             string, 
    `sid`                   string, 
    `shop_open_duration`    string, 
    `date_modified`         timestamp)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' WITH SERDEPROPERTIES ("path"="hdfs://myhdfs/warehouse/tablespace/managed/hive/test3.db/shop_dim")
STORED AS PARQUET
TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"date_modified\":\"true\",\"shop_company_id\":\"true\",\"shop_id\":\"true\",\"shop_name\":\"true\",\"shop_open_duration\":\"true\",\"shop_url1\":\"true\",\"shop_url2\":\"true\",\"sid\":\"true\"}}', 'bucketing_version'='2', 'numFiles'='12', 'numRows'='12', 'rawDataSize'='96', 'spark.sql.create.version'='2.3.0', 'spark.sql.sources.provider'='parquet', 'spark.sql.sources.schema.numParts'='1', 'spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fields\":[{\"name\":\"Shop_id\",\"type\":\"long\",\"nullable\":true,\"metadata\":{}},{\"name\":\"Shop_name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"Shop_company_id\",\"type\":\"long\",\"nullable\":true,\"metadata\":{}},{\"name\":\"Shop_url1\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"Shop_url2\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"sid\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"Shop_open_duration\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"Date_modified\",\"type\":\"timestamp\",\"nullable\":true,\"metadata\":{}}]}', 'totalSize'='17168')
GO

then I insert a record use below sql:

insert into test3.shop_dim values(11,'aaa',22,'11113','2222','sid','opend',unix_timestamp())

I can see the record is inserted,but waited for a long time,there is error:

>[Error] Script lines: 1-2 --------------------------
 Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask 

 [Executed: 2018-10-24 下午12:00:03] [Execution: 0ms] 

I use aqua studio as a tool.Why this error occur?

Answer 1

This issue can happen if the values being inserted are not matching to the expected type. In your case "date_modified" column is of timestamp type, but unix_timestamp() will return bigint (current Unix timestamp in seconds).

If you execute the query

select unix_timestamp();

Output would be like : 1558547043

Instead, you need to use current_timestamp.

select current_timestamp;

Output would be like : 2019-05-22 17:50:18.803

You can refer Hive manual for in-built date functions at https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

Answer 2

Below given hive setting can help resolve org.apache.hadoop.hive.ql.exec.StatsTask (state=08S01,code=1)

set hive.stats.column.autogather=false; or set hive.stats.autogather=false set hive.optimize.sort.dynamic.partition=true;