Hadoop（五）HiveQL常用语句

hive中的数据类型：
原子数据类型：TINYINT SMALLINT INT BIGINT FLOAT DOUBLE BOOLEAN STRING
复杂数据类型：STRUCT MAP ARRAY

一、DDL：数据库定义语言

1、创建内部表：

与数据库中的 Table 在概念上是类似，每一个 Table 在 Hive 中都有一个相应的目录存储数据。例如，一个表 test，它在 HDFS 中的路径为：/ warehouse/test。 warehouse是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录；

create table mytable(
id int, 
name string) 
row format delimited fields terminated by '\t' stored as textfile;// 创建内部表，有两个字段，它们之间通过tab分隔

加载数据

hive>LOAD DATA LOCAL INPATH '/root/id' INTO TABLE t1; // 从本地文件加载
hive>LOAD DATA INPATH '/root/id' INTO TABLE t1; // 从HDFS中加载

2、创建外部表，external

它和 内部表 在元数据的组织上是相同的，而实际数据的存储则有较大的差异。外部表主要指向已经在 HDFS 中存在的数据，可以创建 Partition。

create external table mytable2(
id int, 
name string)
row format delimited fields terminated by '\t' location '/user/hive/warehouse/mytable2';

外部表与内部表的差异：
①内部表 的创建过程和数据加载过程（这两个过程可以在同一个语句中完成），在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除；
②外部表 只有一个过程，加载数据和创建表同时完成，并不会移动到数据仓库目录中，只是与外部数据建立一个链接。当删除一个 外部表 时，仅删除该链接；

3、创建分区表：

所谓分区（Partition） 对应于数据库的 Partition 列的密集索引。在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。例如：test表中包含 date 和 city 两个 Partition，则对应于date=20130201, city = bj 的 HDFS 子目录为：/warehouse/test/date=20130201/city=bj。而对应于date=20130202, city=sh 的HDFS 子目录为：/warehouse/test/date=20130202/city=sh。
分区字段要写在partiton by（）

create table mytable3(
id int, 
name string)
partitioned by(sex string) row format delimited fields terminated by '\t'stored as textfile;

加载数据

load data local inpath '/root/hivedata/boy.txt' into table mytable3 partition(sex='boy');

增加分区：

alter table mytable3 add partition (sex='unknown') location '/user/hive/warehouse/mytable3/sex=unknown';

删除分区：

alter table mytable3 drop if exists partition(sex='unknown');

分区表默认为静态分区，可转换为自动套分区

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

给分区表灌入数据：

insert into table mytable3 partition (sex) select id,name,'boy' from student_mdf;

查询表分区：

show partitions mytable3;

查询分区表数据：

select * from mytable3;

查询表结构：

desc mytable3;

4.创建桶表

桶表是对数据进行哈希取值，然后放到不同文件中存储。查看每个桶文件中的内容，可以看出是通过对 buckets 取模确定的。

create table tblName_bucket(id int) clustered by (id) into 3 buckets;

说明：
clustered by ：按照什么分桶
into x buckets:分成x个桶

加载数据：
不能使用load data这种方式，需要从别的表来引用

insert into table tblName_bucket select * from tbl_other;

注意:在插入数据之前需要先设置开启桶操作，不然插入数据不会设置为桶!

set hive.enforce.bucketing=true;

桶表的主要作用：数据抽样、提高某些查询效率
注意：
clustered by 和 sorted by 不会影响数据的导入，这意味着，用户必须自己负责数据如何导入，包括数据的分桶和排序。

'set hive.enforce.bucketing = true'可以自动控制上一轮 reduce 的数量从而适配 bucket 的个数，
当然，用户也可以自主设置 mapred.reduce.tasks 去适配bucket 个数，
推荐使用'set hive.enforce.bucketing = true'。

4.删除表

DROP TABLE [IF EXISTS] table_name;

DROP TABLE 语句用于删除表的数据和元数据（表结构）。对应外部表，只删除Metastore中的元数据，而外部数据保存不动。

如果只想删除表数据，保留表结构，跟MYSQL类似，使用TRUNCATE语句。truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中

Truncate table table_name;

6、创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
　　[COMMENT database_name]
　　[LOCATION hdfs_path]
　　[WITH DBPROPERTIES(property_name=property_value,...)];

例如：

hive> create database my_hive_test if not exists
　　commit 'this is my first hive database'
　　with dbproperties（‘creator’ ='mike', 'date' = '2018-08-10'）;

7、切换数据库

hive> use my_hive_test;

8、查看数据库

hive> describe database my_hive_test;

9、删除数据库

hive> drop database my_hive_test;

默认情况下，Hive不允许用户删除一个包含表的数据库。用户要么先删除数据库中的表，再删除数据库；要么在删除命令的最后加上关键字CASCADE,这样Hive会先删除数据库中的表，再删除数据库，
命令如下（务必谨慎使用此命令）：

hive>drop database my_hive_test CASCADE;

10、查看所有数据库

hive>show databases;

二、DML数据操纵语言：

表操作

1重命名表：

alter table student rename to student_mdf;

2.增加列：

alter table student_mdf add columns (sex string);

3.修改列名：

alter table student_mdf change sex gender string;

4.替换列结构：

alter table student_mdf replace columns (id string, name string);

5.装载数据：

（本地数据）
load data local inpath '/home/lym/zs.txt' overwrite into student_mdf;
（HDFS数据）
load data inpath '/zs.txt' into table student_mdf;

指定overwrite关键字，目标文件夹中之前存在的数据将会被先删除掉，如果没有这个关键字，仅仅会把新增的文件增加到目标文件夹中，而不会删除之前的数据。
6.插入一条数据：

insert into table student_mdf values('1','zhangsan');

7.创建表接收查询结果：

create table mytable5 as select id, name from mytable3;

8.导出数据：

（导出到本地）
insert overwrite local directory '/root/hivedata/mytable5.txt' select * from mytable5;
（导出到HDFS）
insert overwrite directory 'hdfs://master:9000/user/hive/warehouse/mytable5_load' select * from mytable5;

表查询

select * from mytable3;  //查询全表
select uid,uname from student; //查询学生表中的学生姓名与学号字段
select  uname,count(*) from student group by uname; //统计学生表中每个名字的个数。常用的功能还有 having、order by、sort by、distribute by、cluster by等等

关联查询：

//内连接：将符合两边连接条件的数据查询出来
select * from t_a a inner join t_b b on a.id=b.id;
//左外连接：以左表数据为匹配标准，右边若匹配不上则数据显示null
select * from t_a a left join t_b b on a.id=b.id;
//右外连接：与左外连接相反
select * from t_a a right join t_b b on a.id=b.id;
//左半连接：左半连接会返回左边表的记录，前提是其记录对于右边表满足on语句中的判定条件。
select * from t_a a left semi join t_b b on a.id=b.id;
//全连接(full outer join)：
select * from t_a a full join t_b b on a.id=b.id;
//in/exists关键字(1.2.1之后新特性)：效果等同于left semi join
select * from t_a a where a.id in (select id from t_b);
select * from t_a a where exists (select * from t_b b where a.id = b.id);

三、常用函数

图片1.png