1. 数据库操作
（1）创建数据库
create database db01;
create database db02 comment "this is a test database";
create database db03 with dbproperties(‘creator‘=‘Louis‘,‘data‘=‘2020-04-01‘);
（2）删除数据库
drop database db01;
drop database db02 cascade;
(3)查看当前正在使用的数据库
select current_database();
（4）切换数据库
use db03;
2.数据库表的操作
（1）创建表
A:内部表
create table table_name(id int,name string)
row format delimited fileds terminated by ",";
B:外部表
create external table table_name(id int,name string)
row format delimited fileds terminated by ","
location ‘集群上的路径‘
（2）加载数据
load data local inpath ‘本地路径‘ into table table_name
load data inpath ‘集群路径‘ into table table_name
create table t_tb01
as
select * from t_tb02
where salary>2000
insert into t_tb03
select name , salary from t_tb02
where salary>2000
导出
insert directory ‘集群上的路径‘
insert local directory ‘本地路径‘
（3）查看表信息
desc t_tb01;
desc extended t_tb02;
desc formatted t_tb03;
show create table t_tb04;
(4) 删除表和清空biao
drop table t_tb01;
truncate table t_tb02;
5.创建分区表
#####静态分区
//1.创建分区表
create table t_tb01( id int, name string, salary bigint, address string) partitioned by (day string) row format delimited fields terminated by ‘,‘;
//2.导入数据
load data local inpath ‘/opt/data/2.txt‘ into table t_tb01 partition(day=‘2020-04-01‘);
####动态分区
//1.创建表
create table student(id int,name string,sex string,age int,department string) row format delimited fields terminated by ",";
//2.导入数据
load data local inpath ‘/opt/data/stu.txt‘ into student;
//3.创建表
create table student_age(id int,name string,sex string,department string) partitioned by (age int);
//4动态分区需要设置
set hive.exec.dynamic.partition.mode=nonstrict;
//5插入数据
insert overwrite table student_partitioin partition(age) select id,name,sex,department,age from student;
6. case when
select name,age, case when age<=18 then ‘青年‘ when age<30 then ‘中年‘ else ‘老年‘ end level from student;
7.创建复杂对象表
数组
create table person(departname string,address array<string>)row format delimited fields terminated by ‘ ‘ collection items terminated by ‘,‘;
departone 北京,上海,天津,重庆,山东,河南,河北
departtwo 北京,天津,重庆,山东,河南,上海,河北
departthree 山东,北京,上海,天津,河南,河北
departfour 北京,天津,重庆,山东,河南,上海,河北
departfive 北京,重庆,天津,重庆,河南,河北
select address[2] from person;
select departname from person;
select * from person where array_contains(address,‘北京‘);
map对象
//复杂数据类型--Map
create table maptable(name string,score map<string,int>) row format delimited fields terminated by ‘\t‘ collection items terminated by ‘,‘ map keys terminated by ‘:‘;
小明 Math:80,Chinese:81,English:85,Physic:87,Chemical:80
张三 Math:73,Chinese:92,English:60,Physic:76,Chemical:76
李明 Math:90,Chinese:80,English:90,Physic:80,Chemical:66
王五 Math:95,Chinese:75,English:78,Physic:70,Chemical:90
select name,score[‘Math‘] from person;
//复杂数据类型-Struct
create table structtable(id int,course struct<scorename:string,score:int,stuname:string>) row format delimited fields terminated by ‘\t‘ collection items terminated by ‘,‘;
1 Math,80,John
2 Chinese,92,Lucy
6 English,90,Louis
8 Chemical,90,Tom
select course.score from structtable;
原文：https://www.cnblogs.com/Louis-Victory/p/12614217.html