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
33
votes
4 answers

How to rename a hive table without changing location?

Based on the Hive doc below: Rename Table ALTER TABLE table_name RENAME TO new_table_name; This statement lets you change the name of a table to a different name. As of version 0.6, a rename on a managed table moves its HDFS location as well.…
Osiris
  • 1,007
  • 4
  • 17
  • 30
33
votes
3 answers

Select top 2 rows in Hive

I'm trying to retrieve top 2 tables from my employee list based on salary in hive (version 0.11). Since it doesn't support TOP function, is there any alternatives? Or do we have define a UDF?
Holmes
  • 1,059
  • 2
  • 17
  • 25
33
votes
3 answers

How to calculate median in Hive

I have a hive table, name age sal A 45 1222 B 50 4555 c 44 8888 D 78 1222 E 12 7888 F 23 4555 I want to calculate median of age column. Below is my approach select min(age)…
Amaresh
  • 3,231
  • 7
  • 37
  • 60
33
votes
1 answer

Add a column in a table in HIVE QL

I'm writing a code in HIVE to create a table consisting of 1300 rows and 6 columns: create table test1 as SELECT cd_screen_function, SUM(access_count) AS max_count, MIN(response_time_min) as response_time_min, AVG(response_time_avg)…
user2532312
  • 331
  • 1
  • 3
  • 4
32
votes
3 answers

Explode (transpose?) multiple columns in Spark SQL table

I am using Spark SQL (I mention that it is in Spark in case that affects the SQL syntax - I'm not familiar enough to be sure yet) and I have a table that I am trying to re-structure, but I'm getting stuck trying to transpose multiple columns at the…
anthr
  • 1,026
  • 4
  • 17
  • 34
32
votes
8 answers

Transferring hive table from one database to another

I need to move a hive table from one database to another. How can I do that?
user2942227
  • 1,023
  • 6
  • 19
  • 26
31
votes
2 answers

What does msck stands for in Msck repair command

Hive Msck repair command is used to repair partitions, but what is full form of MSCK. I already tried to find in hive doc's but hard luck.
Kaustubh Deshpande
  • 438
  • 6
  • 13
26
votes
2 answers

How can I convert array to string in hive sql?

I want to convert an array to string in hive. I want to collect_set array values to convert to string without [[""]]. select actor, collect_set(date) as grpdate from actor_table group by actor; so that [["2016-07-01", "2016-07-02"]] would become…
Bethlee
  • 825
  • 3
  • 17
  • 28
26
votes
4 answers

Can we load Parquet file into Hive directly?

I know we can load parquet file using Spark SQL and using Impala but wondering if we can do the same using Hive. I have been reading many articles but I am still confused. Simply put, I have a parquet file - say users.parquet. Now I am struck here…
annunarcist
  • 1,637
  • 3
  • 20
  • 42
26
votes
4 answers

How to copy all hive table from one Database to other Database

I have default db in hive table which contains 80 tables . I have created one more database and I want to copy all the tables from default DB to new Databases. Is there any way I can copy from One DB to Other DB, without creating individual…
Amaresh
  • 3,231
  • 7
  • 37
  • 60
25
votes
6 answers

What is the difference between -hivevar and -hiveconf?

From hive -h : --hiveconf Use value for given property --hivevar Variable subsitution to apply to hive commands. e.g. --hivevar A=B
Karnimrod
  • 425
  • 1
  • 4
  • 8
24
votes
6 answers

How to list all hive databases being in use or created so far?

Similar to SHOW TABLES command, do we have any such command to list all databases created so far?
Raja Reddy
  • 772
  • 8
  • 19
  • 37
24
votes
2 answers

metastore_db created wherever I run Hive

Folder metastore_db is created in any directory where I run Hive query. Is there any way to have only one metastore_db in a defined location and stop it from being created all over the places? Does it have anything to do with hive.metastore.local?
darcyy
  • 5,236
  • 5
  • 28
  • 41
22
votes
5 answers

Hive Explode / Lateral View multiple arrays

I have a hive table with the following schema: COOKIE | PRODUCT_ID | CAT_ID | QTY 1234123 [1,2,3] [r,t,null] [2,1,null] How can I normalize the arrays so I get the following result COOKIE | PRODUCT_ID | CAT_ID | QTY 1234123 …
user2726995
  • 2,064
  • 2
  • 21
  • 26
21
votes
3 answers

RIGHT() / LEFT() functions

Is there a function in Hiveql that is equivalent to Right() or Left() function from TSQL? For example, RIGHT(col1,10) to get the first 10 characters from col1.
jmich738
  • 1,565
  • 3
  • 24
  • 41
1
2
3
…
99 100