Hive中的sql语句的使用

数据库相关操作

1. 创建数据库

hive中有一个默认的库：

库名： default

库目录：hdfs://hdp01:9000/user/hive/warehouse

新建库：

create database db_order;

库建好后，在hdfs中会生成一个库目录：

hdfs://hdp01:9000/user/hive/warehouse/db_order.db

 

 

数据表相关操作

1. 创建数据表（如果没有使用use指定数据库，那么会默认创建在default库中）

use db_order;

create table t_order(id string,create_time string,amount float,uid string);

表建好后，会在所属的库目录中生成一个表目录

/user/hive/warehouse/db_order.db/t_order

只是，这样建表的话，hive会认为表数据文件中的字段分隔符为 ^A

正确的建表语句为：

create table t_order(id string,create_time string,amount float,uid string)

row format delimited

fields terminated by ',';

这样就指定了，我们的表数据文件中的字段分隔符为 ","

还可以通过已存在表来建表create table t_user_2 like t_user; 新建的t_user_2表结构定义与源表t_user一致，但是没有数据

还可以在建表的时候插入数据，create table t_access_user as select ip,url from t_access; t_access_user会根据select查询的字段来建表，同时将查询的结果插入新表中

2. 删除数据表

drop table t_order;

删除表的效果是：

hive会从元数据库（mysql）中清除关于这个表的信息；

hive还会从hdfs中删除这个表的表目录（存储在表目录中的数据信息也会一道删除）；

3. 修改数据表

仅修改Hive元数据，不会触动表中的数据，用户需要确定实际的数据布局符合元数据的定义。只有在实际查询的时候才会将hdfs中的数据元素映射到mysql表结构中。

修改表名： alter table table_name rename to new_table_name;

修改分区名：alter table t_partition partition(department='x') rename to partition(department='1');

添加分区：alter table t_partition add partition (department='2',sex='0',howold=40);

删除分区：alter table t_partition drop partition (department='2');

4. 内部表与外部表

内部表(MANAGED_TABLE)：表目录按照hive的规范来部署，位于hive的仓库目录/user/hive/warehouse中

外部表(EXTERNAL_TABLE)：表目录由建表用户自己指定

create external table t_access(ip string,url string,access_time string)

row format delimited

fields terminated by ','

location '/access/log';

外部表和内部表的特性差别：

1. 内部表的目录在hive的仓库目录中 VS 外部表的目录由用户指定
2. drop一个内部表时：hive会清除相关元数据，并删除表数据目录及其中存储的数据信息
3. drop一个外部表时：hive只会清除相关元数据（mysql中）；

一个hive的数据仓库，最底层的表，一定是来自于外部系统，为了不影响外部系统的工作逻辑，在hive中可建external表来映射这些外部系统产生的数据目录；然后，后续的etl操作，产生的各种表建议用内部表。

5. 分区表介绍

分区表的实质是：在表目录中为数据文件创建分区子目录，以便于在查询时，MR程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。

比如，网站每天产生的浏览记录，浏览记录应该建一个表来存放，但是，有时候，我们可能只需要对某一天的浏览记录进行分析这时，就可以将这个表建为分区表，每天的数据导入其中的一个分区；当然，每日的分区目录，应该有一个目录名（分区字段）。

创建一个带分区的表：

create table t_access(ip string,url string,access_time string)

partitioned by(dt string)         # 分区，在一个表中创建一个子表。先创建好分区，再导入数据

row format delimited

fields terminated by ',';

注意：分区字段不能是表定义中的已存在字段

向分区中导入数据（需要进入hive命令行）：

load data local inpath '/root/access.log.2017-08-04.log' into table t_access partition(dt='20170804');

load data local inpath '/root/access.log.2017-08-05.log' into table t_access partition(dt='20170805');

针对分区中的数据进行查询的时候用语句select count(*) from t_access where dt='20170804';即可，相当于分区表是主表中的一个字段，但是在将sql语句分解为mr程序的时候会很方便。

可以创建多个分区，其意义在于，比如要收集日志，发现所有服务器产生的日志精确到天后存储在一个分区目录中还是有很多，那么就可以精确到小时，以每一个小时再创建一个分区目录，作为天那个分区目录的子目录。

6. hive中数据的导入与导出

6.1 数据的导入

方式1：导入数据的一种方式：

手动用hdfs命令，将文件放入表目录；

方式2：在hive的交互式shell中用hive命令来导入本地数据到表目录

hive>load data local inpath '/root/order.data.2' into table t_order;

方式3：用hive命令导入hdfs中的数据文件到表目录

hive>load data inpath '/access.log.2017-08-06.log' into table t_access partition(dt='20170806');

注意：导本地文件和导HDFS文件的区别：

本地文件导入表：复制

hdfs文件导入表：移动

6.2 数据的导出

将hive表中的数据导入HDFS的文件：

insert overwrite directory '/user/hive/access-data'

row format delimited fields terminated by ','

select * from t_access;

将hive表中的数据导入本地磁盘文件：

insert overwrite local directory '/home/user/data/access-data'

row format delimited fields terminated by ','

select * from t_access limit 100000;

 

 

Hive查询

提示：在做小数据量查询测试时，可以让hive将mrjob提交给本地运行器运行（不是交给yarn集群去调度），可以在hive会话中设置如下参数：

hive> set hive.exec.mode.local.auto=true;

1. 基本查询

select * from t_access;

select count(*) from t_access;

select max(ip) from t_access;

2. 条件查询

select * from t_access where access_time<'2017-08-06 15:30:20'

select * from t_access where access_time<'2017-08-06 16:30:20' and ip>'192.168.33.3';

3. join关联查询

假设数据库中有表A和B，数据如下：

A表

	name	numb
01	a	11
02	b	12
03	c	31
04	d	46

B表

	name	nick
01	a	a85141
02	b	b4621
03	c	c9846
04	d	d312

查询a表和b表中的name相同的记录的结果（以a表为主表）：

select a.name, a.numb, b.name, b.nick from a join b on a.name=b.name；

# join连接又分内连接，左连接和右连接，上面的是内连接，只保留两方都能匹配上的记录。如果是左连接，则左边a表的所有记录全部保留，右边b表中无法匹配上的记录设为null。

4. groub by分组聚合

select dt,count(*),max(ip) as cnt from t_access group by dt having dt>'20170804';

注意：一旦有group by子句，那么，在select子句中就不能有 （分组字段（dt），聚合函数(count, min, max等)） 以外的字段。where过滤不满足条件的数据，然后用聚合函数和group by进行数据运算聚合，得到聚合结果，最后用having条件过滤掉聚合结果中不满足条件的数据。

5. 子查询

select id,name,father from (select id,name,family_members['brother'] as father from t_person) tmp where father is not null;

 

 

Hive数据类型

1. 数字类型 

TINYINT (1-byte signed integer, from -128 to 127)

SMALLINT (2-byte signed integer, from -32,768 to 32,767)

INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)

BIGINT (8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)

FLOAT (4-byte single precision floating point number)

DOUBLE (8-byte double precision floating point number)

2. 日期类型 

TIMESTAMP (Note: Only available starting with Hive 0.8.0)

DATE (Note: Only available starting with Hive 0.12.0)

       # 存入的数据数据形式如 1985-06-30

3. 字符串

string

4. array

假如有如下数据需要用hive的表去映射：战狼2,吴京:吴刚:龙母,2017-08-16。

create table t_movie(moive_name string,actors array<string>,first_show date)

row format delimited fields terminated by ','

collection items terminated by ':';   # 用于分隔array中的数据元素

查询语法：

select moive_name,actors[0] from t_movie;

select moive_name,actors from t_movie where array_contains(actors,'吴刚');

5. map

假如有如下数据：

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28

2,lisi,father:mayun#mother:huangyi#brother:guanyu,22

可以用一个map类型来对上述数据中的家庭成员进行描述。建表语句如下：

create table t_person(id int,name string,family_members map<string,string>,age int)

row format delimited fields terminated by ','

collection items terminated by '#'

map keys terminated by ':';

查询语句如下所示：

## 取map字段的指定key的值

select id,name,family_members['father'] as father from t_person;

## 取map字段的所有key

select id,name,map_keys(family_members) as relation from t_person;

## 取map字段的所有value

select id,name,map_values(family_members) from t_person;

select id,name,map_values(family_members)[0] from t_person;

6. struct 

假设有如下数据，想用一个字段来描述整个用户信息，可以采用struct：

1,zhangsan,18:male:beijing

2,lisi,28:female:shanghai

create table t_person_struct(id int,name string,info struct<age:int,sex:string,addr:string>)

row format delimited fields terminated by ','

collection items terminated by ':';

查询语句如下所示：

select id,name,info.age from t_person_struct;

 

 

问题

1. 在外部表已经有数据的情况下如何对其创建分区表？

解: hive sql是读时模式，表创建好之后，存入数据时不会对其进行检查，只有在查询的时候才会对数据进行验证，所以如果想要创建一个外部分区表，表对应的目录可以已经存在，hive只是将元数据存入mysql中而已，创建外部表的时候不会为其创建分区目录（也有可能是判断是否存在，然后在判断是否需要创建）。mysql中则是写时模式。