ByteHouse CDW provides the self-increasing ID function generateSnowflakeID, which achieves a self-increasing ID effect similar to the auto_increment function in the MySQL dialect System. If the Datasource cannot provide an ID, or in Scenes where direct writing is required, ByteHouse Recommended you use the generateSnowflakeID function. This article introduces the implementation logic and usage of the self-increasing function.

In the ClickHouse dialect System of ByteHouse CDW, the generateSnowflakeID function is based on SnowflakeID to provide self-increasing and unique IDs (not guaranteed to be continuous or compact).
In the MySQL dialect System, ByteHouse CDW Default ignores the auto_increment Keywords. You can manually set set enable_auto_increment=1 to enable it. After activation, the system rewrites auto_increment in DDL to generateSnowflakeID(); you can also manually change the Column function to generateSnowflakeID(). For specific usage, please refer to the section below usage>#f8d39e3b.

The structure of SnowflakeID is a 64-bit binary number (actually using 63 bits to accommodate signed integer), composed as follows:

• The first 41 bits represent the timestamp, in milliseconds, calculated from a specified epoch.
• The next 10 bits represent the machine ID, used to avoid conflicts.
• The following 12 bits represent the serial number of each machine, allowing multiple Snowflake IDs to be generated in the same millisecond.
• Finally, this number is usually serialized in decimal form.

SnowflakeID is sorted by time, as it is based on the generation time. In addition, the generation time can be extracted from SnowflakeID to obtain SnowflakeIDs (and their associated objects) created before or after a specific date.

• Command format

  generateSnowflakeID();
  

• Description
  According to the implementation logic above, returns a string of decimal serialized self-increasing ID data.

• Example

  • Search

    SELECT generateSnowflakeID();
    

  • Create table

    • ClickHouse dialect System:

      set dialect_type='CLICKHOUSE'; 
      CREATE TABLE t1(id Int64 DEFAULT generateSnowflakeID(), val1 String) 
      Engine=CnchMergeTree() Order by id;
      

    • MySQL dialect System:

      | | | \

      Correct Example	Error Example
      		\
      Before ByteHouse engine rewrite:	\
      ```SQL	\
      set dialect_type='MYSQL';	\
      create table t1(id Int64 auto_increment primary key, val1 String);	\
      ```	\
      	\
      After ByteHouse engine rewrite:	\
      ```SQL	\
      CREATE TABLE t1 (	\
      id Int64 NOT NULL DEFAULT generateSnowflakeID(),	\
      val1 String NULL	\
      )ENGINE = CnchMergeTree	\
      ORDER BY tuple(id)	\
      UNIQUE KEY tuple(id)	\
      SETTINGS partition_level_unique_keys = 0, storage_policy = 'cnch_default_hdfs', allow_nullable_key = 1, storage_dialect_type = 'MYSQL', index_granularity = 8192	\
      ```	\
      	\
      	```SQL	\
      	-- MySQL does not allow this usage, because the auto_increment column must be a primary key; but ByteHouse can use this usage.	\
      	create table t1(id Int64 auto_increment, val1 String);	\
      	```	\

  • Write

    | | | \

    Correct Example	Error example
    		\
    * Example 1	\
    ```SQL	\
    insert into t1 values (NULL,'a');	\
    SELECT * FROM t1;	\
    ```	\
    	\
    Returned result example:	\
    ```SQL	\
    ┌──────────────────id─┬─val1─┐	\
    │ 7242371370334552064 │ a │	\
    └─────────────────────┴──────┘	\
    ```	\
    	\
    * Example 2	\
    ```SQL	\
    insert into t1(val1) values ('a');	\
    ```	\
    	```SQL	\
    	-- not allowed in MySQL and ByteHouse -> exception	\
    	insert into t1 values ('a');	\
    	```	\