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 resul…
Tag: hiveql
how to include 0 results in count with group by in HIVEQL
I’m a newbie in HIVE. I want to include 0 rows in results I have one table like this is my query example result is but my desired result is How can I get a 0 in results? Answer You can do this but you need to remove where clause. You can also do using self join. EDIT – I
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? …
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 …
How can i add days to a Hive timestamp without loosing hours, minutes and seconds
I am using Hive 2.6.5 and when i want to add days to my timestamp, it doesn’t keep the hours, minutes and seconds. Exemple in addition to that it returns a wrong result as : I would like it to return the value 2021-01-17 09:34:21 Thank you Answer date_add truncates Unnecessary unix_timestamp+from_unixti…
What is difference between where and join in Hive SQL when joining two tables?
For example, What is difference between where and join in Hive SQL when joining two tables? Answer Join like this is a bad practice because in general, WHERE is being applied after join and transforming it to JOIN and pushing predicates is upon optimizer, to convert it to proper join and avoid CROSS join (joi…
Hive trunc date format issues
I am trying to convert GP to Hive migration logic but below statement giving the wrong output while execute query: output for GP : 2021-12-31 similar if we converted Hive query out put if Hive query : 2022-01-02 i could see the difference of the date. please help me. Thanks Answer You are subtracting interval…
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 wan…
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 v…
QUALIFY equivalent in HIVE / SPARK SQL
I am trying to convert a Teradata SQL query into HIVE/Spark SQL equivalent. Is there any substitute for qualify along with count Answer Got it 🙂