from_unixtime thinks data is string where data is int/bigint in hadoop hive

Question

My code is as below..

SELECT
        to_date(from_unixtime(time_first_touch)) AS sDate
    FROM (
SELECT
            MIN(GET_JSON_OBJECT(swanviraw.textcol,'$.ev_time')) as time_first_touch,
            COUNT(*) as number_of_events
        FROM swanviraw
    ) v

Its throwing an error- Error while compiling statement: FAILED: SemanticException [Error 10014]: Line 2:10 Wrong arguments 'time_first_touch': No matching method for class org.apache.hadoop.hive.ql.udf.UDFFromUnixTime with (string). Possible choices: FUNC(bigint) FUNC(bigint, string) FUNC(int) FUNC(int, string) [ERROR_STATUS]

Now, the point is the following query is working fine.. the ev_time has int/bigint values as MIN works perfectly in the following..

 SELECT
                    MIN(GET_JSON_OBJECT(swanviraw.textcol,'$.ev_time')) as time_first_touch,
                    COUNT(*) as number_of_events
                FROM swanviraw

Any help is sincerely appreciated..

Thanks

Answer 1

As GET_JSON_OBJECT returns json string, and as error indicates that from_unixtime expect int or bigint, you need to convert time_first_touch to bigint:

SELECT
        to_date(from_unixtime(time_first_touch)) AS sDate
    FROM (
SELECT
            MIN(cast(GET_JSON_OBJECT(swanviraw.textcol,'$.ev_time') as bigint)) as time_first_touch,
            COUNT(*) as number_of_events
        FROM swanviraw
    ) as v

OR

SELECT
        to_date(from_unixtime(time_first_touch)) AS sDate
    FROM (
SELECT
            MIN(unix_timestamp(GET_JSON_OBJECT(swanviraw.textcol,'$.ev_time'))) as time_first_touch,
            COUNT(*) as number_of_events
        FROM swanviraw
    ) as v