目录
分区表
分区是将一个表或索引物理地分解为多个更小、更可管理的部分。
分区对应用透明，即对访问数据库的应用而言，逻辑上讲只有一个表或一个
索引（相当于应用“看到”的只是一个表或索引），但在物理上这个表或索引可
能由数十个物理分区组成。
分区表应用场景
oracle 分区表种类
oracle 分区-范围分区
CREATE TABLE ORDER_ACTIVITIES
( ORDER_ID
NUMBER(7) NOT NULL,
ORDER_DATE
DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID
CHAR(1)
)
-- 建表语句不变
PARTITION BY RANGE (ORDER_DATE) -- 范 围 分 区 创 建 语 句 关 键 字
range( 列名 )
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY
-2003','DD-MON-YYYY')) TABLESPACE ORD_TS01,
-- 分区名 （时间值）表空间
PARTITION
ORD_ACT_PART02
VALUES
LESS
THAN
(TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (MAXVALUE) TABLESPACE
ORD_TS03
-- 使用 maxvalue 将其他不符合上述范围的值放入其中
);
oracle 分区-列表分区
CREATE TABLE ORDER_ACTIVITIES
( PROBLEM_ID
NUMBER(7) NOT NULL PRIMARY KEY,
CUSTOMER_ID NUMBER(7) NOT NULL,
STATUS
VARCHAR2(20))
PARTITION BY LIST (STATUS)
-- 范围分区创建 语句 关键字 LIST( 列
名 )
(
PARTITION PROB_ACTIVE
VALUES ('ACTIVE') TABLESPACE
PROB_TS01, --
PARTITION
PROB_INACTIVE
VALUES
('INACTIVE','unknow')
TABLESPACE PROB_TS02
);
oracle 分区-散列分区
CREATE TABLE HASH_TABLE
( COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
-- 范围分区创建 语句 关键字 HASH( 列名 )
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)
oracle 分区-组合分区
CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) (
PARTITION
P1
VALUES
LESS
THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION
P1SUB1
VALUES
('ACTIVE')
TABLESPACE
rptfact2009,
SUBPARTITION
P1SUB2
VALUES
('INACTIVE')
TABLESPACE
rptfact2009
),
PARTITION
P2
VALUES
LESS
THAN
(TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION
P2SUB1
VALUES
('ACTIVE')
TABLESPACE
rptfact2009,
SUBPARTITION
P2SUB2
VALUES
('INACTIVE')
TABLESPACE
rptfact2009
)
)
oracle 分区-分区表操作
-- 添加分区
ALTER
TABLE
SALES
ADD
PARTITION
P3
VALUES
LESS
THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
-- 注意：以上添加的分区界限应该高于最后一个 分区界限。
-- 添加了一个 P3SUB1 子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1
VALUES('COMPLETE');
-- 删除分区
ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
-- 注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想
删除此分区，必须删除表。
-- 交换分区
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH
TABLE nonpartition_name;
-- 将一个分区 ( 子分区 ) 和非分区表进行数据交换， oracle 交换的方法是其实是
对逻辑存储段进行交换。使用 INCLUDEING INDEXES 子句可以同步将本地索引
也进行交换，使用 WITH VALIDATATION 子句还可以实现行数据的验证。 -- 交换分区时如果不带 UPDATE INDEXES 子句，则全局索引或全局索引基于的
分区将变为不可用。
hive 分区-创建分区表
-- 创建分区表
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';
hive 分区-分区表操作
-- 分区表数据导入
load data local inpath '/opt/module/hive/datas/dept_20200401.log'
into table dept_partition partition(day='20200401');
--select 分区表插入数据
insert into table log_list_6 partition(dat='20221231') select *
from log_list_tmp
-- 多表分区插入
from student
insert overwrite table student partition(month='201707')select
id, name where month='201707'
insert overwrite table student partition(month='201706')select
id, name where month='201706';
-- 查看分区
show partitions tab_name;
-- 添加分区
alter table dept_partition add partition(day='20200404') ;
-- 添加多分区
alter
table
dept_partition
add
partition(day='20200405')
partition(day='20200406');
-- 删除分区
alter table dept_partition drop partition (day='20200406');
-- 查看分区表信息
show partitions dept_partition;
-- 查看分区表结构
desc formatted dept_partition;
-- 修改分区表
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION
"new location"; ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO
PARTITION (dt='20080808');
hive 分区-动态分区表配置
-- 开启动态分区 ( 默认开启 )
set hive.exec.dynamic.partition=true
-- 指定非严格模式 nonstrict 模式表示允许所有的分区字段都可以使用动态
分区
set hive.exec.dynamic.partition.mode=nonstrict
-- 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
set hive.exec.max.dynamic.partitions=1000
-- 在每个执行 MR 的节点上，最大可以创建多少个动态分区 ( 分区字段有多少种
设多少个 )
set hive.exec.max.dynamic.partitions.pernode=100
-- 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
set hive.exec.max.created.files=100000
-- 当有空分区生成时，是否抛出异常
set hive.error.on.empty.partition=false
-- 打开正则查询模式 `(dt|hr)?+.+`
set hive.support.quoted.identifiers=none
上机练习
1 清洗 超市数据 excel 为文本文件,按照 分区字段对文件进行拆分
超市数据.xlsx:
import pandas as pd
XlsxFile="D:\智云大数据\数据源\超市\超市数据.xlsx"
XlsxRead=pd.read_excel(XlsxFile)
# 选择要分组的列
group_column = '细分'
# 按照指定列分组
grouped = XlsxRead.groupby(group_column)
# 遍历每个分组，并将每个分组保存为单独的 xlsx 文件
# group_name: 分组名称
# group_df: 分组数据
for group_name, group_df in grouped:
output_file = f'D:\智云大数据\{group_name}超市数据.txt'
group_df.to_csv(output_file,
header=False,index=False,sep='\t')
print("文件拆分完成") 2 创建一个分区表 将文本文件分别 插入到分区中
create table if not exists supermarket_p (id string, -- 行 ID
ord_id string comment '订单 ID',
ord_date string comment '订单日期',
exch_date string comment '发货日期',
exch_type string comment '邮寄方式',
cust_id string comment '客户 ID
',
cust_name string comment '客户名称',
d_type string comment '细分',
city string comment '城市',
prov string comment '省/自治区',
country string comment'国家',
area string comment '地区',
pro_id string comment '产品 ID',
type1 string comment '类别',
type2 string comment '子类别',
pro_name string comment '产品名称',
sales float comment '销售额',
count1 int comment '数量 ',
discount float comment '折扣 ',
profit float comment '利润'
)
partitioned by (cd_type string)
row format delimited fields terminated by '\t'
lines TERMINATED by '\n'load data local inpath '/root/公司超市数据.txt' into table
supermarket_p
partition(cd_type='company');
load data local inpath '/root/消费者超市数据.txt' into table supermarket_p
partition(cd_type='consumer');
load data local inpath '/root/小型企业超市数据.txt' into table
supermarket_p partition(cd_type='enterprise'); 3 创建一个 按订单时间分区的分区表 (year_ string, month string)
create table if not exists supermarket_p_ord_date (
id string, -- 行 ID
ord_id string comment '订单 ID',
--ord_date string comment '订单日期',
exch_date string comment '发货日期',
exch_type string comment '邮寄方式',
cust_id string comment '客户 ID
',
cust_name string comment '客户名称',
d_type string comment '细分',
city string comment '城市',
prov string comment '省/自治区',
country string comment'国家',
area string comment '地区',
pro_id string comment '产品 ID',
type1 string comment '类别',
type2 string comment '子类别',
pro_name string comment '产品名称',sales float comment '销售额',
count1 int comment '数量 ',
discount float comment '折扣 ',
profit float comment '利润'
)
partitioned by (ord_date_month string)
row format delimited fields terminated by '\t'
lines TERMINATED by '\n' 4 将第二部的数据 使用动态分区的方法 导入到 第三步的表中
导入：
insert into table supermarket_p_ord_date partition(ord_date_month)
select id,ord_id,date_format(ord_date,'YYYY-MM') as
ord_date_month,exch_date,exch_type,cust_id,cust_name,d_type,city,prov,
country,area,pro_id,type1,
type2,pro_name,sales,count1,discount,profit
from supermarket_p; 今天的表
需要开启的
-开启动态分区(默认开启)
set hive.exec.dynamic.partition=true
--指定非严格模式 nonstrict 模式表示允许所有的分区字段都可以使用动态分区
set hive.exec.dynamic.partition.mode=nonstrict
--在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
set hive.exec.max.dynamic.partitions=1000
--在每个执行 MR 的节点上，最大可以创建多少个动态分区(分区字段有多少种设多少个)
set hive.exec.max.dynamic.partitions.pernode=100
--整个 MR
Job 中，最大可以创建多少个 HDFS 文件。默认 100000
set hive.exec.max.created.files=100000
--当有空分区生成时，是否抛出异常
set hive.error.on.empty.partition=false
--打开正则查询模式`(dt|hr)?+.+`
set hive.support.quoted.identifiers=none