Hologres

https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined
Admin
Hologres is a real-time data warehouse service provided by Alibaba Cloud. It offers high-performance...
Tokens:61,613
Snippets:279
Trust Score:10
Update:1 year ago
Show doc for...
Tokens:
Raw
### Install Git and Clone Hologres MCP Server Source Code

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-hologres-mcp-llm-to-build-a-data-analysis-agent

This snippet provides commands to install Git using Homebrew (if not already installed) and then clone the `alibabacloud-hologres-mcp-server` repository from GitHub to obtain the server source code for local file mode installation.

```Shell
brew install git
git clone https://github.com/aliyun/alibabacloud-hologres-mcp-server.git
```

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

### Install Python and uv on macOS

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-hologres-mcp-llm-to-build-a-data-analysis-agent

This snippet provides commands to install Python 3.10+ and uv 0.6.7+ on macOS using Homebrew and pip3. It includes commands for updating brew, installing python, verifying python, installing uv, and verifying uv.

```Shell
# 若Python版本不够高，请先运行brew update
# brew update
brew install python

# 验证python安装
# python3 --version

# 安装uv包管理
pip3 install uv

# 验证uv安装
# uv --version
```

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

### Hologres PrefixScan Table Setup and Query Examples

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Illustrates setting up a table for PrefixScan in Hologres, including defining primary and distribution keys, and setting row orientation. It provides examples of queries that can and cannot utilize Fixed Plan with PrefixScan, highlighting the requirement for including all distribution keys and adhering to PK prefix rules.

```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 = ?;
```

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

### Install Hologres MCP Server using PIP

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-hologres-mcp-llm-to-build-a-data-analysis-agent

This snippet provides the command to install the `hologres-mcp-server` Python package using pip3, which is one method to install the server.

```Shell
pip3 install hologres-mcp-server
```

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

### Hologres PrefixScan Detailed Usage Examples

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Provides comprehensive examples of PrefixScan queries in Hologres, including table creation, data insertion, and various SELECT statements. It covers successful Fixed Plan queries with 'IN', 'ANY', and range conditions on the last PK column (requiring v1.1.48+), as well as examples that fail to use Fixed Plan due to missing distribution keys or non-prefix conditions.

```SQL
BEGIN;
CREATE TABLE test_scan (
    pk1 INT,
    pk2 INT,
    pk3 INT,
    col1 INT,
    PRIMARY KEY (pk1, pk2, pk3)
);
CALL set_table_property ('test_scan', 'orientation', 'row');
CALL set_table_property ('test_scan', 'distribution_key', 'pk1,pk2');
COMMIT;

INSERT INTO test_scan
    VALUES (1, 2, 3, 4);

--支持Fixed Plan
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;

SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2;

--支持Fixed Plan
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;

SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 IN (2, 3);

--支持Fixed Plan
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;

SELECT * FROM test_scan WHERE pk1 = ANY ('{3,4}') AND pk2 IN (2, 3);

--支持fixed plan,pk最后一列是range条件，需要1.1.48及以上版本支持
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;

SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 1 AND pk3 > 1 AND pk3 < 4;

--支持fixed plan,pk最后一列是range条件，需要1.1.48及以上版本支持
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;

SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 1 AND pk3 BETWEEN 1 AND 4;

--不包含所有的distribution key，不支持Fixed Plan
SELECT * FROM test_scan WHERE pk1 = 1;

--不符合主键前缀Prefix，不支持Fixed Plan
SELECT * FROM test_scan WHERE pk2 = 2;
```

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

### Hologres EXPLAIN ANALYZE Resource and Cost Output Example

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This snippet illustrates an example of the resource consumption and initial cost metrics section from a Hologres `EXPLAIN ANALYZE` output. It shows detailed timings for various query phases and resource usage statistics like memory, CPU time, and read bytes, along with straggler worker IDs.

```Hologres Explain Analyze Output
Build gang desc table cost:[2] ms
Start query cost:[18] ms
- Wait schema cost:[0] ms
- Lock query cost:[0] ms
- Create dataset reader cost:[0] ms
- Create split reader cost:[0] ms
Get the first block cost:[2434] ms
Get result cost:[2434] ms
====================resource====================
Memory: 921(244/230/217) MB,  straggler worker id: 72969760xxx
CPU time: 149772(38159/37443/36736) ms, straggler worker id: 72969760xxx
Physical read bytes: 3345(839/836/834) MB, straggler worker id: 72969760xxx
Read bytes: 41787(10451/10446/10444) MB, straggler worker id: 72969760xxx
DAG instance count: 41(11/10/10), straggler worker id: 72969760xxx
Fragment instance count: 275(70/68/67), straggler worker id: 72969760xxx
```

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

### SQL Example for Bitmap Index

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/overview-3

Example query demonstrating the use of Bitmap index for equality queries.

```SQL
select * from tb1 where a =100;
```

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

### Perform Key/Value Query with HoloClient in Java

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/query-key-value-pairs

This Java example demonstrates using the HoloClient library for Key/Value queries, which simplifies development by automatically merging multiple queries into a single SQL statement. It includes the Maven dependency for HoloClient and shows how to configure the client and perform `Get` operations.

```XML
<dependency>
  <groupId>com.alibaba.hologres</groupId>
  <artifactId>holo-client</artifactId>
  <version>{1.2.16.5}</version>
</dependency>
```

```Java
// 配置参数,url格式为 jdbc:postgresql://host:port/db
HoloConfig config = new HoloConfig();
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
config.setReadThreadCount(10);//读并发，最多占用10个jdbc连接
try (HoloClient client = new HoloClient(config)) {
    //create table t0(id int not null,name0 text,address text,primary key(id))
    TableSchema schema0 = client.getTableSchema("t0");

    Get get = Get.newBuilder(schema).setPrimaryKey("id", 0).build(); // where id=0;
    client.get(get).thenAcceptAsync((record)->{
        // do something after get result
    });
    Get get1 = Get.newBuilder(schema).setPrimaryKey("id", 1).build(); // where id=1;
    client.get(get1).thenAcceptAsync((record)->{
        // do something after get result
    });
catch(HoloClientException e){
}
```

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

### SQL: HashAggregate Example for Group By Operations

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This SQL example demonstrates a common `HashAggregate` operation in Hologres. It uses `EXPLAIN` to show the query plan for a `GROUP BY` clause on a large table, where data is hashed and distributed to different shards for aggregation.

```SQL
EXPLAIN SELECT l_orderkey,count(l_linenumber) FROM public.holo_lineitem_100g GROUP BY l_orderkey;
```

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

### Hologres PrefixScan Multi-Value Query Examples

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Demonstrates how 'IN' and 'ANY' clauses can be used in PrefixScan queries to fetch multiple combinations of primary key values, illustrating the resulting scan groups.

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

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

### SQL Example for Distribution Key

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/overview-3

Example query demonstrating the use of Distribution Key for efficient joins, reducing data shuffle and enabling local join capabilities.

```SQL
select * from tbl1 join tbl2 on tbl1.a=tbl2.c;
```

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

### Hologres V3.0: Setting up Full-Incremental Refresh with GUC Parameter

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/create-dynamic-table

Shows how to configure full-incremental data consumption in Hologres V3.0, which requires explicitly enabling the 'incremental_guc_hg_experimental_enable_hybrid_incremental_mode' GUC parameter. The example covers base table creation, binlog setup, data insertion, and dynamic table definition.

```SQL
--准备基表，并开启binlog插入数据
CREATE TABLE base_sales(
  day TEXT NOT NULL,
  hour INT,
  user_id BIGINT,
  ts TIMESTAMPTZ,
  amount FLOAT,
  pk text NOT NULL PRIMARY KEY
);

-- 为基表导入数据
INSERT INTO base_sales values ('2024-08-29',1,222222,'2024-08-29 16:41:19.141528+08',5,'ddd');

-- 为基表打开Binlog
ALTER TABLE base_sales SET (binlog_level = replica);

-- 再为基表导入增量数据
INSERT INTO base_sales VALUES ('2024-08-29',2,3333,'2024-08-29 17:44:19.141528+08',100,'aaaaa');

-- 创建自动刷新的增量Dynamic Table，并开启全增量数据一体消费的GUC
CREATE DYNAMIC TABLE sales_incremental
  WITH (
    refresh_mode='incremental',
    incremental_auto_refresh_schd_start_time = 'immediate',
    incremental_auto_refresh_interval = '3 minutes',
    incremental_guc_hg_experimental_enable_hybrid_incremental_mode= 'true'
  )
AS
  SELECT day, hour, SUM(amount), COUNT(1)
    FROM base_sales
  GROUP BY day, hour;
```

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

### SQL: Multi-stage HashAggregate Example (TPC-H Q6)

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This snippet provides an example of a multi-stage `HashAggregate` operation, typically occurring with very large datasets. It uses a TPC-H Q6 query to illustrate how aggregation can be broken down into partial and final stages across multiple shards and files for improved performance.

```SQL
EXPLAIN SELECT
        sum(l_extendedprice * l_discount) AS revenue
FROM
        lineitem
WHERE
        l_shipdate >= date '1996-01-01'
        AND l_shipdate < date '1996-01-01' + interval '1' year
        AND l_discount BETWEEN 0.02 - 0.01 AND 0.02 + 0.01
        AND l_quantity < 24;
```

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

### Hologres Point Query (Key/Value) Example

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Demonstrates a point query scenario in Hologres, where the WHERE clause contains all primary keys. It shows table creation, setting row orientation, and a SELECT statement optimized for point queries. Supports 'IN' and 'ANY' for multiple key 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;
```

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

### SQL: Record Full Event Path with Time and Event-based Session Splitting

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/path-analysis-function

This example shows how to record complete user event paths by splitting sessions based on both time and specific events. It uses 'browse' as the starting event, a 180-second interval, and a sequence length of 7, then decodes the result with `pad_full_path`.

```SQL
-- Split by time and event: start event "browse", interval 180 s, sequence length 7, and decode the result with pad_full_path function
SELECT uid, pad_full_path(path_analysis_detail(event, event_time, 'browse', 180, 7, 0, false,TRUE)) AS ret FROM path_demo GROUP BY uid;
```

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

### SQL: Broadcast Join Example with Small and Large Tables

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This SQL snippet demonstrates a Broadcast Join scenario in Hologres. It creates two tables, `broadcast_test_1` (small) and `broadcast_test_2` (large), inserts data, updates statistics, and then uses `EXPLAIN` to show the query plan for a join operation, illustrating how Broadcast can be cost-effective for small table joins.

```SQL
BEGIN;
CREATE TABLE broadcast_test_1 (
    f1 int,
    f2 int);
CALL set_table_property('broadcast_test_1','distribution_key','f2');
CREATE TABLE broadcast_test_2 (
    f1 int,
    f2 int);
COMMIT;

INSERT INTO broadcast_test_1 SELECT i AS f1, i AS f2 FROM generate_series(1, 30)i;
INSERT INTO broadcast_test_2 SELECT i AS f1, i AS f2 FROM generate_series(1, 30000)i;

ANALYZE broadcast_test_1;
ANALYZE broadcast_test_2;

EXPLAIN SELECT * FROM broadcast_test_1 t1, broadcast_test_2 t2 WHERE t1.f1=t2.f1;
```

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

### Configure LLM API Key for Alibaba Cloud Bailian (OpenAI Compatible)

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-hologres-mcp-llm-to-build-a-data-analysis-agent

This section describes how to configure API Key information within the Cline extension in VS Code. It details parameters for connecting to Alibaba Cloud Bailian API using the OpenAI Compatible mode, including API Provider, Base URL, API Key, and Model ID.

```APIDOC
API Provider:
  Description: Specifies the API service provider to use. Select 'OpenAI Compatible' to connect to Alibaba Cloud Bailian API.
  Value: OpenAI Compatible
Base URL:
  Description: The base URL for API services, specifying the root address for API requests.
  Example: https://dashscope.aliyuncs.com/compatible-mode/v1
API Key:
  Description: The key used for authentication. Obtain from Alibaba Cloud Bailian console.
Model ID:
  Description: Specifies the model to use. Recommended choices are 'qwen-max-latest' or 'qwq-plus-latest'.
```

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

### SQL: Sort Operator Example with ORDER BY

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This SQL snippet demonstrates the `Sort` operator in Hologres, which is typically triggered by an `ORDER BY` clause. It uses `EXPLAIN` to show the query plan for sorting data from the `lineitem` table by `l_shipdate`.

```SQL
EXPLAIN SELECT l_shipdate FROM public.lineitem ORDER BY l_shipdate;
```

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

### SQL: Record Full Event Path with Time-based Session Splitting

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/path-analysis-function

This example demonstrates how to use `path_analysis_detail` and `pad_full_path` to record complete user event paths. It splits sessions based on time, starting with a 'login' event, a 180-second session interval, and a sequence length of 7.

```SQL
-- Split by time: specify "login" as the start event, SESSION interval 180 s, match sequence length 7, and decode the result with pad_full_path function
SELECT uid, pad_full_path(path_analysis_detail(event, event_time, 'login', 180, 7, 0, false)) AS ret FROM path_demo GROUP BY uid;
```

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

### Example: Create User Mapping for Current User

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-dlf-to-read-data-from-and-write-data-to-oss

An example demonstrating how to create a user mapping for the `current_user` to access DLF and OSS data, specifying the access key ID and secret.

```SQL
--为当前用户创建用户映射
CREATE USER MAPPING FOR current_user SERVER <server_name> OPTIONS
(
    dlf_access_id 'yourAccessKeyID',
    dlf_access_key 'yourAccessKeySecret',
```

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

### Hologres CTE Reuse Example Query

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/optimize-performance-of-queries-on-hologres-internal-tables

This example demonstrates CTE reuse in Hologres. It creates a table, inserts data, then sets `optimizer_cte_inlining=off` to enable CTE reuse. The `EXPLAIN` statement shows how a CTE `c` is defined and then referenced twice, illustrating the performance benefits of single computation and reuse in Hologres V1.3+.

```SQL
create table cte_reuse_test_t
(
    a integer not null,
    b text,
    primary key (a)
);

insert into cte_reuse_test_t values(1, 'a'),(2, 'b'), (3, 'c'), (4, 'b'), (5, 'c'), (6, ''), (7, null);

set optimizer_cte_inlining=off;

explain with c as (select b, max(a) as a from cte_reuse_test_t group by b)
select a1.a,a2.a,a1.b, a2.b
from c a1, c a2
where a1.b = a2.b
order by a1.b
limit 100;
```

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

### Hologres Point Query Performance Test Configuration Example

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/best-practices-for-performance-tests-on-data-writes-data-updates-and-point-queries

This example configuration for `test.conf` specifies connection details for Hologres (JDBC URL, AccessKey ID/Secret), read thread size, and test parameters. Test parameters include thread count, test duration, target table name, asynchronous mode setting, and an option to vacuum the table before running. It also defines settings for data preparation, such as row count, table orientation (row, column, or row,column), column count, and column size.

```Configuration
# 连接配置
holoClient.jdbcUrl=jdbc:hologres://<ENDPOINT>:<PORT>/<DBNAME>
holoClient.username=<AccessKey_ID>
holoClient.password=<AccessKey_Secret>
holoClient.readThreadSize=32

# 测试配置
get.threadSize=8
get.testTime=300000
get.tableName=kv_test
get.async=true
get.vacuumTableBeforeRun=true
get.keyRangeParams=L1-200000000

# 表初始化配置（仅PREPARE_GET_DATA模式生效）
prepareGetData.rowNumber=200000000
prepareGetData.orientation=row
put.columnCount=20
put.columnSize=20
```

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

### SQL Example for Clustering Key

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/overview-3

Example query demonstrating the use of Clustering Key for range or filter queries. Clustering Key supports left-matching principles and is recommended for up to two columns.

```SQL
select sum(a) from tb1 where a > 100 and a < 200;
```

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

### SQL: Limit Operator Example with Pushdown to Seq Scan

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This example illustrates the `Limit` operator in Hologres and its interaction with `Seq Scan`. It shows a query with `LIMIT 1` where the limit is pushed down to the `Seq Scan` node, meaning only one row needs to be scanned to produce the result, optimizing performance.

```SQL
EXPLAIN SELECT * FROM public.lineitem limit 1;
```

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

### Hologres: Example Setting Table Data TTL

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/alter-table

Example demonstrating how to set the `time_to_live_in_seconds` for a table named `dwd.holo_test` to 600 seconds.

```SQL
call set_table_property('dwd.holo_test', 'time_to_live_in_seconds', '600');
```

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

### SQL: Demonstrating PQE Execution with ::timestamp Cast

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This example illustrates how certain operators or functions in Hologres might be executed by the less efficient PQE (Postgres engine) instead of HQE. It creates a table, inserts data, and uses `EXPLAIN` to show that the `::timestamp` cast operator triggers the `ExecuteExternalSQL` operator, indicating PQE usage.

```SQL
CREATE TABLE pqe_test(a text);
INSERT INTO pqe_test VALUES ('2023-01-28 16:25:19.082698+08');
EXPLAIN SELECT a::timestamp FROM pqe_test;
```

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

### Execute Hologres Point Query Performance Test Commands

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/best-practices-for-performance-tests-on-data-writes-data-updates-and-point-queries

These shell commands are used to execute the `holo-e2e-performance-tool-1.0.0.jar` with the `test.conf` file. The first command runs in `PREPARE_GET_DATA` mode to prepare test data, while the second command runs in `GET` mode to perform the actual point query test. Users can skip the `PREPARE_GET_DATA` step if their business data is already prepared or if they are continuing from a previous test.

```Shell
# 使用PREPARE_GET_DATA模式进行数据准备
java -jar holo-e2e-performance-tool-1.0.0.jar test.conf PREPARE_GET_DATA

# 使用GET模式进行点查测试
java -jar holo-e2e-performance-tool-1.0.0.jar test.conf GET
```

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

### Hologres Dictionary Encoding Example (All Versions)

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/dictionary-encoding

This example illustrates how to manage dictionary encoding using the CALL set_table_property command, which is compatible with all Hologres versions. It covers creating a table with dictionary encoding and subsequently modifying it, including both full and incremental updates.

```SQL
--创建表tbl并设置dictionary_encoding_columns索引
begin;
create table tbl (
  a int not null,
  b text not null,
  c text not null
);
call set_table_property('tbl', 'dictionary_encoding_columns', 'a:on,b:off,c:auto');
commit;

--修改dictionary_encoding_columns索引
call set_table_property('tbl', 'dictionary_encoding_columns', 'a:off');--全量修改，b和c因为是text列，会被默认设置为dictionary_encoding_columns

call update_table_property('tbl', 'dictionary_encoding_columns', 'c:off');--增量修改，仅将c关闭dictionary_encoding_columns
```

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

### SQL Example for Event Time Column (Segment_Key)

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/overview-3

Example query demonstrating the use of Event Time Column (formerly Segment_Key) for time-series data like logs or traffic, enabling efficient range queries on time-related columns.

```SQL
select sum(a) from tb1 where ts > '2020-01-01' and a < '2020-03-02';
```

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

### Hologres: Example Moving Table to New Schema

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/alter-table

Example demonstrating how to move a table named `tbl` from the `public` schema to the `testschema` schema.

```SQL
ALTER TABLE IF EXISTS public.tbl
    SET SCHEMA testschema;
```

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

### MAKE_DATE Example: Creating a Date

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/date-and-time-functions

Demonstrates how to use the MAKE_DATE function to construct a date from separate year, month, and day integer values.

```SQL
-- Create date: 2013-07-15
SELECT MAKE_DATE(2013, 7, 15);
-- Expected result:
-- make_date
-- ------------
--  2013-07-15
```

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

### Hologres HG_VERSION Function Usage Example

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/hg-version

Provides a practical SQL example demonstrating how to invoke the HG_VERSION function within a SELECT statement. The output includes the Hologres instance version, compatible PostgreSQL version, operating system, and compiler information.

```SQL
SELECT HG_VERSION();
```

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

### Create Table in Specific Schema from Public Schema

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/create-schema

This SQL example demonstrates how to create a table in a specific, non-public schema (`my_schema`) while the current search path is set to `public`. It explicitly uses the `schema.tablename` syntax.

```SQL
SET search_path TO public;
CREATE TABLE my_schema.mytest (
  name text,
  id INT,
  age INT
);
```

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

### Query Single Key in Hologres

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/query-key-value-pairs

This SQL example shows how to perform a simple Key/Value point query to retrieve a single record from the `test_kv_table` based on its primary key.

```SQL
select * from test_kv_table where key  = '1';
```

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

### SET_TABLE_PROPERTY Usage Example with CREATE TABLE

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/set-table-property-01

An example demonstrating how to use SET_TABLE_PROPERTY in conjunction with CREATE TABLE within a transaction to define various table properties such as clustering key, segment key, bitmap columns, dictionary encoding, and time-to-live for a new table.

```SQL
BEGIN;
CREATE TABLE ORDERS (
  O_ORDERKEY              INTEGER NOT NULL,
  O_CUSTKEY        INTEGER NOT NULL,
  O_ORDERSTATUS    TEXT NOT NULL,
  O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
  O_ORDERDATE      DATE NOT NULL,
  O_ORDERPRIORITY  TEXT NOT NULL,
  O_CLERK          TEXT NOT NULL,
  O_SHIPPRIORITY   INTEGER NOT NULL,
  O_COMMENT        TEXT NOT NULL);
CALL SET_TABLE_PROPERTY ('ORDERS', 'clustering_key', 'O_ORDERKEY:asc,O_CUSTKEY:asc');
CALL SET_TABLE_PROPERTY ('ORDERS', 'segment_key', 'O_ORDERDATE');
CALL SET_TABLE_PROPERTY ('ORDERS', 'bitmap_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY');
CALL SET_TABLE_PROPERTY ('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY');
CALL SET_TABLE_PROPERTY ('ORDERS', 'time_to_live_in_seconds', '172800');
COMMIT;
```

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

### Hologres Dictionary Encoding Example (V2.1+)

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/dictionary-encoding

This example demonstrates how to apply dictionary encoding to columns when creating a table using the CREATE TABLE statement, available in Hologres V2.1 and later. It also shows how to modify the dictionary encoding settings for a table using ALTER TABLE.

```SQL
CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL,
    c text NOT NULL
)
WITH (
    dictionary_encoding_columns = 'a:on,b:off,c:auto'
);

-- 修改dictionary_encoding_columns
ALTER TABLE tbl SET (dictionary_encoding_columns = 'a:off');--ALTER TABLE语法仅支持全量修改
```

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

### SQL: Prepare Data for Path Analysis

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/path-analysis-function

This SQL snippet demonstrates how to set up the necessary environment and data for path analysis in Hologres. It includes creating the `flow_analysis` extension and a `path_demo` table, then populating it with sample user event data.

```SQL
-- Create Extension. Extensions are DB-level functions, only need to be executed once per DB.
CREATE extension flow_analysis;
-- Prepare data
CREATE TABLE path_demo(
    uid text,
    event text,
    event_time timestamptz
);

INSERT INTO path_demo VALUES
('1','register','2023-11-24 16:01:23+08'),
('1','login','2023-11-24 16:02:10+08'),
('1','browse','2023-11-24 16:02:15+08'),
('1','watch_live','2023-11-24 16:03:10+08'),
('1','browse','2023-11-24 16:03:15+08'),
('1','collect','2023-11-24 16:04:20+08'),
('1','browse','2023-11-24 16:07:21+08'),
('1','purchase','2023-11-24 16:08:23+08'),
('1','exit','2023-11-24 16:09:05+08'),

('2','login','2023-11-24 16:10:23+08'),
('2','purchase','2023-11-24 16:12:23+08'),

('3','login','2023-11-24 16:02:23+08'),
('3','browse','2023-11-24 16:02:23+08'),
('3','collect','2023-11-24 16:03:53+08'),
('3','watch_live','2023-11-24 16:04:53+08'),

('4','login','2023-11-24 16:02:23+08'),
('4','browse','2023-11-24 16:03:53+08'),
('4','purchase','2023-11-24 16:04:23+08'),
('4','watch_live','2023-11-24 16:05:53+08'),
('4','cancel_order','2023-11-24 16:06:53+08');
```

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

### Create MaxCompute Partitioned Table and Insert Data

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/use-cases/use-dataworks-to-operate-multiple-partitions-of-a-hologres-table

SQL commands to create a partitioned table named "odps_sale_detail" in MaxCompute and populate it with sample data across multiple partitions (20240110 to 20240116). This prepares the source data for migration.

```SQL
--创建一张MaxCompute分区表sale_detail.
CREATE TABLE IF NOT EXISTS odps_sale_detail (
    shop_name STRING,
	  customer_id STRING,
    total_price DOUBLE
)
PARTITIONED BY ( sale_date STRING);

---- 向源表增加分区20240110并写入数据
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240110');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240110') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);

-- 向源表增加分区20240111
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240111');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240111') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);

-- 向源表增加分区20240112并写入数据
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240112');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240112') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);

-- 向源表增加分区20240113并写入数据
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240113');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240113') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);

-- 向源表增加分区20240114并写入数据
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240114');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240114') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);

-- 向源表增加分区20240115并写入数据
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240115');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240115') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);

-- 向源表增加分区20240116并写入数据
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20240116');
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20240116') VALUES
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3);
```

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

### Hologres EXPLAIN ANALYZE Cost Metrics Breakdown

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This section provides a detailed breakdown of the cost metrics reported by Hologres's `EXPLAIN ANALYZE`, allowing users to pinpoint performance bottlenecks across different query execution stages. It covers total query cost and specific timings for optimization, plan conversion, initialization, query startup (including schema alignment, locking, reader creation), and result retrieval.

```APIDOC
Cost Metrics:
  Total cost: Total Query execution time (ms).
  Optimizer cost: Time for the Query Optimizer (QO) to generate the execution plan (ms).
  Build gang desc table cost: Time taken to convert the QO-generated execution plan into the data structure required by the execution engine (ms).
  Init gangs cost: Time for further preprocessing of the QO-generated plan and sending the request to the execution engine, initiating the Start Query phase (ms).
  Start query cost: Time calculated from the completion of the Init gangs step, covering the initialization phase before actual query execution, including locking and schema version alignment (ms).
    Wait schema cost: Time for the Storage Engine (SE) and Frontend (FE) to align Schema versions. High delay may indicate slow SE processing, especially with frequent DDL on partitioned parent tables.
    Lock query cost: Time spent waiting for query locks. High cost suggests the query is blocked by locks.
    Create dataset reader cost: Time to create the index data reader. High cost might indicate a cache miss.
    Create split reader cost: Time taken to open files. High cost suggests a file metadata cache miss and increased IO overhead.
  Get result cost: Time calculated from the end of the Start Query phase until all results are returned (ms).
    Get the first block cost: Time from the end of the Start Query phase until the first batch of data (record batch) is returned (ms). This metric can be very close to or identical to Get result cost in scenarios where the top query plan operator (e.g., Hash Agg) requires all downstream data before producing output. For streaming queries with filters, this metric typically differs significantly from Get result cost.
```

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

### Create Hologres Performance Test Configuration File

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/best-practices-for-performance-tests-on-data-writes-data-updates-and-point-queries

This command initiates the creation of a new file named `test.conf` using the `vim` editor. This file will store essential connection and test parameters required for conducting point query performance tests on Hologres.

```Shell
vim test.conf
```

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

### Hologres PrefixScan Query Expressions

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/accelerate-the-execution-of-sql-statements-by-using-fixed-plans

Shows SQL expressions for PrefixScan queries in Hologres. This optimization applies when querying a subset of primary keys following a left-match principle. It includes examples with equality and range conditions on the last primary key column, supported from version 1.1.48.

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

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

### Initialize Spark SQL and Create Delta Tables

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/implement-a-data-lakehouse-solution-based-on-delta-lake

Configures and launches the Spark SQL interactive shell with specific serialization and Delta Lake settings. Subsequently, it creates a new database and several Delta Lake tables from existing textfile tables, including partitioned tables, within the Spark SQL environment.

```Shell
spark-sql --conf 'spark.serializer=org.apache.spark.serializer.KryoSerializer' --conf 'spark.sql.delta.mergeSchema=true' --conf 'autoMerge.enable=true' --conf 'spark.sql.parquet.writeLegacyFormat=true'
```

```Spark SQL
CREATE DATABASE IF NOT EXISTS test_spark_delta LOCATION 'oss://oss-bucket-dlftest/test_spark_delta';

USE test_spark_delta;

CREATE TABLE nation_delta
USING delta
AS SELECT * FROM ${SOURCE}.nation_textfile;

CREATE TABLE region_delta
USING delta
AS SELECT * FROM ${SOURCE}.region_textfile;

CREATE TABLE supplier_delta
USING delta
AS SELECT * FROM ${SOURCE}.supplier_textfile;

CREATE TABLE customer_delta
USING delta
partitioned BY (c_mktsegment)
AS SELECT * FROM ${SOURCE}.customer_textfile;

CREATE TABLE part_delta
USING delta
partitioned BY (p_brand)
AS SELECT * FROM ${SOURCE}.part_textfile;

CREATE TABLE partsupp_delta
USING delta
AS SELECT * FROM ${SOURCE}.partsupp_textfile;

CREATE TABLE orders_delta
USING delta
partitioned BY (o_orderdate)
AS SELECT * FROM ${SOURCE}.orders_textfile;

CREATE TABLE lineitem_delta
USING delta
partitioned BY (l_shipdate)
AS SELECT * FROM ${SOURCE}.lineitem_textfile;
```

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

### Get MaxCompute Partitions using DataWorks Hologres SQL Node

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/use-cases/use-dataworks-to-operate-multiple-partitions-of-a-hologres-table

SQL query executed within a DataWorks Hologres SQL node to retrieve distinct "sale_date" values from the "odps_sale_detail" table in MaxCompute. This output serves as the input for a subsequent for-each node to iterate over partitions.

```SQL
SELECT distinct sale_date FROM odps_sale_detail WHERE sale_date > '20240111';
```

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

### Create External Server for DLF Default Catalog and Native OSS

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-dlf-to-read-data-from-and-write-data-to-oss

This SQL snippet demonstrates how to create an external server in Hologres using the `dlf_fdw` wrapper. It connects to the DLF default data catalog and uses native OSS storage. It includes commands to view existing servers and drop a server before creation. Requires Superuser privileges.

```SQL
--查看现有server（其中meta_warehouse_server，odps_server是系统内置server，不可以修改和删除）
SELECT * FROM pg_foreign_server;

--删除现有server
DROP SERVER <server_name> CASCADE;

--创建server
CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_region '<region id>',
    dlf_endpoint 'dlf-share.<region id>.aliyuncs.com',
    oss_endpoint 'oss-<region id>-internal.aliyuncs.com'
);
```

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

### Flink Java Aggregate Function for RoaringBitmap UV Calculation

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/use-cases/real-time-pre-aggregated-uv-computing-solution-based-on-hologres-flink

This Java code defines a Flink AggregateFunction and WindowFunction to perform unique visitor (UV) aggregation using RoaringBitmap. It includes logic for creating, adding to, merging, and getting results from the bitmap, as well as optimizing and serializing the RoaringBitmap to a byte array for storage in Hologres.

```Java
          -- 触发器，可以在窗口未结束时获取聚合结果
          .trigger(ContinuousProcessingTimeTrigger.of(Time.minutes(1)))
          .aggregate(
          -- 聚合函数，根据key By筛选的维度，进行聚合
          new AggregateFunction<
              Tuple5<String, String, String, String, Integer>,
              RoaringBitmap,
              RoaringBitmap>() {
                  @Override
                  public RoaringBitmap createAccumulator() {
                      return new RoaringBitmap();
                  }

                  @Override
                  public RoaringBitmap add(
                      Tuple5<String, String, String, String, Integer> in,
                      RoaringBitmap acc) {
                      -- 将32位的uid添加到RoaringBitmap进行去重
                      acc.add(in.f4);
                      return acc;
                  }

                  @Override
                  public RoaringBitmap getResult(RoaringBitmap acc) {
                      return acc;
                  }

                  @Override
                  public RoaringBitmap merge(
                      RoaringBitmap acc1, RoaringBitmap acc2) {
                      return RoaringBitmap.or(acc1, acc2);
                  }
              },
          -- 窗口函数，输出聚合结果
          new WindowFunction<
              RoaringBitmap,
              Tuple6<String, String, String, String, Timestamp, byte[]>,
              Tuple,
              TimeWindow>() {
                  @Override
                  public void apply(
                      Tuple keys,
                      TimeWindow timeWindow,
                      Iterable<RoaringBitmap> iterable,
                      Collector<
                      Tuple6<String, String, String, String, Timestamp, byte[]>> out)
                      throws Exception {
                      RoaringBitmap result = iterable.iterator().next();

                      // 优化RoaringBitmap
                      result.runOptimize();
                      // 将RoaringBitmap转化为字节数组以存入Holo中
                      byte[] byteArray = new byte[result.serializedSizeInBytes()];
                      result.serialize(ByteBuffer.wrap(byteArray));

                      // 其中 Tuple6.f4(Timestamp) 字段表示以窗口长度为周期进行统计，以秒为单位
                      out.collect(
                          new Tuple6<>(
                              keys.getField(0),
                              keys.getField(1),
                              keys.getField(2),
                              keys.getField(3),
                              new Timestamp(
                                  timeWindow.getEnd() / 1000 * 1000),
                              byteArray));
              }
          });
```

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

### Optimized Data Read from Hologres using Spark Connector

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/write-data-from-apache-spark-to-hologres

Starting from Spark Connector V1.3.2, optimized concurrent read capabilities are available for Hologres. This example demonstrates how to use the Hologres Connector to read data, allowing for setting `scan_parallelism` to leverage Hologres table shards for significantly improved performance compared to the generic JDBC method.

```Scala
val spark = SparkSession
.builder
.appName("ReadFromHologres")
.master("local[*]")
.getOrCreate()

spark.sparkContext.setLogLevel("WARN")

import spark.implicits._

val schema = StructType(Array(
  StructField("id", LongType),
  StructField("counts", IntegerType),
  StructField("name", StringType, false),
  StructField("price", DecimalType(38, 12)),
  StructField("out_of_stock", BooleanType)
))

val readDf = spark.read
.format("hologres")
.schema(schema) // Optional, if not specified, all fields of the Hologres table will be read by default
.option("username", "your_username")
.option("password", "your_password")
.option("jdbcurl", "jdbc:postgresql://hologres_endpoint/test_db")
.option("table", "tb008")
.option("scan_parallelism", "10") // Default concurrency for reading Hologres, maximum is the shard count of the Hologres table
.load()
```

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

### API Documentation for RestartInstance

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/api-hologram-2022-06-01-restartinstance

This section details the API for restarting a Hologres instance, including its authorization requirements, HTTP request syntax, input parameters, expected return values, and a sample JSON response.

```APIDOC
Authorization Information:
- Operation: hologram:RestartInstance
- Access Level: update
- Resource Type: *Instance `acs:hologram:{#regionId}:{#accountId}:instance/{#InstanceId}`
- Condition Keywords: None
- Associated Operations: None
```

```HTTP
POST /api/v1/instances/{instanceId}/restart HTTP/1.1
```

```APIDOC
Request Parameters:
- Name: instanceId
  - Type: string
  - Required: No
  - Description: Instance ID.
  - Example Value: hgprecn-cn-i7m2ucpyu005
```

```APIDOC
Return Parameters:
- Name: (Root Object)
  - Type: object
  - Description: Schema of Response
- Name: RequestId
  - Type: string
  - Description: Id of the request
  - Example Value: 36291497-CDB0-53DC-8CD7-762E054F57A6
- Name: Data
  - Type: boolean
  - Description: Operation success. Enumerated values: true: Success. false: Failure.
  - Example Value: true
- Name: Success
  - Type: boolean
  - Description: Request result, indicates whether an exception occurred, unrelated to business.
  - Example Value: true
- Name: ErrorCode
  - Type: string
  - Description: Error code.
  - Example Value: null
- Name: ErrorMessage
  - Type: string
  - Description: Error message.
  - Example Value: null
- Name: HttpStatusCode
  - Type: string
  - Description: HTTP status code.
  - Example Value: 200
```

```JSON
{
  "RequestId": "36291497-CDB0-53DC-8CD7-762E054F57A6",
  "Data": true,
  "Success": true,
  "ErrorCode": "null",
  "ErrorMessage": "null",
  "HttpStatusCode": "200"
}
```

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

### Configure Druid Connection Pool for Hologres

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/user-guide/use-jdbc-to-connect-to-hologres

Example XML configuration for setting up a Druid connection pool to connect to Hologres. It includes properties for JDBC URL, username, password, connection pool sizing (initial, min, max), timeout settings, and validation queries. It is recommended to configure `keepAlive=true` and use Druid 1.1.12+.

```XML
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
  <!-- jdbc_url is the connection URL of the Hologres instance, which can be obtained from the instance configuration page in the console.-->
  <property name="url" value="${jdbc_url}" />
  <!-- jdbc_user is the Access ID of the user account in the Hologres instance. -->
  <property name="username" value="${jdbc_user}" />
  <!-- jdbc_password is the Access Secret corresponding to the user account in the Hologres instance. -->
  <property name="password" value="${jdbc_password}" />
  <!-- Configure initial connection pool size, minimum connections, maximum connections. -->
  <property name="initialSize" value="5" />
  <property name="minIdle" value="10" />
  <property name="maxActive" value="20" />
  <!-- Configure the timeout period for acquiring a connection. -->
  <property name="maxWait" value="60000" />
  <!-- Configure the interval for checking and closing idle connections, in milliseconds. -->
  <property name="timeBetweenEvictionRunsMillis" value="2000" />
  <!-- Configure the minimum survival time of a connection in the connection pool, in milliseconds. -->
  <property name="minEvictableIdleTimeMillis" value="600000" />
  <property name="maxEvictableIdleTimeMillis" value="900000" />
  <property name="validationQuery" value="select 1" />
  <property name="testWhileIdle" value="true" />
  <!-- Configure whether to check connection validity when acquiring a connection from the pool, true checks every time; false does not check. -->
  <property name="testOnBorrow" value="false" />
  <!-- Configure whether to check connection validity when returning a connection to the pool, true checks every time; false does not check. -->
  <property name="testOnReturn" value="false" />
  <property name="keepAlive" value="true" />
  <property name="phyMaxUseCount" value="100000" />
  <!-- Configure filters for monitoring statistics interception. -->
  <property name="filters" value="stat" />
</bean>
```

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

### SQL: Optimizing Timestamp Conversion to HQE with to_timestamp

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

Following the previous example, this snippet shows how to rewrite a SQL query to leverage Hologres's more efficient HQE (Hologres Query Engine). By replacing the `::timestamp` cast with the `to_timestamp` function, the `EXPLAIN` plan no longer shows `ExecuteExternalSQL`, indicating the operation is now handled by HQE.

```SQL
EXPLAIN SELECT to_timestamp(a,'YYYY-MM-DD HH24:MI:SS') FROM pqe_test;
```

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

### Hologres EXPLAIN ANALYZE Optimization Advice

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/explain-and-explain-analyze

This section outlines the automated tuning suggestions provided by Hologres's `EXPLAIN ANALYZE` based on the execution results. These recommendations aim to improve query performance by addressing issues such as missing indexes, outdated statistics, or data skew.

```APIDOC
ADVICE:
  - Table xxx misses bitmap index: Suggests setting distribution key, clustering key, or bitmap index for the table.
  - Table xxx Miss Stats! please run 'analyze xxx';: Indicates that the table lacks up-to-date statistics, recommending an `analyze` command.
  - shuffle data xxx in different shards! max rows is 20, min rows is 0: Points to potential data skew issues across shards.
```

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

### Create Schema and Table in Hologres

Source: https://help.aliyun.com/zh/hologres/developer-reference/developer-guide/?spm=a2c4g.11186623.0.i1#undefined/developer-reference/create-schema

This SQL snippet demonstrates how to create a new schema, switch the current search path to that schema, and then create a table within the newly active schema. It also includes a command to verify the current schema after the switch.

```SQL
CREATE SCHEMA schemaname;--创建Schema。
SET search_path TO schemaname;--切换至目标Schema。
CREATE TABLE blink_demo (id text); --在目标Schema中创建表。
SELECT CURRENT_SCHEMA();--查看当前Schema。
```
Tokens:
Raw