I have two tables Table A and Table B as listed below. What is the SQL query to get the below output? Answer This looks like a cross join, to generate all possible combinations of rows between the two tables. Assuming a structure like tablea(col), tableb(col): If you want to concatenate the results in a singe…
Tag: hiveql
Hive Query to insert a value conditionally
I have a Table1 containing some blacklisted names. Now suppose I receive a record “def”. The hive query should check if “def” is present in Table1 or not. If not the name_status should be set to blacklisted otherwise null. The name “def” will be inserted in both cases. The …
Hive query conditional statement in same select query
Is there a way to get in one single hive query to do a if-else kind of setup. In the my data below I want to ensure that if Model is empty or having ‘-‘ I populate the Final column with Device else it should be populated with Model Can someone please help here. Answer Read about CASE operator synt…
how to select all the values in hive with distinct of 2 columns in hive
I have a hive table that looks like this (total 460 columns) colA colB ……. ce_id filename ……… dt v j 4 gg 40 v j 5 gg …
Percent change with grouping variables
I have four grouping variables Month, State,County, City. In addition I have the metric column sales which can be null I would like to calculate the percent change of sales per month for each City. My solution would have the same grouping but with the sales column replaced by percent change for each month in …
Get List of Last 15 Days Date in SQL
Could SQL get list of date of last 15 days date in a single query? We can get today date with select current_date() We also can get last 15 days date with select date_add(current_date(), -15) But how to show the list of last 15 days date? For example the output is Answer In Hive or Spark-SQL: Result: See also…
HIVE SQL: Select rows whose values contain string in a column
I want to select rows whose values contain a string in a column. For example, I want to select all rows whose values contain a string ‘123’ in the column ‘app’. table: result: I am not familiar with SQL query. Could anyone help me? . Thanks in advances. Answer In a number of ways: like…
Hive throwing ParseException while exporting a csv
Running this code, I am trying to export a csv but I get the following error: ParseException line 2:3 cannot recognize input near ‘MIN’ ‘(‘ ‘HIGH’ in expression specification Answer Try to add table alias (d) and use select d.* instead of select *:
Why isn’t FIRST_VALUE and LAST_VALUE an aggregation function in SQL?
Is there any special reason that SQL only implements FIRST_VALUE and LAST_VALUE as a windowing function instead of an aggregation function? I find it quite common to encounter problems such as “find the item with highest price in each category”. While other languages (such as python) provide MIN/M…
Hive nested query left join
I’am new to hive and i want to implement the following query It works fine untill the last left join. Nested queries are obviously not authorized in hive. How can i approach this. Answer Use sub-query instead of joined table: Or better move last join condition inside the WHERE clause in the subquery: