Re: INSERT INTO table with STRUCT, SELECT FROM

2013-02-13 Thread Dean Wampler 
Hmm. I tried the following hacks, but all wouldn't parse. Ideas?

I changed:

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

to

  ... select struct(x,y) as structa:int,b:int ...
  ... select cast(struct(x,y) as structa: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 michaelma...@yahoo.comwrote:

 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 STRUCTa: 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 structcol1:int,col2:int to structa:int,b:int.




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

Re: INSERT INTO table with STRUCT, SELECT FROM

2013-02-13 Thread Michael Malak 
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 dean.wamp...@thinkbiganalytics.com wrote:

From: Dean Wampler dean.wamp...@thinkbiganalytics.com
Subject: Re: INSERT INTO table with STRUCT, SELECT FROM
To: user@hive.apache.org
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 structa:int,b:int ... 
  ... select cast(struct(x,y) as structa: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 michaelma...@yahoo.com 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 STRUCTa: 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 structcol1:int,col2:int to structa:int,b:int.







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

Re: INSERT INTO table with STRUCT, SELECT FROM

2013-02-13 Thread Edward Capriolo 
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
dean.wamp...@thinkbiganalytics.com 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 structa:int,b:int ...
   ... select cast(struct(x,y) as structa: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 michaelma...@yahoo.com
 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 STRUCTa: 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 structcol1:int,col2:int to structa:int,b:int.




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