Hive常用指令---增删改查

Hive常用指令—增删改查

Hive的数据库操作语言(HQL)是一种类似SQL的语言，大部分语句与SQL相同，目的是简化Hadoop的Mapreduce程序开发，提升开发效率。

一、数据库操作:

1.新建数据库

create database if not exists databasename; #databasename为你想要建的数据库名（安全性高）
create database databasename;

2.切换数据库

use databasename #进入hive时，默认为default数据库，需要执行本指令来切换到想要操作的数据库

3.查看所有的数据库

show databases;

4.查看当前所在数据库

SELECT current_database();

5.查看当前数据库细节

describe database shopping;

6.更改数据库的所有者

alter database databasename set owner user user1; #将某数据的所有者变更为user1

7.删除数据库

drop database if exists databasename #删除空数据库
drop database if exists databasename cascade; #删除带表数据库

二、数据表简介：

      数据表分为内部表、外部表和临时表。
      (1)内部表(管理表):

• HDFS中为所属数据库目录下的子文件夹。
• 数据完全由Hive管理，删除表(元数据)会删除数据。
  (2)外部表（External Tables）:
• 数据保存在指定位置的HDFS路径中。
• Hive不完全管理数据，删除表(元数据)不会删除数据。
  (3)临时表:
• 表只对当前session有效，session退出后，表自动删除。
• 如果创建的临时表表名已存在，那么当前session引用到该表名时实际用的是临时表，只有drop或rename临时表名才能使用原始表。
• 不支持分区字段和创建索引。

三、Hive表操作–增删改：

（1）创建一个自定义分隔符的普通内部表:

CREATE TABLE IF NOT EXISTS employee (	#employee为举例表名，根据实际情况进行替换，下同
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>>	#小括号内均为“列名 类型,”格式，此处列举了多种典型情况
)
COMMENT 'This is a table' #COMMENT后面替换为你想要加的注释
ROW FORMAT DELIMITED
#行格式化限制后面几行为设定的分隔符和存储格式，根据数据实际需要进行替换，以下几项均可不写，不写时，使用默认分隔符
FIELDS TERMINATED BY '|' #字段分隔符'|'
COLLECTION ITEMS TERMINATED BY ',' #集合分隔符
MAP KEYS TERMINATED BY ':'	#映射分隔符
STORED AS TEXTFILE	#存储为文本形式
tblproperties ("skip.header.line.count"="1"); #跳过表头(可选操作)

(2)创建一个导入数据文件类型为csv的内部表:

CREATE TABLE my_table(
a string, 
b string, ...) #a,b为列名;string为列类型
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' #用于导入csv文件的特殊类
WITH SERDEPROPERTIES (
 "separatorChar" = "\t", #分隔符
 "quoteChar" = "'",	#引用符，即''内的内容作为一个整体
 "escapeChar" = "\\"#忽略符，当遇到引用符时，就自动忽略分隔符，不论''里面的数据是否包含分隔符(separatorChar)
) 
STORED AS TEXTFILE;

      separatorChar,quoteChar,escapeChar均为可选属性默认情况下：

• separatorChar为 ,
• quoteChar为 "
• escapeChar为 \
  （3）创建外部表:

CREATE EXTERNAL TABLE my_table_external(
a string, 
b string, ...) #a,b为列名;string为列类型
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' #用于导入csv文件的特殊类
WITH SERDEPROPERTIES (
 "separatorChar" = "\t", #分隔符
 "quoteChar" = "'",	#引用符，即''内的内容作为一个整体
 "escapeChar" = "\\"#忽略符，当遇到引用符时，就自动忽略分隔符，不论''里面的数据是否包含分隔符(separatorChar)
) 
STORED AS TEXTFILE;
location hdfs_path

(4)创建临时表:

CREATE temporary TABLE my_table(
a string, 
b string, ...) #a,b为列名;string为列类型
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' #用于导入csv文件的特殊类
WITH SERDEPROPERTIES (
 "separatorChar" = "\t", #分隔符
 "quoteChar" = "'",	#引用符，即''内的内容作为一个整体
 "escapeChar" = "\\"#忽略符，当遇到引用符时，就自动忽略分隔符，不论''里面的数据是否包含分隔符(separatorChar)
) 
STORED AS TEXTFILE;

(5)向表中载入数据:

load data local inpath 'file_path' overwrite into table table_name; #file_path为你的本地文件路径,table_name为你要导入的表的名字

(6)向表中添加数据:

insert into table_name values (column1_value,column2_value,...) #column_value为每列的值

(7)修改表中数据:

update table_name set column_name1=column_newvalue1,column_name2=column_newvalue2,... where 条件
#支持同时修改多个列,条件可以为多个不同的条件用and或or进行连接

(8)删除表中数据:

delete from table_name where 条件 #条件可以为多个不同的条件用and或or进行连接

(9)清空表中数据：

truncate table table_name # truncate不能清空外部表中的数据

(10)删除表:

drop table_name

(11)创建静态分区表:

#建表时定义分区,通过PARTITIONED BY定义分区
CREATE TABLE employee_partitioned(
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>> )
PARTITIONED BY (year INT, month INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

(12)静态分区表操作:

 #ADD添加分区,DROP删除分区
ALTER TABLE employee_partitioned ADD PARTITION (year=2019,month=3) PARTITION (year=2019,month=4); 
ALTER TABLE employee_partitioned DROP PARTITION (year=2019, month=4);

(13)创建动态分区表:

#先创建一个普通内部表即可
CREATE TABLE employee_partitioned(
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>> )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

#设置hive.exec.dynamic.partition.mode属性为nonstrice
set hive.exec.dynamic.partition.mode=nonstrict;

#insert方式添加动态分区
insert into table employee_partitioned partition(year, month)
select name,array('Toronto') as work_place,
named_struct("sex","male","age",30) as sex_age,
map("python",90) as skills_score,
map("r&d", array('developer')) as depart_title,
year(start_date) as year,month(start_date) as month
from employee_hr eh ;

Hive表操作–查

(1)简单查询表中数据：

select column1,column2,... from table_name where 条件1，条件2...
#column1,column2为列名;条件可以为一个或多个条件，不同条件间用and和or连接
#例如 where name='tom' and age = 20

(2)查询某列数据中不重复的数据:

select DISTINCT column1 from table_name #DISTINCT限定查询唯一数据

(3)CTE（Common Table Expression）查询方式:

WITH t1 AS (SELECT …) SELECT * FROM t1 # t1执行首次查询中查询的表,后面的select再从前面查询的结果中筛选出新的结果
#举例
#此句为统计订单最多的顾客的订单数,rank()是一种用于排序的开窗函数，后面会讲到
with a as(
select customer_id,count(transcation_id) as nums from transcation_details group by customer_id),
b as(
select *,rank() over(order by nums desc) rn from a)
select * from b;

(4)嵌套查询:

#此处为为一个嵌套查询样例，作用是与CTE方式相同，都是从上一级查询获得的结果中筛选出新的结果
SELECT * FROM (SELECT * FROM employee) a;

(5)列匹配正则表达式:

# Hive要想支持正则表达式查询需先将hive.support.quoted.identifiers设置为none
SET hive.support.quoted.identifiers = none;
SELECT `^o.*` FROM offers; #表示查询offers表中以字母o开头的，包含0个或多个换行符以外字符的列

(6)关联查询:

a. Inner Join(内连接):
      从两表交集中查询结果(即区域C)，隐式连接(Join)也是一种内连接。

select column1,column2 from table_a inner join table_b; #显示内连接
select column1,column2 from table_a join table_b #隐式内连接

b.Outer Join(外连接):
i.左外连接:
      从整个左表中查询结果(即区域A)。

select column1,column2,... from table_a left join table_b;
select column1,column2,... from table_a left outer join table_b;

ii.右外连接:
      从两表交集和右表其余部分中获取查询结果(即区域B)。

select column1,column2,... from table_a right join table_b;
select column1,column2,... from table_a right outer join table_b;

iii.全外连接：
      从两表全部数据中查询结果(即AUBUC)。

select column1,column2,... from table_a full outer join table_b;

c.Cross Join(交叉连接,取笛卡尔积)

select column1,column2,... from table_a,table_b;

(7)合并多次查询结果(Union):

select column1 from table_a where column1='a'
union
select column2 from table_a where column2='b';

Hive数据排序:

(1).ORDER BY(默认正序即ASC)

select * from table_a order by column1; #按column1列从小到大进行排序
select * from table_a order by column1 desc; #按column1列从大到小进行排序

(2).SORT BY/DISTRIBUTE BY
      SORT BY对每个Reducer中的数据进行排序:

• 当Reducer数量设置为1时，等于ORDER BY。
• 排序列必须出现在SELECT column列表中。
        DISTRIBUTE BY类似于标准SQL中的GROUP BY:
• 确保具有匹配列值的行被分区到相同的Reducer。
• 不会对每个Reducer的输出进行排序。
• 通常使用在SORT BY语句之前。
  例:

SELECT department_id , name, employee_id, evaluation_score
FROM employee_hr 
DISTRIBUTE BY department_id SORT BY evaluation_score DESC;

(3).CLUSTER BY
      CLUSTER BY大致相当于sort by和distribute by合在一起的效果，但它不支持ASC和DESC且排序列必须出现在SELECT column列表中。

SELECT name, employee_id FROM employee_hr CLUSTER BY name;

Hive聚合运算

(1)Group By(按列进行分组):

select category, max(offervalue) from offers group by category;

(2)Having(对GROUP BY聚合结果的条件过滤，不建议使用):

#以下为一个典型的Having语句
select sex_age.age from employee group by sex_age.age having count(*) <= 1;

(3)取某列的最大/最小值/平均值/和:

selects customer_id,max(price) `最大值` from offers;
selects customer_id,min(price) `最小值` from offers;
selects customer_id,avg(price) `平均值` from offers;
selects customer_id,sum(price) `总和` from offers;

Hive窗口函数

(1)ROW_NUMBER():对所有数值输出不同的序号，序号唯一连续。
(2)RANK():对相同数值，输出相同的序号，下一个序号跳过（1,1,3)。
(3)DENSE_RANK():对相同数值，输出相同的序号，下一个序号连续（1,1,2）。
(4)NLITE(n):将有序的数据集合平均分配到n个桶中, 将桶号分配给每一行，根据桶号，选取前或后 n分之几的数据。
(5)(目前排名- 1)/(总行数- 1)，值相对于一组值的百分比排名。

SELECT 
name, dept_num, salary, 
ROW_NUMBER() OVER () AS row_num, 
RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS rank, 
DENSE_RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS dense_rank,
PERCENT_RANK() OVER(PARTITION BY dept_num ORDER BY salary) AS percent_rank, 
NTILE(2) OVER(PARTITION BY dept_num ORDER BY salary) AS ntile 
FROM employee_contract 
ORDER BY dept_num, salary;

补:窗口类型:行类型窗口和范围类型窗口：
**行类型窗口：**以当前行为参照，通过向前或向后数任意行进行范围确定。

SELECT
name, dept_num AS dept, salary AS sal,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1 FROM employee_contract  ORDER BY dept, name;

**范围类型窗口:**以当前行为参照，通过向前和向后数任意行进行范围确定。

SELECT department_id,SUM(salary) RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING from salary group by department_id;

从本地导入表/导出表到本地

(1) 从本地导入表:

IMPORT TABLE employee FROM '/tmp/output3';

(2)导出表到本地:

EXPORT TABLE employee TO '/tmp/output3';