Hive笔记
Set
set hive.metastore.disallow.incompatible.col.type.changes=trur/false;
是否阻止不兼容的类型转换
set hive.exec.mode.local.auto=false/true;
是否切换本地模式
set mapreduce.framework.name=yarn/local
mapreduce执行位置
set hive.exec.dynamic.partition=true/false
动态分区功能开关
set hive.exec.dynamic.partition.mode=strict/nonstrict
动态分区(严格模式/非严格模式)
Shell
hive -e "sql"
执行sql语句
hive -f file.sql
执行sql文件
hive -hiveconf xxx
设定hive运行参数
Hive CLI
set xxx
设定hive运行参数
创建数据库
create database db_hive;
create database if not exists db_hive;
create database db_hive location '/user/hive/warehouse/db_hive'
指定hdfs存储路径
create database db_hive with dbproperties ("create_date" = "2023-10-30");
设定备注信息
查看数据库
show databases;
show databases like "db*";
模糊查询
describe database db_hive;
查看数据库信息
describe database extended db_hive;
查看备注信息
修改数据库
alter database db_hive set location "hdfs://bigdata1:9000/user/hive/warehouse/db_hive";
修改location
alter database db_hive set dbproperties ("create_date" = "2023-11-25");
修改properties
alter database db_hive set owner user root;
修改所属用户
删除数据库
drop database db_hive;
删除空数据库
drop database if exists db_hive;
drop database db_hive cascade;
强制删除
创建数据表
create table db_hive1.student( id int, name string ) row format delimited fields terminated by "\t" location "/user/hive/warehouse/student";
常用建表案例
create table student
内部表
create external table student
外部表
create table teacher1 as select * from teacher;
从查询结果建表
create table teacher2 like teacher;
从其他表建表,只包含结构
查看数据表
show tables;
show tables in db_hive1 like "tea*";
指定库模糊查询表
describe extended teacher;
查看详细信息
describe formatted teacher;
格式化查看详细信息
修改数据表
alter table teacher2 rename to teacher;
重命名表
alter table student add columns (name string);
增加列
alter table student change column gender gender double after id;
修改列
alter table student replace columns (id int, name string);
修改全部列
删除数据表
$drop table student;$
truncate table student;
清空表