Is it possible to INSERT INTO TABLE t SELECT FROM where t has a column with a STRUCT?

Based on
http://grokbase.com/t/hive/user/109r87hh3e/insert-data-into-a-column-of-complex-type

I thought perhaps the following would work:

echo 1,2 >twovalues.csv
hive
CREATE TABLE tc (x INT, y INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
LOAD DATA LOCAL INPATH 'twovalues.csv' INTO TABLE tc;
CREATE TABLE oc (z STRUCT<a: int, b: int>);
INSERT INTO TABLE oc SELECT struct(x,y) FROM tc;

but when I do the above I get:

FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into target table because column number/types are different 'oc': Cannot convert column 0 from struct<col1:int,col2:int> to struct<a:int,b:int>.

Hmm. I tried the following hacks, but all wouldn't parse. Ideas?

I changed:

  ... select struct(x,y) ...

to

  ... select struct(x,y) as struct<a:int,b:int> ...
  ... select cast(struct(x,y) as struct<a:int,b:int>) ...
  ... select struct(x as a,y as b) ...

Okay, but there is a hack that does work; By pass INSERT INTO and just
write to the directory:

INSERT DIRECTORY '/path/to/table/directory' SELECT ...;

Just be careful it doesn't clobber any files already there. I'm paranoid,
so I would write to a different directory and then move the files over...

dean

On Wed, Feb 13, 2013 at 1:26 PM, Michael Malak <[EMAIL PROTECTED]>wrote:

> Is it possible to INSERT INTO TABLE t SELECT FROM where t has a column
> with a STRUCT?
>
> Based on
>
> http://grokbase.com/t/hive/user/109r87hh3e/insert-data-into-a-column-of-complex-type
>
> I thought perhaps the following would work:
>
> echo 1,2 >twovalues.csv
> hive
> CREATE TABLE tc (x INT, y INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY
> ',';
> LOAD DATA LOCAL INPATH 'twovalues.csv' INTO TABLE tc;
> CREATE TABLE oc (z STRUCT<a: int, b: int>);
> INSERT INTO TABLE oc SELECT struct(x,y) FROM tc;
>
> but when I do the above I get:
>
> FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into
> target table because column number/types are different 'oc': Cannot convert
> column 0 from struct<col1:int,col2:int> to struct<a:int,b:int>.
>
>
--
*Dean Wampler, Ph.D.*
thinkbiganalytics.com
+1-312-339-1330

I developed the inline udtf. Seems to work:

http://svn.apache.org/repos/asf/hive/trunk/ql/src/test/queries/clientpositive/udf_inline.q
  as (id, text) FROM SRC limit 2;

SELECT inline(
  ARRAY(
    STRUCT (1,'dude!'),
    STRUCT (2,'Wheres'),
    STRUCT (3,'my car?')
  )
)  as (id, text) FROM SRC limit 2;

On Wed, Feb 13, 2013 at 2:47 PM, Dean Wampler
<[EMAIL PROTECTED]> wrote:
> Hmm. I tried the following hacks, but all wouldn't parse. Ideas?
>
> I changed:
>
>   ... select struct(x,y) ...
>
> to
>
>   ... select struct(x,y) as struct<a:int,b:int> ...
>   ... select cast(struct(x,y) as struct<a:int,b:int>) ...
>   ... select struct(x as a,y as b) ...
>
> Okay, but there is a hack that does work; By pass INSERT INTO and just write
> to the directory:
>
> INSERT DIRECTORY '/path/to/table/directory' SELECT ...;
>
> Just be careful it doesn't clobber any files already there. I'm paranoid, so
> I would write to a different directory and then move the files over...
>
> dean
>
> On Wed, Feb 13, 2013 at 1:26 PM, Michael Malak <[EMAIL PROTECTED]>
> wrote:
>>
>> Is it possible to INSERT INTO TABLE t SELECT FROM where t has a column
>> with a STRUCT?
>>
>> Based on
>>
>> http://grokbase.com/t/hive/user/109r87hh3e/insert-data-into-a-column-of-complex-type
>>
>> I thought perhaps the following would work:
>>
>> echo 1,2 >twovalues.csv
>> hive
>> CREATE TABLE tc (x INT, y INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY
>> ',';
>> LOAD DATA LOCAL INPATH 'twovalues.csv' INTO TABLE tc;
>> CREATE TABLE oc (z STRUCT<a: int, b: int>);
>> INSERT INTO TABLE oc SELECT struct(x,y) FROM tc;
>>
>> but when I do the above I get:
>>
>> FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into
>> target table because column number/types are different 'oc': Cannot convert
>> column 0 from struct<col1:int,col2:int> to struct<a:int,b:int>.
>>
>
>
>
> --
> Dean Wampler, Ph.D.
> thinkbiganalytics.com
> +1-312-339-1330
>

Figured it out fromhttps://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-ComplexTypeConstructors

It should beINSERT INTO TABLE oc SELECT named_struct('a', x, 'b', y) FROM tc;

--- On Wed, 2/13/13, Dean Wampler <[EMAIL PROTECTED]> wrote:

From: Dean Wampler <[EMAIL PROTECTED]>
Subject: Re: INSERT INTO table with STRUCT, SELECT FROM
To: [EMAIL PROTECTED]
Date: Wednesday, February 13, 2013, 12:47 PM

Hmm. I tried the following hacks, but all wouldn't parse. Ideas?
I changed:
  ... select struct(x,y) ... 
to
  ... select struct(x,y) as struct<a:int,b:int> ... 
  ... select cast(struct(x,y) as struct<a:int,b:int>) ...   ... select struct(x as a,y as b) ... 
Okay, but there is a hack that does work; By pass INSERT INTO and just write to the directory:

INSERT DIRECTORY '/path/to/table/directory' SELECT ...;
Just be careful it doesn't clobber any files already there. I'm paranoid, so I would write to a different directory and then move the files over...

dean

On Wed, Feb 13, 2013 at 1:26 PM, Michael Malak <[EMAIL PROTECTED]> wrote:

Is it possible to INSERT INTO TABLE t SELECT FROM where t has a column with a STRUCT?

Based on

http://grokbase.com/t/hive/user/109r87hh3e/insert-data-into-a-column-of-complex-type

I thought perhaps the following would work:

echo 1,2 >twovalues.csv

hive

CREATE TABLE tc (x INT, y INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INPATH 'twovalues.csv' INTO TABLE tc;

CREATE TABLE oc (z STRUCT<a: int, b: int>);

INSERT INTO TABLE oc SELECT struct(x,y) FROM tc;

but when I do the above I get:

FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into target table because column number/types are different 'oc': Cannot convert column 0 from struct<col1:int,col2:int> to struct<a:int,b:int>.

--
Dean Wampler, Ph.D.thinkbiganalytics.com+1-312-339-1330