概要：基于HIVE创建分区表是基本操作，使用基本的建表语句就可以创建分区表。而基于关系型数据库SQLSERVER 创建分区表就要麻烦些，基本上有以下几个步骤：
1、创建文件组，创建文件并添加到文件组
2、创建分区函数（多个分区方案可以共用一个分区函数）
3、创建分区方案
4、创建分区表
5、定时维护分区边界值及分区方案
以下是我在建多个分区表时通过学习与实践，总结出来的内容。
1、创建文件组
SQL文件组就是文件的逻辑集合。它的目的是为了方便数据的管理和分配。文件组可以把指定的文件组合在一起。SQL建表语句中的 on [primary] 就是将表建在该数据库默认文件组上。
若以天为分区单位，可以创建32个文件组，对应一月中的日期，每月复用（31个分区边界值创建32个组，所以创建32个文件组）。
--创建文件组语句
USE MYDB;
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY00]
GO
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY01]
GO
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY02]
GO
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY03]
GO
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY04]
GO
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY05]
GO
…………
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY30]
GO
ALTER DATABASE MYDB ADD FILEGROUP [FGDAY31]
GO
2、创建文件，并将文件添加到文件组
用户自定义的数据文件建议用次要文件，次要数据文件的建议文件扩展名是 .ndf。
--创建文件语句
USE MYDB;
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY01', FILENAME = N'D:\folder\FDAY01.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]
GO
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY02', FILENAME = N'D:\folder\FDAY02.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]
GO
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY03', FILENAME = N'D:\folder\FDAY03.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]
GO
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY04', FILENAME = N'D:\folder\FDAY04.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]
GO
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY05', FILENAME = N'D:\folder\FDAY05.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]
GO
…………
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY31', FILENAME = N'D:\folder\FDAY31.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]
GO
ALTER DATABASE MYDB ADD FILE ( NAME = N'FDAY00', FILENAME = N'D:\folder\FDAY00.ndf' , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]
GO
3、创建分区函数
根据要设置的分区时间段的情况选取合适的31个连续的日期作为分区函数的边界值。我这里选取距离建表日期最近的31天的月份作为初始的分区边界值。边界的归属于哪个分区取决于创建的分区函数。如果是right，边界归属于右边；如果是left，边界归属左边。下面创建的分区函数适合以日期为分区边界的分区表，所以多个分区表可以共用一个分区函数。
--创建分区函数
USE MYDB;
CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)
AS RANGE right FOR VALUES
(
'2023-10-01 00:00:00',
'2023-10-02 00:00:00',
'2023-10-03 00:00:00',
'2023-10-04 00:00:00',
'2023-10-05 00:00:00',
'2023-10-06 00:00:00',
'2023-10-07 00:00:00',
'2023-10-08 00:00:00',
'2023-10-09 00:00:00',
'2023-10-10 00:00:00',
'2023-10-11 00:00:00',
'2023-10-12 00:00:00',
'2023-10-13 00:00:00',
'2023-10-14 00:00:00',
'2023-10-15 00:00:00',
'2023-10-16 00:00:00',
'2023-10-17 00:00:00',
'2023-10-18 00:00:00',
'2023-10-19 00:00:00',
'2023-10-20 00:00:00',
'2023-10-21 00:00:00',
'2023-10-22 00:00:00',
'2023-10-23 00:00:00',
'2023-10-24 00:00:00',
'2023-10-25 00:00:00',
'2023-10-26 00:00:00',
'2023-10-27 00:00:00',
'2023-10-28 00:00:00',
'2023-10-29 00:00:00',
'2023-10-30 00:00:00',
'2023-10-31 00:00:00');
);
4、创建分区方案
分区方案的创建可以分为2种情况：例如我为新的分区表创建以日期为边界值的分区方案
1、日期分区函数首次创建，分区边界值和文件组个数匹配
那么可以直接将分区函数的分区与文件组映射：
USE MYDB;
CREATE PARTITION SCHEME part_day_rang_scheme
AS PARTITION part_day_rang_func
TO (
FGDAY00,
FGDAY01,
FGDAY02,
FGDAY03,
FGDAY04,
FGDAY05,
FGDAY06,
FGDAY07,
FGDAY08,
FGDAY09,
FGDAY10,
FGDAY11,
FGDAY12,
FGDAY13,
FGDAY14,
FGDAY15,
FGDAY16,
FGDAY17,
FGDAY18,
FGDAY19,
FGDAY20,
FGDAY21,
FGDAY22,
FGDAY23,
FGDAY24,
FGDAY25,
FGDAY26,
FGDAY27,
FGDAY28,
FGDAY29,
FGDAY30,
FGDAY31
);
2、已有日期分区函数，且分区边界值多于文件组个数
那么就要根据现有的边界值数循环映射到文件组上，以起到复用文件组的目的：
USE MYDB;
declare @par_num int = (SELECT max(boundary_id) id FROM sys.partition_range_values)
declare @i int =1
declare @files_no varchar(max) = 'FGDAY00'
declare @sql varchar(max)
while @i <= @par_num
begin
set @files_no= @files_no+',
'+ 'FGDAY'+(SELECT RIGHT('0'+ DATENAME(DAY, CONVERT(date,value,120)),2) file_no FROM sys.partition_range_values WHERE boundary_id=@i)
set @i= @i+1
end
set @sql=
'CREATE PARTITION SCHEME part_day_rang_scheme
AS PARTITION part_day_rang_func
TO ('+@files_no+');'
exec (@sql)
5、创建分区表
创建分区表时主键需要包含分区字段，建表语句的默认的文件组需要修改。
USE MYDB;
CREATE TABLE [dbo].[table](
column1 int not null,
column2 char(20) null,
column3 varchar(20) null,
[etl_time] [datetime] not null,
CONSTRAINT [pk_simple] PRIMARY KEY NONCLUSTERED
( column1 ASC, etl_time ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [part_day_rang_scheme]([etl_time])
) ON [part_day_rang_scheme]([etl_time])
6、创建定时JOB
创建存储过程及定时作业来新增分区函数边界值及更新分区方案。我一般设置的作业执行频率是按天执行，所以存储过程里默认创建下个日期的分区。
USE MYDB;
CREATE PROCEDURE [dbo].[sp_PartitionManage]
AS
BEGIN
DECLARE @flag CHAR(1) --标志位
-- 1.修改分区方案和分区函数,当天第二天的日期
BEGIN
DECLARE @td_next DATETIME
DECLARE @day_next VARCHAR(2)
DECLARE @sql NVARCHAR(MAX) --动态sql字符串
SET @td_next = DATEADD(DAY, 1, GETDATE()) --下一天日期
SET @day_next = RIGHT('0'+ DATENAME(DAY, @td_next),2)
SELECT @flag = COUNT(1)
FROM sys.partition_functions a ,
sys.partition_range_values b
WHERE a.name = 'part_day_rang_func'
AND a.function_id = b.function_id
AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next, 120)+ ' 00:00:00.000'
--PRINT @flag;
IF ( @flag = '0' )
BEGIN
SET @sql = 'alter partition scheme part_day_rang_scheme next used FGDAY'+ @day_next + ';
alter partition function part_day_rang_func() split range('''+ CONVERT(VARCHAR(10), @td_next, 120) + ''')'
EXEC sp_executesql @sql
END
END
END
GO
上述步骤就是创建分区表的完整过程了。
7、常用分区查询语句
--查询分区函数
SELECT *
FROM sys.partition_functions;
--查询分区函数边界值
SELECT *
FROM sys.partition_range_values;
--查询分区方案
SELECT *
FROM sys.partition_schemes;
参考文章
https://www.cnblogs.com/datazhang/p/4724705.html
https://it.cha138.com/android/show-65163.html#_41
https://codeleading.com/article/95273205222/
扫一扫
715
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
