Questions tagged [hive]

Questions about Hive and HiveQL.

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

Explicitly code array struct types [closed]

Is there a way to code array struct types explicitly so when a new field is added in the target, no changes are required by all sources to insert to the target. If I had to add a new field to the ...
0
votes
1answer
20 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 ...
0
votes
0answers
4 views

Can I remove a text file from a Hive textfile table?

I have loaded data into Hive using the LOAD DATA statement. I can see the files in the HDFS path, e.g. hdfs://mycluster/warehouse/tablespace/managed/hive/database.db/tablename/ Is there any way to ...
1
vote
0answers
6 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
0answers
17 views

What does Hive lastaccesstime mean, and when is it updated?

If I do this: show table extended like migration_log; I get this +----------------------------------------------------+ | tab_name | +----------------------...
0
votes
0answers
13 views

How to set dynamic variables in hive?

The following line will return me an integer of current hour in normal hive statements: HOUR(CURRENT_TIMESTAMP) Next, I want to set above value to a dynamic variable named hour_value: set ...
0
votes
1answer
496 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
29 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
0answers
16 views

How to detect if beeline fails to connect to Hive

The beeline command line utility returns a code in $? (ERRORLEVEL) that indicates how many SQL statements failed. If it fails to connect to Hive, then this number is 0. Is there any other way to ...
0
votes
1answer
45 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
220 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
116 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
105 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
31 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
284 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
15 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
708 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
168 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
77 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
276 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
95 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
2k 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
663 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 target table I need to add null values to extra 2 columns. But when ...
5
votes
1answer
8k 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 >= ...