Hive Sql 入门教程

前置教程
hive库表知识
hive库是表的一个集合，一个库拥有多个表，hive整个数据库拥有多个库。
hive表代表一个对象，比如一个人设计为一个表就有身高体重等属性，在hive
中实际存储表现为每张表会有一个存储地址，比如表名为people，实际存储会有
/xx/xx/people,hive中引用了一个很重要的概念分区，分区实际上也是这个表的字段，反映在
实际存储中通常以天为分区，存储在址为/xx/xx/people/2019-01-01/ 相当于把01号的数据只在写在
/xx/xx/people/2019-01-01/这个目录下，目的是为了减少hive处理时的数据量
表的字段有多种数据类型
int 数值 12
float 浮点数（就有是小数这种）12.22
string 字符串类型 哄哄
timestramp 时间戳类型 2019-01-01 12：23：42
这几种类型基本能覆盖90%的场景
1.* 代表查询所有字段
select * from tablea
2.查询指定字段
select name,age from tablea
3.limit 限制查询条数
select name,age from tablea limit 10
4.where 代表限定条件
select name,age from tablea where name='honghong'
5.where 后面加多个条件
select name,age from tablea where name='honghong' and age=123
6.查询条件是字符串的加上''
select * from tablea where name='honghong'
7.查询条件是数值,
select * from tablea where age=12
8.对于分区，如何判断是不是分区表，执行这个命令show partitions 表名,
如果不报错的话，能看到结果比如dt=2019-01-01 dt就代表分区字段
证明存在分区2019-01-01,需要看下表结构分区字段的数据类型基本上都是字符串类型
9.查询分区数据
select * from tablea where dt='2019-01-01'
10.查询多个分区的数据
select * from tablea where dt>'2019-01-01' and dt<'2019-01-10'
11.distinct的用法，distinct 的用途就是去重
表数据
age name
12 honghong
12 honghong
select distinct age,name from tabela
只能查出来一条数据
12，honghong
distinct 只能出现在最前面
13.group by 的用法
group by 翻译成中文就是分组做一些运算,通常与聚合函数配合使用
select city,sum(money) fron tablea group by city
翻译成中文就是按城市，求和
select sum(money) from tablea
对所有数据求和
常见的出错写法
select city,sum(money) fron tablea
select sum(money) from tablea group by city
聚合函数还有max(),min(),count()
14.order by 对数据排序
select id,name from tablea order by id
对数据按id进行排序，默认是按升序，如果要按降序进行在最后加一个desc
在hive中用了order by 要加limit
15.like的用法，like主要用于模糊匹配
select * from tablea where name like '%honghong%'
查找name中含有honghong这个字的数据
16.in关键字的用法
select name from table where name in ('honghong','dou')
查询name 等于 honghong,dou的数据
17.between and的用法
select id,name from table where id between 12 and 23
包括12,23
18.笛卡尔积
demo
tablea
id name
1 honghong
2 ma
id age
1 12
1 23

select * from tablea,tableb
这样会产生2*2条数据，通常会要tablea.id=tableb.id这种类型的
select * from tablea,tableb
where tablea.id=tableb.id
and tableb.age=12
where条件是最后的操作，从四条数据中选出符合条件的,这种
通常会产生巨大的中间结果，不建议

19.join
join 按照条件把数据连接起来
以18为例
select * from
tablea
join
tableb
on tablea.id=tableb.id
where tableb.age=12
这种只会产生两条中间数据
id name id age
1 honghong 1 12
1 honghong 1 23
where 的顺序在on后面这样只会处理两条数据

20.left join

select * from
tablea
left join
tableb
on tablea.id=tableb.id
left join 会把左表的数据全查出来
以18的数据为例
id name id age
1 honghong 1 12
1 honghong 1 23
2 ma null null

21.right join
right join 会把右表的数据全查出来
select * from
tablea
right join
table
on tablea.id=tableb.id

22.case when 的用法
select case when id=1 then '北京'
when id=2 then '上海'
else '天津' end as city
from
tablea
23.count()用法
count通常用来计数
select count(*) from tablea
tablea的记录数

select count(if(id=1,true,null)) from tablea
查询 id=1的记录数
select count(distinct id) fron tablea
先对id 进行去重再统计数量
24.if的用法
select if(id=1,'北京','上海') from talbea
如果id=1这个值为北京，否则为上海

25.and
and 代表多个条件都要满足
if(tc.job_type='IMPORT' AND job_accepted_time>3,'Y','N')
满足两个条件的话，值为Y,否则为N

26or
or代表满足其中一个
if(tc.job_type='IMPORT' or job_accepted_time>3,'Y','N')
只要满足一个条件，值为Y，否则为N

以上都是基本操作
发现的有几个问题
当有分区表和left join 时
select *
from tablea
left join
tableb on
tablea.id=table.id
写成下面这种子查询的方法

select
*
from
(select * from tablea
where dt='2019-01-01') ta

left join

(select * from tablea
where dt='2019-01-01') tb
on ta.id=tb.id
容易犯的错误

最外面用的字段，一定要先在子查询中查出来
group by 与聚合函数一定要配合使用

27 union all

把数据合起来,条件是字段名与类型必须相同
select id,name from tablea
union all
select id,namea as name from tableb

写sql注意理清结构，需要什么数据，在哪个表里，是不是分区表，
范围是多少，一段一段写

hive日期函数地址
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

返回类型 函数 描述
int day(string date) dayofmonth(date) Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1.

附加操作
数据插入
tabela 的结构为
id int
name string

1.插入多条数据
insert into table 表名
values(1,'honghong'),(2,'ma')

2.插入覆盖之前的数据

insert overwrite table 表名
values(1,'honghong'),(2,'ma')
会把之前的历史数据覆盖掉

3.查询插入
从表中把数据查出来插入到表a中
insert into tablea select * from tableb

4.对分区表中插入数据
insert overwrite table 表名 partition(dt='2019-01-01')
对2019-01-01这个分区插入数据