1,小明1,lol-book-movie,beijing:shangxuetang-shanghai:pudong
2,小明2,lol-book-movie,beijing:shangxuetang-shanghai:pudong
3,小明3,lol-book-movie,beijing:shangxuetang-shanghai:pudong
4,小明4,lol-book-movie,beijing:shangxuetang-shanghai:pudong
5,小明5,lol-movie,beijing:shangxuetang-shanghai:pudong
6,小明6,lol-book-movie,beijing:shangxuetang-shanghai:pudong
7,小明7,lol-book-movie,beijing:shangxuetang-shanghai:pudong
8,小明8,lol-book-movie,beijing:shangxuetang-shanghai:pudong
内部表:
建表 :
create table psn
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
加载数据,类似于sql的insert
load data local inpath '/root/data/data' into table psn;
外部表:
先创建文件,再创建表;
hdfs dfs -mkdir /usr
hdfs dfs -put data /usr
外部表
create external table psn4
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/usr/';
内部表和外部表的区别:
1、创建表的时候,内部表直接存储再默认的hdfs路径,外部表需要自己指定路径
2、删除表的时候,内部表会将数据和元数据全部删除,外部表只删除元数据,数据不删除
注意:hive:读时检查(实现解耦,提高数据记载的效率)
关系型数据库:写时检查
分区(每一个分区 相当于一个新的层次结构):
单分区
create table psn5
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
加载数据(插入数据):
load data local inpath '/root/data/data' into table psn5 partition(age=10);
双分区(多分区):
create table psn6
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
加载数据(插入数据):
load data local inpath '/root/data/data' into table psn6 partition(age=10,sex='man');
分区是按照 name匹配的,可以乱序
分区是在表定义时就给定了的,无法在后面添加,但是可以添加/修改分区的值,(这里就是创建新的目录),不添加值。加的时候,分区节点不能少
alter table psn6 add partition(age=30,sex=‘woman’);
但是删的时候可以少:
alter table psn6 drop partition(age=20);
给新创建的表添加已存在的分区:
[root@node4 data]# hdfs dfs -mkdir -p /usr/age=10
[root@node4 data]# hdfs dfs -mkdir -p /usr/age=20
[root@node4 data]# hdfs dfs -put data /usr/age=10/
[root@node4 data]# hdfs dfs -put data /usr/age=20/
create external table psn7
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/usr/';
修复分区:
msck repair table psn7;
静态分区:分区的值是认为指定的
动态分区:分区的值是由记录的某一列值来决定的
创建表的第二种方式:
create table psn8 as select * from psn7;
创建表的第三种方式:
create table psn9 like psn7;
hive 动态分区
开启支持动态分区
set hive.exec.dynamic.partition=true;
默认:true
set hive.exec.dynamic.partition.mode=nostrict;
默认:strict(至少有一个分区列是静态分区)
相关参数
set hive.exec.max.dynamic.partitions.pernode;
每一个执行mr节点上,允许创建的动态分区的最大数量(100)
set hive.exec.max.dynamic.partitions;
所有执行mr节点上,允许创建的所有动态分区的最大数量(1000)
set hive.exec.max.created.files;
所有的mr job允许创建的文件的最大数量(100000)
动态分区实现案列:
1,小明1,11,girl,lol-book-movie,beijing:shangxuetang-shanghai:pudong
2,小明2,12,boy,lol-book-movie,beijing:shangxuetang-shanghai:pudong
3,小明3,13,girl,lol-book-movie,beijing:shangxuetang-shanghai:pudong
4,小明4,14,boy,lol-book-movie,beijing:shangxuetang-shanghai:pudong
5,小明5,15,girl,lol-movie,beijing:shangxuetang-shanghai:pudong
6,小明6,16,boy,lol-book-movie,beijing:shangxuetang-shanghai:pudong
7,小明7,17,girl,lol-book-movie,beijing:shangxuetang-shanghai:pudong
8,小明8,18,boy,lol-book-movie,beijing:shangxuetang-shanghai:pudong
创建第一张表:
create table psn21(
id int ,
name string,
age int,
gender string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
将数据加载到表中:
load data local inpath '/root/data/xiaomin' into table psn21;
创建第二张表:
create table psn22(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int,gender string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
将 第一张表中的数据按照分区规则加载到第二张表中:
from psn21
insert into psn22 partition(age,gender)
select id,name,likes,address,age,gender;
或者:
insert into psn22 partition(age,gender)
select id,name,likes,address,age,gender from psn21;