You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by ji...@apache.org on 2022/08/24 06:37:48 UTC
[doris-website] branch master updated: fix get-starting,data-model
This is an automated email from the ASF dual-hosted git repository.
jiafengzheng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new f3fcfb02a1d fix get-starting,data-model
f3fcfb02a1d is described below
commit f3fcfb02a1d98e23d452b6fa3a906050e822afd8
Author: jiafeng.zhang <zh...@gmail.com>
AuthorDate: Wed Aug 24 14:08:33 2022 +0800
fix get-starting,data-model
---
docs/data-table/basic-usage.md | 264 ++++++-
docs/data-table/best-practice.md | 33 +-
docs/data-table/data-model.md | 41 +-
docs/data-table/data-partition.md | 419 +++++-----
docs/data-table/index/bloomfilter.md | 8 +-
docs/data-table/index/prefix-index.md | 22 +-
docs/get-starting/get-starting.md | 867 +++++++--------------
.../current/data-table/basic-usage.md | 280 ++++++-
.../current/data-table/best-practice.md | 32 +-
.../current/data-table/data-model.md | 13 +-
.../current/data-table/index/bloomfilter.md | 6 +-
.../current/data-table/index/prefix-index.md | 16 +-
.../current/get-starting/get-starting.md | 862 +++++++-------------
sidebars.json | 1 -
static/images/image-20220822091951739.png | Bin 0 -> 1094798 bytes
15 files changed, 1376 insertions(+), 1488 deletions(-)
diff --git a/docs/data-table/basic-usage.md b/docs/data-table/basic-usage.md
index e35ff93730c..e24d211779f 100644
--- a/docs/data-table/basic-usage.md
+++ b/docs/data-table/basic-usage.md
@@ -25,7 +25,7 @@ under the License.
-->
-# Guidelines for Basic Use
+# Use 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.
@@ -403,3 +403,265 @@ MySQL> SELECT SUM(pv) FROM table2 WHERE siteid IN (SELECT siteid FROM table1 WHE
+-----------+
1 row in set (0.13 sec)
```
+
+## Table Structure Change
+
+Use the [ALTER TABLE COLUMN](. /sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md) command to modify the table Schema, including the following changes.
+
+- Adding columns
+- Deleting columns
+- Modify column types
+- Changing the order of columns
+
+The following table structure changes are illustrated by using the following example.
+
+The Schema for the original table1 is as follows:
+
+```text
++----------+-------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++----------+-------------+------+-------+---------+-------+
+| siteid | int(11) | No | true | 10 | |
+| citycode | smallint(6) | No | true | N/A | |
+| username | varchar(32) | No | true | | |
+| pv | bigint(20) | No | false | 0 | SUM |
++----------+-------------+------+-------+---------+-------+
+```
+
+We add a new column uv, type BIGINT, aggregation type SUM, default value 0:
+
+```sql
+ALTER TABLE table1 ADD COLUMN uv BIGINT SUM DEFAULT '0' after pv;
+```
+
+After successful submission, you can check the progress of the job with the following command:
+
+```sql
+SHOW ALTER TABLE COLUMN;
+```
+
+When the job status is ``FINISHED``, the job is complete. The new Schema has taken effect.
+
+After ALTER TABLE is completed, you can view the latest Schema via ``DESC TABLE``.
+
+```
+mysql> DESC table1;
++----------+-------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++----------+-------------+------+-------+---------+-------+
+| siteid | int(11) | No | true | 10 | |
+| citycode | smallint(6) | No | true | N/A | |
+| username | varchar(32) | No | true | | |
+| pv | bigint(20) | No | false | 0 | SUM |
+| uv | bigint(20) | No | false | 0 | SUM |
++----------+-------------+------+-------+---------+-------+
+5 rows in set (0.00 sec)
+```
+
+You can cancel the currently executing job with the following command:
+
+```sql
+CANCEL ALTER TABLE COLUMN FROM table1;
+```
+
+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.
+
+Use [ALTER TABLE ROLLUP](... /sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-ROLLUP.md) to perform various changes to Rollup.
+
+The following examples are given
+
+The Schema of the original table1 is as follows:
+
+```
++----------+-------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++----------+-------------+------+-------+---------+-------+
+| siteid | int(11) | No | true | 10 | |
+| citycode | smallint(6) | No | true | N/A | |
+| username | varchar(32) | No | true | | |
+| pv | bigint(20) | No | false | 0 | SUM |
+| uv | bigint(20) | No | false | 0 | SUM |
++----------+-------------+------+-------+---------+-------+
+```
+
+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.
+
+```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.
+
+```sql
+SHOW ALTER TABLE ROLLUP;
+```
+
+When the job 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.
+
+```
+mysql> desc table1 all;
++-------------+----------+-------------+------+-------+--------+-------+
+| IndexName | Field | Type | Null | Key | Default | Extra |
++-------------+----------+-------------+------+-------+---------+-------+
+| table1 | siteid | int(11) | No | true | 10 | |
+| | citycode | smallint(6) | No | true | N/A | |
+| | username | varchar(32) | No | true | | |
+| | pv | bigint(20) | No | false | 0 | SUM |
+| | uv | bigint(20) | No | false | 0 | SUM |
+| | | | | | | |
+| rollup_city | citycode | smallint(6) | No | true | N/A | |
+| | pv | bigint(20) | No | false | 0 | SUM |
++-------------+----------+-------------+------+-------+---------+-------+
+8 rows in set (0.01 sec)
+```
+
+You can cancel the currently executing job with 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.
+
+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.
+
+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.
+
+For more information about materialized views, see [Materialized Views](../advanced/materialized-view)
+
+## Data table queries
+
+### Memory limit
+
+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.
+
+If a user finds a `Memory limit exceeded` error while using it, the memory limit is usually exceeded.
+
+When you encounter a memory limit exceeded, you should try to solve it by optimizing your sql statements.
+
+If you find exactly that 2GB of memory cannot be met, you can set the memory parameters manually.
+
+Show query memory limit:
+
+```sql
+mysql> SHOW VARIABLES LIKE "%mem_limit%";
++---------------+------------+
+| Variable_name | Value |
++---------------+------------+
+| exec_mem_limit| 2147483648 |
++---------------+------------+
+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.
+
+```sql
+mysql> SET exec_mem_limit = 8589934592;
+Query OK, 0 rows affected (0.00 sec)
+mysql> SHOW VARIABLES LIKE "%mem_limit%";
++---------------+------------+
+| Variable_name | Value |
++---------------+------------+
+| exec_mem_limit| 8589934592 |
++---------------+------------+
+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.
+
+### 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).
+
+View the current timeout settings:
+
+```sql
+mysql> SHOW VARIABLES LIKE "%query_timeout%";
++---------------+-------+
+| Variable_name | Value |
++---------------+-------+
+| QUERY_TIMEOUT | 300 |
++---------------+-------+
+1 row in set (0.00 sec)
+```
+
+Modify the 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`.
+
+### 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.
+
+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.
+
+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 also switch to a Shuffle Join.
+
+Use Broadcast Join (default):
+
+```sql
+mysql> select sum(table1.pv) from table1 join table2 where table1.siteid = 2;
++--------------------+
+| sum(`table1`.`pv`) |
++--------------------+
+| 10 |
++--------------------+
+1 row in set (0.20 sec)
+```
+
+Use Broadcast Join (explicitly specified):
+
+```sql
+mysql> select sum(table1.pv) from table1 join [broadcast] table2 where table1.siteid = 2;
++--------------------+
+| sum(`table1`.`pv`) |
++--------------------+
+| 10 |
++--------------------+
+1 row in set (0.20 sec)
+```
+
+Use Shuffle Join:
+
+```sql
+mysql> select sum(table1.pv) from table1 join [shuffle] table2 where table1.siteid = 2;
++--------------------+
+| sum(`table1`.`pv`) |
++--------------------+
+| 10 |
++--------------------+
+1 row in set (0.15 sec)
+```
+
+### Query Retry and High Availability
+
+When deploying multiple FE nodes, you can deploy a load balancing layer on top of multiple FEs to achieve high availability of Doris.
+
+Please refer to [Load Balancing](...) for details on installation, deployment, and usage. /admin-manual/cluster-management/load-balancing)
+
+## Data update and deletion
+
+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.
+
+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.
+
+For specific instructions on delete and update operations, see [Data Update](...). /data-operate/update-delete/update) documentation.
diff --git a/docs/data-table/best-practice.md b/docs/data-table/best-practice.md
index 335acd03684..ca6d87c44b7 100644
--- a/docs/data-table/best-practice.md
+++ b/docs/data-table/best-practice.md
@@ -89,7 +89,7 @@ DISTRIBUTED BY HASH(sessionid, visitorid) BUCKETS 10;
### 1.2 Wide Table vs. Star Schema
-In order to adapt to the front-end business, business side often does not distinguish dimension information from indicator information, but defines Schema as a wide table. For Doris, the performance of such wide gauges is often unsatisfactory:
+When the business side builds tables, in order to adapt to the front-end business, they often do not distinguish between dimension information and indicator information, and define the Schema as a large wide table, this operation is actually not so friendly to the database, we recommend users to use the star model.
* There are many fields in Schema, and there may be more key columns in the aggregation model. The number of columns that need to be sorted in the import process will increase.
* Dimensional information updates are reflected in the whole table, and the frequency of updates directly affects the efficiency of queries.
@@ -170,29 +170,14 @@ In addition to visitorid analysis, there are Brower and province analysis cases,
ALTER TABLE session_data ADD ROLLUP rollup_brower(brower,province,ip,url) DUPLICATE KEY(brower,province);
```
-## 2 Schema Change
+## Schema Change
-There are three Schema Change in doris: Sorted Schema Change, Direct Schema Change, Linked Schema Change.
+Users can modify the Schema of an existing table through the Schema Change operation, currently Doris supports the following modifications:
-2.1. Sorted Schema Change
+- Adding and deleting columns
+- Modify column types
+- Reorder columns
+- Adding or modifying Bloom Filter
+- Adding or removing bitmap index
-The sorting of columns has been changed and the data needs to be reordered. For example, delete a column in a sorted column and reorder the fields.
-
-```
-ALTER TABLE site_visit DROP COLUMN city;
-```
-
-2.2. Direct Schema Change: There is no need to reorder, but there is a need to convert the data. For example, modify
- the type of column, add a column to the sparse index, etc.
-
-```
-ALTER TABLE site_visit MODIFY COLUMN username varchar(64);
-```
-
-2.3. Linked Schema Change: No need to transform data, for example add columns.
-
-```
-ALTER TABLE site_visit ADD COLUMN click bigint SUM default '0';
-```
-
-Schema is recommended to be considered when creating tables so that Schema can be changed more quickly.
+For details, please refer to [Schema Change](
diff --git a/docs/data-table/data-model.md b/docs/data-table/data-model.md
index 662e7f405b3..49299404cae 100644
--- a/docs/data-table/data-model.md
+++ b/docs/data-table/data-model.md
@@ -26,7 +26,7 @@ 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 document describes Doris's data model at the logical level to help users better use Doris to cope with different business scenarios.
## Basic concepts
@@ -38,7 +38,7 @@ Columns can be divided into two categories: Key and Value. From a business persp
Doris's data model is divided into three main categories:
* Aggregate
-* Unique
+* Uniq
* Duplicate
Let's introduce them separately.
@@ -234,9 +234,9 @@ Data aggregation occurs in Doris in the following three stages:
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 [...]
-## Unique Model
+## Uniq Model
-In some multi-dimensional analysis scenarios, users are more concerned with how to ensure the uniqueness of Key, that is, how to obtain the Primary Key uniqueness constraint. Therefore, we introduce Unique data model. This model is essentially a special case of aggregation model and a simplified representation of table structure. Let's give an example.
+In some multi-dimensional analysis scenarios, users are more concerned with how to ensure the uniqueness of Key, that is, how to obtain the Primary Key uniqueness constraint. Therefore, we introduce Uniq's data model. This model is essentially a special case of aggregation model and a simplified representation of table structure. Let's give an example.
|ColumnName|Type|IsKey|Comment|
|---|---|---|---|
@@ -303,7 +303,7 @@ PROPERTIES (
);
```
-That is to say, 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, Uniq 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.
## Duplicate Model
@@ -336,14 +336,14 @@ PROPERTIES (
);
```
-This data model is different from Aggregate and Unique 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](./index/prefix-index.md)) On the choice of DUPLICATE KEY, we recommend that the first 2-4 columns be selected appropriately.
+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.
-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](./data-model.md#limitations-of-aggregation-model) section.
+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.
## Limitations of aggregation model
-Here we introduce the limitations of Aggregate model (including Unique model).
+Here we introduce the limitations of Aggregate model (including Uniq 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.
@@ -385,9 +385,7 @@ We add aggregation operator to query engine to ensure data consistency.
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:
-```
-SELECT MIN(cost) FROM table;
-```
+`SELECT MIN(cost) FROM table;`
The result is 5, not 1.
@@ -395,9 +393,7 @@ At the same time, this consistency guarantee will greatly reduce the query effic
Let's take the most basic count (*) query as an example:
-```
-SELECT COUNT(*) FROM table;
-```
+`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**.
@@ -429,9 +425,9 @@ Because the final aggregation result is:
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.
-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 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.
-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, 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:
|ColumnName|Type|AggregationType|Comment|
|---|---|---|---|
@@ -446,16 +442,13 @@ Another way is to **change the aggregation type of the count column above to REP
### 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.
+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.
## 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**.
-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. Unique 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 (because the essence is REPLACE, there is no such aggregation as SUM).
-
- > **[Note]** 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)
-
+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 (because the essence is REPLACE, there is no such aggregation as SUM).
+ - \[Note\] 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).
diff --git a/docs/data-table/data-partition.md b/docs/data-table/data-partition.md
index 8d4372340b5..441e43cee8e 100644
--- a/docs/data-table/data-partition.md
+++ b/docs/data-table/data-partition.md
@@ -140,200 +140,200 @@ When defining columns, you can refer to the following suggestions:
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.
-It is also possible to use only one layer of partitioning. When using a layer partition, only Bucket partitioning is supported.Let's introduce the partitions and buckets separately:
-
-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**
-
- - The partition column is usually a time column to facilitate the 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:
-
- ```text
- 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:
-
- ```text
- 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:
-
- ```text
- 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:
-
- ```text
- 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:
-
- ```text
- 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:
-
- ```text
- 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:
+It is also possible to use only one layer of partitioning. When using a layer partition, only Bucket partitioning is supported.
+
+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:
```text
- PARTITION BY RANGE(`date`, `id`)
- (
- PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"),
- PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"),
- 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:
-
- ```
- * 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:
-
- ```
- * 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
- ```
-
- **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.
-
- ```text
- 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:
-
- ```text
- 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:
-
- ```text
- 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"))
- )
- ```
-
- 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:
-
- ```
- * p1_city: [("1", "Beijing"), ("1", "Shanghai")]
- * p2_city: [("2", "Beijing"), ("2", "Shanghai")]
- * p3_city: [("3", "Beijing"), ("3", "Shanghai")]
- ```
-
- 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.
-
- ```
- * Data ---> Partition
- * 1, Beijing ---> p1_city
- * 1, Shanghai ---> p1_city
- * 2, Shanghai ---> p2_city
- * 3, Beijing ---> p3_city
- * 1, Tianjin ---> 无法导入
- * 4, Beijing ---> 无法导入
- ```
-
-2. **Bucket**
-
- - 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, but it must be a Key column. 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**:
- 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.
-
-3. **Recommendations on the number and amount of data for Partitions and Buckets.**
-
- - 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 r [...]
- - 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.
+ PARTITION BY RANGE(`date`, `id`)
+ (
+ PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"),
+ PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"),
+ 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:
+
+ ```
+ *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:
+
+ ```
+ * 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
+ ```
+
+ #### 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"))
+ )
+ ```
+
+ 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.
+
+ ```
+ * p1_city: [("1", "Beijing"), ("1", "Shanghai")]
+ * p2_city: [("2", "Beijing"), ("2", "Shanghai")]
+ * p3_city: [("3", "Beijing"), ("3", "Shanghai")]
+ ```
+
+ 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.
+
+ ```
+ * 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. Bucket
+
+ * 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, but it must be a Key column. 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**:
+
+ 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.
+
+3. Recommendations on the number and amount of data for Partitions and Buckets.
+
+ * 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.
+
#### Compound Partitions vs Single Partitions
@@ -355,44 +355,43 @@ The user can also use a single partition without using composite partitions. The
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.
### 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.
-### 其他
+### Other
-```text
`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.
-```
## common problem
-###Build Table Operations FAQ
+### Build Table Operations FAQ
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:
- - 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.
+ * 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.
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.
+ 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.
- 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:
+ 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:
- - 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.
- - 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.
- - 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](../install/install-deploy.md) 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 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.
- If there is a timeout when creating a data shard, you can also extend the timeout by setting `tablet_create_timeout_second=xxx` and `max_create_table_timeout_second=xxx` in fe.conf. Among them, `tablet_create_timeout_second` defaults to 1 second, `max_create_table_timeout_second` defaults to 60 seconds, and the overall timeout time is min(tablet_create_timeout_second * replication_num, max_create_table_timeout_second). For specific parameter settings, please refer to [fe-config](../ad [...]
+ You can also extend the timeout by setting `tablet_create_timeout_second=xxx` in fe.conf. The default is 2 seconds.
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
diff --git a/docs/data-table/index/bloomfilter.md b/docs/data-table/index/bloomfilter.md
index eaf92303b66..e63fc5fd418 100644
--- a/docs/data-table/index/bloomfilter.md
+++ b/docs/data-table/index/bloomfilter.md
@@ -1,6 +1,6 @@
---
{
- "title": "BloomFilter Index",
+ "title": "BloomFilter index",
"language": "en"
}
---
@@ -96,7 +96,7 @@ PROPERTIES (
Check that the BloomFilter index we built on the table is to use:
```sql
-SHOW CREATE TABLE <table_name>
+SHOW CREATE TABLE <table_name>;
```
### Delete BloomFilter index
@@ -120,15 +120,11 @@ ALTER TABLE <db.table_name> SET ("bloom_filter_columns" = "k1,k3");
You can consider establishing a Bloom Filter index for a column when the following conditions are met:
1. First, BloomFilter is suitable for non-prefix filtering.
-
2. The query will be filtered according to the high frequency of the column, and most of the query conditions are in and = filtering.
-
3. Unlike Bitmap, BloomFilter is suitable for high cardinality columns. Such as UserID. Because if it is created on a low-cardinality column, such as a "gender" column, each Block will almost contain all values, causing the BloomFilter index to lose its meaning.
### **Doris BloomFilter use precautions**
1. It does not support the creation of Bloom Filter indexes for Tinyint, Float, and Double columns.
-
2. The Bloom Filter index only has an acceleration effect on in and = filtering queries.
-
3. If you want to check whether a query hits the Bloom Filter index, you can check the profile information of the query.
diff --git a/docs/data-table/index/prefix-index.md b/docs/data-table/index/prefix-index.md
index eb4df24cc2f..89a5c174573 100644
--- a/docs/data-table/index/prefix-index.md
+++ b/docs/data-table/index/prefix-index.md
@@ -1,6 +1,6 @@
---
{
- "title": "Prefix Index",
+ "title": "Index",
"language": "en"
}
---
@@ -24,17 +24,23 @@ specific language governing permissions and limitations
under the License.
-->
-# Prefix Index
+# Index
-## Basic concept
+Indexes are used to help quickly filter or find data.
-Unlike traditional database designs, Doris does not support creating indexes on arbitrary columns. OLAP databases with MPP architectures such as Doris usually process large amounts of data by improving concurrency.
+Doris currently supports two main types of indexes: built-in smart indexes, including prefix indexes and ZoneMap indexes. User-created secondary indexes, including the [Bloom Filter index](. /bloomfilter) and [Bitmap inverted index](. /bitmap-index).
-Essentially, Doris' data is stored in a data structure similar to SSTable (Sorted String Table). This structure is an ordered data structure that can be sorted and stored according to the specified column. In this data structure, it is very efficient to use the sorted column as a condition to search.
+The ZoneMap index is the index information automatically maintained for each column in the column storage format, including Min/Max, the number of Null values, and so on. This index is transparent to the user.
-In the three data models of Aggregate, Unique and Duplicate. The underlying data storage is sorted and stored according to the columns specified in the AGGREGATE KEY, UNIQUE KEY and DUPLICATE KEY in the respective table creation statements.
+## Prefix Index
-The prefix index, that is, on the basis of sorting, implements an index method to quickly query data according to a given prefix column.
+Unlike traditional database designs, Doris does not support creating indexes on arbitrary columns. an OLAP database with an MPP architecture like Doris is typically designed to handle large amounts of data by increasing concurrency.
+
+Essentially, Doris data is stored in a data structure similar to an SSTable (Sorted String Table). This structure is an ordered data structure that can be stored sorted by specified columns. On this data structure, it will be very efficient to perform lookups with sorted columns as a condition.
+
+In the Aggregate, Unique and Duplicate data models. The underlying data storage is sorted and stored according to the columns specified in the respective table building statements, AGGREGATE KEY, UNIQUE KEY and DUPLICATE KEY.
+
+The prefix index, which is based on sorting, is an indexing method to query data quickly based on a given prefix column.
## Example
@@ -76,4 +82,4 @@ Therefore, when building a table, choosing the correct column order can greatly
## Adjust prefix index by ROLLUP
-Because the column order has been specified when the table is created, there is only one prefix index for a table. This may not be efficient for queries that use other columns that cannot hit the prefix index as conditions. Therefore, we can artificially adjust the column order by creating a ROLLUP. For details, please refer to [ROLLUP](../hit-the-rollup.md).
\ No newline at end of file
+Because the column order has been specified when the table is created, there is only one prefix index for a table. This may not be efficient for queries that use other columns that cannot hit the prefix index as conditions. Therefore, we can artificially adjust the column order by creating a ROLLUP. For details, please refer to [ROLLUP](../hit-the-rollup.md).
diff --git a/docs/get-starting/get-starting.md b/docs/get-starting/get-starting.md
index 731c66497f4..16410962d32 100644
--- a/docs/get-starting/get-starting.md
+++ b/docs/get-starting/get-starting.md
@@ -25,676 +25,375 @@ specific language governing permissions and limitations
under the License.
-->
-# Getting Started
-
-## Environmental preparation
-
-1. CPU: 2C (minimum) 8C (recommended)
-2. Memory: 4G (minimum) 48G (recommended)
-3. Hard disk: 100G (minimum) 400G (recommended)
-4. Platform: MacOS (Intel), LinuxOS, Windows virtual machine
-5. System: CentOS (7.1 and above), Ubuntu (16.04 and above)
-6. Software: JDK (1.8 and above), GCC (4.8.2 and above)
-
-## Stand-alone deployment
-
-**Before creating, please prepare the compiled FE/BE file, this tutorial will not repeat the compilation process.**
-
-
-**This tutorial is a mixed distribution tutorial of single node, 1FE and 1BE, only one node is needed, which is convenient for quickly experiencing Doris.**
-
-1. Set the maximum number of open file handles in the system
-
- ```shell
- vi /etc/security/limits.conf
- # Add the following two lines of information
- * soft nofile 65536
- * hard nofile 65536
- # Save and exit and restart the server
- ```
-
-2. Download binary package / self-compile FE / BE files
-
- ```shell
- # Select the version you want to deploy from the following address
- https://archive.apache.org/dist/doris
- # For example the following link
- wget https://archive.apache.org/dist/doris/1.1/1.1.0-rc05/apache-doris-1.1.0-bin-x86-jdk8.tar.gz
- ```
-
-3. Extract the tar.gz file
+# Quick Start
- ```shell
- tar -zxvf Downloaded binary archive
- # example
- tar -zxvf apache-doris-1.1.0-bin-x86-jdk8.tar.gz
- ```
+Apache Doris is a high-performance, real-time analytic database based on the MPP architecture and is known for its extreme speed and ease of use. It takes only sub-second response times to return query results under massive amounts of data, and can support not only highly concurrent point query scenarios, but also high-throughput complex analytic scenarios. version, install and run it on a single node, including creating databases, data tables, importing data and queries, etc.
-4. Migrate the decompressed program files to the specified directory and rename them
+## Download Doris
- ```shell
- mv [unzipped root directory] [Target path]
- cd [Target path]
- # example
- mv apache-doris-1.1.0-bin-x86-jdk8 /opt/doris
- cd /opt/doris
- ```
-
-5. Configure FE
-
- ```shell
- # Configure FE-Config
- vi fe/conf/fe.conf
- # Uncomment priority_networks and modify parameters
- # For example, if the IP address of the current node is 10.10.2.21, you need to change it to 10.10.2.0/24 and fill in
- # What needs to be filled in here is the IP subnet address, not the IP address
- priority_networks = 10.10.2.0/24
- # save and exit
- ```
-
-6. Configure BE
-
- ```shell
- # Configure BE-Config
- vi be/conf/be.conf
- # Uncomment priority_networks and modify parameters
- # For example, if the IP address of the current node is 10.10.2.21, you need to change it to 10.10.2.0/24 and fill in
- # What needs to be filled in here is the IP subnet address, not the IP address
- priority_networks = 10.10.2.0/24
- # save and exit
- ```
-
-7. Configure environment variables
-
- ```shell
- # Configure environment variables
- vim /etc/profile.d/doris.sh
- export DORIS_HOME=Doris root path # example:/opt/doris
- export PATH=$PATH:$DORIS_HOME/fe/bin:$DORIS_HOME/be/bin
- # save and source
- source /etc/profile.d/doris.sh
- ```
-
-8. Start FE and BE and register BE to FE
-
- ```shell
- start_fe.sh --daemon
- start_be.sh --daemon
- ```
-
- Check whether the FE startup is successful
-
- > 1. Check whether the startup is successful, and whether there is a PaloFe process under the JPS command
- > 2. After the FE process is started, the metadata will be loaded first. Depending on the role of the FE, you will see transfer from UNKNOWN to MASTER/FOLLOWER/OBSERVER in the log. Finally, you will see the thrift server started log, and you can connect to FE through the MySQL client, which means FE started successfully.
- > 3. You can also check whether the startup is successful through the following connection: http://fe_host:fe_http_port/api/bootstrap If it returns: {"status":"OK","msg":"Success"}, it means the startup is successful, and the rest , there may be a problem.
- > 4. Visit http://fe_host:fe_http_port in the external environment to check whether you can access the WebUI interface. The default login account is root and the password is empty.
- >
- > **Note: If you can't see the startup failure information in fe.log, maybe you can see it in fe.out.**
-
- Verify that BE is successfully started
-
- > 1. After the BE process is started, if there is data before, it may take several minutes to load the data index.
- > 2. If it is the first start of a BE, or the BE has not joined any cluster, the BE log will periodically scroll waiting to receive first heartbeat from frontend. Indicates that the BE has not received the Master's address through the heart hop of the FE, and is passively waiting. This kind of error log will disappear after ADD BACKEND in FE and sending a heartbeat. If the words master client, get client from cache failed.host: , port: 0, code: 7 appear repeatedly after receiving the [...]
- > 3. If the BE has been added to the cluster, the heartbeat log from the FE should be scrolled every 5 seconds: get heartbeat, host: xx.xx.xx.xx, port: 9020, cluster id: xxxxxx , Indicates that the heartbeat is normal.
- > 4. Secondly, the words finish report task success. return code: 0 should be scrolled in the log every 10 seconds, indicating that the communication between BE and FE is normal.
- > 5. At the same time, if there is a data query, you should be able to see the log that keeps scrolling, and there is a log of execute time is xxx, indicating that the BE has been started successfully and the query is normal.
- > 6. You can also check whether the startup is successful through the following connection: http://be_host:be_http_port/api/health If it returns: {"status": "OK","msg": "To Be Added"}, it means the startup is successful, In other cases, there may be problems.
- >
- > **Note: If you can't see the startup failure information in be.INFO, maybe you can see it in be.out.**
-
- Register BE to FE (using MySQL-Client, you need to install it yourself)
-
- ```shell
- # login,Since it is a single node mixed distribution, FE_IP and BE_IP are the same IP address
- mysql -h FE_IP -P 9030 -uroot
- # Register BE
- ALTER SYSTEM ADD BACKEND "BE_IP:9050";
- ```
-
-## Apache Doris is easy to use
-
-Doris uses the MySQL protocol for communication, and users can connect to the Doris cluster through MySQL Client or MySQL JDBC. When choosing the MySQL Client version, it is recommended to use a version after 5.1, because the user name longer than 16 characters cannot be supported before 5.1. Doris SQL syntax basically covers MySQL syntax.
-
-### Apache Doris Web UI access
-
-By default, Http protocol is used for WebUI access, and the following format address is entered in the browser to access
+Doris runs on a Linux environment, CentOS 7.x or Ubuntu 16.04 or higher is recommended, and you need to have a Java runtime environment installed (the minimum JDK version required is 8). To check the version of Java you have installed, run the following command.
```
-http://FE_IP:FE_HTTP_PORT(8030 by default)
+java -version
```
-If the account password is changed during cluster installation, use the new account password to log in
+Next, [download the latest binary version of Doris](https://doris.apache.org/zh-CN/download) and unzip it.
```
-Default account: root
-Default password: empty
+tar zxf apache-doris-x.x.x.tar.gz
```
-1. Introduction to WebUI
-
- On the home page of FE-WEB-UI, Version and Hardware Info are listed
-
- The Tab page has the following six modules:
-
- - Playground (Visual SQL)
-
- - System (system status)
-
- - Log (cluster log)
-
- - QueryProfile (SQL execution log)
-
- - Session (linked list)
-
- - Configuration
+## Configure Doris
-2. View the BE list
+### Configure FE
- Go to `System` --> `backends` to view
+Go to the `apache-doris-x.x.x/fe` directory
- What needs to be paid attention to is the `Alive` column, the `True` and `False` of this column represent the normal and abnormal status of the corresponding BE node
-
-3. Profile query
-
- Enter QueryProfile to view the latest 100 SQL execution report information, click the specified ID in the QueryID column to view the Profile information
+```
+cd apache-doris-x.x.x/fe
+```
-### MySQL command line/graphical client access
+Modify the FE configuration file `conf/fe.conf`, here we mainly modify two parameters: `priority_networks` and `meta_dir`, if you need more optimized configuration, please refer to [FE parameter configuration](... /admin-manual/config/fe-config) for instructions on how to adjust them.
-```shell
-# Command Line
-mysql -h FE-host -P 9030 -u username -p password
-# Client (Navicat as an example)
-Host: FE-host (if it is a cloud server, public IP is required)
-Port: 9030
-username: username
-password password
-````
+1. add priority_networks parameter
-#### Profile settings
+```
+priority_networks=172.23.16.0/24
+```
-FE splits the query plan into fragments and sends them to BE for task execution. When BE executes Fragment, it records **statistical value of running state**, and outputs the statistics of Fragment execution to the log. FE can also collect these statistical values recorded by each Fragment through switches, and print the results on the FE web page.
+>Note:
+>
+>This parameter we have to configure during installation, especially when a machine has multiple IP addresses, we have to specify a unique IP address for FE.
-- Turn on the Report switch of FE
+2. Adding a metadata directory
- ```mysql
- set enable_profile=true;
- ````
+```
+meta_dir=/path/your/doris-meta
+```
-- After executing the SQL statement, you can view the corresponding SQL statement execution report information on the FE's WEB-UI interface
+>Note:
+>
+>Here you can leave it unconfigured, the default is doris-meta in your Doris FE installation directory.
+>
+>To configure the metadata directory separately, you need to create the directory you specify in advance
-For a complete parameter comparison table, please go to [Profile parameter analysis](../admin-manual/query-profile) View Details
+### Start FE
+Execute the following command in the FE installation directory to complete the FE startup.
-#### Library table operations
+```
+./bin/start_fe.sh --daemon
+```
-- View database list
+#### View FE operational status
- ```mysql
- SHOW DATABASES;
- ````
+You can check if Doris started successfully with the following command
-- create database
+```
+curl http://127.0.0.1:8030/api/bootstrap
+```
- ```mysql
- CREATE DATABASE database_name;
- ````
+Here the IP and port are the IP and http_port of FE (default 8030), if you are executing in FE node, just run the above command directly.
- > For more detailed syntax and best practices used by Create-DataBase, see [Create-Database](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-DATABASE) 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 `CREATE DATABASE`, `CREATE TABLE`, `CREATE USER` and other commands
-
- After the database is created, you can view the database information through `SHOW DATABASES;`.
-
- ```mysql
- MySQL> SHOW DATABASES;
- +--------------------+
- | Database |
- +--------------------+
- | example_db |
- | information_schema |
- +--------------------+
- 2 rows in set (0.00 sec)
- ````
-
- `information_schema` exists to be compatible with the MySQL protocol. In practice, the information may not be very accurate, so it is recommended to obtain information about a specific database by directly querying the corresponding database.
-
-- Create data table
+If the return result has the word `"msg": "success"`, then the startup was successful.
- > For more detailed syntax and best practices used by Create-Table, see [Create-Table](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE) command manual.
+You can also check this through the web UI provided by Doris FE by entering the address in your browser
- Use the `CREATE TABLE` command to create a table (Table). More detailed parameters can be viewed:
+http:// fe_ip:8030
- ```mysql
- HELP CREATE TABLE;
- ````
+You can see the following screen, which indicates that the FE has started successfully
- First switch the database:
+
- ```mysql
- USE example_db;
- ````
+>Note:
+>
+>1. Here we use the Doris built-in default user, root, to log in with an empty password.
+>2. This is an administrative interface for Doris, and only users with administrative privileges can log in.
- Doris supports two table creation methods, single partition and composite partition (for details, please refer to [Create-Table](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE) command manual).
+#### Connect FE
- The following takes the aggregation model as an example to demonstrate the table building statements for two partitions.
+We will connect to Doris FE via MySQL client below, download the installation-free [MySQL client](https://doris-build-hk.oss-cn-hongkong.aliyuncs.com/mysql-client/mysql-5.7.22-linux-) glibc2.12-x86_64.tar.gz)
- - single partition
+Unzip the MySQL client you just downloaded and you can find the `mysql` command line tool in the `bin/` directory. Then execute the following command to connect to Doris.
- Create a logical table named table1. The bucketed column is siteid, and the number of buckets is 10.
+```
+mysql -uroot -P9030 -h127.0.0.1
+```
- The schema for this table is as follows:
-
- - siteid: type is INT (4 bytes), default value is 10
- - citycode: type is SMALLINT (2 bytes)
- - username: the type is VARCHAR, the maximum length is 32, the default value is an empty string
- - pv: the type is BIGINT (8 bytes), the default value is 0; this is an indicator column, Doris will perform aggregation operations on the indicator column internally, and the aggregation method of this column is sum (SUM)
-
- The table creation statement is as follows:
-
- ```mysql
- CREATE TABLE table1
- (
- siteid INT DEFAULT '10',
- citycode SMALLINT,
- username VARCHAR(32) DEFAULT '',
- pv BIGINT SUM DEFAULT '0'
- )
- AGGREGATE KEY(siteid, citycode, username)
- DISTRIBUTED BY HASH(siteid) BUCKETS 10
- PROPERTIES("replication_num" = "1");
- ```
-
- - composite partition
-
- Create a logical table named table2.
-
- The schema for this table is as follows:
-
- - event_day: type is DATE, no default value
- - siteid: type is INT (4 bytes), default value is 10
- - citycode: type is SMALLINT (2 bytes)
- - username: the type is VARCHAR, the maximum length is 32, the default value is an empty string
- - pv: The type is BIGINT (8 bytes), the default value is 0; this is an indicator column, Doris will perform aggregation operations on the indicator column internally, and the aggregation method of this column is sum (SUM)
-
- We use the event_day column as the partition column and create 3 partitions: p201706, p201707, p201708
-
- - p201706: Range is [Min, 2017-07-01)
-
- - p201707: The range is [2017-07-01, 2017-08-01)
-
- - p201708: The range is [2017-08-01, 2017-09-01)
-
- > Note that the interval is left closed and right open.
-
- Each partition uses siteid for hash bucketing, and the number of buckets is 10
-
- The table creation statement is as follows:
-
- - ```mysql
- CREATE TABLE table2
- (
- event_day DATE,
- siteid INT DEFAULT '10',
- citycode SMALLINT,
- username VARCHAR(32) DEFAULT '',
- pv BIGINT SUM DEFAULT '0'
- )
- AGGREGATE KEY(event_day, siteid, citycode, username)
- PARTITION BY RANGE(event_day)
- (
- PARTITION p201706 VALUES LESS THAN ('2017-07-01'),
- PARTITION p201707 VALUES LESS THAN ('2017-08-01'),
- PARTITION p201708 VALUES LESS THAN ('2017-09-01')
- )
- DISTRIBUTED BY HASH(siteid) BUCKETS 10
- PROPERTIES("replication_num" = "1");
- ```
-
- After the table is created, you can view the information of the table in example_db:
-
- ```mysql
- MySQL> SHOW TABLES;
- +----------------------+
- | Tables_in_example_db |
- +----------------------+
- | table1 |
- | table2 |
- +----------------------+
- 2 rows in set (0.01 sec)
-
- MySQL> DESC table1;
- +----------+-------------+------+-------+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +----------+-------------+------+-------+---------+-------+
- | siteid | int(11) | Yes | true | 10 | |
- | citycode | smallint(6) | Yes | true | N/A | |
- | username | varchar(32) | Yes | true | | |
- | pv | bigint(20) | Yes | false | 0 | SUM |
- +----------+-------------+------+-------+---------+-------+
- 4 rows in set (0.00 sec)
-
- MySQL> DESC table2;
- +-----------+-------------+------+-------+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------+-------------+------+-------+---------+-------+
- | event_day | date | Yes | true | N/A | |
- | siteid | int(11) | Yes | true | 10 | |
- | citycode | smallint(6) | Yes | true | N/A | |
- | username | varchar(32) | Yes | true | | |
- | pv | bigint(20) | Yes | false | 0 | SUM |
- +-----------+-------------+------+-------+---------+-------+
- 5 rows in set (0.00 sec)
- ```
-
- > Precautions:
- >
- > 1. The above tables are all single-copy tables by setting replication_num. Doris recommends that users use the default 3-copy setting to ensure high availability.
- > 2. You can dynamically add or delete partitions to the composite partition table. See the Partition section in `HELP ALTER TABLE` for details.
- > 3. Data import can import the specified Partition. See `HELP LOAD` for details.
- > 4. The schema of the table can be dynamically modified.
- > 5. You can add Rollup to Table to improve query performance. For this part, please refer to the description of Rollup in the Advanced User Guide.
- > 6. The Null attribute of the table column defaults to true, which will have a certain impact on query performance.
-
-#### Insert Data
-
-1. Insert Into
-
- > For more detailed syntax and best practices for Insert usage, see [Insert](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT) Command Manual.
-
- The Insert Into statement is used in a similar way to the Insert Into statement in databases such as MySQL. But in Doris, all data writing is a separate import job. Therefore, Insert Into is also introduced as an import method here.
-
- The main Insert Into commands include the following two:
-
- - INSERT INTO tbl SELECT ...
- - INSERT INTO tbl (col1, col2, ...) VALUES (1, 2, ...), (1,3, ...);
-
- The second command is for Demo only, not in test or production environments.
-
- The Insert Into command needs to be submitted through the MySQL protocol. Creating an import request will return the import result synchronously.
-
- grammar:
-
- ```mysql
- INSERT INTO table_name [partition_info] [WITH LABEL label] [col_list] [query_stmt] [VALUES];
- ```
-
- Example:
-
- ```mysql
- INSERT INTO tbl2 WITH LABEL label1 SELECT * FROM tbl3;
- INSERT INTO tbl1 VALUES ("qweasdzxcqweasdzxc"),("a");
- ```
-
- **Notice**
-
- When using `CTE(Common Table Expressions)` as the query part in an insert operation, the `WITH LABEL` and `column list` parts must be specified. Example
-
- ```mysql
- INSERT INTO tbl1 WITH LABEL label1
- WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
- SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;
-
- INSERT INTO tbl1 (k1)
- WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
- SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;
- ```
-
- Insert Into itself is an SQL command, and its return results are divided into the following types according to the different execution results:
-
- - If the return result is `ERROR 1064 (HY000)`, it means the import failed.
- - If the returned result is `Query OK`, the execution is successful.
- - If `rows affected` is 0, the result set is empty and no data is imported.
- - If `rows affected` is greater than 0:
- - If `status` is `committed`, the data is not yet visible. Need to view status through `show transaction` statement until `visible`
- - If `status` is `visible`, the data import is successful.
- - If `warnings` is greater than 0, it means that data is filtered. You can get the url through the `show load` statement to view the filtered lines.
+>Note:
+>
+>1. The root user used here is the default user built into doris, and is also the super administrator user, see [Rights Management](...) /admin-manual/privilege-ldap/user-privilege)
+>2. -P: Here is our query port to connect to Doris, the default port is 9030, which corresponds to `query_port` in fe.conf
+>3. -h: Here is the IP address of the FE we are connecting to, if your client and FE are installed on the same node you can use 127.0.0.1, this is also provided by Doris if you forget the root password, you can connect directly to the login without the password in this way and reset the root password
- For more detailed instructions, see the [Insert](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT) command manual.
-
-2. Batch Import
-
- Doris supports multiple data import methods. For details, please refer to the data import documentation. Here we use Stream-Load and Broker-Load as an example.
-
- - Stream-Load
-
- > For more detailed syntax and best practices used by Stream-Load, see [Stream-Load](../sql-manual/sql-reference/Data-Manipulation-Statements/Load/STREAM-LOAD) command manual.
-
- Streaming import transfers data to Doris through the HTTP protocol, and can directly import local data without relying on other systems or components. See `HELP STREAM LOAD;` for detailed syntax help.
-
- Example 1: With "table1_20170707" as the Label, use the local file table1_data to import the table1 table.
-
- ```CQL
- 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 the node where any FE is located, and 8030 is the http_port in fe.conf.
- > 2. You can use the IP of any BE and the webserver_port in be.conf to import. For example: `BE_HOST:8040`
- > 3. example_db is the database which you imported the tables
-
- The local file `table1_data` uses `,` as the separation between data, the details are as follows:
-
- ```text
- 1,1,jim,2
- 2,1,grace,2
- 3,2,tom,2
- 4,3,bush,3
- 5,3,helen,3
- ```
+Execute the following command to view the FE running status
- Example 2: With "table2_20170707" as the Label, use the local file table2_data to import the table2 table.
+```sql
+show frontends\G;
+```
- ```CQL
- 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
- ```
+You can then see a result similar to the following.
+
+```sql
+mysql> show frontends\G;
+*************************** 1. row ***************************
+ Name: 172.21.32.5_9010_1660549353220
+ IP: 172.21.32.5
+ EditLogPort: 9010
+ HttpPort: 8030
+ QueryPort: 9030
+ RpcPort: 9020
+ Role: FOLLOWER
+ IsMaster: true
+ ClusterId: 1685821635
+ Join: true
+ Alive: true
+ReplayedJournalId: 49292
+ LastHeartbeat: 2022-08-17 13:00:45
+ IsHelper: true
+ ErrMsg:
+ Version: 1.1.2-rc03-ca55ac2
+ CurrentConnected: Yes
+1 row in set (0.03 sec)
+```
- The local file `table2_data` uses `|` as the separation between data, the details are as follows:
+1. If the IsMaster, Join and Alive columns are true, the node is normal.
- ```text
- 2017-07-03|1|1|jim|2
- 2017-07-05|2|1|grace|2
- 2017-07-12|3|2|tom|2
- 2017-07-15|4|3|bush|3
- 2017-07-12|5|3|helen|3
- ```
+#### Stop FE
- > Precautions:
- >
- > 1. It is recommended to use streaming import to limit the file size to 10GB. Too large files will increase the cost of failed retry attempts.
- > 2. Each batch of imported data needs to take a Label. The Label should preferably be a string related to a batch of data, which is easy to read and manage. Based on Label, Doris guarantees that within a Database, the same batch of data can only be successfully imported once. Labels of failed tasks can be reused.
- > 3. Streaming import is a synchronous command. If the command returns successfully, it means that the data has been imported, and if it returns a failure, it means that the batch of data has not been imported.
+The stopping of Doris FE can be done with the following command
- - Broker-Load
+```
+./bin/stop_fe.sh
+```
- Broker import uses the deployed Broker process to read data on external storage for import.
+### Configure BE
- > For more detailed syntax and best practices used by Broker Load, see [Broker Load](../sql-manual/sql-reference/Data-Manipulation-Statements/Load/BROKER-LOAD) command manual, you can also enter `HELP BROKER LOAD` in the MySql client command line for more help information.
-
- Example: With "table1_20170708" as the Label, import the files on HDFS into table1
-
- ```mysql
- LOAD LABEL table1_20170708
- (
- DATA INFILE("hdfs://your.namenode.host:port/dir/table1_data")
- INTO TABLE table1
- )
- WITH BROKER hdfs
- (
- "username"="hdfs_user",
- "password"="hdfs_password"
- )
- PROPERTIES
- (
- "timeout"="3600",
- "max_filter_ratio"="0.1"
- );
- ```
-
- Broker imports are asynchronous commands. The successful execution of the above command only means that the submitted task is successful. Whether the import is successful needs to be checked through `SHOW LOAD;`. Such as:
-
- ```mysql
- SHOW LOAD WHERE LABEL = "table1_20170708";
- ```
-
- In the returned result, if the `State` field is FINISHED, the import is successful.
-
- For more information on `SHOW LOAD`, see `HELP SHOW LOAD;`
-
- Asynchronous import tasks can be canceled before they finish:
-
- ```mysql
- CANCEL LOAD WHERE LABEL = "table1_20170708";
- ```
-
-#### Query Data
+Go to the `apache-doris-x.x.x/be` directory
-1. Simple query
+```
+cd apache-doris-x.x.x/be
+```
- ```mysql
- MySQL> SELECT * FROM table1 LIMIT 3;
- +--------+----------+----------+------+
- | siteid | citycode | username | pv |
- +--------+----------+----------+------+
- | 2 | 1 | 'grace' | 2 |
- | 5 | 3 | 'helen' | 3 |
- | 3 | 2 | 'tom' | 2 |
- +--------+----------+----------+------+
- 3 rows in set (0.01 sec)
-
- MySQL> SELECT * FROM table1 ORDER BY citycode;
- +--------+----------+----------+------+
- | siteid | citycode | username | pv |
- +--------+----------+----------+------+
- | 2 | 1 | 'grace' | 2 |
- | 1 | 1 | 'jim' | 2 |
- | 3 | 2 | 'tom' | 2 |
- | 4 | 3 | 'bush' | 3 |
- | 5 | 3 | 'helen' | 3 |
- +--------+----------+----------+------+
- 5 rows in set (0.01 sec)
- ```
+Modify the BE configuration file `conf/be.conf`, here we mainly modify two parameters: `priority_networks'` and `storage_root`, if you need more optimized configuration, please refer to [BE parameter configuration](... /admin-manual/config/be-config) instructions to make adjustments.
-2. Join query
+1. Add priority_networks parameter
- ```mysql
- MySQL> SELECT SUM(table1.pv) FROM table1 JOIN table2 WHERE table1.siteid = table2.siteid;
- +--------------------+
- | sum(`table1`.`pv`) |
- +--------------------+
- | 12 |
- +--------------------+
- 1 row in set (0.20 sec)
- ```
+```
+priority_networks=172.23.16.0/24
+```
-3. subquery
+>Note:
+>
+>This parameter we have to configure during installation, especially when a machine has multiple IP addresses, we have to assign a unique IP address to the BE.
- ```mysql
- MySQL> SELECT SUM(pv) FROM table2 WHERE siteid IN (SELECT siteid FROM table1 WHERE siteid > 2);
- +-----------+
- | sum(`pv`) |
- +-----------+
- | 8 |
- +-----------+
- 1 row in set (0.13 sec)
- ```
+2. Configure the BE data storage directory
-#### Update Data
-> For more detailed syntax and best practices used by Update, see [Update](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE) Command Manual.
+```
+storage_root=/path/your/doris-meta
+```
-The current UPDATE statement **only supports** row updates on the Unique model, and there may be data conflicts caused by concurrent updates. At present, Doris does not deal with such problems, and users need to avoid such problems from the business side.
+>Notes.
+>
+>1. The default directory is in the storage directory of the BE installation directory.
+>2. The storage directory for BE configuration must be created first
-1. grammar rules
+### Start BE
- ```text
- UPDATE table_name
- SET assignment_list
- WHERE expression
-
- value:
- {expr | DEFAULT}
-
- assignment:
- col_name = value
-
- assignment_list:
- assignment [, assignment] ...
- ```
+Execute the following command in the BE installation directory to complete the BE startup.
- **Parameter Description**
+```
+./bin/start_be.sh --daemon
+```
- - table_name: The target table of the data to be updated. Can be of the form 'db_name.table_name'
- - assignment_list: the target column to be updated, in the format 'col_name = value, col_name = value'
- - where expression: the condition that is expected to be updated, an expression that returns true or false can be
+#### Adding a BE node to a cluster
-2. Example
+Connect to FE via MySQL client and execute the following SQL to add the BE to the cluster
- The `test` table is a unique model table, which contains four columns k1, k2, v1, v2. Where k1, k2 are keys, v1, v2 are values, and the aggregation method is Replace.
+```sql
+ALTER SYSTEM ADD BACKEND "be_host_ip:heartbeat_service_port";
+```
- 1. Update the v1 column in the 'test' table that satisfies the conditions k1 =1 , k2 =2 to 1
+1. be_host_ip: Here is the IP address of your BE, match with `priority_networks` in `be.conf`.
+2. heartbeat_service_port: This is the heartbeat upload port of your BE, match with `heartbeat_service_port` in `be.conf`, default is `9050`.
- ```sql
- UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;
- ```
+#### View BE operational status
- 2. Increment the v1 column of the column k1=1 in the 'test' table by 1
+You can check the running status of BE by executing the following command at the MySQL command line.
- ```sql
- UPDATE test SET v1 = v1+1 WHERE k1=1;
- ```
+```sql
+SHOW BACKENDS\G;
+```
-#### Delete Data
+Example:
+
+```sql
+mysql> SHOW BACKENDS\G;
+*************************** 1. row ***************************
+ BackendId: 10003
+ Cluster: default_cluster
+ IP: 172.21.32.5
+ HeartbeatPort: 9050
+ BePort: 9060
+ HttpPort: 8040
+ BrpcPort: 8060
+ LastStartTime: 2022-08-16 15:31:37
+ LastHeartbeat: 2022-08-17 13:33:17
+ Alive: true
+ SystemDecommissioned: false
+ClusterDecommissioned: false
+ TabletNum: 170
+ DataUsedCapacity: 985.787 KB
+ AvailCapacity: 782.729 GB
+ TotalCapacity: 984.180 GB
+ UsedPct: 20.47 %
+ MaxDiskUsedPct: 20.47 %
+ Tag: {"location" : "default"}
+ ErrMsg:
+ Version: 1.1.2-rc03-ca55ac2
+ Status: {"lastSuccessReportTabletsTime":"2022-08-17 13:33:05","lastStreamLoadTime":-1,"isQueryDisabled":false,"isLoadDisabled":false}
+1 row in set (0.01 sec)
+```
-> For more detailed syntax and best practices for Delete use, see [Delete](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE) Command Manual.
+1. Alive : true means the node is running normally
-1. Grammar rules
+#### Stop BE
- This statement is used to conditionally delete data in the specified table (base index) partition.
+The stopping of Doris BE can be done with the following command
- This operation will also delete the data of the rollup index related to this base index.
- grammar:
+```
+./bin/stop_be.sh
+```
- ```sql
- DELETE FROM table_name [PARTITION partition_name | PARTITIONS (p1, p2)]
- WHERE
- column_name1 op { value | value_list } [ AND column_name2 op { value | value_list } ...];
- ````
+## Create table
- illustrate:
+1. Create database
- - Optional types of op include: =, >, <, >=, <=, !=, in, not in.
+```sql
+create database demo;
+```
- - Only conditions on the key column can be specified.
+2. Create table
+
+```sql
+use demo;
+
+CREATE TABLE IF NOT EXISTS demo.expamle_tbl
+(
+ `user_id` LARGEINT NOT NULL COMMENT "user id",
+ `date` DATE NOT NULL COMMENT "",
+ `city` VARCHAR(20) COMMENT "",
+ `age` SMALLINT COMMENT "",
+ `sex` TINYINT COMMENT "",
+ `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "",
+ `cost` BIGINT SUM DEFAULT "0" COMMENT "",
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT ""
+)
+AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
+PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+);
+```
- - Cannot delete when the selected key column does not exist in a rollup.
+3. Example data
- - Conditions can only have an AND relationship.
+```
+10000,2017-10-01,beijing,20,0,2017-10-01 06:00:00,20,10,10
+10006,2017-10-01,beijing,20,0,2017-10-01 07:00:00,15,2,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
+```
- If you want to achieve an "or" relationship, you need to write the conditions in two DELETE statements.
+Save the above data in a test.csv file.
- - If it is a partitioned table, you can specify a partition, if not specified and the session variable delete_without_partition is true, it will be applied to all partitions. If it is a single-partition table, it can be left unspecified.
+4. Import data
- Notice:
+Here we import the data saved to the file above into the table we just created via Stream load。
- - This statement may reduce query efficiency for a period of time after execution.
- - The degree of impact depends on the number of delete conditions specified in the statement.
- - The more conditions you specify, the greater the impact.
+```
+curl --location-trusted -u root: -T test.csv -H "column_separator:," http://127.0.0.1:8030/api/demo/expamle_tbl/_stream_load
+```
-2. Example
+- -T test.csv : This is the data file we just saved, if the path is different, please specify the full path
+- -u root: Here is the user name and password, we use the default user root, the password is empty
+- 127.0.0.1:8030 : is the ip and http_port of fe, respectively
- 1. Delete the data row whose k1 column value is 3 in my_table partition p1
+After successful execution we can see the following return message
- ```sql
- DELETE FROM my_table PARTITION p1 WHERE k1 = 3;
- ````
+```json
+{
+ "TxnId": 30303,
+ "Label": "8690a5c7-a493-48fc-b274-1bb7cd656f25",
+ "TwoPhaseCommit": "false",
+ "Status": "Success",
+ "Message": "OK",
+ "NumberTotalRows": 7,
+ "NumberLoadedRows": 7,
+ "NumberFilteredRows": 0,
+ "NumberUnselectedRows": 0,
+ "LoadBytes": 399,
+ "LoadTimeMs": 381,
+ "BeginTxnTimeMs": 3,
+ "StreamLoadPutTimeMs": 5,
+ "ReadDataTimeMs": 0,
+ "WriteDataTimeMs": 191,
+ "CommitAndPublishTimeMs": 175
+}
+```
- 2. Delete the data rows where the value of column k1 is greater than or equal to 3 and the value of column k2 is "abc" in my_table partition p1
+1. `NumberLoadedRows` indicates the number of data records that have been imported
+
+2. `NumberTotalRows` indicates the total amount of data to be imported
+
+3. `Status` :Success means the import was successful
+
+Here we have finished importing the data, and we can now query and analyze the data according to our own needs.
+
+## Query data
+
+We have finished building tables and importing data above, so we can experience Doris' ability to quickly query and analyze data.
+
+```sql
+mysql> select * from expamle_tbl;
++---------+------------+-----------+------+------+---------------------+------+----------------+----------------+
+| 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 06:00:00 | 20 | 10 | 10 |
+| 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 |
+| 10006 | 2017-10-01 | beijing | 20 | 0 | 2017-10-01 07:00:00 | 15 | 2 | 2 |
++---------+------------+-----------+------+------+---------------------+------+----------------+----------------+
+7 rows in set (0.01 sec)
+
+mysql> select * from expamle_tbl where city='shanghai';
++---------+------------+----------+------+------+---------------------+------+----------------+----------------+
+| user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
++---------+------------+----------+------+------+---------------------+------+----------------+----------------+
+| 10002 | 2017-10-02 | shanghai | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
++---------+------------+----------+------+------+---------------------+------+----------------+----------------+
+1 row in set (0.00 sec)
+
+mysql> select city, sum(cost) as total_cost from expamle_tbl group by city;
++-----------+------------+
+| city | total_cost |
++-----------+------------+
+| beijing | 37 |
+| shenzhen | 111 |
+| guangzhou | 30 |
+| shanghai | 200 |
++-----------+------------+
+4 rows in set (0.00 sec)
+```
- ```sql
- DELETE FROM my_table PARTITION p1 WHERE k1 >= 3 AND k2 = "abc";
- ````
- 3. Delete the data rows where the value of column k1 is greater than or equal to 3 and the value of column k2 is "abc" in my_table partition p1, p2
- ```sql
- DELETE FROM my_table PARTITIONS (p1, p2) WHERE k1 >= 3 AND k2 = "abc";
- ````
+This is the end of our entire quick start. We have experienced the complete Doris operation process from Doris installation and deployment, start/stop, creation of library tables, data import and query, let's start our Doris usage journey.
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/basic-usage.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/basic-usage.md
index 36a1632a3f3..3e2447a35f3 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/basic-usage.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/basic-usage.md
@@ -1,6 +1,6 @@
---
{
- "title": "基础使用指南",
+ "title": "使用指南",
"language": "zh-CN"
}
---
@@ -24,24 +24,24 @@ specific language governing permissions and limitations
under the License.
-->
-# 基础使用指南
+# 使用指南
Doris 采用 MySQL 协议进行通信,用户可通过 MySQL client 或者 MySQL JDBC连接到 Doris 集群。选择 MySQL client 版本时建议采用5.1 之后的版本,因为 5.1 之前不能支持长度超过 16 个字符的用户名。本文以 MySQL client 为例,通过一个完整的流程向用户展示 Doris 的基本使用方法。
## 创建用户
+下载免安装的 [MySQL 客户端](https://doris-build-hk.oss-cn-hongkong.aliyuncs.com/mysql-client/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz)。
+
### Root用户登录与密码修改
-Doris 内置 root 和 admin 用户,密码默认都为空。
+Doris 内置 root,密码默认为空。
>备注:
>
->Doris 提供的默认 root 和 admin 用户是管理员用户
+>Doris 提供的默认 root
>
>root 用户默认拥有集群所有权限。同时拥有 Grant_priv 和 Node_priv 的用户,可以将该权限赋予其他用户,拥有节点变更权限,包括 FE、BE、BROKER 节点的添加、删除、下线等操作。
>
->admin用户拥有 ADMIN_PRIV 和 GRANT_PRIV 权限
->
>关于权限这块的具体说明可以参照[权限管理](/docs/admin-manual/privilege-ldap/user-privilege)
启动完 Doris 程序之后,可以通过 root 或 admin 用户连接到 Doris 集群。 使用下面命令即可登录 Doris,登录后进入到Doris对应的Mysql命令行操作界面:
@@ -193,7 +193,7 @@ DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");
```
-#### 复合分区
+#### 多分区
建立一个名字为 table2 的逻辑表。
@@ -276,7 +276,7 @@ mysql> DESC table2;
>注意事项:
> 1. 上述表通过设置 replication_num 建的都是单副本的表,Doris建议用户采用默认的 3 副本设置,以保证高可用。
-> 2. 可以对复合分区表动态的增删分区,详见 `HELP ALTER TABLE;` 中 Partition 相关部分。
+> 2. 可以对多分区表动态的增删分区,详见 `HELP ALTER TABLE;` 中 Partition 相关部分。
> 3. 数据导入可以导入指定的 Partition,详见 `HELP LOAD;`。
> 4. 可以动态修改表的 Schema,详见`HELP ALTER TABLE;`。
> 5. 可以对 Table 增加上卷表(Rollup)以提高查询性能,这部分可以参见高级使用指南关于 Rollup 的描述。
@@ -284,7 +284,7 @@ mysql> DESC table2;
### 导入数据
-Doris 支持多种数据导入方式。具体可以参阅[数据导入](../data-operate/import/load-manual.md)文档。这里我们使用流式导入和 Broker 导入做示例。
+Doris 支持多种数据导入方式。具体可以参阅 [数据导入](../data-operate/import/load-manual.md) 文档。这里我们使用流式导入和 Broker 导入做示例。
#### 流式导入
@@ -328,7 +328,7 @@ curl --location-trusted -u test:test -H "label:table2_20170707" -H "column_separ
> 注意事项:
>
> 1. 采用流式导入建议文件大小限制在 10GB 以内,过大的文件会导致失败重试代价变大。
-> 2. 每一批导入数据都需要取一个 Label,Label 最好是一个和一批数据有关的字符串,方便阅读和管理。Doris 基于 Label 保证在一个Database 内,同一批数据只可导入成功一次。失败任务的 Label 可以重用。
+> 2. label:Label 的主要作用是唯一标识一个导入任务,并且能够保证相同的 Label 仅会被成功导入一次,具体可以查看 [数据导入事务及原子性 ](../data-operate/import/import-scenes/load-atomicity)。
> 3. 流式导入是同步命令。命令返回成功则表示数据已经导入,返回失败表示这批数据没有导入。
#### Broker 导入
@@ -428,3 +428,263 @@ mysql> SELECT SUM(pv) FROM table2 WHERE siteid IN (SELECT siteid FROM table1 WHE
+-----------+
1 row in set (0.13 sec)
```
+
+## 表结构变更
+
+使用 [ALTER TABLE COLUMN](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md) 命令可以修改表的 Schema,包括如下修改:
+
+- 增加列
+- 删除列
+- 修改列类型
+- 改变列顺序
+
+以下通过使用示例说明表结构变更:
+
+原表 table1 的 Schema 如下:
+
+```text
++----------+-------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++----------+-------------+------+-------+---------+-------+
+| siteid | int(11) | No | true | 10 | |
+| citycode | smallint(6) | No | true | N/A | |
+| username | varchar(32) | No | true | | |
+| pv | bigint(20) | No | false | 0 | SUM |
++----------+-------------+------+-------+---------+-------+
+```
+
+我们新增一列 uv,类型为 BIGINT,聚合类型为 SUM,默认值为 0:
+
+```sql
+ALTER TABLE table1 ADD COLUMN uv BIGINT SUM DEFAULT '0' after pv;
+```
+
+提交成功后,可以通过以下命令查看作业进度:
+
+```sql
+SHOW ALTER TABLE COLUMN;
+```
+
+当作业状态为 `FINISHED`,则表示作业完成。新的 Schema 已生效。
+
+ALTER TABLE 完成之后, 可以通过 `DESC TABLE` 查看最新的 Schema。
+
+```sql
+mysql> DESC table1;
++----------+-------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++----------+-------------+------+-------+---------+-------+
+| siteid | int(11) | No | true | 10 | |
+| citycode | smallint(6) | No | true | N/A | |
+| username | varchar(32) | No | true | | |
+| pv | bigint(20) | No | false | 0 | SUM |
+| uv | bigint(20) | No | false | 0 | SUM |
++----------+-------------+------+-------+---------+-------+
+5 rows in set (0.00 sec)
+```
+
+可以使用以下命令取消当前正在执行的作业:
+
+```sql
+CANCEL ALTER TABLE COLUMN FROM table1;
+```
+
+更多帮助,可以参阅 `HELP ALTER TABLE`。
+
+## Rollup
+
+Rollup 可以理解为 Table 的一个物化索引结构。**物化** 是因为其数据在物理上独立存储,而 **索引** 的意思是,Rollup可以调整列顺序以增加前缀索引的命中率,也可以减少key列以增加数据的聚合度。
+
+使用[ALTER TABLE ROLLUP](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-ROLLUP.md)可以进行Rollup的各种变更操作。
+
+以下举例说明
+
+原表table1的Schema如下:
+
+```text
++----------+-------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++----------+-------------+------+-------+---------+-------+
+| siteid | int(11) | No | true | 10 | |
+| citycode | smallint(6) | No | true | N/A | |
+| username | varchar(32) | No | true | | |
+| pv | bigint(20) | No | false | 0 | SUM |
+| uv | bigint(20) | No | false | 0 | SUM |`
++----------+-------------+------+-------+---------+-------+
+```
+
+对于 table1 明细数据是 siteid, citycode, username 三者构成一组 key,从而对 pv 字段进行聚合;如果业务方经常有看城市 pv 总量的需求,可以建立一个只有 citycode, pv 的rollup。
+
+```sql
+ALTER TABLE table1 ADD ROLLUP rollup_city(citycode, pv);
+```
+
+提交成功后,可以通过以下命令查看作业进度:
+
+```sql
+SHOW ALTER TABLE ROLLUP;
+```
+
+当作业状态为 `FINISHED`,则表示作业完成。
+
+Rollup 建立完成之后可以使用 `DESC table1 ALL` 查看表的 Rollup 信息。
+
+```sql
+mysql> desc table1 all;
++-------------+----------+-------------+------+-------+--------+-------+
+| IndexName | Field | Type | Null | Key | Default | Extra |
++-------------+----------+-------------+------+-------+---------+-------+
+| table1 | siteid | int(11) | No | true | 10 | |
+| | citycode | smallint(6) | No | true | N/A | |
+| | username | varchar(32) | No | true | | |
+| | pv | bigint(20) | No | false | 0 | SUM |
+| | uv | bigint(20) | No | false | 0 | SUM |
+| | | | | | | |
+| rollup_city | citycode | smallint(6) | No | true | N/A | |
+| | pv | bigint(20) | No | false | 0 | SUM |
++-------------+----------+-------------+------+-------+---------+-------+
+8 rows in set (0.01 sec)
+```
+
+可以使用以下命令取消当前正在执行的作业:
+
+```sql
+CANCEL ALTER TABLE ROLLUP FROM table1;
+```
+
+Rollup 建立之后,查询不需要指定 Rollup 进行查询。还是指定原有表进行查询即可。程序会自动判断是否应该使用 Rollup。是否命中 Rollup可以通过 `EXPLAIN your_sql;` 命令进行查看。
+
+更多帮助,可以参阅 `HELP ALTER TABLE`。
+
+## 物化视图
+
+物化视图是一种以空间换时间的数据分析加速技术。Doris 支持在基础表之上建立物化视图。比如可以在明细数据模型的表上建立基于部分列的聚合视图,这样可以同时满足对明细数据和聚合数据的快速查询。
+
+同时,Doris 能够自动保证物化视图和基础表的数据一致性,并且在查询时自动匹配合适的物化视图,极大降低用户的数据维护成本,为用户提供一个一致且透明的查询加速体验。
+
+关于物化视图的具体介绍,可参阅 [物化视图](../advanced/materialized-view)
+
+## 数据表的查询
+
+### 内存限制
+
+为了防止用户的一个查询可能因为消耗内存过大。查询进行了内存控制,一个查询任务,在单个 BE 节点上默认使用不超过 2GB 内存。
+
+用户在使用时,如果发现报 `Memory limit exceeded` 错误,一般是超过内存限制了。
+
+遇到内存超限时,用户应该尽量通过优化自己的 sql 语句来解决。
+
+如果确切发现2GB内存不能满足,可以手动设置内存参数。
+
+显示查询内存限制:
+
+```sql
+mysql> SHOW VARIABLES LIKE "%mem_limit%";
++---------------+------------+
+| Variable_name | Value |
++---------------+------------+
+| exec_mem_limit| 2147483648 |
++---------------+------------+
+1 row in set (0.00 sec)
+```
+
+`exec_mem_limit` 的单位是 byte,可以通过 `SET` 命令改变 `exec_mem_limit` 的值。如改为 8GB。
+
+```sql
+mysql> SET exec_mem_limit = 8589934592;
+Query OK, 0 rows affected (0.00 sec)
+mysql> SHOW VARIABLES LIKE "%mem_limit%";
++---------------+------------+
+| Variable_name | Value |
++---------------+------------+
+| exec_mem_limit| 8589934592 |
++---------------+------------+
+1 row in set (0.00 sec)
+```
+
+> - 以上该修改为 session 级别,仅在当前连接 session 内有效。断开重连则会变回默认值。
+> - 如果需要修改全局变量,可以这样设置:`SET GLOBAL exec_mem_limit = 8589934592;`。设置完成后,断开 session 重新登录,参数将永久生效。
+
+### 查询超时
+
+当前默认查询时间设置为最长为 300 秒,如果一个查询在 300 秒内没有完成,则查询会被 Doris 系统 cancel 掉。用户可以通过这个参数来定制自己应用的超时时间,实现类似 wait(timeout) 的阻塞方式。
+
+查看当前超时设置:
+
+```sql
+mysql> SHOW VARIABLES LIKE "%query_timeout%";
++---------------+-------+
+| Variable_name | Value |
++---------------+-------+
+| QUERY_TIMEOUT | 300 |
++---------------+-------+
+1 row in set (0.00 sec)
+```
+
+修改超时时间到1分钟:
+
+```sql
+mysql> SET query_timeout = 60;
+Query OK, 0 rows affected (0.00 sec)
+```
+
+> - 当前超时的检查间隔为 5 秒,所以小于 5 秒的超时不会太准确。
+> - 以上修改同样为 session 级别。可以通过 `SET GLOBAL` 修改全局有效。
+
+### Broadcast/Shuffle Join
+
+系统默认实现 Join 的方式,是将小表进行条件过滤后,将其广播到大表所在的各个节点上,形成一个内存 Hash 表,然后流式读出大表的数据进行Hash Join。但是如果当小表过滤后的数据量无法放入内存的话,此时 Join 将无法完成,通常的报错应该是首先造成内存超限。
+
+如果遇到上述情况,建议显式指定 Shuffle Join,也被称作 Partitioned Join。即将小表和大表都按照 Join 的 key 进行 Hash,然后进行分布式的 Join。这个对内存的消耗就会分摊到集群的所有计算节点上。
+
+Doris会自动尝试进行 Broadcast Join,如果预估小表过大则会自动切换至 Shuffle Join。注意,如果此时显式指定了 Broadcast Join 也会自动切换至 Shuffle Join。
+
+使用 Broadcast Join(默认):
+
+```sql
+mysql> select sum(table1.pv) from table1 join table2 where table1.siteid = 2;
++--------------------+
+| sum(`table1`.`pv`) |
++--------------------+
+| 10 |
++--------------------+
+1 row in set (0.20 sec)
+```
+
+使用 Broadcast Join(显式指定):
+
+```sql
+mysql> select sum(table1.pv) from table1 join [broadcast] table2 where table1.siteid = 2;
++--------------------+
+| sum(`table1`.`pv`) |
++--------------------+
+| 10 |
++--------------------+
+1 row in set (0.20 sec)
+```
+
+使用 Shuffle Join:
+
+```sql
+mysql> select sum(table1.pv) from table1 join [shuffle] table2 where table1.siteid = 2;
++--------------------+
+| sum(`table1`.`pv`) |
++--------------------+
+| 10 |
++--------------------+
+1 row in set (0.15 sec)
+```
+
+### 查询重试和高可用
+
+当部署多个 FE 节点时,用户可以在多个 FE 之上部署负载均衡层来实现 Doris 的高可用。
+
+具体安装部署及使用方式请参照 [负载均衡](../admin-manual/cluster-management/load-balancing)
+
+## 数据更新和删除
+
+Doris 支持通过两种方式对已导入的数据进行删除。一种是通过 DELETE FROM 语句,指定 WHERE 条件对数据进行删除。这种方式比较通用,适合频率较低的定时删除任务。
+
+另一种删除方式仅针对 Unique 主键唯一模型,通过导入数据的方式将需要删除的主键行数据进行导入。Doris 内部会通过删除标记位对数据进行最终的物理删除。这种删除方式适合以实时的方式对数据进行删除。
+
+关于删除和更新操作的具体说明,可参阅 [数据更新](../data-operate/update-delete/update) 相关文档。
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/best-practice.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/best-practice.md
index 4070c6ad4c5..55c9a396173 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/best-practice.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/best-practice.md
@@ -88,7 +88,7 @@ DISTRIBUTED BY HASH(sessionid, visitorid) BUCKETS 10;
### 大宽表与 Star Schema
-业务方建表时, 为了和前端业务适配, 往往不对维度信息和指标信息加以区分, 而将 Schema 定义成大宽表。对于 Doris 而言, 这类大宽表往往性能不尽如人意:
+业务方建表时, 为了和前端业务适配, 往往不对维度信息和指标信息加以区分, 而将 Schema 定义成大宽表,这种操作对于数据库其实不是那么友好,我们更建议用户采用星型模型。
- Schema 中字段数比较多, 聚合模型中可能 key 列比较多, 导入过程中需要排序的列会增加。
- 维度信息更新会反应到整张表中,而更新的频率直接影响查询的效率。
@@ -171,28 +171,12 @@ ALTER TABLE session_data ADD ROLLUP rollup_brower(brower,province,ip,url) DUPLIC
## Schema Change
-Doris中目前进行 Schema Change 的方式有三种:Sorted Schema Change,Direct Schema Change, Linked Schema Change。
+用户可以通过 Schema Change 操作来修改已存在表的 Schema。目前 Doris 支持以下几种修改:
-### Sorted Schema Change
+- 增加、删除列
+- 修改列类型
+- 调整列顺序
+- 增加、修改 Bloom Filter
+- 增加、删除 bitmap index
-改变了列的排序方式,需对数据进行重新排序。例如删除排序列中的一列, 字段重排序。
-
-```sql
-ALTER TABLE site_visit DROP COLUMN city;
-```
-
-### Direct Schema Change
-
-无需重新排序,但是需要对数据做一次转换。例如修改列的类型,在稀疏索引中加一列等。
-
-```sql
-ALTER TABLE site_visit MODIFY COLUMN username varchar(64);
-```
-
-### Linked Schema Change
-
-```sql
-ALTER TABLE site_visit ADD COLUMN click bigint SUM default '0';
-```
-
-建表时建议考虑好 Schema,这样在进行 Schema Change 时可以加快速度。
+具体请参照 [Schema 变更](../advanced/alter-table/schema-change)
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/data-model.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/data-model.md
index 3a73dbda8c4..90f8df7b867 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/data-model.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/data-model.md
@@ -344,7 +344,7 @@ PROPERTIES (
这种数据模型区别于 Aggregate 和 Unique 模型。数据完全按照导入文件中的数据进行存储,不会有任何聚合。即使两行数据完全相同,也都会保留。 而在建表语句中指定的 DUPLICATE KEY,只是用来指明底层数据按照那些列进行排序。(更贴切的名称应该为 “Sorted Column”,这里取名 “DUPLICATE KEY” 只是用以明确表示所用的数据模型。关于 “Sorted Column”的更多解释,可以参阅[前缀索引](./index/prefix-index.md))。在 DUPLICATE KEY 的选择上,我们建议适当的选择前 2-4 列就可以。
-这种数据模型适用于既没有聚合需求,又没有主键唯一性约束的原始数据的存储。更多使用场景,可参阅[聚合模型的局限性](./data-model.md#聚合模型的局限性)小节。
+这种数据模型适用于既没有聚合需求,又没有主键唯一性约束的原始数据的存储。更多使用场景,可参阅**聚合模型的局限性**小节。
## 聚合模型的局限性
@@ -434,7 +434,7 @@ SELECT COUNT(*) FROM table;
所以,`select count(*) from table;` 的正确结果应该为 **4**。但如果我们只扫描 `user_id` 这一列,如果加上查询时聚合,最终得到的结果是 **3**(10001, 10002, 10003)。而如果不加查询时聚合,则得到的结果是 **5**(两批次一共5行数据)。可见这两个结果都是不对的。
-为了得到正确的结果,我们必须同时读取 `user_id` 和 `date` 这两列的数据,**再加上查询时聚合**,才能返回 **4** 这个正确的结果。也就是说,在 `count(*)` 查询中,Doris 必须扫描所有的 AGGREGATE KEY 列(这里就是 `user_id` 和 `date`),并且聚合后,才能得到语意正确的结果。当聚合列非常多时,`count(*)` 查询需要扫描大量的数据。
+为了得到正确的结果,我们必须同时读取 `user_id` 和 `date` 这两列的数据,**再加上查询时聚合**,才能返回 **4** 这个正确的结果。也就是说,在 count(*) 查询中,Doris 必须扫描所有的 AGGREGATE KEY 列(这里就是 `user_id` 和 `date`),并且聚合后,才能得到语意正确的结果。当聚合列非常多时,count(*) 查询需要扫描大量的数据。
因此,当业务上有频繁的 count(*) 查询时,我们建议用户通过增加一个**值恒为 1 的,聚合类型为 SUM 的列来模拟 count(\*)**。如刚才的例子中的表结构,我们修改如下:
@@ -451,16 +451,13 @@ SELECT COUNT(*) FROM table;
### Duplicate 模型
-Duplicate 模型没有聚合模型的这个局限性。因为该模型不涉及聚合语意,在做 `count(*)` 查询时,任意选择一列查询,即可得到语意正确的结果。
+Duplicate 模型没有聚合模型的这个局限性。因为该模型不涉及聚合语意,在做 count(*) 查询时,任意选择一列查询,即可得到语意正确的结果。
## 数据模型的选择建议
因为数据模型在建表时就已经确定,且**无法修改**。所以,选择一个合适的数据模型**非常重要**。
-1. Aggregate 模型可以通过预聚合,极大地降低聚合查询时所需扫描的数据量和查询的计算量,非常适合有固定模式的报表类查询场景。但是该模型对 `count(*)` 查询很不友好。同时因为固定了 Value 列上的聚合方式,在进行其他类型的聚合查询时,需要考虑语意正确性。
-
+1. Aggregate 模型可以通过预聚合,极大地降低聚合查询时所需扫描的数据量和查询的计算量,非常适合有固定模式的报表类查询场景。但是该模型对 count(*) 查询很不友好。同时因为固定了 Value 列上的聚合方式,在进行其他类型的聚合查询时,需要考虑语意正确性。
2. Unique 模型针对需要唯一主键约束的场景,可以保证主键唯一性约束。但是无法利用 ROLLUP 等预聚合带来的查询优势(因为本质是 REPLACE,没有 SUM 这种聚合方式)。
-
- >**【注意】**Unique 模型仅支持整行更新,如果用户既需要唯一主键约束,又需要更新部分列(例如将多张源表导入到一张 doris 表的情形),则可以考虑使用 Aggregate 模型,同时将非主键列的聚合类型设置为 REPLACE_IF_NOT_NULL。具体的用法可以参考[语法手册](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md)
-
+ - 【注意】Unique 模型仅支持整行更新,如果用户既需要唯一主键约束,又需要更新部分列(例如将多张源表导入到一张 doris 表的情形),则可以考虑使用 Aggregate 模型,同时将非主键列的聚合类型设置为 REPLACE_IF_NOT_NULL。具体的用法可以参考[语法手册](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md)
3. Duplicate 适合任意维度的 Ad-hoc 查询。虽然同样无法利用预聚合的特性,但是不受聚合模型的约束,可以发挥列存模型的优势(只读取相关列,而不需要读取所有 Key 列)。
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/bloomfilter.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/bloomfilter.md
index 49fcde10dc2..3d15676616d 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/bloomfilter.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/bloomfilter.md
@@ -42,9 +42,9 @@ BloomFilter是由Bloom在1970年提出的一种多哈希函数映射的快速查
## Doris BloomFilter索引及使用使用场景
-举个例子:如果要查找一个占用100字节存储空间大小的短行,一个64KB的数据块应该包含(64 * 1024)/100 = 655.53 = ~700行,如果仅能在整个数据块的起始行键上建立索引,那么它是无法给你提供细粒度的索引信息的。因为要查找的行数据可能会落在该数据块的行区间上,也可能行数据没在该数据块上,也可能是表中根本就不存在该行数据,也或者是行数据在另一个数据块里,甚至在内存里。以上这几种情况,都会导致从磁盘读取数据块时带来额外的IO开销,也会滥用数据块的缓存,当面对一个巨大的数据集且处于高并发读时,会严重影响性能。
+举个例子:如果要查找一个占用100字节存储空间大小的短行,一个64KB的HFile数据块应该包含(64 * 1024)/100 = 655.53 = ~700行,如果仅能在整个数据块的起始行键上建立索引,那么它是无法给你提供细粒度的索引信息的。因为要查找的行数据可能会落在该数据块的行区间上,也可能行数据没在该数据块上,也可能是表中根本就不存在该行数据,也或者是行数据在另一个HFile里,甚至在MemStore里。以上这几种情况,都会导致从磁盘读取数据块时带来额外的IO开销,也会滥用数据块的缓存,当面对一个巨大的数据集且处于高并发读时,会严重影响性能。
-因此,Doris 提供了布隆过滤器,它允许你对存储在每个数据块的数据做一个反向测试。当某行被请求时,通过布隆过滤器先检查该行是否不在这个数据块,布隆过滤器要么确定回答该行不在,要么回答它不知道。这就是为什么我们称它是反向测试。布隆过滤器同样也可以应用到行里的单元上,当访问某列标识符时可以先使用同样的反向测试。
+因此,HBase提供了布隆过滤器,它允许你对存储在每个数据块的数据做一个反向测试。当某行被请求时,通过布隆过滤器先检查该行是否不在这个数据块,布隆过滤器要么确定回答该行不在,要么回答它不知道。这就是为什么我们称它是反向测试。布隆过滤器同样也可以应用到行里的单元上,当访问某列标识符时可以先使用同样的反向测试。
但布隆过滤器也不是没有代价。存储这个额外的索引层次会占用额外的空间。布隆过滤器随着它们的索引对象数据增长而增长,所以行级布隆过滤器比列标识符级布隆过滤器占用空间要少。当空间不是问题时,它们可以帮助你榨干系统的性能潜力。
Doris的BloomFilter索引可以通过建表的时候指定,或者通过表的ALTER操作来完成。Bloom Filter本质上是一种位图结构,用于快速的判断一个给定的值是否在一个集合中。这种判断会产生小概率的误判。即如果返回false,则一定不在这个集合内。而如果范围true,则有可能在这个集合内。
@@ -93,7 +93,7 @@ PROPERTIES (
查看我们在表上建立的BloomFilter索引是使用:
```sql
-SHOW CREATE TABLE <table_name>
+SHOW CREATE TABLE <table_name>;
```
## 删除BloomFilter索引
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/prefix-index.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/prefix-index.md
index c2320136fb9..90960fa15d6 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/prefix-index.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-table/index/prefix-index.md
@@ -1,6 +1,6 @@
---
{
- "title": "前缀索引",
+ "title": "索引",
"language": "zh-CN"
}
---
@@ -23,9 +23,15 @@ KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
-# 前缀索引
+# 索引
-## 基本概念
+索引用于帮助快速过滤或查找数据。
+
+目前 Doris 主要支持两类索引:内建的智能索引,包括前缀索引和 ZoneMap 索引。用户创建的二级索引,包括 [Bloom Filter 索引](./bloomfilter) 和[Bitmap倒排索引](./bitmap-index)。
+
+其中 ZoneMap 索引是在列存格式上,对每一列自动维护的索引信息,包括 Min/Max,Null 值个数等等。这种索引对用户透明。
+
+## 前缀索引
不同于传统的数据库设计,Doris 不支持在任意列上创建索引。Doris 这类 MPP 架构的 OLAP 数据库,通常都是通过提高并发,来处理大量数据的。
@@ -73,6 +79,6 @@ SELECT * FROM table WHERE age=20;
所以在建表时,**正确的选择列顺序,能够极大地提高查询效率**。
-## 通过ROLLUP来调整前缀索引
+## 通过 Rollup 来调整前缀索引
-因为建表时已经指定了列顺序,所以一个表只有一种前缀索引。这对于使用其他不能命中前缀索引的列作为条件进行的查询来说,效率上可能无法满足需求。因此,我们可以通过创建 ROLLUP 来人为的调整列顺序。详情可参考[ROLLUP](../hit-the-rollup.md)。
\ No newline at end of file
+因为建表时已经指定了列顺序,所以一个表只有一种前缀索引。这对于使用其他不能命中前缀索引的列作为条件进行的查询来说,效率上可能无法满足需求。因此,我们可以通过创建 ROLLUP 来人为的调整列顺序。详情可参考 [ROLLUP](../hit-the-rollup.md)。
\ No newline at end of file
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/get-starting/get-starting.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/get-starting/get-starting.md
index f77b8715100..2745affdbd6 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/get-starting/get-starting.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/get-starting/get-starting.md
@@ -27,674 +27,376 @@ under the License.
# 快速开始
-## 环境准备
-
-1. CPU:2C(最低)8C(推荐)
-2. 内存:4G(最低)48G(推荐)
-3. 硬盘:100G(最低)400G(推荐)
-4. 平台:MacOS(Intel)、LinuxOS、Windows 虚拟机
-5. 系统:CentOS(7.1及以上)、Ubuntu(16.04 及以上)
-6. 软件:JDK(1.8及以上)、GCC(4.8.2 及以上)
-
-## 单机部署
-
-**在创建之前,请准备好已完成编译的 FE/BE 文件,此教程不再赘述编译过程。**
-
-
-**该教程为单节点、1 FE 和 1 BE 的混合部署教程,只需要一个节点,便于快速体验 Doris。**
-
-1. 设置系统最大打开文件句柄数
-
- ```shell
- vi /etc/security/limits.conf
- # 添加以下两行信息
- * soft nofile 65536
- * hard nofile 65536
- # 保存退出并重启服务器
- ```
-
-2. 下载二进制包/自主编译 FE / BE 文件
-
- ```shell
- # 从以下地址选择想要部署的版本
- https://archive.apache.org/dist/doris
- # 例如如下链接
- wget https://archive.apache.org/dist/doris/1.1/1.1.0-rc05/apache-doris-1.1.0-bin-x86-jdk8.tar.gz
- ```
-
-3. 解压缩 tar.gz 文件
+Apache Doris 是一个基于 MPP 架构的高性能、实时的分析型数据库,以极速易用的特点被人们所熟知,仅需亚秒级响应时间即可返回海量数据下的查询结果,不仅可以支持高并发的点查询场景,也能支持高吞吐的复杂分析场景,这个简短的指南将告诉你如何下载 Doris 最新稳定版本,在单节点上安装并运行它,包括创建数据库、数据表、导入数据及查询等。
- ```shell
- tar -zxvf 下载好的二进制压缩包
- # 例如
- tar -zxvf apache-doris-1.1.0-bin-x86-jdk8.tar.gz
- ```
+## 下载 Doris
-4. 迁移解压缩后的程序文件至指定目录并重命名
+Doris 运行在 Linux 环境中,推荐 CentOS 7.x 或者 Ubuntu 16.04 以上版本,同时你需要安装 Java 运行环境(JDK最低版本要求是8),要检查你所安装的 Java 版本,请运行以下命令:
- ```shell
- mv 解压后的根目录 目标路径
- cd 目标路径
- # 例如
- mv apache-doris-1.1.0-bin-x86-jdk8 /opt/doris
- cd /opt/doris
- ```
-
-5. 配置 FE
-
- ```shell
- # 配置 FE-Config
- vi fe/conf/fe.conf
- # 取消 priority_networks 的注释,修改参数
- # 例如当前节点的 IP 地址为 10.10.2.21,则需要更改为 10.10.2.0/24 填入
- # 这里需要填入的是 IP 子网网段地址,并非 IP 地址
- priority_networks = 10.10.2.0/24
- # 保存退出
- ```
+```
+java -version
+```
-6. 配置 BE
+接下来,[下载 Doris 的最新二进制版本](https://doris.apache.org/zh-CN/download),然后解压。
- ```shell
- # 配置 BE-Config
- vi be/conf/be.conf
- # 取消 priority_networks 的注释,修改参数
- # 例如当前节点的 IP 地址为 10.10.2.21,则需要更改为 10.10.2.0/24 填入
- # 这里需要填入的是 IP 子网网段地址,并非 IP 地址
- priority_networks = 10.10.2.0/24
- # 保存退出
- ```
+```
+tar zxf apache-doris-x.x.x.tar.gz
+```
-7. 配置环境变量
+## 配置 Doris
- ```shell
- # 配置环境变量
- vim /etc/profile.d/doris.sh
- export DORIS_HOME=Doris根目录 # 例如/opt/doris
- export PATH=$PATH:$DORIS_HOME/fe/bin:$DORIS_HOME/be/bin
- # 保存并source
- source /etc/profile.d/doris.sh
- ```
+### 配置 FE
-8. 启动 FE 和 BE 并注册 BE 至 FE
+我们进入到 `apache-doris-x.x.x/fe` 目录
- ```shell
- start_fe.sh --daemon
- start_be.sh --daemon
- ```
+```
+cd apache-doris-x.x.x/fe
+```
- 校验 FE 启动是否成功
+修改 FE 配置文件 `conf/fe.conf` ,这里我们主要修改两个参数:`priority_networks` 及 `meta_dir` ,如果你需要更多优化配置,请参考 [FE 参数配置](../admin-manual/config/fe-config)说明,进行调整。
- > 1. 检查是否启动成功,JPS 命令下有没有 PaloFe 进程
- > 2. FE 进程启动后,会先加载元数据,根据 FE 角色的不同,在志中会看到 transfer from UNKNOWN to MASTER/FOLLOWER/OBSERVER 。最终会看到 thrift server started 日志,并且可以通过 mysql 客户端连接到 FE,则 表示 FE 启动成功。
- > 3. 也可以通过如下连接查看是否启动成功: http://fe_host:fe_http_port/api/bootstrap (fe_http_port默认8030)如果返回: {"status":"OK","msg":"Success"} 则表示启动成功,其余情况,则可能存在问题。
- > 4. 外网环境访问 http://fe_host:fe_http_port 查看是否可以访问WebUI界面,登录账号默认为root,密码为空
- > **注:如果在 fe.log 中查看不到启动失败的信息,也许在 fe.out 中可以看到。**
+1. 添加 priority_networks 参数
- 校验 BE 启动是否成功
+```
+priority_networks=172.23.16.0/24
+```
- > 1. BE 进程启动后,如果之前有数据,则可能有数分钟不等的数据索引加载时间。
- > 2. 如果是 BE 的第一次启动,或者该 BE 尚未加载任何集群,则 BE 日志会定期滚动 waiting to receive first heartbeat from frontend 字样。表示 BE 还未通过 FE 的⼼跳收到 Master 的地址,正在被动等待。这种错误日志, 在 FE 中 ADD BACKEND 并发送⼼跳后,就会消失。如果在接到⼼跳后,重复出现 master client, get client from cache failed.host: , port: 0, code: 7 字样,说明 FE 成功连接了 BE,但 BE 无法主动连接 FE,可能需要检查 BE 到 FE 的 rpc_port 的连通性。
- > 3. 如果 BE 已经被加到集群,日志中应该每隔 5 秒滚动来检测 FE 的心跳日志: get heartbeat, host: xx.xx.xx.xx, port: 9020, cluster id: xxxxxx ,表示⼼跳正常。
- > 4. 其次,日志中应该每隔 10 秒滚动 finish report task success. return code: 0 的字样,表示 BE 向 FE 的通信正常。
- > 5. 同时,如果有数据查询,应该能看到不停滚动的日志,并且有 execute time is xxx 日志,表示 BE 启动成功,并且查询正常。
- > 6. 也可以通过如下连接查看是否启动成功: http://be_host:be_http_port/api/health 如果返回: {"status": "OK","msg": "To Be Added"} 则表示启动成功,其余情况,则可能存在问题。
- >
- > **注:如果在 be.INFO 中查看不到启动失败的信息,也许在 be.out 中可以看到。**
+>注意:
+>
+>这个参数我们在安装的时候是必须要配置的,特别是当一台机器拥有多个IP地址的时候,我们要为 FE 指定唯一的IP地址。
- 注册 BE 至 FE(使用MySQL-Client,需自行安装)
+> 这里假设你的节点 IP 是 `172.23.16.32`,那么我们可以通过掩码的方式配置为 `172.23.16.0/24`。
- ```shell
- # 登录,由于是单节点混布,所以 FE_IP 和 BE_IP 为同一个 IP 地址
- mysql -h FE_IP -P 9030 -uroot
- # 注册 BE
- ALTER SYSTEM ADD BACKEND "BE_IP:9050";
- ```
+2. 添加元数据目录
-## Apache Doris 简单使用
+```
+meta_dir=/path/your/doris-meta
+```
-Doris 采用 MySQL 协议进行通信,用户可通过 MySQL Client 或者 MySQL JDBC 连接到 Doris 集群。选择 MySQL Client 版本时建议采用 5.1 之后的版本,因为 5.1 之前不能支持长度超过 16 个字符的用户名。Doris SQL 语法基本覆盖 MySQL 语法。
+>注意:
+>
+>这里你可以不配置,默认是在你的Doris FE 安装目录下的 doris-meta,
+>
+>单独配置元数据目录,需要你提前创建好你指定的目录
-### Apache Doris Web UI 访问
+### 启动 FE
-默认使用 Http 协议进行 WebUI 访问,在浏览器输入以下格式地址访问
+在 FE 安装目录下执行下面的命令,来完成 FE 的启动。
```
-http://FE_IP:FE_HTTP_PORT(默认8030)
+./bin/start_fe.sh --daemon
```
-如在集群安装时修改了账号密码,则使用新的账号密码登陆
+#### 查看 FE 运行状态
+
+你可以通过下面的命令来检查 Doris 是否启动成功
```
-默认账号:root
-默认密码:空
+curl http://127.0.0.1:8030/api/bootstrap
```
-1. WebUI 简介
-
- 在 FE-WEB-UI 首页,罗列了 Version 和 Hardware Info
+这里 IP 和 端口分别是 FE 的 IP 和 http_port(默认8030),如果是你在 FE 节点执行,直接运行上面的命令即可。
- Tab 页有以下六个模块:
+如果返回结果中带有 `"msg":"success"` 字样,则说明启动成功。
- - Playground(可视化 SQL)
+你也可以通过 Doris FE 提供的Web UI 来检查,在浏览器里输入地址
- - System(系统状态)
+http:// fe_ip:8030
- - Log(集群日志)
+可以看到下面的界面,说明 FE 启动成功
- - QueryProfile(SQL 执行日志)
+
- - Session(链接列表)
- - Configuration(配置参数)
-2. 查看 BE 列表
+>注意:
+>
+>1. 这里我们使用 Doris 内置的默认用户 root 进行登录,密码是空
+>2. 这是一个 Doris 的管理界面,只能拥有管理权限的用户才能登录,普通用户不能登录。
- 进入 `System` ——> `backends` 即可查看
+#### 连接 FE
- 需要关注的是 `Alive` 列,该列的 `True ` 和 `False` 代表对应的 BE 节点的正常和异常状态
+我们下面通过 MySQL 客户端来连接 Doris FE,下载免安装的 [MySQL 客户端](https://doris-build-hk.oss-cn-hongkong.aliyuncs.com/mysql-client/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz)
-3. Profile 查询
+解压刚才下载的 MySQL 客户端,在 `bin/` 目录下可以找到 `mysql` 命令行工具。然后执行下面的命令连接 Doris。
- 进入 QueryProfile 可以查看到最近的 100 条 SQL 执行 Report 信息,点击 QueryID 列的指定 ID 可以查看 Profile 信息
-
-### MySQL 命令行/图形化客户端访问
-
-```shell
-# 命令行
-mysql -h FE-host -P 9030 -u用户名 -p密码
-# 客户端(Navicat为例)
-主机:FE-host(若是云服务器,则需要公网IP)
-端口:9030
-用户名:用户名
-密码:密码
+```
+mysql -uroot -P9030 -h127.0.0.1
```
-#### Profile 设置
-
-FE 将查询计划拆分成为 Fragment 下发到 BE 进行任务执行。BE 在执行 Fragment 时记录了**运行状态时的统计值**,并将 Fragment 执行的统计信息输出到日志之中。 FE 也可以通过开关将各个 Fragment 记录的这些统计值进行搜集,并在 FE 的 Web 页面上打印结果。
-
-- 打开 FE 的 Report 开关
-
- ```mysql
- set enable_profile=true;
- ```
-
-- 执行 SQL 语句后,可在 FE 的 WEB-UI 界面查看对应的 SQL 语句执行 Report 信息
-
-如需获取完整的参数对照表,请至 [Profile 参数解析](../admin-manual/query-profile) 查看详情
-
-#### 库表操作
-
-- 查看数据库列表
-
- ```mysql
- SHOW DATABASES;
- ```
-
-- 创建数据库
-
- ```mysql
- CREATE DATABASE 数据库名;
- ```
-
- > 关于 Create-DataBase 使用的更多详细语法及最佳实践,请参阅 [Create Database](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-DATABASE) 命令手册。
- >
- > 如果不清楚命令的全名,可以使用 "help 命令某一字段" 进行模糊查询。如键入 `HELP CREATE`,可以匹配到 `CREATE DATABASE`, `CREATE TABLE`, `CREATE USER` 等命令。
-
- 数据库创建完成之后,可以通过 `SHOW DATABASES;` 查看数据库信息。
+>注意:
+>
+>1. 这里使用的 root 用户是 doris 内置的默认用户,也是超级管理员用户,具体的用户权限查看 [权限管理](../admin-manual/privilege-ldap/user-privilege)
+>2. -P :这里是我们连接 Doris 的查询端口,默认端口是 9030,对应的是fe.conf里的 `query_port`
+>3. -h : 这里是我们连接的 FE IP地址,如果你的客户端和 FE 安装在同一个节点可以使用127.0.0.1,这种也是 Doris 提供的如果你忘记 root 密码,可以通过这种方式不需要密码直接连接登录,进行对 root 密码进行重置
- ```mysql
- MySQL> SHOW DATABASES;
- +--------------------+
- | Database |
- +--------------------+
- | example_db |
- | information_schema |
- +--------------------+
- 2 rows in set (0.00 sec)
- ```
+执行下面的命令查看 FE 运行状态
- `information_schema` 是为了兼容 MySQL 协议而存在,实际中信息可能不是很准确,所以关于具体数据库的信息建议通过直接查询相应数据库而获得。
+```sql
+show frontends\G;
+```
-- 创建数据表
+然后你可以看到类似下面的结果:
+
+```sql
+mysql> show frontends\G;
+*************************** 1. row ***************************
+ Name: 172.21.32.5_9010_1660549353220
+ IP: 172.21.32.5
+ EditLogPort: 9010
+ HttpPort: 8030
+ QueryPort: 9030
+ RpcPort: 9020
+ Role: FOLLOWER
+ IsMaster: true
+ ClusterId: 1685821635
+ Join: true
+ Alive: true
+ReplayedJournalId: 49292
+ LastHeartbeat: 2022-08-17 13:00:45
+ IsHelper: true
+ ErrMsg:
+ Version: 1.1.2-rc03-ca55ac2
+ CurrentConnected: Yes
+1 row in set (0.03 sec)
+```
- > 关于 Create-Table 使用的更多详细语法及最佳实践,请参阅 [Create Table](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE) 命令手册。
+1. 如果 IsMaster、Join 和 Alive 三列均为true,则表示节点正常。
- 使用 `CREATE TABLE` 命令建立一个表(Table)。更多详细参数可以查看:
+#### 停止 FE 节点
- ```mysql
- HELP CREATE TABLE;
- ```
+Doris FE 的停止可以通过下面的命令完成
- 首先切换数据库:
+```
+./bin/stop_fe.sh
+```
- ```mysql
- USE example_db;
- ```
+### 配置 BE
- Doris 支持支持单分区和复合分区两种建表方式(详细区别请参阅 [Create Table](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE) 命令手册)。
+我们进入到 `apache-doris-x.x.x/be` 目录
- 下面以聚合模型为例,演示两种分区的建表语句。
+```
+cd apache-doris-x.x.x/be
+```
- - 单分区
+修改 FE 配置文件 `conf/be.conf` ,这里我们主要修改两个参数:`priority_networks'` 及 `storage_root` ,如果你需要更多优化配置,请参考 [BE 参数配置](../admin-manual/config/be-config)说明,进行调整。
- 建立一个名字为 table1 的逻辑表。分桶列为 siteid,桶数为 10。
+1. 添加 priority_networks 参数
- 这个表的 schema 如下:
+```
+priority_networks=172.23.16.0/24
+```
- - siteid:类型是 INT(4 字节),默认值为 10
- - citycode:类型是 SMALLINT(2 字节)
- - username:类型是 VARCHAR,最大长度为 32,默认值为空字符串
- - pv:类型是 BIGINT(8 字节),默认值是 0;这是一个指标列,Doris 内部会对指标列做聚合操作,这个列的聚合方法是求和(SUM)
+>注意:
+>
+>这个参数我们在安装的时候是必须要配置的,特别是当一台机器拥有多个IP地址的时候,我们要为 BE 指定唯一的IP地址。
- 建表语句如下:
+2. 配置 BE 数据存储目录
- ```mysql
- CREATE TABLE table1
- (
- siteid INT DEFAULT '10',
- citycode SMALLINT,
- username VARCHAR(32) DEFAULT '',
- pv BIGINT SUM DEFAULT '0'
- )
- AGGREGATE KEY(siteid, citycode, username)
- DISTRIBUTED BY HASH(siteid) BUCKETS 10
- PROPERTIES("replication_num" = "1");
- ```
- - 复合分区
+```
+storage_root=/path/your/doris-meta
+```
- 建立一个名字为 table2 的逻辑表。
+>注意:
+>
+>1. 默认目录在 BE安装目录的 storage 目录下。
+>2. BE 配置的存储目录必须先创建好
- 这个表的 schema 如下:
+### 启动 BE
- - event_day:类型是DATE,无默认值
- - siteid:类型是 INT(4 字节),默认值为 10
- - citycode:类型是 SMALLINT(2 字节)
- - username:类型是 VARCHAR,最大长度为 32,默认值为空字符串
- - pv:类型是BIGINT(8 字节),默认值是 0;这是一个指标列,Doris 内部会对指标列做聚合操作,这个列的聚合方法是求和(SUM)
+在 BE 安装目录下执行下面的命令,来完成 BE 的启动。
- 我们使用 event_day 列作为分区列,建立3个分区:p201706、p201707、p201708
+```
+./bin/start_be.sh --daemon
+```
- - p201706:范围为 [最小值, 2017-07-01)
+#### 添加 BE 节点到集群
- - p201707:范围为 [2017-07-01, 2017-08-01)
+通过MySQL 客户端连接到 FE 之后执行下面的 SQL,将 BE 添加到集群中
- - p201708:范围为 [2017-08-01, 2017-09-01)
+```sql
+ALTER SYSTEM ADD BACKEND "be_host_ip:heartbeat_service_port";
+```
- > 注意区间为左闭右开。
+1. be_host_ip:这里是你 BE 的 IP 地址,和你在 `be.conf` 里的 `priority_networks` 匹配
+2. heartbeat_service_port:这里是你 BE 的心跳上报端口,和你在 `be.conf` 里的 `heartbeat_service_port` 匹配,默认是 `9050`。
- 每个分区使用 siteid 进行哈希分桶,桶数为 10
+#### 查看 BE 运行状态
- 建表语句如下:
+你可以在 MySQL 命令行下执行下面的命令查看 BE 的运行状态。
- ```mysql
- CREATE TABLE table2
- (
- event_day DATE,
- siteid INT DEFAULT '10',
- citycode SMALLINT,
- username VARCHAR(32) DEFAULT '',
- pv BIGINT SUM DEFAULT '0'
- )
- AGGREGATE KEY(event_day, siteid, citycode, username)
- PARTITION BY RANGE(event_day)
- (
- PARTITION p201706 VALUES LESS THAN ('2017-07-01'),
- PARTITION p201707 VALUES LESS THAN ('2017-08-01'),
- PARTITION p201708 VALUES LESS THAN ('2017-09-01')
- )
- DISTRIBUTED BY HASH(siteid) BUCKETS 10
- PROPERTIES("replication_num" = "1");
- ```
-
- 表建完之后,可以查看 example_db 中表的信息:
-
- ```mysql
- MySQL> SHOW TABLES;
- +----------------------+
- | Tables_in_example_db |
- +----------------------+
- | table1 |
- | table2 |
- +----------------------+
- 2 rows in set (0.01 sec)
-
- MySQL> DESC table1;
- +----------+-------------+------+-------+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +----------+-------------+------+-------+---------+-------+
- | siteid | int(11) | Yes | true | 10 | |
- | citycode | smallint(6) | Yes | true | N/A | |
- | username | varchar(32) | Yes | true | | |
- | pv | bigint(20) | Yes | false | 0 | SUM |
- +----------+-------------+------+-------+---------+-------+
- 4 rows in set (0.00 sec)
-
- MySQL> DESC table2;
- +-----------+-------------+------+-------+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------+-------------+------+-------+---------+-------+
- | event_day | date | Yes | true | N/A | |
- | siteid | int(11) | Yes | true | 10 | |
- | citycode | smallint(6) | Yes | true | N/A | |
- | username | varchar(32) | Yes | true | | |
- | pv | bigint(20) | Yes | false | 0 | SUM |
- +-----------+-------------+------+-------+---------+-------+
- 5 rows in set (0.00 sec)
- ```
-
- > 注意事项:
- >
- > 1. 上述表通过设置 replication_num 建的都是单副本的表,Doris 建议用户采用默认的 3 副本设置,以保证高可用。
- > 2. 可以对复合分区表动态的增删分区。详见 `HELP ALTER TABLE` 中 Partition 相关部分。
- > 3. 数据导入可以导入指定的 Partition。详见 `HELP LOAD`。
- > 4. 可以动态修改表的 Schema。
- > 5. 可以对 Table 增加上卷表(Rollup)以提高查询性能,这部分可以参见高级使用指南关于 Rollup 的描述。
- > 6. 表的列的 Null 属性默认为 true,会对查询性能有一定的影响。
-
-#### 插入数据
-
-1. Insert Into 插入
-
- > 关于 Insert 使用的更多详细语法及最佳实践,请参阅 [Insert](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT) 命令手册。
-
- Insert Into 语句的使用方式和 MySQL 等数据库中 Insert Into 语句的使用方式类似。但在 Doris 中,所有的数据写入都是一个独立的导入作业。所以这里将 Insert Into 也作为一种导入方式介绍。
-
- 主要的 Insert Into 命令包含以下两种:
-
- - INSERT INTO tbl SELECT ...
- - INSERT INTO tbl (col1, col2, ...) VALUES (1, 2, ...), (1,3, ...);
-
- 其中第二种命令仅用于 Demo,不要使用在测试或生产环境中。
-
- Insert Into 命令需要通过 MySQL 协议提交,创建导入请求会同步返回导入结果。
-
- 语法:
-
- ```mysql
- INSERT INTO table_name [partition_info] [WITH LABEL label] [col_list] [query_stmt] [VALUES];
- ```
-
- 示例:
-
- ```mysql
- INSERT INTO tbl2 WITH LABEL label1 SELECT * FROM tbl3;
- INSERT INTO tbl1 VALUES ("qweasdzxcqweasdzxc"),("a");
- ```
-
- **注意**
-
- 当需要使用 `CTE(Common Table Expressions)` 作为 Insert 操作中的查询部分时,必须指定 `WITH LABEL` 和 `column list` 部分。示例
-
- ```mysql
- INSERT INTO tbl1 WITH LABEL label1
- WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
- SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;
-
- INSERT INTO tbl1 (k1)
- WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
- SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;
- ```
-
- Insert Into 本身就是一个 SQL 命令,其返回结果会根据执行结果的不同,分为以下几种:
-
- - 如果返回结果为 `ERROR 1064 (HY000)`,则表示导入失败。
- - 如果返回结果为 `Query OK`,则表示执行成功。
- - 如果 `rows affected` 为 0,表示结果集为空,没有数据被导入。
- - 如果 `rows affected` 大于 0:
- - 如果 `status` 为 `committed`,表示数据还不可见。需要通过 `show transaction` 语句查看状态直到 `visible`
- - 如果 `status` 为 `visible`,表示数据导入成功。
- - 如果 `warnings` 大于 0,表示有数据被过滤,可以通过 `show load` 语句获取 url 查看被过滤的行。
-
- 更多详细说明,请参阅 [Insert](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT) 命令手册。
-
-2. 批量导入
-
- Doris 支持多种数据导入方式。具体可以参阅数据导入文档。这里我们使用流式导入(Stream-Load)和 Broker-Load 导入做示例。
-
- - Stream-Load
-
- > 关于 Stream-Load 使用的更多详细语法及最佳实践,请参阅 [Stream-Load](../sql-manual/sql-reference/Data-Manipulation-Statements/Load/STREAM-LOAD) 命令手册。
-
- 流式导入通过 HTTP 协议向 Doris 传输数据,可以不依赖其他系统或组件直接导入本地数据。详细语法帮助可以参阅 `HELP STREAM LOAD;`。
-
- 示例1:以 "table1_20170707" 为 Label,使用本地文件 table1_data 导入 table1 表。
-
- ```CQL
- 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 是任一 FE 所在节点 IP,8030 为 fe.conf 中的 http_port。
- > 2. 可以使用任一 BE 的 IP,以及 be.conf 中的 webserver_port 进行导入。如:`BE_HOST:8040`
- > 3. example_db 是你导入表所在的数据库
-
- 本地文件 `table1_data` 以 `,` 作为数据之间的分隔,具体内容如下:
-
- ```text
- 1,1,jim,2
- 2,1,grace,2
- 3,2,tom,2
- 4,3,bush,3
- 5,3,helen,3
- ```
-
- 示例2:以 "table2_20170707" 为 Label,使用本地文件 table2_data 导入 table2 表。
-
- ```CQL
- 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
- ```
+```sql
+SHOW BACKENDS\G;
+```
- 本地文件 `table2_data` 以 `|` 作为数据之间的分隔,具体内容如下:
+示例:
+
+```sql
+mysql> SHOW BACKENDS\G;
+*************************** 1. row ***************************
+ BackendId: 10003
+ Cluster: default_cluster
+ IP: 172.21.32.5
+ HeartbeatPort: 9050
+ BePort: 9060
+ HttpPort: 8040
+ BrpcPort: 8060
+ LastStartTime: 2022-08-16 15:31:37
+ LastHeartbeat: 2022-08-17 13:33:17
+ Alive: true
+ SystemDecommissioned: false
+ClusterDecommissioned: false
+ TabletNum: 170
+ DataUsedCapacity: 985.787 KB
+ AvailCapacity: 782.729 GB
+ TotalCapacity: 984.180 GB
+ UsedPct: 20.47 %
+ MaxDiskUsedPct: 20.47 %
+ Tag: {"location" : "default"}
+ ErrMsg:
+ Version: 1.1.2-rc03-ca55ac2
+ Status: {"lastSuccessReportTabletsTime":"2022-08-17 13:33:05","lastStreamLoadTime":-1,"isQueryDisabled":false,"isLoadDisabled":false}
+1 row in set (0.01 sec)
+```
- ```text
- 2017-07-03|1|1|jim|2
- 2017-07-05|2|1|grace|2
- 2017-07-12|3|2|tom|2
- 2017-07-15|4|3|bush|3
- 2017-07-12|5|3|helen|3
- ```
+1. Alive : true表示节点运行正常
- > 注意事项:
- >
- > 1. 采用流式导入建议文件大小限制在 10GB 以内,过大的文件会导致失败重试代价变大。
- > 2. 每一批导入数据都需要取一个 Label,Label 最好是一个和一批数据有关的字符串,方便阅读和管理。Doris 基于 Label 保证在一个 Database 内,同一批数据只可导入成功一次。失败任务的 Label 可以重用。
- > 3. 流式导入是同步命令。命令返回成功则表示数据已经导入,返回失败表示这批数据没有导入。
+#### 停止 BE 节点
- - Broker-Load
+Doris BE 的停止可以通过下面的命令完成
- Broker 导入通过部署的 Broker 进程,读取外部存储上的数据进行导入。
-
- > 关于 Broker Load 使用的更多详细语法及最佳实践,请参阅 [Broker Load](../sql-manual/sql-reference/Data-Manipulation-Statements/Load/BROKER-LOAD) 命令手册,你也可以在 MySql 客户端命令行下输入 `HELP BROKER LOAD` 获取更多帮助信息。
-
- 示例:以 "table1_20170708" 为 Label,将 HDFS 上的文件导入 table1 表
-
- ```mysql
- LOAD LABEL table1_20170708
- (
- DATA INFILE("hdfs://your.namenode.host:port/dir/table1_data")
- INTO TABLE table1
- )
- WITH BROKER hdfs
- (
- "username"="hdfs_user",
- "password"="hdfs_password"
- )
- PROPERTIES
- (
- "timeout"="3600",
- "max_filter_ratio"="0.1"
- );
- ```
-
- Broker 导入是异步命令。以上命令执行成功只表示提交任务成功。导入是否成功需要通过 `SHOW LOAD;` 查看。如:
-
- ```mysql
- SHOW LOAD WHERE LABEL = "table1_20170708";
- ```
-
- 返回结果中,`State` 字段为 FINISHED 则表示导入成功。
-
- 关于 `SHOW LOAD` 的更多说明,可以参阅 `HELP SHOW LOAD;`
-
- 异步的导入任务在结束前可以取消:
-
- ```mysql
- CANCEL LOAD WHERE LABEL = "table1_20170708";
- ```
-
-#### 查询数据
-
-1. 简单查询
+```
+./bin/stop_be.sh
+```
- ```mysql
- MySQL> SELECT * FROM table1 LIMIT 3;
- +--------+----------+----------+------+
- | siteid | citycode | username | pv |
- +--------+----------+----------+------+
- | 2 | 1 | 'grace' | 2 |
- | 5 | 3 | 'helen' | 3 |
- | 3 | 2 | 'tom' | 2 |
- +--------+----------+----------+------+
- 3 rows in set (0.01 sec)
-
- MySQL> SELECT * FROM table1 ORDER BY citycode;
- +--------+----------+----------+------+
- | siteid | citycode | username | pv |
- +--------+----------+----------+------+
- | 2 | 1 | 'grace' | 2 |
- | 1 | 1 | 'jim' | 2 |
- | 3 | 2 | 'tom' | 2 |
- | 4 | 3 | 'bush' | 3 |
- | 5 | 3 | 'helen' | 3 |
- +--------+----------+----------+------+
- 5 rows in set (0.01 sec)
- ```
+## 创建数据表
-2. Join 查询
+1. 创建一个数据库
- ```mysql
- MySQL> SELECT SUM(table1.pv) FROM table1 JOIN table2 WHERE table1.siteid = table2.siteid;
- +--------------------+
- | sum(`table1`.`pv`) |
- +--------------------+
- | 12 |
- +--------------------+
- 1 row in set (0.20 sec)
- ```
+```sql
+create database demo;
+```
-3. 子查询
+2. 创建数据表
+
+```sql
+use demo;
+
+CREATE TABLE IF NOT EXISTS demo.expamle_tbl
+(
+ `user_id` LARGEINT NOT NULL COMMENT "用户id",
+ `date` DATE NOT NULL COMMENT "数据灌入日期时间",
+ `city` VARCHAR(20) COMMENT "用户所在城市",
+ `age` SMALLINT COMMENT "用户年龄",
+ `sex` TINYINT COMMENT "用户性别",
+ `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
+ `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
+ `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
+ `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
+)
+AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
+PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+);
+```
- ```mysql
- MySQL> SELECT SUM(pv) FROM table2 WHERE siteid IN (SELECT siteid FROM table1 WHERE siteid > 2);
- +-----------+
- | sum(`pv`) |
- +-----------+
- | 8 |
- +-----------+
- 1 row in set (0.13 sec)
- ```
-
-#### 更新数据
-
-> 关于 Update 使用的更多详细语法及最佳实践,请参阅 [Update](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE) 命令手册。
-
-当前 UPDATE 语句**仅支持**在 Unique 模型上的行更新,存在并发更新导致的数据冲突可能。目前 Doris 并不处理这类问题,需要用户从业务侧规避这类问题。
-
-1. 语法规则
-
- ```text
- UPDATE table_name
- SET assignment_list
- WHERE expression
-
- value:
- {expr | DEFAULT}
-
- assignment:
- col_name = value
-
- assignment_list:
- assignment [, assignment] ...
- ```
-
- **参数说明**
-
- - table_name:待更新数据的目标表。可以是 'db_name.table_name' 形式
- - assignment_list:待更新的目标列,形如 'col_name = value, col_name = value' 格式
- - where expression:期望更新的条件,一个返回 true 或者 false 的表达式即可
+3. 示例数据
-2. 示例
+```
+10000,2017-10-01,北京,20,0,2017-10-01 06:00:00,20,10,10
+10000,2017-10-01,北京,20,0,2017-10-01 07:00:00,15,2,2
+10001,2017-10-01,北京,30,1,2017-10-01 17:05:45,2,22,22
+10002,2017-10-02,上海,20,1,2017-10-02 12:59:12,200,5,5
+10003,2017-10-02,广州,32,0,2017-10-02 11:20:00,30,11,11
+10004,2017-10-01,深圳,35,0,2017-10-01 10:00:15,100,3,3
+10004,2017-10-03,深圳,35,0,2017-10-03 10:20:22,11,6,6
+```
- `test` 表是一个 Unique 模型表,包含 k1、k2、v1、v2 四个列。其中 k1、k2 是 key,v1、v2 是 value,聚合方式是 Replace。
+将上面的数据保存在一个test.csv文件中。
- 1. 将 'test' 表中满足条件 k1 = 1 , k2 = 2 的 v1 列更新为 1
-
- ```sql
- UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;
- ```
-
- 2. 将 'test' 表中 k1 = 1 的列的 v1 列自增1
-
- ```sql
- UPDATE test SET v1 = v1+1 WHERE k1=1;
- ```
-
-#### 删除数据
-
-> 关于 Delete 使用的更多详细语法及最佳实践,请参阅 [Delete](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md) 命令手册。
-
-1. 语法规则
-
- 该语句用于按条件删除指定 table(base index) partition 中的数据。
-
- 该操作会同时删除和此 base index 相关的 rollup index 的数据。
- 语法:
-
- ```sql
- DELETE FROM table_name [PARTITION partition_name | PARTITIONS (p1, p2)]
- WHERE
- column_name1 op { value | value_list } [ AND column_name2 op { value | value_list } ...];
- ```
-
- 说明:
-
- - op 的可选类型包括:=、>、<、>=、<=、!=、in、not in。
+4. 导入数据
- - 只能指定 key 列上的条件。
+这里我们通过Stream load 方式将上面保存到文件中的数据导入到我们刚才创建的表里。
- - 当选定的 key 列不存在于某个 rollup 中时,无法进行 delete。
+```
+curl --location-trusted -u root: -T test.csv -H "column_separator:," http://127.0.0.1:8030/api/demo/expamle_tbl/_stream_load
+```
- - 条件之间只能是“与”的关系。
+- -T test.csv : 这里使我们刚才保存的数据文件,如果路径不一样,请指定完整路径
+- -u root : 这里是用户名密码,我们使用默认用户root,密码是空
+- 127.0.0.1:8030 : 分别是 fe 的 ip 和 http_port
- 若希望达成“或”的关系,需要将条件分写在两个 DELETE 语句中。
+执行成功之后我们可以看到下面的返回信息
- - 如果为分区表,可以指定分区,如不指定,且会话变量 delete_without_partition 为 true,则会应用到所有分区。如果是单分区表,可以不指定。
+```json
+{
+ "TxnId": 30303,
+ "Label": "8690a5c7-a493-48fc-b274-1bb7cd656f25",
+ "TwoPhaseCommit": "false",
+ "Status": "Success",
+ "Message": "OK",
+ "NumberTotalRows": 7,
+ "NumberLoadedRows": 7,
+ "NumberFilteredRows": 0,
+ "NumberUnselectedRows": 0,
+ "LoadBytes": 399,
+ "LoadTimeMs": 381,
+ "BeginTxnTimeMs": 3,
+ "StreamLoadPutTimeMs": 5,
+ "ReadDataTimeMs": 0,
+ "WriteDataTimeMs": 191,
+ "CommitAndPublishTimeMs": 175
+}
+```
- 注意:
+1. `NumberLoadedRows`: 表示已经导入的数据记录数
+
+2. `NumberTotalRows`: 表示要导入的总数据量
+
+3. `Status` :Success 表示导入成功
+
+到这里我们已经完成的数据导入,下面就可以根据我们自己的需求对数据进行查询分析了。
+
+## 查询数据
+
+我们上面完成了建表,输数据导入,下面我们就可以体验 Doris 的数据快速查询分析能力。
+
+```sql
+mysql> select * from expamle_tbl;
++---------+------------+--------+------+------+---------------------+------+----------------+----------------+
+| user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
++---------+------------+--------+------+------+---------------------+------+----------------+----------------+
+| 10000 | 2017-10-01 | 北京 | 20 | 0 | 2017-10-01 07:00:00 | 35 | 10 | 2 |
+| 10001 | 2017-10-01 | 北京 | 30 | 1 | 2017-10-01 17:05:45 | 2 | 22 | 22 |
+| 10002 | 2017-10-02 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
+| 10003 | 2017-10-02 | 广州 | 32 | 0 | 2017-10-02 11:20:00 | 30 | 11 | 11 |
+| 10004 | 2017-10-01 | 深圳 | 35 | 0 | 2017-10-01 10:00:15 | 100 | 3 | 3 |
+| 10004 | 2017-10-03 | 深圳 | 35 | 0 | 2017-10-03 10:20:22 | 11 | 6 | 6 |
++---------+------------+--------+------+------+---------------------+------+----------------+----------------+
+6 rows in set (0.02 sec)
+
+mysql> select * from expamle_tbl where city='上海';
++---------+------------+--------+------+------+---------------------+------+----------------+----------------+
+| user_id | date | city | age | sex | last_visit_date | cost | max_dwell_time | min_dwell_time |
++---------+------------+--------+------+------+---------------------+------+----------------+----------------+
+| 10002 | 2017-10-02 | 上海 | 20 | 1 | 2017-10-02 12:59:12 | 200 | 5 | 5 |
++---------+------------+--------+------+------+---------------------+------+----------------+----------------+
+1 row in set (0.05 sec)
+
+mysql> select city, sum(cost) as total_cost from expamle_tbl group by city;
++--------+------------+
+| city | total_cost |
++--------+------------+
+| 广州 | 30 |
+| 上海 | 200 |
+| 北京 | 37 |
+| 深圳 | 111 |
++--------+------------+
+4 rows in set (0.05 sec)
+```
- - 该语句可能会降低执行后一段时间内的查询效率。
- - 影响程度取决于语句中指定的删除条件的数量。
- - 指定的条件越多,影响越大。
-2. 示例
- 1. 删除 my_table partition p1 中 k1 列值为 3 的数据行
-
- ```sql
- DELETE FROM my_table PARTITION p1 WHERE k1 = 3;
- ```
-
- 2. 删除 my_table partition p1 中 k1 列值大于等于 3 且 k2 列值为 "abc" 的数据行
-
- ```sql
- DELETE FROM my_table PARTITION p1 WHERE k1 >= 3 AND k2 = "abc";
- ```
-
- 3. 删除 my_table partition p1、p2 中 k1 列值大于等于 3 且 k2 列值为 "abc" 的数据行
-
- ```sql
- DELETE FROM my_table PARTITIONS (p1, p2) WHERE k1 >= 3 AND k2 = "abc";
- ```
-
-
+到这里我们整个快速开始就结束了,我们从 Doris 安装部署、启停、创建库表、数据导入及查询,完整的体验了Doris的操作流程,下面开始我们 Doris 使用之旅吧。
diff --git a/sidebars.json b/sidebars.json
index 26d7a10c637..8216b16940e 100644
--- a/sidebars.json
+++ b/sidebars.json
@@ -37,7 +37,6 @@
"data-table/data-model",
"data-table/data-partition",
"data-table/basic-usage",
- "data-table/advance-usage",
"data-table/hit-the-rollup",
"data-table/best-practice",
{
diff --git a/static/images/image-20220822091951739.png b/static/images/image-20220822091951739.png
new file mode 100644
index 00000000000..ab32477df18
Binary files /dev/null and b/static/images/image-20220822091951739.png differ
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org