Hive 的数据类型、HQL

SQL练习

1、count(*)、count(1) 、count('字段名') 区别

2、HQL 执行优先级：

from、where、 group by 、having、order by、join、select 、limit

3、where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists

-- 列出与“SCOTT”从事相同工作的所有员工。
select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");
    
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-03,3000,null,20

select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and exists(
    select  job
    from emp t2
    where ENAME = "SCOTT"
    and t1.job = t2.job
);

4、hive中大小写不敏感

5、在hive中，数据中如果有null字符串，加载到表中的时候会变成 null (不是字符串)

如果需要判断 null，使用 某个字段名 is null 这样的方式来判断

或者使用 nvl() 函数，不能 直接 某个字段名 == null

6、使用explain查看SQL执行计划

explain select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");
    
# 查看更加详细的执行计划，加上extended
explain extended select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");

Hive数据类型

hive读时模式，在读取数据的时候，如果没有数据或者数据的类型不匹配，会显示null

表的结构统称 schema

整型：TINYINT、SMALLINT、INT、BIGINT

TINYINT — 微整型，只占用1个字节，只能存储0-255的整数。
SMALLINT– 小整型，占用2个字节，存储范围–32768 到 32767。
INT– 整型，占用4个字节，存储范围-2147483648到2147483647。BIGINT– 长整型，占用8个字节，存储范围-2^63到263-1。

浮点：FLOAT、DOUBLE

FLOAT– 单精度浮点数

DOUBLE– 双精度浮点数

布尔类型：BOOLEAN (False/True)

TRUE/FALSE

字符串：STRING

STRING– 不设定长度

时间类型：

• 时间戳 timestamp

  Timestamp 格式 “ YYYY-MM-DD HH：MM：SS.fffffffff ”(9位小数位精度)

• 日期 date

  Date DATE值描述特定的年/月/日，格式为YYYY-MM-DD

create table testDate(
    ts timestamp
    ,dt date
) row format delimited fields terminated by ',';

// 2022-02-19 14:24:57.200,2022-02-19

• 时间戳与时间字符串转换

  from_unixtime() -- 时间戳→字符串

  unix_timestamp() -- 字符串→时间戳

// from_unixtime 传入一个时间戳以及pattern(yyyy-MM-dd) 可以将 时间戳转换成对应格式的字符串
select from_unixtime(1630915221,'yyyy年MM月dd日 HH时mm分ss秒')

// unix_timestamp 传入一个时间字符串以及pattern，可以将字符串按照pattern转换成时间戳
select unix_timestamp('2021年09月06日 16时00分21秒','yyyy年MM月dd日 HH时mm分ss秒');
select unix_timestamp('2021-01-14 14:24:57.200')

复杂数据类型：

• array

  数组 有索引

create table testArray(
    name string,
    weight array<string>
)row format delimited 
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';

select name,weight[0] from testArray;

杨老板	140,160,180
张志凯	160,200,180

row format delimited fields terminated by '\t' -- 指定列分割符

COLLECTION ITEMS terminated by ',' -- 指定数组中元素的分割符

weight[0] -- 通过索引获取元素

• map

  key:value,key2:v2,k3:v3

create table scoreMap(
    name string,
    score map<string,int> 
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

select name,score['语文'] from scoreMap;

小明	语文:91,数学:110,英语:40
小红	语文:100,数学:130,英语:140

MAP KEYS TERMINATED BY ':' -- 指定 K V 之间的分割符

score['语文'] -- 通过对应的K获取V

• struct

  结构体 -- 自定义一些属性

create table scoreStruct(
    name string,
    score struct<course:string,score:int,course_id:int,teacher:String> 
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

select name,score.course,score.score from scoreStruct;

小明	语文,91,000001,余老师
小红	数学,100,000002,体育老师

score.course -- 通过 字段名.属性 来获取元素

https://blog.csdn.net/woshixuye/article/details/53317009

Hive HQL

类SQL语言 -- 绝大多数命令与SQL类似

DDL -- 数据定义语言

DQL -- 数据查询语言

DML -- 数据处理语言

DCL -- 数据控制语言

DDL

default库 -- /user/hive/warehouse 在hdfs上的目录

删除库时若该库中有表 -- 级联删除 drop database 库名 cascade;

查看当前所在的数据库 select current_database(); --这里和MySQL中不一样

创建数据库 create database xxxxx;
查看数据库 show databases;
查看当前所在的数据库 select current_database();
删除数据库 drop database tmp;
强制删除数据库：drop database tmp cascade;
查看表：SHOW TABLES;
查看表的元信息：
    desc test_table;
    describe extended test_table;
    describe formatted test_table;
查看建表语句：show create table table_XXX
重命名表：
    alter table test_table rename to new_table;
修改列数据类型：alter table lv_test change column colxx string;
增加、删除分区：
    alter table test_table add partition (pt=xxxx) 
    alter table test_table drop if exists partition(...);

DQL

HQL 执行优先级：from、where、 group by 、having、order by、join、select 、limit

select id,name from tb t where ... and .... group by xxx having xxxx order by xxx asc/desc limit n;

• where ：过滤数据、！！！分区裁剪！！！

• distinct：去重

• join：left join、right join、join 注意MapJoin

  

• group by : 通常结合聚合函数一起使用

• order by：全局排序

  

• sort by：局部排序

  

• distribute by：分区

  

• cluster by = distribute by + sort by

https://zhuanlan.zhihu.com/p/93747613 order by、distribute by、sort by、cluster by详解