Transforming SQL Data Into Apache Age: Family Example

When it comes to data management, relational databases have been the go-to solution for decades. However, in recent years, there has been a surge in popularity for graph databases, which allow for more complex data relationships to be easily queried and analyzed. One such graph database is Apache Age, which is built on top of PostgreSQL and allows for SQL-like queries to be used alongside graph traversal. In this post, we will explore how to transform data from a relational database into Apache Age format using a simple example of people and their relationships.

SQL:

In sql we first need to define the table structure using this:

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  birth_date DATE NOT NULL
);

CREATE TABLE relative (
  id SERIAL PRIMARY KEY,
  person_id INTEGER REFERENCES people(id),
  relative_id INTEGER REFERENCES people(id),
  relationship VARCHAR(50) NOT NULL
);

The people table contains information about each individual, including their name, gender, and birth date. The relative table is used to store relationships between people. Each entry in the relative table includes the IDs of two individuals, their relationship to each other, and a unique ID for the relationship.

Now, let's say we have the following data to insert into our tables:

INSERT INTO people (name, gender, birth_date) VALUES
('Alice', 'F', '1990-01-01'),
('Bob', 'M', '1995-02-01'),
('Charlie', 'M', '1980-05-01'),
('David', 'M', '1970-10-01'),
('Eve', 'F', '1985-12-01');

INSERT INTO relative (person_id, relative_id, relationship) VALUES
(1, 2, 'sibling'),
(1, 5, 'parent'),
(3, 4, 'sibling'),

In this example, we have 5 people with various relationships to each other.

Apache Age:

To transform this data into Apache Age format, we can use the following queries:

-- Create graph
SELECT create_graph('graph');

-- Insert people as nodes
SELECT * FROM cypher('graph', $$
  CREATE (:person {id: 1, name: 'Alice', gender: 'F', birth_date: '1990-01-01'}),
         (:person {id: 2, name: 'Bob', gender: 'M', birth_date: '1995-02-01'}),
         (:person {id: 3, name: 'Charlie', gender: 'M', birth_date: '1980-05-01'}),
         (:person {id: 4, name: 'David', gender: 'M', birth_date: '1970-10-01'}),
         (:person {id: 5, name: 'Eve', gender: 'F', birth_date: '1985-12-01'})
$$) AS (n agtype);

Now, we need to enter the relationships:

SELECT * FROM cypher('graph', $$
  MATCH (a:person), (b:person)
  WHERE a.id = 1 AND (b.id = 2 OR b.id = 4)
  CREATE (a)-[e:rel {type: 'sibling'}]->(b)
  RETURN e
$$) AS (v agtype);

SELECT * FROM cypher('graph', $$
  MATCH (a:person), (b:person)
  WHERE a.id = 1 AND (b.id = 5)
  CREATE (a)-[e:rel {type: 'parent'}]->(b)
  RETURN e
$$) AS (v agtype);

Conclusion:

In conclusion, transforming data from SQL to Apache Age can be done using simple queries and is a powerful way to take advantage of graph databases. By converting relational data into a graph format, we can easily perform complex queries and gain new insights into the relationships between data points. With Apache Age's built-in graph analytics functions, we can go beyond simple data retrieval and perform advanced analysis to unlock the full potential of our data. By leveraging the benefits of graph databases, we can create more intelligent applications and make better, data-driven decisions.