2023年6月27日发(作者：)

Hive（⼆）hive的基本操作⼀、DDL操作（定义操作） 1、创建表 （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]分区：不⽤关注数据的具体类型，放⼊每⼀个分区⾥； 分桶：调⽤哈希函数取模的⽅式进⾏分桶 （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 1 2、 select count(distinct uid) from 1 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/hivedata/' overrite into table mytable3 partition(sex='boy'); 插⼊⼥分区数据： load data local inpath '/root/hivedata/' 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_statementINSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2 ...)]select_statement1INSERT 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 selectid,name,sex ,age,department ; （4）分区插⼊分区插⼊有两种，⼀种是静态分区，另⼀种是动态分区。如果混合使⽤静态分区和动态分区， 则静态分区必须出现在动态分区之前。现分别介绍这两种分区插⼊。 静态分区：创建静态分区表、从查询结果中导⼊数据、查看插⼊结果 动态分区：静态分区需要创建⾮常多的分区，那么⽤户就需要写⾮常多的 SQL！ Hive 提供了⼀个动态分 区功能，其可以基于查询参数推断出需要创建的分区名称。A)、创建分区表，和创建静态分区表是⼀样的B)、 参数设置hive> set ion=true;hive> set =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_statementINSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...具体⽰例： 导出数据到本地 Insert overwrite local directory '/root/hivedata/' 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 = ][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 进⾏排 序，并且设置 >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 ,, from a join b on = ;如果 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 sortby age desc, id desc;

这是分桶操作，按照 id 分桶，但是不进⾏排序insert overwrite directory '/root/outputdata4' select * from mingxing2 distribute by id sortby age;这是分桶操作，按照 id 分桶，并且按照 id 排序insert overwrite directory '/root/outputdata3' select * from mingxing2 cluster by id;

分桶查询：指定开启分桶：set ing = true;指定 reducetask 数量，也就是指定桶的数量set s=4;insert overwrite directory '/root/outputdata3' select * from mingxing2 cluster by id;

(3)按学⽣名称汇总学⽣年龄 select name ,sum(age) from student group by name;

解释三个执⾏参数：直接使⽤不带设置值得时候是可以查看到这个参数的默认值：set r：⼀个 hive，就相当于⼀次 hive 查询中，每⼀个 reduce任务它处理的平均数据量如果要改变值，我们使⽤这种⽅式：set r=51200000查看设置的最⼤ reducetask 数量set ：⼀次 hive 查询中，最多使⽤的 reduce task 的数量我们可以这样使⽤去改变这个值：set = 20

查看设置的⼀个 reducetask 常量数量set s：我们设置的 reducetask 数量三个参数的优先级顺序是：s > > r

补充：// 创建内部表 student

create table student(id int, name string, sex string, age int, department string)

row format delimited fields terminated by ',' //⾏分隔符⽤ linesstored as textfile;// 从本地导⼊数据load data local inpath '/home/hadoop/' 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 -put /ext_student;load data local inpath '/home/hadoop/' 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) row format delimited fields terminated by ',';// 往分区表导⼊数据load data local inpath '/home/hadoop/' into table ptn_student partition(code='112233');load data local inpath '/home/hadoop/' into table ptn_student1 partition(code='335566');// 创建多字段分区表create table ptn_student1(id int, name string, sex string, age int, department string)

partitioned by(code string, province string)

row format delimited fields terminated by ',';// 往多分区表导⼊数据，需指定两个分区字段值load data local inpath '/home/hadoop/'

into table ptn_student1 partition(code='335566', province='beijing');load data local inpath '/home/hadoop/'

into table ptn_student1 partition(code='335566', province='tianjing');load data local inpath '/home/hadoop/'

into table ptn_student1 partition(code='kk', province='tianjing');// 创建分桶表create table bck_student(id int, name string, sex string, age int, department string)

clustered by(department) into 4 buckets

row format delimited fields terminated by ',';// 重命名表alter table student rename to studentss;// 修改表的字段alter table studentss add columns(abc string, efg int);// 删除表的字段，有⼀些问题。drop字段有问题。不确定能不能执⾏成功alter table studentss drop column efg;

// 修改表的字段alter table studentss change abc code int;// 修改表的字段，并且改变字段的位置alter table studentss change efg province string after name;// 替换所有字段alter table studentss replace columns(id int, name string, sex string ,age int , department string);// 删除表drop table bck_student;drop table if exists bck_student;// 添加分区alter table ptn_student add partition(code='445566');alter table ptn_student add partition(code='445566') partition(code='778899');// 删除分区alter table ptn_student drop partition(code='445566');// insert ... select .....

insert into table student select id, name, sex, age, department from studentss where department = 'MA';// 以下两句操作是为多重插⼊准备两张表create table student11(id int, name string)

row format delimited fields terminated by ','

stored as textfile;create table student22(sex string, age int, department string)

row format delimited fields terminated by ','

stored as textfile;// 多重插⼊from studentss

insert into table student11 select id, name

insert into table student22 select sex, age, department;// 静态分区插⼊load data local inpath '' into table ptn_student partition(code='112233');// 动态分区插⼊set ion=true;set =nonstrict;insert into table d_student partition(age) select id, name, sex, department,age from studentss;// 创建⼀个分区表，为上⾯的动态分区做准备的create table d_student(id int, name string, sex string ,department string)

partitioned by(age int)

row format delimited fields terminated by ',';// CTAScreate table my_student as select id, name , sex, age, department from studentss;// likecreate table like_student like studentss;// insert导出hive表数据到本地insert overwrite local directory '/home/hadoop/myoutput'

select id, name, sex, age, department from studentss;