hive 回顾---字段拆分为 多行+多列

 

一、拆分为多行
 

     
     

       
       

        
        

       
       
       
       

        
        

         
         测试数据：
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         huangbo	a,1:b,2:c,3
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         xuzheng	a,4:b,5:c,6
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         wangbaoqiang	a,7:b,8:c,9
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         ---创建表的语句：
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         create 
         
         table testMap(
         
         name 
         
         string,info 
         
         map<
         
         string, 
         
         int>) 
         
         row 
         
         format 
         
         delimited 
         
         fields 
         
         terminated 
         
         by 
         
         '\t' collection items 
         
         terminated 
         
         by 
         
         ':' 
         
         map 
         
         keys 
         
         terminated 
         
         by 
         
         ',';
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         load 
         
         data 
         
         local inpath 
         
         '/opt/hive_test.txt' 
         
         into 
         
         table testMap;
        
        
       
       
     
     

展示效果：

     
     

       
       

        
        

       
       
       
       

        
        

         
         ---实现行转列---使用lateral view exlode（）
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         case1
         
         ----数据为map
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         select m.name, tf.key, tf.value 
         
         from testMap m lateral 
         
         view explode(m.info) tf 
         
         as 
         
         key,
         
         value;
        
        
       
       
     
     

     
     

       
       

        
        

       
       
       
       

        
        

         
         case2
         
         ----数据为Array
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         数据源：
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         1	子宫病损切除术，经宫腔镜|扩张和刮宫术，诊断性
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         2	胫骨内固定装置去除术|腓骨内固定装置去除术
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         3	腰椎穿刺术|腰椎穿刺术|腰椎穿刺术
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         --创建语句
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         Create 
         
         table testArray(
         
         name 
         
         string, ssmc 
         
         array<
         
         string> ) 
         
         row 
         
         format 
         
         delimited 
         
         fields 
         
         terminated 
         
         by 
         
         '\t' collection items 
         
         terminated 
         
         by 
         
         '|';
        
        
       
       
     
     

--展示结果

     
     

       
       

        
        

       
       
       
       

        
        

         
         --拆分为多行：
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         select 
         
         name,ss.ssmc 
         
         from testArray lateral 
         
         view explode(testArray.ssmc) ss 
         
         as ssmc;
        
        
       
       
     
     

--拆分结果：

case3--数据为String

--拆分为多行：
select name,ss.ssmc from testStr lateral view explode(split(ssmc,"\\|")) ss as ssmc;

--拆分结果

 

二、拆分为多列

     
     

       
       

        
        

       
       
       
       

        
        

         
         二、拆分为多列
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         case1
         
         ---字段数据为Array
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         select 
         
         name,ssmc[
         
         0] 
         
         as ssmc1,ssmc[
         
         1] 
         
         as ssmc2,ssmc[
         
         2] 
         
         as ssmc3 
         
         from testArray;
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         case2
         
         ---字段数据为String
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         select t.name,t.ssmcs[
         
         0] 
         
         as ssmc1,t.ssmcs[
         
         1] 
         
         as ssmc2,t.ssmcs[
         
         2] 
         
         as ssmc3 
         
         from (
        
        
       
       

       
       

        
        

       
       
       
       

        
        
 
        
        
       
       

       
       

        
        

       
       
       
       

        
        

         
         select 
         
         name,
         
         split(ssmc,
         
         "\\|") 
         
         as ssmcs 
         
         from testArray) t
        
        
       
       
     
     

 

<ins class="adsbygoogle"></ins>
</div>
                                    </div>
                                        </div>            </div>
                  </div>
</article>