Skip to content

Tag: hive

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

convert a single row into 2 rows on impala/hive

I have a huge table with millions of rows/IDs in the below format. I need to convert this into the below format so that the values are in 2 rows as shown below. Can you please help me with an impala/hive query to help with this? Thanks a lot. Answer I think a way would be this one:

How to retain the first record while using Window function LAG()?

I have a data which looks like – Actual Table – VIN Mode Status Start End ABC123456789 Mode 1 Waiting for Auth 01/01/2010 00:00:00 05/05/2014 14:54:54 ABC123456789 Mode 1 Waiting for URL 05/05/2014 14:54:54 05/13/2014 19:09:51 ABC123456789 Mode 1 Waiting for User 05/13/2014 19:09:51 11/13/2014 22:26:32 ABC123456789 Mode 1 Authorized 11/13/2014 22:26:32 11/13/2014 22:31:00 ABC123456789 Mode 1 Authorized 11/13/2014 22:31:00

find the consecutive values in impala

I have a data set below with ID, Date and Value. I want to flag the ID where three consecutive days has value 0. id date value 1 8/10/2021 1 1 8/11/2021 0 1 8/12/2021 0 1 8/13/2021 0 1 8/14/2021 5 2 8/10/2021 2 2 8/11/2021 3 2 8/12/2021 0 2 8/13/2021 0 2 8/14/2021 6 3 8/10/2021 3

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