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 <pras...@cloudera.com> > 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 <dileepkumar...@gmail.com>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 <dileepkumar...@gmail.com>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) >>> 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 <grover.markgro...@gmail.com >>> > wrote: >>> >>>> 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 <dileepkumar...@gmail.com> >>>> 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"} >>>> > >>>> > 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) >>>> > >>>> > >>>> > Please suggest what could be wrong here as datatypes are exact same >>>> in both >>>> > cases. >>>> > >>>> > >>>> > Thanks ! >>>> >>> >>> >> >
