精华内容
下载资源
问答
  • hive转列转行的方法
    2020-12-31 08:58:51

    一、行转列的使用

    1、问题

    hive如何将

    a       b       1

    a       b       2

    a       b       3

    c       d       4

    c       d       5

    c       d       6

    变为:

    a       b       1,2,3

    c       d       4,5,6

    2、数据

    test.txt

    a       b       1

    a       b       2

    a       b       3

    c       d       4

    c       d       5

    c       d       6

    3、答案

    1.建表

    drop table tmp_jiangzl_test;

    create table tmp_jiangzl_test

    (

    col1 string,

    col2 string,

    col3 string

    )

    row format delimited fields terminated by '\t'

    stored as textfile;

    load data local inpath '/home/jiangzl/shell/test.txt' into table tmp_jiangzl_test;

    2.处理

    select col1,col2,concat_ws(',',collect_set(col3))

    from tmp_jiangzl_test

    group by col1,col2;

    二、列转行

    1、问题

    hive如何将

    a       b       1,2,3

    c       d       4,5,6

    变为:

    a       b       1

    a       b       2

    a       b       3

    c       d       4

    c       d       5

    c       d       6

    2、答案

    1.建表

    drop table tmp_jiangzl_test;

    create table tmp_jiangzl_test

    (

    col1 string,

    col2 string,

    col3 string

    )

    row format delimited fields terminated by '\t'

    stored as textfile;

    处理:

    select col1, col2, col5

    from tmp_jiangzl_test a

    lateral  view explode(split(col3,','))  b AS col5

    本文行转列的例子引自:http://my.oschina.net/repine/blog/295961

    更多相关内容
  • 转列: 1.行转列所用的函数 CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串; CONCAT_WS(separator, str1, str2,…):它是一个特殊形式的 CONCAT()。第一个参数作为剩余...

    行转列:
    1.行转列所用的函数
    CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;

    CONCAT_WS(separator, str1, str2,…):它是一个特殊形式的 CONCAT()。第一个参数作为剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

    COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段(也就是将一列字段去重以后放在一个数组中)。

    在这里插入图片描述

    案例分析:原数据为用户的姓名,住址,年龄(中间为\t分割)。

    在这里插入图片描述

    目标数据:(先将住址和年龄合并成一列数据,再将住址和年龄相同的用户放到一起形成如下的表)

    在这里插入图片描述

    思路分析:1.先将住址和年龄合并成一列数据
    在这里插入图片描述

    2.按照住址和年龄分组,然后用COLLECT_SET(col)函数将用户名变为一行。

    实际操作:
    1.建表:

    create table usertab(
    name string, 
    address string, 
    age string) 
    row format delimited fields terminated by "\t";
    

    2.导入数据并查询:

    load data local inpath '/opt/demo.txt' into table usertab;
    

    在这里插入图片描述

    接上面思路分析第一步:先将住址和年龄合并成一列数据

    select name,concat_ws(',',address,age) address_age from usertab;
    

    在这里插入图片描述
    第二步:按照住址和年龄分组,然后用COLLECT_SET(col)函数将用户名变为一行,最后用concat_ws()函数来连接用户名字。

    select address_age,concat_ws('-',collect_set(name)) from (select name,concat_ws(',',address,age) address_age from usertab) t group by address_age;
    

    最终结果:

    在这里插入图片描述

    列转行:

    EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
    LATERAL VIEW
    用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
    解释:用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。

    案例:原数据为姓名和喜欢吃的水果(\t分割字段)

    在这里插入图片描述

    目标数据,将原数据中每个人喜欢吃的水果分成多行,如下为目标数据。

    在这里插入图片描述

    1.创建表并且导入数据

    create table fruit(
        name string,
        like_fruits  array<string>)
    row format delimited fields terminated by "\t"
    collection items terminated by ",";
    
    load data local inpath "/opt/fruit.txt"  into table fruit;
    

    在这里插入图片描述

    查询数据的sql

    select
        name,
        like_fruits_name
    from
        fruit lateral view explode(like_fruits) table_tmp as like_fruits_name;
    

    在这里插入图片描述

    这里需要注意的是函数explode(like_fruits)的别名是在as 后面,table_tmp为explode函数将like_fruits字段中的值分解后形成的表。

    展开全文
  • hive转列转行

    千次阅读 2020-11-15 21:41:46
    第一部分:行转列* 1.简单行转列:将一个融合多个信息的字段拆分成多行 步骤:先用split切开按照,进行分隔成数组的形式 ["aaa","bbb","ccc"] 然后再用explode函数炸开,将一行分成多行 (1)将一个融合多个...

    **

    第一部分:行转列*

    • 1.简单行转列:将一个融合多个信息的字段拆分成多行
    步骤:先用split切开按照,进行分隔成数组的形式 ["aaa","bbb","ccc"]
    	  然后再用explode函数炸开,将一行分成多行
    
    (1)将一个融合多个信息的字段拆分为多行	
    
    结果展示:aaa,bbb,ccc  ===>  aaa
                       bbb
                       ccc
    
    	(2)实现方法:利用内置UDTF函数explode实现
    
    结果展示:
    split('aaa,bbb,ccc',','); //根据逗号进行分割,将复合字段转化为hive数组类型
    explode(split('aaa,bbb,ccc',',')); //对得到的数据使用explode函数拆分为多行
    
    
    	(3)实现结果
    
    hive> select split('aaa,bbb,ccc',',');
    OK
    ["aaa","bbb","ccc"]
    ----------------------------------------------
    hive> select explode(split('aaa,bbb,ccc',','));
    OK
    aaa
    bbb
    ccc
    

    2.复杂行转列
    将转换后的多行信息与原表字段关联查询
    lateral view侧视图即产生一张虚拟表
    explode(split(hobby,’,’))temp as x此与句本身返回的就是一张虚拟表,记为temp,可以为temp表的列指定字段名X
    student lateral view temp 使用lateral view可以直接关联两张表,保持原有的映射关系
    select后边的是字段,我们炸开的是原student表的hobby字段,此字段炸开后就可以用lateral view形成一个与原表字段连接的桥梁
    select student.name.temp.x
    from student lateral view explode(split(hobby,’,’)) temp as x;//完整的
    实例
    (1)需求:将转换后的多行信息与原表字段关联查询。
    比如:原始数据student表

    name hobby
    tom 唱歌,跳舞,弹琴
    jack 唱歌,绘画,打篮球
    

    (2)转换后数据

    tom 唱歌
    tom 跳舞
    tom 弹琴
    jack 唱歌
    jack 绘画
    jack 打篮球
    

    (3)拆分后的字段与原表的name字段是没有办法直接关联的,因为它们不是一张表。可以使用later view侧视图进行关联查询

    explode(split(hobby,',')) temp as x   //此语句本身返回的就是一张虚拟表 记为temp 可以为temp表的列指定字段名x
    student lateral view temp     //使用later view可以直接关联两张表,保持原有映射关系
    select student.name,temp.x from student lateral view explode(split(hobby,',')) temp as x;//完整写法
    

    (4)创建student表并加载数据

    create external table student
    (
       name string,
       hobby string
    ) row format delimited fields terminated by ' '; 
    load data local inpath '/root/student.txt' into table student;
    

    (5)执行sql语句 查看结果

    hive> select student.name,temp.x from student lateral view explode(split(hobby,',')) temp as x;
    OK
    tom     唱歌
    tom     跳舞
    tom     弹琴
    jack    唱歌
    jack    绘画
    jack    打篮球
    

    **

    第二部分 列转行

    **
    1.简单列转行
    将多行(一列)数据融合为一行
    collect_list不去重
    collect_set(column)将某一列所有的数据转化为一个集合(去重)
    concat_ws(’,’,collect_set(column))将集合中的所有元素以,号分割连接为字符串
    实例:

    aaa   ===>   aaa,bbb,ccc
    bbb
    ccc
    

    实现方法:

    collect_set(column) //将某一列所有数据转化为一个集合(去重)
    concat_ws(',',collect_set(column))  //将集合中所有元素以逗号分割连接为字符串
    

    将dept表的dep列转为一行 展示结果

    select dep from dept;
    销售
    测试
    开发
    算法
    --------------------------------------------------------
    select concat_ws(',',collect_set(dep)) from dept;
    销售,测试,开发,算法
    

    此外还有一个聚合函数 collect_list 不去重

    2.复杂列转行
    转换后的一行信息与原始行字段关联查询
    实例:
    (1)原始数据

    name hobby
    tom 唱歌
    tom 跳舞
    tom 弹琴
    jack 唱歌
    jack 绘画
    jack 打篮球
    

    (2)结果数据

    tom 唱歌,跳舞,弹琴
    jack 唱歌,绘画,打篮球
    

    (3)根据name字段进行分组,在每个分组内部name是唯一的可以直接查询,hobby字段就可以使用collect_set与concat_ws进行连接为复合字段。
    创建stu表导入数据

    create external table stu
    (
       name string,
       hobby string
    ) row format delimited fields terminated by ' '; 
    load data local inpath '/root/stu.txt' into table stu;
    

    (4)查询

    查询:select name,concat_ws(,,collect_set(hobby)) from stu group by name;
    

    (5)结果如下

    OK
    jack    唱歌,绘画,打篮球
    tom     唱歌,跳舞,弹琴
    
    展开全文
  • ="" // hive在split(空格)的时候会把两个空格中间视为"" )T """).show() >>> 30386387 方法2:explode之 sparkSQL-API friends.select($"user".alias("user_id"),explode(split($"friends"," ")).alias("friends_1")...

    一、方法1:with

    2017年月度 各店铺 单客户平均消费(店铺消费/店铺unique客户数量)

    环比:每个月和上一个月比

    withtabas(selectdate,store_id,avgCusPrice-lastAvgPrice priceDifffrom(selectdate,store_id,avgCusPrice,

    lag(avgCusPrice,1,0) over(partition by store_id order bydate) lastAvgPricefrom(select totalprice/cusCount avgCusPrice,date,store_idfrom(select count(distinct customer_name) cusCount,sum(price) totalprice,date,store_idfrom(selectcustomer_name,price

    ,from_unixtime(unix_timestamp(date,'yyyy-MM-dd'),'yyyy-MM') date,store_idfromtransaction_details

    )Tgroup bydate,store_id

    )T

    )T

    )T

    )selectt4.store_id,

    t4.priceDiffasdiff4,

    t5.priceDiffasdiff5,

    t6.priceDiffasdiff6from(selectstore_id,priceDifffromtabwheredate= "2017-04"

    )t4inner join tab t5 on t4.store_id=t5.store_id and t5.date="2017-05"inner join tab t6 on t4.store_id=t6.store_id and t6.date="2017-06"

    二、方法2:case when

    例1

    selectstore_id,max(case date when'2017-04' then priceDiff else null end) as`4月diff`,max(case date when'2017-05' then priceDiff else null end) as`5月diff`,max(case date when'2017-06' then priceDiff else null end) as`6月diff`,max(case date when'2017-07' then priceDiff else null end) as`7月diff`from(selectdate,store_id,avgCusPrice-lastAvgPrice priceDifffrom(selectdate,store_id,avgCusPrice,

    lag(avgCusPrice,1,0) over(partition by store_id order bydate) lastAvgPricefrom(select totalprice/cusCount avgCusPrice,date,store_idfrom(select count(distinct customer_name) cusCount,sum(price) totalprice,date,store_idfrom(selectcustomer_name,price

    ,from_unixtime(unix_timestamp(date,'yyyy-MM-dd'),'yyyy-MM') date,store_idfromtransaction_details

    )Tgroup bydate,store_id

    )T

    )T

    )T

    )Tgroup by store_id

    例2

    stu_id, question_id, score分为 1 0 0.5,对应 right,error,half。需要根据stu_id求他们每人right,error,half的题目集合

    思路1

    selectstudent_id,

    concat_ws('',collect_list(t.right)) right,

    concat_ws('',collect_list(t.half)) half,

    concat_ws('',collect_list(t.error)) errorfrom(selectstudent_id,case when score=1.0 then concat_ws(",",collect_list(question_id)) else null end right,case when score=0.5 then concat_ws(",",collect_list(question_id)) else null endhalf,case when score=0.0 then concat_ws(",",collect_list(question_id)) else null enderrorfromex_exam_recordgroup bystudent_id,score

    ) tgroup by student_id;

    思路2

    selectstudent_id,

    concat_ws(',',collect_list(`right`)) as `right`,

    concat_ws(',',collect_list(`half`)) as`half`,

    concat_ws(',',collect_list(`error`)) as`error`from(selectstudent_id,max(case score when 0 then question_id else null end) as`error`,max(case score when 1 then question_id else null end) as `right`,max(case score when 0.5 then question_id else null end) as`half`fromex_exam_recordgroup bystudent_id,question_id

    )Tgroup by student_id

    selectddd.d_date,sum(case when datediff('2018-10-20',ddd.d_date)<=1 then 1 else 0 end) two_countfromdw_sales_source.dwd_fact_sales_order dfoinner join dwd_dim_date ddd on dfo.date_sk =ddd.date_skinner join dwd_dim_customer ddc on dfo.customer_sk =ddc.customer_skinner join dwd_dim_product ddp on dfo.product_sk =ddp.product_skwhere ddd.d_date>='2018-10-19' and ddd.d_date<='2018-10-20'

    group byddd.d_date;selectddd.d_date,case when datediff('2018-10-20',ddd.d_date)<=1 then 1 else 0 endtwo_count # 近两天的order次数fromdw_sales_source.dwd_fact_sales_order dfoinner join dwd_dim_date ddd on dfo.date_sk =ddd.date_skinner join dwd_dim_customer ddc on dfo.customer_sk =ddc.customer_skinner join dwd_dim_product ddp on dfo.product_sk =ddp.product_skwhere ddd.d_date>='2018-10-19' and ddd.d_date<='2018-10-20'

    group byddd.d_date;

    case when 后的d_date在group by 后面,“10-19 1”“10-19 1”分组加起来“10-19 出现次数”

    思路3

    只groupby stuid,是否就能够分类出对应的question_id集合?

    答:不行

    三、方法3:Lateral view explode

    val friends = spark.read.format("csv").option("header","true").load("hdfs://192.168.56.111:9000/party/data/user_friends.csv")

    方法1:lateral view explode之 hive

    friends.createOrReplaceTempView("friends")

    spark.sql("""select

    count(1)from(select distinct user,friends_1fromfriends

    LATERALVIEW explode(split(friends,' '))friends asfriends_1where friends_1 is not null and friends_1 !="" // hive在split(空格)的时候会把两个空格中间视为""

    )T

    """).show()

    >>> 30386387

    方法2:explode之 sparkSQL-API

    friends.select($"user".alias("user_id"),explode(split($"friends"," ")).alias("friends_1")).filter("friends_1 is not null").distinct.count

    >>> 30386387

    结果都是:

    展开全文
  • 1、转行1.1 相关函数的说明:concat(string1,string,...) //连接括号内字符串,数量不限。concat_ws(separator,string1,string2,...) //连接括号内字符串,数量不限,连接符为separator。collect_set(col) //此...
  • Hive转列详解

    2020-12-31 08:58:51
    需求:孙悟空 白羊座 A沙悟净 射手座 A宋松松 白羊座 B猪八戒 白羊座 A小凤姐 射手座 A转换成以下格式:白羊座,A 孙悟空|猪八戒白羊座,B 宋松松射手座,A 沙悟净|小凤姐思路:表的行转列知识点:concat(string1,...
  • Hive常用函数之行转列转行函数

    千次阅读 2021-01-13 17:09:09
    一、行转列函数将类似于下面的形式:ABC转化为类似于下面的形式:A B C即将多行转化为一行,排在一行了,就成了一concat函数:select concat('A','B','C');concat_ws函数:select concat_ws('|',array('A','B','C'...
  • Hive转列实现Pivot

    2021-01-13 17:09:11
    前言传统关系型数据库中,无论是Oracle(11g之后)还是SQLserver(2005之后),都自带了Pivot函数实现行转列功能,本文主要讲述在Hive中实现行转列的两种方式。传统数据库方式这种方式是借鉴在Oracle或者SQLserver在支持...
  • 一般写sql经常会遇到行转列或者转行之类的操作,就像concat_ws之类的函数被广泛的使用,今天这个也是经常要使用的拓展方法。Lateral View 语法描述横向视图与用户定义的表生成函数(如explode())结合使用。 如内置...
  • 1。问题hive如何将a b 1,2,3c d 4,5,6变为:a b 1a b 2a b 3c d 4c d 5c d 6答案如下:2。原始数据:test.txta b 1,2,3c d 4,5,63。解决...
  • 1.行转列1.1 问题引入:如何将a b 1,2,3c d 4,5,6变为:a b 1a b 2a b 3c d 4c d 5c d 61.2 原始数据:test.txta b 1,2,3c d 4,5,61...
  • 1、演示多转为单行数据文件及内容: student.txtxiaoming|english|92.0xiaoming|chinese|98.0xiaoming|math|89.5huahua|chinese|80.0huahua|math|89.5创建表studnet:create table student(name string,subject ...
  • 1。问题hive如何将a b 1,2,3c d 4,5,6变为:a b 1a b 2a b 3c d 4c d 5c d 6答案如下:2。原始数据:test.txta b 1,2,3c d 4,5,63。解决...
  • 一个小知识点-Hive转列实现Pivot

    千次阅读 2019-04-24 01:00:00
    传统关系型数据库中,无论是Oracle(11g之后)还是SQLserver(2005之后),都自带了Pivot函数实现行转列功能,本文主要讲述在Hive中实现行转列的两种方式。 传统数据库方式 这种方式是借鉴在Oracle或者SQL...
  • Hive 行列转换

    2020-12-31 08:58:49
    一、转行(对某拆分,形成新)使用函数:lateral view explode(split(column, ',')) numeg: 如表:t_row_to_column_tmp 数据如下,对tag进行拆分SQL代码:select id,tag,tag_newfrom t_row_to_column_...
  • 传统关系型数据库中,无论是Oracle(11g之后)还是SQLserver(2005之后),都自带了Pivot函数实现行转列功能,本文主要讲述在Hive中实现行转列的两种方式。 一、关系型数据库方式 如果使用mysql\oracle对应的pivot...
  • 在内部地处理上,作业系统会决定哪一些hive可包含这个机码,而系统则负责存取适当的文件。    图5-1  使用RegEdit.exe所显示的Windows 2000登录结构 Windows 2000之...
  • 5. 系统登录

    千次阅读 2007-03-28 04:42:00
    在内部地处理上,作业系统会决定哪一些hive可包含这个机码,而系统则负责存取适当的文件。    图5-1  使用RegEdit.exe所显示的Windows 2000登录结构 Windows 2000之...

空空如也

空空如也

1 2
收藏数 22
精华内容 8
关键字:

hive中实现行转列