PolarDB:PolarDB graph database quick start

Terms

• Graph: a data structure composed of nodes and edges. For example, you can use a graph to represent the complex relationships in a social network, in which each person is a node and their relationships, such as friendships, family ties, or professional connections, are edges.

  

• Node: a basic element in a graph database. A node represents an entity in a database. A node may have properties, which are key-value pairs that store information about the entity. For example, in a social network, you can use nodes to represent users, companies, and organizations.

• Edge: a connection between two nodes that represents the relationship between them. An edge may also have properties such as weight and direction to describe the strength, importance, or direction of the relationship. For example, in a social network, edges can represent various types of relationships among users, such as friendships, follows, or fans.

• Label: a classification or attribute used to identify and categorize nodes or edges within a graph. Labels help add semantic meaning to data and make data easier to understand, manage, and query. For example, in a social network, nodes can be assigned labels such as Person or Company, and edges can be assigned labels such as Knows or WorkIn.

• Property graph: a type of graph in which nodes or edges can have properties. The following figure is a property graph of professional relationships in which nodes and edges have properties.

  

• Graph database: a special type of database that uses graphs to store and manage data. In the database, nodes represent entities, and edges represent the relationships between entities. Graph databases are suitable for managing complex relationship data in domains such as social networks, trust networks, and knowledge graphs.

  Graph databases allow you to use a graph query language such as Cypher and Gremlin to query data. PolarDB supports OpenCypher. OpenCypher is an open source subset of the Cypher query language and is designed to be functionally equivalent to Cypher in most use cases.

Schema

Properties

In Cypher, properties are represented as key-value pairs enclosed in curly braces {}, similar to the structure in JSON. The key is a string that identifies the property, and the value can be a string, a number, or an array. For example, a property represented as {name: 'Reeves'} indicates that the name property has a value of Reeves.

Nodes

In Cypher, nodes are enclosed in parentheses (). Node representation examples:

()
(matrix)
(:Movie)
(matrix:Movie)
(matrix:Movie {title: 'The Matrix'})
(matrix:Movie {title: 'The Matrix', released: 1997})

Where,

• The simplest form () represents an anonymous and unlabeled node without properties.

• If you want to reference a node in another location in the same query, add a variable to the node. Example: (matrix). Variables are temporary and scoped to the current statement. Variables cannot be reused across different queries unless explicitly redefined.

• A colon (:) followed by a label such as :Movie declares the label of the node. Labels are used to categorize nodes and impose constraints on what types of nodes can be matched in queries.

• Key-value pairs enclosed in curly braces {} such as {title: 'The Matrix'} declare the properties of a node. For example, properties can be used to store information or restrict patterns.

Edges

In Cypher, an undirected edge is represented by a pair of short dashes --, and a directed edge is represented by an arrow <-- or -->. To add detailed information such as variables, types, and properties to an edge, use square brackets [...] to enclose them. Edge representation examples:

--
-->
-[role]->
-[:ACTED_IN]->
-[role:ACTED_IN]->
-[role:ACTED_IN {roles: ['Neo']}]->

Where,

• -- represents an anonymous undirected edge.

• --> represents an anonymous, directed edge.

• You can define a variable, such as role, which allows you to reference this relationship elsewhere in the same query.

• A colon (:) followed by a type, such as :ACTED_IN, declares the type of the relationship.

• Properties such as roles: ['Neo'] are defined in the same way as node properties.

Examples

This section uses a movie database as an example to describe how to use a PolarDB graph database. The movie database includes information about actors and movies.

Install the AGE extension

Use the privileged account to execute the following statement to install the Apache AGE (AGE) extension. For information about how to create a privileged account, see Create a database account.

CREATE EXTENSION age;

Configure the database

Each time you connect to the database, add the ag_catalog schema to the search_path to simplify queries, and invoke the get_cypher_keywords function to load the AGE extension:

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

We strongly recommend that you use the privileged account to set database parameters to permanently load the AGE extension. This way, you do not need to repeat the preceding operations each time you connect to the database.

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

Allow a regular user to use AGE

Grant USAGE permissions on the ag_catalog schema to a regular user.

GRANT USAGE ON SCHEMA ag_catalog TO <username>;

If the regular user has read-write permissions and needs to create tables and graphs, grant CREATE permissions on the database to the user.

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

Query structure

In PolarDB, cypher queries are executed by invoking a function called cypher in the ag_catalog schema. The function returns a SETOF records. Query example:

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

The graph_name parameter specifies the name of the graph. Write the actual Cypher query within /* */.

Create a graph

To use a graph, first create the graph. Use the create_graph function in the ag_catalog schema to create a graph.

Syntax:

SELECT create_graph('<graph_name>');

Example:

Create a graph named moviedb.

SELECT create_graph('moviedb');

Insert data

Execute the following SQL statement to insert data into the moviedb graph:

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);

The graph created contains six nodes and three edges. Three nodes are assigned the label Movie and three nodes are assigned the label Person. Two edges are assigned the label ACTED_IN and one edge is assigned the label DIRECTED. The following graph is created:

Query data

Data query

In Cypher, you can use the MATCH and RETURN clauses in combination to query graph data.

• The MATCH clause identifies the nodes and relationships that match the specified pattern.

• The RETURN clause specifies the values or results that you want to retrieve from the Cypher query.

Syntax:

SELECT * FROM cypher('graph_name', $$
  MATCH <patterns>
  RETURN <variables>
$$) AS (result1 agtype);

Examples:

• Find all nodes that are labeled as Movie.

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

• Find edges of type ACTED_IN that connect Person and Movie nodes.

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

Data filtering

If you want a graph query to return only a subset of the data of interest, you can use the WHERE clause in the query. This clause allows you to use a Boolean expression for filtering.

Examples:

• Find all movies titled The Matrix.

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

• Find all actors in the movie titled Forrest Gump.

  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

In Cypher, you can use the CREATE clause to create a node or edge.

Syntax:

SELECT * FROM cypher('<graph_name>', $$
  CREATE <patterns>
$$) AS (result1 agtype);

Examples:

• Create a node. Label the node as Person and define the name property of the node as Tom Tykwer and the birth year property as 1965.

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

• Create a directed edge between a Person node whose name property is Tom Tykwer and a Movie node whose name property is Cloud Atlas to represent that Tom Tykwer directed the movie Cloud Atlas.

  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

If you execute the same CREATE statement multiple times, duplicate data is inserted into the graph. To avoid such duplicate data, you can use the MERGE clause to insert data. If you use the MERGE clause to select or insert data, the system first checks whether the data already exists in the database. If the data exists, the system returns the existing data or upgrades the existing nodes or relationships. If the data does not exist, the system creates the node or relationship as specified.

Syntax:

SELECT * FROM cypher('<graph_name>', $$
  MERGE <patterns>
$$) AS (result1 agtype);

Examples:

• Create a node. Assign the Person label to the node and define its name property as Tom Cruise and the birth year property as 1962.

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

• Establish an edge between the two existing Person nodes Tom Hanks and Tom Cruise to represent the friendship relationship between them.

  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

To modify the properties of an existing node or relationship, you can first use the match clause to find the node or relationship and then use the SET clause to add or update the property.

Syntax:

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  SET <property>
  RETURN <variable>
$$) AS (result1 agtype);

Examples:

• Change the birth year property of the Person node whose name property is Tom Tykwer to 1970.

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

• Add a gender property male for the Person node whose name property is Tom Tykwer.

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

Remove data

In Cypher, you can use the DELETE clause to remove nodes and edges.

Remove an edge

To remove an edge, use the MATCH clause to find the edge that matches the pattern, and then use the DELETE keyword to delete the edge.

Syntax

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  DELETE <variable>
$$) AS (result1 agtype);

Example

Remove the edge relationship between the Person node Tom Tykwer and Movie node Cloud Atlas.

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);

Remove a node

To remove a node, use the MATCH keyword to find the node that meets the conditions, and then use the DELETE clause to remove the node.

Syntax

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  DELETE <variable>
$$) AS (result1 agtype);

Example

Remove the Person node whose name property is Tom Tykwer.

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

Remove nodes and edges

If a node has edges, you cannot directly remove the node. To remove a node with edges, you can use the DETACH DELETE statement to delete the edge relationships for this node and then delete the node.

Syntax

SELECT * FROM cypher('<graph_name>', $$
  MATCH <patterns>
  DETACH DELETE <variable>
$$) AS (result1 agtype);

Example

Deletes the Person node whose name property is Tom Hanks and its associated edges.

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

Remove a property

To remove a property of a node or edge, you can use the REMOVE keyword to remove the property.

Syntax

SELECT * FROM cypher('<graph_name>', $$
  MATCH <match_pattern>
  REMOVE <property>
$$) AS (result1 agtype);

Example

Delete the Cloud Atlas movie's released property.

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

Visualizer

Age Viewer is a visualization tool for graph databases and can display query results in graphs. For more information, see Age Viewer.