Questions tagged [hive-query]
82 questions
0
votes
1 answer
Hive Joins on String Performance
We are using hive 3.1.3 and we're running Queries on Tez engine (Hive on Tez) on Cloudera platform.
We are still in the beginning phase and we have a situation where we want to join several tables together using string columns.
for example
table…
speroo
- 31
- 4
0
votes
1 answer
Hive query to find conversion ratio
I am trying this query in Hive and it's not working.
select
(
(
select
count(*)
from
click_streaming
where
page_id= 'e7bc5fb2-1231-11eb-adc1-0242ac120002'
and is_page_view = 'Yes'
) / (
…
0
votes
0 answers
Complex dataType in Hive
Hi Is there any option to create hive table on below hdfs file.So lets say if I query with on customer then it should return all the columns for customer in a single row in json format.
Hdfs File Content:
{
"customer": {
"legalBusinessName":…
Sonu
- 77
- 11
0
votes
1 answer
Both left and right aliases encountered in JOIN '0' In Hive : Operator With Left Join in Hive
I am joining 2 tables and using <= in Join Condition also applying one filter condition so that it can only fetch remaining data from left table where filter condition is true.
I am using below query.
SELECT * FROM test.TABLE1 T1
left join…
Sonu
- 77
- 11
0
votes
1 answer
Replace Digits Using SubString and lpad/rpad In Hive
Hellow Everyone,
Someone Kinldy help me to Implement Below logic In Hive.I have 2 Table in hive(table1,table2). I need to replace 0's by 9 from One column of table1 with some conditions, and need to generate one output column then same 'output'…
Sonu
- 77
- 11
0
votes
1 answer
Padding Leading Zero's based on Column Length In Hive
I have one table in Hive.
Vari_Length Fixed_Length
12345 12345
1234 12345
123 12345
12 12345
1 12345
Here I am generating one new column by comparing the length of both the…
Rahul Patidar
- 47
- 1
- 3
- 9
0
votes
0 answers
Hive SQL error: Failed rule ‘identifier’ in the Select target
I wrote a hive sql query here:
SELECT
dt,
COUNT(CASE WHEN search_word like ‘%A%’ THEN id END) AS a,
COUNT(CASE WHEN search_word like ‘%B%’ THEN id END) AS b,
FROM database
GROUP BY dt
However, Hive returns an error :
Error while compiling…
0
votes
1 answer
Select max query returning all the rows in a table in Apache Hive
I am querying my data using this query
SELECT date_col,max(rate) FROM crypto group by date_col ;
I am expecting a single row but it is returning all the rows in the table. What is the mistake in this query?
sheharbano
- 211
- 2
- 13
0
votes
1 answer
I want to find out the second most populous country from the partitioned table
i am using the following query:
select country_name from (SELECT country_name, rank() OVER (ORDER BY TOTAL_pop desc)
as rk FROM (SELECT country_name, sum(col8) as TOTAL_pop FROM world_bank_data_partitioned GROUP BY country_name)
) SUM_EMP where rk…
Vasudha Jain
- 93
- 2
- 10
0
votes
1 answer
Hive nested SUM over partition - error Expression not in GROUP BY key
I'm trying to get cumulative sum in one query. It works fine in SQL, PRESTO etc. but not in HIVE, which throws error msg.
create table test(store varchar(10), item int, quantity int)
insert into test
select 'depot',101,1
union select…
d-_-b
- 761
- 2
- 11
- 26
0
votes
2 answers
Optimized way to check if record is present in table 1. If not then check table 2, else return default value
Asked in an interview:
I have 2 tables, one table has records like ID, Name, address. id(pk) is from 1 to 10000000.
Another table has records from 10000001 to 20000000.
I have to check if a particular ID is present in table 1 or table 2 and return…
Ekta J
- 1
0
votes
1 answer
Hive : Validate the quality of data filled in array by comparing with data definition record and find percentage of data filled, quality rank of data
I have two tables. Table t1 defines the metadata. ie, what are the attribute values an ideal transaction should contain. It also defines the order of importance of attributes by the order of records in the array. The first record is most important…
Mathan
- 1
- 1
0
votes
1 answer
use hive variable in HiveQL
I have a query which provides the max_processed_date (it provides a single value) and this date is used to fetch data from master_table.I am using the hivevars to do so, but it is directly substituting the query instead of first evaluating it.
set…
Dhiraj Tayade
- 407
- 3
- 10
- 22
0
votes
1 answer
ConvertJsonToSQL for Hive Insert query
I want to insert Json to hive database.
I try to transform Json to SQL using ConvertJsonToSQL Ni-Fi processor. How can I use PARTITION (....) part into my query??
Can I do this or I should use ReplaceText processor for making query?
Grigory Skvortsov
- 431
- 4
- 22
0
votes
2 answers
Hive SQL logic - case when statement
My query:
case
when upper(a.camp_name) like "%Event%" and (upper(a.camp_name) not like "%Event-WBR%" or upper(a.camp_name) not like "%Event-Webinar%") THEN "Field"
else "Demand"
end as Tactic
Desired Output:
Event-WBR = Demand
Event =…
RashItIs
- 87
- 1
- 1
- 10