AGE graph search - Hologres - Alibaba Cloud Documentation Center

Usage notes

AGE graph search is supported only in Hologres V5.0 and later.
AGE graph search is based on the age extension, which is installed automatically after you upgrade the instance. No manual action is required.
The id column of a vertex table or an edge table is generated by the system. Do not specify it when you write data.
A graph maps to a schema, and a label maps to a table in that schema.
Cypher DML (CREATE, SET, REMOVE, DELETE, DETACH DELETE, and MERGE) is partially supported and works for simple graph writes and updates. For complex or bulk workloads, use standard SQL INSERT, UPDATE, and DELETE instead.
Vertices with multiple labels, such as MATCH (n:Person:Employee), are not supported. Assign a single label to each vertex and store the other categories in the properties column.
Cypher does not support datetime(). Use timestamp() instead.

Enable the extension

The AGE extension is installed automatically after you upgrade the instance. In each session where you use graph search, we recommend that you set search_path:

SET search_path = ag_catalog;

Manage graphs and labels

Create a graph

When you create a graph, Hologres creates a schema with the same name as the graph and generates two default label tables:

CALL ag_catalog.hg_age_create_graph('finGraph');

Create a label

A label is either a vertex label or an edge label, which maps to a vertex table or an edge table in the schema:

-- Vertex label
CALL ag_catalog.hg_age_create_vlabel('finGraph', 'person');

-- Edge label
CALL ag_catalog.hg_age_create_elabel('finGraph', 'transfer');

Drop a label

CALL ag_catalog.hg_age_drop_label('finGraph', 'person');

Note

You cannot drop the default labels _ag_label_vertex and _ag_label_edge. The force parameter is not supported when you drop a label.

Drop a graph

When you drop a graph, all label tables in the graph are dropped in cascade:

CALL ag_catalog.hg_age_drop_graph('finGraph', true);

Manage graph data

Hologres AGE provides two ways to manage graph data: standard SQL DML, where you run INSERT, UPDATE, and DELETE directly against label tables, and Cypher DML, where you run write operations with graph semantics through ag_catalog.cypher. Use SQL DML for complex or bulk workloads. Use Cypher DML when you want to express a write operation as a graph pattern.

Use SQL DML

Run standard SQL write operations directly against the table that backs a label. This approach fits bulk imports and updates with complex conditions.

Insert vertex data

Build vertex properties with agtype_build_map. The system generates the id value automatically:

INSERT INTO "finGraph"."person"(properties) VALUES
  (ag_catalog.agtype_build_map('name', 'Alice', 'age', 30)),
  (ag_catalog.agtype_build_map('name', 'Bob',   'age', 25));

Insert edge data

An edge table contains the start_id and end_id columns, which point to the id values of the start vertex and the end vertex:

INSERT INTO "finGraph"."transfer"(start_id, end_id, properties)
SELECT s.id, e.id, ag_catalog.agtype_build_map('amount', 1000)
FROM "finGraph"."person" s, "finGraph"."person" e
WHERE s.properties ->> '"name"'::agtype = 'Alice'
  AND e.properties ->> '"name"'::agtype = 'Bob';

Note

Property keys are stored as JSON strings in agtype. To extract a property value, use the properties ->> '"key"'::agtype syntax.

Update and delete data

Run SQL UPDATE or DELETE directly against the label table:

-- Update vertex properties
UPDATE "finGraph"."person"
SET properties = ag_catalog.agtype_build_map('name', 'Alice', 'age', 31)
WHERE properties ->> '"name"'::agtype = 'Alice';

-- Delete a vertex
DELETE FROM "finGraph"."person"
WHERE properties ->> '"name"'::agtype = 'Eve';

-- Delete an edge
DELETE FROM "finGraph"."transfer"
WHERE properties ->> '"amount"'::agtype = '1000';

Note

To delete an edge, run DELETE against the edge table. When you delete a vertex, maintain the related edges manually, because cascading deletes are not supported in the current version.

Use Cypher DML

Hologres AGE lets you write and update graph data with Cypher DML, which fits cases where you want to express write operations in graph semantics.

Supported DML clauses

Clause	Description
CREATE	Creates a vertex, an edge, or a path.
SET	Sets or updates the properties of a vertex or an edge.
REMOVE	Removes the properties of a vertex or an edge.
DELETE	Deletes a vertex or an edge.
DETACH DELETE	Deletes a vertex and all of its edges.
MERGE	Matches the pattern if it exists, or creates it if it does not.

Basic syntax

You also run DML statements through ag_catalog.cypher(). A write clause returns 0 rows when it succeeds. To check the result, run a separate MATCH query.

SELECT * FROM ag_catalog.cypher('finGraph', $$
  CREATE (:person {name: 'Alice', age: 30})
$$) AS (result agtype);

Create data

-- Create a single vertex
SELECT * FROM ag_catalog.cypher('finGraph', $$
  CREATE (:person {name: 'Alice', age: 30})
$$) AS (result agtype);

-- Create two vertices and one edge
SELECT * FROM ag_catalog.cypher('finGraph', $$
  CREATE (:person {name: 'Bob', age: 25})-[:transfer {amount: 1000}]->(:person {name: 'Carol', age: 35})
$$) AS (result agtype);

-- Create a relationship based on MATCH
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person {name: 'Alice'}), (b:person {name: 'Bob'})
  CREATE (a)-[:transfer {amount: 500}]->(b)
$$) AS (result agtype);

Update data

-- Set properties
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (p:person {name: 'Alice'})
  SET p.age = 31, p.active = true
$$) AS (result agtype);

-- Merge properties
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (p:person {name: 'Bob'})
  SET p += {score: 9.5}
$$) AS (result agtype);

-- Remove a property
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (p:person {name: 'Bob'})
  REMOVE p.score
$$) AS (result agtype);

Delete data

-- Delete a relationship
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (:person {name: 'Alice'})-[r:transfer]->(:person {name: 'Bob'})
  DELETE r
$$) AS (result agtype);

-- Delete an isolated vertex
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (p:person {name: 'Orphan'})
  DELETE p
$$) AS (result agtype);

-- Delete a vertex and its edges
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (p:person {name: 'Alice'})
  DETACH DELETE p
$$) AS (result agtype);

MERGE data

-- Create or match a vertex
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MERGE (:person {name: 'Grace', age: 27})
$$) AS (result agtype);

-- Create a relationship based on MATCH
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person {name: 'Alice'}), (b:person {name: 'Carol'})
  MERGE (a)-[:transfer {amount: 500}]->(b)
$$) AS (result agtype);

Limits

Limit	Description
One write clause per statement	A Cypher statement can contain only one CREATE, SET, REMOVE, DELETE, or MERGE clause.
Write clause at the end	The write clause must be the last clause in the statement and cannot be followed by RETURN.
Outer SQL must be a plain SELECT	WHERE, JOIN, LIMIT, CTEs, and set operations are not allowed.
Labels must be created in advance	The target label of a write operation must already be created with `hg_age_create_vlabel` or `hg_age_create_elabel`.
Property values must be literals	Only scalar literals and single-level maps are supported. Lists, nested maps, parameters, and property references are not supported.
Write targets must come from a labeled MATCH	The target of a SET or DELETE clause must come from `MATCH (n:Label)` or OPTIONAL MATCH.
WITH prefix not supported	You cannot use WITH before a write clause. Use MATCH or OPTIONAL MATCH instead.

Query with Cypher

Basic syntax

Run a query with ag_catalog.cypher('graph_name', $$ Cypher statement $$) AS (column_name type, ...):

SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (p:person) WHERE p.age <= 30 RETURN p.name AS name
$$) AS (name agtype);

Supported query capabilities

Capability	Description
MATCH / WHERE / RETURN	Basic graph pattern matching.
ORDER BY / LIMIT / SKIP / DISTINCT	Sorting, pagination, and deduplication.
Aggregate functions	`count`, `collect`, `min`, `max`, and `avg`.
WITH / UNWIND	Passing results between query parts and expanding lists.
Fixed-length multi-hop paths	For example, `(a)-[:KNOWS]->(b)-[:KNOWS]->(c)`.
Variable-length paths (VLE)	For example, `(a)-[:KNOWS*1..3]->(b)`.
OPTIONAL MATCH	Optional matching. The result is similar to that of a SQL left join.
Built-in functions	`id()`, `label()`, `type()`, `nodes()`, `relationships()`, `timestamp()`, `toUpper()`, and `toLower()`, as well as string concatenation.

Query examples

The following examples are based on a social network graph that contains the person vertex label and the KNOWS edge label.

Note

The KNOWS edge label used in this section and the transfer label in Create a label belong to different example scenarios. Before you run the examples in this section, create the required labels as described in Create a label and write the data.

Query a specific vertex

SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (n:person {name: 'Alice'}) RETURN n.name, n.age
$$) AS (name agtype, age agtype);

Filter by condition

SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (n:person) WHERE n.age > 28 RETURN n.name ORDER BY n.name
$$) AS (name agtype);

Traverse edges

-- Friends of Alice
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person {name: 'Alice'})-[:KNOWS]->(b:person)
  RETURN b.name ORDER BY b.name
$$) AS (friend agtype);

Multi-hop paths

-- Second-degree connections of Alice
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person {name: 'Alice'})-[:KNOWS]->(b:person)-[:KNOWS]->(c:person)
  RETURN a.name, b.name, c.name ORDER BY b.name
$$) AS (a agtype, b agtype, c agtype);

Variable-length paths (VLE)

-- First- and second-degree connections of Alice
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person {name: 'Alice'})-[:KNOWS*1..2]->(b:person)
  RETURN a.name, b.name ORDER BY b.name
$$) AS (a_name agtype, b_name agtype);

Note

By default, the explicit upper bound of a VLE pattern cannot exceed age.vle_max_depth, which defaults to 10. We recommend that you always specify a small explicit upper bound, such as *1..3.

Aggregation and grouping

SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person)-[:KNOWS]->(b:person)
  RETURN a.name, collect(b.name) AS friends ORDER BY a.name
$$) AS (name agtype, friends agtype);

OPTIONAL MATCH

SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person)
  OPTIONAL MATCH (a)-[:KNOWS]->(b:person)
  RETURN a.name, count(b), collect(b.name) ORDER BY a.name
$$) AS (person agtype, friend_count agtype, friends agtype);

Hybrid SQL and Cypher queries

The cypher() function works as a regular table function in a SQL query, so you can combine it with GROUP BY, JOIN, UNION, CTAS, and EXPLAIN:

-- Count the friends of each person
SELECT c.name, count(*) AS friend_count
FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person)-[:KNOWS]->(b:person) RETURN b.name
$$) AS c(name agtype)
GROUP BY c.name
ORDER BY friend_count DESC, c.name;

-- Join the results of two Cypher queries
SELECT a.name
FROM ag_catalog.cypher('finGraph', $$
  MATCH (n:person) RETURN n.name
$$) AS a(name agtype)
JOIN ag_catalog.cypher('finGraph', $$
  MATCH (n:person) WHERE n.age > 28 RETURN n.name
$$) AS b(name agtype)
ON a.name = b.name
ORDER BY a.name;

-- Create a result table
CREATE TABLE public.alice_friends AS
SELECT * FROM ag_catalog.cypher('finGraph', $$
  MATCH (a:person {name: 'Alice'})-[:KNOWS]->(b:person)
  RETURN b.name
$$) AS (friend_name agtype);

View metadata

Use the following views to check the metadata of graphs and labels:

-- List all graphs
SELECT * FROM hologres.hg_ag_graph;

-- List all labels in a specific graph
SELECT graph, name, id, kind, relation
FROM hologres.hg_ag_label
WHERE graph = 'finGraph'
ORDER BY id;

FAQ

Q: The property values returned by a Cypher query are enclosed in quotation marks. How do I convert them to plain strings?

A: Cypher returns property values as the agtype type. Cast them in the outer SQL query, for example with ::text as in name::text.
Q: A VLE query fails with the error "exceeding age.vle_max_depth". What do I do?

A: Lower the upper bound of the variable-length path. For example, change *1..11 to *1..3. You can also adjust the age.vle_max_depth GUC as needed.
Q: How do I check whether a Cypher query uses an index?

A: In the current version, AGE graph search relies mainly on base table scans. You can run EXPLAIN to view the execution plan. If you have strict performance requirements, watch for optimizations in later versions.