版本：1.0.13

Halo_Cepat的基本使用

设置协调器节点和Worker节点

设置协调器节点

在协调器节点上运行以下命令，注意“coordinator”需要能解析成IP

halo0root=# SELECT cepat_set_coordinator_host('coordinator',1921);
 cepat_set_coordinator_host 
----------------------------

(1 行记录)

创建Worker节点

在协调器节点上运行以下命令，注意“worker”需要能解析成IP

halo0root=# SELECT * from cepat_add_node('worker', 1921);
 cepat_add_node 
----------------
     2
 (1 行记录)

halo0root=# SELECT * from cepat_add_node('worker', 1922);
 cepat_add_node 
----------------
      4
 (1 行记录)

halo0root=# SELECT * FROM cepat_get_active_worker_nodes();
 node_name | node_port 
-----------+-----------
 worker    |   1922
 worker    |   1921
(2 行记录)

创建分布式表

使用create_distributed_table 在协调器节点上创建分布式表，分片表会均匀地创建在各个worker节点上

halo0root=# CREATE TABLE events (
halo0root(#  device_id bigint,
halo0root(#  event_id bigserial,
halo0root(#  event_time timestamptz default now(),
halo0root(#  data jsonb not null,
halo0root(#  PRIMARY KEY (device_id, event_id)
halo0root(# );
CREATE TABLE

halo0root=# SELECT create_distributed_table('events', 'device_id');
 create_distributed_table 
--------------------------

(1 行记录)

使用pg_dist_shard_placement 查看分片情况

halo0root=# SELECT * from pg_dist_shard_placement order by shardid, placementid;
 shardid | shardstate | shardlength | nodename | nodeport   | placementid 
---------+------------+-------------+----------+----------+-------------
 102075  |     1      |      0      | worker   |   **1921** |     69
 102076  |     1      |      0      | worker   |   **1922** |     70
 102077  |     1      |      0      | worker   |   **1921** |     71
 102078  |     1      |      0      | worker   |   **1922** |     72
(4 行记录)

插入数据后查看执行计划是否调用各个Worker节点的分片表。默认设置下，通过EXPLAIN查看只会列出1个Task的执行计划信息，若要查看所有Task的执行计划信息，需要设置cepat.explain_all_tasks参数为on。

halo0root=# INSERT INTO events (device_id, data)

halo0root-# SELECT s % 100, ('{"measurement":'||random()||'}')::jsonb FROM generate_series(1,1000000) s;

INSERT 0 1000000

halo0root=# set cepat.explain_all_tasks='on';

SET

halo0root=# EXPLAIN SELECT count(*) FROM events;

              QUERY PLAN    
-------------------------------------------------------------------------------------------------------------------------

Aggregate (cost=250.00..250.02 rows=1 width=8)

-> Custom Scan (Cepat Adaptive) (cost=0.00..0.00 rows=100000 width=8)

Task Count: 4

Tasks Shown: All

-> Task

Node: host=worker port=1921 dbname=halo0root

-> Finalize Aggregate (cost=5785.18..5785.19 rows=1 width=8)

-> Gather (cost=5785.07..5785.18 rows=1 width=8)

Workers Planned: 1

-> Partial Aggregate (cost=4785.07..4785.08 rows=1 width=8)

-> Parallel Seq Scan on events_102075 events (cost=0.00..4386.65 rows=159365 width=0)

-> Task

Node: host=worker port=1922 dbname=halo0root

-> Finalize Aggregate (cost=6184.36..6184.37 rows=1 width=8)

-> Gather (cost=6184.25..6184.36 rows=1 width=8)

Workers Planned: 1

-> Partial Aggregate (cost=5184.25..5184.26 rows=1 width=8)

-> Parallel Seq Scan on events_102076 events (cost=0.00..4752.60 rows=172660 width=0)

-> Task

Node: host=worker port=1921 dbname=halo0root

-> Finalize Aggregate (cost=5386.00..5386.01 rows=1 width=8)

-> Gather (cost=5385.88..5385.99 rows=1 width=8)

Workers Planned: 1

-> Partial Aggregate (cost=4385.88..4385.89 rows=1 width=8)

-> Parallel Seq Scan on events_102077 events (cost=0.00..4020.71 rows=146071 width=0)

-> Task

Node: host=worker port=1922 dbname=halo0root

-> Finalize Aggregate (cost=5904.18..5904.19 rows=1 width=8)

-> Gather (cost=5903.97..5904.18 rows=2 width=8)

Workers Planned: 2

-> Partial Aggregate (cost=4903.97..4903.98 rows=1 width=8)

-> Parallel Seq Scan on events_102078 events (cost=0.00..4574.77 rows=131678 width=0)

(32 行记录)

创建参考表

使用create_reference_table 在协调器节点上创建参考表，参考表同时创建到各个worker节点上并保证数据一致

halo0root=# CREATE TABLE device_types (

halo0root(#  device_type_id int primary key,

halo0root(#  device_type_name text not null unique

halo0root(# );

CREATE TABLE

halo0root=# SELECT create_reference_table('device_types');

 create_reference_table 
 ------------------------

(1 行记录)

在协调器节点上插入数据，查看各个Worker节点表的情况

halo0root=# INSERT INTO device_types (device_type_id, device_type_name) VALUES (55, 'laptop');

INSERT 0 1

Worker 节点 1：

halo0root=# select * from device_types;

 device_type_id | device_type_name 

----------------+------------------

       55 | laptop

(1 行记录)

Worker 节点 2：

halo0root=# select * from device_types;

 device_type_id | device_type_name 

----------------+------------------

       55 | laptop

(1 行记录)