PolarDB:Quick Start

Cypher syntax reference

Properties are key-value pairs enclosed in {}. The key is a string; the value can be a string, number, or array.

{name: 'Reeves'}
{title: 'The Matrix', released: 1997}

Node syntax:

()                                          -- anonymous, unlabeled node
(matrix)                                    -- node with a variable
(:Movie)                                    -- node with a label constraint
(matrix:Movie)                              -- variable + label
(matrix:Movie {title: 'The Matrix'})        -- variable + label + property
(matrix:Movie {title: 'The Matrix', released: 1997})

Variables are scoped to a single statement. The label constraint :Movie limits pattern matches to nodes with that label.

Edge syntax:

--                                          -- undirected edge
-->                                         -- directed edge (anonymous)
-[role]->                                   -- directed edge with a variable
-[:ACTED_IN]->                              -- directed edge with a type label
-[role:ACTED_IN]->                          -- variable + type
-[role:ACTED_IN {roles: ['Neo']}]->         -- variable + type + property

Query structure

In PolarDB, wrap Cypher queries in the cypher() function from the ag_catalog schema. The function returns a set of records.

SELECT * FROM cypher('graph_name', $$
  /* Your Cypher query */
$$) AS (result1 agtype, result2 agtype);

Install the AGE extension

After the extension is created, enable it:

CREATE EXTENSION age;

Configure the connection

For each connection, add ag_catalog to the search_path and load the extension:

SET search_path = ag_catalog, "$user", public;
SELECT * FROM get_cypher_keywords() limit 0;

To avoid repeating this for every connection, use a privileged account to set it permanently at the database level:

ALTER DATABASE <dbname> SET search_path = "$user", public, ag_catalog;
ALTER DATABASE <dbname> SET session_preload_libraries TO 'age';

Grant access to regular users

Grant the USAGE permission on ag_catalog to allow regular users to query graph data:

GRANT USAGE ON SCHEMA ag_catalog TO <username>;

If the user needs to create tables (read/write user), also grant the CREATE permission:

GRANT CREATE ON DATABASE <dbname> TO <username>;

Create a graph

Create a graph named moviedb:

SELECT create_graph('moviedb');

Insert data

Insert sample data representing movies and the people who worked on them:

SELECT * FROM cypher('moviedb', $$
  CREATE (matrix:Movie {title: 'The Matrix', released: 1997})
  CREATE (cloudAtlas:Movie {title: 'Cloud Atlas', released: 2012})
  CREATE (forrestGump:Movie {title: 'Forrest Gump', released: 1994})
  CREATE (keanu:Person {name: 'Keanu Reeves', born: 1964})
  CREATE (robert:Person {name: 'Robert Zemeckis', born: 1951})
  CREATE (tom:Person {name: 'Tom Hanks', born: 1956})
  CREATE (tom)-[:ACTED_IN {roles: ['Forrest']}]->(forrestGump)
  CREATE (tom)-[:ACTED_IN {roles: ['Zachry']}]->(cloudAtlas)
  CREATE (robert)-[:DIRECTED]->(forrestGump)
$$) AS (result1 agtype);

This creates 6 nodes (3 Movie, 3 Person) and 3 edges (2 ACTED_IN, 1 DIRECTED):

Query data

Use MATCH to find nodes or edges that match a pattern, and RETURN to specify what to return.

Find all movies:

SELECT * FROM cypher('moviedb', $$
  MATCH (m:Movie)
  RETURN m
$$) AS (result1 agtype);

Expected output (3 rows):

Find all `ACTED_IN` edges:

SELECT * FROM cypher('moviedb', $$
  MATCH (:Person)-[r:ACTED_IN]->(:Movie)
  RETURN r
$$) AS (result1 agtype);

Expected output (2 rows):

Filter results

Add a WHERE clause to filter results with a Boolean expression.

Find a specific movie by title:

SELECT * FROM cypher('moviedb', $$
  MATCH (m:Movie)
  WHERE m.title = 'The Matrix'
  RETURN m
$$) AS (result1 agtype);

Find actors in a specific movie:

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person)-[:ACTED_IN]->(m)
  WHERE m.title = 'Forrest Gump'
  RETURN p
$$) AS (result1 agtype);

Find actors in movies released after 2000:

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person)-[:ACTED_IN]->(m)
  WHERE m.released > 2000
  RETURN p, m
$$) AS (result1 agtype, result2 agtype);

Create a node or edge

Use CREATE to add new nodes or edges.

Add a person:

SELECT * FROM cypher('moviedb', $$
  CREATE (:Person {name: 'Tom Tykwer', born: 1965})
$$) AS (result1 agtype);

Add a directed edge between existing nodes:

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person), (m:Movie)
  WHERE p.name='Tom Tykwer' AND m.title='Cloud Atlas'
  CREATE (p)-[:DIRECTED]->(m)
$$) AS (result1 agtype);

Use MERGE to avoid duplicate data

Running CREATE multiple times inserts duplicate records. Use MERGE instead to perform an upsert: if the node or edge already exists, MERGE returns it as-is; if not, it creates it.

Add a person (no duplicate if run again):

SELECT * FROM cypher('moviedb', $$
  MERGE (:Person {name: 'Tom Cruise', born: 1962})
$$) AS (result1 agtype);

Add an undirected friendship edge:

SELECT * FROM cypher('moviedb', $$
  MATCH (t1:Person),(t2:Person)
  WHERE t1.name='Tom Hanks' AND t2.name='Tom Cruise'
  MERGE (t1)-[:FRIEND]-(t2)
$$) AS (result1 agtype);

Update properties

Use SET to add or update properties on existing nodes or edges.

Update a property value:

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person {name: 'Tom Tykwer', born: 1965})
  SET p.born = 1970
  RETURN p
$$) AS (result1 agtype);

Add a new property:

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person {name: 'Tom Tykwer', born: 1970})
  SET p.gender = 'male'
  RETURN p
$$) AS (result1 agtype);

Delete nodes and edges

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person)-[r:DIRECTED]->(m:Movie)
  WHERE p.name='Tom Tykwer' AND m.title='Cloud Atlas'
  DELETE r
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person {name: 'Tom Tykwer', born: 1970})
  DELETE p
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (p:Person {name: 'Tom Hanks', born: 1956})
  DETACH DELETE p
$$) AS (result1 agtype);

SELECT * FROM cypher('moviedb', $$
  MATCH (m:Movie {title: 'Cloud Atlas', released: 2012})
  REMOVE m.released
  RETURN m
$$) AS (result1 agtype);