Hive学习笔记（5）- Hive 中数据库的基本操作1 创建表2 创建数据库

112 阅读 0 评论 74 点赞

我是靠谱客的博主野性玉米，这篇文章主要介绍Hive学习笔记（5）- Hive 中数据库的基本操作1 创建表2 创建数据库，现在分享给大家，希望可以做个参考。

1 创建表

1.1 建表方式1

复制代码

1
2
3
4
5
6
7
8
9
10
11
create table IF NOT EXISTS default.web_log
(
	ip string COMMENT 'remote ip address',
	user string,
	req_url string COMMENT 'user request url'
)
COMMENT 'Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/web_log'

1.2 建表方式2

复制代码

1
2
3
create table IF NOT EXISTS default.web_log_2
AS select ip,user from default.web_log;

1.3 建表方式3

复制代码

1
2
3
create table IF NOT EXISTS default.web_log_3
like default.web_log

1.4 测试

复制代码

1
2
3
4
5
6
7
8
create table IF NOT EXISTS default.web_log(
ip string COMMENT 'remote ip address',
user string,
req_url string COMMENT 'user request url')
COMMENT 'Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE;

在这里插入图片描述

复制代码

hive (default)> desc formatted default.web_log;
OK
col_name	data_type	comment
# col_name            	data_type           	comment             
	 	 
ip                  	string              	remote ip address   
user                	string              	                    
req_url             	string              	user request url    
	 	 
# Detailed Table Information	 	 
Database:           	default             	 
Owner:              	hadoop              	 
CreateTime:         	Wed Jan 16 17:09:17 CST 2019	 
LastAccessTime:     	UNKNOWN             	 
Protect Mode:       	None                	 
Retention:          	0                   	 
Location:           	hdfs://node1:8020/user/hive/warehouse/web_log	 
Table Type:         	MANAGED_TABLE       	 
Table Parameters:	 	 
	comment             	Web Access Logs     
	transient_lastDdlTime	1547629757          
	 	 
# Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	 
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Storage Desc Params:	 	 
	field.delim         	                    
	serialization.format	                    
Time taken: 0.18 seconds, Fetched: 30 row(s)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
hive (default)> desc formatted default.web_log;
OK
col_name	data_type	comment
# col_name            	data_type           	comment             
	 	 
ip                  	string              	remote ip address   
user                	string              	                    
req_url             	string              	user request url    
	 	 
# Detailed Table Information	 	 
Database:           	default             	 
Owner:              	hadoop              	 
CreateTime:         	Wed Jan 16 17:09:17 CST 2019	 
LastAccessTime:     	UNKNOWN             	 
Protect Mode:       	None                	 
Retention:          	0                   	 
Location:           	hdfs://node1:8020/user/hive/warehouse/web_log	 
Table Type:         	MANAGED_TABLE       	 
Table Parameters:	 	 
	comment             	Web Access Logs     
	transient_lastDdlTime	1547629757          
	 	 
# Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	 
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Storage Desc Params:	 	 
	field.delim         	                    
	serialization.format	                    
Time taken: 0.18 seconds, Fetched: 30 row(s)

导入数据

复制代码

1
2
load data local inpath '/home/hadoop/test_log.txt' into table default.web_log;

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
hive (default)> select * from default.web_log;
OK
web_log.ip	web_log.user	web_log.req_url
"27.38.5.159"	"-"	"31/Aug/2015:00:04:37
"27.38.5.159"	"-"	"31/Aug/2015:00:04:37
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:53
"27.38.5.159"	"-"	"31/Aug/2015:00:04:54
"27.38.5.159"	"-"	"31/Aug/2015:00:04:54
"27.38.5.159"	"-"	"31/Aug/2015:00:04:54
Time taken: 0.251 seconds, Fetched: 15 row(s)

复制代码

1
2
3
4
5
6
7
8
9
hive (default)> create table IF NOT EXISTS default.web_log_3 like default.web_log;
OK
Time taken: 0.109 seconds
hive (default)> select * from default.web_log_3;
OK
web_log_3.ip	web_log_3.user	web_log_3.req_url
Time taken: 0.082 seconds

2 创建数据库

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

复制代码

1
2
3
4
5
create database mybase_01;                                              
create database if not exists mybase_02; ----> 标准                     
create database if not exists mybase_03 location '/user/hive/warehouse';

复制代码

1
2
show databases like 'mybase*';

复制代码

1
2
3
4
5
6
7
8
9
hive (default)> show databases like 'mybase*';
OK
database_name
mybase_01
mybase_02
mybase_03
Time taken: 0.061 seconds, Fetched: 3 row(s)

2.1 查看数据库信息

复制代码

1
2
3
4
5
6
hive (default)> desc database mybase_03;
OK
db_name	comment	location	owner_name	owner_type	parameters
mybase_03		hdfs://node1:8020/user/hive/warehouse	hadoop	USER	
Time taken: 0.053 seconds, Fetched: 1 row(s)

复制代码

1
2
3
4
5
6
hive (default)> desc database extended mybase_03;
OK
db_name	comment	location	owner_name	owner_type	parameters
mybase_03		hdfs://node1:8020/user/hive/warehouse	hadoop	USER	
Time taken: 0.05 seconds, Fetched: 1 row(s)

2.2 删除数据库

如果待删除的数据库中存在表

复制代码

1
2
3
hive (default)> drop database mybase_01 cascade;

drop database if exists mybase_02;

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
hive (default)> show databases;
OK
database_name
default
mybase_01
mybase_02
mybase_03
test_hive
Time taken: 0.046 seconds, Fetched: 5 row(s)
hive (default)> drop database mybase_01 cascade;
OK
Time taken: 0.124 seconds
hive (default)> show databases;
OK
database_name
default
mybase_02
mybase_03
test_hive
Time taken: 0.02 seconds, Fetched: 4 row(s)