hive的行列互转

行转列

多行转多列

 

数据表 row2col
col1   col2    col3
a      c       1
a      d       2
a      e       3  
b      c       4
b      d       5
b      e       6
现在要将其转化为:
col1   c      d      e
a      1      2      3
b      4      5      6
此时需要使用到max(case … when … then … else 0 end),仅限于转化的字段为数值类型且为正值的情况
创建表:
create table row2col(col1 string,col2 string,col3 int)
row format delimited
fields terminated by ',';
加载数据:
load data local inpath '/root/hivedata/row2col.txt' into table row2col;
a,c,1
a,d,2
a,e,3
b,c,4
b,d,5
b,e,6
select col1,
max(case col2 when 'c' then col3 else 0 end) as c,
max(case col2 when 'd' then col3 else 0 end) as d,
max(case col2 when 'e' then col3 else 0 end) as e
from row2col
group by col1;

 

 

多行转单列(重要)

数据表 row2col_1:
col1    col2    col3
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6
将其转化为:
col1    col2    col3
a       b       1,2,3
c       d       4,5,6
此时需要两个内置的函数:
a)concat_ws(参数1,参数2),用于进行字符的拼接 
    参数1—指定分隔符 
    参数2—拼接的内容 
b)collect_set(col3),它的主要作用是将某字段的值进行去重汇总,产生array类型字段
   如果不想去重可用collect_list()
创建表:
create table row2col_1(col1 string,col2 string,col3 int)
row format delimited
fields terminated by ',';
加载数据:
load data local inpath '/root/hivedata/row2col_1.txt' into table row2col_1;
a,b,1
a,b,2
a,b,3
c,d,4
c,d,5
c,d,6

 

select col1, col2, concat_ws('&', collect_set(cast(col3 as string))) as col3
from row2col_1
group by col1, col2;

 

 

列转行

 

多列转多行

数据表 col2row:
col1   c      d      e
a      1      2      3
b      4      5      6
现要将其转化为:
col1   col2    col3
a      c       1
a      d       2
a      e       3
b      c       4
b      d       5
b      e       6
这里需要使用union进行拼接。
union 可以结合多个select语句 返回共同的结果集
保证每个select语句返回的数据类型个数是一致的。
创建表:
create table col2row(col1 string,c int,d int,e int)
row format delimited
fields terminated by ',';
加载数据:
load data local inpath '/root/hivedata/col2row.txt' into table col2row;
a,1,2,3
b,4,5,6
 
select col1, 'c' as col2, c as col3 from col2row
UNION
select col1, 'd' as col2, d as col3 from col2row
UNION
select col1, 'e' as col2, e as col3 from col2row
order by col1, col2;

 

  • 单列转多行(重要)

 

数据表 col2row_2:

col1 col2 col3
a b 1,2,3
c d 4,5,6

 

现要将其转化为:

col1 col2 col3
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6

 

这里需要使用UDTF(表生成函数)explode(),该函数接受array类型的参数,其作用恰好与collect_set相反,实现将array类型数据行转列。explode配合lateral view实现将某列数据拆分成多行。

创建表:

create table col2row_2(col1 string,col2 string,col3 string)
row format delimited
fields terminated by '\t';

加载数据:

load data local inpath '/root/hivedata/col2row_2.txt' into table col2row_2;
a	b	1,2,3
c	d	4,5,6
select col1, col2, lv.col3 as col3
from col2row_2 
lateral view explode(split(col3, ',')) lv as col3;

 

 

转载于:https://www.cnblogs.com/alexzhang92/p/11073392.html

<think>好的,用户现在想找Hive中进行行列转换的示例代码或教程。之前我回答过Spark的底层原理,但用户现在转向Hive行列转换,可能需要具体的SQL示例和应用场景。首先,我需要回顾用户提供的引用内容,看看是否有相关的信息可以利用。 用户提供的引用[1]和[2]都提到了Hive行列转换,特别是行转列(UNPIVOT)和列转行操作。引用[3]则涉及Java API访问Hive的示例,但可能和当前问题关联不大。需要重点参考引用[1]和[2]中的内容。 用户的需求是查找Hive行列转换的示例代码或教程。需要明确Hive行列转换的两种主要类型:行转列和列转行。行转列通常使用聚合函数配合CASE WHEN或collect_list等方法,而列转行则常用explode和lateral view。 接下来,需要检查Hive的版本,因为不同版本支持的函数可能不同。例如,Hive 0.13及以上支持lateral view explode,而更高版本可能有更多优化。同时,用户可能需要实际案例,比如销售数据转置、日志分析等常见场景。 用户可能对语法细节有疑问,比如如何处理多列转换,或者如何处理NULL值。需要确保示例中包含这些情况的处理方式。另外,用户可能还需要了解性能优化,比如在行列转换时如何避免数据倾斜,或者使用哪种存储格式更高效。 考虑到用户之前提到过Hive的教程和综合示例,可能需要提供分步的代码说明,并解释每个步骤的作用。同时,引用中的内容提到了行列转换在数据分析和可视化中的应用,可以结合实际案例说明其重要性。 最后,确保回答结构清晰,分点列出行转列和列转行的示例,并附上相关注意事项和常见问题。需要验证示例代码的正确性,比如测试是否存在语法错误,是否适用于Hive的不同模式(如本地模式或远程模式)。如果有复杂转换,可能需要提到使用UDF或其他高级功能。</think>#### Hive行列转换实现方法与示例代码详解 --- ### 一、行转列(多列合并为单列) **适用场景**:将多个字段的值合并到单个列中,常用于指标横向对比或数据透视表生成。 #### 1. 基础实现(使用`collect_list`/`collect_set`) ```sql -- 原始表结构(用户访问记录) CREATE TABLE user_visits ( user_id STRING, visit_date STRING, page_views INT, click_count INT ); -- 行转列操作 SELECT user_id, visit_date, map( &#39;page_views&#39;, page_views, &#39;click_count&#39;, click_count ) AS metrics_map FROM user_visits; ``` #### 2. 复杂场景(动态列转行) ```sql -- 使用Lateral View+explode处理动态字段 SELECT user_id, visit_date, metric_name, metric_value FROM user_visits LATERAL VIEW explode( map( &#39;page_views&#39;, page_views, &#39;click_count&#39;, click_count ) ) metrics AS metric_name, metric_value; ``` #### 3. 聚合型行转列 ```sql -- 学生成绩表转换示例 SELECT student_id, MAX(CASE WHEN subject = &#39;math&#39; THEN score END) AS math_score, MAX(CASE WHEN subject = &#39;english&#39; THEN score END) AS english_score FROM scores_table GROUP BY student_id; ``` --- ### 二、列转行(单列拆分为多行) **适用场景**:将包含数组/Map类型的字段展开为多行记录。 #### 1. 基础列转行 ```sql -- 原始数据示例(用户标签表) CREATE TABLE user_tags ( user_id STRING, tags ARRAY<STRING> ); -- 使用explode展开数组 SELECT user_id, exploded_tag FROM user_tags LATERAL VIEW explode(tags) tag_table AS exploded_tag; ``` #### 2. JSON格式字段解析 ```sql -- 解析JSON格式的订单商品 SELECT order_id, item.item_id, item.quantity FROM orders LATERAL VIEW explode( from_json( items_json, &#39;array<struct<item_id:STRING,quantity:INT>>&#39; ) ) items AS item; ``` #### 3. Map类型字段展开 ```sql -- 用户特征表转换 SELECT user_id, feature_key, feature_value FROM user_features LATERAL VIEW explode(feature_map) ft AS feature_key, feature_value; ``` --- ### 三、高级转换技巧 #### 1. 多字段联合转换 ```sql -- 同时处理多个数组字段 SELECT user_id, event_type, event_time FROM user_events LATERAL VIEW posexplode(events_type) et AS pos1, event_type LATERAL VIEW posexplode(events_time) etm AS pos2, event_time WHERE pos1 = pos2; ``` #### 2. 嵌套数据结构处理 ```sql -- 处理嵌套数组结构 SELECT department, employee.name, employee.skill FROM company_data LATERAL VIEW explode(employees) emp AS employee LATERAL VIEW explode(employee.skills) skl AS skill; ``` --- ### 四、性能优化建议 1. **数据格式选择**: - 使用ORC格式存储数据,其列式存储特性对行列转换操作有显著加速效果 - 启用压缩:`SET hive.exec.compress.output=true;` 2. **执行参数调优**: ```sql SET hive.vectorized.execution.enabled=true; -- 启用向量化执行 SET hive.optimize.ppd=true; -- 谓词下推优化 ``` 3. **内存管理**: ```sql SET hive.tez.container.size=8192; -- 调整容器内存大小 SET hive.auto.convert.join.noconditionaltask.size=256000000; -- 控制MapJoin阈值 ``` --- ### 五、常见问题解决方案 #### 问题1:`explode`导致数据膨胀 **解决方案**: ```sql -- 使用size()函数预先过滤空数组 SELECT * FROM table WHERE size(data_array) > 0; ``` #### 问题2:转换后字段顺序错乱 **解决方案**: ```sql -- 使用posexplode保持数组索引 SELECT index, exploded_value FROM table LATERAL VIEW posexplode(data_array) adt AS index, exploded_value; ``` #### 问题3:处理NULL值异常 **解决方案**: ```sql -- 使用coalesce函数处理空值 SELECT user_id, coalesce(exploded_value, &#39;N/A&#39;) AS safe_value FROM table LATERAL VIEW explode(data_field) dft AS exploded_value; ``` --- ### 六、综合应用示例 **电商用户行为分析场景**: ```sql -- 原始表结构 CREATE TABLE user_behavior ( user_id STRING, view_products ARRAY<STRING>, cart_products MAP<STRING,INT>, purchase_history ARRAY<STRUCT<product_id:STRING, quantity:INT>> ); -- 多层级列转行操作 SELECT u.user_id, vp.product AS viewed_product, cp.key AS cart_product, cp.value AS cart_quantity, ph.product_id AS purchased_product, ph.quantity AS purchase_qty FROM user_behavior u LATERAL VIEW explode(view_products) vp AS product LATERAL VIEW explode(cart_products) cp LATERAL VIEW explode(purchase_history) ph; ``` --- ### 七、新特性应用(Hive 3.0+) #### 1. **横向聚合(LATERAL VIEW aggregate)** ```sql SELECT order_id, item_summary.total_qty, item_summary.avg_price FROM orders LATERAL VIEW aggregate( transform( items, item -> named_struct(&#39;qty&#39;, item.quantity, &#39;price&#39;, item.price) ) ) item_summary AS total_qty, avg_price; ``` #### 2. **JSON_TUPLE高效解析** ```sql SELECT json_tuple(json_data, &#39;$.name&#39;, &#39;$.age&#39;) AS (name, age) FROM json_table; ``` --- ### 注意事项 1. 使用`lateral view`时需注意OOM风险,建议通过`hive.exec.max.dynamic.partitions`控制分区数量 2. 复杂转换操作建议分步骤创建中间表,避免单语句过于复杂 3. 对大规模数据操作时优先使用Tez执行引擎:`SET hive.execution.engine=tez;` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值