Something like this should work on 1.2.1 an onward. Or if your accessing with
JDBC you can always bind in an array as well. We do this all over the place.
with
x as (select explode(split('1,2,3,4,5',',')) as y)
select *
from x;
From: Mainak Ghosh <[email protected]>
Sent: Friday, March 29, 2019 11:02 AM
To: [email protected]
Subject: Re: Creating temp tables in select statements
Thanks everyone for the reply.
We are using 2.3.2 :( but good to see this implemented in Hive. It is such a
common use case.
Edward, will you be having the UDTF source anywhere.
On a related note, do we have any Hive UDF library ala
Hivemall<https://hivemall.incubator.apache.org/> but for general functions like
date, etc.
Thanks and Regards.
Mainak
On Mar 28, 2019, at 3:17 PM, Edward Capriolo
<[email protected]<mailto:[email protected]>> wrote:
I made a udtf a while back that's let's you specify lists of tuples from there
you can explode them into rows
On Thursday, March 28, 2019, Jesus Camacho Rodriguez
<[email protected]<mailto:[email protected]>>
wrote:
Depending on the version you are using, table + values syntax is supported.
https://issues.apache.org/jira/browse/HIVE-18416
SELECT a, b FROM TABLE(VALUES(1,2),(3,4)) AS x(a,b);
-Jesús
From: David Lavati <[email protected]<mailto:[email protected]>>
Reply-To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Date: Thursday, March 28, 2019 at 4:44 AM
To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Subject: Re: Creating temp tables in select statements
Hi Mainak,
For select queries the only way I know of for multiple records is through using
union:
0: jdbc:hive2://localhost:10000> with x as (select 1 num union select 2 union
select 3) select * from x;
+--------+
| x.num |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
For table insertion you can use a syntax somewhat similar to VALUES
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingvaluesintotablesfromSQL
Kind Regards,
David
On Wed, Mar 27, 2019 at 12:40 AM Mainak Ghosh
<[email protected]<mailto:[email protected]>> wrote:
Hello,
We want to create temp tables at a select query level. For example:
with x as (1, 2, 3) select * from x;
Or
Select * from table where id in <list of integers>; Here list of integers is an
input and can change.
Currently Postgres VALUES syntax is not supported in Hive. Is there some easy
workarounds which does not involved explicitly creating temporary tables and
can be specified at the select query level?
Thanks and Regards,
Mainak
--
David Lavati | Software Engineer
t. (+3620) 951-7468<tel:0036209517468>
cloudera.com<https://www.cloudera.com/>
<https://www.cloudera.com/>
<https://www.cloudera.com/>
<https://www.cloudera.com/>
<https://www.cloudera.com/>
<https://www.cloudera.com/>
________________________________
<https://www.cloudera.com/>
--
Sorry this was sent from mobile. Will do less grammar and spell check than
usual.<https://www.cloudera.com/>
<https://www.cloudera.com/>
