hql三

1. 请创建Hive分区表表名为sockinfo，按照行业进行分区,分区字段名自定义。

create table sockinfo_tmp(

  id int,

  stockno string,

  stockname string,

  trade string,

  platform string,

  volume int)

row format delimited fields terminated by ','

stored as textfile;

 

load data local inpath '/home/test/hive-2.3.7/data/gupiao.txt' into table sockinfo_tmp;

 

create table sockinfo(

  id int,

  stockno string,

  stockname string,

  trade string,

  platform string,

  volume int)

partitioned by (trade_p string)

row format delimited fields terminated by ','

stored as textfile;

 

1. 将以上信息导入到Hive表中。

insert into sockinfo partition(trade_p) select *,

case when  trade='5G通讯' then '5Gtongxun' when  trade='医疗' then 'yiliao' 

when  trade='环保' then 'huanbao'   when  trade='高端制造' then 'gaoduanzhizao'

else 'guofangjungong' end as trade_p from sockinfo_tmp;

 

1. 按交易平台统计交易额前3的记录。

select * from (select *,row_number() over(partition by platform order by volume desc) rank from sockinfo)t where t.rank<=3;

3)创建Hive自定义函数，功能是根据平台区分平台性质同花顺,钱龙 为国营，其余为私营。（核心）

public class TypeUDF extends UDF {

    public String evaluate(String platform) {

        String type = null;

        if ("同花顺".equals(platform) || "钱龙".equals(platform)) {

            type = "国营";

        } else {

            type = "私营";

        }

        return type;

    }

}

4)查询5G通讯分区对应的所有数据，信息包括：股票号 股票名称 行业 交易平台 ,平台性质。（6分）

select *,getType(platform) type from sockinfo where trade_p='5Gtongxun';