hive 去重 字符串_hive sql教程

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

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这个分区插入数据