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”)
Tag: impala
How to solve impala insert with select query error?
i want to insert a row to service_log table with max+1 id value. But this gives me the following error ; How can i solve this problem? Answer This should work with most SQL dialects:
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
How to make dummy variables for a same data id in IMPALA SQL
I have a dataset in impala SQL like this: And I want to look like this: I have tried using CASE WHEN but results in duplicates for those ids where has 2 values different. Can someone help me with this issue. Thenk you much in advance. Answer
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
SSRS Converting MultiValue Text Paramater into a String in SQL Syntax
I am being forced to use an ODBC source for SSRS report builder, source is Impala SQL, which doesn’t support the use of parameters. So I had to use a string builder function to get parameters into my script. This is working well, however the ask was to allow for multiple values in the text parameter. I am not sure