hive规则及常用语法

Hive规则及常用语法

一.hive介绍

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

二.Hive规则

1.建表规则

表名使用小写英文以字符串模块名称，项目以BI开头(数据集市以DM)以’_’分割主业务功能块名,然后详细业务名,最后以数据表类别结尾,数据表存放在相应的表空间

例如:

数据仓库层_业务大类_业务小类-***

示例 dw_mobile_start_week

dw_mobile_start_mon

dw_mobile_start_day

1）在建立长期使用的表时，需要给每个字段Column填写Comment栏，加上中文注释方便查看。对于临时表在表名后加上temp;

2）对运算过程中临时使用的表，用完后即使删除，以便及时的回收空间。临时表如果只用来处理一天数据并且每天都要使用不要按时间建分区。避免造成临时表数据越来越大。

3）字段名应使用英文创建，字段类型尽量使用string.避免多表关联时关联字段类型不一致。

4）多表中存在关联关系的字段，名称，字段类型保持一致。方便直观看出关联关系及连接查询。

5）建表时能够划分分区的表尽量划分分区。

6）建表时为表指定表所在数据库中。

2.查询规则

1.多表执行join操作时，用户需保证连续查询中的表的大小从左到右是依次增加的。也

可以使用/*+STREAMTABLES(表别名)*/来标记大表。

2. 对于join，在判断小表不大于1G的情况下，使用map join。如/*+MAPJOIN(表别名) */

来标示。Map表一般选用key分散均匀的小表。

3、对于group by或distinct，设定 hive.groupby.skewindata=true

hive.map.aggr = true。

4. 采用sum() group by的方式来替换count(distinct)完成计算。

5.多表关联时如果关联字段存在字段类型不同。要使用cast把两边字段类型转换成一致，如把数字类型转换成字符串类型。

具体查询的示例可以看第四节hive数据倾斜的优化。

三.Hive基本语法

1创建表：

hive> CREATE TABLE pokes (foo INT, bar STRING);

Creates a table called pokes with two columns, the first being an integer and the other a string

创建一个新表，结构与其他一样

hive> create table new_table like records;

2创建分区表：

hive> create table logs(ts bigint,line string) partitioned by (dt String,country String);

3加载分区表数据：

hive> load data local inpath '/home/Hadoop/input/hive/partitions/file1' into table logs partition (dt='2001-01-01',country='GB');

4展示表中有多少分区：

hive> show partitions logs;

5.展示所有表：

hive> SHOW TABLES;

lists all the tables

hive> SHOW TABLES '.*s';

lists all the table that end with 's'. The pattern matching follows Java regular

expressions. Check out this link for documentation

6.显示表的结构信息

hive> DESCRIBE invites;

shows the list of columns

7.更新表的名称：

hive> ALTER TABLE source RENAME TO target;

8.添加新一列

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

9.删除表：

hive> DROP TABLE records;

10.删除表中数据，但要保持表的结构定义

hive> dfs -rmr /user/hive/warehouse/records;

11.从本地文件加载数据：

hive> LOAD DATA LOCAL INPATH '/home/hadoop/input/ncdc/micro-tab/sample.txt' OVERWRITE INTO TABLE records;

12.显示所有函数：

hive> show functions;

13.查看函数用法：

hive> describe function substr;

14.查看数组、map、结构

hive> select col1[0],col2['b'],col3.c from complex;

15.内连接：

hive> SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

16.查看hive为某个查询使用多少个MapReduce作业