Skip to content

Tag: hiveql

LEFT JOIN by closer value condition

I have this query In the second LEFT JOIN, I would like to change the second condition pob.year = proc.anno_eleccion so that it does not only search for the exact year when joining. Instead, I would like to get the closer year stored in my pob table. For example, the first year stored in pob is 2003, so I want

Hive Union of two queries is giving compile error

I am not sure what is wrong with following hive query. However, it’s throwing error. I am able to run this queries individually. However, facing issues while running with union Error Answer Hive versions prior to 1.2.0 only support UNION ALL Workaround: Use UNION ALL instead of UNION, wrap all into subquery and apply distinct UNION is the same as

Want to convert timestamp to date format in hive

want to convert this number ‘20210412070422’ to date format ‘2021-04-12’ in hive I am trying but this returns null value from_unixtime(unix_timestamp(eap_as_of_dt, ‘MM/dd/yyyy’)) Answer The best methoid is to do without unix_timestamp/from_unixtime if possible and in your case it is possible. date() can be removed, string in yyyy-MM-dd format is compatible with date type: Result: Another efficient method using regexp_replace: If

Hive SQL cast string as timestamp without losing the milliseconds

I have string data in the form 2020-10-21 12:49:27.090 I want to cast it as a timestamp. When I do this: select cast(column_name as timestamp) as column_name from table_name all of the milliseconds are dropped, like this: 2020-10-21 12:49:27 I also tried this: select cast(date_format(column_name,’yyyy-MM-dd HH:mm:ss.SSS’) as timestamp) as column_name from table_name and the same problem persists, it drops the

Filtering records not containing numbers

I have a table that has numbers in string format. Ideally the table should contain 10 digit number in string format, but it has many junk values. I wanted to filter out the records that are not ideal …