hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Morgan Reece <winter2...@gmail.com>
Subject Re: Not able to use the timestamp columns
Date Wed, 06 Mar 2013 00:00:28 GMT
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 <dileepkumar.dk@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|
>
>

Mime
View raw message