数据来源Mysql
mysql 建表:
CREATE TABLE IF NOT EXISTS test1 (
emp_no BIGINT,
name VARCHAR (32),
date DATE,
PRIMARY KEY (emp_no)
);
mysql 插入数据:
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (1, 'zhangsan', '2020-10-23');
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (2, 'zhangsan2', '2021-10-23');
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (3, 'zhangsan3', '2022-10-23');
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (4, 'zhangsan4', '2019-10-23');
mysql导出数据:
SELECT * FROM test1 INTO OUTFILE '/var/lib/mysql-files/test1.txt' FIELDS TERMINATED BY ',';
#将导出的文件放入hdfs上
hdfs dfs -put /var/lib/mysql-files/test1.txt /hive/data/
Hive操作:#不分区
create table test1
(
emp_no bigint,
name string,
date date
)
row format delimited
fields terminated by ',';
load data inpath '/hive/data/test1.txt' into table test1; #加载hdfs上的数据
#表内提前有数据增加字段yaer作为分区 导出到本地/root/data/emp_xxx上
FROM test1
INSERT OVERWRITE LOCAL DIRECTORY '/root/data/emp_2020' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * where year(date)=2020
INSERT OVERWRITE LOCAL DIRECTORY '/root/data/emp_2021' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * where year(date)=2021
INSERT OVERWRITE LOCAL DIRECTORY '/root/data/emp_2022' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * where year(date)=2022
#创建静态分区表
create table test_partition
(
emp_no bigint,
name string,
date date
)
partitioned by (year string)
row format delimited
fields terminated by ','
hive> show partitions test_partition; #查看分区 应是无
#加载上步分好的数据到静态分区里面
load data local inpath '/root/data/emp_2020/000000_0' into table test_partition partition(year="2020");
load data local inpath '/root/data/emp_2021/000000_0' into table test_partition partition(year="2021");
#hive> show partitions test_partition; #查看分区 应是有
#hdfs dfs -ls /user/hive/warehouse/test_partition/ 可在这里面查看
#alter table 表名 drop partition(year='xxxx'); #删除分区
或者
在hdfs上
hdfs dfs -mkdir -p /user/hive/warehouse/test.db/emp_partition/year=2020 #创建目录
hdfs dfs -put /root/data/emp_20/0000000_0 /user/hive/warehouse/test.db/emp_partition/year=2020 #上传目录
hive> show partitions test_partitoin; #再查看分区应是无
hive> msck repair table emp_partition --关联mysql的元数据 修复分区
hive> show partitions test_partitoin; #再查看分区应是有
或者
hive>alter table emp_partition add partition(year=2022) --建立分区目录,关联元数据
hive> show partitions test_partitoin; #再查看分区
hdfs dfs -put /root/data/emp_2022/000000_0 /user/hive/warehouse/test.db/emp_partition/year=2022
hive> show partitions test_partitoin; #再查看分区
#共以上三种方式
#动态分区
create table test_ac
(
emp_no bigint,
name string,
date date
)
partitioned by (year string)
row format delimited
fields terminated by ',';
set hive.exec.dynamic.partition=true; //开启动态分区功能(默认true,开启)
set hive.exec.dynamic.partition.mode=nonstrict; //设置为非严格模式(动态分区的模式,默认strict,表示必须指定至少一个分区为静态分区,nonstrict模式表示允许所有的分区字段都可以使用动态分区。
insert into test_ac partition (year) select emp_no,name,date,year(date) as year from test1;
hive> show partitions test_partitoin; #查看分区
#动态分区扩展(可不加):
#在所有执行MR的节点上,最大一共可以创建多少个动态分区。
hive.exec.max.dynamic.partitions=1000
#在每个执行MR的节点上,最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如:源数据中包含了一年的数据,即day字段有365个值,那么该参数就需要设置成大于365,如果使用默认值100,则会报错。
hive.exec.max.dynamic.partitions.pernode=100
#整个MR Job中,最大可以创建多少个HDFS文件。
hive.exec.max.created.files=100000
#当有空分区生成时,是否抛出异常。一般不需要设置。
hive.error.on.empty.partition=false
#Hive分桶
mysql 追加数据:
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (5, 'zhangsan5', '2022-10-23');
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (6, 'zhangsan6', '2023-10-23');
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (7, 'zhangsan7', '2024-10-23');
INSERT INTO `test`.`test1` (`emp_no`, `name`, `date`) VALUES (8, 'zhangsan8', '2025-10-23');
#导出数据
SELECT * FROM test1 INTO OUTFILE '/var/lib/mysql-files/test1.txt' FIELDS TERMINATED BY ',';
#Hive操作:#创建数据表
CREATE TABLE test_bs(
emp_no bigint,
name string,
date date
)
row format delimited
fields terminated by ',';
load data local inpath '/hive/data/test1.txt' into table test_bs; #加载本地
load data inpath '/hive/data/test1.txt' into table test1; #加载hdfs
SET hive.enforce.bucketing=true; #启用分块
#创建分桶表
CREATE TABLE test_bucket(
emp_no bigint,
name string,
date date
)
CLUSTERED BY(date) INTO 4 BUCKETS //按(日期)分类为4个块
row format delimited
fields terminated by ',';
#指定数据来源
from test_bs
insert into test_bucket select emp_no,name,date;
#查看hdfs上分的桶
hdfs dfs -cat /user/hive/warehouse/test_bucket/000000_0
#抽样
SELECT * FROM test_bucket TABLESAMPLE(BUCKET 1 OUT OF 4 )
SELECT * FROM test_bucket TABLESAMPLE(BUCKET 2 OUT OF 8 )
SELECT * FROM test_bucket TABLESAMPLE(BUCKET 2 OUT OF 2 )
SELECT * FROM test_bucket TABLESAMPLE(BUCKET 3 OUT OF 4 )
#规则
tablesample是抽样语句,
语法:tablesample(bucket x out of y),
y必须是table总共bucket数的倍数或者因子。
Hive根据y的大小,决定抽样的比例。
例如:table总共分了32份,
当y=16时,抽取2(32/16)个bucket的数据,
当y=64时,抽取1/2(32/64)个bucket的数据。
x表示从哪个bucket开始抽取。
例如:table总共bucket数为32,tablesample(bucket 3 out of 16)
表示总共抽取2(32/16)个bucket的数据,
分别为第三个bucket和第19(3+16)个bucket的数据。