Skip to content
Advertisement

Tag: hiveql

SQL query to get the output

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 column:

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 problem I am facing is that in hive we cannot use

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 calendar year 2019. Any help

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 this

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: rlike: instr: locate: Invent your

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/MAX functions with keywords such that is possible, In SQL the only way

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:

Advertisement