Importing Data from Relationnel Table into Graph DB

[johnnyMick]

I have data saved in relationnel Tables, i want to Import them as a Graph data, so i can use Graph relations between them.

Is there a way to do that directly in SQL ? or do i have to use a Programming Language to query the data first from the relational tables first then create Cipher Queries to create the Data in the Graph DB ?

ps: i noticed that it's possible to Join the Select Data with Relationnel Tables, but here i'm searching to export the data from the Relationnel Table into the graph DB

[joe-fagan]

Is this referring to AGE or AgensGraph?

[johnnyMick]

in the AGE pdf documentation (advance Cypher Queries), it's possible to join regular table with Graph data like so

SELECT id,
graph_query.name = t.name as names_match,
graph_query.age = t.age as ages_match
FROM schema_name.sql_person AS t
JOIN cypher('graph_name', $$
MATCH (n:Person)
RETURN n.name, n.age, id(n)
$$) as graph_query(name agtype, ag

But what if i want to Import the data from the sql_person table into the a Graph DB as vertices ?

SELECT *
FROM cypher('graph_name', $$
CREATE (:Person {name: ‘Andres’, title: ‘Developer’)
$$) as (n agtype);

Do i have to use a Programming language to query the data from sql_person table than manipulate the data and create the above string to create the vertices ? Or can i just use SQL query directly to able to create all rows in the sql_person as a vertex in the Graph ?

[JoshInnis]

There are currently two ways to import data into a graph in AGE via sql. Both of these ways are new and not in 0.3.0. So you will need to download master to be able to do this. They will be in the 0.4.0 release, which should be released shortly.

1. Prepared Statements: https://www.postgresql.org/docs/11/sql-prepare.html.

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"}');

This would work for one row, so you would need a cursor to import the whole table

2. Dynamic SQL in a pl/pgSQL Function You could make a pl/pgsql function that takes in your arguments of 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;

This would create a vertex for every row in sql_schema.Person.

[johnnyMick]

@JoshInnis thanks for the reply and the detailed example