Skip to content

Tag: impala

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”)

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 add a new column in a view?

I have this view: And I wanted to add a new column with the following replication code: but he returns the error: I thought about deleting this table that already exists, but I don’t know if it would be the best way to do it. How can I do it? Answer you can use alter view. Its better because it

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

Impala convert string to timestamp always returns to NULL

I want to convert dd/mm/yyyy hh:mm:ss string to date format. this is not converting the string to timeformat and always returns me NULL. Please guide where I am going wrong. Answer You need to use correct format. The pattern string supports the following subset of Java SimpleDateFormat. A date string including all fields could be ‘yyyy-MM-dd HH:mm:ss.SSSSSS’, ‘dd/MM/yyyy HH:mm:ss.SSSSSS’, ‘MMM

SQL query – not null

I have sql query: I got this table id position10 position20 position30 1 2021-05-02 2021-05-02 2021-05-3 2 2021-05-15 2021-05-16 2021-05-18 3 2021-07-08 NULL NULL And I want lines where NULL values are not picked up. A try this: But it still chooses to me where the NULL value appears somewhere. Answer You need to change OR to AND in WHERE

How to do group_concat with Integer columns?

This query displays performance of 3 endpoints combined (decided using some priority) over a series of days in the period of last 2 weeks. Just for display in the dashboard, I need to combine the values of the dataset using something like group_concat. This seems to be working fine for String type column. However when I want to combine the