Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive, mail # user - Hive insert into RCFILE issue with timestamp columns


Copy link to this message
-
Hive insert into RCFILE issue with timestamp columns
Dileep Kumar 2013-03-05, 01:37
Hi All,

I am using the schema in the Impala VM and trying to create a dynamic
partitioned table on date_dim.
New table is called date_dim_i and schema for that is defined as:
create table date_dim_i
(
    d_date_sk                 int,
    d_date_id                 string,
    d_date                    timestamp,
    d_month_seq               int,
    d_week_seq                int,
    d_quarter_seq             int,
    d_dow                     int,
    d_moy                     int,
    d_dom                     int,
    d_qoy                     int,
    d_fy_year                 int,
    d_fy_quarter_seq          int,
    d_fy_week_seq             int,
    d_day_name                string,
    d_quarter_name            string,
    d_holiday                 string,
    d_weekend                 string,
    d_following_holiday       string,
    d_first_dom               int,
    d_last_dom                int,
    d_same_day_ly             int,
    d_same_day_lq             int,
    d_current_day             string,
    d_current_week            string,
    d_current_month           string,
    d_current_quarter         string,
    d_current_year            string
)
PARTITIONED BY (d_year int)
stored as RCFILE;

Then I do insert overwrite as:
insert overwrite table date_dim_i
PARTITION (d_year)
select d_date_sk, d_date_id, d_date, d_month_seq, d_week_seq,
d_quarter_seq, d_dow, d_moy, d_dom, d_qoy, d_fy_year, d_fy_quarter_seq,
d_fy_week_seq, d_day_name, d_quarter_name, d_holiday, d_weekend,
d_following_holiday, d_first_dom, d_last_dom, d_same_day_ly, d_same_day_lq,
d_current_day, d_current_week, d_current_month, d_current_quarter,
d_current_year, d_year
from date_dim;

The date_dim table schema is as :
create external table date_dim
(
    d_date_sk                 int,
    d_date_id                 string,
    d_date                    timestamp,
    d_month_seq               int,
    d_week_seq                int,
    d_quarter_seq             int,
    d_year                    int,
    d_dow                     int,
    d_moy                     int,
    d_dom                     int,
    d_qoy                     int,
    d_fy_year                 int,
    d_fy_quarter_seq          int,
    d_fy_week_seq             int,
    d_day_name                string,
    d_quarter_name            string,
    d_holiday                 string,
    d_weekend                 string,
    d_following_holiday       string,
    d_first_dom               int,
    d_last_dom                int,
    d_same_day_ly             int,
    d_same_day_lq             int,
    d_current_day             string,
    d_current_week            string,
    d_current_month           string,
    d_current_quarter         string,
    d_current_year            string
)
row format delimited fields terminated by '|'
location '/hive/tpcds/date_dim';

It fails with following exception:

Error: java.lang.RuntimeException:
org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while
processing row
{"d_date_sk":2415022,"d_date_id":"AAAAAAAAOKJNECAA","d_date":"1969-12-31
19:00:00","d_month_seq":0,"d_week_seq":1,"d_quarter_seq":1,"d_year":1900,"d_dow":1,"d_moy":1,"d_dom":2,"d_qoy":1,"d_fy_year":1900,"d_fy_quarter_seq":1,"d_fy_week_seq":1,"d_day_name":"Monday","d_quarter_name":"1900Q1","d_holiday":"N","d_weekend":"N","d_following_holiday":"Y","d_first_dom":2415021,"d_last_dom":2415020,"d_same_day_ly":2414657,"d_same_day_lq":2414930,"d_current_day":"N","d_current_week":"N","d_current_month":"N","d_current_quarter":"N","d_current_year":"N"}
****

        at
org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:161)****

        at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)****

        at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:399)**
**

        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:334)****

        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:152)****

        at java.security.AccessController.doPrivileged(Native Method)****

        at javax.security.auth.Subject.doAs(Subject.java:396)****

        at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1332)
****

        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:147)****

Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime
Error while processing row
{"d_date_sk":2415022,"d_date_id":"AAAAAAAAOKJNECAA","d_date":"1969-12-31
19:00:00","d_month_seq":0,"d_week_seq":1,"d_quarter_seq":1,"d_year":1900,"d_dow":1,"d_moy":1,"d_dom":2,"d_qoy":1,"d_fy_year":1900,"d_fy_quarter_seq":1,"d_fy_week_seq":1,"d_day_name":"Monday","d_quarter_name":"1900Q1","d_holiday":"N","d_weekend":"N","d_following_holiday":"Y","d_first_dom":2415021,"d_last_dom":2415020,"d_same_day_ly":2414657,"d_same_day_lq":2414930,"d_current_day":"N","d_current_week":"N","d_current_month":"N","d_current_quarter":"N","d_current_year":"N"}
****

        at
org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:548)****

        at
org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:143)****

        ... 8 more****

Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Error
evaluating d_date****

        at
org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:80)
****

        at
org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471)****

        at
org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762)****

        at
org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:83)
****

        at
org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471)****

        at
org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762)****

        at
org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:529)****

        ... 9 more****

Caused by: java.lang.IllegalArgumentException: Timestamp format must be
yyyy-mm-dd hh:mm:ss[.fffffffff]****

        at jav
+
Mark Grover 2013-03-05, 01:51
+
Dileep Kumar 2013-03-05, 02:00
+
Dileep Kumar 2013-03-05, 22:56
+
Prasad Mujumdar 2013-03-06, 08:28
+
Sékine Coulibaly 2013-03-06, 09:58
+
Mark Grover 2013-03-05, 23:28