Selection of the Doris Data Model and Its Impacts

Aggregate and Unique

In the Doris data model, Aggregate and Unique are two important attributes that help improve data storage efficiency and query execution speed.

Aggregate

What is Aggregate?

Aggregate (aggregation) refers to performing certain operations on data to generate new values or totals. For example, calculating the average age or average rating for each user.

How Aggregate affects the data model

When using Aggregate, Doris aggregates related data and stores it in a single unit, which reduces storage space and improves query execution speed.

example

CREATE TABLE users (
  id INT,
  name VARCHAR(255),
  age INT,
  total_score DECIMAL(10,2)
);

INSERT INTO users (id, name, age) VALUES (1, 'John', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Jane', 30);

In this case, total_score is an Aggregate, and Doris will calculate the sum of scores for the two users.

impact

Using Aggregate can reduce storage space and improve query execution speed.

Unique

What is Unique?

Unique (uniqueness) refers to ensuring that each record in the data is unique, thereby avoiding duplicate data and improving data storage efficiency.

How Unique affects the data model

When using Unique, Doris creates an index to store this data, which can reduce query execution time.

example

CREATE TABLE users (
  id INT,
  name VARCHAR(255),
  age INT
);

INSERT INTO users (id, name) VALUES (1, 'John');
INSERT INTO users (id, name) VALUES (2, 'Jane');

In this case, Doris creates an index to store the names of these users, enabling fast retrieval of all unique names.

impact

Using Unique can improve data storage efficiency and query execution speed.

Primary Key and Composite Primary Key

Primary Key (Primary Key)

A primary key refers to one or more columns in a table used to uniquely identify each record. For example, in the users table, id is a primary key.

How Primary Key affects the data model

When using a primary key, Doris creates an index to store this data, enabling fast retrieval of all records.

Composite Primary Key (Composite Primary Key)

A composite primary key refers to multiple columns in a table serving as the primary key. For example, in the users table, we can use id and name as a composite primary key.

How Composite Primary Key affects the data model

When using a composite primary key, Doris creates a complex index to store this data, enabling fast retrieval of all records.

impact

Using primary keys and composite primary keys can improve data storage efficiency and query execution speed.

Schema

What is Schema?

Schema is a set of rules representing the data structure. Doris creates tables based on the schema we define.

How Schema affects the data model

When using Schema, Doris creates tables based on the schema we define, which facilitates the management of data storage and retrieval.

example

CREATE TABLE users (
  id INT,
  name VARCHAR(255),
  age INT
);

ALTER TABLE users ADD COLUMN total_score DECIMAL(10,2);

In this case, we use Schema to define the structure of the users table, facilitating the management of data storage and retrieval.

impact

Using Schema can improve data storage efficiency and facilitate data management.