You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mainak Ghosh <mg...@twitter.com> on 2019/03/26 23:40:21 UTC
Creating temp tables in select statements
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
RE: Creating temp tables in select statements
Posted by Shawn Weeks <sw...@weeksconsulting.us>.
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 <mg...@twitter.com>
Sent: Friday, March 29, 2019 11:02 AM
To: user@hive.apache.org
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 <ed...@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 <jc...@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 <dl...@cloudera.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Thursday, March 28, 2019 at 4:44 AM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@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 <mg...@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://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/>
Re: Creating temp tables in select statements
Posted by Mainak Ghosh <mg...@twitter.com>.
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 <ed...@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 <jcamachorodriguez@hortonworks.com <ma...@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 <dlavati@cloudera.com <ma...@cloudera.com>>
> Reply-To: "user@hive.apache.org <ma...@hive.apache.org>" <user@hive.apache.org <ma...@hive.apache.org>>
> Date: Thursday, March 28, 2019 at 4:44 AM
> To: "user@hive.apache.org <ma...@hive.apache.org>" <user@hive.apache.org <ma...@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 <mghosh@twitter.com <ma...@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.
Re: Creating temp tables in select statements
Posted by Edward Capriolo <ed...@gmail.com>.
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 <
jcamachorodriguez@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 <dl...@cloudera.com>
> *Reply-To: *"user@hive.apache.org" <us...@hive.apache.org>
> *Date: *Thursday, March 28, 2019 at 4:44 AM
> *To: *"user@hive.apache.org" <us...@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 <mg...@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.
Re: Creating temp tables in select statements
Posted by Jesus Camacho Rodriguez <jc...@hortonworks.com>.
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 <dl...@cloudera.com>
Reply-To: "user@hive.apache.org" <us...@hive.apache.org>
Date: Thursday, March 28, 2019 at 4:44 AM
To: "user@hive.apache.org" <us...@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 <mg...@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>
[Image removed by sender. Cloudera]<https://www.cloudera.com/>
[Image removed by sender. Cloudera on Twitter]<https://twitter.com/cloudera>
[Image removed by sender. Cloudera on Facebook]<https://www.facebook.com/cloudera>
[Image removed by sender. Cloudera on LinkedIn]<https://www.linkedin.com/company/cloudera>
________________________________
Re: Creating temp tables in select statements
Posted by David Lavati <dl...@cloudera.com>.
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 <mg...@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: Cloudera] <https://www.cloudera.com/>
[image: Cloudera on Twitter] <https://twitter.com/cloudera> [image:
Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: Cloudera
on LinkedIn] <https://www.linkedin.com/company/cloudera>
------------------------------