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 <edlinuxg...@gmail.com> 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 > <jcamachorodrig...@hortonworks.com > <mailto:jcamachorodrig...@hortonworks.com>> wrote: > Depending on the version you are using, table + values syntax is supported. > > https://issues.apache.org/jira/browse/HIVE-18416 > <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 <dlav...@cloudera.com <mailto:dlav...@cloudera.com>> > Reply-To: "user@hive.apache.org <mailto:user@hive.apache.org>" > <user@hive.apache.org <mailto:user@hive.apache.org>> > Date: Thursday, March 28, 2019 at 4:44 AM > To: "user@hive.apache.org <mailto:user@hive.apache.org>" > <user@hive.apache.org <mailto:user@hive.apache.org>> > 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 > > <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 <mgh...@twitter.com > <mailto:mgh...@twitter.com>> 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://twitter.com/cloudera> > <https://www.facebook.com/cloudera> > <https://www.linkedin.com/company/cloudera> > > > > > -- > Sorry this was sent from mobile. Will do less grammar and spell check than > usual.
