Synchronize relational tables-PolarDB(PolarDB)-阿里云帮助中心

This topic describes how to synchronize data from a relational table to a graph database.

Prerequisites

Use a high-privilege account to create the ganos_graph extension.
Note
- This extension depends on Apache AGE. You must install and enable the Apache AGE extension before installing this one.
- If you encounter an error such as ERROR: invalid extension name: "ganos_graph" when installing the extension, contact us.
```
CREATE EXTENSION IF NOT EXISTS ganos_graph;
```

You must create a graph and labels before you use the functions.

SELECT create_graph('<graph_name>');
SELECT create_vlabel('<graph_name>', '<label_name>');
SELECT create_elabel('<graph_name>', '<label_name>');

How it works

To synchronize operations from a relational database to a graph database, you can use triggers to ensure data consistency. When an insert, update, or delete operation occurs in a table of the relational database, a trigger automatically captures the change and applies the corresponding operation to the graph database.

Vertex triggers

age_create_vertex_insert_trigger

Creates a trigger to insert a vertex.

Syntax

age_create_vertex_insert_trigger('<graph_name>', 
                                '<label_name>', 
                                '<table_name>',
                                '<vertex_primary_key_field>' default 'id',
                                '<custom_property_handler_function>')

Note

The <vertex_primary_key_field> must be of an integer type: int4 (32-bit integer) or int8 (64-bit integer).

The <custom_property_handler_function> is a user-defined function that maps properties from the relational table to the graph vertex. The function signature is as follows:

CREATE OR REPLACE FUNCTION custom_insert_function(NEW RECORD, columns TEXT) 
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_vertex_update_trigger

Creates a trigger to update a vertex.

Syntax

age_create_vertex_update_trigger('<graph_name>', 
                                '<label_name>', 
                                '<table_name>',
                                '<vertex_primary_key_field>' default 'id',
                                '<custom_property_handler_function>')

Note

The <vertex_primary_key_field> must be of an integer type: int4 (32-bit integer) or int8 (64-bit integer).
The <custom_property_handler_function> is a user-defined function that maps properties from the relational table to the graph vertex. This function also supports retaining old property values. The function signature is as follows:
```
CREATE OR REPLACE FUNCTION custom_update_function(OLD RECORD, NEW RECORD, columns TEXT) 
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name_old', OLD.name, 'name_new', NEW.name);
END;
$$ LANGUAGE plpgsql;
```

age_create_vertex_delete_trigger

Creates a trigger to delete a vertex.

Syntax

age_create_vertex_delete_trigger('<graph_name>', 
                                '<label_name>', 
                                '<table_name>',
                                '<vertex_primary_key_field>' default 'id',
                                '<custom_property_handler_function>')

Note

The <vertex_primary_key_field> must be of an integer type: int4 (32-bit integer) or int8 (64-bit integer).
The <custom_property_handler_function> is a user-defined function that runs additional logic when a vertex is deleted, such as logging the deleted data or performing cleanup operations. The function signature is as follows:
```
CREATE OR REPLACE FUNCTION custom_delete_function(OLD RECORD,  columns TEXT) 
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'delete: %', OLD;
END;
$$ LANGUAGE plpgsql;
```

Edge triggers

age_create_edge_insert_trigger

Creates a trigger to insert an edge.

Syntax

age_create_edge_insert_trigger('<graph_name>', 
                                '<edge_label_name>', 
                                '<edge_table_name>',
                                '<start_vertex_label_name>',
                                '<end_vertex_label_name>',
                                '<edge_table_pk_field>' default 'id',
                                '<start_vertex_pk_field>' default 'from_id',
                                '<end_vertex_pk_field>' default 'to_id',
                                '<custom_property_handler_function>')

Note

All primary key fields must be of an integer type: int4 (32-bit integer) or int8 (64-bit integer).

The <custom_property_handler_function> is a user-defined function that maps properties from the relational table to the graph edge. The function signature is as follows:

CREATE OR REPLACE FUNCTION custom_insert_function(NEW RECORD, columns TEXT) 
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_edge_update_trigger

Creates a trigger to update an edge.

Syntax

age_create_edge_update_trigger('<graph_name>', 
                                '<edge_label_name>', 
                                '<edge_table_name>',
                                '<start_vertex_label_name>',
                                '<end_vertex_label_name>',
                                '<edge_table_pk_field>' default 'id',
                                '<start_vertex_pk_field>' default 'from_id',
                                '<end_vertex_pk_field>' default 'to_id',
                                '<custom_property_handler_function>')

Note

All primary key fields must be of an integer type: int4 (32-bit integer) or int8 (64-bit integer).

The <custom_property_handler_function> is a user-defined function that maps properties from the relational table to the graph edge. The function signature is as follows:

CREATE OR REPLACE FUNCTION custom_update_function(OLD RECORD, NEW RECORD, columns TEXT) 
RETURNS JSON AS $$
BEGIN
    RETURN json_build_object('name_old', OLD.name, 'name_new', NEW.name);
END;
$$ LANGUAGE plpgsql;

age_create_edge_delete_trigger

Creates a trigger to delete an edge.

Syntax

age_create_edge_delete_trigger('<graph_name>', 
                                '<edge_label_name>', 
                                '<edge_table_name>',
                                '<edge_table_pk_field>' default 'id',                                
                                '<custom_property_handler_function>')

Note

The <edge_table_pk_field> must be of an integer type: int4 (32-bit integer) or int8 (64-bit integer).

The <custom_property_handler_function> is a user-defined function that runs additional logic when an edge is deleted. The function signature is as follows:

CREATE OR REPLACE FUNCTION custom_delete_function(OLD RECORD,  columns TEXT) 
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'delete: %', OLD;
END;
$$ LANGUAGE plpgsql;

Example

This example shows how to create triggers that synchronize relational tables with graph elements. These triggers automatically update the graph whenever data in the relational table is inserted, updated, or deleted.

Relational table	Graph element
`person_table`	`Person`
`city_table`	`City`
`lives_in_table`	`LivesIn`

Create graph structure

SELECT ag_catalog.create_graph('graph_age_create_trigger');

-- Create vertex labels
SELECT ag_catalog.create_vlabel('graph_age_create_trigger', 'Person');
SELECT ag_catalog.create_vlabel('graph_age_create_trigger', 'City');

-- Create edge label
SELECT ag_catalog.create_elabel('graph_age_create_trigger', 'LivesIn');

Create relational tables

-- Create tables
CREATE TABLE person_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT
);

CREATE TABLE city_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    population INT
);

CREATE TABLE lives_in_table (
    id SERIAL PRIMARY KEY,
    person_id INT,
    city_id INT,
    since DATE
);

Create triggers

SELECT age_create_vertex_insert_trigger('graph_age_create_trigger', 'Person', 'person_table');
SELECT age_create_vertex_update_trigger('graph_age_create_trigger', 'Person', 'person_table');
SELECT age_create_vertex_delete_trigger('graph_age_create_trigger', 'Person', 'person_table');

SELECT age_create_vertex_insert_trigger('graph_age_create_trigger', 'City', 'city_table');
SELECT age_create_vertex_update_trigger('graph_age_create_trigger', 'City', 'city_table');
SELECT age_create_vertex_delete_trigger('graph_age_create_trigger', 'City', 'city_table');

SELECT age_create_edge_insert_trigger('graph_age_create_trigger', 'LivesIn', 'lives_in_table', 'Person', 'City', 'id', 'person_id', 'city_id');
SELECT age_create_edge_update_trigger('graph_age_create_trigger', 'LivesIn', 'lives_in_table', 'Person', 'City', 'id', 'person_id', 'city_id');
SELECT age_create_edge_delete_trigger('graph_age_create_trigger', 'LivesIn', 'lives_in_table', 'id');

Insert data

-- Insert data into tables
INSERT INTO person_table (name, age) VALUES ('Alice', 30);
INSERT INTO person_table (name, age) VALUES ('Bob', 25);

INSERT INTO city_table (name, population) VALUES ('New York', 8419000);
INSERT INTO city_table (name, population) VALUES ('Los Angeles', 3980000);

INSERT INTO lives_in_table (person_id, city_id, since) VALUES (1, 1, '2010-01-01');
INSERT INTO lives_in_table (person_id, city_id, since) VALUES (2, 2, '2015-06-15');

The graph now contains the inserted data:

SELECT * from cypher('graph_age_create_trigger', $$    
MATCH (p1)-[r]->(p2)    
RETURN properties(p1), properties(r), properties(p2)$$) as (p agtype, r agtype, p2 agtype);

{"id": 1, "age": 30, "name": "Alice"} | {"id": 1, "since": "2010-01-01", "city_id": 1, "person_id": 1} | {"id": 1, "name": "New York", "population": 8419000} 
{"id": 2, "age": 25, "name": "Bob"}   | {"id": 2, "since": "2015-06-15", "city_id": 2, "person_id": 2} | {"id": 2, "name": "Los Angeles", "population": 3980000}

Update data

UPDATE person_table SET age = 31 WHERE id = 1;
UPDATE city_table SET population = 100000 WHERE id = 1;
UPDATE lives_in_table SET since = '2025-01-01' WHERE id = 1;

The updates are now reflected in the graph:

SELECT * FROM cypher('graph_age_create_trigger', $$    
MATCH (p1)-[r]->(p2)    
RETURN properties(p1), properties(r), properties(p2)$$) as (p agtype, r agtype, p2 agtype);

{"id": 1, "age": 31, "name": "Alice"} | {"id": 1, "since": "2025-01-01", "city_id": 1, "person_id": 1} | {"id": 1, "name": "New York", "population": 100000} 
{"id": 2, "age": 25, "name": "Bob"}   | {"id": 2, "since": "2015-06-15", "city_id": 2, "person_id": 2} | {"id": 2, "name": "Los Angeles", "population": 3980000}

Delete data

DELETE FROM lives_in_table WHERE person_id = (SELECT id FROM person_table WHERE name = 'Bob' limit 1);
DELETE FROM person_table WHERE name = 'Bob';

SELECT * FROM cypher('graph_age_create_trigger', $$    
MATCH (p1)-[r]->(p2)    
RETURN properties(p1), properties(r), properties(p2)$$) as (p agtype, r agtype, p2 agtype); 

{"id": 1, "age": 31, "name": "Alice"} | {"id": 1, "since": "2025-01-01", "city_id": 1, "person_id": 1} | {"id": 1, "name": "New York", "population": 100000}