Unable to load managed table with maptype column (complex datatype) from external table in hive

user_14399185 0
Viewed 37

I have external table with complex datatype,(map(string,array(struct))) and I'm able to select and query this external table without any issue. However if I am trying to load this data to a managed table, it runs forever. Is there any best approach to load this data to managed table in hive?

CREATE EXTERNAL TABLE DB.TBL( 
  id string ,
  list map<string,array<struct<ID:string,col:boolean,col2:string,col3:string,col4:string>>> 
) LOCATION <path>
complex-data-types hiveql hdfs hive
1 Answers

BTW, you can convert table to managed (though this may not work on cloudera distribution due warehouse dir restriction):

use DB;
alter table TBLSET TBLPROPERTIES('EXTERNAL'='FALSE');

If you need to load into another managed table, you can simply copy files into it's location.

--Create managed table (or use existing one)

use db;
create table tbl_managed(id string, 
                 list map<string,array<struct<ID:string,col:boolean,col2:string,col3:string,col4:string>>> ) ;

 --Check table location
use db;
desc formatted tbl_managed;

This will print location along with other info, use it to copy files.

Copy all files from external table location into managed table location, this will work most efficiently, much faster than insert..select:

hadoop fs -cp external/location/path/* managed/location/path

After copying files, table will be selectable. You may want to analyze table to compute statistics:

ANALYZE TABLE db_name.tablename COMPUTE STATISTICS [FOR COLUMNS]
user_27003440
Related