Hive学习

建表

元数据 ：描述数据的数据

表分类：主要分为内表和外表

内表：元数据和数据本身均被hive管理。删除表则全部删除。

外表：元数据被hive管理，数据本身存储在hdfs,不受hive管理。删除表则只删除元数 据，数据本身不变。

DDL（数据定义语言）

CREATE [external] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [comment col_comment], ...)]
[comment table_comment]
[partitioned by (col_name data_type [comment col_comment], ...)]
[clustered by (col_name, col_name, ...)
[sorted by (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[row format row_format]
[stored as file_format]
[location hdfs_path]

external: 创建内部表还是外部表，此为内外表的唯一区分关键字。

comment col_comment: 给字段添加注释

comment table_comment: 给表本身添加注释

partitioned by: 按哪些字段分区，可以是一个，也可以是多个

clustered by col_name... into num_buckets BUCKETS:按哪几个字段做hash后分桶存储

row format:用于设定行、列、集合的分隔符等设置

stored as : 用于指定存储的文件类型，如text,rcfile等

location : 设定该表存储的hdfs目录，如果不手动设定，则采用hive默认的存储路径

基本操作

show databases；查看所有数据库

show tables; 查看所有表

show create table 表名; · 查看已存在表的详细信息

alter table 表名 rename to 要改成的表名； 更改表

alter table 表名 add columns( age int comment "我是新增的字段") 添加新字段

insert into 表名 partition(分区字段='要分的区名') values(要添加的数据)；新增数据

drop table 表名； 删除表；

show views；查看所有视图

truncate table 表名； 清楚表数据

创建视图

show views; 查看所以视图

cerate view 视图名 as select 要查询的字段 from 表名； 创建视图

drop view 视图名； 删除视图

select * from 视图名； 查看视图

DML（数据管理语言）

加载hdfs文件

LOAD DATA INPATH '要上传的文件路径' OVERWRITE INTO TABLE 表名 PARTITION (come_date=20170904);

将查询结果插入到数据表

insert overwrite table 表名 partition(come_date='20170905')
select
id,username,classid,classname 
from 表名 
where come_date='20170904';

一次查询多次插入

from student
insert overwrite table student partition(come_date='20170906') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170907') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170908') select id,username,classid,classname where come_date='20170905'

动态分区模式

INSERT OVERWRITE TABLE tablename 
PARTITION (col_name) select_statement FROM from_statement

设置非严格模式

set hive.exec.dynamic.partition.mode=nonstric;

外表的说明和使用

· 内外表的唯一区分标识，即为external关键字。创建表时候有，则为外表，没有则默认均为内表。

· 内表和外表的hdfs目录，均可以自由指定location，如不指定，则数据存储在hive的默认hdfs目录中，且后续均可以自由改变，但改变的场景不多，一般为了省事都不会轻易改变存储目录。

• 应用场景
  • 如果表数据是外部load过来的，即为不可hive内部自生成，此时的表必须是外表。
  • 如果表数据是hive内部可以自生成的，即不需要依赖外部的load数据，则此时的表应该是内表。

DQL（数据查询语言）

SELECT [DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[ CLUSTER BY col_list
   | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

DISTRIBUTE BY col_list

以指定字段作为key作hash partition,保证相同的key会到同一个reduce去处理。

Sort By col_list

以指定字段作为单个reduce排序的key，保证单个reduce内的key有序排列输出。

Order By col_list

只会生成一个reduce任务，对全部排序

CLUSTER BY col_list

以指定字段作为key做hash partition，保证相同key会到同一个reduce去处理。该命令相当于distributed by col_list和sort by col_list的联合使用。

基本操作

limit 分页限制

order by 排序 desc降序/asc升序

inner join 将左表和右表满足联接条件的数据，全部查询出来

left outer join 以左表为主，将左表数据全部保留，右表没有关联上数据字段置成NULL

right outer join 以右表为主，将右表数据全部保留，左表没有关联上数据字段置成NULL

full outer join 没有关联上数据字段全部置成NULL,full join=inner join+left join+right join union all 将所有表数据，完全叠加在一起，不去重。要求：所有表的字段和类型完全一致union 将所有表数据，完全叠加在一起，总体去重。要求：所有表的字段和类型完全一致。

Hive 重要说明

rdb sql等值连接

select * from table1 t1,table2 t2  where t1.key=t2.key

对分号敏感性（新版本没有问题）

select concat(key,concat(';',key))  from table1;

hive当中的子查询，必须要给予别名

NULL值判断

hql中用is NULL或者is not NULL来判断字段是否是NULL值,与""没有直接关系

Hive系统函数

show functions 查看所有系统函数

count 统计记录行

if 判断

coalesce 将参数列表中第1个不为null的值作为最后的值

case...when 分支判断

split 将字符串拆分成一个数组

explode：将一个集合元素，打散成一行一行的组成，即将一行改成多行，换句话说行转列

lateral view： 与explode联用，形成一张新表