Questions tagged [hive]
Questions about Hive and HiveQL.
34
questions
1
vote
1answer
33 views
Getting the row before a row with a certain value in SQL
I have a table like below where user actions are stored with a timestamp. My goal is to identify the action that happened before a specific action (named reference_action) and count the number of ...
0
votes
0answers
5 views
Hive nested SUM over partition - error
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
...
1
vote
1answer
14 views
How to check total allotted space inside a HDFS 'group'
Our DBA has created a schema for our team in HDFS/HIVE. Not sure if 'schema' is the right word, they call it a 'group'.
Anyway, we can only write to the data lake inside this schema, whether it is ...
0
votes
0answers
5 views
Execute special processing on calling table in hive
I want to set up so that when someone calls my table, it first does some processing to the table, and then later, restores the table when it is no longer being used. Is there a way to do this? I think ...
0
votes
0answers
19 views
Field with Top Ranking Field Name
Let's imagine a table structured like this:
Bucket
Red
Blue
Green
First
1
3
4
Second
6
5
2
What I'm trying to achieve is based on the values within each bucket, I'd like to generate another set of ...
0
votes
0answers
67 views
Is Temporary table supported in Hive
I run queries in Hive...I wanted to create a temporary tables to save intermediate results of a complex query. Here is my query:
CREATE TEMPORARY TABLE test AS
SELECT * FROM my_table
LIMIT 100
The ...
0
votes
1answer
32 views
I am trying to write a join using temporary table in hive
I am trying to write a join using subquery/ temporary table in hive but it doesn't seem to be working:
create temporary table q as select server, max(time) as time from ghs group by header.server;
...
0
votes
1answer
77 views
Creating external tables from SQL Server using JDBC Drivers
I am trying to query Hive tables AND Kylin tables from SQL Server 2019. I was doing some research and found that MSSQL provide PolyBase as a form of interface to Hadoop storage. However, the examples ...
0
votes
0answers
10 views
Bit And Operation in Decimal Column in Impala
Consider in impala, I have a decimal column, is there a way to convert to binary using bin function
I can see clearly that bin is accepting only big int argument not the decimal
So, if there is a ...
0
votes
0answers
21 views
Has using overwrite saved my from inserting duplicate data?
I set up a pipeline to insert data daily. The query is a simple grouped count:
CREATE TABLE IF NOT EXISTS schema1.import_table AS
SELECT
p.yyyy_mm_dd,
m.feature,
p.xml_id,
count(...
0
votes
0answers
55 views
Can Hive selectively reject bad JSON?
We are loading big files full of JSON, one JSON object per line, each LF-delmited line being loaded as a row.
If any row fails to match the JSON structure, the entire file is rejected. For example:
...
0
votes
1answer
2k views
Testing a Hive array for IS NULL says not null
I have a table containing an array, and I want to check if it is empty or NULL. It appears that I cannot check the NULL directly! Can anyone shed light on why the NULL check isn't working?
create ...
0
votes
1answer
131 views
Query fails sometimes on casting error
I have a query that can run on the same data set, and sometimes it fails and sometimes it succeeds
The query is generated by hive metadata service, and I can't modify it.
This is a simplified version ...
1
vote
0answers
8 views
Defining external table on JSON with an @ sign in an element
I need to define a Hive external table onto a JSON file that has @ signs in its elements, e.g.
{ "data": { "@type": "person", "name": "Phil", "job": "Programmer" } }
This works:
create external ...
0
votes
1answer
3k views
HIVE SQL [Error 10025]: Expression not in GROUP BY key
Here is my SQL statement:
my_table includes 10+ columns (e.g, day, ip_address, user, request, etc), including strings and numbers. I want to GROUP by & HAVING based on column 'ip_address', if ...
0
votes
1answer
40 views
SQL filter only if each unique value has more than N records
Here is my sample SQL statement:
SELECT DAY,
name,
value
FROM my_table
WHERE DAY = '${date}'
GROUP BY DAY
name,
value
ORDER BY name ASC
For example, 3 unique names in ...
0
votes
1answer
52 views
Compare running / cumulative total with static target
I have some daily data and monthly data.
I want to compare these two datasets, with Hive and perform a payout when the sum of actual for a month passes that respective months target. In the above ...
1
vote
0answers
1k views
HIVE + understanding the hive-metastore logs
we have HDP cluster version - 2.6.4 , and we are runs spark streaming app
and we are uses presto cluster in order to run Hive queries
when we look on the hivemetastore logs ( under /var/log/hive ) ,...
0
votes
2answers
513 views
Omit table name and dot in SELECT query
When I perform this query:
SELECT `tablename`.* FROM `something`.`tablename`
I get a table with column names that contain dots:
tablename.c1 | tablename.c2 | tablename.c3
---------------------------...
1
vote
1answer
191 views
IDE Access to Hive without user password?
I'm a DB Developer and in my company we're only able to interact with hive through a technical user who owns the specific write permissions for Hive on HDFS.
So in practice this looks like this:
I ...
0
votes
0answers
125 views
Sum of integers between two rows in Hive
Suppose I have a Hive table that looks like this:
a b
41 77
8 32
31 76
I would like to have another column say c which applies a function that loops through all integers between columns a ...
1
vote
0answers
451 views
Do Hive Materialized Views respond to added partitions to source tables?
Hive Materialized Views support partitions,
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
[DISABLE REWRITE]
[COMMENT materialized_view_comment]
[PARTITIONED ON (...
0
votes
0answers
17 views
HIVE SQL - JOIN
I am trying to perform a left join between tables. The syntax looks something like this:
select a.x1, a.x2, a.x3, b.y1 from a left join b on a.x1=b.x1
Ideally, the query should give all records that ...
0
votes
0answers
16 views
Join hive tables which are being populated in real time
I have two tables in hive which are being populated in real time from a set of files. There is a request_time and trx_id columns in both tables where trx_id is the joining key.
I want to join them ...
1
vote
0answers
1k views
Hive query with RANK() in a WHERE clause
I want to do this, to get all the rows for a given a,b combo that have the highest value of c:
SELECT a, b, c
FROM x
WHERE RANK() OVER (PARTITION BY a,b ORDER BY c DESC) = 1
It fails, saying invalid ...
1
vote
1answer
309 views
“Error while processing statement” running a query finding unique values with multiple values in other rows
Is there anything wrong in the syntax of this query finding unique values with multiple values in other rows?
Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr As Ss
Join (
Select `cd`
...
2
votes
1answer
80 views
joining the where clause to select statement of two different tables [closed]
How can I properly structure the below query to make it work? I would like to have the Ref_CD = MBR_ID_TYPE_ID to be my select statement on the beginning of it.
select MBR_ID_TYP_ID || '-' || (
...
0
votes
1answer
397 views
User specific default database in Hive
I am running a Oracle Big Data Appliance platform that has Cloudera EDH 5.9.x running under the hood. My users are mainly using beeswax, which is the Hive query editor app within Hue.
When a user ...
1
vote
1answer
98 views
Hive metrastore Database in Postgresql External [closed]
We recently migrated hive metastore database from embedded postgresql(coming with cloudera hadoop) to external postgresql(standalone in vm server). After that we notified that there is increase of 5 ...
2
votes
2answers
2k views
How can I group the null results in this FULL OUTER JOIN with non-null responses?
I am looking at the overlap and non-overlap (unique values) of users-ids from two different select statements using a full join. The main differentiation being that one table will have a deal_id = 0 ...
0
votes
1answer
3k views
Hive. Over partition by vs where conditions
I have a table partitioned this way.
# Partition Information
# col_name data_type
year string
month string
day string
hour ...
2
votes
1answer
952 views
how to insert data into extra columns of target avro table when source table is having less no of columns compared to target using hive or impala?
Suppose I am having a source Avro table having 10 columns and my target Avro table is having 12 columns, while inserting data into the target table I need to add null values to the extra 2 columns.
...
5
votes
1answer
9k views
Cumulative sum using hiveql
I have a table in Hive which looks like:
col1 col2
b 1
b 2
a 3
b 2
c 4
c 5
How do I, with hiveql, group up col1 elements together, ...
4
votes
1answer
4k views
correlate subquery in hive
SELECT ndo.sku ParentSKU, visitsWhenSKUWasOnline.s
FROM
Temp.NumberOfDaysOnline ndo
JOIN
(
SELECT SUM(gasessiondata.sessions) as s
FROM gasessiondata
WHERE
gasessiondata.date >= ...
