FAQ
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;


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 !

Search Discussions

  • Marcel Kornacker at Mar 5, 2013 at 12:25 am

    On Mon, Mar 4, 2013 at 4:22 PM, DK 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;


    It fails with following exception:

    Error: java.lang.RuntimeException:
    org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while
    You seem to be hitting some problem in Hive, so you should repost this
    question on the Hive user mailing list.

    Note that Impala currently does not support inserting into rcfile-backed tables.
    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 !
  • DK at Mar 5, 2013 at 1:47 am
    My system timezone is PST/PDT.
    I think this should be converted to the timestamps as I was able to use
    other date related function on these data.
    I havn't specified any timezone info in data so asuming it will be PST/PDT
    as well.
    Do you think from_utc_timestamp(timestamp, string timezone) will help but
    not sure if UTC will match to my local tz?

    Thanks,
    DK
    On Monday, March 4, 2013 5:39:23 PM UTC-8, Mark Grover wrote:

    DK,
    While I do agree that posting on Hive list is the best, I did notice
    that the date your trying to parse is "1969-12-31 19:00:00". Hive's
    timestamp type relies on string dates being able to converted to unix
    timestamps. From what I know about timestamps, this date is not a
    valid date in certain timezones (e.g. GMT). Any chance your dataset
    corresponds to one timezone while you are parsing it on another
    timezone? What's the system timezone on the nodes running Hive?

    Mark

    On Mon, Mar 4, 2013 at 5:13 PM, DK <dileepk...@gmail.com <javascript:>>
    wrote:
    Thanks I will repost this in Hive forum.
    I was using Hive to do the insert into rcfile backed tables.

    Thanks,
    DK

    On Monday, March 4, 2013 4:25:38 PM UTC-8, Marcel Kornacker wrote:
    On Mon, Mar 4, 2013 at 4:22 PM, DK 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;


    It fails with following exception:

    Error: java.lang.RuntimeException:
    org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error
    while
    You seem to be hitting some problem in Hive, so you should repost this
    question on the Hive user mailing list.

    Note that Impala currently does not support inserting into
    rcfile-backed tables.
    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 !
  • DK at Mar 5, 2013 at 1:49 am
    Just FYI I was able to use the date_dim original table in my queries.

    Thanks,
    DK
    On Monday, March 4, 2013 5:47:47 PM UTC-8, DK wrote:

    My system timezone is PST/PDT.
    I think this should be converted to the timestamps as I was able to use
    other date related function on these data.
    I havn't specified any timezone info in data so asuming it will be PST/PDT
    as well.
    Do you think from_utc_timestamp(timestamp, string timezone) will help but
    not sure if UTC will match to my local tz?

    Thanks,
    DK
    On Monday, March 4, 2013 5:39:23 PM UTC-8, Mark Grover wrote:

    DK,
    While I do agree that posting on Hive list is the best, I did notice
    that the date your trying to parse is "1969-12-31 19:00:00". Hive's
    timestamp type relies on string dates being able to converted to unix
    timestamps. From what I know about timestamps, this date is not a
    valid date in certain timezones (e.g. GMT). Any chance your dataset
    corresponds to one timezone while you are parsing it on another
    timezone? What's the system timezone on the nodes running Hive?

    Mark

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupimpala-user @
categorieshadoop
postedMar 5, '13 at 12:22a
activeMar 5, '13 at 1:49a
posts4
users2
websitecloudera.com
irc#hadoop

2 users in discussion

DK: 3 posts Marcel Kornacker: 1 post

People

Translate

site design / logo © 2021 Grokbase