I have 5 tables and I want to find the common values between them in one column. The column name differs in two tables(account_number, account,account_id). select * from db.table1 as a INNER JOIN db….
Tag: impala
Selecting most recent rows in a SQL query
I want to join two tables, selecting the most recent rows for an ID value present in table 1. i.e. For each ID value in table 1, only return the most recently added row for an ID value. For example, table 1 looks something like this: So if the same ID value is found twice in this table, only return
SQL query – fill missing value
I have a table: id type place 1 X1 10 2 X2 10 3 X3 10 1 X1 50 2 X2 50 3 X3 50 1 null 70 2 null 70 3 null 70 I have three parts …
Count distinct with HUE/IMPALA
I have a table in HUE like : I need to count the distinct number of ProductID’s for each ID. Something like this : I’ve tried : What I really need to do is a count(distinct) inside the analytical function. HUE doesn’t let me do this. Is there another way I can count distinct for window of rows? Answer Your
Create external table from csv on HDFS , all values come with quotes
I have a csv file on HDFS and I am trying to create an impala table , the situation is it created the table and values with all the ” CREATE external TABLE abc.def ( name STRING, title STRING, last …
AnalysisException: subqueries are not supported in the select list
I get this error code shown in title when using this following query. I’m trying query two tables to find total patients with hearing issues and the total of those patients with hearing issues who have undergone some sort of scan (MR,SC,CT). Any help would be appreciated. Answer I tried, pls refer to below SQL – this will work in
Convert date to ISO week date
How to convert dates to ISO week date in Impala SQL? For example 2019-12-30 in the ISO week date calendar would be written as 2020-W01-1 or 2020W011 ANSWER: Marked Gordon Linoff answer as correct, as it solves the essential part of the question, the deducing of the year part of ISO week date. For the week part of the ISO
Impala: Split single row into multiple rows based on Date and time
I want to split a single row into multiple rows based on time. Expected output is below: Day start from 00:00 AM to next day 00:00 AM. When EndDate time is greater than 00:00 AM (midnight) then split this date in two rows. First row end date is 30/03/2020 11:59:00 and next row start 31/03/2020 00:00:00. Please help me to
AnalysisException: No matching function with signature: rtrim(VARCHAR(40), STRING)
I have the value of 58.3308% in the table(include % character), I need output of 0.583308, and the SQL query as below works fine in SQL console But I get this error in my python code, it’s a hive/impala problem, right? AnalysisException: No matching function with signature: rtrim(VARCHAR(40), STRING) Thanks so much for any advice Answer As per Hive manual,
Impala SQL LEFT ANTI JOIN
Goal is to find the empid’s for a given timerange that are present in LEFT table but not in RIGHT table. I have the following two Impala queries which I ran and got different results? QUERY 1: select …