Questions tagged [hive]

Questions about Hive and HiveQL.

Filter by
Sorted by
Tagged with
0 votes
0 answers
44 views

Is it necessary to optimize join in hdfs?

What is the most optimal way to query in Hive (Datalake, based on hdfs)? Establishing filters in the tables prior to join them, select* from (select code from table_1 where type="a") a ...
Daniel_DS's user avatar
1 vote
0 answers
13 views

How to pass multiple values into hive hql for the same hivevar

Requirement : My Hql has below script in which I want to pass values into the where clause dynamically. How do I dynamically pass using hivevar in below specific scenario where multiple values are ...
RaCh's user avatar
  • 11
0 votes
0 answers
52 views

Hive Mysql Metastore Row lock increases

Below is the query causing row locks in Mariadb Galera Cluster 10.2.32 (3Node Cluster). select MT_COMMENT from AUX_TABLE where MT_KEY1='Cleaner' and MT_KEY2=0 for update Hive is a framework, we ...
Mannoj's user avatar
  • 1,551
2 votes
1 answer
1k views

Connecting to cluster with cqlsh returns "Unable to connect to any servers"

I am trying to deploy The Hive 4 on a VMware Workstation 17 player VM to test Splunk integration with The Hive. I am following the guide at this link, but I encountered an error at one of the stages, ...
aimakovm's user avatar
1 vote
1 answer
145 views

Metastore(Mysql) bottleneck for Hive

We have a hive installation that has MariaDB as metastore database. MariaDB has around ~250 GB metadata with ~100GB indexes. It becomes terribly slow during the peak load of 40-60K QPS. Looking from ...
Shakti Garg's user avatar
1 vote
1 answer
261 views

For each tuple, get the name of the first column which is non-zero

I have a table in Hive which looks like: | Name | 1990 | 1991 | 1992 | 1993 | 1994 | | Rex | 0 | 0 | 1 | 1 | 1 | | Max | 0 | 0 | 0 | 0 | 1 | | Phil | 1 | 1 | 1 ...
user2891462's user avatar
0 votes
0 answers
78 views

Is there an open source implementation of QGM (Query Graph Model)?

I am building a new system that needs to interact essentially as an SQL backend. We would like to import logical queries into it (e.g. from ApacheSPARQ or Postgres and related things) and want to ...
intel_chris's user avatar
2 votes
1 answer
2k 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 ...
Daniel Müller's user avatar
1 vote
1 answer
629 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 ...
Victor's user avatar
  • 127
0 votes
0 answers
22 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 ...
Franco Buhay's user avatar
0 votes
1 answer
515 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; ...
user1795999's user avatar
0 votes
1 answer
294 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 ...
user859385's user avatar
0 votes
1 answer
6k 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 ...
PhilHibbs's user avatar
  • 539
0 votes
1 answer
406 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 ...
lev's user avatar
  • 111
1 vote
0 answers
23 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 ...
PhilHibbs's user avatar
  • 539
1 vote
1 answer
8k 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 ...
TJCLK's user avatar
  • 127
1 vote
1 answer
327 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 ...
TJCLK's user avatar
  • 127
0 votes
1 answer
89 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 ...
Someguywhocodes's user avatar
1 vote
0 answers
3k 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 ) ,...
King David's user avatar
0 votes
2 answers
1k 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 ---------------------------...
Mehdi Nellen's user avatar
1 vote
1 answer
731 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 ...
Mayak's user avatar
  • 181
0 votes
0 answers
414 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 ...
Abhishek Parab's user avatar
0 votes
0 answers
64 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 ...
Raunak87's user avatar
  • 101
0 votes
0 answers
34 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 ...
orak's user avatar
  • 101
1 vote
0 answers
2k 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 ...
PhilHibbs's user avatar
  • 539
1 vote
1 answer
547 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` ...
William Humphries's user avatar
2 votes
1 answer
95 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 || '-' || ( ...
Ina gurey's user avatar
0 votes
1 answer
648 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 ...
rustycodemonkey's user avatar
1 vote
1 answer
137 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 ...
Mughil's user avatar
  • 493
2 votes
2 answers
3k 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 ...
userLP's user avatar
  • 31
0 votes
1 answer
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 ...
Eugene Cuz's user avatar
2 votes
1 answer
1k 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. ...
user109612's user avatar
5 votes
1 answer
11k 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, ...
klx123's user avatar
  • 53
4 votes
1 answer
5k 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 >= ...
systemdebt's user avatar