Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> INSERT INTO table with STRUCT, SELECT FROM


Copy link to this message
-
Re: INSERT INTO table with STRUCT, SELECT FROM
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB