You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Michael Malak <mi...@yahoo.com> on 2013/06/19 21:23:33 UTC
INSERT non-static data to array?
Is the only way to INSERT data into a column of type array<> to load data from a pre-existing file, to use hard-coded values in the INSERT statement, or copy an entire array verbatim from another table? I.e. I'm assuming that a) SQL1999 array INSERT via subquery is not (yet) implemented in Hive, and b) there is also no other way to load dynamically generated data into an array<> column? If my assumption in a) is true, does a Jira item need to be created for it?
Re: INSERT non-static data to array?
Posted by Michael Malak <mi...@yahoo.com>.
My understanding is that LATERAL VIEW goes the other direction: takes an array and makes it into separate rows. I use that a lot. But I also need to go the other way sometimes: take a bunch of rows and squeeze them down into an array. Please correct me if I'm missing something.
________________________________
From: Edward Capriolo <ed...@gmail.com>
To: "user@hive.apache.org" <us...@hive.apache.org>; Michael Malak <mi...@yahoo.com>
Sent: Thursday, June 20, 2013 9:15 PM
Subject: Re: INSERT non-static data to array?
i think you could select into as sub query and then use lateral view.not exactly the same but something similar could be done,.
On Thursday, June 20, 2013, Michael Malak <mi...@yahoo.com> wrote:
> I've created
> https://issues.apache.org/jira/browse/HIVE-4771
>
> to track this issue.
>
>
> ----- Original Message -----
> From: Michael Malak <mi...@yahoo.com>
> To: "user@hive.apache.org" <us...@hive.apache.org>
> Cc:
> Sent: Wednesday, June 19, 2013 2:35 PM
> Subject: Re: INSERT non-static data to array?
>
> The example code for inline_table() there has static data. It's not possible to use a subquery inside the inline_table() or array() is it?
>
> The SQL1999 way is described here:
>
> http://www.postgresql.org/message-id/20041028232152.GA76168@winnie.fuhr.org
>
>
> CREATE TABLE table_a(a int, b int, c int[]);
>
> INSERT INTO table_a
> SELECT a, b, ARRAY(SELECT c FROM table_c WHERE table_c.parent = table_b.id)
> FROM table_b
>
> ________________________________
> From: Edward Capriolo <ed...@gmail.com>
> To: "user@hive.apache.org" <us...@hive.apache.org>; Michael Malak <mi...@yahoo.com>
> Sent: Wednesday, June 19, 2013 2:06 PM
> Subject: Re: INSERT non-static data to array?
>
>
>
> : https://issues.apache.org/jira/browse/HIVE-3238
>
>
> This might fit the bill.
>
>
>
>
> On Wed, Jun 19, 2013 at 3:23 PM, Michael Malak <mi...@yahoo.com> wrote:
>
> Is the only way to INSERT data into a column of type array<> to load data from a pre-existing file, to use hard-coded values in the INSERT statement, or copy an entire array verbatim from another table? I.e. I'm assuming that a) SQL1999 array INSERT via subquery is not (yet) implemented in Hive, and b) there is also no other way to load dynamically generated data into an array<> column? If my assumption in a) is true, does a Jira item need to be created for it?
>>
>
Re: INSERT non-static data to array?
Posted by Edward Capriolo <ed...@gmail.com>.
i think you could select into as sub query and then use lateral view.not
exactly the same but something similar could be done,.
On Thursday, June 20, 2013, Michael Malak <mi...@yahoo.com> wrote:
> I've created
> https://issues.apache.org/jira/browse/HIVE-4771
>
> to track this issue.
>
>
> ----- Original Message -----
> From: Michael Malak <mi...@yahoo.com>
> To: "user@hive.apache.org" <us...@hive.apache.org>
> Cc:
> Sent: Wednesday, June 19, 2013 2:35 PM
> Subject: Re: INSERT non-static data to array?
>
> The example code for inline_table() there has static data. It's not
possible to use a subquery inside the inline_table() or array() is it?
>
> The SQL1999 way is described here:
>
>
http://www.postgresql.org/message-id/20041028232152.GA76168@winnie.fuhr.org
>
>
> CREATE TABLE table_a(a int, b int, c int[]);
>
> INSERT INTO table_a
> SELECT a, b, ARRAY(SELECT c FROM table_c WHERE table_c.parent =
table_b.id)
> FROM table_b
>
> ________________________________
> From: Edward Capriolo <ed...@gmail.com>
> To: "user@hive.apache.org" <us...@hive.apache.org>; Michael Malak <
michaelmalak@yahoo.com>
> Sent: Wednesday, June 19, 2013 2:06 PM
> Subject: Re: INSERT non-static data to array?
>
>
>
> : https://issues.apache.org/jira/browse/HIVE-3238
>
>
> This might fit the bill.
>
>
>
>
> On Wed, Jun 19, 2013 at 3:23 PM, Michael Malak <mi...@yahoo.com>
wrote:
>
> Is the only way to INSERT data into a column of type array<> to load data
from a pre-existing file, to use hard-coded values in the INSERT statement,
or copy an entire array verbatim from another table? I.e. I'm assuming
that a) SQL1999 array INSERT via subquery is not (yet) implemented in Hive,
and b) there is also no other way to load dynamically generated data into
an array<> column? If my assumption in a) is true, does a Jira item need
to be created for it?
>>
>
Re: INSERT non-static data to array?
Posted by Michael Malak <mi...@yahoo.com>.
I've created
https://issues.apache.org/jira/browse/HIVE-4771
to track this issue.
----- Original Message -----
From: Michael Malak <mi...@yahoo.com>
To: "user@hive.apache.org" <us...@hive.apache.org>
Cc:
Sent: Wednesday, June 19, 2013 2:35 PM
Subject: Re: INSERT non-static data to array?
The example code for inline_table() there has static data. It's not possible to use a subquery inside the inline_table() or array() is it?
The SQL1999 way is described here:
http://www.postgresql.org/message-id/20041028232152.GA76168@winnie.fuhr.org
CREATE TABLE table_a(a int, b int, c int[]);
INSERT INTO table_a
SELECT a, b, ARRAY(SELECT c FROM table_c WHERE table_c.parent = table_b.id)
FROM table_b
________________________________
From: Edward Capriolo <ed...@gmail.com>
To: "user@hive.apache.org" <us...@hive.apache.org>; Michael Malak <mi...@yahoo.com>
Sent: Wednesday, June 19, 2013 2:06 PM
Subject: Re: INSERT non-static data to array?
: https://issues.apache.org/jira/browse/HIVE-3238
This might fit the bill.
On Wed, Jun 19, 2013 at 3:23 PM, Michael Malak <mi...@yahoo.com> wrote:
Is the only way to INSERT data into a column of type array<> to load data from a pre-existing file, to use hard-coded values in the INSERT statement, or copy an entire array verbatim from another table? I.e. I'm assuming that a) SQL1999 array INSERT via subquery is not (yet) implemented in Hive, and b) there is also no other way to load dynamically generated data into an array<> column? If my assumption in a) is true, does a Jira item need to be created for it?
>
Re: INSERT non-static data to array?
Posted by Michael Malak <mi...@yahoo.com>.
The example code for inline_table() there has static data. It's not possible to use a subquery inside the inline_table() or array() is it?
The SQL1999 way is described here:
http://www.postgresql.org/message-id/20041028232152.GA76168@winnie.fuhr.org
CREATE TABLE table_a(a int, b int, c int[]);
INSERT INTO table_a
SELECT a, b, ARRAY(SELECT c FROM table_c WHERE table_c.parent = table_b.id)
FROM table_b
________________________________
From: Edward Capriolo <ed...@gmail.com>
To: "user@hive.apache.org" <us...@hive.apache.org>; Michael Malak <mi...@yahoo.com>
Sent: Wednesday, June 19, 2013 2:06 PM
Subject: Re: INSERT non-static data to array?
: https://issues.apache.org/jira/browse/HIVE-3238
This might fit the bill.
On Wed, Jun 19, 2013 at 3:23 PM, Michael Malak <mi...@yahoo.com> wrote:
Is the only way to INSERT data into a column of type array<> to load data from a pre-existing file, to use hard-coded values in the INSERT statement, or copy an entire array verbatim from another table? I.e. I'm assuming that a) SQL1999 array INSERT via subquery is not (yet) implemented in Hive, and b) there is also no other way to load dynamically generated data into an array<> column? If my assumption in a) is true, does a Jira item need to be created for it?
>
Re: INSERT non-static data to array?
Posted by Edward Capriolo <ed...@gmail.com>.
: https://issues.apache.org/jira/browse/HIVE-3238
This might fit the bill.
On Wed, Jun 19, 2013 at 3:23 PM, Michael Malak <mi...@yahoo.com>wrote:
> Is the only way to INSERT data into a column of type array<> to load data
> from a pre-existing file, to use hard-coded values in the INSERT statement,
> or copy an entire array verbatim from another table? I.e. I'm assuming
> that a) SQL1999 array INSERT via subquery is not (yet) implemented in Hive,
> and b) there is also no other way to load dynamically generated data into
> an array<> column? If my assumption in a) is true, does a Jira item need
> to be created for it?
>