大数据实训第五天总结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create database if not exists hive;
--创建数据库
show databases;
--查看Hive中包含数据库
show databases like 'h.*';
--查看Hive中以h开头数据库
desc database hive;
--查看hive数据库位置等信息
alter database hive set dbproperties;
--为hive设置键值对属性
use hive;
--切换到hive数据库下
drop database if exists hive;
--删除不含表的数据库
drop database if exists hive cascade;
--删除数据库和它中的表

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
--创建内部表(管理表)
create table if not exists hive.usr(
name string comment 'username',
pwd string comment 'password',
address struct<street:string,city:string,state:string,zip:int>,
comment
'home address',
identify map<int,tinyint> comment 'number,sex')
comment 'description of the table'
tblproperties('creator'='me','time'='2016.1.1');
--创建外部表
create external table if not exists usr2(
name string,
pwd string,
address struct<street:string,city:string,state:string,zip:int>,
identify map<int,tinyint>)
row format delimited fields terminated by ','
location '/usr/local/hive/warehouse/hive.db/usr';
--创建分区表
create table if not exists usr3(
name string,
pwd string,
address struct<street:string,city:string,state:string,zip:int>,
identify map<int,tinyint>)
partitioned by(city string,state string);
--复制usr表的表模式
create table if not exists hive.usr1 like hive.usr;
show tables in hive;
show tables 'u.*';
--查看hive中以u开头的表
describe hive.usr;
--查看usr表相关信息
alter table usr rename to custom;
--重命名表
--为表增加一个分区
alter table usr2 add if not exists
partition(city=”beijing”,state=”China”)
location '/usr/local/hive/warehouse/usr2/China/beijing';
--修改分区路径
alter table usr2 partition(city=”beijing”,state=”China”)
set location '/usr/local/hive/warehouse/usr2/CH/beijing';
--删除分区
alter table usr2 drop if exists
partition(city=”beijing”,state=”China”)
--修改列信息
alter table usr change column pwd password string after address;
alter table usr add columns(hobby string);
--增加列
alter table usr replace columns(uname string);
--删除替换列
alter table usr set tblproperties('creator'='liming');
--修改表属性
alter table usr2 partition(city=”beijing”,state=”China”)
--修改存储属性
set fileformat sequencefile;
use hive;
--切换到hive数据库下
drop table if exists usr1;
--删除表
drop database if exists hive cascade;
--删除数据库和它中的表

1
2
3
4
5
6
7
8
9
create view view_name as....;
--创建视图
alter view view_name set tblproperties(…);
--修改视图
drop view if exists view_name;
--删除视图
create index index_name on table table_name(partition_name/column_name)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild....;
--创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建表
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
--列分隔符
collection items terminated by '_'
--MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'
--MAP中的key与value的分隔符
lines terminated by 'n';
--行分隔符

1
2
3
4
5
6
7
8
load data local inpath '/opt/module/datas/test.txt' into table test;
--导入文本到test表
export table default.test to
'/user/hive/warehouse/export/test';
--数据导出到HDFS
insert overwrite local directory '/home/wyp/wyp'
select * from wyp;
--导出到本地