DEV Community

Cover image for Exploring Apache-AGE Hybrid Queries
Nnaemeka Daniel John
Nnaemeka Daniel John

Posted on • Updated on

Exploring Apache-AGE Hybrid Queries

#postgres #database #apacheage #graphql

Today we are going to learn how to make Hybrid Queries using Apache-Age's extension for PostgreSQl.

If you don't already have PostgreSQl or AGE installed you can visit Apache-Age Setup and PostgreSQL Installation site to get more information on how you can get your own instance of PostgreSQL and the AGE extension.

One of the most powerful features of AGE is the ability to perform hybrid queries, which allow users to combine the power of graph queries with traditional SQL queries. In this blog post, we will explore the concept of hybrid queries and how they can be used to improve data analysis.

What are Hybrid Queries?

A hybrid query is a powerful feature of AGE for PostgreSQL that allows users to perform both graph and SQL queries on their data. With hybrid queries, users can take advantage of the strengths of both types of queries to gain deeper insights into their data.

Graph queries are designed to work with inter-connected data, such as social networks, recommendation engines, and knowledge graphs. They allow users to traverse the relationships between nodes in the graph and extract meaningful information. SQL queries, on the other hand, are ideal for working with structured data, such as tables and columns, and performing operations like aggregation, filtering, and sorting.

How to perform Hybrid Queries using AGE in PostgreSQL

To perform hybrid queries in AGE, you need to install and configure the extension. Once you've installed the extension and loaded AGE, you can create a graph in PostgreSQL using the AGE syntax. For example, the following command creates a graph called "my_graph":

demo=# SELECT * FROM ag_catalog.create_graph('my_graph');
NOTICE:  graph "my_graph" has been created
Enter fullscreen mode Exit fullscreen mode

Once you have created a graph, you can add vertices and edges into it. For example, the following command adds a node with the label "Person" and the property "name" set to "Daniel" and other properties to the graph:

demo=# SELECT * FROM cypher('my_graph', $$
CREATE (:Person {id: 123, name: 'Daniel', title: 'Software Engineer', city:'Lagos'}) $$)
as (n agtype);
 n
---
(0 rows)
Enter fullscreen mode Exit fullscreen mode

We can add another node:

demo=# SELECT * FROM cypher('my_graph', $$
CREATE (:Person {id: 124, name: "Emmanuel", title: "Developer", city: "Sydney"}) $$)
as (n agtype);
 n
---
(0 rows)
Enter fullscreen mode Exit fullscreen mode

And now we can add our edge by creating a relationship between 'Daniel' and 'Emmanuel':

demo=# SELECT * FROM cypher('my_graph', $$ MATCH (a:Person), (b:Person)
demo$# WHERE a.name = 'Daniel' AND b.name = 'Emmanuel'
demo$# CREATE (a)-[e:Friends]->(b)
demo$# RETURN e $$) as (e agtype);
Enter fullscreen mode Exit fullscreen mode

We can now check to see the relationship between 'Daniel' and 'Emmanuel' using:

demo=# SELECT * FROM cypher('my_graph', $$ MATCH
(a {name: 'Daniel'})-[r]-(b {name: 'Emmanuel'})
RETURN type(r) $$) as (type agtype);
   type
-----------
 "Friends"
(1 row)
Enter fullscreen mode Exit fullscreen mode

After creating our graph and inserting our vertices and edges, let's create and view our table in SQL:

demo=# CREATE TABLE Persons (
id INT,
name VARCHAR,
city VARCHAR,
demo(# hired_year INT);
CREATE TABLE
demo=# SELECT * FROM Persons;
 id | name | city | hired_year
----+------+------+------------
Enter fullscreen mode Exit fullscreen mode

And let's INSERT our VALUES:

demo=# INSERT INTO Persons (id, name, city, hired_year)
VALUES (123, '"Daniel"', 'Lagos', 2023);
INSERT 0 1
Enter fullscreen mode Exit fullscreen mode 
demo=# INSERT INTO Persons (id, name, city, hired_year)
VALUES (124, '"Emmanuel"', 'Sydney', 2018);
INSERT 0 1
Enter fullscreen mode Exit fullscreen mode

When we view our Table now:

demo=# SELECT * FROM Persons;
 id  |    name    |  city  | hired_year
-----+------------+--------+------------
 123 | "Daniel"   | Lagos  |       2023
 124 | "Emmanuel" | Sydney  |       2018
Enter fullscreen mode Exit fullscreen mode

Now Let's Perform Hybrid Queries

To run a hybrid query, we first need to define the graph pattern we want to match using the AGE syntax.

For example, the inner cypher query searches for the name 'Daniel' in 'my_graph' in the Label :Person and then returns it to the outer query, which SELECTS and returns the id, city and hired_year of the person whose name matches the inner (cypher) query which in this case is 'Daniel':

demo=# SELECT p.id, p.city, p.hired_year FROM Persons AS p
demo-# WHERE p.name in (SELECT name from cypher('my_graph',
demo(# $$ MATCH (v:Person) WHERE v.name='Daniel'
demo$# RETURN v.name $$) as (name VARCHAR(25)));
 id  | city  | hired_year
-----+-------+------------
 123 | Lagos |       2023
(1 row)
Enter fullscreen mode Exit fullscreen mode

The following is another example of Hybrid query, the inner cypher query MATCHES and returns all id(s) in 'my_graph' in the Label :Person to the outer SQL query which returns the names of the owners of the id(s) from the table Persons:

demo=# SELECT p.name FROM Persons AS p WHERE p.id in (
demo(# SELECT id FROM cypher('my_graph', $$
demo$# MATCH (v:Person) RETURN v.id $$)
demo(# AS (id int));
    name
------------
 "Daniel"
 "Emmanuel"
(2 rows)
Enter fullscreen mode Exit fullscreen mode

Benefits of Hybrid Queries

Hybrid queries offer several benefits over traditional SQL and graph queries.

Firstly, they allow users to work with complex, interconnected data sets in a more efficient and effective way. By combining the strengths of graph and SQL queries, users can gain deeper insights into their data and make more informed decisions.

Secondly, hybrid queries are highly flexible and customizable. Users can define complex graph patterns and apply SQL operations to the result set, allowing them to tailor the query to their specific needs.

Thirdly, hybrid queries are compatible with existing SQL tools and interfaces. This means that users can leverage their existing knowledge and skills in SQL to work with graph data, without needing to learn a new query language.

Finally, hybrid queries are supported by a growing number of open-source graph databases, including Apache AGE. This means that users have access to a range of tools and resources to help them get started with hybrid queries and graph databases.

Conclusion

In conclusion, hybrid queries are a powerful feature of the Apache AGE's extension for PostgreSQL that allow users to combine the strengths of graph and SQL queries to gain deeper insights into their data. By defining graph patterns and applying SQL operations to the result set, users can work with complex, interconnected data sets in a more efficient and effective way. Furthermore, the compatibility of hybrid queries with existing SQL tools and interfaces means that users can leverage their existing knowledge and skills in SQL to work with graph data. With the increasing popularity of graph databases and tools, hybrid queries are becoming an essential tool for data analysts and developers.

Relevant Resources

Top comments (0)

Read next

frontendmonster profile image

The Myth of GraphQL

ASafaeirad -

truongpx396 profile image

🌐 Golang RESTful API with Gin, Gorm, PostgreSQL 🐘

Truong Phung -

khold profile image

Inside the Database: A Deep Dive into Disk-Oriented DBMS.

Ahmed Khaled -

alialp profile image

Why Running Databases on Kubernetes is a Recipe for Disaster: The Case for a New Platform Designed for Stateful Workloads

Ali Alp -