Not able to use the timestamp columns

[Dileep Kumar <di...@gmail.com>]

Hi All,

I am looking for some help in using timestamp column and not sure why I am
getting this error:
Here are how I created the tables and how I am querying it
--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';
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|

Re: Not able to use the timestamp columns

[Morgan Reece <wi...@gmail.com>]

It looks like your row is of this format:

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|

Where your timestamp is in the third field; however, your table only has a
single column.  Hive is reading your fields from left to right, so, you
don't seem to be accessing the correct field.  Further, since this field
isn't in the timestamp format, it's throwing this exception: Timestamp
format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

One quick fix might be to create your table like this:

create external table date_ts
(
    field0                      string
    field1                      string
    d_date                    timestamp
)
row format delimited fields terminated by '|'
location '/hive/tpcds/date_ts';

Then run your query like this:

hive -e "select d_date from date_ts"

Hope this helps!  :)



On Tue, Mar 5, 2013 at 4:58 PM, Dileep Kumar <di...@gmail.com>wrote:

> Hi All,
>
> I am looking for some help in using timestamp column and not sure why I am
> getting this error:
> Here are how I created the tables and how I am querying it
> --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';
> 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|
>
>