Search code examples
hiveimpala

How can I split a '%Y%m%d%H%i%s' column into two columns: one that contains %Y%m%d% and the that contains H%i%s in hive?

I have a column displaying '%Y%m%d%H%i%s' (e.g. 20150125145900) How can I convert it into two columns, one "ymd" and another "his" (e.g 2015/01/25 and 14:59:00)?

Solution

  • Hive

    select  ts[0] as year
       ,ts[1] as hour

from   (select  split(from_unixtime(unix_timestamp('20150125145900','yyyyMMddHHmmss')
                    ,'yyyy-MM-dd HH:mm:ss'),' ') as ts
        ) t

    +------------+----------+
|    year    |   hour   |
+------------+----------+
| 2015-01-25 | 14:59:00 |
+------------+----------+

    Impala

    select  split_part(ts,' ',1) as year
       ,split_part(ts,' ',2) as hour

from   (select  from_unixtime(unix_timestamp('20150125145900','yyyyMMddHHmmss')
                    ,'yyyy-MM-dd HH:mm:ss') as ts
        ) t
;

    +------------+----------+
| year       | hour     |
+------------+----------+
| 2015-01-25 | 14:59:00 |
+------------+----------+