This document mainly describes the table and data model of Doris from a logical perspective so you can make better use of Doris in different business scenarios.
Data Table
In Doris, data is logically described in the form of table. A table is a collection of homogeneous data with the same pattern. A table includes rows and columns. A row is a row of user data. Column is used to describe different fields in a row of data and can adopt different data types (such as integer, character string, and boolean, etc.) according to actual situations.
In the OLAP scenario, columns can be divided into two categories: key and value. Key represents dimension columns, and value represents metric columns.
Data Model
The data model of Doris is mainly divided into three categories: Aggregate, Unique and Duplicate.
Aggregate Model
We illustrate what aggregation model is and how to use it correctly with practical examples.
Example 1: Importing Data Aggregation
Assume that the business has the following data table schema:
The corresponding table to CREATE TABLE statement would be as follows (omitting the Partition and Distribution information):
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "user id",
`date` DATE NOT NULL COMMENT "data import date and time",
`city` VARCHAR(20) COMMENT "user's city",
`age` SMALLINT COMMENT "user's age",
`sex` TINYINT COMMENT "user's gender",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "last visit time of user",
`cost` BIGINT SUM DEFAULT "0" COMMENT "total consumption of user",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "user's maximum stay time",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "user's minimum stay time"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
As you can see, this is a typical fact table of user information and visit behaviors. In star models, user information and visit behaviors are usually stored in dimension tables and fact tables, respectively. Here, for the purposes of explanation, we store the two types of information in one single table.
The columns in the table are divided into Key (dimension column) and Value (index column) according to whether they are set with an AggregationType. Those without a set AggregationType, such as user_id, date, age, and sex, are referred to as keys, while those with a set AggregationType are referred to as values.
When we import data, rows with the same key column will be aggregated into one row, and their values in the Value columns will be aggregated according to the set AggregationType. The AggregationType currently has the following four ways of aggregation:
1. SUM: total sum, the values of multiple rows are accumulated.
2. REPLACE: Replacement. The newly imported value will replace the previous value.
3. MAX: Retain maximum value.
4. MIN: Retain minimum value.
Assume we have the following imported data (original data):
Assume this is a table that records the user behaviors when visiting a certain commodity page. We will explain the first row of data as follows:
|
| User ID, each user's unique identification ID |
| Data storage time, accurate to the day |
| |
| |
| Gender Male (1 for Female) |
| The user’s time to visit this page, accurate to the second |
| The consumption generated by the user's visit this time |
| User's visit, time to stay on the page |
| User's visit, time spent on the page (Redundancy) |
So when these data are correctly imported into Doris, the final Storage in Doris is as follows:
As can be seen, the data of user 10000 have been aggregated to one row after Aggregate. The data of other users remain the same. The explanation for the aggregated data of user 10000 is as follows (The first five columns remain unchanged, starting with column 6?last_visit_date?:
2017-10-01 07:00:00: The last_visit_date column is aggregated by REPLACE, so 2017-10-01 07:00:00 has replaced 2017-10-01 06:00:00.
Note
When using REPLACE to aggregate data from the same import batch, the order of replacement is uncertain. That means, in this case, the data eventually saved Doris could be 2017-10-01 06:00:00. However, for different import batches, it is certain that data from the data from the new batch will replace those from the old batch.
35: The cost column is aggregated by SUM, so the update value 35 is the result of 20 + 15.
10: The max_dwell_time column is aggregated by MAX, so 10 is saved as it is the maximum between 10 and 2.
2: The min_dwell_time column is aggregated by MIN, so 2 is saved as it is the minimum between 10 and 2.
After aggregation, Doris will only store the aggregated data. In other words, the detailed raw data will no longer be available.
Example 2: Keep Detailed Data
Here is a modified version of the table schema in Example 1:
|
| | | |
| | | |
| | | Data import time precise to the second |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
One column has been added timestamp that records the data import time accurate to the second.
Also, set AGGREGATE KEY as AGGREGATE KEY (user_id, date, timestamp, city, age, sex).
The imported data are as follows:
So when these data are correctly imported into Doris, the final Storage in Doris is as follows:
We can see that the stored data are exactly the same as the imported data, without any aggregation. This is because, this newly added timestamp column results in difference of keys among the rows. That is to say, as long as the Keys of the rows are not exactly the same in the import data, Doris can also save complete detailed data even in the Aggregate Model.
Example 3: Aggregate Imported Data and the Existing Data
Based on Example 1, suppose that you have the following data stored in Doris:
We will import another batch of data:
So when these data are correctly imported into Doris, the final Storage in Doris is as follows:
As you can see that the existing data and newly imported data of user 10004 have been aggregated. At the same time, the new data of user 10005 have been added.
Data aggregation occurs in the following three stages in Doris:
1. The ETL stage of each batch of import data. At this stage, the batch of import data will be aggregated internally.
2. The data compaction stage of the underlying BE. AT this stage, BE will aggregate data from different batches that have been imported.
3. Data query stage. The data involved in the query is aggregated accordingly.
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. However, for users, they can only query the aggregated data. That is, what users see are the aggregated data, and they should not assume that what they have seen are not aggregated.
Unique model
In some multi-dimensional analysis scenarios, what users care more is how to ensure the uniqueness of keys, that is, how to get primary key uniqueness constraints. Therefore, we introduced the unique data model. This model is essentially a special case of the aggregation model and a simplified way of representing the table structure. For example:
This is a typical user basic information table. This kind of data doesn't need aggregation, we just need to ensure the uniqueness of the primary key. (Here the primary key is user_id + username). Therefore, our table creation statement is as follows:
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "user id",
`username` VARCHAR(50) NOT NULL COMMENT "User Nickname",
`city` VARCHAR(20) COMMENT "user's city",
`age` SMALLINT COMMENT "user's age",
`sex` TINYINT COMMENT "user's gender",
`phone` LARGEINT COMMENT "User's Phone Number",
`address` VARCHAR(500) COMMENT "User's Address",
`register_time` DATETIME COMMENT "User Registration Time"
)
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
The structure of this table matches completely to the structure of the table described by the following Aggregate model:
And table creation statement:
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "user id",
`username` VARCHAR(50) NOT NULL COMMENT "User Nickname",
`city` VARCHAR(20) REPLACE COMMENT "City where the user is located",
`age` SMALLINT REPLACE COMMENT "User Age",
`sex` TINYINT REPLACE COMMENT "User Gender",
`phone` LARGEINT REPLACE COMMENT "User's Phone Number",
`address` VARCHAR(500) REPLACE COMMENT "User's Address",
`register_time` DATETIME REPLACE COMMENT "User Registration Time"
)
AGGREGATE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
The Unique model can be completely replaced by the REPLACE method in the Aggregate model. Its internal implementation method and data storage method are exactly the same. We will not continue to give examples here.
Duplicate model
In some multi-dimensional analysis scenarios, data neither has primary keys, nor aggregate requirements. Therefore, we introduce the duplicate data model to meet these needs. Here is an example.
|
| | | |
| | | |
| | | |
| | | Detailed Error Information |
| | | |
| | | |
The table creation statement is as follows:
CREATE TABLE IF NOT EXISTS example_db.expamle_tbl
(
`timestamp` DATETIME NOT NULL COMMENT "Log time",
`type` INT NOT NULL COMMENT "Log type",
`error_code` INT COMMENT "Error code",
`error_msg` VARCHAR(1024) COMMENT "Detailed error info",
`op_id` BIGINT COMMENT "Person in charge ID",
`op_time` DATETIME COMMENT "Processing time"
)
DUPLICATE KEY(`timestamp`, `type`)
DISTRIBUTED BY HASH(`type`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
This data model differs from the Aggregate and Unique models. Data is stored according to the data in the imported file without any aggregation. Even if two rows of data are identical, they will both be retained. The DUPLICATE KEY specified in the table creation statement is only used to specify based on which columns the data are ordered by. When choosing DUPLICATE KEY, we recommend the first 2-4 columns. This data model is suitable for storing raw data without aggregation requirements or primary key uniqueness constraints.
Limitations of the Aggregate Model
Aggregate Model & Unique Model
Here we introduce the limitations of the Aggregate model (including the Unique model).
In the Aggregate model, what the model presents is the data finally aggregated. That is, for any data not yet aggregated (for example, the data of two different import batches), a certain way must be ensured to present consistency.
Assume the table structure is as follows:
Assume there are the following two batches of data that have been successfully imported into the storage engine:
Batch 1
batch 2
As you can see, the data of user 10001 in the two different import batches have not been aggregated yet. However, in order to ensure that the user can only query the following aggregated data as follows:
We have added an aggregation operator to the query engine to ensure the presentation consistency of data.
Moreover, on the aggregate column (Value), when executing aggregate class queries that are inconsistent with the aggregate type, please pay attention to the semantics. For example, if we execute the following query in the above example:
SELECT MIN(cost) FROM table;
The result will be 5, not 1.
At the same time, this guarantee of consistency will significantly reduce efficiency in some queries.
Let's use the most basic count(*) query as an example:
SELECT COUNT(*) FROM table;
In other databases, such queries return results quickly. This is because in actual implementation, the models can get the query result by counting rows and saving the statistics upon import, or by scanning only a particular column of data to get the count value during the query, with very little overhead. However, in Doris's Aggregation model, the cost of this kind of query is very large.
Let's take our previous data as an example:
batch 1
batch 2
Since the final Aggregate results as:
Therefore, the correct result of select count(*) from table; should be 4. But if the model only scans the user_id column and operates aggregation upon query, the final result will be 3(10001, 10002, 10003). And if it does not operate aggregation, the final result will be 5 (a total of five rows in two batches). You can see that both of these results are incorrect.
In order to get the correct result, we must read both the user_id and date column, and perform aggregation when querying, to return the correct result of 4. That is to say, in a count( ) query, Doris must scan all the AGGREGATE KEY columns (in this case, user_id and date), and aggregate them to get the semantically correct results. That means if there are many aggregated columns, count( ) queries could involve scanning large amounts of data.
Therefore, if you need to perform frequent count( ) queries, we recommend users to simulate count( ) by adding a column **which value is always 1 and the aggregation type SUM)**. In this way, the table schema in the previous example will be modified as follows:
Add a count column, and in the data import, the value of this column is always 1. Then the result of select count(*) from table; is equivalent to select sum(count) from table;. The latter's query efficiency is much higher than the former. However, this approach has limitations in use, that is, users need to ensure that they do not import rows with the same AGGREGATE KEY column. Otherwise, select sum(count) from table; can only represent the number of rows in the original import, not the semantics of select count(*) from table;, and the former value will erroneously increase.
Another way is to change the aggregation type of the above count column to REPLACE, and keep its value constant at 1. Then, the results of select sum(count) from table; and select count(*) from table; will be consistent. Moreover, this approach has no restriction on the import of duplicate rows.
Duplicate model
The Duplicate model does not impose the same limitation as the Aggregate model because it does not involve aggregation semantics. For any columns, it can return the semantically correct results in count(*) queries.
Best Practice
Since the data model is determined when creating the table and cannot be modified. Therefore, choosing a suitable data model is very important.
Choosing a Data Model
There are currently three types of Doris data models: AGGREGATE KEY, UNIQUE KEY, and DUPLICATE KEY. Data in the three models are all ordered according to the KEY.
Aggregate Model
The Aggregate model can significantly reduce the amount of data scanned and the calculation load during aggregation queries by Pre-Aggregate, which is very suitable for fixed pattern reporting query scenarios. However, this model is not friendly for count( *) queries. Also, because the aggregation method on the Value column is fixed, the semantic correctness needs to be considered when conducting other types of aggregation queries.
When the Aggregate Key is the same, the new and old records are aggregated, and the currently supported aggregation functions are SUM, MIN, MAX, and REPLACE.
CREATE TABLE site_visit
(
siteid INT,
city SMALLINT,
username VARCHAR(32),
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY (siteid, city, username)
DISTRIBUTED BY HASH (siteid) BUCKETS 10;
Unique model
The Unique model is designed for scenarios that need unique primary key constraints. When the Unique key is the same, the new record replaces the old one, ensuring the uniqueness constraint of the primary key. It is suitable for analysis businesses with update requirements. The current implementation of Unique key is the same as the REPLACE aggregation method of Aggregate key, which are essentially the same. However, it can't take advantage of the query advantages brought by Pre-Aggregate such as ROLLUP (because the essence is REPLACE, there is no SUM, etc. of the aggregation method).
Note
The Unique model only supports whole-row updates. If users need both unique primary key constraints and updating of specific columns only (for instance, merging columns from multiple source tables into one Doris table), the Aggregate model might be considered, setting the aggregation type of non-primary key columns to REPLACE_IF_NOT_NULL. For specific usage, see Data Model. CREATE TABLE sales_order
(
orderid BIGINT,
status TINYINT,
username VARCHAR(32),
amount BIGINT DEFAULT '0'
)
UNIQUE KEY(orderid)
DISTRIBUTED BY HASH(orderid) BUCKETS 10;
Duplicate model
The Duplicate model does not merge identical rows and is suitable for Ad-hoc queries of any dimension. Although it can't use the characteristics of Pre-Aggregate, it is not subject to the constraints of the Aggregate model, and it can take advantage of the column storage model (column pruning, vector execution, etc.).
CREATE TABLE session_data
(
visitorid SMALLINT,
sessionid BIGINT,
visittime DATETIME,
city CHAR(20),
province CHAR(20),
ip varchar(32),
brower CHAR(20),
url VARCHAR(1024)
)
DUPLICATE KEY(visitorid, sessionid)
DISTRIBUTED BY HASH(sessionid, visitorid) BUCKETS 10;
Large Wide Tables and Star Schema
When business party creates tables, they usually do not distinguish between dimension information and metric information, and define the Schema into a large wide table, in order to adapt to front-end businesses. For Doris, the performance of these large wide tables is not satisfactory:
There are many fields in the Schema, and there may be more key columns in the aggregation model, which will increase the columns to be ordered during the import process.
The update of dimension information will be reflected in the entire table, and its update frequency directly affects query efficiency.
In use, the user is advised to use the Star Schema to distinguish between dimension tables and metric tables. Frequently updated dimension tables can also be placed in an external table in MySQL. And if there are only a few updates, it can be placed directly in Doris. When a dimension table is stored in Doris, more replicas can be set for the dimension table to improve the performance of Join.
