第十三章——表和索引分区(2)——使用拆分删除和加载大数据

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 原文: 第十三章——表和索引分区(2)——使用拆分删除和加载大数据 前言:        很多时候需要对大数据量进行归档或者删除,并周期性加载大数据量到一个大表中,现在来做个简单的例子,你经常需要删除大数据量表中的大量数据。
原文: 第十三章——表和索引分区(2)——使用拆分删除和加载大数据

前言:

        很多时候需要对大数据量进行归档或者删除,并周期性加载大数据量到一个大表中,现在来做个简单的例子,你经常需要删除大数据量表中的大量数据。同时,你想加载大量数据到这个表中,当表中数据有数十亿时,这个操作可能消耗几个小时,但是如果你的表有分区,那么执行起来会很有效。

本文将模拟删除一个季度的数据,并加载整个季度到现有表,其中使用了拆分(splitting)、合并(merging)和切换分区(switching)。

假设我们需要存储季度数据,并且每个季度存放在一个分区。现在需要清除最久的季度和加载最新的季度数据,比如分区中包含:

1、 季度1:2011年1月到2011年3月

2、 季度2:2011年4月到2011年6月

3、 季度3:2011年7月到2011年9月

4、 季度4:2011年10月到2011年12月

现在需要删除季度1并加载2012年第一季度的数据到一个新的分区中。为了实现这个功能,可以使用在date和time列上针对每个季度创建分区。可以合并两个分区或者拆分现有有分区。

本文将演示使用滑动窗口实现,在滑动窗口中,可以清除最久的分区。

 

步骤:

1、 打开SQLServer

2、 执行下面脚本,创建一个分区函数,并使用RANGE RIGHT,然后创建一个分区架构:

USE Sample_DBUSE master
GO

IF DB_ID('Sample_DB') IS NOT NULL 
    DROP DATABASE Sample_DB
	
CREATE DATABASE Sample_DB ON PRIMARY
(
	NAME=N'Sample_DB',FILENAME=N'C:\SQLData\Sample_DB.mdf',
	SIZE=3072KB,FILEGROWTH=1024KB
) LOG ON
(
	NAME =N'Sample_DB_log',FILENAME=N'C:\SQLData\Sample_DB_log.ldf',
	SIZE=1024KB ,FILEGROWTH=10%
)
GO


USE Sample_DB
GO
CREATE PARTITION FUNCTION pf_Quaterly_RangeRight(DATETIME)
AS RANGE RIGHT FOR VALUES
('20110101','20110401','20110701','20111001','20120101')
GO

CREATE PARTITION SCHEME ps_Quaterly_RangeRight
AS PARTITION pf_Quaterly_RangeRight ALL TO ([PRIMARY])
GO

注意:当分区列是datetime类型时,应该总是尝试使用RANGE RIGHT,这样会使得分区更好,如果使用RANGE LEFT,你必须考虑特定的time部分,包含毫秒。
 

3、 创建一个表tbl_MyData并插入275000行数据:

USE Sample_DB
GO

CREATE TABLE tbl_MyData
    (
      RecordDateTime DATETIME NOT NULL ,
      RecordID INT NOT NULL ,
      RecordData VARCHAR(40) NOT NULL
    )
GO

CREATE CLUSTERED INDEX idx_tbl_MyData_RecordDateTime
ON tbl_MyData(RecordDateTime,RecordID) ON ps_Quaterly_RangeRight(RecordDateTime)

INSERT  INTO tbl_MyData
        SELECT  '2011' + RIGHT('0' + CAST(( CASE WHEN ID % 12 = 0 THEN 12
                                                 ELSE ID % 12
                                            END ) AS VARCHAR), 2) + RIGHT('0'
                                                              + CAST(( CASE
                                                              WHEN ID % 28 = 0
                                                              THEN 28
                                                              ELSE ID % 28
                                                              END ) AS VARCHAR),
                                                              2) AS RecordDateTime ,
                ID ,
                RecordData
        FROM    ( SELECT TOP 275000
                            ID = ROW_NUMBER() OVER ( ORDER BY c1.name ) ,
                            RecordData = NEWID()
                  FROM      sys.columns AS C1
                            CROSS JOIN sys.columns AS C2
                            CROSS JOIN sys.columns AS C3
                ) AS T
GO


4、 执行下面语句,检验分区数量: 
USE Sample_DB
GO

SELECT  partition_number ,
        rows
FROM    sys.partitions
WHERE   object_id = OBJECT_ID('tbl_MyData')
ORDER BY partition_number


5、 结果如下:


6、 现在先移除2011年第一个季度也就是分区1的数据:

USE Sample_DB
GO

IF OBJECT_ID('tbl_MyStagingData') IS NOT NULL 
    DROP TABLE tbl_MyStagingData

CREATE TABLE tbl_MyStagingData
    (
      RecordDateTime DATETIME NOT NULL ,
      RecordID INT NOT NULL ,
      RecordData VARCHAR(40) NOT NULL
    )
GO

CREATE CLUSTERED INDEX idx_tbl_MyStagingData_RecordDateTime ON tbl_MyStagingData(RecordDateTime,RecordID)
GO


7、 现在,切换tbl_MyData的分区2到分期表tbl_MyStagingData并清空分期表。在清空以后,检查分区数据:

USE Sample_DB
GO

ALTER TABLE tbl_MyData
SWITCH PARTITION 2 TO tbl_MyStagingData PARTITION 1
GO
TRUNCATE TABLE tbl_MyStagingData
GO


SELECT  partition_number ,
        rows
FROM    sys.partitions
WHERE   object_id = OBJECT_ID('tbl_MyData')
ORDER BY partition_number


8、 结果可见,分区已经清空:


9、 现在,把分区2与分区1合并,设置下一个使用分区架构的文件组到PRIMARY并验证数据:

 USE Sample_DB
GO

ALTER PARTITION FUNCTION pf_Quaterly_RangeRight()
MERGE RANGE ('20110101')
GO

ALTER PARTITION SCHEME ps_Quaterly_RangeRight
NEXT USED [PRIMARY]


SELECT  partition_number ,
        rows
FROM    sys.partitions
WHERE   object_id = OBJECT_ID('tbl_MyData')
ORDER BY partition_number


10、  结果如下:


11、 现在加载数据到tbl_MyData,为了实现这个步骤,需要生成一些数据并插入到分期表tbl_MyStagingData:

USE Sample_DB
GO

ALTER TABLE tbl_MyStagingData
ADD CONSTRAINT ck_tbl_MyStagingData_RecordDateTime
CHECK (RecordDateTime>='20120101' AND RecordDateTime<'20120401')

INSERT  INTO tbl_MyStagingData
        SELECT  '2012' + RIGHT('0' + CAST(( CASE WHEN ID % 3 = 0 THEN 3
                                                 ELSE ID % 3
                                            END ) AS VARCHAR), 2) + RIGHT('0'
                                                              + CAST(( CASE
                                                              WHEN ID % 28 = 0
                                                              THEN 28
                                                              ELSE ID % 28
                                                              END ) AS VARCHAR),
                                                              2) AS RecordDateTime ,
                ID ,
                RecordData
        FROM    ( SELECT TOP 275000
                            ID = ROW_NUMBER() OVER ( ORDER BY c1.name ) ,
                            RecordData = NEWID()
                  FROM      sys.columns AS C1
                            CROSS JOIN sys.columns AS C2
                            CROSS JOIN sys.columns AS C3
                ) AS T
GO



12、  现在通过拆分最后一个空的分区来创建一个新的分区,拆分后,把分区tbl_MyStagingData切换到tbl_MyData的分区5中,并设置下一个分区架构使用Primary:

USE Sample_DB
GO

ALTER PARTITION FUNCTION pf_Quaterly_RangeRight()
SPLIT RANGE ('20120401')
GO

ALTER TABLE tbl_MyStagingData
SWITCH PARTITION 1 TO tbl_MyData PARTITION 5


ALTER PARTITION SCHEME ps_Quaterly_RangeRight
NEXT USED [PRIMARY]
GO




13、现在来验证一下数据:

USE Sample_DB
GO


SELECT  partition_number ,
        rows
FROM    sys.partitions
WHERE   object_id = OBJECT_ID('tbl_MyData')
ORDER BY partition_number



14、结果如下: 

      

 

分析:

本文使用了CREATEPARTITION FUNCTION命令,首先创建了一个分区函数pf_Quaterly_RangeRight,通过定义RecordDateTime来做分区列,使得20110101~20120101进行分区。然后使用CREATE PARTITION SCHEME命令创建分区架构ps_Quaterly_RangeRight,映射所有分区到PRIMARY文件组。通过一些列的操作处理分区中的数据。

 

扩展知识:

本文显示了一个简单的例子,可以在生产环境中配置周期性实现。重点是脚本要足够智能化和动态化。

 

 

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
22天前
|
自然语言处理 大数据 应用服务中间件
大数据-172 Elasticsearch 索引操作 与 IK 分词器 自定义停用词 Nginx 服务
大数据-172 Elasticsearch 索引操作 与 IK 分词器 自定义停用词 Nginx 服务
49 5
|
22天前
|
存储 分布式计算 大数据
大数据-169 Elasticsearch 索引使用 与 架构概念 增删改查
大数据-169 Elasticsearch 索引使用 与 架构概念 增删改查
51 3
|
23天前
|
存储 分布式计算 druid
大数据-155 Apache Druid 架构与原理详解 数据存储 索引服务 压缩机制
大数据-155 Apache Druid 架构与原理详解 数据存储 索引服务 压缩机制
42 3
|
23天前
|
消息中间件 分布式计算 算法
大数据-67 Kafka 高级特性 分区 分配策略 Ranger、RoundRobin、Sticky、自定义分区器
大数据-67 Kafka 高级特性 分区 分配策略 Ranger、RoundRobin、Sticky、自定义分区器
41 3
|
23天前
|
存储 分布式计算 大数据
大数据-145 Apache Kudu 架构解读 Master Table 分区 读写
大数据-145 Apache Kudu 架构解读 Master Table 分区 读写
39 0
|
23天前
|
存储 算法 NoSQL
大数据-138 - ClickHouse 集群 表引擎详解3 - MergeTree 存储结构 数据标记 分区 索引 标记 压缩协同
大数据-138 - ClickHouse 集群 表引擎详解3 - MergeTree 存储结构 数据标记 分区 索引 标记 压缩协同
29 0
|
23天前
|
存储 消息中间件 分布式计算
大数据-137 - ClickHouse 集群 表引擎详解2 - MergeTree 存储结构 一级索引 跳数索引
大数据-137 - ClickHouse 集群 表引擎详解2 - MergeTree 存储结构 一级索引 跳数索引
27 0
|
23天前
|
缓存 分布式计算 大数据
大数据-90 Spark 集群 RDD 编程-高阶 RDD容错机制、RDD的分区、自定义分区器(Scala编写)、RDD创建方式(一)
大数据-90 Spark 集群 RDD 编程-高阶 RDD容错机制、RDD的分区、自定义分区器(Scala编写)、RDD创建方式(一)
33 0
|
23天前
|
分布式计算 算法 大数据
大数据-90 Spark 集群 RDD 编程-高阶 RDD容错机制、RDD的分区、自定义分区器(Scala编写)、RDD创建方式(二)
大数据-90 Spark 集群 RDD 编程-高阶 RDD容错机制、RDD的分区、自定义分区器(Scala编写)、RDD创建方式(二)
36 0
|
22天前
|
存储 机器学习/深度学习 分布式计算
大数据技术——解锁数据的力量,引领未来趋势
【10月更文挑战第5天】大数据技术——解锁数据的力量,引领未来趋势