|
|
-
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
-
Re: Hive insert into RCFILE issue with timestamp columns
Mark Grover 2013-03-05, 01:51
Hi Dilip,
Are you able to run this query successfully?
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
On Mon, Mar 4, 2013 at 5:37 PM, Dileep Kumar <[EMAIL PROTECTED]> wrote:
> 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"}
-
Re: Hive insert into RCFILE issue with timestamp columns
Dileep Kumar 2013-03-05, 02:00
No. Here are the errors: Task with the most failures(4): ----- Task ID: task_1361599885844_0013_m_000000 URL: http://localhost.localdomain:50030/taskdetails.jsp?jobid=job_1361599885844_0013&tipid=task_1361599885844_0013_m_000000----- Diagnostic Messages for this Task: 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 java.sql.Timestamp.valueOf(Timestamp.java:185) at org.apache.hadoop.hive.serde2.lazy.LazyTimestamp.init(LazyTimestamp.java:74) at org.apache.hadoop.hive.serde2.lazy.LazyStruct.uncheckedGetField(LazyStruct.java:219) at org.apache.hadoop.hive.serde2.lazy.LazyStruct.getField(LazyStruct.java:192) at org.apache.hadoop.hive.serde2.lazy.objectinspector.LazySimpleStructObjectInspector.getStructFieldData(LazySimpleStructObjectInspector.java:188) at org.apache.hadoop.hive.ql.exec.ExprNodeColumnEvaluator.evaluate(ExprNodeColumnEvaluator.java:98) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:76) ... 15 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job 0: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL Total MapReduce CPU Time Spent: 0 msec On Mon, Mar 4, 2013 at 5:51 PM, Mark Grover <[EMAIL PROTECTED]>wrote:
-
Re: Hive insert into RCFILE issue with timestamp columns
Dileep Kumar 2013-03-05, 22:56
--hdfs dfs -mkdir /hive/tpcds/date_ts create external table date_ts ( d_date timestamp ) row format delimited fields terminated by '|' location '/hive/tpcds/date_ts'; [cloudera@localhost tmp-work]$ hive -e "select * from date_ts" Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j.properties Hive history file=/tmp/cloudera/hive_job_log_cloudera_201303052251_950655265.txt OK Failed with exception java.io.IOException:java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] Time taken: 3.556 seconds [cloudera@localhost tmp-work]$ hdfs dfs -cat /hive/tpcds/date_ts/* 2415022|AAAAAAAAOKJNECAA|1900-01-02 02:00:21.000000000|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N| On Mon, Mar 4, 2013 at 6:00 PM, Dileep Kumar <[EMAIL PROTECTED]>wrote: > No. > Here are the errors: > Task with the most failures(4): > ----- > Task ID: > task_1361599885844_0013_m_000000 > > URL: > > http://localhost.localdomain:50030/taskdetails.jsp?jobid=job_1361599885844_0013&tipid=task_1361599885844_0013_m_000000> ----- > Diagnostic Messages for this Task: > 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)
-
Re: Hive insert into RCFILE issue with timestamp columns
Mark Grover 2013-03-05, 23:28
Dileep, Can you use a more contemporary timestamp? Something after Jan 1, 1970 GMT, say Jan 1st, 2013? Let us know what you see. On Tue, Mar 5, 2013 at 2:56 PM, Dileep Kumar <[EMAIL PROTECTED]> wrote: > --hdfs dfs -mkdir /hive/tpcds/date_ts > > create external table date_ts > ( > d_date timestamp > ) > row format delimited fields terminated by '|' > location '/hive/tpcds/date_ts'; > > [cloudera@localhost tmp-work]$ hive -e "select * from date_ts" > Logging initialized using configuration in > file:/etc/hive/conf.dist/hive-log4j.properties > Hive history > file=/tmp/cloudera/hive_job_log_cloudera_201303052251_950655265.txt > OK > Failed with exception > java.io.IOException:java.lang.IllegalArgumentException: Timestamp format > must be yyyy-mm-dd hh:mm:ss[.fffffffff] > Time taken: 3.556 seconds > [cloudera@localhost tmp-work]$ hdfs dfs -cat /hive/tpcds/date_ts/* > 2415022|AAAAAAAAOKJNECAA|1900-01-02 > 02:00:21.000000000|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N| > > > > > > On Mon, Mar 4, 2013 at 6:00 PM, Dileep Kumar <[EMAIL PROTECTED]> > wrote: >> >> No. >> Here are the errors: >> Task with the most failures(4): >> ----- >> Task ID: >> task_1361599885844_0013_m_000000 >> >> URL: >> >> http://localhost.localdomain:50030/taskdetails.jsp?jobid=job_1361599885844_0013&tipid=task_1361599885844_0013_m_000000>> ----- >> Diagnostic Messages for this Task: >> 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)
-
Re: Hive insert into RCFILE issue with timestamp columns
Prasad Mujumdar 2013-03-06, 08:28
Dilip, Looks like you are using the data from the original schema for this new table that has single timestamp column. When I tried with just the timestamp from your data, the query runs fine. I guess the original issue you hit on the data that didn't have fraction part (1969-12-31 19:00:00, no .fffff). thanks Prasad On Tue, Mar 5, 2013 at 2:56 PM, Dileep Kumar <[EMAIL PROTECTED]>wrote: > --hdfs dfs -mkdir /hive/tpcds/date_ts > > create external table date_ts > ( > d_date timestamp > ) > row format delimited fields terminated by '|' > location '/hive/tpcds/date_ts'; > > [cloudera@localhost tmp-work]$ hive -e "select * from date_ts" > Logging initialized using configuration in > file:/etc/hive/conf.dist/hive-log4j.properties > Hive history > file=/tmp/cloudera/hive_job_log_cloudera_201303052251_950655265.txt > OK > Failed with exception > java.io.IOException:java.lang.IllegalArgumentException: Timestamp format > must be yyyy-mm-dd hh:mm:ss[.fffffffff] > Time taken: 3.556 seconds > [cloudera@localhost tmp-work]$ hdfs dfs -cat /hive/tpcds/date_ts/* > 2415022|AAAAAAAAOKJNECAA|1900-01-02 > 02:00:21.000000000|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N| > > > > > > On Mon, Mar 4, 2013 at 6:00 PM, Dileep Kumar <[EMAIL PROTECTED]>wrote: > >> No. >> Here are the errors: >> Task with the most failures(4): >> ----- >> Task ID: >> task_1361599885844_0013_m_000000 >> >> URL: >> >> http://localhost.localdomain:50030/taskdetails.jsp?jobid=job_1361599885844_0013&tipid=task_1361599885844_0013_m_000000>> ----- >> Diagnostic Messages for this Task: >> 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)
-
Re: Hive insert into RCFILE issue with timestamp columns
Sékine Coulibaly 2013-03-06, 09:58
Prasad, Isn't the fractional part of the TIMESTAMP type supposed to be optional, as per the error message : Failed with exception java.io.IOException:java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] Shall we understand 9 digits for fractional part are mandatory ? Thanks 2013/3/6 Prasad Mujumdar <[EMAIL PROTECTED]> > Dilip, > > Looks like you are using the data from the original schema for this new > table that has single timestamp column. When I tried with just the > timestamp from your data, the query runs fine. I guess the original issue > you hit on the data that didn't have fraction part (1969-12-31 19:00:00, no > .fffff). > > thanks > Prasad > > > On Tue, Mar 5, 2013 at 2:56 PM, Dileep Kumar <[EMAIL PROTECTED]>wrote: > >> --hdfs dfs -mkdir /hive/tpcds/date_ts >> >> create external table date_ts >> ( >> d_date timestamp >> ) >> row format delimited fields terminated by '|' >> location '/hive/tpcds/date_ts'; >> >> [cloudera@localhost tmp-work]$ hive -e "select * from date_ts" >> Logging initialized using configuration in >> file:/etc/hive/conf.dist/hive-log4j.properties >> Hive history >> file=/tmp/cloudera/hive_job_log_cloudera_201303052251_950655265.txt >> OK >> Failed with exception >> java.io.IOException:java.lang.IllegalArgumentException: Timestamp format >> must be yyyy-mm-dd hh:mm:ss[.fffffffff] >> Time taken: 3.556 seconds >> [cloudera@localhost tmp-work]$ hdfs dfs -cat /hive/tpcds/date_ts/* >> 2415022|AAAAAAAAOKJNECAA|1900-01-02 >> 02:00:21.000000000|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N| >> >> >> >> >> >> On Mon, Mar 4, 2013 at 6:00 PM, Dileep Kumar <[EMAIL PROTECTED]>wrote: >> >>> No. >>> Here are the errors: >>> Task with the most failures(4): >>> ----- >>> Task ID: >>> task_1361599885844_0013_m_000000 >>> >>> URL: >>> >>> http://localhost.localdomain:50030/taskdetails.jsp?jobid=job_1361599885844_0013&tipid=task_1361599885844_0013_m_000000>>> ----- >>> Diagnostic Messages for this Task: >>> 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"}
|
|
