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; 清空表