Hive（⼆）hive的基本操作
⼀、DDL操作（定义操作）
1、创建表
（1）建表语法结构
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
drop table if exists user[(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]
分区：不⽤关注数据的具体类型，放⼊每⼀个分区⾥；分桶：调⽤哈希函数取模的⽅式进⾏分桶
（2）建表语句相关解释
create  table：创建⼀个指定名字的表。如果相同名字的表已经存在，则抛出异常；⽤户可以⽤ IF NOT EXISTS 选项来忽略这个异常。
external  ：关键字可以让⽤户创建⼀个外部表，在建表的同时指定⼀个指向实际数据的路径（ LOCATION）， Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被⼀起删除，⽽外部表只删除元数据，不删除数据。 (经典⾯试问题)
partitioned ：在 Hive Select 查询中⼀般会扫描整个表内容，会消耗很多时间做没必要的⼯作。有时候只需要扫描表中关⼼的⼀部分数据，因此建表时引⼊了 partition 概念。个表可以拥有⼀个或者多个分区，每个分区以⽂件夹的形式单独存在表⽂件夹的⽬录下，分区是以字段的形式在表结构中存在，通过 desc table 命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表⽰。分区建
表分为 2 种，⼀种是单分区，也就是说在表⽂件夹⽬录下只有⼀级⽂件夹⽬录。另外⼀种是多分区，表⽂件夹下出现多⽂件夹嵌套模式
like：允许⽤户复制现有的表结构，但是不复制数据。
comment：可以为表与字段增加描述
row format:
stored as ：如果⽂件数据是纯⽂本，可以使⽤ STORED AS TEXTFILE。如果数据需要压缩，使⽤ STORED AS SEQUENCEFILE。
clustered by (分桶)：
location：指定数据⽂件存放的 hdfs ⽬录
（3）hive的建表⽰例
执⾏查看表结构：desc formatted  page_view
(4)Hive QL对SQL语句的⽀持
1、 select * from db.table1
2、 select count(distinct uid) from db.table1
3、⽀持 select、 union all、 join（ left、 right、 full join）、 like、 where、各种聚合函数、⽀持 json 解析
4、 UDF（ User Defined Function） / UDAF（多⾏合并为⼀⾏）
5、不⽀持 update 和 delete
6、 hive ⽀持 in/exists， hive 使⽤ semi join 的⽅式来代替实现，⽽且效率更⾼。
（5）具体⽰例
a、创建内部表
create table mytable (id int, name string) row format delimited fields terminated by ','
stored as textfile;
b、创建外部表
create external table mytable2 (id int, name string) row format delimited fields
terminated by ',' location '/user/hive/warehouse/mytable2';
c、创建分区表
create table mytable3(id int, name string)
partitioned by(sex string) row format delimited fields terminated by ','stored as textfile;
插⼊数据
插⼊男分区数据： load data local inpath '/root/' overrite into table
mytable3 partition(sex='boy');
插⼊⼥分区数据： load data local inpath '/root/' overrite into table
mytable3 partition(sex='girl');
d、创建分桶表
create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno) sorted by(Sno DESC) into 4 buckets
row format delimited fields terminated by ',';
2、修改表
（1）重命名表  ALTER TABLE table_name RENAME TO new_table_name
（2）增加、删除、改变、替换列
（3）删除表  DROP TABLE [IF EXISTS] table_name;
（4）增加、删除分区
增加分区语法结构：
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ]
partition_spec [ LOCATION 'location2' ] ...
删除分区语法结构：ALTER TABLE table_name DROP partition_spec, partition_spec,...
具体⽰例：alter table student_p add partition(part='a') partition(part='b');
显⽰命令：
show tables;
show databases;
show partitions table_name;
show functions;    展⽰内置函数
desc extended table_name;
desc formatted table_name;
⼆、DML操作
1、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_host:9000/user/hive/project/data1
（3） local 关键字
如果指定了 LOCAL， load 命令会去查本地⽂件系统中的 filepath。
如果没有指定 LOCAL 关键字，则根据 inpath 中的 uri 查⽂件
注意： uri 是指 hdfs 上的路径，分简单模式和完整模式两种，例如：
简单模式： /user/hive/project/data1
完整模式： hdfs://namenode_host:9000/user/hive/project/data1
（4）overwrite 关键字
如果使⽤了 OVERWRITE 关键字，则⽬标表（或者分区）中的内容会被删除，然后再将 filepath 指向的⽂件/⽬录中的内容添加到表/分区中。
如果⽬标表（分区）已经有⼀个⽂件，并且⽂件名和 filepath 中的⽂件名冲突，那么现有的⽂件会被新⽂件所替代。
2、insert 插⼊数据
（1）插⼊⼀条数据  INSERT INTO TABLE table_name VALUES(XX,YY,ZZ);
（2）利⽤查询语句将结果导⼊新表：
INSERT OVERWRITE [INTO] TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 FROM from_statement
（3）多重插⼊
FROM from_statement
INSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1
INSERT OVERWRITE TABLE table_name2 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement2] ...
⽰例：从 mingxing 表中，按不同的字段进⾏查询得的结果分别插⼊不同的 hive 表
from mingxing insert into table mingxing2 select id,name,sex,age insert into table mingxing select
id,name,sex ,age,department ;
（4）分区插⼊
分区插⼊有两种，⼀种是静态分区，另⼀种是动态分区。如果混合使⽤静态分区和动态分区，则静态分区必须出现在动态分区之前。现分别介绍这两种分区插⼊。
静态分区：创建静态分区表、从查询结果中导⼊数据、查看插⼊结果
动态分区：
静态分区需要创建⾮常多的分区，那么⽤户就需要写⾮常多的 SQL！ Hive 提供了⼀个动态分区功能，其可以基于查询参数推断出需要创建的分区名称。
A)、创建分区表，和创建静态分区表是⼀样的
B)、参数设置
hive> dynamic.partition=true;
hive> de=nonstrict;
注意：动态分区默认情况下是没有开启的。开启后，默认是以”严格“模式执⾏的，在
这种模式下要求⾄少有⼀列分区字段是静态的。这有助于阻⽌因设计错误导致查询产⽣
⼤量的分区。但是此处我们不需要静态分区字段，估将其设为 nonstrict。
C)、动态数据插⼊（partition字段必须出现在select字段的最后）
insert into table test2 partition (age) select name,address,school,age from students;
注意：查询语句 select 查询出来的 age 字段必须放在最后，和分区字段对应，不然结果
会出错
D)、查看插⼊结果
（5）case（create table ... as  select..）
在实际情况中，表的输出结果可能太多，不适于显⽰在控制台上，这时候，将 Hive 的查询输出结果直接存在⼀个新的表中是⾮常⽅便的，我们称这种情况为 CTAS
展⽰：
CREATE TABLE mytest AS SELECT name, age FROM test;
注意： CTAS 操作是原⼦的，因此如果 select 查询由于某种原因⽽失败，新表是不会创建的！
3、insert 导出数据（如果有local 导出到本地，不带local，导⼊HDFS）
单模式导出：
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement
多模式导出：
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
具体⽰例：
导出数据到本地  Insert overwrite local directory '/root/' select * from studentss;
注意：数据写⼊到⽂件系统时进⾏⽂本序列化，且每列⽤^A 来区分， \n 为换⾏符。⽤
more 命令查看时不容易看出分割符，可以使⽤: sed -e 's/\x01/|/g' filename 来查看。
导出数据到HDFS：  Insert overwrite directory 'hdfs://hadoop02:9000/user/hive/warehouse/mystudent' select * from studentss;
4、select 查询数据
语法结构：
SELECT [ALL | DISTINCT] select_ condition, select_ condition, ...
FROM table_name a
[JOIN table_other b ON a.id = b.id]
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list | ORDER BY col_list] ]
[LIMIT number]
说明：
（1）select_ condition 查询字段
（2）table_name 表名
（3） order by 会对输⼊做全局排序，因此只有⼀个 reducer，只有⼀个 reduce task 的结果，⽐如⽂件名是 000000_0，会导致当输⼊规模较⼤时，需要较长的计算时间。
（4） sort by 不是全局排序，其在数据进⼊ reducer 前完成排序。因此，如果⽤ sort by 进⾏排序，并且设置 duce.tasks>1，则 sort by 只保证每个 reducer 的输出有序，不保证全局有序。
那万⼀，我要对我的所有处理结果进⾏⼀个综合排序，⽽且数据量⼜⾮常⼤，那么怎么解决？
我们不适⽤ order by 进⾏全数据排序，我们适⽤ sort by 对数据进⾏局部排序，完了之后，再对所有的局部排序结果做⼀个归并排序（5）distribute by(字段)根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列。
（6）Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进⾏排序。
因此，如果分桶和 sort 字段是同⼀个时，此时， clustered by = distribute by + sort by  如果我们要分桶的字段和要排序的字段不⼀样，那么我们就不能适⽤ clustered by
分桶表的作⽤：最⼤的作⽤是⽤来提⾼ join 操作的效率；
（思考这个问题： select a.id,a.name,b.addr from a join b on a.id = b.id；如果 a 表和 b 表已经是分桶表，⽽且分桶的字段是 id 字段做这个join 操作时，还需要全表做笛卡尔积吗）
实例：（1）获取年龄⼤的三个学⽣
select id ,age,name from student where stat_date='20140101'  order by  age desc limit 3;
(2)查询学⽣年龄，按降序排序
select id,age,name from student sort by age desc;
select id,age,name from student order by age desc;
select id,age,name from student distribute by age;
这是分桶和排序的组合操作，对 id 进⾏分桶，对 age， id 进⾏降序排序
insert overwrite directory '/root/outputdata6' select * from mingxing2 distribute by id sort
by age desc, id desc;
这是分桶操作，按照 id 分桶，但是不进⾏排序
insert overwrite directory '/root/outputdata4' select * from mingxing2 distribute by id sort
by age;
这是分桶操作，按照 id 分桶，并且按照 id 排序
insert overwrite directory '/root/outputdata3' select * from mingxing2 cluster by id;
分桶查询：
指定开启分桶：
force.bucketing = true;
指定 reducetask 数量，也就是指定桶的数量
set duces=4;
insert overwrite directory '/root/outputdata3' select * from mingxing2 cluster by id;
(3)按学⽣名称汇总学⽣年龄
select  name ,sum(age) from student group by name;
解释三个执⾏参数：
直接使⽤不带设置值得时候是可以查看到这个参数的默认值：
educers.ducer
如果要改变值，我们使⽤这种⽅式：
educers.ducer=51200000
查看设置的最⼤ reducetask 数量
educers.max
我们可以这样使⽤去改变这个值：
educers.max = 20
查看设置的⼀个 reducetask 常量数量
set duces
duces：我们设置的 reducetask 数量
三个参数的优先级顺序是：
duces > ducers.max > ducers.ducer
补充：
// 创建内部表 student
create table student(id int, name string, sex string, age int, department string)
row format delimited fields terminated by ','              //⾏分隔符⽤ lines
stored as textfile;
// 从本地导⼊数据
load data local inpath '/home/' into table student;
// 查询数据
select id, name, sex, age, department from student;
// 创建⼀个external表
create external table ext_student(id int, name string, sex string, age int, department string)
row format delimited fields terminated by ','
location '/ext_student';
// 导⼊数据
hadoop  fs - /ext_student;
load data local inpath '/home/' into table ext_student;
// 查询数据
select id, name ,sex, age , department from ext_student;
// 创建内部表引⽤外部路径
create table mng_student(id int, name string, sex string, age int, department string)
row format delimited fields terminated by ','
location '/ext_student';
// 创建分区表
create table ptn_student(id int, name string, sex string, age int, department string)
partitioned by(code string)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。