1、 创建内部表
create database if not exists test ;
use test;
create table test(id int,name string) row format delimited fields terminated by “,”;
2、创建外部表
(1)使用默认的仓库路径
create external table test_ext_2(id int,name string) row format delimited fields terminated by “,”
(2)指定一个不存在的外部路径
create external table test_ext(id int,name string) row format delimited fields terminated by “,” location “/external/test”;
3、创建分区表
(1)创建只有一个分区字段的分区表:
create table test_partition(id int,name string,department string) partitioned by (city string) row format delimited fields terminated by “,”;
(2)创建有多个分区字段的分区表:
create table test_partition2(id int,name string,department string) partitioned by (city string,sale_date date) row format delimited fields terminated by “,”;
(3)往分区中导入数据:
load data local inpath “/root/city.txt” into table test_partition2 partition(city=“chengdu”,sale_date=‘2020-01-10’)
(4)添加分区
alter table test_partition2 add partition(city=“hle”,sale_date=‘2020-09-10’)
(5)查询分区
show partitions test_partition2
4、创建分桶表
(1)创建一个分桶表
create table test_bucket(id int,name string ,sex string,age int,department string) clustered by (department)
sorted by (age desc,id asc) into 1 buckets row format delimited fields terminated by “,”;
5、从查询语句的结果创建新表
(1)从查询语句的结果创建新表(CTAS语句)
create table test_bak as select * from test_partition2;
6、通过like复制已有表的结构创建表
(1)通过like复制已有表的结构创建表
create table test_bak2 like test_partition2
7、删除表
drop table test_bak2;
8、修改表
(1) 修改表名
alter table test_ext rename to test_ext_rename;
(2)修改字段
添加字段:
create table test_d(id int,name string)
hive> alter table test_d add columns(department string);
OK
Time taken: 0.442 seconds
删除字段:
hive> alter table test_d replace columns(id int,name string);
替换字段:
hive> alter table test_d replace columns(id int,named string);
OK
Time taken: 0.563 seconds
改变列的定义:将字段名称ID修改为ID_NEW,类型由INT修改为STRING
hive> alter table test_d change id id_new string;
OK
Time taken: 0.492 seconds
改变列的顺序:将new id字段放在named之后
alter table test_d change id_new id_new string after named
(3)修改分区
添加分区:
hive> alter table test_partition2 add partition(city=“test”,sale_date=‘2021-09-10’);
OK
Time taken: 0.321 seconds
删除分区:
hive> alter table test_partition2 drop partition(city=“test”,sale_date=‘2021-09-10’);
Dropped the partition city=test/sale_date=2021-09-10
OK
Time taken: 1.066 seconds
修改分区的数据目录:
hive> alter table test_partition2 partition(city=“hle”,sale_date=‘2020-09-10’) set location “hdfs://hadoop0:9000/hle/test”;
OK
Time taken: 0.655 seconds
9、清空表
10、显示创建表的详细信息
11、导入数据
(1)普通导入
create table test_input(id int,name string, department string,city string,vdate date) row format delimited fields terminated by “,”;
load data local inpath “/root/city.txt” into table test_input;
(2) 单重插入
insert into test_input values(5,“ee”,“hr”,“beijing”,“2021-10-1”)
(3) 多重插入
insert into test_input values(6,‘ff’,‘hr’,‘beijing’,‘2021-11-1’),(7,‘gg’,‘hr’,‘shengyang’,‘2020-09-1’)
12、导出数据
insert overwrite local directory “/root/test-input.txt” select * from test_input;