Select from list of values on beeline

Question

I use beeline, which is a JDBC client based on SQLLine, to run Hive queries. I would like to select from a liste of values like:

SELECT DISTINCT * FROM (a,b,b,c,d,..z)

is that posssible?

likely a duplicate of https://stackoverflow.com/questions/1564956/how-can-i-select-from-list-of-values-in-sql-server — arhak, Feb 21 '18 at 13:36
ok, not the exact same scenario perhaps, a little variation would make the trick — arhak, Feb 21 '18 at 13:41

score 2 · Accepted Answer · answered Feb 21 '18 at 13:42

2

SELECT DISTINCT *
FROM (
    SELECT 12
    UNION SELECT 23
    UNION SELECT 34
    UNION SELECT 12
    UNION SELECT 23
) AS t1;

PS: also refer to very closely related question How can I select from list of values in SQL Server

answered Feb 21 '18 at 13:42

arhak

2,488
1
24
38

1

Is there something like `select * from [a,b,c,d,e, f...z]` because I have a lot of values and it painful to write such a query – HISI Feb 21 '18 at 13:50
1

`DISTINCT` is superfluous here. A`UNION` operation always returns unique rows. But, a `UNION ALL` with `DISTINCT` would make sense. – Kaushik Nayak Feb 22 '18 at 03:38

score 1 · Answer 2 · answered Feb 21 '18 at 14:27

1

Also you can use stack, it works much faster than UNION:

hive> select id from
    > (
    > select stack(5, --the number of elements
    > 10 ,
    > 20 ,
    > 30 ,
    > 40 ,
    > 50
    > ) as (id)
    > )s;
OK
10
20
30
40
50
Time taken: 4.88 seconds, Fetched: 5 row(s)

answered Feb 21 '18 at 14:27

leftjoin

36,950
8
57
116

is that work in beeline? have you never tried in beeline? – HISI Feb 21 '18 at 14:29
@hisi unfortunately I cannot check beeline right now – leftjoin Feb 21 '18 at 14:37
That doesn't seem working!! I've just tried in beeline – HISI Feb 21 '18 at 14:43

score 0 · Answer 3 · answered Feb 22 '18 at 03:34

0

You may try split and explode

 SELECT DISTINCT * FROM 
 (
 select explode( split('a,b,b,c,d,e,f' ,',')) c
 ) as t
 ORDER BY c

answered Feb 22 '18 at 03:34

Kaushik Nayak

30,772
5
32
45

Select from list of values on beeline

3 Answers3