DEV Community: Zainab-Saad

Why use Apache AGE?

Zainab-Saad — Sun, 19 Mar 2023 18:25:23 +0000

Apache AGE is an extension currently based on powerful and robust relational database postreSQL. AGE can handle complex graph queries in an optimized manner while supporting rdbms features such as stored procedures, triggers, constraints, multi-version concurrency control, ACID transactions etc.

The extension allows hybrid querying between SQL and cypher queries.

AGE's implementation is based on openCypher - a graph query language originally used for querying graph database such as Neo4j. The biggest advantage of using AGE is that users do not need to migrate data from the relational database in order to use the graph analytics and visualization.

Moreover, AGE has the following features:

Flexibility - users can query multiple graphs at the same time
Fast Graph Processing - the relationships between the nodes/entities of the graph can be processed efficiently

Applications

Real-time fraud detection in financial services
Traceability and master data management in manufacturing
Criminal and tax fraud investigation
Recommendation Systems
Social Network

References

Why use graph database?

Zainab-Saad — Sun, 19 Mar 2023 17:53:32 +0000

Graph database uses nodes/vertices to store entities and edges to store relationships between these entities. Most of the value of graph databases are derived from these relationships. These relationships represent complex interconnected relations between the nodes.

Examples of graph databases include Neo4j. Examples of relational databases are postgreSQL, MySQL, Oracle etc.

We use graph databases because it provides:

Flexibility
Graph databases are schema-free and thus any change in the properties (attributes) of a node or edge does not require the entire schema to be altered. This is quite unlike relational databases such as postgreSQL where if one record has a different set of attributes, you need to change the entire schema of the relation/table.
Agility
Graph databases being time-efficient are a great choice in agile development where there is a need for efficient data storage and management under a time constraint.

Size vs Complexity Relation
There is a size vs complexity relation between the graph and relational database. It is a fact that the graph databases perform the best when the complexity of relationships between the entities is to be scaled whereas relational databases perform best when size of the database needs to be scaled.

References

Install Apache AGE Viewer

Zainab-Saad — Wed, 15 Mar 2023 08:26:05 +0000

What is Apache AGE Viewer

Apache AGE Viewer is a web user interface for the visualization of graphs created in AGE - A multi-model database that supports graph database on top of the already existing relational database while also supporting JSON and key-value data.

AGE Viewer has the following prospects:

Provides data visualization
Supports complex graph queries
Helps users discover the relationships among strongly connected entities in the graph

Prerequisites

You should have the following installed on your system.

git
postgreSQL
Apache AGE

If you are on linux, follow the guide on How to install postgreSQL and Apache AGE from source code

Installing AGE Viewer

Step 1 Go to the directory postgres_age
cd postgres_age

Step 2 Install node.js and npm. It is recommened to install using node version manager. nvm manages different versions of node on your system and thus prevents any clashes between the different projects using different node version. Install nvm following the guide here
You can check if nvm is installed correctly using nvm --version
Check if the specified version of node is installed using node --version

Step 3 Clone the AGE Viewer GitHub repository
git clone https://github.com/apache/age-viewer.git

Step 4 Start the database server for the database cluster named pg_age

cd postgres/postgresql-11.18
bin/pg_ctl -D pg_age -l logfile start

Step 5 Go to directory age-viewer

cd ../../
cd age-viewer

Step 6 Setup AGE Viewer development server on local system.

npm run setup
npm run start

This takes you to AGE Viewer running on localhost

Step 7 Configure Viewer with the database you want to connect. Provide the details as here:

Connect URL : 127.0.0.1
Connect Port: 5432
Database Name: practicedb
Username: your_username_on_linux
Password: your_system_password

Start querying the database

Make sure you have the database server running. Example queries:

Create graph

SELECT create_graph('country');

Create vertices with the label City and property name

SELECT * FROM cypher('country', $$
CREATE (a: City {name: "A"}),
(b: City {name : "B"}),
(c : City {name : "C"}),
(d: City {name : "D"}),
(e : City {name : "E"})
$$) AS (result agtype);

View all the cities(verices)

SELECT * FROM cypher('country', $$
MATCH (u : City)
RETURN u
$$) AS (result agtype);

It shows the 5 vertices created as

Create edges between nodes as

SELECT * FROM cypher('country', $$
MATCH (a: City {name: "A"}), (b: City {name : "B"})
CREATE (a)-[e1: CONNECTED {time : 2}]->(b)
$$) AS (result agtype);

SELECT * FROM cypher('country', $$
MATCH (b: City {name: "B"}), (c: City {name : "C"})
CREATE (b)-[e1: CONNECTED {time : 1}]->(c)
$$) AS (result agtype);

SELECT * FROM cypher('country', $$
MATCH (a: City {name: "A"}), (d: City {name : "D"})
CREATE (a)-[e1: CONNECTED {time : 4}]->(d)
$$) AS (result agtype);

SELECT * FROM cypher('country', $$
MATCH (d: City {name: "D"}), (e: City {name : "E"})
CREATE (d)-[e1: CONNECTED {time : 3}]->(e)
$$) AS (result agtype);

View the graph

SELECT * FROM cypher('country', $$
MATCH(u : City)-[e : CONNECTED]->(v : City)
RETURN u, e, v
$$) AS (u agtype, e agtype, v agtype);

Try something interesting here
You can notice that there is a variable length edge from A to C and A to D i.e; A is indirectly connected to C and E

SELECT * FROM cypher('country', $$
MATCH(a : City {name : "A"})-[e : CONNECTED* ]->(x : City)
RETURN a, e, x
$$) AS (a agtype, e agtype, x agtype);

References:

Install PostgreSQL and AGE from Source Code

Zainab-Saad — Sun, 12 Feb 2023 08:16:10 +0000

What is Apache AGE

AGE is an acronym for A Graph Extension - an extension built on the top of already existing object relational database management system (ORDBMS)PostgreSQL. AGE is a multi-model database which means that it supports the following

graph database and cypher queries
relational database and SQL queries
JSON
Key value database
SQL and/or cypher Hybrid Querying

In this article we will look into the following

Installation of PostgreSQL 11.18 from source code
Installation of AGE from source code

Prerequisites

This article uses the commands for Linux Operating System. You should have Linux installed either on the virtual machine or dual boot alongside Windows/MacOS

For Windows users: here
For MacOS users: here

You should also have git installed on your system. To check if git is installed, type into terminal
git --version
If it returns the version of git, you are good to go else see the installation guide on Git Doc

Installing postgres

Step 1 Make a directory and a subdirectory where you will install postgres

mkdir postgres_age
cd postgres_age
mkdir postgres
cd postgres

Step 2 Install the Linux libraries before the actual installation. The commands may vary for different OS. Check this link out if you are not Ubuntu.
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison

Step 3 Install the development files for postres from postgresql-server-dev-xx package. This command will vary according to the version of Linux. Check out the compatible command for your version here. Note: Install postgres 11 or 12 as these are the versions AGE is compatible with. I am on Jammy(22.04 LTS), so I use the following command
sudo apt install postgresql-server-dev-all

Step 4 Download the tar file to install postgres which will extract to the directory /postgres_age/postgres
wget https://ftp.postgresql.org/pub/source/v11.18/postgresql-11.18.tar.gz && tar -xvf postgresql-11.18.tar.gz && rm -f postgresql-11.18.tar.gz

Step 5 Installing psql. While running ./configure, enable debugging flag. To read more on the flags, read Doc. pwd will install postgres in the current directory

cd postgresql-11.18
./configure --enable-debug --enable-cassert --prefix=$(pwd) CFLAGS="-ggdb -Og -fno-omit-frame-pointer"
make install
cd ../../

Installing AGE

Step 6 Clone the AGE GitHub Repository
git clone https://github.com/apache/age.git

Step 7 Installing and configuring AGE with psql

cd age
sudo make PG_CONFIG=/home/zainab/postgres_age/postgres/postgresql-11.18/bin/pg_config install
make PG_CONFIG=/home/zainab/postgres_age/postgres/postgresql-11.18/bin/pg_config installcheck

If you see that all the tests have passed as shown in the image below, then AGE is installed successfully

Database Initialization

Step 8 Initialize a database cluster. You can think of database cluster as The collection of databases which will be managed by single instance of running server.

cd ..
cd postgres/postgresql-11.18
bin/initdb pg_age

Step 9 Start server
bin/pg_ctl -D pg_age -l logfile start
Now create a database named practicedb
bin/createdb practicedb

Step 10 Launch the postgres command line client psql
bin/psql practicedb

Create and Load AGE

Step 11 Create and Load AGE extension in order to start using it

CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

Step 12 Start querying using cypher commands

SELECT create_graph('people');
SELECT * FROM cypher('people', $$ CREATE (n: Person {name : "Zainab", bornIn : "Pakistan"}) $$ ) AS (a agtype);
SELECT * FROM cypher ('people', $$ CREATE (n : Person {name : "John", bornIn : "US"}) $$ ) AS (a agtype);
SELECT * FROM cypher('people', $$ MATCH (v) RETURN v $$) as (v agtype);

The output is shown below

To get more information about Apache AGE, explore the website

References

If you find any ambiguity or incorrect statement, let me know in comment section

Happy learning!