java行转列性能不好 行转列列转行hive

1、建表

create table tmp_test
(col1 string,
col2 string,
bian string)
row format delimited fields
terminated by '\t'
stored as textfile;

2、构建测试数据

vi row_to_col.txt

 

 

 3、加载数据至数据库

load data local inpath '/root/test/row_to_col.txt' into table tmp_test;

4、执行转换

方法一：该种转换只有新值，无对应索引

set hive.execution.engine=spark;　　-- 该步骤为设置hive执行引擎，如未安装spark可以不写
select
    col1,
    col2,
    col3_new
from crisps_test.tmp_test a
lateral view explode(split(bian, ',')) b AS col3_new;

 

 

 

方法二：该种转换不仅有新值，还有对应索引

set hive.execution.engine=spark;　　-- 该步骤为设置hive执行引擎，如未安装spark可以不写

select 
　　col1, 
　　col2, 
　　pos, 
　　val 
from crisps_test.tmp_test a 
lateral view posexplode(split(bian, ',')) a as pos,val