hive系列（1）

一、概述

理解下hive整体流程：

二、Hive的数据存储

1、Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）
2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。
3、Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。
1）、db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
2）、table：在hdfs中表现所属db目录下一个文件夹
3）、external table：外部表, 与table类似，不过其数据存放位置可以在任意指定路径
普通表: 删除表后, hdfs上的文件都删了
External外部表删除后, hdfs上的文件没有删除, 只是把文件删除了
4）、partition：在hdfs中表现为table目录下的子目录
5）、bucket：桶, 在hdfs中表现为同一个表目录下根据hash散列之后的多个文件, 会根据不同的文件把数据放到不同的文件中。

三、Hive thrift服务

原理图

启动方式，（假如是在mini01上）：
启动为前台：bin/hiveserver2
启动为后台：nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &

启动成功后，可以在别的节点上用beeline去连接
方式（1）
hive/bin/beeline 回车，进入beeline的命令界面
输入命令连接hiveserver2
beeline> !connect jdbc:hive2//mini1:10000
（mini01是hiveserver2所启动的那台主机名，端口默认是10000）
方式（2）
或者启动就连接：
bin/beeline -u jdbc:hive2://mini01:10000 -n root

接下来就可以做正常sql查询了

四、常用命令

DDL操作

1.创建表

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]

说明：1、  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。2、  EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。3、  LIKE 允许用户复制现有的表结构，但是不复制数据。4、  ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]         [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]    | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。5、  STORED AS SEQUENCEFILE|TEXTFILE|RCFILE如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。6、CLUSTERED BY对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。 把表（或者分区）组织成桶（Bucket）有两个理由：（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

表创建实例

1、创建内部表

create table if not exists mytable(sid int,sname string)row format delimited fields terminated by ',' stored as textfile;

2、创建外部表

create external table if not exists pageview(pageid int,page_url string comment 'The page URL')row format delimited fields terminated by ','location 'hdfs://mini01:9000/user/hive/warehouse/tianjun'

3、创建分区表

create table student p(Sno int,Sname string,Sex string,Sage int,Sdept string) partitioned by(part string) row format delimited fields terminated by ',' stored as textfile;

4、创建带桶的表

create table student(id int,name string)partitioned by (stat_date string)clustered by (id) sorted by(age) into 2 bucketsrow format delimited fields terminated by ',';

表修改实例

增加或修改分区

语法结构

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION ‘location1’ ] partition_spec [ LOCATION ‘location2’ ] …

partition_spec:

PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, …)
ALTER TABLE table_name DROP partition_spec, partition_spec,…
具体实例如下：

alter table student_p add partition(part='a') partition(part='b');

显示某表的分区

 show partitions studens;

重命名表
语法结构
ALTER TABLE table_name RENAME TO new_table_name

增加/更新列
语法结构
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

显示命令
show tables
show databases
show partitions
show functions
desc extended t_name;
desc formatted table_name;

DML操作

Load

语法结构

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

说明：
1、 Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
2、 filepath：
相对路径，例如：project/data1
绝对路径，例如：/user/hive/project/data1
包含模式的完整 URI，列如：
hdfs://namenode:9000/user/hive/project/data1
3、 LOCAL关键字
如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。
如果没有指定 LOCAL 关键字，则根据inpath中的uri查找文件
4、 OVERWRITE 关键字
如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

Insert

将查询结果插入Hive表

语法结构
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement
Multiple inserts:
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION …] select_statement2] …
Dynamic partition inserts:
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] …) select_statement FROM from_statement

导出表数据
语法结构
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT … FROM …
实例如下：

 insert overwrite local directory '/root/tianjun/student' select * from student;

insert overwrite directory 'hdfs://mini01:9000/user/tianjun/student'select * from student;

hive导出查询文件到本地文件的2种办法通过HQL语句可以将hive  中表的数据生成到指定的目录。有时候 我们可以利用hive来生成统计的中间文件（比源文件小的多的）方法有如下2种：1.INSERT OVERWRITE LOCAL DIRECTORY将结果输出到指定的目录：生成的文件数 和redurcer的数目的一样的在hive下面执行INSERT OVERWRITE LOCAL DIRECTORY '/hive_dat/package_name'select package_name,count(1) from app_list group by package_name;2.直接查询生成结果文件：在linux下面执行:(也支持后台运行 如果执行时间比较长的话)hive -S -e"select package_name,count(1) from app_list group by package_name;"> grp_app_id.dat这个只生成一个文件 并且输出的文件里面的列 是以空格隔开的。

SELECT

基本的Select操作
语法结构
SELECT [ALL | 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]

注：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by根据distribute by指定的内容将数据分到同一个reducer。
4、Cluster by 除了具有Distribute by的功能外，还会对该字段进行排序。因此，常常认为cluster by = distribute by + sort by

分桶表的作用：最大的作用是用来提高join操作效率；
（思考：
Select a.id ,a.name,b.addr from a join b on a.id = b.id;
如果a表和b表已经是分桶表，而且分桶的字段是id字段，做这个join操作时，还需要全表做笛卡尔积么？）

#创建分桶表create table stu_buck(sno int,sname string,sex string,sage int,sdept string)clustered by(sno) sorted by(sno DESC)into 4 bucketsrow format delimitedfields terminated by ',';#设置变量,设置分桶为true, 设置reduce数量是分桶的数量个数set hive.enforce.bucketing = true;set mapreduce.job.reduces=4;#客户往创建的分桶表插入数据(插入数据需要是已分桶, 且排序的)#可以使用distribute by(sno) sort by(sno asc)   或是排序和分桶的字段相同的时候使用Cluster by(字段)#注意使用cluster by  就等同于分桶+排序(sort)insert into table stu_buckselect sno,sname,sex,sage,sdept from student distribute by(sno) sort by(sno asc);

查看表大小
show create table table_name(表名)——>查看表的hdfs上的位置
计算表大小，单位G

hdfs dfs -du /dhome/trace/di/t_di_trace_event_d|awk '{SUM += $1} END {print SUM/(1024*1024*1024)}'

hive 空值处理

coalesce(a,x1,x2,x3,...)isnull(a,x)