Stack Overflow
Stack Overflow

Questions tagged [hiveql]

Variant of SQL used in the Apache Hive data warehouse infrastructure. Use this tag for questions related to the Hive Query Language including HiveQL syntax and HiveQL-specific functions.

HiveQL is the dialect of used in Apache Hive. HiveQL is similar to other dialects of SQL, but has some limitations (e.g. absence of nested subqueries and top-level disjuncts) and additional features (e.g. MapJoin), compared to most other dialects. Apache Hive is a infrastructure package built on the distributed-computing framework, written in Java.

Please read How to write good SQL question section in SQL tag wiki and follow the same rules.

References

4862 questions
10
votes
2 answers

how to insert multiple rows of data into a hive(0.13.1) table?

I am looking for an equivalent of bellow query for Hive version 0.13.1. INSERT INTO TABLE table1 VALUES (151, 'cash', 'lunch'), (152, 'credit', 'lunch'), (153, 'cash', 'dinner'); from this answer it is clear "INSERT.... VALUES" query available…
John
  • 1,212
  • 1
  • 16
  • 30
10
votes
1 answer

NULL data converted into '\N' for numeric columns in hive.?

I created a hive table which has numeric columns such as double and string columns.My file contains some NULL values for both numeric and string columns. When I try to load a file into this table, NULL values for the numeric columns is replaced by…
Vishwanath Sharma
  • 211
  • 2
  • 19
10
votes
1 answer

HIVE: How to include null rows in lateral view explode

I have a table as follows: user_id email u1 e1, e2 u2 null My goal is to convert this into the following format: user_id email u1 e1 u1 e2 u2 null So for this I am using the lateral view explode() function in Hive, as…
Dileep Kumar Patchigolla
  • 948
  • 1
  • 14
  • 30
10
votes
3 answers

How to subtract months from date in HIVE

I am looking for a method that helps me subtract months from a date in HIVE I have a date 2015-02-01. Now i need to subtract 2 months from this date so that result should be 2014-12-01. Can you guys help me out here?
Vaishak
  • 607
  • 3
  • 8
  • 30
10
votes
4 answers

how to write case and group by in hive query

This is my hive table: course dept subject status btech cse java pass btech cse hadoop fail btech cse cg detained btech cse cc pass btech it daa pass btech it wt …
priyanka
  • 287
  • 2
  • 5
  • 11
10
votes
2 answers

Hive - LIKE Operator

I can not figure out how I deal with that problem: This is my Data: Table1: Table2: BRAND PRODUCT SOLD Sony Sony ABCD 1233 Apple Sony adv 1233 Google Sony aaaa …
Daniel
  • 552
  • 2
  • 9
  • 29
10
votes
1 answer

Hive - Converting a string to bigint

Suppose I have a string like '00321' and I want to convert it into a BIGINT in Hive, how would I do it? Follow-up question: would the resulting BIGINT value be 321 or 00321?
activelearner
  • 7,055
  • 20
  • 53
  • 94
10
votes
7 answers

unable to create hive table with primary key

I am unable to create an external table in hive with primary key. Following is the example code: hive> create table exmp((name string),primary key(name)); This returns me the following error message: NoViableAltException(278@[]) at …
Hussain Shaik
  • 117
  • 1
  • 4
  • 11
10
votes
6 answers

Hive: Sum over a specified group (HiveQL)

I have a table: key product_code cost 1 UK 20 1 US 10 1 EU 5 2 UK 3 2 EU 6 I would like to find the sum of all products for each group of "key" and…
joshlk
  • 1,499
  • 3
  • 20
  • 33
10
votes
3 answers

Wrong result for count(*) in hive table

I have created a table in HIVE CREATE TABLE IF NOT EXISTS daily_firstseen_analysis ( firstSeen STRING, category STRING, circle STRING, specId STRING, language STRING, osType …
minhas23
  • 9,291
  • 3
  • 58
  • 40
10
votes
4 answers

how can we test HIVE functions without referencing a table

I wanted to understand the UDF WeekOfYear and how it starts the first week. I had to artifically hit a table and run the query . I wanted to not hit the table and compute the values. Secondly can I look at the UDF source code? SELECT…
vkaul11
  • 4,098
  • 12
  • 47
  • 79
10
votes
4 answers

How to access the last element in an array ?

In my hive table, the session field is a string in format like: ip-sessionID-userID or area-sessionID-userID There's 3 or 4 fields separated by "-", but userID is always the last one. i wanna select userID, but how to access the last field? In…
qiuxiafei
  • 5,827
  • 5
  • 30
  • 43
10
votes
2 answers

Query two related tables (Joins)

This is First table in Hive- It contains information about the item we are purchasing. CREATE EXTERNAL TABLE IF NOT EXISTS Table1 (This is the MAIN table through which comparisons need to be made) ( ITEM_ID BIGINT, CREATED_TIME STRING, BUYER_ID…
AKIWEB
  • 19,008
  • 67
  • 180
  • 294
9
votes
1 answer

How to get the same percent_rank in SQL and pandas?

I was learning pyspark which uses HiveQL and found it interesting that the percent rank gives two different answers for pyspark-sql and pandas. Question Source with sql code: https://www.windowfunctions.com/questions/ranking/3 How to get the same…
BhishanPoudel
  • 15,974
  • 21
  • 108
  • 169
9
votes
2 answers

how to map column names in a hive table and replace it with new values in hive table

I have a csv data as below where data comes every 10mins in the following format. I need to insert this data into hive by mapping column names with different column names. (columns don't come in constant order they change their order, we have total…
Rahul Varma
  • 550
  • 5
  • 23
1 2 3
…
99 100