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 2023/01/20 15:05:50 UTC
[doris] branch master updated: [docs](en) update en docs (#16124)
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 d318d644ff [docs](en) update en docs (#16124)
d318d644ff is described below
commit d318d644ffd9262fb22e704b1d58404500222360
Author: Hu Yanjun <10...@users.noreply.github.com>
AuthorDate: Fri Jan 20 23:05:39 2023 +0800
[docs](en) update en docs (#16124)
---
docs/en/docs/data-table/basic-usage.md | 260 ++++++-----
docs/en/docs/data-table/data-model.md | 519 ++++++++++-----------
docs/en/docs/data-table/data-partition.md | 487 +++++++++----------
docs/en/docs/data-table/hit-the-rollup.md | 12 +-
.../compilation-with-ldb-toolchain.md | 2 +-
.../docs/lakehouse/multi-catalog/multi-catalog.md | 332 ++++++++++++-
6 files changed, 983 insertions(+), 629 deletions(-)
diff --git a/docs/en/docs/data-table/basic-usage.md b/docs/en/docs/data-table/basic-usage.md
index 8da31f9652..4f7bba8bf8 100644
--- a/docs/en/docs/data-table/basic-usage.md
+++ b/docs/en/docs/data-table/basic-usage.md
@@ -25,28 +25,25 @@ under the License.
-->
-# Use Guide
+# User Guide
-Doris uses MySQL protocol to communicate. Users can connect to Doris cluster through MySQL client or MySQL JDBC. When selecting the MySQL client version, it is recommended to use the version after 5.1, because user names of more than 16 characters cannot be supported before 5.1. This paper takes MySQL client as an example to show users the basic usage of Doris through a complete process.
+Doris uses MySQL protocol for communication. Users can connect to Doris clusters through MySQL Client or MySQL JDBC. MySQL Client 5.1 or newer versions are recommended because they support user names of more than 16 characters. This topic walks you through how to use Doris with the example of MySQL Client.
## Create Users
-### Root User Logon and Password Modification
+### Root User Login and Change Password
-Doris has built-in root and admin users, and the password is empty by default.
+Doris has its built-in root, and the default password is empty.
->Remarks:
+>Note:
>
->The default root and admin users provided by Doris are admin users
+>Doris provides a default root.
>
->The >root user has all the privileges of the cluster by default. Users who have both Grant_priv and Node_priv can grant this permission to other users and have node change permissions, including operations such as adding, deleting, and going offline of FE, BE, and BROKER nodes.
+>The root user has all the privileges about the clusters by default. Users who have both Grant_priv and Node_priv can grant these privileges to other users. Node changing privileges include adding, deleting, and offlining FE, BE, and BROKER nodes.
>
->admin user has ADMIN_PRIV and GRANT_PRIV privileges
->
->For specific instructions on permissions, please refer to [Permission Management](../../admin-manual/privilege-ldap/user-privilege)
+>For more instructions on privileges, please refer to [Privilege Management](../../admin-manual/privilege-ldap/user-privilege)
-After starting the Doris program, you can connect to the Doris cluster through root or admin users.
-Use the following command to log in to Doris:
+After starting the Doris program, root or admin users can connect to Doris clusters. You can use the following command to log in to Doris. After login, you will enter the corresponding MySQL command line interface.
```sql
[root@doris ~]# mysql -h FE_HOST -P9030 -uroot
@@ -65,49 +62,74 @@ Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
```
->` FE_HOST` is the IP address of any FE node. ` 9030 ` is the query_port configuration in fe.conf.
+>1. ` FE_HOST` is the IP address of any FE node. ` 9030 ` is the query_port configuration in fe.conf.
-After login, you can modify the root password by following commands
+After login, you can change the root password by the following command:
```sql
mysql> SET PASSWORD FOR 'root' = PASSWORD('your_password');
Query OK, 0 rows affected (0.00 sec)
```
-> `your_password` is a new password for the `root` user, which can be set at will. It is recommended to set a strong password to increase security, and use the new password to log in the next time you log in.
+> `your_password` is a new password for the `root` user, which can be set at will. A strong password is recommended for security. The new password is required in the next login.
-### Creating New Users
+### Create New Users
-We can create a regular user `test` with the following command:
+You can create a regular user named `test` with the following command:
```sql
mysql> CREATE USER 'test' IDENTIFIED BY 'test_passwd';
Query OK, 0 rows affected (0.00 sec)
```
-Follow-up login can be done through the following connection commands.
+Follow-up logins can be performed with the following connection commands.
```sql
[root@doris ~]# mysql -h FE_HOST -P9030 -utest -ptest_passwd
```
-> By default, the newly created common user does not have any permissions. Permission grants can be referred to later permission grants.
+> By default, the newly created regular users do not have any privileges. Privileges can be granted to these users.
-## Data Table Creation and Data Import
+## Create Data Table and Import Data
### Create a database
-Initially, a database can be created through root or admin users:
+Initially, root or admin users can create a database by the following command:
```sql
CREATE DATABASE example_db;
```
-> All commands can use `HELP` command to see detailed grammar help. For example: `HELP CREATE DATABASE;'`.You can also refer to the official website [SHOW CREATE DATABASE](../sql-manual/sql-reference/Show-Statements/SHOW-CREATE-DATABASE.md) command manual.
+> You can use the `HELP` command to check the syntax of all commands. For example, `HELP CREATE DATABASE;`. Or you can refer to the [SHOW CREATE DATABASE](../sql-manual/sql-reference/Show-Statements/SHOW-CREATE-DATABASE.md) command manual.
>
-> If you don't know the full name of the command, you can use "help command a field" for fuzzy query. If you type `HELP CREATE`, you can match commands like `CREATE DATABASE', `CREATE TABLE', `CREATE USER', etc.
-
-After the database is created, you can view the database information through [SHOW DATABASES](../../sql-manual/sql-reference/Show-Statements/SHOW-DATABASES).
+> If you don't know the full name of the command, you can use "HELP + a field of the command" for fuzzy query. For example, if you type in `HELP CREATE`, you can find commands including `CREATE DATABASE`, `CREATE TABLE`, and `CREATE USER`.
+>
+> ```
+> mysql> HELP CREATE;
+> Many help items for your request exist.
+> To make a more specific request, please type 'help <item>',
+> where <item> is one of the following
+> topics:
+> CREATE CLUSTER
+> CREATE DATABASE
+> CREATE ENCRYPTKEY
+> CREATE FILE
+> CREATE FUNCTION
+> CREATE INDEX
+> CREATE MATERIALIZED VIEW
+> CREATE REPOSITORY
+> CREATE RESOURCE
+> CREATE ROLE
+> CREATE SYNC JOB
+> CREATE TABLE
+> CREATE USER
+> CREATE VIEW
+> ROUTINE LOAD
+> SHOW CREATE FUNCTION
+> SHOW CREATE ROUTINE LOAD
+> ```
+
+After the database is created, you can view the information about the database via the [SHOW DATABASES](../../sql-manual/sql-reference/Show-Statements/SHOW-DATABASES) command.
```sql
MySQL> SHOW DATABASES;
@@ -120,42 +142,42 @@ MySQL> SHOW DATABASES;
2 rows in set (0.00 sec)
```
-> Information_schema exists to be compatible with MySQL protocol. In practice, information may not be very accurate. Therefore, information about specific databases is suggested to be obtained by directly querying the corresponding databases.
+> `information_schema` exists for compatibility with MySQL protocol, so the information might not be 100% accurate in practice. Therefore, for information about the specific databases, please query the corresponding databases directly.
-### Account Authorization
+### Authorize an Account
-After the example_db is created, the read and write permissions of example_db can be authorized to ordinary accounts, such as test, through the root/admin account. After authorization, the example_db database can be operated by logging in with the test account.
+After `example_db` is created, root/admin users can grant read/write privileges of `example_db` to regular users, such as `test`, using the `GRANT` command. After authorization, user `test` can perform operations on `example_db`.
```sql
mysql> GRANT ALL ON example_db TO test;
Query OK, 0 rows affected (0.01 sec)
```
-### Formulation
+### Create a Table
-Create a table using the [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) command. More detailed parameters can be seen:`HELP CREATE TABLE;`
+You can create a table using the [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) command. For detailed parameters, you can send a `HELP CREATE TABLE;` command.
-First, we need to switch databases using the [USE](../sql-manual/sql-reference/Utility-Statements/USE.md) command:
+Firstly, you need to switch to the target database using the [USE](../sql-manual/sql-reference/Utility-Statements/USE.md) command:
```sql
mysql> USE example_db;
Database changed
```
-Doris supports [composite partition and single partition](./data-partition) two table building methods. The following takes the aggregation model as an example to demonstrate how to create two partitioned data tables.
+Doris supports two table creation methods: [compound partitioning and single partitioning](./data-partition). The following takes the Aggregate Model as an example to demonstrate how to create tables with these two methods, respectively.
-#### Single partition
+#### Single Partitioning
-Create a logical table with the name table1. The number of barrels is 10.
+Create a logical table named `table1`. The bucketing column is the `siteid` column, and the number of buckets is 10.
-The schema of this table is as follows:
+The table schema is as follows:
-* Siteid: Type is INT (4 bytes), default value is 10
-* citycode: The type is SMALLINT (2 bytes)
-* username: The type is VARCHAR, the maximum length is 32, and the default value is an empty string.
-* pv: Type is BIGINT (8 bytes), default value is 0; this is an index column, Doris will aggregate the index column internally, the aggregation method of this column is SUM.
+* `siteid`: INT (4 bytes); default value: 10
+* `citycode`: SMALLINT (2 bytes)
+* `username`: VARCHAR, with a maximum length of 32; default value: empty string
+* `pv`: BIGINT (8 bytes); default value: 0; This is a metric column, and Doris will aggregate the metric columns internally. The `pv` column is aggregated by SUM.
-The TABLE statement is as follows:
+The corresponding CREATE TABLE statement is as follows:
```sql
CREATE TABLE table1
(
@@ -169,29 +191,29 @@ DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");
```
-#### Composite partition
+#### Compound Partitioning
-Create a logical table named table2.
+Create a logical table named `table2`.
-The schema of this table is as follows:
+The table schema is as follows:
-* event_day: Type DATE, no default
-* Siteid: Type is INT (4 bytes), default value is 10
-* citycode: The type is SMALLINT (2 bytes)
-* username: The type is VARCHAR, the maximum length is 32, and the default value is an empty string.
-* pv: Type is BIGINT (8 bytes), default value is 0; this is an index column, Doris will aggregate the index column internally, the aggregation method of this column is SUM.
+* `event_day`: DATE; no default value
+* `siteid`: INT (4 bytes); default value: 10
+* `citycode`: SMALLINT (2 bytes)
+* `username`: VARCHAR, with a maximum length of 32; default value: empty string
+* `pv`: BIGINT (8 bytes); default value: 0; This is a metric column, and Doris will aggregate the metric columns internally. The `pv` column is aggregated by SUM.
-We use the event_day column as the partition column to create three partitions: p201706, p201707, and p201708.
+Use the `event_day` column as the partitioning column and create 3 partitions: p201706, p201707, and p201708.
* p201706: Range [Minimum, 2017-07-01)
-* p201707: Scope [2017-07-01, 2017-08-01)
-* p201708: Scope [2017-08-01, 2017-09-01)
+* p201707: Range [2017-07-01, 2017-08-01)
+* p201708: Range [2017-08-01, 2017-09-01)
-> Note that the interval is left closed and right open.
+> Note that the intervals are left-closed and right-open.
-Each partition uses siteid to hash buckets, with a bucket count of 10
+HASH bucket each partition based on `siteid`. The number of buckets per partition is 10.
-The TABLE statement is as follows:
+The corresponding CREATE TABLE statement is as follows:
```sql
CREATE TABLE table2
(
@@ -212,7 +234,7 @@ DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");
```
-After the table is built, you can view the information of the table in example_db:
+After the table is created, you can view the information of the table in `example_db`:
```sql
MySQL> SHOW TABLES;
@@ -248,33 +270,33 @@ MySQL> DESC table2;
5 rows in set (0.00 sec)
```
-> Notes:
+> Note:
>
-> 1. By setting replication_num, the above tables are all single-copy tables. Doris recommends that users adopt the default three-copy settings to ensure high availability.
-> 2. Composite partition tables can be added or deleted dynamically. See the Partition section in `HELP ALTER TABLE`.
-> 3. Data import can import the specified Partition. See `HELP LOAD;`.
-> 4. Schema of table can be dynamically modified, See `HELP ALTER TABLE;`.
-> 5. Rollup can be added to Table to improve query performance. This section can be referred to the description of Rollup in Advanced Usage Guide.
-> 6. The default value of Null property for column is true, which may result in poor scan performance.
+> 1. As `replication_num` is set to `1`, the above tables are created with only one copy. We recommend that you adopt the default three-copy settings to ensure high availability.
+> 2. You can dynamically add or delete partitions of compoundly partitioned tables. See `HELP ALTER TABLE`.
+> 3. You can import data into the specified Partition. See `HELP LOAD;`.
+> 4. You can dynamically change the table schema. See `HELP ALTER TABLE;`.
+> 5. You can add Rollups to Tables to improve query performance. See the Rollup-related section in "Advanced Usage".
+> 6. The default value in the Null column is true, which may affect query performance.
-### Import data
+### Load data
-Doris supports a variety of data import methods. Specifically, you can refer to the [data import](../data-operate/import/load-manual.md) document. Here we use streaming import and Broker import as examples.
+Doris supports a variety of data loading methods. You can refer to [Data Loading](../data-operate/import/load-manual.md) for more details. The following uses Stream Load and Broker Load as examples.
-#### Flow-in
+#### Stream Load
-Streaming import transfers data to Doris via HTTP protocol. It can import local data directly without relying on other systems or components. Detailed grammar help can be found in `HELP STREAM LOAD;'
+The Stream Load method transfers data to Doris via HTTP protocol. It can import local data directly without relying on any other systems or components. For the detailed syntax, please see `HELP STREAM LOAD;`.
-Example 1: With "table1_20170707" as Label, import table1 tables using the local file table1_data.
+Example 1: Use "table1_20170707" as the Label, import the local file `table1_data` into `table1`.
```bash
curl --location-trusted -u test:test_passwd -H "label:table1_20170707" -H "column_separator:," -T table1_data http://FE_HOST:8030/api/example_db/table1/_stream_load
```
-> 1. FE_HOST is the IP of any FE node and 8030 is http_port in fe.conf.
-> 2. You can use the IP of any BE and the webserver_port in be.conf to connect the target left and right for import. For example: `BE_HOST:8040`
+> 1. FE_HOST is the IP address of any FE node and 8030 is the http_port in fe.conf.
+> 2. You can use the IP address of any BE and the webserver_port in be.conf for import. For example: `BE_HOST:8040`.
-The local file `table1_data` takes `,` as the separation between data, and the specific contents are as follows:
+The local file `table1_data` uses `,` as the separator between data. The details are as follows:
```text
1,1,Jim,2
@@ -284,13 +306,13 @@ The local file `table1_data` takes `,` as the separation between data, and the s
5,3,helen,3
```
-Example 2: With "table2_20170707" as Label, import table2 tables using the local file table2_data.
+Example 2: Use "table2_20170707" as the Label, import the local file `table2_data` into `table2`.
```bash
curl --location-trusted -u test:test -H "label:table2_20170707" -H "column_separator:|" -T table2_data http://127.0.0.1:8030/api/example_db/table2/_stream_load
```
-The local file `table2_data'is separated by `|'. The details are as follows:
+The local file `table2_data` uses `|` as the separator between data. The details are as follows:
```
2017-07-03|1|1|jim|2
@@ -300,17 +322,17 @@ The local file `table2_data'is separated by `|'. The details are as follows:
2017-07-12|5|3|helen|3
```
-> Notes:
+> Note:
>
-> 1. The recommended file size for streaming import is limited to 10GB. Excessive file size will result in higher cost of retry failure.
-> 2. Each batch of imported data needs to take a Label. Label is best a string related to a batch of data for easy reading and management. Doris based on Label guarantees that the same batch of data can be imported only once in a database. Label for failed tasks can be reused.
-> 3. Streaming imports are synchronous commands. The successful return of the command indicates that the data has been imported, and the failure of the return indicates that the batch of data has not been imported.
+> 1. The recommended file size for Stream Load is less than 10GB. Excessive file size will result in higher retry cost.
+> 2. Each batch of import data should have a Label. Label serves as the unique identifier of the load task, and guarantees that the same batch of data will only be successfully loaded into a database once. For more details, please see [Data Loading and Atomicity](https://doris.apache.org/docs/dev/data-operate/import/import-scenes/load-atomicity/).
+> 3. Stream Load is a synchronous command. The return of the command indicates that the data has been loaded successfully; otherwise the data has not been loaded.
#### Broker Load
-Broker imports import data from external storage through deployed Broker processes. For more help, see `HELP BROKER LOAD;`
+The Broker Load method imports externally stored data via deployed Broker processes. For more details, please see `HELP BROKER LOAD;`
-Example: Import files on HDFS into table1 table with "table1_20170708" as Label
+Example: Use "table1_20170708" as the Label, import files on HDFS into `table1` .
```sql
LOAD LABEL table1_20170708
@@ -330,23 +352,23 @@ PROPERTIES
);
```
-Broker imports are asynchronous commands. Successful execution of the above commands only indicates successful submission of tasks. Successful imports need to be checked through `SHOW LOAD;' Such as:
+The Broker Load is an asynchronous command. Successful execution of it only indicates successful submission of the task. You can check if the import task has been completed by `SHOW LOAD;` . For example:
```sql
SHOW LOAD WHERE LABEL = "table1_20170708";
```
-In the return result, `FINISHED` in the `State` field indicates that the import was successful.
+In the return result, if you find `FINISHED` in the `State` field, that means the import is successful.
-For more instructions on `SHOW LOAD`, see` HELP SHOW LOAD; `
+For more instructions on `SHOW LOAD`, see` HELP SHOW LOAD; `.
-Asynchronous import tasks can be cancelled before the end:
+Asynchronous import tasks can be cancelled before it is completed:
```sql
CANCEL LOAD WHERE LABEL = "table1_20170708";
```
-## Data query
+## Query the Data
### Simple Query
@@ -378,7 +400,7 @@ MySQL> SELECT * FROM table1 ORDER BY citycode;
### SELECT * EXCEPT
-A `SELECT * EXCEPT` statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.
+The `SELECT * EXCEPT` statement is used to exclude one or more columns from the result. The output will not include any of the specified columns.
```sql
MySQL> SELECT * except (username, citycode) FROM table1;
@@ -392,7 +414,7 @@ MySQL> SELECT * except (username, citycode) FROM table1;
3 rows in set (0.01 sec)
```
-**Note**: `SELECT * EXCEPT` does not exclude columns that do not have names.
+**Note**: `SELECT * EXCEPT` does not exclude columns that do not have a name.
### Join Query
@@ -422,7 +444,7 @@ MySQL> SELECT SUM(pv) FROM table2 WHERE siteid IN (SELECT siteid FROM table1 WHE
1 row in set (0.13 sec)
```
-## Table Structure Change
+## Change Table Schema
Use the [ALTER TABLE COLUMN](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN) command to modify the table Schema, including the following changes.
@@ -486,13 +508,13 @@ For more help, see ``HELP ALTER TABLE``.
## Rollup
-Rollup can be understood as a materialized index structure for a Table. **Materialized** because its data is physically stored independently, and **Indexed** in the sense that Rollup can reorder columns to increase the hit rate of prefix indexes, and can reduce key columns to increase the aggregation of data.
+Rollup can be seen as a materialized index structure for a Table, **materialized** in the sense that its data is physically independent in storage, and **indexed** in the sense that Rollup can reorder columns to increase the hit rate of prefix indexes as well as reduce Key columns to increase the aggregation level of data.
-Use [ALTER TABLE ROLLUP](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-ROLLUP) to perform various changes to Rollup.
+You can perform various changes to Rollup using [ALTER TABLE ROLLUP](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-ROLLUP).
-The following examples are given
+The following is an exemplified illustration.
-The Schema of the original table1 is as follows:
+The original schema of `table1` is as follows:
```
+----------+-------------+------+-------+---------+-------+
@@ -506,21 +528,21 @@ The Schema of the original table1 is as follows:
+----------+-------------+------+-------+---------+-------+
```
-For table1 detailed data is siteid, citycode, username three constitute a set of key, so that the pv field aggregation; if the business side often have to see the total number of city pv needs, you can create a rollup of only citycode, pv.
+For `table1`, `siteid`, `citycode`, and `username` constitute a set of Key, based on which the pv fields are aggregated; if you have a frequent need to view the total city pv, you can create a Rollup consisting of only `citycode` and `pv`:
```sql
ALTER TABLE table1 ADD ROLLUP rollup_city(citycode, pv);
```
-After successful submission, you can check the progress of the job with the following command.
+After successful submission, you can check the progress of the task with the following command:
```sql
SHOW ALTER TABLE ROLLUP;
```
-When the job status is ``FINISHED``, the job is completed.
+If the task status is ``FINISHED``, the job is completed.
-You can use ``DESC table1 ALL`` to view the rollup information of the table after the rollup is created.
+After the Rollup is created, you can use ``DESC table1 ALL`` to check the information of the Rollup.
```
mysql> desc table1 all;
@@ -539,13 +561,13 @@ mysql> desc table1 all;
8 rows in set (0.01 sec)
```
-You can cancel the currently executing job with the following command:
+You can cancel the currently ongoing task using the following command:
```sql
CANCEL ALTER TABLE ROLLUP FROM table1;
```
-After Rollup is created, the query does not need to specify Rollup for the query. You can still specify the original table for the query. The program will automatically determine if Rollup should be used, and whether Rollup is hit or not can be checked with the ``EXPLAIN your_sql;`` command.
+With created Rollups, you do not need to specify the Rollup in queries, but only specify the original table for the query. The program will automatically determine if Rollup should be used. You can check whether Rollup is hit or not using the ``EXPLAIN your_sql;`` command.
For more help, see `HELP ALTER TABLE`.
@@ -553,25 +575,23 @@ For more help, see `HELP ALTER TABLE`.
## Materialized Views
-Materialized views are a space-for-time data analysis acceleration technique, and Doris supports building materialized views on top of base tables. For example, a partial column-based aggregated view can be built on top of a table with a granular data model, allowing for fast querying of both granular and aggregated data.
+Materialized views are a space-for-time data analysis acceleration technique. Doris supports building materialized views on top of base tables. For example, a partial column-based aggregated view can be built on top of a table with a granular data model, allowing for fast querying of both granular and aggregated data.
-At the same time, Doris can automatically ensure data consistency between materialized views and base tables, and automatically match the appropriate materialized view at query time, greatly reducing the cost of data maintenance for users and providing a consistent and transparent query acceleration experience for users.
+Doris can automatically ensure data consistency between materialized views and base tables, and automatically match the appropriate materialized view at query time, greatly reducing the cost of data maintenance for users and providing a consistent and transparent query acceleration experience.
For more information about materialized views, see [Materialized Views](../../advanced/materialized-view)
-## Data table queries
-
-### Memory limit
+## Data Table Queries
-To prevent a user from having a single query that may consume too much memory because it consumes too much memory. The query is memory-controlled, and by default a query task can use no more than 2GB of memory on a single BE node.
+### Memory Limit
-If a user finds a `Memory limit exceeded` error while using it, the memory limit is usually exceeded.
+To prevent excessive memory usage of one single query, Doris imposes memory limit on queries. By default, one query task should consume no more than 2GB of memory on one single BE node.
-When you encounter a memory limit exceeded, you should try to solve it by optimizing your sql statements.
+If you find a `Memory limit exceeded` error, that means the program is trying to allocate more memory than the memory limit. You can solve this by optimizing your SQL statements.
-If you find exactly that 2GB of memory cannot be met, you can set the memory parameters manually.
+You can change the 2GB memory limit by modifying the memory parameter settings.
-Show query memory limit:
+Show the memory limit for one query:
```sql
mysql> SHOW VARIABLES LIKE "%mem_limit%";
@@ -583,7 +603,7 @@ mysql> SHOW VARIABLES LIKE "%mem_limit%";
1 row in set (0.00 sec)
```
-The unit of `exec_mem_limit` is byte, and the value of `exec_mem_limit` can be changed by `SET` command. For example, change it to 8GB.
+ `exec_mem_limit` is measured in byte. You can change the value of `exec_mem_limit` using the `SET` command. For example, you can change it to 8GB as follows:
```sql
mysql> SET exec_mem_limit = 8589934592;
@@ -597,12 +617,12 @@ mysql> SHOW VARIABLES LIKE "%mem_limit%";
1 row in set (0.00 sec)
```
->- The above change is session level and is only valid for the current connection session. Disconnecting and reconnecting will change it back to the default value.
->- If you need to change the global variable, you can set it like this: `SET GLOBAL exec_mem_limit = 8589934592;`. After setting, disconnect and log back in, the parameter will take effect permanently.
+>- The above change is executed at the session level and is only valid for the currently connected session. The default memory limit will restore after reconnection.
+>- If you need to change the global variable, you can set: `SET GLOBAL exec_mem_limit = 8589934592;`. After this, you disconnect and log back in, and then the new parameter will take effect permanently.
### Query Timeout
-The current default query timeout is set to a maximum of 300 seconds. If a query does not complete within 300 seconds, the query will be cancelled by the Doris system. You can use this parameter to customize the timeout for your application to achieve a blocking method similar to wait(timeout).
+The default query timeout is set to 300 seconds. If a query is not completed within 300 seconds, it will be cancelled by the Doris system. You change this parameter and customize the timeout for your application to achieve a blocking method similar to wait(timeout).
View the current timeout settings:
@@ -616,23 +636,23 @@ mysql> SHOW VARIABLES LIKE "%query_timeout%";
1 row in set (0.00 sec)
```
-Modify the timeout to 1 minute:
+Change query timeout to 1 minute:
```sql
mysql> SET query_timeout = 60;
Query OK, 0 rows affected (0.00 sec)
```
->- The current timeout check interval is 5 seconds, so a timeout of less than 5 seconds is not too accurate.
->- The above changes are also session level. You can change the global validity by `SET GLOBAL`.
+>- The current timeout check interval is 5 seconds, so if you set the query timeout to less than 5 seconds, it might not be executed accurately.
+>- The above changes are also performed at the session level. You can change the global variable by `SET GLOBAL`.
### Broadcast/Shuffle Join
-The default way to implement Join is to filter the small table conditionally, broadcast it to each node of the large table to form a memory Hash table, and then stream the data of the large table for Hash Join, but if the filtered data of the small table cannot be put into memory, the Join will not be completed, and the usual error should be the first to cause memory overrun.
+The default way to implement Join is to filter the sub table conditionally, broadcast it to each node of the overall table to form a memory Hash table, and then stream read the data of the overall table for Hash Join, but if the filtered data of the sub table cannot be put into memory, the Join will not be completed, and then usually a memory overrun error will occur.
-In this case, it is recommended to explicitly specify Shuffle Join, also known as Partitioned Join, where both the small and large tables are Hashed according to the key of the join and then perform a distributed join, with the memory consumption being spread across all compute nodes in the cluster.
+In this case, it is recommended to explicitly specify Shuffle Join, also known as Partitioned Join, where both the sub table and overall table are Hashed according to the Key of the Join and then perform a distributed Join, with the memory consumption being spread across all compute nodes in the cluster.
-Doris will automatically attempt a Broadcast Join and switch to a Shuffle Join if the small table is estimated to be too large; note that if a Broadcast Join is explicitly specified at this point, it will enforce Broadcast Join.
+Doris will automatically attempt a Broadcast Join and switch to a Shuffle Join if the sub table is estimated to be too large; note that if a Broadcast Join is explicitly specified at this point, it will enforce Broadcast Join.
Use Broadcast Join (default):
@@ -676,10 +696,10 @@ When deploying multiple FE nodes, you can deploy a load balancing layer on top o
Please refer to [Load Balancing](../admin-manual/cluster-management/load-balancing) for details on installation, deployment, and usage.
-## Data update and deletion
+## Update and Delete Data
-Doris supports deleting imported data in two ways. One way is to delete data by specifying a WHERE condition with a DELETE FROM statement. This method is more general and suitable for less frequent scheduled deletion tasks.
+Doris supports two methods to delete imported data. One is to use the `DELETE FROM` statement and specify the target data by the `WHERE` condition. This method is widely applicable and suitable for less frequent scheduled deletion tasks.
-The other deletion method is for the Unique primary key unique model only, where the primary key rows to be deleted are imported by importing the data, and the final physical deletion of the data is performed internally by Doris using the delete tag bit. This deletion method is suitable for deleting data in a real-time manner.
+The other method is only used in the Unique Models with a unique primary key. It imports the the primary key rows that are to be deleted, and the final physical deletion of the data is performed internally by Doris using the deletion mark. This method is suitable for real-time deletion of data.
-For specific instructions on delete and update operations, see [Data Update](../../data-operate/update-delete/update) documentation.
+For specific instructions on deleting and updating data, see [Data Update](../../data-operate/update-delete/update).
diff --git a/docs/en/docs/data-table/data-model.md b/docs/en/docs/data-table/data-model.md
index 052807507a..6b445c9748 100644
--- a/docs/en/docs/data-table/data-model.md
+++ b/docs/en/docs/data-table/data-model.md
@@ -26,22 +26,21 @@ under the License.
# Data Model
-This document describes Doris's data model at the logical level to help users better use Doris to cope with different business scenarios.
+This topic introduces the data models in Doris from a logical perspective so you can make better use of Doris in different business scenarios.
## Basic concepts
-In Doris, data is logically described in the form of tables.
-A table consists of rows and columns. Row is a row of user data. Column is used to describe different fields in a row of data.
+In Doris, data is logically described in the form of tables. A table consists of rows and columns. Row is a row of user data. Column is used to describe different fields in a row of data.
-Columns can be divided into two categories: Key and Value. From a business perspective, Key and Value can correspond to dimension columns and indicator columns, respectively.
+Columns can be divided into two categories: Key and Value. From a business perspective, Key and Value correspond to dimension columns and indicator columns, respectively.
-Doris's data model is divided into three main categories:
+Data models in Doris fall into three types:
* Aggregate
-* Uniq
+* Unique
* Duplicate
-Let's introduce them separately.
+The following is the detailed introduction to each of them.
## Aggregate Model
@@ -63,7 +62,7 @@ Assume that the business has the following data table schema:
| max dwell time | INT | MAX | Maximum user residence time|
| min dwell time | INT | MIN | User minimum residence time|
-If converted into a table-building statement, the following is done (omitting the Partition and Distribution information in the table-building statement)
+The corresponding CREATE TABLE statement would be as follows (omitting the Partition and Distribution information):
```
CREATE TABLE IF NOT EXISTS example_db.example_tbl
@@ -85,19 +84,18 @@ PROPERTIES (
);
```
-As you can see, this is a typical fact table of user information and access behavior.
-In general star model, user information and access behavior are stored in dimension table and fact table respectively. Here, in order to explain Doris's data model more conveniently, we store the two parts of information in a single table.
+As you can see, this is a typical fact table of user information and visit behaviors. In star models, user information and visit behaviors are usually stored in dimension tables and fact tables, respectively. Here, for the convenience of explanation, we store the two types of information in one single table.
-The columns in the table are divided into Key (dimension column) and Value (indicator column) according to whether `AggregationType` is set or not. No `AggregationType`, such as `user_id`, `date`, `age`, etc., is set as **Key**, while Aggregation Type is set as **Value**.
+The columns in the table are divided into Key (dimension) columns and Value (indicator columns) based on whether they are set with an `AggregationType`. **Key** columns are not set with an `AggregationType`, such as `user_id`, `date`, and `age`, while **Value** columns are.
-When we import data, the same rows and aggregates into one row for the Key column, while the Value column aggregates according to the set `AggregationType`. `AggregationType` currently has the following four ways of aggregation:
+When data are imported, rows with the same contents in the Key columns will be aggregated into one row, and their values in the Value columns will be aggregated as their `AggregationType` specify. Currently, their are four aggregation types:
-1. SUM: Sum, multi-line Value accumulation.
-2. REPLACE: Instead, Values in the next batch of data will replace Values in rows previously imported.
-3. MAX: Keep the maximum.
-4. MIN: Keep the minimum.
+1. SUM: Accumulate the values in multiple rows.
+2. REPLACE: The newly imported value will replace the previous value.
+3. MAX: Keep the maximum value.
+4. MIN: Keep the minimum value.
-Suppose we have the following imported data (raw data):
+Suppose that you have the following import data (raw data):
|user\_id|date|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
|---|---|---|---|---|---|---|---|---|
@@ -109,7 +107,7 @@ Suppose we have the following imported data (raw data):
| 10004 | 2017-10-01 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3|
| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6|
-Let's assume that this is a table that records the user's behavior in accessing a commodity page. Let's take the first row of data as an example and explain it as follows:
+Assume that this is a table recording the user behaviors when visiting a certain commodity page. The first row of data, for example, is explained as follows:
| Data | Description|
|---|---|
@@ -123,7 +121,7 @@ Let's assume that this is a table that records the user's behavior in accessing
| 10 | User's visit, time to stay on the page|
| 10 | User's current visit, time spent on the page (redundancy)|
-Then when this batch of data is imported into Doris correctly, the final storage in Doris is as follows:
+After this batch of data is imported into Doris correctly, it will be stored in Doris as follows:
|user\_id|date|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
|---|---|---|---|---|---|---|---|---|
@@ -134,169 +132,168 @@ Then when this batch of data is imported into Doris correctly, the final storage
| 10004 | 2017-10-01 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3|
| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6|
-As you can see, there is only one line of aggregated data left for 10,000 users. The data of other users are consistent with the original data. Here we first explain the aggregated data of user 10000:
+As you can see, the data of User 10000 have been aggregated to one row, while those of other users remain the same. The explanation for the aggregated data of User 10000 is as follows (the first 5 columns remain unchanged, so it starts with Column 6 `last_visit_date`):
-The first five columns remain unchanged, starting with column 6 `last_visit_date`:
+*`2017-10-01 07:00`: The `last_visit_date` column is aggregated by REPLACE, so `2017-10-01 07:00` has replaced `2017-10-01 06:00`.
-*`2017-10-01 07:00`: Because the `last_visit_date`column is aggregated by REPLACE, the `2017-10-01 07:00` column has been replaced by `2017-10-01 06:00`.
-> Note: For data in the same import batch, the order of replacement is not guaranteed for the aggregation of REPLACE. For example, in this case, it may be `2017-10-01 06:00`. For data from different imported batches, it can be guaranteed that the data from the latter batch will replace the former batch.
+> Note: When using REPLACE to aggregate data from the same import batch, the order of replacement is uncertain. That means, in this case, the data eventually saved in Doris could be `2017-10-01 06:00`. However, for different import batches, it is certain that data from the new batch will replace those from the old batch.
-*`35`: Because the aggregation type of the `cost`column is SUM, 35 is accumulated from 20 + 15.
-*`10`: Because the aggregation type of the`max_dwell_time`column is MAX, 10 and 2 take the maximum and get 10.
-*`2`: Because the aggregation type of `min_dwell_time`column is MIN, 10 and 2 take the minimum value and get 2.
+*`35`: The `cost`column is aggregated by SUM, so the update value `35` is the result of `20` + `15`.
-After aggregation, Doris ultimately only stores aggregated data. In other words, detailed data will be lost and users can no longer query the detailed data before aggregation.
+*`10`: The `max_dwell_time` column is aggregated by MAX, so `10` is saved as it is the maximum between `10` and `2`.
-### Example 2: Keep detailed data
+*`2`: The `min_dwell_time` column is aggregated by MIN, so `2` is saved as it is the minimum between `10` and `2`.
-Following example 1, we modify the table structure as follows:
+After aggregation, Doris only stores the aggregated data. In other words, the detailed raw data will no longer be available.
-|ColumnName|Type|AggregationType|Comment|
-|---|---|---|---|
-| userid | LARGEINT | | user id|
-| date | DATE | | date of data filling|
-| Time stamp | DATETIME | | Data filling time, accurate to seconds|
-| City | VARCHAR (20) | | User City|
-| age | SMALLINT | | User age|
-| sex | TINYINT | | User gender|
-| Last visit date | DATETIME | REPLACE | Last user access time|
-| Cost | BIGINT | SUM | Total User Consumption|
-| max dwell time | INT | MAX | Maximum user residence time|
-| min dwell time | INT | MIN | User minimum residence time|
+### Example 2: keep detailed data
-That is to say, a column of `timestamp` has been added to record the data filling time accurate to seconds.
+Here is a modified version of the table schema in Example 1:
-The imported data are as follows:
+| ColumnName | Type | AggregationType | Comment |
+| --------------- | ------------ | --------------- | ------------------------------------------------------------ |
+| user_id | LARGEINT | | User ID |
+| date | DATE | | Date when the data are imported |
+| time stamp | DATETIME | | Date and time when the data are imported (with second-level accuracy) |
+| city | VARCHAR (20) | | User location city |
+| age | SMALLINT | | User age |
+| sex | TINYINT | | User gender |
+| last visit date | DATETIME | REPLACE | Last visit time of the user |
+| cost | BIGINT | SUM | Total consumption of the user |
+| max_dwell_time | INT | MAX | Maximum user dwell time |
+| min_dwell_time | INT | MIN | Minimum user dwell time |
-|user_id|date|timestamp|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
-|---|---|---|---|---|---|---|---|---|---|
-| 10000 | 2017-10-01 | 2017-10-01 08:00:05 | Beijing | 20 | 0 | 2017-10-01 06:00 | 20 | 10 | 10|
-| 10000 | 2017-10-01 | 2017-10-01 09:00:05 | Beijing | 20 | 0 | 2017-10-01 07:00 | 15 | 2 | 2|
-| 10001 | 2017-10-01 | 2017-10-01 18:12:10 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22|
-| 10002 | 2017-10-02 | 2017-10-02 13:10:00 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5|
-| 10003 | 2017-10-02 | 2017-10-02 13:15:00 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11|
-| 10004 | 2017-10-01 | 2017-10-01 12:12:48 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3|
-| 10004 | 2017-10-03 | 2017-10-03 12:38:20 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6|
+A new column `timestamp` has been added to record the date and time when the data are imported (with second-level accuracy).
-Then when this batch of data is imported into Doris correctly, the final storage in Doris is as follows:
+Suppose that the import data are as follows:
-|user_id|date|timestamp|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
-|---|---|---|---|---|---|---|---|---|---|
-| 10000 | 2017-10-01 | 2017-10-01 08:00:05 | Beijing | 20 | 0 | 2017-10-01 06:00 | 20 | 10 | 10|
-| 10000 | 2017-10-01 | 2017-10-01 09:00:05 | Beijing | 20 | 0 | 2017-10-01 07:00 | 15 | 2 | 2|
-| 10001 | 2017-10-01 | 2017-10-01 18:12:10 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22|
-| 10002 | 2017-10-02 | 2017-10-02 13:10:00 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5|
-| 10003 | 2017-10-02 | 2017-10-02 13:15:00 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11|
-| 10004 | 2017-10-01 | 2017-10-01 12:12:48 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3|
-| 10004 | 2017-10-03 | 2017-10-03 12:38:20 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6|
+| user_id | date | timestamp | city | age | sex | last\_visit\_date | cost | max\_dwell\_time | min\_dwell\_time |
+| ------- | ---------- | ------------------- | --------- | ---- | ---- | ------------------- | ---- | ---------------- | ---------------- |
+| 10000 | 2017-10-01 | 2017-10-01 08:00:05 | Beijing | 20 | 0 | 2017-10-01 06:00 | 20 | 10 | 10 |
+| 10000 | 2017-10-01 | 2017-10-01 09:00:05 | Beijing | 20 | 0 | 2017-10-01 07:00 | 15 | 2 | 2 |
+| 10001 | 2017-10-01 | 2017-10-01 18:12:10 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
+| 10002 | 2017-10-02 | 2017-10-02 13:10:00 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
+| 10003 | 2017-10-02 | 2017-10-02 13:15:00 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
+| 10004 | 2017-10-01 | 2017-10-01 12:12:48 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
+| 10004 | 2017-10-03 | 2017-10-03 12:38:20 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
-We can see that the stored data, just like the imported data, does not aggregate at all. This is because, in this batch of data, because the `timestamp` column is added, the Keys of all rows are **not exactly the same**. That is, as long as the keys of each row are not identical in the imported data, Doris can save the complete detailed data even in the aggregation model.
+After importing, this batch of data will be stored in Doris as follows:
-### Example 3: Importing data and aggregating existing data
+| user_id | date | timestamp | city | age | sex | last\_visit\_date | cost | max\_dwell\_time | min\_dwell\_time |
+| ------- | ---------- | ------------------- | --------- | ---- | ---- | ------------------- | ---- | ---------------- | ---------------- |
+| 10000 | 2017-10-01 | 2017-10-01 08:00:05 | Beijing | 20 | 0 | 2017-10-01 06:00 | 20 | 10 | 10 |
+| 10000 | 2017-10-01 | 2017-10-01 09:00:05 | Beijing | 20 | 0 | 2017-10-01 07:00 | 15 | 2 | 2 |
+| 10001 | 2017-10-01 | 2017-10-01 18:12:10 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
+| 10002 | 2017-10-02 | 2017-10-02 13:10:00 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
+| 10003 | 2017-10-02 | 2017-10-02 13:15:00 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
+| 10004 | 2017-10-01 | 2017-10-01 12:12:48 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
+| 10004 | 2017-10-03 | 2017-10-03 12:38:20 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
-Take Example 1. Suppose that the data in the table are as follows:
+As you can see, the stored data are exactly the same as the import data. No aggregation has ever happened. This is because, the newly added `timestamp` column results in **difference of Keys** among the rows. That is to say, as long as the Keys of the rows are not identical in the import data, Doris can save the complete detailed data even in the Aggregate Model.
-|user_id|date|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
-|---|---|---|---|---|---|---|---|---|
-| 10000 | 2017-10-01 | Beijing | 20 | 0 | 2017-10-01 07:00 | 35 | 10 | 2|
-| 10001 | 2017-10-01 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22|
-| 10002 | 2017-10-02 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5|
-| 10003 | 2017-10-02 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11|
-| 10004 | 2017-10-01 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3|
-| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6|
+### Example 3: aggregate import data and existing data
-We imported a new batch of data:
+Based on Example 1, suppose that you have the following data stored in Doris:
-|user_id|date|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
-|---|---|---|---|---|---|---|---|---|
-| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 11:22:00 | 44 | 19 | 19|
-| 10005 | 2017-10-03 | Changsha | 29 | 1 | 2017-10-03 18:11:02 | 3 | 1 | 1|
+| user_id | date | city | age | sex | last\_visit\_date | cost | max\_dwell\_time | min\_dwell\_time |
+| ------- | ---------- | --------- | ---- | ---- | ------------------- | ---- | ---------------- | ---------------- |
+| 10000 | 2017-10-01 | Beijing | 20 | 0 | 2017-10-01 07:00 | 35 | 10 | 2 |
+| 10001 | 2017-10-01 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
+| 10002 | 2017-10-02 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
+| 10003 | 2017-10-02 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
+| 10004 | 2017-10-01 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
+| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
-Then when this batch of data is imported into Doris correctly, the final storage in Doris is as follows:
+Now you need to import a new batch of data:
-|user_id|date|city|age|sex|last\_visit\_date|cost|max\_dwell\_time|min\_dwell\_time|
-|---|---|---|---|---|---|---|---|---|
-| 10000 | 2017-10-01 | Beijing | 20 | 0 | 2017-10-01 07:00 | 35 | 10 | 2|
-| 10001 | 2017-10-01 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22|
-| 10002 | 2017-10-02 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5|
-| 10003 | 2017-10-02 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11|
-| 10004 | 2017-10-01 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3|
-| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 11:22:00 | 55 | 19 | 6|
-| 10005 | 2017-10-03 | Changsha | 29 | 1 | 2017-10-03 18:11:02 | 3 | 1 | 1|
+| user_id | date | city | age | sex | last\_visit\_date | cost | max\_dwell\_time | min\_dwell\_time |
+| ------- | ---------- | -------- | ---- | ---- | ------------------- | ---- | ---------------- | ---------------- |
+| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 11:22:00 | 44 | 19 | 19 |
+| 10005 | 2017-10-03 | Changsha | 29 | 1 | 2017-10-03 18:11:02 | 3 | 1 | 1 |
-As you can see, the existing data and the newly imported data of user 10004 have been aggregated. At the same time, 10005 new user's data were added.
+After importing, the data stored in Doris will be updated as follows:
-Data aggregation occurs in Doris in the following three stages:
+| user_id | date | city | age | sex | last\_visit\_date | cost | max\_dwell\_time | min\_dwell\_time |
+| ------- | ---------- | --------- | ---- | ---- | ------------------- | ---- | ---------------- | ---------------- |
+| 10000 | 2017-10-01 | Beijing | 20 | 0 | 2017-10-01 07:00 | 35 | 10 | 2 |
+| 10001 | 2017-10-01 | Beijing | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
+| 10002 | 2017-10-02 | Shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
+| 10003 | 2017-10-02 | Guangzhou | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
+| 10004 | 2017-10-01 | Shenzhen | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
+| 10004 | 2017-10-03 | Shenzhen | 35 | 0 | 2017-10-03 11:22:00 | 55 | 19 | 6 |
+| 10005 | 2017-10-03 | Changsha | 29 | 1 | 2017-10-03 18:11:02 | 3 | 1 | 1 |
-1. The ETL stage of data import for each batch. This phase aggregates data within each batch of imported data.
-2. The stage in which the underlying BE performs data Compaction. At this stage, BE aggregates data from different batches that have been imported.
-3. Data query stage. In data query, the data involved in the query will be aggregated accordingly.
+As you can see, the existing data and the newly imported data of User 10004 have been aggregated. Meanwhile, the new data of User 10005 have been added.
-Data may be aggregated to varying degrees at different times. For example, when a batch of data is just imported, it may not be aggregated with the existing data. But for users, user**can only query aggregated data**. That is, different degrees of aggregation are transparent to user queries. Users should always assume that data exists in terms of the degree of aggregation that **ultimately completes**, and **should not assume that some aggregation has not yet occurred**. (See the section [...]
+In Doris, data aggregation happens in the following 3 stages:
-## Uniq Model
+1. The ETL stage of each batch of import data. At this stage, the batch of import data will be aggregated internally.
+2. The data compaction stage of the underlying BE. At this stage, BE will aggregate data from different batches that have been imported.
+3. The data query stage. The data involved in the query will be aggregated accordingly.
-In some multi-dimensional analysis scenarios, users are more concerned about how to ensure the uniqueness of the Key, that is, how to obtain the uniqueness constraint of the Primary Key. Therefore, we introduced the Unique data model. Prior to version 1.2, the model was essentially a special case of the Aggregate Model and a simplified representation of the table structure. The implementation of the aggregation model is merge on read, it has poor performance on some aggregation queries ( [...]
+At different stages, data will be aggregated to varying degrees. For example, when a batch of data is just imported, it may not be aggregated with the existing data. But for users, they **can only query aggregated data**. That is, what users see are the aggregated data, and they **should not assume that what they have seen are not or partly aggregated**. (See the [Limitations of Aggregate Model](#Limitations of Aggregate Model) section for more details.)
-### Merge on read (same implementation as aggregate model)
+## Unique Model
-|ColumnName|Type|IsKey|Comment|
-|---|---|---|---|
-| user_id | BIGINT | Yes | user id|
-| username | VARCHAR (50) | Yes | User nickname|
-| city | VARCHAR (20) | No | User City|
-| age | SMALLINT | No | User Age|
-| sex | TINYINT | No | User Gender|
-| phone | LARGEINT | No | User Phone|
-| address | VARCHAR (500) | No | User Address|
-| register_time | DATETIME | No | user registration time|
+In some multi-dimensional analysis scenarios, users are highly concerned about how to ensure the uniqueness of the Key, that is, how to create uniqueness constraints for the Primary Key. Therefore, we introduce the Unique Model. Prior to Doris 1.2, the Unique Model was essentially a special case of the Aggregate Model and a simplified representation of table schema. The Aggregate Model is implemented by Merge on Read, so it might not deliver high performance in some aggregation queries ( [...]
+
+### Merge on Read ( Same Implementation as Aggregate Model)
-This is a typical user base information table. There is no aggregation requirement for this type of data, just the uniqueness of the primary key is guaranteed. (The primary key here is user_id + username). Then our statement is as follows:
+| ColumnName | Type | IsKey | Comment |
+| ------------- | ------------- | ----- | ---------------------- |
+| user_id | BIGINT | Yes | User ID |
+| username | VARCHAR (50) | Yes | Username |
+| city | VARCHAR (20) | No | User location city |
+| age | SMALLINT | No | User age |
+| sex | TINYINT | No | User gender |
+| phone | LARGEINT | No | User phone number |
+| address | VARCHAR (500) | No | User address |
+| register_time | DATETIME | No | User registration time |
+
+This is a typical user basic information table. There is no aggregation requirement for such data. The only concern is to ensure the uniqueness of the primary key. (The primary key here is user_id + username). The CREATE TABLE statement for the above table is as follows:
```
CREATE TABLE IF NOT EXISTS example_db.example_tbl
(
-`user_id` LARGEINT NOT NULL COMMENT "user id",
-`username` VARCHAR (50) NOT NULL COMMENT "username",
-`city` VARCHAR (20) COMMENT "user city",
-`age` SMALLINT COMMENT "age",
-`sex` TINYINT COMMENT "sex",
-`phone` LARGEINT COMMENT "phone",
-`address` VARCHAR (500) COMMENT "address",
-`register_time` DATETIME COMMENT "register time"
+`user_id` LARGEINT NOT NULL COMMENT "User ID",
+`username` VARCHAR (50) NOT NULL COMMENT "Username",
+`city` VARCHAR (20) COMMENT "User location city",
+`age` SMALLINT COMMENT "User age",
+`sex` TINYINT COMMENT "User sex",
+`phone` LARGEINT COMMENT "User phone number",
+`address` VARCHAR (500) COMMENT "User address",
+`register_time` DATETIME COMMENT "User registration time"
)
-Unique Key (`user_id`, `username`)
+UNIQUE KEY (`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
```
-This table structure is exactly the same as the following table structure described by the aggregation model:
-
-|ColumnName|Type|AggregationType|Comment|
-|---|---|---|---|
-| user_id | BIGINT | | user id|
-| username | VARCHAR (50) | | User nickname|
-| city | VARCHAR (20) | REPLACE | User City|
-| age | SMALLINT | REPLACE | User Age|
-| sex | TINYINT | REPLACE | User Gender|
-| phone | LARGEINT | REPLACE | User Phone|
-| address | VARCHAR (500) | REPLACE | User Address|
-| register_time | DATETIME | REPLACE | User registration time|
+This is the same table schema and the CREATE TABLE statement as those of the Aggregate Model:
-And table-building statements:
+| ColumnName | Type | AggregationType | Comment |
+| ------------- | ------------- | --------------- | ---------------------- |
+| user_id | BIGINT | | User ID |
+| username | VARCHAR (50) | | Username |
+| city | VARCHAR (20) | REPLACE | User location city |
+| age | SMALLINT | REPLACE | User age |
+| sex | TINYINT | REPLACE | User gender |
+| phone | LARGEINT | REPLACE | User phone number |
+| address | VARCHAR (500) | REPLACE | User address |
+| register_time | DATETIME | REPLACE | User registration time |
```
CREATE TABLE IF NOT EXISTS example_db.example_tbl
(
-`user_id` LARGEINT NOT NULL COMMENT "user id",
-`username` VARCHAR (50) NOT NULL COMMENT "username",
-`city` VARCHAR (20) REPLACE COMMENT "user city",
-`sex` TINYINT REPLACE COMMENT "sex",
-`phone` LARGEINT REPLACE COMMENT "phone",
-`address` VARCHAR(500) REPLACE COMMENT "address",
-`register_time` DATETIME REPLACE COMMENT "register time"
+`user_id` LARGEINT NOT NULL COMMENT "User ID",
+`username` VARCHAR (50) NOT NULL COMMENT "Username",
+`city` VARCHAR (20) REPLACE COMMENT "User location city",
+`sex` TINYINT REPLACE COMMENT "User gender",
+`phone` LARGEINT REPLACE COMMENT "User phone number",
+`address` VARCHAR(500) REPLACE COMMENT "User address",
+`register_time` DATETIME REPLACE COMMENT "User registration time"
)
AGGREGATE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
@@ -305,84 +302,86 @@ PROPERTIES (
);
```
-That is to say, the merge-on-read implementation of the Unique model can be completely replaced by REPLACE in aggregation model. Its internal implementation and data storage are exactly the same. No further examples will be given here.
+That is to say, the Merge on Read implementation of the Unique Model is equivalent to the REPLACE aggregation type in the Aggregate Model. The internal implementation and data storage are exactly the same.
-### Merge on write (introduced from version 1.2)
+### Merge on Write (Since Doris 1.2)
-The merge-on-write implementation of the Unique model is completely different from the aggregation model. The query performance is closer to the duplicate model. Compared with the aggregation model, it has a better query performance.
+The Merge on Write implementation of the Unique Model is completely different from that of the Aggregate Model. It can deliver better performance in aggregation queries with primary key limitations.
-In version 1.2.0, as a new feature, merge-on-write is disabled by default, and users can enable it by adding the following property
+In Doris 1.2.0, as a new feature, Merge on Write is disabled by default, and users can enable it by adding the following property:
```
"enable_unique_key_merge_on_write" = "true"
```
-Let's continue to use the previous table as an example, the create table statement:
+Take the previous table as an example, the corresponding CREATE TABLE statement should be:
```
CREATE TABLE IF NOT EXISTS example_db.example_tbl
(
-`user_id` LARGEINT NOT NULL COMMENT "user id",
-`username` VARCHAR (50) NOT NULL COMMENT "username",
-`city` VARCHAR (20) COMMENT "user city",
-`age` SMALLINT COMMENT "age",
-`sex` TINYINT COMMENT "sex",
-`phone` LARGEINT COMMENT "phone",
-`address` VARCHAR (500) COMMENT "address",
-`register_time` DATETIME COMMENT "register time"
+`user_id` LARGEINT NOT NULL COMMENT "User ID",
+`username` VARCHAR (50) NOT NULL COMMENT "Username",
+`city` VARCHAR (20) COMMENT "User location city",
+`age` SMALLINT COMMENT "Userage",
+`sex` TINYINT COMMENT "User gender",
+`phone` LARGEINT COMMENT "User phone number",
+`address` VARCHAR (500) COMMENT "User address",
+`register_time` DATETIME COMMENT "User registration time"
)
-Unique Key (`user_id`, `username`)
+UNIQUE KEY (`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
-"replication_allocation" = "tag.location.default: 1",
+"replication_allocation" = "tag.location.default: 1"
"enable_unique_key_merge_on_write" = "true"
);
```
-In this implementation, the table structure is completely different with aggregate model:
+The table schema produced by the above statement will be different from that of the Aggregate Model.
-|ColumnName|Type|AggregationType|Comment|
-|---|---|---|---|
-| user_id | BIGINT | | user id|
-| username | VARCHAR (50) | | User nickname|
-| city | VARCHAR (20) | NONE | User City|
-| age | SMALLINT | NONE | User Age|
-| sex | TINYINT | NONE | User Gender|
-| phone | LARGEINT | NONE | User Phone|
-| address | VARCHAR (500) | NONE | User Address|
-| register_time | DATETIME | NONE | User registration time|
+| ColumnName | Type | AggregationType | Comment |
+| ------------- | ------------- | --------------- | ---------------------- |
+| user_id | BIGINT | | User ID |
+| username | VARCHAR (50) | | Username |
+| city | VARCHAR (20) | NONE | User location city |
+| age | SMALLINT | NONE | User age |
+| sex | TINYINT | NONE | User gender |
+| phone | LARGEINT | NONE | User phone number |
+| address | VARCHAR (500) | NONE | User address |
+| register_time | DATETIME | NONE | User registration time |
-On a Unique table with the merge-on-write option enabled, the data that is overwritten and updated will be marked for deletion during the load job, and new data will be written to a new file at the same time. When querying, all data marked for deletion will be filtered out at the file level, only the latest data would be readed, which eliminates the data aggregation cost while reading, and can support many predicates pushdown now. Therefore, it can bring a relatively large performance im [...]
+On a Unique table with the Merge on Write option enabled, during the import stage, the data that are to be overwritten and updated will be marked for deletion, and new data will be written in. When querying, all data marked for deletion will be filtered out at the file level, and only the latest data would be readed. This eliminates the data aggregation cost while reading, and supports many types of predicate pushdown now. Therefore, it can largely improve performance in many scenarios, [...]
[NOTE]
-1. The new Merge-on-write implementation is disabled by default, and can only be enabled by specifying a property when creating a new table.
-2. The old Merge-on-read implementation cannot be seamlessly upgraded to the new version (the data organization is completely different). If you need to use the merge-on-write implementation version, you need to manually execute `insert into unique-mow- table select * from source table` to load data to new table.
-3. The feature delete sign and sequence col on the Unique model can still be used normally in the new implementation, and the usage has not changed.
+
+1. The new Merge on Write implementation is disabled by default, and can only be enabled by specifying a property when creating a new table.
+2. The old Merge on Read cannot be seamlessly upgraded to the new implementation (since they have completely different data organization). If you want to switch to the Merge on Write implementation, you need to manually execute `insert into unique-mow- table select * from source table` to load data to new table.
+3. The two unique features `delete sign` and `sequence col` of the Unique Model can be used as normal in the new implementation, and their usage remains unchanged.
## Duplicate Model
-In some multidimensional analysis scenarios, data has neither primary keys nor aggregation requirements. Therefore, we introduce Duplicate data model to meet this kind of demand. Examples are given.
+In some multi-dimensional analysis scenarios, there is no need for primary keys or data aggregation. For these cases, we introduce the Duplicate Model to. Here is an example:
+
+| ColumnName | Type | SortKey | Comment |
+| ---------- | -------------- | ------- | -------------- |
+| timstamp | DATETIME | Yes | Log time |
+| type | INT | Yes | Log type |
+| error_code | INT | Yes | Error code |
+| Error_msg | VARCHAR (1024) | No | Error details |
+| op_id | BIGINT | No | Operator ID |
+| op_time | DATETIME | No | Operation time |
+
+The corresponding CREATE TABLE statement is as follows:
-|ColumnName|Type|SortKey|Comment|
-|---|---|---|---|
-| timestamp | DATETIME | Yes | Logging Time|
-| type | INT | Yes | Log Type|
-| error_code|INT|Yes|error code|
-| Error_msg | VARCHAR (1024) | No | Error Details|
-| op_id|BIGINT|No|operator id|
-| op_time|DATETIME|No|operation time|
-
-The TABLE statement is as follows:
```
CREATE TABLE IF NOT EXISTS example_db.example_tbl
(
- `timestamp` DATETIME NOT NULL COMMENT "log time",
- `type` INT NOT NULL COMMENT "log type",
- `error_code` INT COMMENT "error code",
- `error_msg` VARCHAR(1024) COMMENT "error detail",
- `op_id` BIGINT COMMENT "operater id",
- `op_time` DATETIME COMMENT "operate time"
+ `timestamp` DATETIME NOT NULL COMMENT "Log time",
+ `type` INT NOT NULL COMMENT "Log type",
+ `error_code` INT COMMENT "Error code",
+ `error_msg` VARCHAR(1024) COMMENT "Error details",
+ `op_id` BIGINT COMMENT "Operator ID",
+ `op_time` DATETIME COMMENT "Operation time"
)
DUPLICATE KEY(`timestamp`, `type`, `error_code`)
DISTRIBUTED BY HASH(`type`) BUCKETS 1
@@ -391,113 +390,112 @@ PROPERTIES (
);
```
-This data model is different from Aggregate and Uniq models. Data is stored entirely in accordance with the data in the imported file, without any aggregation. Even if the two rows of data are identical, they will be retained.
-The DUPLICATE KEY specified in the table building statement is only used to specify which columns the underlying data is sorted according to. (The more appropriate name should be "Sorted Column", where the name "DUPLICATE KEY" is used to specify the data model used. For more explanations of "Sorted Column", see the section **Prefix Index**.) On the choice of DUPLICATE KEY, we recommend that the first 2-4 columns be selected appropriately.
+Different from the Aggregate and Unique Models, the Duplicate Model stores the data as they are and executes no aggregation. Even if there are two identical rows of data, they will both be retained. The DUPLICATE KEY in the CREATE TABLE statement is only used to specify based on which columns the data are sorted. (A more appropriate name than DUPLICATE KEY would be SORTING COLUMN, but it is named as such to specify the data model used. For more information, see [Prefix Index](https://dor [...]
-This data model is suitable for storing raw data without aggregation requirements and primary key uniqueness constraints. For more usage scenarios, see the **Limitations of the Aggregation Model** section.
+The Duplicate Mode l is suitable for storing raw data without aggregation requirements or primary key uniqueness constraints. For more usage scenarios, see the [Limitations of Aggregate Model](#Limitations of Aggregate Model) section.
-## Limitations of aggregation model
+## Limitations of Aggregate Model
-Here we introduce the limitations of Aggregate model.
+This section is about the limitations of the Aggregate Model.
-In the aggregation model, what the model presents is the aggregated data. That is to say, any data that has not yet been aggregated (for example, two different imported batches) must be presented in some way to ensure consistency. Let's give an example.
+The Aggregate Model only presents the aggregated data. That means we have to ensure the presentation consistency of data that has not yet been aggregated (for example, two different import batches). The following provides further explanation with examples.
-The hypothesis table is structured as follows:
+Suppose that you have the following table schema:
-|ColumnName|Type|AggregationType|Comment|
-|---|---|---|---|
-| user\_id | LARGEINT | | user id|
-| date | DATE | | date of data filling|
-| cost | BIGINT | SUM | Total User Consumption|
+| ColumnName | Type | AggregationType | Comment |
+| ---------- | -------- | --------------- | ------------------------------- |
+| user\_id | LARGEINT | | User ID |
+| date | DATE | | Date when the data are imported |
+| cost | BIGINT | SUM | Total user consumption |
Assume that there are two batches of data that have been imported into the storage engine as follows:
**batch 1**
-|user\_id|date|cost|
-|---|---|---|
-|10001|2017-11-20|50|
-|10002|2017-11-21|39|
+| user\_id | date | cost |
+| -------- | ---------- | ---- |
+| 10001 | 2017-11-20 | 50 |
+| 10002 | 2017-11-21 | 39 |
**batch 2**
-|user\_id|date|cost|
-|---|---|---|
-|10001|2017-11-20|1|
-|10001|2017-11-21|5|
-|10003|2017-11-22|22|
+| user\_id | date | cost |
+| -------- | ---------- | ---- |
+| 10001 | 2017-11-20 | 1 |
+| 10001 | 2017-11-21 | 5 |
+| 10003 | 2017-11-22 | 22 |
-As you can see, data belonging to user 10001 in two import batches has not yet been aggregated. However, in order to ensure that users can only query the aggregated data as follows:
+As you can see, data about User 10001 in these two import batches have not yet been aggregated. However, in order to ensure that users can only query the aggregated data as follows:
-|user\_id|date|cost|
-|---|---|---|
-|10001|2017-11-20|51|
-|10001|2017-11-21|5|
-|10002|2017-11-21|39|
-|10003|2017-11-22|22|
+| user\_id | date | cost |
+| -------- | ---------- | ---- |
+| 10001 | 2017-11-20 | 51 |
+| 10001 | 2017-11-21 | 5 |
+| 10002 | 2017-11-21 | 39 |
+| 10003 | 2017-11-22 | 22 |
-We add aggregation operator to query engine to ensure data consistency.
+We have added an aggregation operator to the query engine to ensure the presentation consistency of data.
-In addition, on the aggregate column (Value), when executing aggregate class queries that are inconsistent with aggregate types, attention should be paid to semantics. For example, in the example above, we execute the following queries:
+In addition, on the aggregate column (Value), when executing aggregate class queries that are inconsistent with the aggregate type, please pay attention to the semantics. For example, in the example above, if you execute the following query:
`SELECT MIN(cost) FROM table;`
-The result is 5, not 1.
+The result will be 5, not 1.
-At the same time, this consistency guarantee will greatly reduce the query efficiency in some queries.
+Meanwhile, this consistency guarantee could considerably reduce efficiency in some queries.
-Let's take the most basic count (*) query as an example:
+Take the basic count (*) query as an example:
`SELECT COUNT(*) FROM table;`
-In other databases, such queries return results quickly. Because in the implementation, we can get the query result by counting rows at the time of import and saving count statistics information, or by scanning only a column of data to get count value at the time of query, with very little overhead. But in Doris's aggregation model, the overhead of this query is **very large**.
+In other databases, such queries return results quickly. Because in actual implementation, the models can get the query result by counting rows and saving the statistics upon import, or by scanning only one certain column of data to get count value upon query, with very little overhead. But in Doris's Aggregation Model, the overhead of such queries is **large**.
-Let's take the data as an example.
+For the previous example:
**batch 1**
-|user\_id|date|cost|
-|---|---|---|
-|10001|2017-11-20|50|
-|10002|2017-11-21|39|
+| user\_id | date | cost |
+| -------- | ---------- | ---- |
+| 10001 | 2017-11-20 | 50 |
+| 10002 | 2017-11-21 | 39 |
**batch 2**
-|user\_id|date|cost|
-|---|---|---|
-|10001|2017-11-20|1|
-|10001|2017-11-21|5|
-|10003|2017-11-22|22|
+| user\_id | date | cost |
+| -------- | ---------- | ---- |
+| 10001 | 2017-11-20 | 1 |
+| 10001 | 2017-11-21 | 5 |
+| 10003 | 2017-11-22 | 22 |
-Because the final aggregation result is:
+Since the final aggregation result is:
-|user\_id|date|cost|
-|---|---|---|
-|10001|2017-11-20|51|
-|10001|2017-11-21|5|
-|10002|2017-11-21|39|
-|10003|2017-11-22|22|
+| user\_id | date | cost |
+| -------- | ---------- | ---- |
+| 10001 | 2017-11-20 | 51 |
+| 10001 | 2017-11-21 | 5 |
+| 10002 | 2017-11-21 | 39 |
+| 10003 | 2017-11-22 | 22 |
-So `select count (*) from table;` The correct result should be **4**. But if we only scan the `user_id`column and add query aggregation, the final result is **3** (10001, 10002, 10003). If aggregated without queries, the result is **5** (a total of five rows in two batches). It can be seen that both results are wrong.
+The correct result of `select count (*) from table;` should be **4**. But if the model only scans the `user_id` column and operates aggregation upon query, the final result will be **3** (10001, 10002, 10003). And if it does not operate aggregation, the final result will be **5** (a total of five rows in two batches). Apparently, both results are wrong.
-In order to get the correct result, we must read the data of `user_id` and `date`, and **together with aggregate** when querying, to return the correct result of **4**. That is to say, in the count (*) query, Doris must scan all AGGREGATE KEY columns (here are `user_id` and `date`) and aggregate them to get the semantically correct results. When aggregated columns are large, count (*) queries need to scan a large amount of data.
+In order to get the correct result, we must read both the `user_id` and `date` column, and **performs aggregation** when querying. That is to say, in the `count (*)` query, Doris must scan all AGGREGATE KEY columns (in this case, `user_id` and `date`) and aggregate them to get the semantically correct results. That means if there are many aggregated columns, `count (*)` queries could involve scanning large amounts of data.
-Therefore, when there are frequent count (*) queries in the business, we recommend that users simulate count (*) by adding a column with a value of 1 and aggregation type of SUM. As the table structure in the previous example, we modify it as follows:
+Therefore, if you need to perform frequent `count (*)` queries, we recommend that you simulate `count (*)` by adding a column of value 1 and aggregation type SUM. In this way, the table schema in the previous example will be modified as follows:
-|ColumnName|Type|AggregationType|Comment|
-|---|---|---|---|
-| user ID | BIGINT | | user id|
-| date | DATE | | date of data filling|
-| Cost | BIGINT | SUM | Total User Consumption|
-| count | BIGINT | SUM | for counting|
+| ColumnName | Type | AggregationType | Comment |
+| ---------- | ------ | --------------- | ------------------------------- |
+| user ID | BIGINT | | User ID |
+| date | DATE | | Date when the data are imported |
+| Cost | BIGINT | SUM | Total user consumption |
+| count | BIGINT | SUM | For count queries |
-Add a count column and import the data with the column value **equal to 1**. The result of `select count (*) from table;`is equivalent to `select sum (count) from table;` The query efficiency of the latter is much higher than that of the former. However, this method also has limitations, that is, users need to guarantee that they will not import rows with the same AGGREGATE KEY column repeatedly. Otherwise, `select sum (count) from table;`can only express the number of rows originally im [...]
+The above adds a count column, the value of which will always be **1**, so the result of `select count (*) from table;`is equivalent to that of `select sum (count) from table;` The latter is much more efficient than the former. However, this method has its shortcomings, too. That is, it requires that users will not import rows with the same values in the AGGREGATE KEY columns. Otherwise, `select sum (count) from table;` can only express the number of rows of the originally imported data [...]
-Another way is to **change the aggregation type of the count column above to REPLACE, and still weigh 1**. Then `select sum (count) from table;` and `select count (*) from table;` the results will be consistent. And in this way, there is no restriction on importing duplicate rows.
+Another method is to add a `cound` column of value 1 but aggregation type of REPLACE. Then `select sum (count) from table;` and `select count (*) from table;` could produce the same results. Moreover, this method does not require the absence of same AGGREGATE KEY columns in the import data.
-### Merge-on-write implementation of Unique model
+### Merge on Write of Unique model
-In Merge-on-write implementation, there is no limitation of aggregation model. A new structure delete-bitmap is added to each rowset during loading, to mark some data as overwritten or deleted. With the previous example, after the first batch of data is loaded, the status is as follows:
+The Merge on Write implementation in the Unique Model does not impose the same limitation as the Aggregate Model. In Merge on Write, the model adds a `delete bitmap` for each imported rowset to mark the data being overwritten or deleted. With the previous example, after Batch 1 is imported, the data status will be as follows:
**batch 1**
@@ -506,7 +504,7 @@ In Merge-on-write implementation, there is no limitation of aggregation model. A
| 10001 | 2017-11-20 | 50 | false |
| 10002 | 2017-11-21 | 39 | false |
-After the batch2 is loaded, the duplicate rows in the first batch will be marked as deleted, and the status of the two batches of data is as follows
+After Batch 2 is imported, the duplicate rows in the first batch will be marked as deleted, and the status of the two batches of data is as follows
**batch 1**
@@ -523,25 +521,24 @@ After the batch2 is loaded, the duplicate rows in the first batch will be marked
| 10001 | 2017-11-21 | 5 | false |
| 10003 | 2017-11-22 | 22 | false |
-When querying, all data marked for deletion in the delete-bitmap will not be read out, so there is no need to do any data aggregation. The number of valid rows in the above data is 4 rows, and the query result should also be 4 rows.
+In queries, all data marked `true` in the `delete bitmap` will not be read, so there is no need for data aggregation. Since there are 4 valid rows in the above data, the query result should also be 4. This also enables minimum overhead since it only scans one column of data.
-It is also possible to obtain the result in the least expensive way, that is, the way of "scanning only a certain column of data to obtain the count value" mentioned above.
-
-In the test environment, the performance of the count(*) query in the merge-on-write implementation of the Unique model is more than 10 times faster than that of the aggregation model.
+In the test environment, `count(*)` queries in Merge on Write of the Unique Model deliver 10 times higher performance than that of the Aggregate Model.
### Duplicate Model
-Duplicate model has no limitation of aggregation model. Because the model does not involve aggregate semantics, when doing count (*) query, we can get the correct semantics by choosing a column of queries arbitrarily.
+The Duplicate Model does not impose the same limitation as the Aggregate Model because it does not involve aggregation semantics. For any columns, it can return the semantically correct results in `count (*)` queries.
## Key Columns
-For the Duplicate,Aggregate and Unique models,The key columns will be given when the table created, but it is actually different: For the Duplicate model, the key columns of the table can be regarded as just "sort columns", not an unique identifier. In aggregate type tables such as Aggregate and Unique models, the key columns are both "sort columns" and "unique identification columns", which were the real "key columns".
+
+For the Duplicate, Aggregate, and Unique Models, the Key columns will be specified when the table is created, but there exist some differences: In the Duplicate Model, the Key columns of the table can be regarded as just "sorting columns", but not unique identifiers. In Aggregate and Unique Models, the Key columns are both "sorting columns" and "unique identifier columns".
## Suggestions for Choosing Data Model
-Because the data model was established when the table was built, and **could not be modified. Therefore, it is very important to select an appropriate data model**.
+Since the data model was established when the table was built, and **irrevocable thereafter, it is very important to select the appropriate data model**.
-1. Aggregate model can greatly reduce the amount of data scanned and the amount of query computation by pre-aggregation. It is very suitable for report query scenarios with fixed patterns. But this model is not very friendly for count (*) queries. At the same time, because the aggregation method on the Value column is fixed, semantic correctness should be considered in other types of aggregation queries.
-2. Uniq model guarantees the uniqueness of primary key for scenarios requiring unique primary key constraints. However, the query advantage brought by pre-aggregation such as ROLLUP cannot be exploited.
- 1. For users who have high performance requirements for aggregate queries, it is recommended to use the merge-on-write implementation added since version 1.2.
- 2. The Unique model only supports the entire row update. If the user needs unique key with partial update (such as loading multiple source tables into one doris table), you can consider using the Aggregate model, setting the aggregate type of the non-primary key columns to REPLACE_IF_NOT_NULL. For detail, please refer to [CREATE TABLE Manual](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md)
-3. Duplicate is suitable for ad-hoc queries of any dimension. Although it is also impossible to take advantage of the pre-aggregation feature, it is not constrained by the aggregation model and can take advantage of the queue-store model (only reading related columns, but not all Key columns).
+1. The Aggregate Model can greatly reduce the amount of data scanned and query computation by pre-aggregation. Thus, it is very suitable for report query scenarios with fixed patterns. But this model is unfriendly to `count (*)` queries. Meanwhile, since the aggregation method on the Value column is fixed, semantic correctness should be considered in other types of aggregation queries.
+2. The Unique Model guarantees the uniqueness of primary key for scenarios requiring unique primary key. The downside is that it cannot exploit the advantage brought by pre-aggregation such as ROLLUP in queries.
+ 1. Users who have high performance requirements for aggregate queries are recommended to use the newly added Merge on Write implementation since version 1.2.
+ 2. The Unique Model only supports entire-row updates. If you require primary key uniqueness as well as partial updates of certain columns (such as loading multiple source tables into one Doris table), you can consider using the Aggregate Model, while setting the aggregate type of the non-primary key columns to REPLACE_IF_NOT_NULL. See [CREATE TABLE Manual](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) for more details.
+3. The Duplicate Model is suitable for ad-hoc queries of any dimensions. Although it may not be able to take advantage of the pre-aggregation feature, it is not limited by what constrains the Aggregate Model and can give full play to the advantage of columnar storage (reading only the relevant columns, but not all Key columns).
diff --git a/docs/en/docs/data-table/data-partition.md b/docs/en/docs/data-table/data-partition.md
index 3de2b3e34d..8ee9a738a4 100644
--- a/docs/en/docs/data-table/data-partition.md
+++ b/docs/en/docs/data-table/data-partition.md
@@ -24,51 +24,53 @@ specific language governing permissions and limitations
under the License.
-->
-# Data Partition
+# Data Partitioning
-This document mainly introduces Doris's table construction and data partitioning, as well as problems and solutions that may be encountered in the construction of the table.
+This topic is about table creation and data partitioning in Doris, including the common problems in table creation and their solutions.
## Basic Concepts
-In Doris, data is logically described in the form of a table.
+In Doris, data is logically described in the form of table.
### Row & Column
-A table includes rows (rows) and columns (columns). Row is a row of data for the user. Column is used to describe different fields in a row of data.
+A table contains rows and columns.
-Column can be divided into two broad categories: Key and Value. From a business perspective, Key and Value can correspond to dimension columns and metric columns, respectively. From the perspective of the aggregation model, the same row of Key columns will be aggregated into one row. The way the Value column is aggregated is specified by the user when the table is built. For an introduction to more aggregation models, see the [Doris Data Model](./data-model.md).
+Row refers to a row of data about the user. Column is used to describe different fields in a row of data.
+
+Columns can be divided into two categories: Key and Value. From a business perspective, Key and Value correspond to dimension columns and metric columns, respectively. In the Aggregate Model, rows with the same values in Key columns will be aggregated into one row. The way how Value columns are aggregated is specified by the user when the table is built. For more information about the Aggregate Model, please see the [Data Model](./data-model.md).
### Tablet & Partition
-In Doris's storage engine, user data is horizontally divided into several data slices (also known as data buckets). Each tablet contains several rows of data. The data between the individual tablets does not intersect and is physically stored independently.
+In the Doris storage engine, user data are horizontally divided into data tablets (also known as data buckets). Each tablet contains several rows of data. The data between the individual tablets do not intersect and is physically stored independently.
-Multiple tablets are logically attributed to different partitions. A tablet belongs to only one Partition. And a Partition contains several Tablets. Because the tablet is physically stored independently, it can be considered that the Partition is physically independent. Tablet is the smallest physical storage unit for data movement, replication, and so on.
+Tablets are logically attributed to different Partitions. One Tablet belongs to only one Partition, and one Partition contains several Tablets. Since the tablets are physically stored independently, the partitions can be seen as physically independent, too. Tablet is the smallest physical storage unit for data operations such as movement and replication.
-Several Partitions form a Table. Partition can be thought of as the smallest logical unit of management. Importing and deleting data can be done for one Partition or only for one Partition.
+A Table is formed of multiple Partitions. Partition can be thought of as the smallest logical unit of management. Data import and deletion can be performed on only one Partition.
-## Data division
+## Data Partitioning
-We use a table-building operation to illustrate Doris' data partitioning.
+The following illustrates on data partitioning in Doris using the example of a CREATE TABLE operation.
-Doris's table creation is a synchronous command. The result is returned after the SQL execution is completed. If the command returns successfully, it means that the table creation is successful. For specific table creation syntax, please refer to [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md), or you can view more details through `HELP CREATE TABLE;` Much help.See more help with `HELP CREATE TABLE;`.
+CREATE TABLE in Doris is a synchronous command. It returns results after the SQL execution is completed. Successful returns indicate successful table creation. For more information on the syntax, please refer to [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md), or input the `HELP CREATE TABLE;` command.
-This section introduces Doris's approach to building tables with an example.
+This section introduces how to create tables in Doris.
```sql
-- Range Partition
CREATE TABLE IF NOT EXISTS example_db.example_range_tbl
(
- `user_id` LARGEINT NOT NULL COMMENT "User id",
- `date` DATE NOT NULL COMMENT "Data fill in date time",
- `timestamp` DATETIME NOT NULL COMMENT "Timestamp of data being poured",
- `city` VARCHAR(20) COMMENT "The city where the user is located",
+ `user_id` LARGEINT NOT NULL COMMENT "User ID",
+ `date` DATE NOT NULL COMMENT "Date when the data are imported",
+ `timestamp` DATETIME NOT NULL COMMENT "Timestamp when the data are imported",
+ `city` VARCHAR(20) COMMENT "User location city",
`age` SMALLINT COMMENT "User age",
`sex` TINYINT COMMENT "User gender",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User last visit time",
`cost` BIGINT SUM DEFAULT "0" COMMENT "Total user consumption",
- `max_dwell_time` INT MAX DEFAULT "0" COMMENT "User maximum dwell time",
- `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "User minimum dwell time"
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum user dwell time",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum user dwell time"
)
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
@@ -91,16 +93,16 @@ PROPERTIES
CREATE TABLE IF NOT EXISTS example_db.example_list_tbl
(
- `user_id` LARGEINT NOT NULL COMMENT "User id",
- `date` DATE NOT NULL COMMENT "Data fill in date time",
- `timestamp` DATETIME NOT NULL COMMENT "Timestamp of data being poured",
- `city` VARCHAR(20) NOT NULL COMMENT "The city where the user is located",
+ `user_id` LARGEINT NOT NULL COMMENT "User ID",
+ `date` DATE NOT NULL COMMENT "Date when the data are imported",
+ `timestamp` DATETIME NOT NULL COMMENT "Timestamp when the data are imported",
+ `city` VARCHAR(20) COMMENT "User location city",
`age` SMALLINT COMMENT "User Age",
`sex` TINYINT COMMENT "User gender",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User last visit time",
`cost` BIGINT SUM DEFAULT "0" COMMENT "Total user consumption",
- `max_dwell_time` INT MAX DEFAULT "0" COMMENT "User maximum dwell time",
- `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "User minimum dwell time"
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum user dwell time",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum user dwell time"
)
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
@@ -120,100 +122,102 @@ PROPERTIES
```
-### Column Definition
+### Definition of Column
-Here we only use the AGGREGATE KEY data model as an example. See the [Doris Data Model](./data-model.md) for more data models.
+Here we only use the AGGREGATE KEY data model as an example. See [Doris Data Model](./data-model.md) for more information.
-The basic type of column can be viewed by executing `HELP CREATE TABLE;` in mysql-client.
+You can view the basic types of columns by executing `HELP CREATE TABLE;` in MySQL Client.
-In the AGGREGATE KEY data model, all columns that do not specify an aggregation mode (SUM, REPLACE, MAX, MIN) are treated as Key columns. The rest is the Value column.
+In the AGGREGATE KEY data model, all columns that are specified with an aggregation type (SUM, REPLACE, MAX, or MIN) are Value columns. The rest are the Key columns.
-When defining columns, you can refer to the following suggestions:
+A few suggested rules for defining columns include:
-1. The Key column must precede all Value columns.
-2. Try to choose the type of integer. Because integer type calculations and lookups are much more efficient than strings.
-3. For the selection principle of integer types of different lengths, follow **enough to**.
-4. For lengths of type VARCHAR and STRING, follow **enough to**.
+1. The Key columns must precede all Value columns.
+2. Try to choose the INT type as much as possible. Because calculations and lookups on INT types are much more efficient than those on strings.
+3. For the lengths of the INT types, follow the **good enough** principle.
+4. For the lengths of the VARCHAR and STRING types, also follow the **good enough** principle.
-### Partitioning and Bucket
+### Partitioning and Bucketing
-Doris supports two levels of data partitioning. The first layer is Partition, which supports Range and List partitioning. The second layer is the Bucket (Tablet), which only supports Hash partitioning.
+Doris supports two layers of data partitioning. The first level is Partition, including range partitioning and list partitioning. The second is Bucket (Tablet), which only supports hash partitioning.
-It is also possible to use only one layer of partitioning. When using a layer partition, only Bucket partitioning is supported.
+It is also possible to use one layer of data partitioning. In this case, it only supports data bucketing.
1. Partition
- * The Partition column can specify one or more columns. The partition class must be a KEY column. The use of multi-column partitions is described later in the **Multi-column partitioning** summary.
- * Regardless of the type of partition column, double quotes are required when writing partition values.
- * There is no theoretical limit on the number of partitions.
- * When you do not use Partition to build a table, the system will automatically generate a Partition with the same name as the table name. This Partition is not visible to the user and cannot be modified.
- * **Do not add partitions with overlapping ranges** when creating partitions.
-
- #### Range Partition
-
- * Partition columns are usually time columns for easy management of old and new data.
-
- * Partition supports only the upper bound by `VALUES LESS THAN (...)`, the system will use the upper bound of the previous partition as the lower bound of the partition, and generate a left closed right open interval. Passing, also supports specifying the upper and lower bounds by `VALUES [...)`, and generating a left closed right open interval.
-
- * It is easier to understand by specifying `VALUES [...)`. Here is an example of the change in partition range when adding or deleting partitions using the `VALUES LESS THAN (...)` statement:
- * As in the `example_range_tbl` example above, when the table is built, the following 3 partitions are automatically generated:
- ```
- P201701: [MIN_VALUE, 2017-02-01)
- P201702: [2017-02-01, 2017-03-01)
- P201703: [2017-03-01, 2017-04-01)
- ```
-
- * When we add a partition p201705 VALUES LESS THAN ("2017-06-01"), the partition results are as follows:
-
- ```
- P201701: [MIN_VALUE, 2017-02-01)
- P201702: [2017-02-01, 2017-03-01)
- P201703: [2017-03-01, 2017-04-01)
- P201705: [2017-04-01, 2017-06-01)
- ```
-
- * At this point we delete the partition p201703, the partition results are as follows:
-
- ```
- p201701: [MIN_VALUE, 2017-02-01)
- p201702: [2017-02-01, 2017-03-01)
- p201705: [2017-04-01, 2017-06-01)
- ```
-
- > Note that the partition range of p201702 and p201705 has not changed, and there is a hole between the two partitions: [2017-03-01, 2017-04-01). That is, if the imported data range is within this hole, it cannot be imported.
-
- * Continue to delete partition p201702, the partition results are as follows:
-
- ```
- p201701: [MIN_VALUE, 2017-02-01)
- p201705: [2017-04-01, 2017-06-01)
- ```
-
- > The void range becomes: [2017-02-01, 2017-04-01)
-
- * Now add a partition p201702new VALUES LESS THAN ("2017-03-01"), the partition results are as follows:
-
- ```
- p201701: [MIN_VALUE, 2017-02-01)
- p201702new: [2017-02-01, 2017-03-01)
- p201705: [2017-04-01, 2017-06-01)
- ```
-
- > You can see that the hole size is reduced to: [2017-03-01, 2017-04-01)
-
- * Now delete partition p201701 and add partition p201612 VALUES LESS THAN ("2017-01-01"), the partition result is as follows:
-
- ```
- p201612: [MIN_VALUE, 2017-01-01)
- p201702new: [2017-02-01, 2017-03-01)
- p201705: [2017-04-01, 2017-06-01)
- ```
-
- > A new void appeared: [2017-01-01, 2017-02-01)
-
- In summary, the deletion of a partition does not change the scope of an existing partition. There may be holes in deleting partitions. When a partition is added by the `VALUES LESS THAN` statement, the lower bound of the partition immediately follows the upper bound of the previous partition.
-
- In addition to the single-column partitioning we have seen above, Range partition also supports **multi-column partitioning**, examples are as follows:
+ * You can specify one or more columns as the partitioning columns, but they have to be KEY columns. The usage of multi-column partitions is described further below.
+ * Regardless of the type of the partitioning columns, double quotes are required for partition values.
+ * There is no theoretical limit on the number of partitions.
+ * If users create a table without specifying the partitions, the system will automatically generate a Partition with the same name as the table. This Partition contains all data in the table and is neither visible to users nor modifiable.
+ * **Partitions should not have overlapping ranges**.
+
+ #### Range Partitioning
+
+ * Partitioning columns are usually time columns for easy management of old and new data.
+
+ * Range partitioning supports specifying only the upper bound by `VALUES LESS THAN (...)`. The system will use the upper bound of the previous partition as the lower bound of the next partition, and generate a left-closed right-open interval. It also supports specifying both the upper and lower bounds by `VALUES [...)`, and generate a left-closed right-open interval.
+
+ * The following takes the `VALUES [...)` method as an example since it is more comprehensible. It shows how the partition ranges change as we use the `VALUES LESS THAN (...)` statement to add or delete partitions:
+
+ * As in the `example_range_tbl` example above, when the table is created, the following 3 partitions are automatically generated:
+
+ ```
+ P201701: [MIN_VALUE, 2017-02-01)
+ P201702: [2017-02-01, 2017-03-01)
+ P201703: [2017-03-01, 2017-04-01)
+ ```
+
+ * If we add Partition p201705 VALUES LESS THAN ("2017-06-01"), the results will be as follows:
+
+ ```
+ P201701: [MIN_VALUE, 2017-02-01)
+ P201702: [2017-02-01, 2017-03-01)
+ P201703: [2017-03-01, 2017-04-01)
+ P201705: [2017-04-01, 2017-06-01)
+ ```
+
+ * Then we delete Partition p201703, the results will be as follows:
+
+ ```
+ p201701: [MIN_VALUE, 2017-02-01)
+ p201702: [2017-02-01, 2017-03-01)
+ p201705: [2017-04-01, 2017-06-01)
+ ```
+
+ > Note that the partition range of p201702 and p201705 has not changed, and there is a gap between the two partitions: [2017-03-01, 2017-04-01). That means, if the imported data is within this gap range, the import would fail.
+
+ * Now we go on and delete Partition p201702, the results will be as follows:
+
+ ```
+ p201701: [MIN_VALUE, 2017-02-01)
+ p201705: [2017-04-01, 2017-06-01)
+ ```
+
+ > The gap range expands to: [2017-02-01, 2017-04-01)
+
+ * Then we add Partition p201702new VALUES LESS THAN ("2017-03-01"), the results will be as follows:
+
+ ```
+ p201701: [MIN_VALUE, 2017-02-01)
+ p201702new: [2017-02-01, 2017-03-01)
+ p201705: [2017-04-01, 2017-06-01)
+ ```
+
+ > The gap range shrinks to: [2017-03-01, 2017-04-01)
+
+ * Now we delete Partition p201701 and add Partition p201612 VALUES LESS THAN ("2017-01-01"), the partition result is as follows:
+
+ ```
+ p201612: [MIN_VALUE, 2017-01-01)
+ p201702new: [2017-02-01, 2017-03-01)
+ p201705: [2017-04-01, 2017-06-01)
+ ```
+
+ > This results in a new gap range: [2017-01-01, 2017-02-01)
+
+ In summary, the deletion of a partition does not change the range of the existing partitions, but might result in gaps. When a partition is added via the `VALUES LESS THAN` statement, the lower bound of one partition is the upper bound of its previous partition.
+
+ In addition to the single-column partitioning mentioned above, Range Partitioning also supports **multi-column partitioning**. Examples are as follows:
```text
PARTITION BY RANGE(`date`, `id`)
@@ -223,191 +227,194 @@ It is also possible to use only one layer of partitioning. When using a layer pa
PARTITION `p201703_all` VALUES LESS THAN ("2017-04-01")
)
```
-
- In the above example, we specify `date` (DATE type) and `id` (INT type) as partition columns. The resulting partitions in the above example are as follows:
-
+
+ In the above example, we specify `date` (DATE type) and `id` (INT type) as the partitioning columns, so the resulting partitions will be as follows:
+
```
- *p201701_1000: [(MIN_VALUE, MIN_VALUE), ("2017-02-01", "1000") )
- *p201702_2000: [("2017-02-01", "1000"), ("2017-03-01", "2000") )
- *p201703_all: [("2017-03-01", "2000"), ("2017-04-01", MIN_VALUE))
+ *p201701_1000: [(MIN_VALUE, MIN_VALUE), ("2017-02-01", "1000") )
+ *p201702_2000: [("2017-02-01", "1000"), ("2017-03-01", "2000") )
+ *p201703_all: [("2017-03-01", "2000"), ("2017-04-01", MIN_VALUE))
```
-
- Note that the last partition user defaults only the partition value of the `date` column, so the partition value of the `id` column will be filled with `MIN_VALUE` by default. When the user inserts data, the partition column values are compared in order, and the corresponding partition is finally obtained. Examples are as follows:
-
+
+ Note that in the last partition, the user only specifies the partition value of the `date` column, so the system fills in `MIN_VALUE` as the partition value of the `id` column by default. When data are imported, the system will compare them with the partition values in order, and put the data in their corresponding partitions. Examples are as follows:
+
```
- * Data --> Partition
- * 2017-01-01, 200 --> p201701_1000
- * 2017-01-01, 2000 --> p201701_1000
- * 2017-02-01, 100 --> p201701_1000
- * 2017-02-01, 2000 --> p201702_2000
- * 2017-02-15, 5000 --> p201702_2000
- * 2017-03-01, 2000 --> p201703_all
- * 2017-03-10, 1 --> p201703_all
- * 2017-04-01, 1000 --> Unable to import
- * 2017-05-01, 1000 --> Unable to import
+ * Data --> Partition
+ * 2017-01-01, 200 --> p201701_1000
+ * 2017-01-01, 2000 --> p201701_1000
+ * 2017-02-01, 100 --> p201701_1000
+ * 2017-02-01, 2000 --> p201702_2000
+ * 2017-02-15, 5000 --> p201702_2000
+ * 2017-03-01, 2000 --> p201703_all
+ * 2017-03-10, 1 --> p201703_all
+ * 2017-04-01, 1000 --> Unable to import
+ * 2017-05-01, 1000 --> Unable to import
```
-
+
<version since="1.2.0">
- Range partition also supports use partition desc: `FROM ("2022-01-03") TO ("2022-01-06") INTERVAL 1 DAY`, can multi create partitions by day：2022-01-03 to 2022-01-06（not include 2022-01-06）, the partition result is as follows:
- ```text
+Range partitioning also supports batch partitioning. For example, you can create multiple partitions that are divided by day at a time using the `FROM ("2022-01-03") TO ("2022-01-06") INTERVAL 1 DAY`：2022-01-03 to 2022-01-06 (not including 2022-01-06), the results will be as follows:
+
p20220103: [2022-01-03, 2022-01-04)
p20220104: [2022-01-04, 2022-01-05)
p20220105: [2022-01-05, 2022-01-06)
- ```
-
+
</version>
- #### List Partition
-
- * The partition column supports the `BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR` data type, and the partition value is an enumeration value. Partitions can be hit only if the data is one of the target partition enumeration values.
- * Partition supports specifying the number of partitions contained in each partition via `VALUES IN (...) ` to specify the enumeration values contained in each partition.
- * The following example illustrates how partitions change when adding or deleting partitions.
-
- * As in the `example_list_tbl` example above, when the table is built, the following three partitions are automatically created.
-
- ```
- p_cn: ("Beijing", "Shanghai", "Hong Kong")
- p_usa: ("New York", "San Francisco")
- p_jp: ("Tokyo")
- ```
-
- * When we add a partition p_uk VALUES IN ("London"), the result of the partition is as follows
-
- ```
- p_cn: ("Beijing", "Shanghai", "Hong Kong")
- p_usa: ("New York", "San Francisco")
- p_jp: ("Tokyo")
- p_uk: ("London")
- ```
-
- * When we delete the partition p_jp, the result of the partition is as follows.
-
- ```
- p_cn: ("Beijing", "Shanghai", "Hong Kong")
- p_usa: ("New York", "San Francisco")
- p_uk: ("London")
- ```
-
- List partition also supports **multi-column partition**, examples are as follows:
-
- ```text
- PARTITION BY LIST(`id`, `city`)
- (
- PARTITION `p1_city` VALUES IN (("1", "Beijing"), ("1", "Shanghai")),
- PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("2", "Shanghai")),
- PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("3", "Shanghai"))
- )
+
+#### List Partitioning
+
+* The partitioning columns support the `BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR` data types, and the partition values are enumeration values. Partitions can be only hit if the data is one of the enumeration values in the target partition.
+
+* List partitioning supports using `VALUES IN (...) ` to specify the enumeration values contained in each partition.
+
+* The following example illustrates how partitions change when adding or deleting a partition.
+
+ * As in the `example_list_tbl` example above, when the table is created, the following three partitions are automatically created.
+
```
-
- In the above example, we specify `id`(INT type) and `city`(VARCHAR type) as partition columns. The above example ends up with the following partitions.
-
+ p_cn: ("Beijing", "Shanghai", "Hong Kong")
+ p_usa: ("New York", "San Francisco")
+ p_jp: ("Tokyo")
```
- * p1_city: [("1", "Beijing"), ("1", "Shanghai")]
- * p2_city: [("2", "Beijing"), ("2", "Shanghai")]
- * p3_city: [("3", "Beijing"), ("3", "Shanghai")]
+
+ * If we add Partition p_uk VALUES IN ("London"), the results will be as follows:
+
+ ```
+ p_cn: ("Beijing", "Shanghai", "Hong Kong")
+ p_usa: ("New York", "San Francisco")
+ p_jp: ("Tokyo")
+ p_uk: ("London")
```
-
- When the user inserts data, the partition column values will be compared sequentially in order to finally get the corresponding partition. An example is as follows.
-
+
+ * Now we delete Partition p_jp, the results will be as follows:
+
```
- * Data ---> Partition
- * 1, Beijing ---> p1_city
- * 1, Shanghai ---> p1_city
- * 2, Shanghai ---> p2_city
- * 3, Beijing ---> p3_city
- * 1, Tianjin ---> Unable to import
- * 4, Beijing ---> Unable to import
+ p_cn: ("Beijing", "Shanghai", "Hong Kong")
+ p_usa: ("New York", "San Francisco")
+ p_jp: ("Tokyo")
+ p_uk: ("London")
```
-2. Bucket
+ List partitioning also supports **multi-column partitioning**. Examples are as follows:
- * If a Partition is used, the `DISTRIBUTED ...` statement describes the division rules for the data in each partition. If you do not use Partition, it describes the rules for dividing the data of the entire table.
- * The bucket column can be multiple columns,Aggregate and Unique models must be key columns, and Duplicate models can be key columns and value columns. The bucket column can be the same or different from the Partition column.
- * The choice of bucket column is a trade-off between **query throughput** and **query concurrency**:
+ ```
+ PARTITION BY LIST(`id`, `city`)
+ (
+ PARTITION `p1_city` VALUES IN (("1", "Beijing"), ("1", "Shanghai")),
+ PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("2", "Shanghai")),
+ PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("3", "Shanghai"))
+ )
+ ```
- 1. If you select multiple bucket columns, the data is more evenly distributed. However, if the query condition does not include the equivalent condition for all bucket columns, a query will scan all buckets. The throughput of such queries will increase, and the latency of a single query will decrease. This method is suitable for large throughput and low concurrent query scenarios.
- 2. If you select only one or a few bucket columns, the point query can query only one bucket. This approach is suitable for high-concurrency point query scenarios.
-
- * There is no theoretical limit on the number of buckets.
+ In the above example, we specify `id` (INT type) and `city` (VARCHAR type) as the partitioning columns. The partitions are as follows:
-3. Recommendations on the number and amount of data for Partitions and Buckets.
+ ```
+ p1_city: [("1", "Beijing"), ("1", "Shanghai")]
+ * p2_city: [("2", "Beijing"), ("2", "Shanghai")]
+ * p3_city: [("3", "Beijing"), ("3", "Shanghai")]
+ ```
- * The total number of tablets in a table is equal to (Partition num * Bucket num).
- * The number of tablets in a table, which is slightly more than the number of disks in the entire cluster, regardless of capacity expansion.
- * The data volume of a single tablet does not theoretically have an upper and lower bound, but is recommended to be in the range of 1G - 10G. If the amount of data for a single tablet is too small, the aggregation of the data is not good and the metadata management pressure is high. If the amount of data is too large, it is not conducive to the migration, completion, and increase the cost of Schema Change or Rollup operation failure retry (the granularity of these operations failure [...]
- * When the tablet's data volume principle and quantity principle conflict, it is recommended to prioritize the data volume principle.
- * When building a table, the number of Buckets for each partition is uniformly specified. However, when dynamically increasing partitions (`ADD PARTITION`), you can specify the number of Buckets for the new partition separately. This feature can be used to easily reduce or expand data.
- * Once the number of Buckets for a Partition is specified, it cannot be changed. Therefore, when determining the number of Buckets, you need to consider the expansion of the cluster in advance. For example, there are currently only 3 hosts, and each host has 1 disk. If the number of Buckets is only set to 3 or less, then even if you add more machines later, you can't increase the concurrency.
- * Give some examples: Suppose there are 10 BEs, one for each BE disk. If the total size of a table is 500MB, you can consider 4-8 shards. 5GB: 8-16. 50GB: 32. 500GB: Recommended partitions, each partition is about 50GB in size, with 16-32 shards per partition. 5TB: Recommended partitions, each with a size of around 50GB and 16-32 shards per partition.
-
- > Note: The amount of data in the table can be viewed by the [show data](../sql-manual/sql-reference/Show-Statements/SHOW-DATA.md) command. The result is divided by the number of copies, which is the amount of data in the table.
+ When data are imported, the system will compare them with the partition values in order, and put the data in their corresponding partitions. Examples are as follows:
+
+ ```
+ Data ---> Partition
+ 1, Beijing ---> p1_city
+ 1, Shanghai ---> p1_city
+ 2, Shanghai ---> p2_city
+ 3, Beijing ---> p3_city
+ 1, Tianjin ---> Unable to import
+ 4, Beijing ---> Unable to import
+ ```
+
+2. Bucketing
+
+ * If you use the Partition method, the `DISTRIBUTED ...` statement will describe how data are divided among partitions. If you do not use the Partition method, that statement will describe how data of the whole table are divided.
+ * You can specify multiple columns as the bucketing columns. In Aggregate and Unique Models, bucketing columns must be Key columns; in the Duplicate Model, bucketing columns can be Key columns and Value columns. Bucketing columns can either be partitioning columns or not.
+ * The choice of bucketing columns is a trade-off between **query throughput** and **query concurrency**:
+
+ 1. If you choose to specify multiple bucketing columns, the data will be more evenly distributed. However, if the query condition does not include the equivalent conditions for all bucketing columns, the system will scan all buckets, largely increasing the query throughput and decreasing the latency of a single query. This method is suitable for high-throughput, low-concurrency query scenarios.
+ 2. If you choose to specify only one or a few bucketing columns, point queries might scan only one bucket. Thus, when multiple point queries are preformed concurrently, they might scan various buckets, with no interaction between the IO operations (especially when the buckets are stored on various disks). This approach is suitable for high-concurrency point query scenarios.
+
+ * There is no theoretical limit on the number of buckets.
+
+3. Recommendations on the number and data volume for Partitions and Buckets.
+
+ * The total number of tablets in a table is equal to (Partition num * Bucket num).
+ * The recommended number of tablets in a table, regardless of capacity expansion, is slightly more than the number of disks in the entire cluster.
+ * The data volume of a single tablet does not have an upper or lower limit theoretically, but is recommended to be in the range of 1G - 10G. Overly small data volume of a single tablet can impose a stress on data aggregation and metadata management; while overly large data volume can cause trouble in data migration and completion, and increase the cost of Schema Change or Rollup operation failures (These operations are performed on the Tablet level).
+ * For the tablets, if you cannot have the ideal data volume and the ideal quantity at the same time, it is recommended to prioritize the ideal data volume.
+ * Upon table creation, you specify the same number of Buckets for each Partition. However, when dynamically increasing partitions (`ADD PARTITION`), you can specify the number of Buckets for the new partitions separately. This feature can help you cope with data reduction or expansion.
+ * Once you have specified the number of Buckets for a Partition, you may not change it afterwards. Therefore, when determining the number of Buckets, you need to consider the need of cluster expansion in advance. For example, if there are only 3 hosts, and each host has only 1 disk, and you have set the number of Buckets is only set to 3 or less, then no amount of newly added machines can increase concurrency.
+ * For example, suppose that there are 10 BEs and each BE has one disk, if the total size of a table is 500MB, you can consider dividing it into 4-8 tablets; 5GB: 8-16 tablets; 50GB: 32 tablets; 500GB: you may consider dividing it into partitions, with each partition about 50GB in size, and 16-32 tablets per partition; 5TB: divided into partitions of around 50GB and 16-32 tablets per partition.
+
+ > Note: You can check the data volume of the table using the [show data](../sql-manual/sql-reference/Show-Statements/SHOW-DATA.md) command. Divide the returned result by the number of copies, and you will know the data volume of the table.
-4. About the settings and usage scenarios of Random Distribution.
+4. About the settings and usage scenarios of Random Distribution:
- * If the OLAP table does not have columns with replace type, set the data bucket mode of the table to RANDOM to avoid the severe data skew(When data is loaded into the partition corresponding to the table, each batch of data in a single load task will randomly select a tablet to write)).
- * When the bucket distribution mode of the table is set to RANDOM, because there is no bucket distribution column, it is not possible to query only a few buckets based on the bucket distribution column values. When querying the table, all buckets int the hit partition will be scanned at the same time. This setting is suitable for aggregate query analysis of the table data as a whole, but not for highly concurrent point queries.
- * If the data distribution of the OLAP table is Random Distribution, you can set the load to single tablet mode (set 'load_to_single_tablet' to true) when importing data. When importing large amounts of data, a task will only write one tablet when writing data to the corresponding partition. This will improve the concurrency and throughput of data import and reduce the problem of write amplification caused by data import and compaction, finally ensure the stability of the cluster.
+ * If the OLAP table does not have columns of REPLACE type, set the data bucketing mode of the table to RANDOM. This can avoid severe data skew. (When loading data into the partition corresponding to the table, each batch of data in a single load task will be written into a randomly selected tablet).
+ * When the bucketing mode of the table is set to RANDOM, since there are no specified bucketing columns, it is impossible to query only a few buckets, so all buckets in the hit partition will be scanned when querying the table. Thus, this setting is only suitable for aggregate query analysis of the table data as a whole, but not for highly concurrent point queries.
+ * If the data distribution of the OLAP table is Random Distribution, you can set `load to single tablet` to true when importing data. In this way, when importing large amounts of data, in one task, data will be only written in one tablet of the corresponding partition. This can improve both the concurrency and throughput of data import and reduce write amplification caused by data import and compaction, and thus, ensure cluster stability.
-#### Compound Partitions vs Single Partitions
+#### Compound Partitioning vs Single Partitioning
-Compound Partitions
+Compound Partitioning
-- The first level is called Partition, which is partition. Users can specify a dimension column as a partition column (currently only columns of integer and time types are supported), and specify the value range of each partition.
-- The second level is called Distribution, which means bucketing. Users can specify one or more dimension columns and the number of buckets to perform HASH distribution on the data or set it to Random Distribution with not specifying the bucket distribution column to randomly distribute the data.
+- The first layer of data partitioning is called Partition. Users can specify a dimension column as the partitioning column (currently only supports columns of INT and TIME types), and specify the value range of each partition.
+- The second layer is called Distribution, which means bucketing. Users can perform HASH distribution on data by specifying the number of buckets and one or more dimension columns as the bucketing columns, or perform random distribution on data by setting the mode to Random Distribution.
-Composite partitions are recommended for the following scenarios
+Compound partitioning is recommended for the following scenarios:
-- There is a time dimension or similar dimension with ordered values, which can be used as a partition column. Partition granularity can be evaluated based on import frequency, partition data volume, etc.
-- Deletion of historical data: If there is a need to delete historical data (for example, only keep the data of the last N days). With composite partitions, this can be achieved by removing historical partitions. Data deletion is also possible by sending a DELETE statement within the specified partition.
-- Solve the problem of data skew: each partition can specify the number of buckets individually. For example, partitioning by day, when the amount of data per day varies greatly, you can reasonably divide the data in different partitions by specifying the number of buckets for the partition. It is recommended to select a column with a large degree of discrimination for the bucketing column.
+- Scenarios with time dimensions or similar dimensions with ordered values, which can be used as partitioning columns. The partitioning granularity can be evaluated based on data import frequency, data volume, etc.
+- Scenarios with a need to delete historical data: If, for example, you only need to keep the data of the last N days), you can use compound partitioning so you can delete historical partitions. To remove historical data, you can also send a DELETE statement within the specified partition.
+- Scenarios with a need to avoid data skew: you can specify the number of buckets individually for each partition. For example, if you choose to partition the data by day, and the data volume per day varies greatly, you can customize the number of buckets for each partition. For the choice of bucketing column, it is recommended to select the column(s) with variety in values.
-The user can also use a single partition without using composite partitions. Then the data is only distributed in HASH.
+Users can also choose for single partitioning, which is about HASH distribution.
### PROPERTIES
-In the last PROPERTIES of the table building statement, for the relevant parameters that can be set in PROPERTIES, we can check [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) for a detailed introduction.
+In the `PROPERTIES` section at the last of the CREATE TABLE statement, you can set the relevant parameters. Please see [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) for a detailed introduction.
### ENGINE
-In this example, the type of ENGINE is olap, the default ENGINE type. In Doris, only this ENGINE type is managed and stored by Doris. Other ENGINE types, such as mysql, broker, es, etc., are essentially mappings to tables in other external databases or systems to ensure that Doris can read the data. And Doris itself does not create, manage, and store any tables and data of a non-olap ENGINE type.
+In this example, the ENGINE is of OLAP type, which is the default ENGINE type. In Doris, only the OALP ENGINE type is managed and stored by Doris. Other ENGINE types, such as MySQL, Broker, ES, are essentially mappings to tables in other external databases or systems to ensure that Doris can read the data. And Doris itself does not create, manage, or store any tables and data of non-OLAP ENGINE type.
### Other
-`IF NOT EXISTS` indicates that if the table has not been created, it is created. Note that only the table name is judged here, and it is not determined whether the new table structure is the same as the existing table structure. So if there is a table with the same name but different structure, the command will also return success, but it does not mean that a new table and a new structure have been created.
+`IF NOT EXISTS` means to create the table if it is non-existent. Note that the system only checks the existence of table based on the table name, but not compare the schema of the newly created table with the existing ones. So if there exists a table of the same name but different schema, the command will also return, but it does not mean that a new table of a new schema has been created.
-## common problem
+## FAQ
-### Build Table Operations FAQ
+### Table Creation
-1. If a syntax error occurs in a long build statement, a syntax error may be incomplete. Here is a list of possible syntax errors for manual error correction:
+1. If a syntax error occurs in a long CREATE TABLE statement, the error message may be incomplete. Here is a list of possible syntax errors for your reference in manual touble shooting:
- * The syntax is incorrect. Please read `HELP CREATE TABLE;` carefully to check the relevant syntax structure.
- * Reserved words. When the user-defined name encounters a reserved word, it needs to be enclosed in the backquote ``. It is recommended that all custom names be generated using this symbol.
- * Chinese characters or full-width characters. Non-utf8 encoded Chinese characters, or hidden full-width characters (spaces, punctuation, etc.) can cause syntax errors. It is recommended to check with a text editor with invisible characters.
+ * Incorrect syntax. Please use `HELP CREATE TABLE;`to check the relevant syntax.
+ * Reserved words. Reserved words in user-defined names should be enclosed in backquotes ``. It is recommended that all user-defined names be enclosed in backquotes.
+ * Chinese characters or full-width characters. Non-UTF8 encoded Chinese characters, or hidden full-width characters (spaces, punctuation, etc.) can cause syntax errors. It is recommended that you check for these characters using a text editor that can display non-printable characters.
2. `Failed to create partition [xxx] . Timeout`
- Doris builds are created in order of Partition granularity. This error may be reported when a Partition creation fails. Even if you don't use Partition, you will report `Failed to create partition` when there is a problem with the built table, because as mentioned earlier, Doris will create an unchangeable default Partition for tables that do not have a Partition specified.
+ In Doris, tables are created in the order of the partitioning granularity. This error prompt may appear when a partition creation task fails, but it could also appear in table creation tasks with no partitioning operations, because, as mentioned earlier, Doris will create an unmodifiable default partition for tables with no partitions specified.
- When this error is encountered, it is usually the BE that has encountered problems creating data fragments. You can follow the steps below to troubleshoot:
+ This error usually pops up because the tablet creation goes wrong in BE. You can follow the steps below for troubleshooting:
- 1. In fe.log, find the `Failed to create partition` log for the corresponding point in time. In this log, a series of numbers like `{10001-10010}` will appear. The first number of the pair is the Backend ID and the second number is the Tablet ID. As for the pair of numbers above, on the Backend with ID 10001, creating a tablet with ID 10010 failed.
- 2. Go to the be.INFO log corresponding to Backend and find the log related to the tablet id in the corresponding time period. You can find the error message.
- 3. Listed below are some common tablet creation failure errors, including but not limited to:
- * BE did not receive the relevant task, and the tablet id related log could not be found in be.INFO. Or the BE is created successfully, but the report fails. For the above questions, see [Deployment and Upgrade Documentation] to check the connectivity of FE and BE.
- * Pre-allocated memory failed. It may be that the length of a line in a row in the table exceeds 100KB.
- * `Too many open files`. The number of open file handles exceeds the Linux system limit. The handle limit of the Linux system needs to be modified.
+ 1. In fe.log, find the `Failed to create partition` log of the corresponding time point. In that log, find a number pair that looks like `{10001-10010}` . The first number of the pair is the Backend ID and the second number is the Tablet ID. As for `{10001-10010}`, it means that on Backend ID 10001, the creation of Tablet ID 10010 failed.
+ 2. After finding the target Backend, go to the corresponding be.INFO log and find the log of the target tablet, and then check the error message.
+ 3. A few common tablet creation failures include but not limited to:
+ * The task is not received by BE. In this case, the tablet ID related information will be found in be.INFO, or the creation is successful in BE but it still reports a failure. To solve the above problems, see [Installation and Deployment](https://doris.apache.org/docs/dev/install/install-deploy/) about how to check the connectivity of FE and BE.
+ * Pre-allocated memory failure. It may be that the length of a row in the table exceeds 100KB.
+ * `Too many open files`. The number of open file descriptors exceeds the Linux system limit. In this case, you need to change the open file descriptor limit of the Linux system.
- You can also extend the timeout by setting `tablet_create_timeout_second=xxx` in fe.conf. The default is 2 seconds.
+ If it is a timeout error, you can set `tablet_create_timeout_second=xxx` and `max_create_table_timeout_second=xxx` in fe.conf. The default value of `tablet_create_timeout_second=xxx` is 1 second, and that of `max_create_table_timeout_second=xxx` is 60 seconds. The overall timeout would be min(tablet_create_timeout_second * replication_num, max_create_table_timeout_second). For detailed parameter settings, please check [FE Configuration](https://doris.apache.org/docs/dev/admin-manual/ [...]
3. The build table command does not return results for a long time.
- Doris's table creation command is a synchronous command. The timeout of this command is currently set to be relatively simple, ie (tablet num * replication num) seconds. If you create more data fragments and have fragment creation failed, it may cause an error to be returned after waiting for a long timeout.
+ Doris's table creation command is a synchronous command. The timeout of this command is currently set to be relatively simple, ie (tablet num * replication num) seconds. If you create more data fragments and have fragment creation failed, it may cause an error to be returned after waiting for a long timeout.
- Under normal circumstances, the statement will return in a few seconds or ten seconds. If it is more than one minute, it is recommended to cancel this operation directly and go to the FE or BE log to view the related errors.
+ Under normal circumstances, the statement will return in a few seconds or ten seconds. If it is more than one minute, it is recommended to cancel this operation directly and go to the FE or BE log to view the related errors.
-## More help
+## More Help
-For more detailed instructions on data partitioning, we can refer to the [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) command manual, and also You can enter `HELP CREATE TABLE;` under the Mysql client to get more help information.
+For more detailed instructions on data partitioning, please refer to the [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md) command manual, or enter `HELP CREATE TABLE;` in MySQL Client.
diff --git a/docs/en/docs/data-table/hit-the-rollup.md b/docs/en/docs/data-table/hit-the-rollup.md
index 9461696538..c3b47f1bd1 100644
--- a/docs/en/docs/data-table/hit-the-rollup.md
+++ b/docs/en/docs/data-table/hit-the-rollup.md
@@ -24,17 +24,17 @@ specific language governing permissions and limitations
under the License.
-->
-# Rollup and query
+# Rollup and Query
-ROLLUP in multidimensional analysis means "scroll up", which means that data is aggregated further at a specified granularity.
+In multidimensional analysis, Rollups refers to data tables that are aggregated at a specified granular level.
-### Basic concepts
+### Basic Concepts
-In Doris, we make the table created by the user through the table building statement a Base table. Base table holds the basic data stored in the way specified by the user's table-building statement.
+In Doris, tables that are created by users via the CREATE TABLE statement are called "Base Tables". Base Tables contains the basic data stored in the way specified by the user in the the CREATE TABLE statement.
-On top of the Base table, we can create any number of ROLLUP tables. These ROLLUP data are generated based on the Base table and physically **stored independently**.
+On top of Base Tables, you can create any number of Rollups. Data in Rollups are generated based on the Base Tables and are physically **independent in storage**.
-The basic function of ROLLUP tables is to obtain coarser aggregated data on the basis of Base tables.
+Rollups are created to aggregated versions of Base Tables.
Let's illustrate the ROLLUP tables and their roles in different data models with examples.
diff --git a/docs/en/docs/install/source-install/compilation-with-ldb-toolchain.md b/docs/en/docs/install/source-install/compilation-with-ldb-toolchain.md
index 7ef3a6303e..842ce1c765 100644
--- a/docs/en/docs/install/source-install/compilation-with-ldb-toolchain.md
+++ b/docs/en/docs/install/source-install/compilation-with-ldb-toolchain.md
@@ -126,7 +126,7 @@ If supported, execute `sh build.sh` directly.
This script will compile the third-party libraries first and then the Doris components (FE, BE) later. The compiled output will be in the `output/` directory.
-## Precompile the three-party binaries
+## Precompile the Third-Party Binaries
The `build.sh` script will first compile the third-party dependencies. You can also directly download the precompiled three-party binaries:
diff --git a/docs/en/docs/lakehouse/multi-catalog/multi-catalog.md b/docs/en/docs/lakehouse/multi-catalog/multi-catalog.md
index 21acd73046..5118a62509 100644
--- a/docs/en/docs/lakehouse/multi-catalog/multi-catalog.md
+++ b/docs/en/docs/lakehouse/multi-catalog/multi-catalog.md
@@ -27,4 +27,334 @@ under the License.
# Multi Catalog
-TODO: translate
+<version since="1.2.0">
+
+Multi-Catalog is a newly added feature in Doris 1.2.0. It allows Doris to interface with external catalogs more conveniently and thus increases the data lake analysis and federated query capabilities of Doris.
+
+In older versions of Doris, user data is in a two-tiered structure: database and table. Thus, connections to external catalogs could only be done at the database or table level. For example, users could create a mapping to a table in an external catalog via `create external table`, or to a database via `create external database` . If there were large amounts of databases or tables in the external catalog, users would need to create mappings to them one by one, which could be a heavy workload.
+
+With the advent of Multi-Catalog, Doris now has a new three-tiered metadata hierarchy (catalog -> database -> table), which means users can connect to external data at the catalog level. The currently supported external catalogs include:
+
+1. Hive
+2. Iceberg
+3. Hudi
+4. Elasticsearch
+5. JDBC
+
+Multi-Catalog works as an additional and enhanced external table connection method. It helps users conduct multi-catalog federated queries quickly.
+
+</version>
+
+## Basic Concepts
+
+1. Internal Catalog
+
+ Existing databases and tables in Doris are all under the Internal Catalog, which is the default catalog in Doris and cannot be modified or deleted.
+
+2. External Catalog
+
+ Users can create an External Catalog using the [CREATE CATALOG](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG/) command, and view the existing Catalogs via the [SHOW CATALOGS](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Show-Statements/SHOW-CATALOGS/) command.
+
+3. Switch Catalog
+
+ After login, you will enter the Internal Catalog by default. Then, you can view or switch to your target database via `SHOW DATABASES` and `USE DB` .
+
+ Example of switching catalog:
+
+ ```
+ SWITCH internal;
+ SWITCH hive_catalog;
+ ```
+
+ After switching catalog, you can view or switch to your target database in that catalog via `SHOW DATABASES` and `USE DB` . You can view and access data in External Catalogs the same way as doing that in the Internal Catalog.
+
+ Doris only supports read-only access to data in External Catalogs currently.
+
+4. Delete Catalog
+
+ Databases and tables in External Catalogs are for read only, but External Catalogs are deletable via the [DROP CATALOG](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Data-Definition-Statements/Drop/DROP-CATALOG/) command. (The Internal Catalog cannot be deleted.)
+
+ The deletion only means to remove the mapping in Doris to the corresponding catalog. It doesn't change the external catalog itself by all means.
+
+5. Resource
+
+ Resource is a set of configurations. Users can create a Resource using the [CREATE RESOURCE](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE/) command, and then apply this Resource for a newly created Catalog. One Resource can be reused for multiple Catalogs.
+
+## Examples
+
+### Connect to Hive
+
+The followings are the instruction on how to connect to a Hive catalog using the Catalog feature.
+
+For more information about Hive, please see [Hive](./hive).
+
+1. Create Catalog
+
+ ```sql
+ CREATE CATALOG hive PROPERTIES (
+ 'type'='hms',
+ 'hive.metastore.uris' = 'thrift://172.21.0.1:7004'
+ );
+ ```
+
+ > Syntax Help: [CREATE CATALOG](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG/)
+
+2. View Catalog
+
+ View existing Catalogs via the `SHOW CATALOGS` command:
+
+ ```
+ mysql> SHOW CATALOGS;
+ +-----------+-------------+----------+
+ | CatalogId | CatalogName | Type |
+ +-----------+-------------+----------+
+ | 10024 | hive | hms |
+ | 0 | internal | internal |
+ +-----------+-------------+----------+
+ ```
+
+ > Syntax Help: [SHOW CATALOGS](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Show-Statements/SHOW-CATALOGS/)
+
+ > You can view the CREATE CATALOG statement via [SHOW CREATE CATALOG](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Show-Statements/SHOW-CREATE-CATALOG/).
+
+ > You can modify the Catalog PROPERTIES via [ALTER CATALOG](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-CATALOG/).
+
+3. Switch Catalog
+
+ Switch to the Hive Catalog using the `SWITCH` command, and view the databases in it:
+
+ ```
+ mysql> SWITCH hive;
+ Query OK, 0 rows affected (0.00 sec)
+
+ mysql> SHOW DATABASES;
+ +-----------+
+ | Database |
+ +-----------+
+ | default |
+ | random |
+ | ssb100 |
+ | tpch1 |
+ | tpch100 |
+ | tpch1_orc |
+ +-----------+
+ ```
+
+ > Syntax Help: [SWITCH](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Utility-Statements/SWITCH/)
+
+4. Use the Catalog
+
+ After switching to the Hive Catalog, you can use the relevant features.
+
+ For example, you can switch to Database tpch100, and view the tables in it:
+
+ ```
+ mysql> USE tpch100;
+ Database changed
+
+ mysql> SHOW TABLES;
+ +-------------------+
+ | Tables_in_tpch100 |
+ +-------------------+
+ | customer |
+ | lineitem |
+ | nation |
+ | orders |
+ | part |
+ | partsupp |
+ | region |
+ | supplier |
+ +-------------------+
+ ```
+
+ You can view the schema of Table lineitem:
+
+ ```
+ mysql> DESC lineitem;
+ +-----------------+---------------+------+------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-----------------+---------------+------+------+---------+-------+
+ | l_shipdate | DATE | Yes | true | NULL | |
+ | l_orderkey | BIGINT | Yes | true | NULL | |
+ | l_linenumber | INT | Yes | true | NULL | |
+ | l_partkey | INT | Yes | true | NULL | |
+ | l_suppkey | INT | Yes | true | NULL | |
+ | l_quantity | DECIMAL(15,2) | Yes | true | NULL | |
+ | l_extendedprice | DECIMAL(15,2) | Yes | true | NULL | |
+ | l_discount | DECIMAL(15,2) | Yes | true | NULL | |
+ | l_tax | DECIMAL(15,2) | Yes | true | NULL | |
+ | l_returnflag | TEXT | Yes | true | NULL | |
+ | l_linestatus | TEXT | Yes | true | NULL | |
+ | l_commitdate | DATE | Yes | true | NULL | |
+ | l_receiptdate | DATE | Yes | true | NULL | |
+ | l_shipinstruct | TEXT | Yes | true | NULL | |
+ | l_shipmode | TEXT | Yes | true | NULL | |
+ | l_comment | TEXT | Yes | true | NULL | |
+ +-----------------+---------------+------+------+---------+-------+
+ ```
+
+ You can perform a query:
+
+ ```
+ mysql> SELECT l_shipdate, l_orderkey, l_partkey FROM lineitem limit 10;
+ +------------+------------+-----------+
+ | l_shipdate | l_orderkey | l_partkey |
+ +------------+------------+-----------+
+ | 1998-01-21 | 66374304 | 270146 |
+ | 1997-11-17 | 66374304 | 340557 |
+ | 1997-06-17 | 66374400 | 6839498 |
+ | 1997-08-21 | 66374400 | 11436870 |
+ | 1997-08-07 | 66374400 | 19473325 |
+ | 1997-06-16 | 66374400 | 8157699 |
+ | 1998-09-21 | 66374496 | 19892278 |
+ | 1998-08-07 | 66374496 | 9509408 |
+ | 1998-10-27 | 66374496 | 4608731 |
+ | 1998-07-14 | 66374592 | 13555929 |
+ +------------+------------+-----------+
+ ```
+
+ Or you can conduct a join query:
+
+ ```
+ mysql> SELECT l.l_shipdate FROM hive.tpch100.lineitem l WHERE l.l_partkey IN (SELECT p_partkey FROM internal.db1.part) LIMIT 10;
+ +------------+
+ | l_shipdate |
+ +------------+
+ | 1993-02-16 |
+ | 1995-06-26 |
+ | 1995-08-19 |
+ | 1992-07-23 |
+ | 1998-05-23 |
+ | 1997-07-12 |
+ | 1994-03-06 |
+ | 1996-02-07 |
+ | 1997-06-01 |
+ | 1996-08-23 |
+ +------------+
+ ```
+
+ The table is identified in the format of `catalog.database.table` . For example, `internal.db1.part` in the above snippet.
+
+ If the target table is in the current Database of the current Catalog, `catalog` and `database` in the format can be omitted.
+
+ You can use the `INSERT INTO` command to insert table data from the Hive Catalog into a table in the Internal Catalog. This is how you can **import data from External Catalogs to the Internal Catalog**:
+
+ ```
+ mysql> SWITCH internal;
+ Query OK, 0 rows affected (0.00 sec)
+
+ mysql> USE db1;
+ Database changed
+
+ mysql> INSERT INTO part SELECT * FROM hive.tpch100.part limit 1000;
+ Query OK, 1000 rows affected (0.28 sec)
+ {'label':'insert_212f67420c6444d5_9bfc184bf2e7edb8', 'status':'VISIBLE', 'txnId':'4'}
+ ```
+
+### Connect to Iceberg
+
+See [Iceberg](./iceberg)
+
+### Connect to Hudi
+
+See [Hudi](./hudi)
+
+### Connect to Elasticsearch
+
+See [Elasticsearch](./elasticsearch)
+
+### Connect to JDBC
+
+See [JDBC](./jdbc)
+
+## Column Type Mapping
+
+After you create a Catalog, Doris will automatically synchronize the databases and tables from the corresponding external catalog to it. The following shows how Doris maps different types of catalogs and tables.
+
+<version since="dev">
+
+As for types that cannot be mapped to a Doris column type, such as `map` and `struct` , Doris will map them to an UNSUPPORTED type. Here are examples of queries in a table containing UNSUPPORTED types:
+
+Suppose the table is of the following schema:
+
+```
+k1 INT,
+k2 INT,
+k3 UNSUPPORTED,
+k4 INT
+```
+
+```
+select * from table; // Error: Unsupported type 'UNSUPPORTED_TYPE' in '`k3`
+select * except(k3) from table; // Query OK.
+select k1, k3 from table; // Error: Unsupported type 'UNSUPPORTED_TYPE' in '`k3`
+select k1, k4 from table; // Query OK.
+```
+
+</version>
+
+You can find more details of the mapping of various data sources (Hive, Iceberg, Hudi, Elasticsearch, and JDBC) in the corresponding pages.
+
+## Privilege Management
+
+Access from Doris to databases and tables in an External Catalog is not under the privilege control of the external catalog itself, but is authorized by Doris.
+
+Along with the new Multi-Catalog feature, we also added privilege management at the Catalog level (See [Privilege Management](https://doris.apache.org/docs/dev/admin-manual/privilege-ldap/user-privilege/) for details).
+
+## Metadata Update
+
+### Manual Update
+
+By default, changes in metadata of external data sources, including addition or deletion of tables and columns, will not be synchronized into Doris.
+
+Users need to manually update the metadata using the [REFRESH CATALOG](https://doris.apache.org/docs/dev/sql-manual/sql-reference/Utility-Statements/REFRESH/) command.
+
+### Automatic Update
+
+<version since="dev">
+
+Currently, Doris only supports automatic update of metadata in Hive Metastore (HMS). It perceives changes in metadata by the FE node which regularly reads the notification events from HMS. The supported events are as follows:
+
+</version>
+
+| Event | Corresponding Update Operation |
+| :-------------- | :----------------------------------------------------------- |
+| CREATE DATABASE | Create a database in the corresponding catalog. |
+| DROP DATABASE | Delete a database in the corresponding catalog. |
+| ALTER DATABASE | Such alterations mainly include changes in properties, comments, or storage location of databases. They do not affect Doris' queries in External Catalogs so they will not be synchronized. |
+| CREATE TABLE | Create a table in the corresponding database. |
+| DROP TABLE | Delete a table in the corresponding database, and invalidate the cache of that table. |
+| ALTER TABLE | If it is a renaming, delete the table of the old name, and then create a new table with the new name; otherwise, invalidate the cache of that table. |
+| ADD PARTITION | Add a partition to the cached partition list of the corresponding table. |
+| DROP PARTITION | Delete a partition from the cached partition list of the corresponding table, and invalidate the cache of that partition. |
+| ALTER PARTITION | If it is a renaming, delete the partition of the old name, and then create a new partition with the new name; otherwise, invalidate the cache of that partition. |
+
+> After data ingestion, changes in partition tables will follow the `ALTER PARTITION` logic, while those in non-partition tables will follow the `ALTER TABLE` logic.
+>
+> If changes are conducted on the file system directly instead of through the HMS, the HMS will not generate an event. As a result, such changes will not be perceived by Doris.
+
+The automatic update feature involves the following parameters in fe.conf:
+
+1. `enable_hms_events_incremental_sync`: This specifies whether to enable automatic incremental synchronization for metadata, which is disabled by default.
+2. `hms_events_polling_interval_ms`: This specifies the interval between two readings, which is set to 10000 by default. (Unit: millisecond)
+3. `hms_events_batch_size_per_rpc`: This specifies the maximum number of events that are read at a time, which is set to 500 by default.
+
+To enable automatic update, you need to modify the hive-site.xml of HMS and then restart HMS:
+
+```
+<property>
+ <name>hive.metastore.event.db.notification.api.auth</name>
+ <value>false</value>
+</property>
+<property>
+ <name>hive.metastore.dml.events</name>
+ <value>true</value>
+</property>
+<property>
+ <name>hive.metastore.transactional.event.listeners</name>
+ <value>org.apache.hive.hcatalog.listener.DbNotificationListener</value>
+</property>
+
+```
+
+> Note: To enable automatic update, whether for existing Catalogs or newly created Catalogs, all you need is to set `enable_hms_events_incremental_sync` to `true`, and then restart the FE node. You don't need to manually update the metadata before or after the restart.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org