Sequences in Phoenix

Vladimir Adamenkov

Overview

Sequence is a tool that consecutively generates a monotonically increasing or decreasing row of integers according to specified conditions. Such rows are mostly used to create IDs, but can also be used for other purposes. Sequences may or may not belong to a schema.

Create a sequence

To create a sequence, use the following syntax:

CREATE SEQUENCE [IF NOT EXISTS] {[<schema_name>.]<sequence_name>}
[START [WITH] <number>]
[INCREMENT [BY] <number>]
[MINVALUE <number>]
[MAXVALUE <number>]
[CYCLE]
[CACHE <number>];
Parameters and keywords of the CREATE SEQUENCE command
Parameter or keyword Description

IF NOT EXISTS

Without this keyword, attempting to create a sequence with the name of an already existing sequence will result in an error message. Not used by default

schema_name

Name of the schema the sequence belongs to

sequence_name

Name of the sequence

START (WITH)

The number that the sequence begins with. It is returned the first time the sequence is called to generate a new value. This value must be within the range specified by the values of the MINVALUE and MAXVALUE keywords, ends included. The WITH keyword is optional (does not affect anything). Default: 1

number

Numerical value required by the START WITH, INCREMENT BY, MINVALUE, MAXVALUE, and CACHE keywords

INCREMENT (BY)

Value of the sequence increment or decrement size. It is added to the current sequence value to obtain the next value. The BY keyword is optional (does not affect anything). Default: 1

MINVALUE

Minimum value of the sequence. Used as a stopping point for descending sequences or as a starting point for ascending sequences that use cycling after reaching the maximum value. Default: -9223372036854775808

MAXVALUE

Maximum value of the sequence. Used as a stopping point for ascending sequences or as a starting point for descending sequences that use cycling after reaching the minimum value. Default: 9223372036854775807

CYCLE

Defines whether to restart the sequence from the minimum value (or from the maximum value if the sequence is descending) when the other end value is exceeded or to throw an exception. Not used by default

CACHE

The number of consecutive values to store on the client side that must be exhausted before another batch can be requested from the server. Multiple clients can use the same sequence. Any time any client requests a new batch, it will obtain the next one. Default: 100. The default value can be changed manually by adding the phoenix.sequence.cacheSize parameter to the Custom hbase-site.xml section of the HBase service configuration page
NOTE
Sequences in Phoenix can only use the BIGINT type for returned values (ranging from -9223372036854775808 to 9223372036854775807).

Example:

CREATE SEQUENCE IF NOT EXISTS test_schema.test_sequence
START WITH 97 INCREMENT BY 2 MINVALUE 0 MAXVALUE 100 CYCLE CACHE 50;

This command creates a sequence that returns the first value of 97, then the value of 99, and then will cyclically return even numbers from 0 to 100 in ascending order. The first 50 values will be stored on the client side and returned consecutively during executions of queries before obtaining another 50 values from the server.

Delete a sequence

To delete a sequence, use the following syntax:

DROP SEQUENCE [IF EXISTS] <sequence_ref>;

where <sequence_ref> is the exact name of the sequence as it was specified when the sequence was originally created. If a sequence belongs to a schema, then it needs to be specified in this command as well.

Example:

DROP SEQUENCE IF EXISTS test_schema.test_sequence;

Generate new values

To make a sequence generate a new value, use the NEXT VALUE FOR expression followed by the sequence name in a SELECT or an UPSERT VALUES statement. A sequence that is called the first time generates the starting value (default or predefined one).

UPSERT VALUES statement

For example, to create a table and fill it with values while using a sequence to generate IDs for the primary key, execute the following commands:

CREATE TABLE weapon_types (
id INTEGER NOT NULL PRIMARY KEY,
wpn_type VARCHAR(50),
ammo_type VARCHAR(50));

CREATE SEQUENCE IF NOT EXISTS test_schema.test_sequence START 1 INCREMENT 1;

UPSERT INTO weapon_types(id, wpn_type, ammo_type) VALUES (NEXT VALUE FOR test_schema.test_sequence, 'handgun', 'bullet');
UPSERT INTO weapon_types(id, wpn_type, ammo_type) VALUES (NEXT VALUE FOR test_schema.test_sequence, 'shotgun', 'buckshot');
UPSERT INTO weapon_types(id, wpn_type, ammo_type) VALUES (NEXT VALUE FOR test_schema.test_sequence, 'shotgun', 'slug');
UPSERT INTO weapon_types(id, wpn_type, ammo_type) VALUES (NEXT VALUE FOR test_schema.test_sequence, 'assault rifle', 'bullet');
UPSERT INTO weapon_types(id, wpn_type, ammo_type) VALUES (NEXT VALUE FOR test_schema.test_sequence, 'sniper rifle', 'bullet');
UPSERT INTO weapon_types(id, wpn_type, ammo_type) VALUES (NEXT VALUE FOR test_schema.test_sequence, 'crossbow', 'arrow');

SELECT * FROM weapon_types;

Result:

+----+---------------+-----------+
| ID |   WPN_TYPE    | AMMO_TYPE |
+----+---------------+-----------+
| 1  | handgun       | bullet    |
| 2  | shotgun       | buckshot  |
| 3  | shotgun       | slug      |
| 4  | assault rifle | bullet    |
| 5  | sniper rifle  | bullet    |
| 6  | crossbow      | arrow     |
+----+---------------+-----------+

SELECT statement

You can use the SELECT statement to load values into a table from another table using a single UPSERT statement. For example, to create a table enumerating the ammo types from the previously created table while using a sequence to generate IDs for the primary key, execute the following commands:

CREATE TABLE ammo_types (
id INTEGER NOT NULL PRIMARY KEY,
ammo_type VARCHAR(50));

CREATE SEQUENCE IF NOT EXISTS test_schema.test_sequence2 START 1 INCREMENT 1;

UPSERT INTO ammo_types(id, ammo_type)
SELECT NEXT VALUE FOR test_schema.test_sequence2, ammo_type
FROM weapon_types
GROUP BY ammo_type;

SELECT * FROM ammo_types;

Result:

+----+-----------+
| ID | AMMO_TYPE |
+----+-----------+
| 1  | arrow     |
| 2  | buckshot  |
| 3  | bullet    |
| 4  | slug      |
+----+-----------+

View the last value

To view the last value generated by a sequence, use the CURRENT VALUE FOR expression followed by the sequence name in a SELECT statement. Example:

SELECT CURRENT VALUE FOR test_schema.test_sequence;

Result:

+---------------------------------------------+
| CURRENT VALUE FOR TEST_SCHEMA.TEST_SEQUENCE |
+---------------------------------------------+
| 4                                           |
+---------------------------------------------+
NOTE
If a sequence has never been called to generate a new value, this command will return an error.

If a CURRENT VALUE FOR expression is used together with a NEXT VALUE FOR expression for the same sequence in a single statement, they will both return the same newly generated value.

View sequence details

To view the details of all sequences, use the following command:

SELECT * FROM SYSTEM."SEQUENCE";

Result:

+-----------+-----------------+---------------+------------+---------------+--------------+------------+----------------------+---------------------+------------+--------------------+
| TENANT_ID | SEQUENCE_SCHEMA | SEQUENCE_NAME | START_WITH | CURRENT_VALUE | INCREMENT_BY | CACHE_SIZE |      MIN_VALUE       |      MAX_VALUE      | CYCLE_FLAG | LIMIT_REACHED_FLAG |
+-----------+-----------------+---------------+------------+---------------+--------------+------------+----------------------+---------------------+------------+--------------------+
|           | TEST_SCHEMA     | TEST_SEQUENCE | 1          | 101           | 1            | 100        | -9223372036854775808 | 9223372036854775807 | false      | false              |
+-----------+-----------------+---------------+------------+---------------+--------------+------------+----------------------+---------------------+------------+--------------------+

Notice that the CURRENT_VALUE column value for the sequence is 101. This is because the cache size is 100, and the first 100 values were already sent to the client. If you create another connection session to the Phoenix Query Server, it will be treated as another client. In this case, if you generate the next value for the same sequence in that second session, a value of 101 will be returned, and the CURRENT_VALUE column value will change to 201. When a client exhausts its cache of values, it will obtain the next batch from the server. The starting value of this batch will depend on how many batches were given to other clients before that.

The SYSTEM.SEQUENCE table is read-only.

Sequence specifics

When using sequences, consider the following:

  • You cannot alter a sequence after it is created.

  • Both NEXT VALUE FOR and CURRENT VALUE FOR expressions can only be used in the SELECT and UPSERT VALUES statements. Using these expressions in the WHERE, GROUP BY, HAVING, or ORDER BY statements will result in an error.

  • The NEXT VALUE FOR expression generates a new value only once per a single SELECT or UPSERT statement. For example, you may use the statement of the following kind:

    UPSERT INTO test_table(c1,c2) VALUES (NEXT VALUE FOR some_sequence, NEXT VALUE FOR some_sequence);

    In this case, the values in both columns will be the same.

Found a mistake? Seleсt text and press Ctrl+Enter to report it