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
124
votes
19 answers

Difference between Hive internal tables and external tables?

Can anyone tell me the difference between Hive's external table and internal tables. I know the difference comes when dropping the table. I don't understand what you mean by the data and metadata is deleted in internal and only metadata is deleted…
NJ_315
  • 1,863
  • 7
  • 22
  • 30
115
votes
10 answers

How to set variables in HIVE scripts

I'm looking for the SQL equivalent of SET varname = value in Hive QL I know I can do something like this: SET CURRENT_DATE = '2012-09-16'; SELECT * FROM foo WHERE day >= @CURRENT_DATE But then I get this error: character '@' not supported here
user1678312
  • 1,309
  • 3
  • 10
  • 11
111
votes
4 answers

How to get/generate the create statement for an existing hive table?

Assuming you have "table" already in Hive, is there a quick way like other databases to be able to get the "CREATE" statement for that table?
Rolando
  • 58,640
  • 98
  • 266
  • 407
89
votes
6 answers

How to Update/Drop a Hive Partition?

After adding a partition to an external table in Hive, how can I update/drop it?
darcyy
  • 5,236
  • 5
  • 28
  • 41
85
votes
18 answers

How do I output the results of a HiveQL query to CSV?

we would like to put the results of a Hive query to a CSV file. I thought the command should look like this: insert overwrite directory '/home/output.csv' select books from table; When I run it, it says it completeld successfully but I can never…
AAA
  • 2,388
  • 9
  • 32
  • 47
79
votes
6 answers

How to select current date in Hive SQL

How do we get the current system date in Hive? In MySQL we have select now(), can any one please help me to get the query results. I am very new to Hive, is there a proper documentation for Hive that gives the details information about the pseudo…
Elingela
  • 819
  • 1
  • 6
  • 4
77
votes
10 answers

I have created a table in hive, I would like to know which directory my table is created in?

I have created a table in hive, I would like to know which directory my table is created in? I would like to know the path...
Muneer Basha Syed
  • 789
  • 1
  • 6
  • 5
74
votes
16 answers

Hive insert query like SQL

I am new to hive, and want to know if there is anyway to insert data into Hive table like we do in SQL. I want to insert my data into hive like INSERT INTO tablename VALUES (value1,value2..) I have read that you can load the data from a file to…
Y0gesh Gupta
  • 2,184
  • 5
  • 40
  • 56
66
votes
3 answers

PySpark: withColumn() with two conditions and three outcomes

I am working with Spark and PySpark. I am trying to achieve the result equivalent to the following pseudocode: df = df.withColumn('new_column', IF fruit1 == fruit2 THEN 1, ELSE 0. IF fruit1 IS NULL OR fruit2 IS NULL 3.) I am trying to do this…
user2205916
  • 3,196
  • 11
  • 54
  • 82
57
votes
3 answers

Hive: Convert String to Integer

I am looking for a Built-in UDF to convert values of a string column to integer in my hive table for sorting using SELECT and ORDER BY. I searched in the Language Manual, but no use. Any other suggestions also welcome.
Srinivas
  • 2,479
  • 8
  • 47
  • 69
52
votes
7 answers

How to export data from Spark SQL to CSV

This command works with HiveQL: insert overwrite directory '/data/home.csv' select * from testtable; But with Spark SQL I'm getting an error with an org.apache.spark.sql.hive.HiveQl stack trace: java.lang.RuntimeException: Unsupported language…
shashankS
  • 1,043
  • 1
  • 11
  • 21
45
votes
3 answers

Explode the Array of Struct in Hive

This is the below Hive Table CREATE EXTERNAL TABLE IF NOT EXISTS SampleTable ( USER_ID BIGINT, NEW_ITEM ARRAY> ) And this is the data in the above table- 1015826235 …
arsenal
  • 23,366
  • 85
  • 225
  • 331
35
votes
18 answers

java.lang.RuntimeException:Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient

I have configured my Hive as given on link: http://www.youtube.com/watch?v=Dqo1ahdBK_A, but I am getting the following error while creating a table in Hive. I am using hadoop-1.2.1 and hive-0.12.0. hive> create table employee(emp_id int,name…
Raju Sharma
  • 2,496
  • 3
  • 23
  • 41
35
votes
3 answers

Skip first line of csv while loading in hive table

Hello Friends, I created table in hive with help of following command - CREATE TABLE db.test ( fname STRING, lname STRING, age STRING, mob BIGINT ) row format delimited fields terminated BY '\t' stored AS textfile;…
Pankaj
  • 369
  • 1
  • 4
  • 7
34
votes
4 answers

How to calculate Date difference in Hive

I'm a novice. I have a employee table with a column specifying the joining date and I want to retrieve the list of employees who have joined in the last 3 months. I understand we can get the current date using from_unixtime(unix_timestamp()). How do…
Holmes
  • 1,059
  • 2
  • 17
  • 25
1
2 3
…
99 100