You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2022/10/20 00:57:00 UTC
[doris] branch master updated: [doc](storage policy) add cold and hot separation docs (#13096)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new bc08854a35 [doc](storage policy) add cold and hot separation docs (#13096)
bc08854a35 is described below
commit bc08854a35205141a7cd2a3c9613c4b2fc194cb2
Author: deardeng <56...@qq.com>
AuthorDate: Thu Oct 20 08:56:53 2022 +0800
[doc](storage policy) add cold and hot separation docs (#13096)
---
docs/en/docs/advanced/cold_hot_separation.md | 117 +++++++++++++++++++++
.../Alter/ALTER-RESOURCE.md | 16 +++
.../Alter/ALTER-STORAGE-POLICY.md} | 25 +++--
.../Alter/ALTER-TABLE-PROPERTY.md | 13 +++
.../Create/CREATE-POLICY.md | 11 +-
.../Create/CREATE-RESOURCE.md | 20 ++--
.../Create/CREATE-TABLE.md | 31 ++++++
.../Data-Definition-Statements/Drop/DROP-POLICY.md | 5 -
.../sql-reference/Show-Statements/SHOW-POLICY.md | 36 +++++--
docs/sidebars.json | 9 +-
docs/zh-CN/docs/advanced/cold_hot_separation.md | 117 +++++++++++++++++++++
.../Alter/ALTER-RESOURCE.md | 16 +++
.../{ALTER-RESOURCE.md => ALTER-STORAGE-POLICY.md} | 21 ++--
.../Alter/ALTER-TABLE-PROPERTY.md | 11 ++
.../Create/CREATE-POLICY.md | 12 ++-
.../Create/CREATE-RESOURCE.md | 20 ++--
.../Create/CREATE-TABLE.md | 31 ++++++
.../Data-Definition-Statements/Drop/DROP-POLICY.md | 10 --
.../sql-reference/Show-Statements/SHOW-POLICY.md | 26 ++++-
19 files changed, 473 insertions(+), 74 deletions(-)
diff --git a/docs/en/docs/advanced/cold_hot_separation.md b/docs/en/docs/advanced/cold_hot_separation.md
new file mode 100644
index 0000000000..03259e701f
--- /dev/null
+++ b/docs/en/docs/advanced/cold_hot_separation.md
@@ -0,0 +1,117 @@
+---
+{
+ "title": "cold hot separation",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# cold hot separation
+
+## Demand scenario
+
+A big usage scenario in the future is similar to the es log storage. In the log scenario, the data will be cut by date. Many data are cold data, with few queries. Therefore, the storage cost of such data needs to be reduced. From the perspective of saving storage costs
+1. The price of ordinary cloud disks of cloud manufacturers is higher than that of object storage
+2. In the actual online use of the doris cluster, the utilization rate of ordinary cloud disks cannot reach 100%
+3. Cloud disk is not paid on demand, but object storage can be paid on demand
+4. High availability based on ordinary cloud disks requires multiple replicas, and a replica migration is required for a replica exception. This problem does not exist when data is placed on the object store, because the object store is shared。
+
+## Solution
+Set the freeze time on the partition level to indicate how long the partition will be frozen, and define the location of remote storage stored after the freeze. On the be, the daemon thread will periodically determine whether the table needs to be frozen. If it does, it will upload the data to s3.
+
+The cold and hot separation supports all doris functions, but only places some data on object storage to save costs without sacrificing functions. Therefore, it has the following characteristics:
+
+- When cold data is stored on object storage, users need not worry about data consistency and data security
+- Flexible freeze policy, cooling remote storage property can be applied to table and partition levels
+- Users query data without paying attention to the data distribution location. If the data is not local, they will pull the data on the object and cache it to be local
+- Optimization of replica clone. If the stored data is on the object, the replica clone does not need to pull the stored data locally
+- Remote object space recycling recycler. If the table and partition are deleted, or the space is wasted due to abnormal conditions in the cold and hot separation process, the recycler thread will periodically recycle, saving storage resources
+- Cache optimization, which caches the accessed cold data to be local, achieving the query performance of non cold and hot separation
+- Be thread pool optimization, distinguish whether the data source is local or object storage, and prevent the delay of reading objects from affecting query performance
+
+## Storage policy
+
+The storage policy is the entry to use the cold and hot separation function. Users only need to associate a storage policy with a table or partition during table creation or doris use. that is, they can use the cold and hot separation function.
+
+For example:
+
+```
+CREATE RESOURCE "remote_s3"
+PROPERTIES
+(
+ "type" = "s3",
+ "s3_endpoint" = "bj.s3.com",
+ "s3_region" = "bj",
+ "s3_bucket" = "test-bucket",
+ "s3_root_path" = "path/to/root",
+ "s3_access_key" = "bbb",
+ "s3_secret_key" = "aaaa",
+ "s3_max_connections" = "50",
+ "s3_request_timeout_ms" = "3000",
+ "s3_connection_timeout_ms" = "1000"
+);
+
+CREATE STORAGE POLICY test_policy
+PROPERTIES(
+ "storage_resource" = "remote_s3",
+ "cooldown_ttl" = "1d"
+);
+
+CREATE TABLE IF NOT EXISTS create_table_use_created_policy
+(
+ k1 BIGINT,
+ k2 LARGEINT,
+ v1 VARCHAR(2048)
+)
+UNIQUE KEY(k1)
+DISTRIBUTED BY HASH (k1) BUCKETS 3
+PROPERTIES(
+ "storage_policy" = "test_policy"
+);
+```
+Or for an existing table, associate the storage policy
+```
+ALTER TABLE create_table_not_have_policy set ("storage_policy" = "test_policy");
+```
+Or associate a storage policy with an existing partition
+```
+ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="test_policy");
+```
+For details, please refer to the resource, policy, create table, alter and other documents in the docs directory
+
+### Some restrictions
+
+- A single table or a single partition can only be associated with one storage policy. After association, the storage policy cannot be dropped
+- The object information associated with the storage policy does not support modifying the data storage path information, such as bucket, endpoint, and root_ Path and other information
+- Currently, the storage policy only supports creation, not deletion
+
+## Show size of objects occupied by cold data
+方式一：
+Through show proc '/backends', you can view the size of each object being uploaded to, and the RemoteUsedCapacity item.
+
+方式二：
+Through show tables from tableName, you can view the object size occupied by each table, and the RemoteDataSize item.
+
+
+## Unfinished Matters
+
+- After the data is frozen, there are new data updates or imports, etc. The compression has not been processed at present.
+- The schema change operation after the data is frozen is not supported at present.
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
index 82633b35ea..2bc5484ab0 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
@@ -54,6 +54,22 @@ ALTER RESOURCE 'spark0' PROPERTIES ("working_dir" = "hdfs://127.0.0.1:10000/tmp/
ALTER RESOURCE 'remote_s3' PROPERTIES ("s3_max_connections" = "100");
```
+3. Modify information related to cold and hot separation S3 resources
+- Support
+ - `s3_max_connections` : default 50
+ - `s3_connection_timeout_ms` : default 1000ms
+ - `s3_secret_key` : s3 sk
+ - `s3_access_key` : s3 ak
+ - `s3_request_timeout_ms` : default 3000ms
+- Not Support
+ - `s3_region`
+ - `s3_bucket`
+ - `s3_root_path`
+ - `s3_endpoint`
+
+```sql
+ ALTER RESOURCE "showPolicy_1_resource" PROPERTIES("s3_max_connections" = "1111");
+```
### Keywords
```text
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY.md
similarity index 59%
copy from docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
copy to docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY.md
index fb4555ddee..ed17566afd 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY.md
@@ -1,7 +1,7 @@
---
{
-"title": "ALTER-RESOURCE",
-"language": "zh-CN"
+"title": "ALTER-POLICY",
+"language": "en"
}
---
@@ -24,36 +24,35 @@ specific language governing permissions and limitations
under the License.
-->
-## ALTER-RESOURCE
+## ALTER-POLICY
### Name
-ALTER RESOURCE
+ALTER STORAGE POLICY
### Description
-该语句用于修改一个已有的资源。仅 root 或 admin 用户可以修改资源。
-语法：
+This statement is used to modify an existing cold and hot separation migration strategy. Only root or admin users can modify resources.
+
```sql
-ALTER RESOURCE 'resource_name'
+ALTER STORAGE POLICY 'policy_name'
PROPERTIES ("key"="value", ...);
```
-注意：resource type 不支持修改。
### Example
-1. 修改名为 spark0 的 Spark 资源的工作目录：
+1. Modify the name to coolown_datetime Cold and hot separation data migration time point:
```sql
-ALTER RESOURCE 'spark0' PROPERTIES ("working_dir" = "hdfs://127.0.0.1:10000/tmp/doris_new");
+ALTER STORAGE POLICY has_test_policy_to_alter PROPERTIES("cooldown_datetime" = "2023-06-08 00:00:00");
```
-2. 修改名为 remote_s3 的 S3 资源的最大连接数：
+2. Modify the name to coolown_countdown of hot and cold separation data migration of ttl
```sql
-ALTER RESOURCE 'remote_s3' PROPERTIES ("s3_max_connections" = "100");
+ALTER STORAGE POLICY has_test_policy_to_alter PROPERTIES ("cooldown_ttl" = "10000");
```
### Keywords
```sql
-ALTER, RESOURCE
+ALTER, STORAGE, POLICY
```
### Best Practice
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md
index b47a64e99e..b520c823c8 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md
@@ -252,6 +252,19 @@ ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2
ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
```
+12. Add a cold and hot separation data migration strategy to the table
+```sql
+ ALTER TABLE create_table_not_have_policy set ("storage_policy" = "created_create_table_alter_policy");
+```
+NOTE：The table can be successfully added only if it hasn't been associated with a storage policy. A table just can have one storage policy.
+
+13. Add a hot and cold data migration strategy to the partition of the table
+```sql
+ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="created_create_table_partition_alter_policy");
+```
+NOTE：The table's partition can be successfully added only if it hasn't been associated with a storage policy. A table just can have one storage policy.
+
+
### Keywords
```text
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md
index 73e7b240c7..86f3dc6f76 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md
@@ -94,7 +94,10 @@ illustrate：
```
2. Create policy for storage
- 1. Create policy on cooldown_datetime
+ 1. NOTE
+ - To create a cold hot separation policy, you must first create a resource, and then associate the created resource name when creating a migration policy
+ - Currently, the drop data migration policy is not supported to prevent data from being migrated. If the policy has been deleted, then the system cannot retrieve the data
+ 2. Create policy on cooldown_datetime
```sql
CREATE STORAGE POLICY testPolicy
PROPERTIES(
@@ -102,7 +105,7 @@ illustrate：
"cooldown_datetime" = "2022-06-08 00:00:00"
);
```
- 2. Create policy on cooldown_ttl
+ 3. Create policy on cooldown_ttl
```sql
CREATE STORAGE POLICY testPolicy
PROPERTIES(
@@ -110,6 +113,10 @@ illustrate：
"cooldown_ttl" = "1d"
);
```
+ Relevant parameters are as follows:
+ - `storage_resource`: the storage resource of create
+ - `cooldown_datetime`: Data migration time
+ - `cooldown_ttl`: Countdown of the distance between the migrated data and the current time
### Keywords
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md
index 2a6604a4c4..e1cbb66d7a 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md
@@ -118,8 +118,9 @@ illustrate:
PROPERTIES
(
"type" = "s3",
- "s3_endpoint" = "http://bj.s3.com",
+ "s3_endpoint" = "bj.s3.com",
"s3_region" = "bj",
+ "s3_bucket" = "test-bucket",
"s3_root_path" = "/path/to/root",
"s3_access_key" = "bbb",
"s3_secret_key" = "aaaa",
@@ -131,15 +132,16 @@ illustrate:
S3 related parameters are as follows:
- Required parameters
- - s3_endpoint: s3 endpoint
- - s3_region: s3 region
- - s3_root_path: s3 root directory
- - s3_access_key: s3 access key
- - s3_secret_key: s3 secret key
+ - `s3_endpoint`: s3 endpoint
+ - `s3_region`: s3 region
+ - `s3_root_path`: s3 root directory
+ - `s3_access_key`: s3 access key
+ - `s3_secret_key`: s3 secret key
+ - `s3_bucket`: s3 bucket
- optional parameter
- - s3_max_connections: the maximum number of s3 connections, the default is 50
- - s3_request_timeout_ms: s3 request timeout, in milliseconds, the default is 3000
- - s3_connection_timeout_ms: s3 connection timeout, in milliseconds, the default is 1000
+ - `s3_max_connections`: the maximum number of s3 connections, the default is 50
+ - `s3_request_timeout_ms`: s3 request timeout, in milliseconds, the default is 3000
+ - `s3_connection_timeout_ms`: s3 connection timeout, in milliseconds, the default is 1000
### Keywords
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md
index 3711779a63..007d56c408 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md
@@ -572,6 +572,37 @@ distribution_desc
);
```
+11. Set the table hot and cold separation policy through the `storage_policy` property.
+```
+ CREATE TABLE IF NOT EXISTS create_table_use_created_policy
+ (
+ k1 BIGINT,
+ k2 LARGEINT,
+ v1 VARCHAR(2048)
+ )
+ UNIQUE KEY(k1)
+ DISTRIBUTED BY HASH (k1) BUCKETS 3
+ PROPERTIES(
+ "storage_policy" = "test_create_table_use_policy",
+ "replication_num" = "1"
+ );
+```
+NOTE: Need to create the s3 resource and storage policy before the table can be successfully associated with the migration policy
+
+12. Add a hot and cold data migration strategy for the table partition
+```
+ CREATE TABLE create_table_partion_use_created_policy
+ (
+ k1 DATE,
+ k2 INT,
+ V1 VARCHAR(2048) REPLACE
+ ) PARTITION BY RANGE (k1) (
+ PARTITION p1 VALUES LESS THAN ("2022-01-01") ("storage_policy" = "test_create_table_partition_use_policy_1" ,"replication_num"="1"),
+ PARTITION p2 VALUES LESS THAN ("2022-02-01") ("storage_policy" = "test_create_table_partition_use_policy_2" ,"replication_num"="1")
+ ) DISTRIBUTED BY HASH(k2) BUCKETS 1;
+```
+NOTE: Need to create the s3 resource and storage policy before the table can be successfully associated with the migration policy
+
### Keywords
CREATE, TABLE
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md
index 38c553a3e9..088eebabef 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md
@@ -61,11 +61,6 @@ DROP STORAGE POLICY policy_name1
```sql
DROP ROW POLICY test_row_policy_1 on table1 for test
```
-
-3. Drop the storage policy named policy_name1
- ```sql
- DROP STORAGE POLICY policy_name1
- ```
### Keywords
DROP, POLICY
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md b/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
similarity index 62%
copy from docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
copy to docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
index 2d7e2604d7..8d66c17ff2 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
+++ b/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
@@ -1,7 +1,7 @@
---
{
- "title": "SHOW-ROW-POLICY",
- "language": "zh-CN"
+ "title": "SHOW-POLICY",
+ "language": "en"
}
---
@@ -32,9 +32,7 @@ SHOW ROW POLICY
### Description
-查看当前 DB 下的行安全策略
-
-语法：
+View the row security policy under the current DB
```sql
SHOW ROW POLICY [FOR user]
@@ -42,7 +40,7 @@ SHOW ROW POLICY [FOR user]
### Example
-1. 查看所有安全策略。
+1. view all security policies.
```sql
mysql> SHOW ROW POLICY;
@@ -57,7 +55,7 @@ SHOW ROW POLICY [FOR user]
2 rows in set (0.00 sec)
```
-2. 指定用户名查询
+2. specify user name query
```sql
mysql> SHOW ROW POLICY FOR test;
@@ -69,7 +67,29 @@ SHOW ROW POLICY [FOR user]
+-------------------+----------------------+-----------+------+------------+-------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
```
-
+
+3. demonstrate data migration strategies
+ ```sql
+ mysql> SHOW STORAGE POLICY;
+ +---------------------+---------+-----------------------+---------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ | PolicyName | Type | StorageResource | CooldownDatetime | CooldownTtl | properties |
+ +---------------------+---------+-----------------------+---------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ | showPolicy_1_policy | STORAGE | showPolicy_1_resource | 2022-06-08 00:00:00 | -1 | {
+ "s3_secret_key": "******",
+ "s3_region": "bj",
+ "s3_access_key": "bbba",
+ "s3_max_connections": "50",
+ "s3_connection_timeout_ms": "1000",
+ "type": "s3",
+ "s3_root_path": "path/to/rootaaaa",
+ "s3_bucket": "test-bucket",
+ "s3_endpoint": "bj.s3.comaaaa",
+ "s3_request_timeout_ms": "3000"
+ } |
+ +---------------------+---------+-----------------------+---------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+ ```
+
### Keywords
diff --git a/docs/sidebars.json b/docs/sidebars.json
index a4bfea09f6..31193a7e78 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -163,7 +163,8 @@
"advanced/using-hll",
"advanced/variables",
"advanced/time-zone",
- "advanced/small-file-mgr"
+ "advanced/small-file-mgr",
+ "advanced/cold_hot_separation"
]
},
{
@@ -657,7 +658,8 @@
"sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-REPLACE",
"sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY",
"sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-ROLLUP",
- "sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-RENAME"
+ "sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-RENAME",
+ "sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY"
]
},
{
@@ -845,7 +847,8 @@
"sql-manual/sql-reference/Show-Statements/SHOW-ALTER",
"sql-manual/sql-reference/Show-Statements/SHOW-SMALL-FILES",
"sql-manual/sql-reference/Show-Statements/SHOW-CREATE-TABLE",
- "sql-manual/sql-reference/Show-Statements/SHOW-CHARSET"
+ "sql-manual/sql-reference/Show-Statements/SHOW-CHARSET",
+ "sql-manual/sql-reference/Show-Statements/SHOW-POLICY"
]
},
{
diff --git a/docs/zh-CN/docs/advanced/cold_hot_separation.md b/docs/zh-CN/docs/advanced/cold_hot_separation.md
new file mode 100644
index 0000000000..71720acc40
--- /dev/null
+++ b/docs/zh-CN/docs/advanced/cold_hot_separation.md
@@ -0,0 +1,117 @@
+---
+{
+ "title": "冷热分离",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# 冷热分离
+
+## 需求场景
+
+未来一个很大的使用场景是类似于es日志存储，日志场景下数据会按照日期来切割数据，很多数据是冷数据，查询很少，需要降低这类数据的存储成本。从节约存储成本角度考虑
+1. 各云厂商普通云盘的价格都比对象存储贵
+2. 在doris集群实际线上使用中，普通云盘的利用率无法达到100%
+3. 云盘不是按需付费，而对象存储可以做到按需付费
+4. 基于普通云盘做高可用，需要实现多副本，某副本异常要做副本迁移。而将数据放到对象存储上则不存在此类问题，因为对象存储是共享的。
+
+## 解决方案
+在Partition级别上设置freeze time，表示多久这个Partition会被freeze，并且定义freeze之后存储的remote storage的位置。在be上daemon线程会周期性的判断表是否需要freeze，若freeze后会将数据上传到s3上。
+
+冷热分离支持所有doris功能，只是把部分数据放到对象存储上，以节省成本，不牺牲功能。因此有如下特点：
+
+- 冷数据放到对象存储上，用户无需担心数据一致性和数据安全性问题
+- 灵活的freeze策略，冷却远程存储property可以应用到表和partition级别
+- 用户查询数据，无需关注数据分布位置，若数据不在本地，会拉取对象上的数据，并cache到be本地
+- 副本clone优化，若存储数据在对象上，则副本clone的时候不用去拉取存储数据到本地
+- 远程对象空间回收recycler，若表、分区被删除，或者冷热分离过程中异常情况产生的空间浪费，则会有recycler线程周期性的回收，节约存储资源
+- cache优化，将访问过的冷数据cache到be本地，达到非冷热分离的查询性能
+- be线程池优化，区分数据来源是本地还是对象存储，防止读取对象延时影响查询性能
+
+## Storage policy的使用
+
+存储策略是使用冷热分离功能的入口，用户只需要在建表或使用doris过程中，给表或分区关联上storage policy，即可以使用冷热分离的功能。
+
+例如：
+
+```
+CREATE RESOURCE "remote_s3"
+PROPERTIES
+(
+ "type" = "s3",
+ "s3_endpoint" = "bj.s3.com",
+ "s3_region" = "bj",
+ "s3_bucket" = "test-bucket",
+ "s3_root_path" = "path/to/root",
+ "s3_access_key" = "bbb",
+ "s3_secret_key" = "aaaa",
+ "s3_max_connections" = "50",
+ "s3_request_timeout_ms" = "3000",
+ "s3_connection_timeout_ms" = "1000"
+);
+
+CREATE STORAGE POLICY test_policy
+PROPERTIES(
+ "storage_resource" = "remote_s3",
+ "cooldown_ttl" = "1d"
+);
+
+CREATE TABLE IF NOT EXISTS create_table_use_created_policy
+(
+ k1 BIGINT,
+ k2 LARGEINT,
+ v1 VARCHAR(2048)
+)
+UNIQUE KEY(k1)
+DISTRIBUTED BY HASH (k1) BUCKETS 3
+PROPERTIES(
+ "storage_policy" = "test_policy"
+);
+```
+或者对一个已存在的表，关联storage policy
+```
+ALTER TABLE create_table_not_have_policy set ("storage_policy" = "test_policy");
+```
+或者对一个已存在的partition，关联storage policy
+```
+ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="test_policy");
+```
+具体可以参考docs目录下resource、policy、create table、alter等文档，里面有详细介绍
+
+### 一些限制
+
+- 单表或单partition只能关联一个storage policy，关联后不能drop掉storage policy
+- storage policy关联的对象信息不支持修改数据存储path的信息，比如bucket、endpoint、root_path等信息
+- storage policy目前只支持创建，不支持删除
+
+## 冷数据占用对象大小
+方式一：
+通过show proc '/backends'可以查看到每个be上传到对象的大小，RemoteUsedCapacity项
+
+方式二：
+通过show tablets from tableName可以查看到表的每个tablet占用的对象大小，RemoteDataSize项
+
+
+## 未尽事项
+
+- 数据被cooldown后，又有新数据update或导入等，compaction目前没有处理
+- 数据被cooldown后，schema change操作，目前不支持
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
index fb4555ddee..56e9333962 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
@@ -50,6 +50,22 @@ ALTER RESOURCE 'spark0' PROPERTIES ("working_dir" = "hdfs://127.0.0.1:10000/tmp/
```sql
ALTER RESOURCE 'remote_s3' PROPERTIES ("s3_max_connections" = "100");
```
+3. 修改冷热分离S3资源相关信息
+- 支持修改项
+ - `s3_max_connections` s3最大连接数，默认50
+ - `s3_connection_timeout_ms` s3连接超时时间，默认1000ms
+ - `s3_secret_key` s3的sk信息
+ - `s3_access_key` s3的ak信息
+ - `s3_request_timeout_ms` s3请求超时时间，默认3000ms
+- 禁止修改项
+ - `s3_region`
+ - `s3_bucket`
+ - `s3_root_path`
+ - `s3_endpoint`
+
+```sql
+ ALTER RESOURCE "showPolicy_1_resource" PROPERTIES("s3_max_connections" = "1111");
+```
### Keywords
```sql
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY.md
similarity index 63%
copy from docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
copy to docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY.md
index fb4555ddee..eab0fbb3c1 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-RESOURCE.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-STORAGE-POLICY.md
@@ -1,6 +1,6 @@
---
{
-"title": "ALTER-RESOURCE",
+"title": "ALTER-POLICY",
"language": "zh-CN"
}
---
@@ -24,36 +24,35 @@ specific language governing permissions and limitations
under the License.
-->
-## ALTER-RESOURCE
+## ALTER-POLICY
### Name
-ALTER RESOURCE
+ALTER STORAGE POLICY
### Description
-该语句用于修改一个已有的资源。仅 root 或 admin 用户可以修改资源。
+该语句用于修改一个已有的冷热分离迁移策略。仅 root 或 admin 用户可以修改资源。
语法：
```sql
-ALTER RESOURCE 'resource_name'
+ALTER STORAGE POLICY 'policy_name'
PROPERTIES ("key"="value", ...);
```
-注意：resource type 不支持修改。
### Example
-1. 修改名为 spark0 的 Spark 资源的工作目录：
+1. 修改名为 cooldown_datetime冷热分离数据迁移时间点：
```sql
-ALTER RESOURCE 'spark0' PROPERTIES ("working_dir" = "hdfs://127.0.0.1:10000/tmp/doris_new");
+ALTER STORAGE POLICY has_test_policy_to_alter PROPERTIES("cooldown_datetime" = "2023-06-08 00:00:00");
```
-2. 修改名为 remote_s3 的 S3 资源的最大连接数：
+2. 修改名为 cooldown_ttl的冷热分离数据迁移倒计时
```sql
-ALTER RESOURCE 'remote_s3' PROPERTIES ("s3_max_connections" = "100");
+ALTER STORAGE POLICY has_test_policy_to_alter PROPERTIES ("cooldown_ttl" = "10000");
```
### Keywords
```sql
-ALTER, RESOURCE
+ALTER, STORAGE, POLICY
```
### Best Practice
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md
index ba430e3dcf..958c5c97a7 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-PROPERTY.md
@@ -261,6 +261,17 @@ ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2
ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
```
+12. 给表添加冷热分离数据迁移策略
+```sql
+ ALTER TABLE create_table_not_have_policy set ("storage_policy" = "created_create_table_alter_policy");
+```
+注：表没有关联过storage policy，才能被添加成功，一个表只能添加一个storage policy
+
+13. 给表的partition添加冷热分离数据迁移策略
+```sql
+ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="created_create_table_partition_alter_policy");
+```
+注：表的partition没有关联过storage policy，才能被添加成功，一个表只能添加一个storage policy
### Keywords
```text
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md
index b3b9c7f041..c2904081ff 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md
@@ -92,7 +92,11 @@ PROPERTIES ("key"="value", ...);
select * from (select * from table1 where c1 = 'a' and c2 = 'b' or c3 = 'c' or c4 = 'd')
```
2. 创建数据迁移策略
- 1. 指定数据冷却时间创建数据迁移策略
+ 1. 说明
+ - 冷热分离创建策略，必须先创建resource，然后创建迁移策略时候关联创建的resource名
+ - 当前不支持删除drop数据迁移策略，防止数据被迁移后。策略被删除了，系统无法找回数据
+
+ 2. 指定数据冷却时间创建数据迁移策略
```sql
CREATE STORAGE POLICY testPolicy
PROPERTIES(
@@ -100,7 +104,7 @@ PROPERTIES ("key"="value", ...);
"cooldown_datetime" = "2022-06-08 00:00:00"
);
```
- 2. 指定热数据持续时间创建数据迁移策略
+ 3. 指定热数据持续时间创建数据迁移策略
```sql
CREATE STORAGE POLICY testPolicy
PROPERTIES(
@@ -108,6 +112,10 @@ PROPERTIES ("key"="value", ...);
"cooldown_ttl" = "1d"
);
```
+ 相关参数如下：
+ - `storage_resource`：创建的storage resource名称
+ - `cooldown_datetime`：迁移数据的时间点
+ - `cooldown_ttl`：迁移数据距离当前时间的倒计时，单位s。与cooldown_datetime二选一即可
### Keywords
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md
index 28f3146a52..1498c5f878 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md
@@ -118,11 +118,12 @@ PROPERTIES ("key"="value", ...);
PROPERTIES
(
"type" = "s3",
- "s3_endpoint" = "http://bj.s3.com",
+ "s3_endpoint" = "bj.s3.com",
"s3_region" = "bj",
"s3_root_path" = "/path/to/root",
"s3_access_key" = "bbb",
"s3_secret_key" = "aaaa",
+ "s3_bucket" = "test-bucket",
"s3_max_connections" = "50",
"s3_request_timeout_ms" = "3000",
"s3_connection_timeout_ms" = "1000"
@@ -131,15 +132,16 @@ PROPERTIES ("key"="value", ...);
S3 相关参数如下：
- 必需参数
- - s3_endpoint：s3 endpoint
- - s3_region：s3 region
- - s3_root_path：s3 根目录
- - s3_access_key：s3 access key
- - s3_secret_key：s3 secret key
+ - `s3_endpoint`：s3 endpoint
+ - `s3_region`：s3 region
+ - `s3_root_path`：s3 根目录
+ - `s3_access_key`：s3 access key
+ - `s3_secret_key`：s3 secret key
+ - `s3_bucket`：s3 的桶名
- 可选参数
- - s3_max_connections：s3 最大连接数量，默认为 50
- - s3_request_timeout_ms：s3 请求超时时间，单位毫秒，默认为 3000
- - s3_connection_timeout_ms：s3 连接超时时间，单位毫秒，默认为 1000
+ - `s3_max_connections`：s3 最大连接数量，默认为 50
+ - `s3_request_timeout_ms`：s3 请求超时时间，单位毫秒，默认为 3000
+ - `s3_connection_timeout_ms`：s3 连接超时时间，单位毫秒，默认为 1000
### Keywords
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md
index 24f9ad4f9b..fea9503fc3 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md
@@ -573,6 +573,37 @@ distribution_desc
);
```
+11. 通过`storage_policy`属性设置表的冷热分离数据迁移策略
+```
+ CREATE TABLE IF NOT EXISTS create_table_use_created_policy
+ (
+ k1 BIGINT,
+ k2 LARGEINT,
+ v1 VARCHAR(2048)
+ )
+ UNIQUE KEY(k1)
+ DISTRIBUTED BY HASH (k1) BUCKETS 3
+ PROPERTIES(
+ "storage_policy" = "test_create_table_use_policy",
+ "replication_num" = "1"
+ );
+```
+注：需要先创建s3 resource 和 storage policy，表才能关联迁移策略成功
+
+12. 为表的分区添加冷热分离数据迁移策略
+```
+ CREATE TABLE create_table_partion_use_created_policy
+ (
+ k1 DATE,
+ k2 INT,
+ V1 VARCHAR(2048) REPLACE
+ ) PARTITION BY RANGE (k1) (
+ PARTITION p1 VALUES LESS THAN ("2022-01-01") ("storage_policy" = "test_create_table_partition_use_policy_1" ,"replication_num"="1"),
+ PARTITION p2 VALUES LESS THAN ("2022-02-01") ("storage_policy" = "test_create_table_partition_use_policy_2" ,"replication_num"="1")
+ ) DISTRIBUTED BY HASH(k2) BUCKETS 1;
+```
+注：需要先创建s3 resource 和 storage policy，表才能关联迁移策略成功
+
### Keywords
CREATE, TABLE
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md
index 4a15088e53..d76a70eff2 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-POLICY.md
@@ -43,11 +43,6 @@ DROP POLICY
DROP ROW POLICY test_row_policy_1 on table1 [FOR user];
```
-2. 删除冷热数据存储策略
-```sql
-DROP STORAGE POLICY policy_name1
-```
-
### Example
1. 删除 table1 的 test_row_policy_1
@@ -61,11 +56,6 @@ DROP STORAGE POLICY policy_name1
```sql
DROP ROW POLICY test_row_policy_1 on table1 for test
```
-
-3. 删除 policy_name1 对应的冷热数据存储策略
- ```sql
- DROP STORAGE POLICY policy_name1
- ```
### Keywords
DROP, POLICY
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md b/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
index 2d7e2604d7..efcec3bbbd 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-POLICY.md
@@ -1,6 +1,6 @@
---
{
- "title": "SHOW-ROW-POLICY",
+ "title": "SHOW-POLICY",
"language": "zh-CN"
}
---
@@ -69,7 +69,29 @@ SHOW ROW POLICY [FOR user]
+-------------------+----------------------+-----------+------+------------+-------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
```
-
+
+3. 展示数据迁移策略
+ ```sql
+ mysql> SHOW STORAGE POLICY;
+ +---------------------+---------+-----------------------+---------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ | PolicyName | Type | StorageResource | CooldownDatetime | CooldownTtl | properties |
+ +---------------------+---------+-----------------------+---------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ | showPolicy_1_policy | STORAGE | showPolicy_1_resource | 2022-06-08 00:00:00 | -1 | {
+ "s3_secret_key": "******",
+ "s3_region": "bj",
+ "s3_access_key": "bbba",
+ "s3_max_connections": "50",
+ "s3_connection_timeout_ms": "1000",
+ "type": "s3",
+ "s3_root_path": "path/to/rootaaaa",
+ "s3_bucket": "test-bucket",
+ "s3_endpoint": "bj.s3.comaaaa",
+ "s3_request_timeout_ms": "3000"
+ } |
+ +---------------------+---------+-----------------------+---------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+ ```
+
### Keywords
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org