Hologres

https://help.aliyun.com/zh/hologres/developer-reference
Admin
Hologres is a real-time data warehouse on Alibaba Cloud that provides various data types and data...
Tokens:42,368
Snippets:721
Trust Score:10
Update:3 months ago
Show doc for...
Tokens:
Raw
### Create test table for UPDATE examples

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Creates a sample table named 'test_update' with primary keys and columns for testing UPDATE statements with fixed plans.

```sql
BEGIN;
CREATE TABLE test_update (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
COMMIT;
```

--------------------------------

### PrefixScan with IN or ANY clause

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Examples showing how to use `IN` or `ANY` clauses with primary keys in PrefixScan queries to retrieve multiple rows efficiently.

```sql
pk1 IN (1,2) AND pk2 = 3 <=> scan(1,3),(2,3)两组
```

```sql
pk2 =any('{3,4}') AND pk1 IN (1,2) <=> scan(1,3),(1,4),(2,3),(2,4)四组
```

--------------------------------

### BIT, VARBIT, and BYTEA Examples

Source: https://help.aliyun.com/zh/hologres/developer-reference

Illustrates the usage of BIT, VARBIT, and BYTEA data types, including creating tables, inserting values, and handling bytea output formats.

```sql
-- BIT、VARBIT

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');

ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;

-- BYTEA

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
 
RESET bytea_output;  -- 'hex' by default

SELECT 'abc \153\154\155 \052\251\124'::bytea;
```

--------------------------------

### PrefixScan SQL query examples

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Examples of PrefixScan queries. The WHERE clause must include all distribution keys and a prefix of the primary key. Range conditions on the last PK column are supported from V1.1.48.

```sql
SELECT col1,col2,col3,... FROM TABLE WHERE pk1 = ? AND pk2 = ?;
```

```sql
SELECT col1,col2,col3,... FROM TABLE WHERE pk1 = ? AND pk2 < ?;--从1.1.48版本开始支持pk最后一列条件为range
```

```sql
SELECT col1,col2,col3,... FROM TABLE WHERE pk1 = ? AND pk2 BETWEEN ? AND ?;--从1.1.48版本开始支持pk最后一列条件为range
```

--------------------------------

### Multi-row INSERT with UNNEST and explicit casting example

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Example of multi-row INSERT using `SELECT unnest(...)`. Demonstrates correct explicit casting of arrays for fixed plan compatibility. Incorrect casting or missing explicit casts can lead to fixed plan incompatibility.

```sql
BEGIN;
CREATE TABLE test_insert_multiline (
    pk1 int8,
    col1 float4,
    PRIMARY KEY (pk1)
);
COMMIT;

--支持Fixed Plan
SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;

INSERT INTO test_insert_multiline
SELECT
    unnest(ARRAY[1, 2, 3]::int8[]),
    unnest(ARRAY[1.11, 2.222, 3]::float4[])
ON CONFLICT
    DO NOTHING;

--unnest里ARRAY没有显式cast，不支持Fixed Plan
INSERT INTO test_insert_multiline
SELECT
    unnest(ARRAY[1, 2, 3]),
    unnest(ARRAY[1.11, 2.222, 3])
ON CONFLICT
    DO NOTHING;

--第一列是int8,所以应该cast为int8[],这里例子是int4[]，因此不支持Fixed Plan
INSERT INTO test_insert_multiline
SELECT
    unnest(ARRAY[1, 2, 3]::int4[]),
    unnest(ARRAY[1.11, 2.222, 3]::float4[])
ON CONFLICT
    DO NOTHING;
```

--------------------------------

### Basic UPDATE statement with Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

An example of an UPDATE statement that supports fixed plans. Ensure the `hg_experimental_enable_fixed_dispatcher_for_update` parameter is set to ON.

```sql
UPDATE TABLE SET col1 = ?,col2 = ? WHERE pk1 = ? AND pk2 = ?;
```

--------------------------------

### INSERT Statement with Expressions in Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Examples demonstrating the use of expressions within various clauses of an INSERT statement when Fixed Plan expression support is enabled.

```SQL
-- 建表
CREATE TABLE test_t (
    id INT PRIMARY KEY,
    col INT,
    ts TIMESTAMP
)
WITH (
    orientation = 'row',
    distribution_key = 'id'
);

-- 开启guc
SET hg_experimental_enable_fixed_plan_expression = ON;

-- values子句包含表达式
INSERT INTO test_t VALUES (1, 1, now());

-- on conflict do update子句含有表达式
INSERT INTO test_t AS old 
    VALUES (1, 1, now())
    ON CONFLICT (id) 
    DO UPDATE SET col = excluded.col + old.col, ts = excluded.ts;

-- on conflict where子句包含表达式
INSERT INTO test_t AS old
    VALUES (1, 1, now())
    ON CONFLICT (id)
    DO UPDATE SET col = excluded.col + old.col, ts = excluded.ts
    WHERE excluded.ts > old.ts;

-- returning 子句包含表达式
INSERT INTO test_t AS old
    VALUES (1, 1, now())
    ON CONFLICT (id)
    DO UPDATE SET col = excluded.col + old.col, ts = excluded.ts
    WHERE excluded.ts > old.ts
    RETURNING 2 * col, ts;
```

--------------------------------

### Using BIT, VARBIT, and BYTEA Data Types

Source: https://help.aliyun.com/zh/hologres/developer-reference/data-types?spm=a2c4g.11186623.0.i0

Illustrates the usage of fixed-size BIT, variable-size BIT VARYING, and BYTEA data types for storing binary data. Includes examples of insertion and retrieval with different output formats for BYTEA.

```SQL
-- BIT、VARBIT

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');

ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;

  a  |  b
-----+-----
 101 | 00
 100 | 101

-- BYTEA

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T
 
RESET bytea_output;  -- 'hex' by default

SELECT 'abc \153\154\155 \052\251\124'::bytea;
          bytea
--------------------------
 \x616263206b6c6d202aa954
(1 row)
```

--------------------------------

### SELECT Statement with Expressions in Fixed Plan (Point Query)

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Example of a SELECT statement using expressions on JSONB fields and date truncation for point queries, leveraging Fixed Plan optimization.

```SQL
-- 建表
CREATE TABLE test_t (
    id int PRIMARY KEY,
    ts TIMESTAMP NOT NULL,
    col JSONB
)
WITH (
    orientation = 'row',
    distribution_key = 'id'
);

-- Select字段含有表达式，支持Jsonb操作符
SELECT
    (col ->> 'b')::int + (col ->> 'a')::int,
    date_trunc('day', ts)
FROM
    test_t
WHERE
    id = 1;
```

--------------------------------

### SELECT statement with Primary Key conditions

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Example of a SELECT statement for point lookups using all primary key columns in the WHERE clause. This is a prerequisite for fixed plan optimization.

```sql
SELECT
    col1,
    col2,
    col3, 
...
FROM
    TABLE
WHERE
    pk1 = ?
    AND pk2 = ?
    AND pk3 = ?;
```

--------------------------------

### Query Multiple Array Elements

Source: https://help.aliyun.com/zh/hologres/developer-reference/data-types?spm=a2c4g.11186623.0.i0

Select a range of elements from an array by specifying the start and end indices within square brackets, separated by a colon. For example, `[1:2]` selects the first two elements.

```sql
SELECT int4_array[1:2] FROM array_example;
```

--------------------------------

### Create and test PrefixScan with Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Demonstrates setting up a table with distribution keys and primary keys, then executing queries. Only queries that include all distribution keys and a valid PK prefix can utilize the fixed plan.

```sql
BEGIN;
CREATE TABLE test_select_prefix (
    pk1 INT,
    pk2 INT,
    pk3 INT,
    pk4 INT,
    PRIMARY KEY (pk1, pk2, pk3, pk4)
);
CALL set_table_property ('test_select_prefix', 'orientation', 'row');
CALL set_table_property ('test_select_prefix', 'distribution_key', 'pk1,pk3');
COMMIT;

--没有包含所有distribution key，不能走fixed plan
SELECT * FROM test_select_prefix WHERE pk1 = ? AND pk2 = ?;
--不是pk的prefix，不能走fixed plan
SELECT * FROM test_select_prefix WHERE pk1 = ? AND pk3 = ?;

--可以走fixed plan
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;

SELECT * FROM test_select_prefix WHERE pk1 = ? AND pk2 = ? AND pk3 = ?;
```

--------------------------------

### Modify Hologres Serial Sequence Restart Value

Source: https://help.aliyun.com/zh/hologres/developer-reference/auto-increment-serial

Alter the 'restart with' parameter of a Hologres Sequence to control the starting value for Serial data generation. Replace 'ods.test_tb_id_seq' with your Sequence name and '100' with the desired starting number.

```sql
alter sequence ods.test_tb_id_seq restart with 100
```

--------------------------------

### Create and test SELECT with Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Demonstrates the creation of a test table and the execution of a SELECT statement with fixed plan enabled. Ensure the WHERE clause includes all primary keys for point lookups.

```sql
BEGIN;
CREATE TABLE test_select (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
CALL set_table_property ('test_select', 'orientation', 'row');
COMMIT;

--支持Fixed Plan
SELECT * FROM test_select WHERE pk1 = 1 AND pk2 = 2;
```

--------------------------------

### Create and test DELETE with Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Demonstrates the creation of a test table and the execution of a DELETE statement with fixed plan enabled. Ensure the WHERE clause includes all primary keys.

```sql
BEGIN;
CREATE TABLE test_delete (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
COMMIT;

--支持Fixed Plan，更多场景与Update样例一致
SET hg_experimental_enable_fixed_dispatcher_for_delete = ON;

DELETE FROM test_delete
WHERE pk1 = 1
    AND pk2 = 2;
```

--------------------------------

### Create Table with TIMESTAMPTZ, DATE, DECIMAL, CHAR, and VARCHAR

Source: https://help.aliyun.com/zh/hologres/developer-reference/data-types?spm=a2c4g.11186623.0.i0

Shows how to create a table with various common data types including timestamp with time zone, date, decimal, fixed-length character, and variable-length character. Useful for general data storage.

```SQL
CREATE TABLE test_data_type (
  tswtz_column TIMESTAMP WITH TIME ZONE,
  date_column date,
  decimal_column decimal(38, 10),
  char_column char(20),
  varchar_column varchar(225)
);

INSERT INTO test_data_type
VALUES ('2004-10-19 08:08:08', '2004-10-19', 123.456, 'abcd', 'a');

SELECT * FROM test_data_type;
```

--------------------------------

### Hologres COPY Statement with Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Example of using the COPY statement with Fixed Plan in Hologres, supporting binary format, stream mode, and on-conflict updates.

```SQL
COPY table_name (column0, column1, column2)
FROM
    STDIN WITH (
        format BINARY,
        stream_mode TRUE,
        on_conflict UPDATE);

```

--------------------------------

### Configure Table for Prefix Scan Pagination

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Set up a table with specified orientation, distribution key, and clustering key for optimized pagination queries. Ensure the clustering key is configured for desired sorting.

```sql
-- 建表
CREATE TABLE test_scan(
  pk1 INT, 
  pk2 INT, 
  pk3 INT, 
  col1 INT, 
  PRIMARY KEY(pk1, pk2, pk3)
) WITH (
  orientation = 'row',
  distribution_key = 'pk1,pk2',
  clustering_key = 'pk1:asc,pk2:asc,pk3:asc'
);

-- 写数据
INSERT INTO test_scan VALUES (1,2,3,4),(1,2,5,6),(1,2,7,8);

-- 支持offset + limit， 在Prefix Scan的基础上，从指定的行数开始返回一定行数的结果
SET hg_experimental_enable_fixed_dispatcher_for_scan = on;
-- 默认情况下，返回结果将按照pk3正序
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2 OFFSET 1 limit 2;
```

--------------------------------

### DELETE statement with Primary Key conditions

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Example of a DELETE statement targeting specific rows using all primary key columns in the WHERE clause. This is a prerequisite for fixed plan optimization.

```sql
DELETE FROM TABLE
WHERE pk1 = ?
    AND pk2 = ?
    AND pk3 = ?;
```

--------------------------------

### Non-Immutable Expressions in SELECT with Fixed Plan

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Examples of SELECT statements that will NOT be optimized by Fixed Plan due to the use of non-immutable functions like random() or functions not supporting constant parameters.

```SQL
-- 建表
CREATE TABLE test_t (
    id INT PRIMARY KEY,
    ts TIMESTAMP NOT NULL,
    col JSONB
)
WITH (
    orientation = 'row',
    distribution_key = 'id'
);

-- random() 不是 immutable 的函数表达式，不支持 fixed plan
SELECT
    id + random()
FROM
    test_t
WHERE
    id = 1;

-- toString 函数不支持常量入参，不支持 fixed plan
SELECT
    toString (id)
FROM
    test_t
WHERE
    id = 1;
```

--------------------------------

### UPDATE with Fixed Plan (multiple PKs with ANY)

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

UPDATE statement supporting fixed plans, using `IN` for one PK and `ANY` for another. The `hg_experimental_enable_fixed_dispatcher_for_update` parameter must be ON.

```sql
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;

UPDATE
    test_update
SET
    col1 = 1,
    col2 = 2
WHERE
    pk1 IN (1, 2)
    AND pk2 = ANY ('{3,4}');
```

--------------------------------

### JDBC for Auto-Increment Serial

Source: https://help.aliyun.com/zh/hologres/developer-reference/auto-increment-serial

Implement auto-incrementing serial columns using JDBC to connect to Hologres. This example demonstrates table creation, data insertion, and querying with 'serial' type. Bigserial is also supported.

```java
package test;

import java.sql.*;

public class HoloSerial {

//创建表，包含id和f1两个字段。
    private static void Init(Connection conn) throws Exception {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("drop table if exists test_tb;");
            stmt.execute("create table if not exists test_tb(id serial primary key, f1 text);");
        }
    }

//给f1字段插入数据。
    private static void TestSerial(Connection conn) throws Exception {
        try (PreparedStatement stmt = conn.prepareStatement("insert into test_tb(f1) values(?)")) {
            for (int i = 0; i < 100; ++i) {
                stmt.setString(1, String.valueOf(i + 1));
                int affected_rows = stmt.executeUpdate();
                System.out.println("affected rows => " + affected_rows);
            }
        }

//查询test_tb表，按照id增序排列。
        try (PreparedStatement stmt = conn.prepareStatement("select * from test_tb order by id asc")) {
            try(ResultSet rs = stmt.executeQuery()) {
                while(rs.next()) {
                    String res = rs.getObject(1).toString() + "\t" + rs.getObject(2).toString();
                    System.out.println(res);
                }
            }
        }
    }

//使用JDBC连接Hologres。
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver").newInstance();
        String host = "127.0.0.1:13737";
        String db = "postgres";
        String user = "xx";
        String password = "xx";
        String url = "jdbc:postgresql://" + host + "/" + db;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Init(conn);

            TestSerial(conn);
        }
    }
}
```

--------------------------------

### SELECT Statement with Expressions in Fixed Plan (Prefix Scan)

Source: https://help.aliyun.com/zh/hologres/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Demonstrates a SELECT statement with expressions for prefix scan scenarios, including JSONB field extraction and date truncation, with Fixed Dispatcher enabled.

```SQL
-- 建表
CREATE TABLE test_t (
    id INT,
    ts TIMESTAMP NOT NULL,
    col JSONB,
    PRIMARY KEY (id, ts)
)
WITH (
    orientation = 'row',
    distribution_key = 'id'
);

-- 开启GUC
SET hg_experimental_enable_fixed_dispatcher_for_scan = TRUE;

-- Select字段含有表达式
SELECT
    (col ->> 'b')::int + (col ->> 'a')::int,
    date_trunc('day', ts)
FROM
    test_t
WHERE
    id = 1;
```
Tokens:
Raw