hive 数据定义语言(DDL)

一、数据库操作

1、创建数据库
语法格式：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

hive> create database r_db;

> 创建数据库时指定存储目录
hive> create database r_db1 location "/user/test";
> 创建数据库是指定描述信息
hive (default)> create database r_db2 comment "this is a test database";
> 显示数据库的信息
hive (default)> desc database r_db2;
或者
hive (default)> desc database extended r_db2;
> 创建时指定数据库的描述信息
hive (default)> create database r_db3 with dbproperties('author'='yjt','create'='2020-06-29');

hive (default)> desc database extended r_db3;
db_name	comment	location	owner_name	owner_type	parameters
r_db3		hdfs://yjt:9000/hive/warehouse/r_db3.db	hduser	USER	{create=2020-06-29, author=yjt}

> 对已经存在的数据库添加描述信息
hive (default)> alter database r_db3 set dbproperties("address"='beijin');  # 目前还不能删除属性，只能设置为''字符串

hive (default)> desc database extended r_db3;
db_name	comment	location	owner_name	owner_type	parameters
r_db3		hdfs://yjt:9000/hive/warehouse/r_db3.db	hduser	USER	{create=2020-06-29, address=beijin, author=yjt}

2、显示数据库

> 使用like 匹配
hive (default)> show databases like "shang.*";
OK
database_name
shanghai

3、删除数据库

hive (r_db2)> drop database r_db3;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database r_db3 is not empty. One or more tables exist.)
> 使用cascade强制删除
hive (r_db2)> drop database r_db3  cascade;
OK

其他的关于数据库的操作，查看官网
官网

二、表操作

语法格式

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

管理表

1、创建表

create table test1(
id int,
name string,
subo array<string>,
deb map<string, float>,
address struct<city:string,state:string>)
row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by "\n"
location "/hive/warehouse/r_db2.db/test1"
tblproperties('create_time'='2020-06-29');

2、加载数据

hive (default)> load data local inpath "/tmp/hive/hive.txt"  into table test1;

测试数据：

[hduser@yjt hive]$ cat hive.txt
1,yjt,n1_n2,key1:12.0,beijin_changping
2,yjl,n3_n4,key2:23.11,chongqin_yubei

3、查询

hive (default)> select * from test1;
OK
test1.id	test1.name	test1.subo	test1.deb	test1.address
1	yjt	["n1","n2"]	{"key1":12.0}	{"city":"beijin","state":"changping"}
2	yjl	["n3","n4"]	{"key2":23.11}	{"city":"chongqin","state":"yubei"}
Time taken: 0.215 seconds, Fetched: 2 row(s)

查询指定的数据
hive (default)> select subo[0], deb['key1'], address.city from test1 where name='yjt';
OK
_c0	_c1	city
n1	12.0	beijin
Time taken: 0.319 seconds, Fetched: 1 row(s)

4、复制表结构

hive (default)> create table test2 like test1;
OK
Time taken: 0.21 seconds
hive (default)> show create table test2;
OK
createtab_stmt
CREATE TABLE `test2`(
  `id` int, 
  `name` string, 
  `subo` array<string>, 
  `deb` map<string,float>, 
  `address` struct<city:string,state:string>)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'collection.delim'='_', 
  'field.delim'=',', 
  'line.delim'='\n', 
  'mapkey.delim'=':', 
  'serialization.format'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://yjt:9000/hive/warehouse/test2'
TBLPROPERTIES (
  'transient_lastDdlTime'='1593478235')

4、查看表的详细信息

hive (r_db2)> desc formatted test;  # 输出结果易读性更强
hive (r_db2)> desc extended test;

5、删除表

hive (r_db2)> drop table test; #管理表在删除的时候不仅删除元数据，同时也删除hdfs上面存在的真实数据

外部表

1、拷贝数据到hdfs

> 数据样式
[hduser@yjt hive]$ cat test.txt 
1,hadoop
2,spark
3,hive
4,hbase
5,python
> copy到hdfs
$ hadoop fs -put test.txt /user/hduser/test

2、创建外部表

hive (r_db2)> create external table test1(id int, name string) row format delimited fields terminated by ',' location "/user/hduser/test";

3、查看数据

hive (r_db2)> select * from test1;
OK
test1.id	test1.name
1	hadoop
2	spark
3	hive
4	hbase
5	python

4、删除外部表

hive (r_db2)> drop table test1;

5、查看数据是否存在

hive (r_db2)> dfs -ls /user/hduser/test/;
Found 1 items
-rw-r--r--   1 hduser supergroup         41 2020-06-30 10:32 /user/hduser/test/test.txt

管理表与分区表之间的转换

1、首先创建管理表

hive (r_db2)> create table manag_table(id int);
hive (r_db2)> desc formatted manag_table;
Table Type:         	MANAGED_TABLE

2、管理表转外部表

hive (r_db2)> alter table manag_table set tblproperties ('EXTERNAL'='TRUE');
hive (r_db2)> desc formatted manag_table;
Table Type:         	EXTERNAL_TABLE

3、外部表转管理表
把刚才转换成的外部表manag_table重新转化为内部表

hive (r_db2)> alter table manag_table set tblproperties ('EXTERNAL'='FALSE');

注意： 'EXTERNAL'='FALSE' 和 'EXTERNAL'='TRUE' 是固定写法，区分大小写，如果写错，仅仅当做表属性被显示

分区表

数据分区主要是用于高效的查询和分散压力，hive里面的分区表也是这样，对数据分区，当查询的时候只查询某个条件的时候，不用进行全表扫描。
hive分区表：所谓的分区表就是在hdfs目录上面对当前进行分区的字段创建一个目录(分层存储)，同时进行分区的字段不能在源字段内。

分区管理表

1、创建分区管理表

hive (r_db2)> create table test3(id int, name string) partitioned by(currentDate string)  row format delimited fields terminated by ',';

2、添加静态分区

hive (r_db2)> alter table test3 add partition(currentDate="2020-06-30"); # 这一步可以省略，只是为了说明可以通过alter的方式创建分区，需要注意这里的关键字是partition，创建表的时候关键字是partitioned(多了ed)；当然也支持创建多个分区，但是创建分区的时候需要以空格分离
> 创建多分区
hive (r_db2)> alter table test3 add partition(currentDate="2020-07-04") partition(currentDate="2020-07-05");
hive (r_db2)> alter table test3 add partition(currentDate="2020-07-02", currentDate="2020-07-03");    # **这种是错误的**

3、加载数据

hive (r_db2)> load data local inpath '/tmp/hive/hive1.txt' overwrite into table test3 partition(currentDate="2020-06-30");

4、查询

hive (r_db2)> select * from test3;
OK
test3.id	test3.name	test3.currentdate
1	hadoop	2020-06-30
2	spark	2020-06-30
1	hadoop	2020-07-01
2	spark	2020-07-01
Time taken: 0.2 seconds, Fetched: 4 row(s)
hive (r_db2)> select * from test3 where currentdate="2020-07-01";   # 指定分区查询
OK
test3.id	test3.name	test3.currentdate
1	hadoop	2020-07-01
2	spark	2020-07-01
> 联合查询
hive (r_db2)> select * from test3 where currentdate="2020-06-30" union select * from test3 where currentdate="2020-07-01";

5、删除分区

> 显示当前的分区数
hive (r_db2)> show partitions test3;
OK
partition
currentdate=2020-06-30
currentdate=2020-07-01
currentdate=2020-07-03
currentdate=2020-07-04
currentdate=2020-07-05
currentdate=2020-07-06

> 删除分区
hive (r_db2)> alter table test3 drop partition(currentDate="2020-07-06");    # 删除单个分区
Dropped the partition currentdate=2020-07-06
OK
Time taken: 0.315 seconds
hive (r_db2)> alter table test3 drop partition(currentDate="2020-07-04"),partition(currentDate="2020-07-05");  # 删除多个分区，注意：多分区之间是逗号，与刚刚的添加分区用空格分开是不同的。
Dropped the partition currentdate=2020-07-04
Dropped the partition currentdate=2020-07-05
OK

6、让分区表与数据产生关联的三种方式
hive当中查询数据的时候首先会从元数据里面查询对应的信息(比如实际存放数据的路径)，然后去读取数据，同理让分区表与数据产生关联，就是修改元数据的对应关系，让表可以关联到最终存放在hdfs上的数据。

方式1：
① 直接在hdfs创建分区目录
hive (r_db2)> dfs  -mkdir /hive/warehouse/r_db2.db/test3/currentdate=2020-07-04;
② 拷贝数据
hive (r_db2)> dfs -put /tmp/hive/hive2.txt /hive/warehouse/r_db2.db/test3/currentdate=2020-07-04;
③ 直接查询
hive (r_db2)> select * from test3 where currentdate="2020-07-04";  #可以看到是没有数据的，说明元数据与真实数据还没有对应上
OK
test3.id	test3.name	test3.currentdate
Time taken: 0.252 seconds
④ 修复表
hive (r_db2)> msck repair table test3;
OK
Partitions not in metastore:	test3:currentdate=2020-07-04
Repair: Added partition to metastore test3:currentdate=2020-07-04
Time taken: 0.262 seconds, Fetched: 2 row(s)
hive (r_db2)> select * from test3 where currentdate="2020-07-04";
OK
test3.id	test3.name	test3.currentdate
100	yjt	2020-07-04
200	yjl	2020-07-04
Time taken: 0.254 seconds, Fetched: 2 row(s)

方式2：
接上一步方式1，在put数据到hdfs后，不使用msck修复，而是使用alter ... add 添加分区的方式，修改元数据

方式3：
接方式1，创建完分区目录以后，直接load数据到分区表，同时指定创建的分区目录。

7、查看某个表的特定分区

hive (r_db2)> show partitions test3 partition(currentdate="2020-06-30");

8、动态分区

#开启动态分区功能
set hive.exec.dynamic.partition=true;
# 动态分区模式，默认是strict
set hive.exec.dynamic.partition.mode=nonstrict;   #如果是strict，在使用创建动态分区的时候需要先指定一个静态分区，同时静态分区在动态分区前面。

hive (r_db2)> create  table test5(id int, name string) partitioned by(score string);   #创建测试表(源表)
hive (r_db2)> create table test6 like test5;
hive (r_db2)> load data local inpath "/tmp/hive/hive2.txt" into table test5 partition(score='60'); #加载数据到源表，同时指定分区

hive (r_db2)> insert into test6  partition(score)  select s.id,s.name, s.score from test5 as s;  # 使用动态分区的方式插入数据到表test6，
或者
hive (r_db2)> insert into test6 select s.id,s.name, s.score from test5 as s;   # 测试了一下这种方式也可以，同时不需要指定动态分区为严格。

分区外部表

1、创建分区外部表

hive (r_db2)> create external  table test4(id int, name string) partitioned by(testPartion string) row format delimited fields terminated by ',';

2、添加分区和指定存储位置

hive (r_db2)> alter table test4 add partition(testPartion="2020-06-30")  location "/out1/2020-06-30";

3、创建目分区录

hive (r_db2)> dfs -mkdir /out1/2020-06-30;

4、put数据

hive (r_db2)> dfs -put "/tmp/hive/hive2.txt" "/out1/2020-06-30";

5、查询

hive (r_db2)> select * from test4 where testPartion="2020-06-30";
OK
test4.id	test4.name	test4.testpartion
100	yjt	2020-06-30
200	yjl	2020-06-30

6、查看分区的位置

hive (r_db2)> desc formatted test4 partition(testPartion="2020-06-30");