Skip to content

Tag: hiveql

convert multiple date format into one format

I would like to know the answer I have this kind of problem, I have one column that consist 2 date format but in string format and then I would like to convert it to default datetime format, so it would be like this I’ve tried many ways but cant find the solution, Answer Consider below query query results

How Create a hive external table with parquet format

I am trying to create an external table in hive with the following query in HDFS. getting error Error while compiling statement: FAILED: ParseException line 11:2 missing EOF at ‘LOCATION’ near ‘)’ What is the best way to create a HIVE external table with data stored in parquet format? Answer I am able to create table after removing property TBLPROPERTIES(“Parquet.compression”=”SNAPPY”)

COUNT with CASE WHEN is showing the same result when using division

I have the following query which returns the sold products: I want to calculate the percentage of the sold products comparing to all products for product_category =7 : I get the result as 100, while I execute each query separately they don’t have the same result. Answer count() counts both 0 and 1s, it does not counts NULLs. Use ELSE

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 regexp_extract numeric value from a string

I have a table as: I am trying to get the numeric values from the table. The expected output is A -> 123 / B -> 124 etc I am trying to do using regexp_extract Any suggestions please? Answer If the delimiters are fixed – ‘; ‘ between key-value pairs and ‘=’ between key and value, you can use str_to_map

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