Hive基本操作
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
库操作
创建库
- 进入终端
beeline -u " jdbc:hive2://10.240.xx.xx:10000/dc_ods " -n user
beeline -u "jdbc:hive2://10.9.251.xx:10001/zxl_db hive xxx"
# LDAP方式
beeline -u jdbc:hive2://xx.xx.xx.xx:10008 -n username -p password --hiveconf mapreduce.job.queuename='Yarnroot.queen_name' -e 'show databases;'
- 判断是否存在并添加注释
create database if not exists zxl_test comment 'hive_test';
- 添加属性
create database if not exists zxl_test with dbproperties('creator'='zxl','date'='2019-04-09');
- 描述库
desc database zxl_test;
desc database extended zxl_test;
- 查看建库语句
show create database zxl_test;
删除库
- 库下无表,直接删除
drop database dbname;
drop database if exists dbname;
- 库下有表
方式1:先删除表再删除库;
方式2: drop database if exists zxl_test cascade;
表操作
创建
- 创建表内部表
create table student(id int, name string, sex string,
age int,department string) row format delimited fields terminated by ",";
- 创建外部表
create external table student_ext(id int, name string, sex string,
age int,department string) row format delimited
fields terminated by "," location "/hive/student";
- 内部表与外部表相互转化
# 将内部表转为外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
# 将外部表转为内部表
alter table student_ext set tblproperties('EXTERNAL'='FALSE');
- 创建分区表
create external table student_ptn(id int, name string, sex string,
age int,department string) partitioned by (city string) row format delimited
fields terminated by ","
location "/hive/student_ptn";
# 添加分区
alter table student_ptn add partition(city="beijing");
- 创建分桶表
create external table student_bck(id int, name string, sex string,
age int,department string)
clustered by (id) sorted by (id asc, name desc) into 4 buckets
row format delimited fields terminated by ","
location "/hive/student_bck";
- 使用CTAS创建表
create table student_ctas as select * from student where id < 95012;
- 复制表结构(able前面没有加external关键字则是内部表,否则外部表)
create table student_copy like student;
查看表
查看库下的表
show tables in zxl_test;
模糊匹配表名
show tables like '*dent*';
查看表结构
desc student;
desc extended student;
desc formatted student;(格式好)
查看分区信息
show partitions student_ptn;
查看建表语句
show create table student_ptn;
修改表
- 修改表名
alter table student rename to new_student;
- 增加字段 字段位置在所有列后面(partition列前)
alter table new_student add columns (score int, hometown string);
- 修改字段定义
alter table new_student change name new_name string;
- 替换所有字段
alter table new_student replace columns (id int, name string, address string);
- 替换可以用来删除字段
alter table new_student replace columns (id int, name string);
- 删除表
drop table new_student;
- 清空表
truncate table student_ptn;
- 内部表转换为外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
- 外部表转换为内部表
alter table student set tblproperties('EXTERNAL'='FALSE');
修改分区
- 静态分区
alter table student_ptn add partition(city="shenzhen");
- 动态分区 [内容直接插入到另一张表student_ptn_age中,并实现age为动态分区]
load data local inpath "/data1/student.txt" into table student_ptn partition(city="beijing");
create table student_ptn_age(id int,name string,sex string,department string) partitioned by (age int);
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table student_ptn_age partition(age) select id,name,sex,department,age from student_ptn;
- 删除分区
alter table student_ptn drop partition (city='beijing');
Hive函数
内置函数
- 查看内置函数
show functions;
- 显示函数的详细信息
desc function extended substr;
自定义函数UDF
UDF(user-defined function)作用于单个数据行,产生一个数据行作为输出。(数学函数,字 符串函数)
UDAF(用户定义聚集函数 User- Defined Aggregation Funcation):接收多个输入数据行,并产 生一个输出数据行。(count,max)
UDTF(表格生成函数 User-Defined Table Functions):接收一行输入,输出多行(explode)
Hive命令行
- hive -e sql语句 执行完退出终端
hive -e "select * from zxl_test.student";
beeline -u " jdbc:hive2://10.240.xx.xx:10001 user passwd" -e "sql"
- hive -f sql文件名 执行完退出终端
select * from zxl_test.student # test.sql内容
hive -f test.sql
- hive临时设置
查看属性值: set 属性名
临时修改属性值: set 属性名 = 属性值
Shylin