HIve Error: parser exception cannot recognize input

[#1]

select cast(10000 as int),
           case data_type
            when 'TIMESTAMP' then
    		  concat('     ,cast((cast(sys_extract_utc(update_time) AS DATE) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 86400 AS INT) last_date_time_ts')
            else
              concat('     ,#unknown data_type : ', data_type)
           end
      from (select row_number() over() ln, table_name, column_name, data_type, column_id 
              from test.all_tab_columns
             where date_key in (select max(date_key) from test.all_tab_columns)
               and table_name = 'test_table'
                      ) data

Tried to extract the the last updated time using below query.  hive is throwing exception

Error: Error while compiling statement: FAILED: ParseException line 4:7 cannot recognize input near ''cast((cast(sys_extract_utc(update_time) AS DATE) - TO_DATE('' '01' '-' in expression specification (state=42000,code=40000)

[#2 · Accepted solution]

@Adithya Sajjanam

sys_extract_utc is not a valid Hive function. You are translating your Oracle query as is and that's why getting this error. You can refer to the Hive documentation for reference to Date functions in hive and translate your query from Oracle syntax to Hive QL accordingly.