24

I'd like to run statements like

SELECT date_add('2008-12-31', 1) FROM DUAL

Does Hive (running on Amazon EMR) have something similar?

jbreed
  • 1,514
  • 5
  • 22
  • 35
  • Most databases do not need a pseudotable like DUAL, thats Oracle only. So whats your real question, do you want to do date arithmetics? – schlenk Mar 21 '12 at 02:39
  • @schlenk I just want something to run functions and do debugging from, since I'm not very familiar with the language. – jbreed Mar 21 '12 at 21:32
  • This feature is now supported in hive – Jainik Feb 19 '19 at 07:39

6 Answers6

28

Best solution is not to mention table name. 

select 1+1;

Gives the result 2. But poor Hive need to spawn map reduce to find this!

Ram Ghadiyaram
  • 28,239
  • 13
  • 95
  • 121
Harikrishnan Ck
  • 920
  • 1
  • 11
  • 12
  • Not yet: hive> select 1 + 1; FAILED: ParseException line 1:14 mismatched input '' expecting FROM near '1' in from clause – teu Jun 01 '15 at 20:13
  • 2
    Old post but worth mentioning that this is supported in at least version 0.13 – Aaron Jun 04 '15 at 15:20
  • this is the simplest, and it works in scenarios where one doesn't have the rights to create a table. – Bhavin Doshi Feb 18 '16 at 05:39
11

Not yet: https://issues.apache.org/jira/browse/HIVE-1558

Jeff Hammerbacher
  • 4,226
  • 2
  • 29
  • 36
  • 1
    that issue was filed about 8 years ago and still not resolved... you can pretty much bet it never will be. – harschware Jul 24 '18 at 22:49
7

To create a dual like table in hive where there is one column and one row you can do the following:

create table dual (x int);
insert into table dual select count(*)+1 as x from dual;

Test an expression:

select split('3,2,1','\\,') as my_new_array from dual;

Output:

["3","2","1"]
invoketheshell
  • 3,819
  • 2
  • 20
  • 35
5

There is a nice working solution (well, workaround) available in the link, but it is slow as you might imagine.

The idea is that you create a table with a dummy field, create a text file whose content is just 'X', load that text into that table. Viola. 

CREATE TABLE dual (dummy STRING);

load data local inpath '/path/to/textfile/dual.txt' overwrite into table dual;

SELECT date_add('2008-12-31', 1) from dual;
ciuncan
  • 1,062
  • 2
  • 11
  • 25
3

Hive does support this function now and also does support many other dates function as well.

You can run query like below in hive, which will add days the provided date in first argument.

SELECT DATE_ADD('2019-03-01', 5);

Hive Date Functions

Jainik
  • 2,352
  • 1
  • 19
  • 27
2

Quick Solution:

We can use existing table to achieve dual functionality by following query.

SELECT date_add('2008-12-31', 1) FROM <Any Existing Table> LIMIT 1

For example:

SELECT CONCAT('kbdjj','56454') AS a, null AS b FROM tbl_name LIMIT 1

Result

"limit 1" in query is used to avoid multiple occurrences of specified values (kbdjj56454,null).

Balaji
  • 21
  • 6