Is it possible to create a graph in AGE using existing table in the database?

Question

I have just started with Apache AGE extension. I am exploring the functionalities of graph database. Is there a way to create a graph from existing tables/schema such that the table becomes the label and the attributes become the properties for the vertex?

The create_graph('graph name') is used for creating graphs but I can only create a new graph using this function.

Answer 1

It's not as simple as that. For a start you have to understand this.

When deriving a graph model from a relational model, keep in mind some general guidelines.

1. A row is a node.

2. A table name is a label name.

3. A join or foreign key is a relationship.

Using those relationships, you can model out the data. This is if you need to ensure no errors.

Without an example here is the Dynamic way of creating Graph from Relational model.

1st make a PostgreSQL function that takes in the arguments. Example, name and title of Person. It will create a node.

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

2nd use the function like so,

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

You'll have created a node for every row in SQL_SCHEMA.Person

Answer 2

I am now building the appropriate queries for inserting data by looping through table.

This biggest difference I discovered between the example and my solution is using "%1$s", "%2$s", etc. for the substitution in format command. This allowed me to use int, boolean or other supported types in AGE.

Answer 3

To export data from a PGSQL table to an AGE graph, you can try exporting a CSV file. For example, if you have the following table called employees:

SELECT * from employees;

 id |          name          | manager_id |   title    
----+------------------------+------------+------------
  1 | Gabriel Garcia Marquez |            | Boss
  2 | Dostoevsky             |          1 | Director
  3 | Victor Hugo            |          1 | Manager
  4 | Albert Camus           |          2 | Engineer
  5 | Haruki Murakami        |          3 | Analyst
  6 | Virginia Woolf         |          1 | Consultant
  7 | Liu Cixin              |          2 | Manager
  8 | Franz Kafka            |          4 | Intern
  9 | Daphne Du Maurier      |          7 | Engineer

First export a CSV using the following command:

\copy (SELECT * FROM employees) to '/home/username/employees.csv' with csv header

Now you can import this into AGE. Remember that for a graph database, the name of the table is the name of the vertex label. The columns of the table are the properties of the vertex.

First make sure you create a label for your graph. In this case, the label name will be 'employees', the same as the table name.

SELECT create_vlabel('graph_name','employees');

Now we load all the nodes of this label (each row from the original table is one node in the graph).

SELECT load_labels_from_file('graph_name','employees','/home/username/employees.csv');

Now your graph should have all the table data of the employees table.

More information can be found on the documentation: https://age.apache.org/age-manual/master/intro/agload.html