hive 操作总结

CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION position];
[IF NOT EXISTS]:判断数据库是否存在

Location:可以指定数据库在HDFS的存储路径
数据库本质上就是在HDFS之上的文件夹。
默认数据库的存放路径是HDFS的：/user/hive/warehouse内
创建数据库myhive2 放在hdfs /myhive2路径下
create database myhive2 location '/myhive2'

（2）删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错
drop database db_name;
强制删除数据库，包含数据库下面的表一起删除
drop database db_name cascade;

（3）查看数据库

desc database db_name;

2 数据表的操作

2.1 表操作语法

（1）创建表

下面为创建表可以添加的参数

EXTERNAL，创建外部表
PARTITIONED BY，分区表
CLUSTERED BY，分桶表
STORED AS，存储格式
LOCATION，存储位置

创建一个表名为test 有三列的表
CREATE TABLE test(
id INT,
name STRING,
gender STRING
);

（2）删除表

DROP TABLE table_name;

2.2 内部表操作

（1）内部表（CREATE TABLE table_name ......）

未被external关键字修饰的即是内部表，即普通表。内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据。

create database if not exists myhive;
use myhive;
create table if not exists stu(id int,name string);
insert into stu values (1,"zhangsan"), (2, "wangwu");
select * from stu;

默认是特殊字符:’\001’
我们发现输出的数据很难看没有间隔，这时我们可以用以下语句间隔
create table if not exists stu(id int,name string)row format delimited fields terminated by '\t'：表示以\t分隔
row format delimited fields terminated by '\t'：表示以\t分隔

2.3 外部表操作

外部表（CREATE EXTERNAL TABLE table_name ......LOCATION......）

被external关键字修饰的即是外部表，即关联表。
外部表是指表数据可以在任何位置，通过LOCATION关键字指定。数据存储的不同也代表了这个表在理念是并不是Hive内部管理的，而是可以随意临时链接到外部数据上的。

外部表有两种导入数据的方法

（1）可以先有表，然后把数据移动到表指定的LOCATION中

下面为具体操作

首先检查：hdfs dfs -ls /tmp，确认不存在/tmp/test_ext1目录
创建外部表：create external table test_ext1(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext1’;
可以看到，目录/tmp/test_ext1被创建
select * from test_ext1，空结果，无数据
上传数据： hdfs dfs -put test_external.txt /tmp/test_ext1/
select * from test_ext1，即可看到数据结果

（2）也可以先有数据，然后创建表通过LOCATION指向数据

hdfs dfs -mkdir /tmp/test_ext2
hdfs dfs -put test_external.txt /tmp/test_ext2/
create external table test_ext2(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext2’;
select * from test_ext2;

（3）删除外部表

可以发现，在Hive中通过show table，表不存在了
但是在HDFS中，数据文件依旧保留

（4）内外部表转换

desc formatted tablename;
内部表转外部表
alter table tablename set tblproperties('EXTERNAL'='TRUE');

外部表转内部表
alter table tablename set tblproperties('EXTERNAL'='FALSE');
要注意：('EXTERNAL'='FALSE') 或 ('EXTERNAL'='TRUE')为固定写法，区分大小写！！！
通过tablename set tblproperties来修改属性

2.4 数据加载和导出

（1）数据加载-load法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
local:数据在本地加载
OVERWRITE:是否覆盖数据（有就覆盖没有就不覆盖）

实例
CREATE TABLE myhive.test_load(
dt string comment '时间（时分秒）',
user_id string comment '用户ID',
word string comment '搜索词',
url string comment '用户访问网址'
) comment '搜索引擎日志表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load;
load data inpath '/tmp/search_log.txt' overwrite into table myhive.test_load;
注意，基于HDFS进行load加载数据，源数据文件会消失（本质是被移动到表所在的目录中）

（2）数据加载 - INSERT SELECT 语法

INSERT [OVERWRITE | INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
将SELECT查询语句的结果插入到其它表中，被SELECT查询的表可以是内部表或外部表。
INSERT INTO TABLE tbl1 SELECT * FROM tbl2;
INSERT OVERWRITE TABLE tbl1 SELECT * FROM tbl2;

（3）数据加载 - 两种语法的选择

对于数据加载，我们学习了：LOAD和INSERT SELECT的方式，那么如何选择它们使用呢？
数据在本地
推荐 load data local加载
数据在HDFS
如果不保留原始文件：推荐使用LOAD方式直接加载
如果保留原始文件：推荐使用外部表先关联数据，然后通过INSERT SELECT 外部表的形式加载数据
数据已经在表中
只可以INSERT SELECT

（4） hive表数据导出 - insert overwrite 方式

insert overwrite [local] directory ‘path’ select_statement1 FROM from_statement;

将查询的结果导出到本地 - 使用默认列分隔符
insert overwrite local directory '/home/hadoop/export1' select * from test_load ;
将查询的结果导出到本地 - 指定列分隔符
insert overwrite local directory '/home/hadoop/export2' row format delimited fields terminated by '\t' select * from test_load;
将查询的结果导出到HDFS上(不带local关键字)
insert overwrite directory '/tmp/export' row format delimited fields terminated by '\t' select * from test_load;

（5）基本语法：（hive -f/-e 执行语句或者脚本 > file）

bin/hive -e 'SQL' > file
bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt

bin/hive -f ‘sql file’ > file
bin/hive -f export.sql > /home/hadoop/export4/export4.txt
编写一个sql文件执行这个文件实现数据追加

2.5 分区表

create table tablename(...) partitioned by (分区列列类型, ......)
row format delimited fields terminated by '';

create table score(id int,age int,name string) partitioned by (year string,month string,day string)row format delimited fields terminated by '\t';

（1）加载分区

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename partitioned by (分区列列类型, ......) ;

（2）查看分区

show partitions tablename;

（3）增加分区

表格有几个分区，增加分区就要有几个
alter table myhive.score add partition (year='11',month='01',day='14')partition (year='12',month='05',day='13');

（4）删除分区

alter table myhive.score2 drop partition(year='12',month='05',day='13');

（5）修改分区值

ALTER TABLE tablename PARTITION (month='202005') RENAME TO PARTITION (month='201105');

2.6 分桶表

分桶和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储

开启分桶的自动优化（自动匹配reduce task数量和桶数量一致）

set hive.enforce.bucketing=true;

（1）创建分桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

（2）分桶表的数据载入

分桶表无法使用load加载数据需要通过临时的表格用insert 加载数据
--创建临时表用于存储需要加载的数据
create table tmp(c_id string,c_name string,t_id string)row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/home/hadoop/course.txt' into table tmp;
--将临时表的数据用insert方法加载到course分桶表中
insert overwrite table myhive.course select * from myhive.tmp cluster by (c_id);
clustered by指定分桶字段
into num buckets指定分桶数量

（3）为啥用insert 不用load

数据的三份划分基于分桶列的值进行hash取模来决定
由于load data不会触发MapReduce，也就是没有计算过程（无法执行Hash算法），只是简单的移动数据而已
所以无法用于分桶表数据插入。
同样计算出的hash取模放在同一个桶

2.7 修改表

（1）表的重命名

alter table old_table_name rename to new_table_name;

（2）修改表属性值

ALTER TABLE table_name SET TBLPROPERTIES("EXTERNAL"="TRUE"); 修改内外部表属性
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment); 修改表注释
更多属性看官方网站
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-listTableProperties

（3）增加列

ALTER TABLE table_name ADD COLUMNS (v1 int, v2 string);

（4）修改表名

ALTER TABLE test_change CHANGE v1 v1new INT;

（5）清空内部表

TRUNCATE TABLE tablename;

2.8 复杂类型操作

（1） array数组类型

create table myhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

row format delimited fields terminated by '\t' 表示列分隔符是\t
COLLECTION ITEMS TERMINATED BY ',' 表示集合（array）元素的分隔符是逗号

常见的操作
-- 查询所有数据
select * from myhive.test_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from myhive.test_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from myhive.test_array;
-- 查询location数组中包含tianjin的信息
select * from myhive.test_array where array_contains(work_locations,'tianjin');

（2）map映射类型

map类型其实就是简单的指代：Key-Value型数据格式。有如下数据文件，其中members字段是key-value型数据字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"（相当于python的字典）

create table myhive.test_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':';

MAP KEYS TERMINATED BY ':' 表示key-value之间用:分隔
常用的查询命令

--查询father、mother这两个map的key
select id, name, members['father'] father, members['brother'] mother, age from myhive.test_map;
--查询全部map的key，使用map_keys函数，结果是array类型
select id, name, map_keys(members) as relation from myhive.test_map;
--查询全部map的value，使用map_values函数，结果是array类型
select id, name, map_values(members) as relation from myhive.test_map;
--查询map类型的KV对数量
select id,name,size(members) num from myhive.test_map;
--查询map的key中有brother的数据
select * from myhive.test_map where array_contains(map_keys(members), 'brother');

（3） struct结构体类型

struct类型是一个复合类型，可以在一个列中存入多个子列，每个子列允许设置类型和名称有如下数据文件。

create table myhive.test_struct(
id string, info struct<name:string, age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

load data local inpath '/home/hadoop/data_for_struct_type.txt' into table myhive.test_struct;
常用语句
select * from myhive.test_struct;
--直接使用列名.子列名即可从struct中取出子列查询
select id,info.name from myhive.test_struct;