Apache AGE: Transform Relational Database to Graph Database

Udaya Ganesh — Thu, 21 Sep 2023 19:21:33 +0000

Introduction

Understanding the relationship between the different data members is very important for better working of data and the applications. From the very beginning data has been an important part of computer applications. Relational databases like MySQL and PostgreSQL can store the data very efficiently. But as the data grows and they have some complex relations between the data elements. It’s very important to understand the relations between data. Here comes the graph database. Unlike a relational database which uses tables or documents to store data, a graph database uses graph concepts to store and represent data effectively.

Figure 1 A relational Database

Graph Database

Graph database organizes or store data in the form of vertex and edges. It stores the relationship between data as an edge and the data as a node or vertex. So, it’s very helpful to understand the complex relations between the data.

Figure 2 A Graph database
Understanding the relation between the data is much easier with graph databases.

Apache AGE

Apache AGE is a powerful PostgreSQL extension which is used to convert relational databases into graph databases. AGE is an acronym for “A Graph Extension”. It combines both the advantages of relational databases using PostgreSQL with the Graph database. Apache AGE’s main goal or functionality is to provide graph data processing and analytics capabilities to all relational databases. With the help of this extension users can have access to graph query modeling within their existing relational databases. With the help of this extension users can read and write graph data in nodes and edges. And they can also use various graph algorithms to analyze data. Some of its main features are:
• Hybrid queries (OpenCypher and SLQ),
• Fast Graph Query processing
• Graph Visualization and Analytics

Why Apache AGE

It gives user to have the ability of both relational and graph data models within the same database system. As it is an extension of PostgreSQL which is a relational database. It’s an opensource project which is being managed by Apache Software Foundation. So, users can take advantage of its open source also.

How to Use AGE

You can check their official documentation to know more about AGE. Once you have installed the extension on your system you can easily use it and convert your already existing relational data into a graph database. You can easily select your platform and incorporate the relevant SDK into your code because it has some built-in drivers for Go, java, Nodejs and Python.

Convert relation to Graph database

There are currently two ways to import data into a graph in AGE via sql. These are supported in AGE version 0.4.0 release.

Method 1

So, you would need to format the query as

PREPARE create_person AS
SELECT *
FROM cypher('graph_name', $$
CREATE (:Person {name: $name, title: $title})
$$, $1) as (n agtype);

Then to run the basic example you would do

EXECUTE create_person('{"name": "Andres", "title": "Developer"}');

Method 2

You could make a pl/pgsql function that takes in your argument name and title and creates a node, such as

CREATE OR REPLACE FUNCTION public.create_person(name text, title text)
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $BODY$
BEGIN
load 'age';
SET search_path TO ag_catalog;
EXECUTE format('SELECT * FROM cypher(''graph_name'', $$CREATE (:Person {name: %s, title: %s})$$) AS (a agtype);', quote_ident(name), quote_ident(title));
END
$BODY$;

Then you could call the function like:

SELECT public.create_person(sql_person.name, sql_person.title)
FROM sql_schema.Person AS sql_person;

In this way by calling your pgsql function that you created you can convert your relational database into graph database.
If you want to further explore this conversion part you can check this issue from their official site.

Conclusion

Apache AGE is a big development. It expands the usage of data and database as both of relation as well as Graph modeling along with ensuring the complex relations are represented efficiently. As an extension of PostgreSQL (used by Apple, Spotify and NASA) AGE supports all the functionality and features of PostgreSQL while also offering a graph model to boot.

References

https://github.com/apache/age
https://neo4j.com/developer/graph-database/
https://age.apache.org/

DEV Community: Udaya Ganesh