Stripelog

This engine belongs to the family of log engines. See the common properties of log engines and their differences in the Log Engine Family article.

Use this engine in scenarios when you need to write many tables with a small amount of data (less than 1 million rows).

Creating a Table

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  4.     column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  5.     ...
  6. ) ENGINE = StripeLog

See the detailed description of the CREATE TABLE query.

Writing the Data

The StripeLog engine stores all the columns in one file. For each INSERT query, ClickHouse appends the data block to the end of a table file, writing columns one by one.

For each table ClickHouse writes the files:

  • data.bin — Data file.
  • index.mrk — File with marks. Marks contain offsets for each column of each data block inserted.

The StripeLog engine does not support the ALTER UPDATE and ALTER DELETE operations.

Reading the Data

The file with marks allows ClickHouse to parallelize the reading of data. This means that a SELECT query returns rows in an unpredictable order. Use the ORDER BY clause to sort rows.

Example of Use

Creating a table:

  1. CREATE TABLE stripe_log_table
  2. (
  3.     timestamp DateTime,
  4.     message_type String,
  5.     message String
  6. )
  7. ENGINE = StripeLog

Inserting data:

  1. INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The first regular message')
  2. INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The second regular message'),(now(),'WARNING','The first warning message')

We used two INSERT queries to create two data blocks inside the data.bin file.

ClickHouse uses multiple threads when selecting data. Each thread reads a separate data block and returns resulting rows independently as it finishes. As a result, the order of blocks of rows in the output does not match the order of the same blocks in the input in most cases. For example:

  1. SELECT * FROM stripe_log_table
  1. ┌───────────timestamp─┬─message_type─┬─message────────────────────┐
  2. │ 2019-01-18 14:27:32 │ REGULAR      │ The second regular message │
  3. │ 2019-01-18 14:34:53 │ WARNING      │ The first warning message  │
  4. └─────────────────────┴──────────────┴────────────────────────────┘
  5. ┌───────────timestamp─┬─message_type─┬─message───────────────────┐
  6. │ 2019-01-18 14:23:43 │ REGULAR      │ The first regular message │
  7. └─────────────────────┴──────────────┴───────────────────────────┘

Sorting the results (ascending order by default):

  1. SELECT * FROM stripe_log_table ORDER BY timestamp
  1. ┌───────────timestamp─┬─message_type─┬─message────────────────────┐
  2. │ 2019-01-18 14:23:43 │ REGULAR      │ The first regular message  │
  3. │ 2019-01-18 14:27:32 │ REGULAR      │ The second regular message │
  4. │ 2019-01-18 14:34:53 │ WARNING      │ The first warning message  │
  5. └─────────────────────┴──────────────┴────────────────────────────┘

Original article