Unique Key table

When you create a table, you can define unique key columns and metric columns. This way, queries return the most recent record among a group of records that have the same values in the unique key columns. Compared with the Duplicate Key table, the Unique Key table simplifies the data loading process to better support real-time and frequent data updates.

Scenarios

The Unique Key table is suitable for business scenarios in which data needs to be frequently updated in real time. For example, in e-commerce scenarios, hundreds of millions of orders can be placed per day, and the statuses of the orders frequently change.

Principle

The Unique Key table can be considered a special Aggregate table in which the REPLACE aggregate function is specified for metric columns to return the most recent record among a group of records that have the same unique key.

When you load data into a Unique Key table, the data is split into multiple batches. Each batch is assigned a version number. Therefore, records with the same unique key may come in multiple versions, of which the most recent version (namely, the record with the largest version number) is retrieved for queries.

As shown in the following table, ID is the unique key column, value is a metric column, and _version holds the data version numbers generated within CelerData. In this example, the record with an ID of 1 is loaded by two batches whose version numbers are 1 and 2, and the record with an ID of 2 is loaded by three batches whose version numbers are 3, 4, and 5.

When you query the record with an ID of 1, the most recent record with the largest version number, which is 2 in this case, is returned. When you query the record with an ID of 2, the most recent record with the largest version number, which is 5 in this case, is returned. The following table shows the records returned by the two queries:

Create a table

In e-commerce scenarios, you often need to collect and analyze the statuses of orders by date. In this example, create a table named orders to hold the orders, define create_time and order_id, which are frequently used as conditions to filter the orders, as unique key columns, and define the other two columns, order_state and total_price, as metric columns. This way, the orders can be updated in real time as their statuses change, and can be quickly filtered to accelerate queries.

The statement for creating the table is as follows:

CREATE TABLE IF NOT EXISTS orders (
    create_time DATE NOT NULL COMMENT "create time of an order",
    order_id BIGINT NOT NULL COMMENT "id of an order",
    order_state INT COMMENT "state of an order",
    total_price BIGINT COMMENT "price of an order"
)
UNIQUE KEY(create_time, order_id)
DISTRIBUTED BY HASH(order_id);

NOTICE

• When you create a table, you can specify the bucketing column by using the DISTRIBUTED BY HASH clause.
• CelerData can automatically set the number of buckets (BUCKETS) when you create a table or add a partition. You no longer need to manually set the number of buckets.

Usage notes

• Take note of the following points about the unique key of a table:

  • The unique key is defined by using the UNIQUE KEY keyword.
  • The unique key must be created on columns on which unique constraints are enforced and whose names cannot be changed.
  • The unique key must be properly designed:
    • When queries are run, unique key columns are filtered before the aggregation of multiple data versions, whereas metric columns are filtered after the aggregation of multiple data versions. Therefore, we recommend that you identify the columns that are frequently used as filter conditions and define these columns as unique key columns. This way, data filtering can start before the aggregation of multiple data versions to improve query performance.
    • During the aggregation process, CelerData compares all unique key columns. This is time-consuming and may decrease query performance. Therefore, do not define a large number of unique key columns. If a column is rarely used as a filter condition for queries, we recommend that you do not define the column as a unique key column.

• When you create a table, you cannot create BITMAP indexes or Bloom Filter indexes on the metric columns of the table.

• The Unique Key table does not support materialized views.

What to do next

After a table is created, you can use various data ingestion methods to load data into CelerData. For information about the data ingestion methods that are supported by CelerData, see Data import.

NOTICE

• When you load data into a Unique Key table, you can only update all columns of the table. For example, when you update the preceding orders table, you must update all its columns, which are create_time, order_id, order_state, and total_price.
• When you query data from a Unique Key table, CelerData needs to aggregate records of multiple data versions. In this situation, a large number of data versions decreases query performance. Therefore, we recommend that you specify a proper frequency at which data is loaded into the table to meet meet your requirements for real-time data analytics while preventing a large number of data versions. If you require minute-level data, you can specify a loading frequency of 1 minute instead of a loading frequency of 1 second.