hive相关命令

1.hive -help

• hive -e: 不进入hive交互窗口，执行sql语句

hive -e "select * users"

• hive -f: 执行脚本中sql语句

# 创建文件hqlfile1.sql，内容：select * from users
# 执行文件中的SQL语句
hive -f hqlfile1.sql
# 执行文件中的SQL语句，将结果写入文件
hive -f hqlfile1.sql >> result1.log

2.进入退出hive命令行

# 进入
hive
# 退出
exit;
quit;

3.在hive命令行执行 shell 命令 / dfs 命令

# !+空格+shell命令
hive> ! ls;
hive> ! clear;
# dfs命令
hive> dfs -ls / ;

数据类型与文件格式

数据类型

# 整数
tinyint smallint int bigint
# 布尔型
boolean
# 浮点型
float double
# 字符串
string
# 时间
timestamp
# 字节数组
binary

1. 数据转换

-- 隐式转换
hive> select '1.0'+2;
OK
3.0
hive> select '1111' > 10;
hive> select 1 > 0.8;
-- 显示转换
-- 使用 cast 函数进行强制类型转换；如果强制类型转换失败，返回NULL
hive> select cast('1111s' as int);
OK
NULL
hive> select cast('1111' as int);
OK
1111

集合数据类型

# array
# 有序相同数据类型集合
array(1,2);
# map
# key-value对，key必须是基本类型数据类型，value不限
map('a',1,'b',2)
# struct 
#不同类型字段的集合。类似于C语言的结构体
struct('1',1,1.0),
named_struct('col1', '1', 'col2', 1,'clo3', 1.0)
# union 
# 不同类型的元素存储在同一个字段的不同行中
create_union(1, 'a', 63)

• 应用

-- array
hive> select array(1,2,3);
OK
[1,2,3]
-- map
-- 使用 [] 访问数组元素
-- 当arr[5]下标越界时返回null
hive> select arr[0] from (select array(1,2,3) arr) tmp;
hive> select map('a', 1, 'b', 2, 'c', 3);
OK
{"a":1,"b":2,"c":3}
-- 使用 [] 访问map元素
hive> select mymap["a"] from (select map('a', 1, 'b', 2, 'c', 3)as mymap) tmp;
-- 使用 [] 访问map元素。 key 不存在返回 NULL
hive> select mymap["x"] from (select map('a', 1, 'b', 2, 'c', 3)as mymap) tmp;
NULL
-- struct --
-- 系统自己给key值：col1...
hive> select struct('username1', 7, 1288.68);
OK
{"col1":"username1","col2":7,"col3":1288.68}
-- 给 struct 中的字段命名
hive> select named_struct("name", "username1", "id", 7, "salary",12880.68);
OK
{"name":"username1","id":7,"salary":12880.68}
-- 使用 列名.字段名 访问具体信息
hive> select userinfo.id
> from (select named_struct("name", "username1", "id", 7,"salary", 12880.68) userinfo) tmp;
OK
id
7
-- union
-- union 数据类型
hive> select create_union(0, "zhansan", 19, 8000.88) uinfo;

文本文件数据编码

Hive默认分隔符

• 例子

-- id name age hobby(array) score(map)
-- 换行符： n : 
-- 用于分隔行。每一行是一条记录，使用换行符分割数据
-- 字段之间: ^A : < Ctrl >+A
-- 用于分隔字段。在CREATE TABLE语句中使用八进制编码01表示
-- 元素之间: ^B ：< Ctrl >+B
-- 用于分隔 ARRAY、MAP、STRUCT 中的元素。在CREATETABLE语句中使用八进制编码02表示
-- key-value之间：^C < Ctrl +C>
-- Map中 key、value之间的分隔符。在CREATE TABLE语句中使用八进制编码03表示
-- 文件内容
-- 666^Alisi^A18^Aread^Bgame^Ajava^C97^Bhadoop^C87
create table s1(
id int,
name string,
age int,
hobby array<string>,
score map<string, int>
);
load data local inpath '/home/hadoop/data/s1.dat' into table s1;
select * from s1;

HQL – DDL命令

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

数据库操作

• Hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个 库，则使用默认数据库；
• Hive的数据库名、表名均不区分大小写；
• 名字不能使用数字开头；
• 不能使用关键字，尽量不使用特殊符号；

创建数据库语法

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

• 案例

-- 创建数据库，在HDFS上存储路径为 默认路径 /user/hive/warehouse/*.db
-- 默认hive-site.xml 中 hive.metastore.warehouse.dir 设置
hive (default)> create database mydb;
hive (default)> dfs -ls /user/hive/warehouse;
-- 避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】
hive (default)> create database if not exists mydb;
-- 创建数据库。添加备注，指定数据库在存放位置
hive (default)> create database if not exists mydb2
comment 'this is mydb2'
location '/user/hive/mydb2.db;

查看数据库

-- 查看所有数据库
show databases;
-- 查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

使用数据库

use mydb;

删除数据库

-- 删除一个空数据库
drop database databasename;
-- 如果数据库不为空，使用 cascade 强制删除
drop database databasename cascade;

表操作

创建表

create [external] table [IF NOT EXISTS] table_name
[(colName colType [comment 'comment'], ...)]
[comment table_comment]
[partition by (colName colType [comment col_comment], ...)]
[clustered BY (colName, colName, ...)
[sorted by (col_name [ASC|DESC], ...)] into num_buckets buckets]
[row format row_format]
[stored as file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

1. CREATE TABLE。按给定名称创建表，如果表已经存在则抛出异常。可使用if not exists 规避。

2. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。 删除内部表时，数据和表的定义同时被删除； 删除外部表时，仅仅删除了表的定义，数据保留； 在生产环境中，多使用外部表；

3. comment。表的注释

4. partition by。对表中数据进行分区，指定表的分区字段

5. clustered by。创建分桶表，指定分桶字段

6. sorted by。对桶中的一个或多个列排序，较少使用

7. 存储子句。

ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] | SERDE serde_name
[WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]

建表时可指定 SerDe 。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用默认的 SerDe。建表时还需要为表指定列，在指定列的同 时也会指定自定义的 SerDe。

Hive通过 SerDe 确定表的具体的列的数据。 SerDe是 Serialize/Deserilize 的简称， hive使用Serde进行行对象的序列与反 序列化。

8. stored as SEQUENCEFILE|TEXTFILE|RCFILE。如果文件数据是纯文本，可以使 用 STORED AS TEXTFILE（缺省）；如果数据需要压缩，使用 STORED AS SEQUENCEFILE（二进制序列文件）。
9. LOCATION。表在HDFS上的存放位置
10. TBLPROPERTIES。定义表的属性
11. AS。后面可以接查询语句，表示根据后面的查询结果创建表
12. LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

内部表 & 外部表

• 默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
• 在删除内部表时，表的定义(元数据) 和 数据 同时被删除
• 在删除外部表时，仅删除表的定义，数据被保留
• 在生产环境中，多使用外部表

内部表

• t1.dat文件内容

2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

• 创建表 SQL

create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 显示表的定义，显示的信息较少
desc t1;
-- 显示表的定义，显示的信息多，格式友好
desc formatted t1;
-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t1;
-- 查询数据
select * from t1;
-- 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;
-- 删除表。表和数据同时被删除
drop table t1;
-- 再次查询数据文件，已经被删除

外部表

-- 创建外部表
create external table t2(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 显示表的定义
desc formatted t2;
-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t2;
-- 查询数据
select * from t2;
-- 删除表。表删除了，目录仍然存在
drop table t2;
-- 再次查询数据文件，仍然存在

内部表与外部表的转换

-- 创建内部表，加载数据，并检查数据文件和表的定义
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
load data local inpath '/home/hadoop/data/t1.dat' into table t1;
dfs -ls /user/hive/warehouse/mydb.db/t1;
desc formatted t1;
-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;
-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;

• 建表时：
  • 如果不指定external关键字，创建的是内部表；
  • 指定external关键字，创建的是外部表；
• 删表时
  • 删除外部表时，仅删除表的定义，表的数据不受影响
  • 删除内部表时，表的数据和定义同时被删除
• 外部表的使用场景
  • 想保留数据时使用。生产多用外部表

分区表

分区表创建与数据加载

-- 创建表
create table if not exists t3(
id int
,name string
,hobby array<string>
,addr map<String,string>
)
partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';
-- 加载数据。
load data local inpath "/home/hadoop/data/t1.dat" into table t3
partition(dt="2020-06-01");
load data local inpath "/home/hadoop/data/t1.dat" into table t3
partition(dt="2020-06-02");

备注：分区字段不是表中已经存在的数据，可以将分区字段看成伪列

查看分区

show partitions t3;

新增分区并设置数据

-- 增加一个分区，不加载数据
alter table t3 add partition(dt='2020-06-03');
-- 增加多个分区，不加载数据
alter table t3
add partition(dt='2020-06-05') partition(dt='2020-06-06');
-- 增加多个分区。准备数据
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
/user/hive/warehouse/mydb.db/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
/user/hive/warehouse/mydb.db/t3/dt=2020-06-08
-- 增加多个分区。加载数据
alter table t3 add
partition(dt='2020-06-07') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
partition(dt='2020-06-08') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';
-- 查询数据
select * from t3;

修改分区的hdfs路径

alter table t3 partition(dt='2020-06-01') set location
'/user/hive/warehouse/t3/dt=2020-06-03';

删除分区

-- 可以删除一个或多个分区，用逗号隔开
alter table t3 drop partition(dt='2020-06-03'),
partition(dt='2020-06-04');

分桶表

Hive中：分桶字段.hashCode % 分桶个数

-- 测试数据
1 java 90
1 c 78
1 python 91
1 hadoop 80
2 java 75
2 c 76
2 python 80
2 hadoop 93
3 java 98
3 c 74
3 python 89
3 hadoop 91
5 java 93
6 c 76
7 python 87
8 hadoop 88

-- 创建分桶表
create table course(
id int,
name string,
score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by "t";
-- 创建普通表
create table course_common(
id int,
name string,
score int
)
row format delimited fields terminated by "t";
-- 普通表加载数据
load data local inpath '/home/hadoop/data/course.dat' into table
course_common;
-- 通过 insert ... select ... 给桶表加载数据
insert into table course select * from course_common;
-- 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区

备注

• 分桶规则：分桶字段.hashCode % 分桶数
• 分桶表加载数据时，使用 insert… select … 方式进行
• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive 1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶；

修改表 & 删除表

-- 修改表名。rename
alter table course_common
rename to course_common1;
-- 修改列名。change column
alter table course_common1
change column id cid int;
-- 修改字段类型。change column
alter table course_common1
change column cid cid string;
-- The following columns have types incompatible with the
existing columns in their respective positions
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是
string不能转为int
-- 增加字段。add columns
alter table course_common1
add columns (common string);
-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1
replace columns(
id string, cname string, score int);
-- 删除表
drop table course_common1;

数据操作

数据导入

装载数据(Load)

基本语法：

LOAD DATA [LOCAL] INPATH 'filepath'
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,
partcol2=val2 ...)]

• LOCAL：

  • LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷 贝到Hive表指定的位置
  • LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定 的位置

• INPATH：加载数据的路径

• OVERWRITE：覆盖表中已有数据；否则表示追加数据

• PARTITION：将数据加载到指定的分区

准备工作：

-- 创建表
CREATE TABLE tabA (
id int
,name string
,area string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
-- 数据文件（~/data/sourceA.txt）：
1,fish1,SZ
2,fish2,SH
3,fish3,HZ
4,fish4,QD
5,fish5,SR
-- 拷贝文件到 HDFS
hdfs dfs -put sourceA.txt data/

装载数据：

-- 加载本地文件到hive(tabA)
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt'
INTO TABLE tabA;
-- 检查本地文件还在
-- 加载hdfs文件到hive(tabA)
LOAD DATA INPATH 'data/sourceA.txt'
INTO TABLE tabA;
-- 检查HDFS文件，已经被转移
-- 加载数据覆盖表中已有数据
LOAD DATA INPATH 'data/sourceA.txt'
OVERWRITE INTO TABLE tabA;
-- 创建表时加载数据
hdfs dfs -mkdir /user/hive/tabB
hdfs dfs -put sourceA.txt /user/hive/tabB
CREATE TABLE tabB (
id INT
,name string
,area string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
Location '/user/hive/tabB';

插入数据(Insert)

-- 创建分区表
CREATE TABLE tabC (
id INT
,name string
,area string
)
partitioned by (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 插入数据
insert into table tabC
partition(month='202001')
values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan',
'TJ');
-- 插入查询的结果数据
insert into table tabC partition(month='202002')
select id, name, area from tabC where month='202001';
-- 多表（多分区）插入模式
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';

创建表并插入数据(as select)

-- 根据查询结果创建表
create table if not exists tabD
as select * from tabC;

使用import导入数据

import table student2 partition(month='201709')
from '/user/hive/warehouse/export/student';

数据导出

-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;
-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;
-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;
-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001
/home/hadoop/data/tabC4
-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件
hive -e "select * from tabC" > a.log
-- export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息
export table tabC to '/user/hadoop/data/tabC4';
-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构可能不一致
create table tabE like tabc;
import table tabE from ''/user/hadoop/data/tabC4';
-- 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;
-- 以下语句报错，外部表不能执行 truncate 操作
alter table tabC set tblproperties("EXTERNAL"="TRUE");
truncate table tabC;

小结：

**数据导入：**load data / insert / create table … as select … / import table

**数据导出：**insert overwrite … diretory … / hdfs dfs -get / hive -e “select …” > a.log / export table

… Hive的数据导入与导出还可以使用其他工具：Sqoop、DataX等；

HQL操作之–DQL命令

DQL – Data Query Language 数据查询语言

查询 (select)：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY
col_list]]
[LIMIT [offset,] rows]

SQL语句书写注意事项：

• SQL语句对大小写不敏感
• SQL语句可以写一行（简单SQL）也可以写多行(复杂SQL)
• 关键字不能缩写，也不能分行
• 各子句一般要分行
• 使用缩进格式，提高SQL语句的可读性(重要)

创建表，加载数据

-- 测试数据 /home/hadoop/data/emp.dat
7369,SMITH,CLERK,7902,2010-12-17,800,,20
7499,ALLEN,SALESMAN,7698,2011-02-20,1600,300,30
7521,WARD,SALESMAN,7698,2011-02-22,1250,500,30
7566,JONES,MANAGER,7839,2011-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,2011-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,2011-05-01,2850,,30
7782,CLARK,MANAGER,7839,2011-06-09,2450,,10
7788,SCOTT,ANALYST,7566,2017-07-13,3000,,20
7839,KING,PRESIDENT,,2011-11-07,5000,,10
7844,TURNER,SALESMAN,7698,2011-09-08,1500,0,30
7876,ADAMS,CLERK,7788,2017-07-13,1100,,20
7900,JAMES,CLERK,7698,2011-12-03,950,,30
7902,FORD,ANALYST,7566,2011-12-03,3000,,20
7934,MILLER,CLERK,7782,2012-01-23,1300,,10
-- 建表并加载数据
CREATE TABLE emp (
empno int,
ename string,
job string,
mgr int,
hiredate DATE,
sal int,
comm int,
deptno int
)row format delimited fields terminated by ",";
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.dat'
INTO TABLE emp;

基本查询

-- 省略from子句的查询
select 8*888 ;
select current_date ;
-- 使用列别名
select 8*888 product;
select current_date as currdate;
-- 全表查询
select * from emp;
-- 选择特定列查询
select ename, sal, comm from emp;
-- 使用函数
select count(*) from emp;
-- count(colname) 按字段进行count，不统计NULL
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
-- 使用limit子句限制返回的行数
select * from emp limit 3;

where

WHERE子句紧随FROM子句，使用WHERE子句，过滤不满足条件的数据

where 子句中不能使用列的别名；

select * from emp
where sal > 2000;

比较运算符

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

备注：通常情况下NULL参与运算，返回值为NULL；NULL<=>NULL的结果为true

逻辑运算符

and、or、not

-- 比较运算符，null参与运算
select null=null;
select null==null;
select null<=>null
-- 使用 is null 判空
select * from emp where comm is null;
-- 使用 in
select * from emp where deptno in (20, 30);
-- 使用 between ... and ...
select * from emp where sal between 1000 and 2000;
-- 使用 like
select ename, sal from emp where ename like '%L%';
-- 使用 rlike。正则表达式，名字以A或S开头
select ename, sal from emp where ename rlike '^(A|S).*';

group by

GROUP BY语句通常与聚组函数一起使用，按照一个或多个列对数据进行分组，对每个组进行聚合操作。

-- 计算emp表每个部门的平均工资
select deptno, avg(sal)
from emp
group by deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal)
from emp
group by deptno, job;

• where子句针对表中的数据发挥作用；
• having针对查询结果（聚组以后的结果） 发挥作用
• where子句不能有分组函数；
• having子句可以有分组函数 having只用于group by分组统计之后

-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;

表连接

最后

以上就是矮小鱼为你收集整理的hive相关命令hive相关命令数据类型与文件格式HQL – DDL命令HQL操作之–DQL命令的全部内容，希望文章能够帮你解决hive相关命令hive相关命令数据类型与文件格式HQL – DDL命令HQL操作之–DQL命令所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。