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> 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 <dlav...@cloudera.com> > *Reply-To: *"user@hive.apache.org" <user@hive.apache.org> > *Date: *Thursday, March 28, 2019 at 4:44 AM > *To: *"user@hive.apache.org" <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 > > > > Kind Regards, > > David > > > > > > On Wed, Mar 27, 2019 at 12:40 AM Mainak Ghosh <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 <0036209517468> > > cloudera.com <https://www.cloudera.com> > > [image: Image removed by sender. Cloudera] <https://www.cloudera.com/> > > [image: Image removed by sender. Cloudera on Twitter] > <https://twitter.com/cloudera> > > [image: Image removed by sender. Cloudera on Facebook] > <https://www.facebook.com/cloudera> > > [image: Image removed by sender. Cloudera on LinkedIn] > <https://www.linkedin.com/company/cloudera> > ------------------------------ > > > -- Sorry this was sent from mobile. Will do less grammar and spell check than usual.
