hivewith（hivewith as建表）

本篇文章给大家谈谈hivewith，以及hivewith as建表对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

• 1、Hive常用命令
• 2、hivewithas不同的作业相同的临时表会有影响吗
• 3、Hive常用查询函数
• 4、Hive sql及窗口函数
• 5、hive中with...as...的用法
• 6、HIVE复杂结构体生成和使用

Hive常用命令

#hive相关资料

#

#

#

#

#获取主机相关信息

export password='qwe'

export your_ip=$(ip ad|grep inet|grep -v inet6|grep -v 127.0.0.1|awk '{print $2}'|cut -d/ -f1)

export your_hosts=$(cat /etc/hosts |grep $(echo $your_ip)|awk '{print $2}'高运)

#安装mysql

echo "mysql-server-5.5 mysql-server/root_password password $password" | debconf-set-selections

echo "mysql-server-5.5 mysql-server/root_password_again password $password" | debconf-set-selections

apt-get -y install mariadb-server python-pymysql --force-yes

echo "[mysqld]

bind-address = $your_ip

default-storage-engine = innodb

innodb_file_per_table

max_connections = 4096

collation-server = utf8_general_ci

character-set-server = utf8" | tee /etc/mysql/conf.d/openstack.cnf

sed -i "s/127.0.0.1/0.0.0.0/g" /etc/mysql/mariadb.conf.d/50-server.cnf

service mysql restart

#创建hive用户和赋予权限

mysql -uroot -p$password EOF

CREATE DATABASE hive;

CREATE USER 'hive' IDENTIFIED BY "$password";

GRANT ALL PRIVILEGES ON  *.* TO 'hive'@'%' WITH GRANT OPTION;

flush privileges;

EOF

#增加hive环境变量

hive_flag=$(grep "hive" /etc/profile)

if [ ! -n "$hive_flag" ]; then

    sed -i "s/\$PATH:/\$PATH:\/opt\/apache-hive-2.3.2-bin\/bin:/g" /etc/profile

else

    echo "Already exist!"

fi

#使脚本中环境变量生效

source /etc/profile

#修改hive配置

echo "$(grep "JAVA_HOME=" /etc/profile)

$(grep "HADOOP_HOME=" /etc/profile)

export HIVE_HOME=/opt/apache-hive-2.3.2-bin

export HIVE_CONF_DIR=/opt/apache-hive-2.3.2-bin/conf" |tee /opt/apache-hive-2.3.2-bin/conf/hive-env.sh

sed -i "s/hadoop3/$your_hosts/g" /opt/apache-hive-2.3.2-bin/conf/hive-site.xml

#在戚弯梁hdfs 中创建下闹肆面的目录 ，并赋予所有权限

hdfs dfs -mkdir -p /user/hive/warehouse

hdfs dfs -mkdir -p /user/hive/tmp

hdfs dfs -mkdir -p /user/hive/log

hdfs dfs -chmod -R 777 /user/hive/warehouse

hdfs dfs -chmod -R 777 /user/hive/tmp

hdfs dfs -chmod -R 777 /user/hive/log

mkdir -p /user/hive/tmp

#初始化hive

schematool -dbType mysql -initSchema

#安装hive到此结束

#######################

#创建hive表

create table film

(name string,

time string,

score string,

id int,

time1 string,

score1 string,

name2 string,

score2 string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ';'

STORED AS TEXTFILE;

#将本地文本导入hive

load data local inpath '/root/my.txt' overwrite into table film;

#hive相关笔记

create table patition_table(name string,salary float,gender string)  partitioned by (dt string,dep string)  row format delimited fields terminated by ',' stored as textfile;

create database movie;

create table movie(name string,data string,record int);

#删除表

DROP TABLE if exists movies;

#创建表

CREATE TABLE movies(

    name string,

    data string,

    record int

) COMMENT '2014全年上映电影的数据记录' FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;

load data local inpath 'dat0204.log' into table movies;

#hive 中使用dfs命令

hive dfs -ls /user/hive/warehouse/wyp ;

select * from movies;

hive -e "select * from test" res.csv 

或者是： 

hive -f sql.q res.csv 

其中文件sql.q写入你想要执行的查询语句 

#导出到本地文件系统

hive insert overwrite local directory '/home/wyp/wyp'

hive select * from wyp;

导出到HDFS中

和导入数据到本地文件系统一样的简单，可以用下面的语句实现：

hive insert overwrite directory '/home/wyp/hdfs'

hive select * from wyp;

将会在HDFS的/home/wyp/hdfs目录下保存导出来的数据。注意，和导出文件到本地文件系统的HQL少一个local，数据的存放路径就不一样了。

#将提取到的数据保存到临时表中

insert overwrite table movies

本地加载  load data local inpath '/Users/tifa/Desktop/1.txt' into table test;

从hdfs上加载数据  load data inpath '/user/hadoop/1.txt' into table test_external; 

抹掉之前的数据重写  load data inpath '/user/hadoop/1.txt' overwrite into table test_external;

[img]

hivewithas不同的作业相同的临时表会有影响吗

会。

在同一个会话不同作用域的时首蠢肆候(例如:大存储过程档谈内含小存储过程的场景),使用同一个临时表名，会引起错乱。

只要把这个@operNum=#Tab.oper_num的字段获取的值注释掉,就可以正常运行，不然运行的时候就一直'提示oper_num列无效',就很纳闷，这个字段是表中存在的，怎么会无效呢。

反复查找原因，发现原来是那个功能使用的存储过程里面有一段执行更新这个表的代码，而刚好在这个更新语句前面有个同样名的#Tab临时表，于是，在执行存储过程事务进程的时候，触发器在事务内就直接使用前面产生的临时表进者轿行赋值操作，而导致列名不匹配，无效。

Hive常用查询函数

函数说明：

NVL ：给值为 NULL 的数据赋值，

它的格式是

NVL( string1, replace_with)。

它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NULL ，则返回NULL。

date_format:格式化时间

date_add:时间跟天数相加

date_sub:时间跟天数相减，类似与add，使用一个就ok

select date_sub('2019-06-29',5);

datediff:两个时间相减

select datediff('2019-06-29','2019-06-24');

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。

分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL， 返回值也将为 NULL。

这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-',str1,str2) from XX; //str为string类型的列名

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，

产生 array 类芹毁型字段。

练习：把星座和血型一样的人归类到一起

第一步转换：

白羊座,A 孙悟空

射手座,A 大海

白羊座,B 宋宋

白羊座,A 猪八戒

射手座,A 凤姐

合并销前

EXPLODE(col)：将hive 一嫌斗备列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW //侧写

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

练习：将电影分类中的数组数据展开

select explode(category) from movie_info;

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

/ 在over(里面使用) /

CURRENT ROW：当前行；

n PRECEDING：往前 n 行数据；

n FOLLOWING：往后 n 行数据；

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDEDFOLLOWING 表示到后面的终点；

/* 在over外面使用*/

LAG(col,n)：往前第n 行数据；

LEAD(col,n)：往后第 n 行数据；

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，

对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据：

一、查询在 2017 年 4 月份购买过的顾客及总人数

group分组一组一个值，over给每一条数据独立开窗

查询顾客的购买明细及购买总额

要将 cost 按照日期进行累加

先排序

再累加

查询顾客上次的购买时间

下次的购买时间

查询前 20%时间的订单信息

RANK() 排序相同时会重复，总数不会变，可以并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

练习：计算每门学科成绩排名

计算每门学科成绩排名

Hive sql及窗口函数

hive函数：

1、根据指定条件返回结果：case when then else end as

2、基本类型转换：CAST（）

3、nvl：处理空字段：三个str时，是否为空可以指定返回不同的值

4、sql通配符：

5、count(1)与COUNT(*)：返回行数

如果表没有主键，那么count（1）比count（*）快；

如果有主键，那么count（主键，联合主键）比count（*）快；

count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。

性能问题：

1.任何情况下SELECT COUNT(*) FROM tablename是最优选择,(指没有where的情况）；

2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；

3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

count(expression)：查询 is_reply=0 的数量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;

6、distinct与group by

distinct去重所有distinct之后所有的字段，如果有一个字段值不一致就不作为一条

group by是根据某一字段分组，然后查询出该条数据的所需字段，可以搭配 where max(time)或者Row_Number函数使用，求出最大的一条数据

7、使用with 临时表名 as() 的形式,简单的临时表直接嵌套进枝段sql中，复杂的和需要复用的表写到临猛行誉时表中，关联的时候先找到关联字段，过滤条件最好在临时表中先过滤后关联

处理json的函数：

split(json_array_string(schools), '\\|\\|') AS schools

get_json_object(school, '$.id') AS school_id,

字符串函数：

1、instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）

instr(sourceString,destString,start,appearPosition)

1.sourceString代表源字符串； destString代表要从源字符串中查找的子串；

2.start代表查找的开始位置，这个参数可选的，默认为1；

3.appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1

4.如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计带枯算。

5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。

最简单例子： 在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置

select instr(‘abcd’,’a’,1,1) from dual; —1

应用于模糊查询：instr(字段名/列名, ‘查找字段’)

select code,name,dept,occupation from staff where instr(code, ‘001’) 0;

等同于 select code, name, dept, occupation from staff where code like ‘%001%’ ;

应用于判断包含关系：

select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)0;

等同于 select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);

2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

substr(time,1,8) 表示将time从第1位开始截取，截取的长度为8位

第一种用法：

substr（string A，int start）和 substring（string A，int start），用法一样

功效：返回字符串A从下标start位置到结尾的字符串

第二种用法：

substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

功效：返回字符串A从下标start位置开始，长度为len的字符串

3、get_json_object(form_data,'$.学生姓名') as student_name

json_tuple 函数的作用：用来解析json字符串中的多个字段

4、split(full_name, '\\.') [5] AS zq;  取的是数组里的第六个

日期(时间)函数：

1、to_date(event_time) 返回日期部分

2、date_sub：返回当前日期的相对时间

当前日期：select curdate() 

当前日期前一天：select  date_sub(curdate(),interval 1 day)

当前日期后一天：select date_sub(curdate(),interval -1 day)

date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14)  将现在的时间总秒数转为标准格式时间，返回14天之前的时间

时间戳日期：

from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 将现在的时间总秒数转为标准格式时间

from_unixtime(get_json_object(get_json_object(form_data,'$.挽单时间'),'$.$date')/1000) as retain_time

unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss')  --1565858400

日期时间戳：unix_timestamp()

date_format：yyyy-MM-dd HH:mm:ss 时间转格式化时间

select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000

1.日期比较函数: datediff语法: datediff(string enddate,string startdate) 

返回值: int 

说明: 返回结束日期减去开始日期的天数。 

举例：  hive select datediff('2016-12-30','2016-12-29');  1

2.日期增加函数: date_add语法: date_add(string startdate, intdays) 

返回值: string 

说明: 返回开始日期startdate增加days天后的日期。 

举例：  hiveselect date_add('2016-12-29',10);  2017-01-08

3.日期减少函数: date_sub语法: date_sub (string startdate,int days) 

返回值: string 

说明: 返回开始日期startdate减少days天后的日期。 

举例：  hiveselect date_sub('2016-12-29',10);  2016-12-19

4.查询近30天的数据

select * from table where datediff(current_timestamp,create_time)=30；

create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00

3、trunc()函数的用法：当前日期的各种第一天,或者对数字进行不四舍五入的截取

日期：

1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18

2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.

上月1号    trunc(add_months(current_date(),-1),'MM')

3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天

4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日

5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天

6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天

7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  

8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

数字：TRUNC（number,num_digits） Number 需要截尾取整的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入

11.select trunc(123.458,1) from dual --123.4

12.select trunc(123.458,-1) from dual --120

4、round()：四舍五入：

select round(1.455, 2)  #结果是：1.46，即四舍五入到十分位，也就是保留两位小数

select round(1.5)  #默认四舍五入到个位，结果是：2

select round(255, -1)  #结果是：260，即四舍五入到十位，此时个位是5会进位

floor()：地板数

ceil()天花板数

5、

6.日期转年函数: year语法:   year(string date) 

返回值: int

说明: 返回日期中的年。

举例：

hive   select year('2011-12-08 10:03:01') from dual;

2011

hive   select year('2012-12-08') fromdual;

2012

7.日期转月函数: month语法: month   (string date) 

返回值: int

说明: 返回日期中的月份。

举例：

hive   select month('2011-12-08 10:03:01') from dual;

12

hive   select month('2011-08-08') fromdual;

8

8.日期转天函数: day语法: day   (string date) 

返回值: int

说明: 返回日期中的天。

举例：

hive   select day('2011-12-08 10:03:01') from dual;

8

hive   select day('2011-12-24') fromdual;

24

9.日期转小时函数: hour语法: hour   (string date) 

返回值: int

说明: 返回日期中的小时。

举例：

hive   select hour('2011-12-08 10:03:01') from dual;

10

10.日期转分钟函数: minute语法: minute   (string date) 

返回值: int

说明: 返回日期中的分钟。

举例：

hive   select minute('2011-12-08 10:03:01') from dual;

3

11.日期转秒函数: second语法: second   (string date) 

返回值: int

说明: 返回日期中的秒。

举例：

hive   select second('2011-12-08 10:03:01') from dual;

1

12.日期转周函数: weekofyear语法:   weekofyear (string date) 

返回值: int

说明: 返回日期在当前的周数。

举例：

hive   select weekofyear('2011-12-08 10:03:01') from dual;

49

查看hive表在hdfs中的位置：show create table 表名;

在hive中hive2hive，hive2hdfs：

HDFS、本地、hive ----- Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;

Hive ---- Hdfs、本地：使用：insert overwrite | local

网站访问量统计：

uv：每用户访问次数

ip：每ip(可能很多人)访问次数

PV:是指页面的浏览次数

VV:是指你访问网站的次数

sql：

基本函数：

count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正则）

and、or、not、in   

where、group by、having、{ join on 、full join}  、order by（desc降序）

sort by需要与distribut by集合结合使用：

hive (default) set mapreduce.job.reduces=3;  //先设置reduce的数量 

insert overwrite local directory '/opt/module/datas/distribute-by'

row format delimited fields terminated by '\t'

先按照部门编号分区，再按照员工编号降序排序。

select * from emp distribute by deptno sort by empno desc;

外部表  create external table if not exists dept

分区表：create table dept_partition ( deptno int, dname string, loc string )  partitioned by ( month string )

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201809'); 

 alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');

多分区联合查询：union

select * from dept_partition2 where month='201809' and day='10';

show partitions dept_partition;

desc formatted dept_partition;

二级分区表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';

分桶抽样查询：分区针对的是数据的存储路径；分桶针对的是数据文件

create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';

设置开启分桶与reduce为1：

set hive.enforce.bucketing=true;

set mapreduce.job.reduces=-1;

分桶抽样：select * from stu_bucktablesample(bucket x out of y on id);

抽取，桶数/y，x是从哪个桶开始抽取，y越大 抽样数越少，y与抽样数成反比，x必须小于y

给空字段赋值：

如果员工的comm为NULL，则用-1代替或用其他字段代替  ：select nvl(comm,-1) from emp;

case when:如何符合记为1，用于统计、分组统计

select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;

用于组合归类汇总(行转列)：UDAF：多转一

concat：拼接查询结果

collect_set(col)：去重汇总，产生array类型字段，类似于distinct

select t.base, concat_ws('|',collect_set(t.name))   from (select concat_ws(',',xingzuo,blood_type) base,name  from person_info) t group by t.base;

解释：先第一次查询得到一张没有按照（星座血型）分组的表，然后分组，使用collect_set将名字组合成数组，然后使用concat将数组变成字符串

用于拆分数据：(列转行)：UDTF：一转多

explode(col)：将hive一列中复杂的array或者map结构拆分成多行。

lateral view  侧面显示：用于和UDTF一对多函数搭配使用

用法：lateral view udtf(expression) tablealias as cate

cate：炸开之后的列别名

temptable ：临时表表名

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

开窗函数：

Row_Number,Rank，Dense_Rank  over：针对统计查询使用

Row_Number：返回从1开始的序列

Rank:生成分组中的排名序号，会在名词s中留下空位。3 3 5

dense_rank：生成分组中的排名序号，不会在名词中留下空位。3 3 4

over：主要是分组排序，搭配窗口函数使用

结果：

SUM、AVG、MIN、MAX、count

preceding：往前

following：往后

current row：当前行

unbounded：unbounded preceding 从前面的起点， unbounded following：到后面的终点

sum：直接使用sum是总的求和，结合over使用可统计至每一行的结果、总的结果、当前行+之前多少行/之后多少行、当前行到往后所有行的求和。

over(rowsbetween 3/current rowprecedingandunboundedfollowing )  当前行到往后所有行的求和

ntile：分片，结合over使用，可以给数据分片，返回分片号

使用场景：统计出排名前百分之或n分之一的数据。

lead,lag,FIRST_VALUE,LAST_VALUE

lag与lead函数可以返回上下行的数据

lead(col,n,dafault) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

使用场景：通常用于统计某用户在某个网页上的停留时间

FIRST_VALUE:取分组内排序后，截止到当前行，第一个值

LAST_VALUE:取分组内排序后，截止到当前行,最后一个值

范围内求和:

cume_dist，percent_rank

–CUME_DIST :小于等于当前值的 行数 / 分组内总行数

–比如，统计小于等于当前薪水的人数，占总人数的比例

percent_rank:分组内当前行的RANK值-1/分组内总行数-1

总结：

在Spark中使用spark sql与hql一致，也可以直接使用sparkAPI实现。

HiveSql窗口函数主要应用于求TopN，分组排序TopN、TopN求和，前多少名前百分之几。

与Flink窗口函数不同。

Flink中的窗口是用于将无线数据流切分为有限块处理的手段。

window分类：

CountWindow：按照指定的数据条数生成一个 Window，与时间无关。

TimeWindow：按照时间生成 Window。

1. 滚动窗口（Tumbling Windows）：时间对齐，窗口长度固定，不重叠：：常用于时间段内的聚合计算

2.滑动窗口（Sliding Windows）：时间对齐，窗口长度固定，可以有重叠：：适用于一段时间内的统计（某接口最近 5min 的失败率来报警）

3. 会话窗口（Session Windows）无时间对齐，无长度，不重叠：：设置session间隔，超过时间间隔则窗口关闭。

hive中with...as...的用法

with...as... 需要定义一个sql片段,会将这个片段产生的结果集保存在内存中,

后续的sql均可以访问这个结果集和,作信游用与视图或临时表类似.

with...as... 的完整格式是这样的

只定义不实用

同级的多个temp之间用 , 分割 with 只需要一次, as 后的子颂唯句必须用 () ,

with...as... 当然是可以嵌套的,此处举一个简单例子

with...as... 只能在一条sql中使用

现有 city 表,结构如下:

然后分别有商品表 good

现在需要分别统计这上海商品,一般sql如下:

除了子查询,上述的的例子还可以用 join 来实现,

如果用 with...as... 语句实现,如下

看起来使用 with...as... 语句反而更复杂一点,但如果 tmp_shanghai 要被多次使用的使用,就很有必要

来看一个实际的例子,有一张操作表 event 主要字段如下:

......

现在要求一条sql统计出 Get 与 Set 操作的数量,先使用子查询实现

如果再增加其他项的统计呢,是否每一个均需要增加一个对 event 表进行扫描的自查询

使用 with...as...

阅读性是否比之前有所提高?此外,这条语句只对event表进野坦培行了一次扫描,将符合条件的数据存入temp中供后续计算,

在event表数据集非常大的情况下,性能将比子查询的方式优秀很多

HIVE复杂结构体生成和使用

test_person样例数据：

biansutao beijing,shanghai,tianjin,hangzhou

linan changchu,chengdu,wuhan

create table  default.test_person(name string,work_locations arraystring)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ',';

LOAD DATA 散段肆 INPATH 'hdfs://sfbdp1/user/xxxx/upload/test_person.txt' OVERWRITE INTO TABLE default.test_person;

select name,work_locations,work_locations[0] from default.test_person limit 10;

insert into table default.test_person select 'AA' as name,array('china','beijing') from dual

select * from  default.test_person limit 10;

test_score样例数据：

AA 数学:90,语文:80,英语:100

BB 数学:90,语文:80,英语:100

drop table default.test_score;

create table default.test_score(name string, score mapstring,int)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ','

MAP KEYS TERMINATED BY ':';

truncate table default.test_score;

load data inpath 'hdfs://sfbdp1/user/xxx/upload/test_score.txt' overwrite into table default.test_score;

select *, score['英语'] from default.test_score limit 10;

create table test_map as select 'CC' as name, str_to_map('唱歌:90,跳舞:80',',',':') as score from dual

select * from test_map limit 10;

其他例子：

CREATE TABLE IF NOT EXISTS default.explode_laterview_org(

    day1_num BIGINT

    ,day2_num BIGINT

    ,day3_num BIGINT

    ,day4_num BIGINT

    ,day5_num BIGINT

    ,day6_num BIGINT

    ,day7_num BIGINT

 冲轿   ,campaign_name STRING

    ,campaign_id BIGINT

);

INSERT OVERWRITE TABLE default.explode_laterview_org VALUES

(40, 20, 10, 4, 4, 2, 1, 'zoo', 2 )

,(100, 80, 53, 40, 7, 6, 5, 'moji', 3)

;

str_to_map使用：

select campaign_id,campaign_name,

STR_TO_MAP(

CONCAT(

'day1_num='燃没,CAST (day1_num AS STRING),

'day2_num=',CAST (day2_num AS STRING),

'day3_num=',CAST (day3_num AS STRING),

'day4_num=',CAST (day4_num AS STRING),

'day5_num=',CAST (day5_num AS STRING),

'day6_num=',CAST (day6_num AS STRING),

'day7_num=',CAST (day7_num AS STRING)

),'', '=') as aa

from default.explode_laterview_org

str_to_map行转列：

select * from (

select campaign_id,campaign_name,

STR_TO_MAP(

CONCAT(

'day1_num=',CAST (day1_num AS STRING),

'day2_num=',CAST (day2_num AS STRING),

'day3_num=',CAST (day3_num AS STRING),

'day4_num=',CAST (day4_num AS STRING),

'day5_num=',CAST (day5_num AS STRING),

'day6_num=',CAST (day6_num AS STRING),

'day7_num=',CAST (day7_num AS STRING)

),'', '=') as aa

from default.explode_laterview_org

) t lateral view explode(aa) mycol as mycol1,mycol2

备注：explode的用法：

explode(ARRAY) 列表中的每个元素生成一行

explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列

explode（array）

select explode(array_col) as new_col from  table_name

array_col：为数组类型的字段

new_col：array_col被explode之后对应的列

explode（map）

map是key-value结构，所以在用explode转换的时候，会转换为2列，一列是key转换而成的，一列是vaule转换而成的

select explode（map_col） as （map_key_col，map_value_col） from table_name

map_col：map类型的字段

map_key_col：是map_col被拆分之后的map映射的key

map_value_col：是map_col被拆分之后的map映射的value

explode局限性：

1）不能关联原有的表中的其他字段

2）不能与group by、cluster by、distribute by、sort by联用。

3）不能进行UDTF嵌套

4）不允许选择其他表达式

lateral view explode() 能解除以上限制：

一个或多个lateral view explode：

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

注意：lateral view explode(array())，当array()为空的时候，查询的结果返回空，而不是查询结果的对应列为空，使用lateral view outer explode（array（）），查询的结果不为空，只有查询的对应列返回的结果为空

test_struct样例数据：

1 english,80

2 math,89

3 chinese,95

CREATE TABLE default.test_struct(id int,course structcourse:string,score:int)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' '

COLLECTION ITEMS TERMINATED BY ',';

load data inpath 'hdfs://sfbdp1/user/xxxx/upload/test_struct.txt' overwrite into table default.test_struct;

select *, course.course,course.score from default.test_struct limit 10

insert into table default.test_struct select '4' as id, named_struct('course','ant','score',100) from dual

select * from default.test_struct limit 10;

生成：

concat('{"barscantm":"',barscantm,

'","opcode":"',opcode

'}') as json_new

使用：

get_json_object( json_new,'$.opcode')

array_contains(字段,'str') 判断字段中是否包含有str字符串

with tables as

(

select 'A' as useid,'1' as pageid

union all

select 'B' as useid,'1' as pageid

union all

select 'A' as useid,'2' as pageid

union all

select 'B' as useid,'3' as pageid

union all

select 'A' as useid,'4' as pageid

union all

select 'B' as useid,'4' as pageid

)

select * from (

select useid,collect_set(pageid) as pageid

from

(

select useid,pageid

from tables

group by useid,pageid

) t

group by useid

) t1

where array_contains(pageid,'1') and array_contains(pageid,'4')

-- 同时访问了pageid为1和4的userid数

sort_array(字段) 对字段进行升序排序

select name,work_locations from default.test_person limit 10;

select name,sort_array(work_locations) from default.test_person limit 10;

select waybill_no,package_no,

packageno

from gdl.tt_waybill_info

lateral view explode(package_no) mycol as packageno

where inc_day='20210519' and size(package_no)1

关于hivewith和hivewith as建表的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。