[GitHub] [age] Chidera6 commented on issue #966: How to export graph data for Giraph analysis?

["Chidera6 (via GitHub)" <gi...@apache.org>]

Chidera6 commented on issue #966:
URL: https://github.com/apache/age/issues/966#issuecomment-1579968502

   To export graph created by AGE, we can use the postgres COPY command and export the data to a CSV file, this is how the syntax looks like.
   ```
    COPY ( SELECT *  FROM <table name> ) TO 'absolute/path/to/export.csv' WITH CSV HEADER;
   ```
   For example, supposing we have a graph with edges and vertex like the following below,
   ```
   SELECT create_graph('library');
   SELECT * FROM cypher('library', $$ CREATE (n:Book {name : "Purple Hibiscus"}) $$) AS (a agtype);
   SELECT * FROM cypher('library', $$ CREATE (n:Book {name : "A Time to Kill"}) $$) AS (a agtype);
   SELECT * FROM cypher('library', $$ CREATE (n:Book {name : "If Tomorrow Comes"}) $$) AS (a agtype);
   SELECT * FROM cypher('library', $$ CREATE (n:Author {name : "Chimamanda Adichie"}) $$) AS (a agtype);
   SELECT * FROM cypher('library', $$ CREATE (n:Author {name : "Sidney Sheldon"}) $$) AS (a agtype);
   SELECT * FROM cypher('library', $$ CREATE (n:Author {name : "John Grisham"}) $$) AS (a agtype);
   
   SELECT * FROM cypher('library', $$ MATCH (a:Book), (b:Author)
   WHERE a.name = 'Purple Hibiscus' AND b.name = 'Chimamanda Adichie' CREATE (a)-[e:WrittenBy]->(b)RETURN e $$) as (e agtype);
   SELECT * FROM cypher('library', $$ MATCH (a:Book), (b:Author)
   WHERE a.name = 'A Time to Kill' AND b.name = 'John Grisham'
   CREATE (a)-[e:WrittenBy]->(b) RETURN e $$) as (e agtype);
   SELECT * FROM cypher('library', $$ MATCH (a:Book), (b:Author)
   WHERE a.name = 'If Tomorrow Comes' AND b.name = 'Sidney Sheldon' CREATE (a)-[e:WrittenBy]->(b) RETURN e $$) as (e agtype);
   
   ```
   
   Now, Postgres COPY command exports tables or SQL expressions to CSV, creating a graph in AGE creates different tables for edges, graphs and labels.
   To view all the tables in one graph, you can use the command below in a psql shell  `\dt library.*`.
   This will output the following for the graph created above.
   ```
   Schema  |       Name       | Type  |  Owner
   ---------+------------------+-------+---------
    library | Author           | table | chidera
    library | Book             | table | chidera
    library | WrittenBy        | table | chidera
    library | _ag_label_edge   | table | chidera
    library | _ag_label_vertex | table | chidera
   ```
   To export an edge or vertex, Its schema and the table name is used.
   ```
   COPY ( SELECT *  FROM library._ag_label_vertex ) TO '/mnt/c/Users/HP/ex.csv' WITH CSV HEADER;```
   To export the edges and vertices, an SQL expression can be used.
   ```
   COPY (
     SELECT v1.id AS "first_vertex_id", v1.properties AS "first_vertex_properties",
            e.id AS "edge_id", e.start_id, e.end_id, e.properties AS "edge_properties",
            v2.id AS "second_vertex_id", v2.properties AS "second_vertex_properties"
     FROM library._ag_label_vertex AS v1
     JOIN library._ag_label_edge AS e ON v1.id = e.start_id
     JOIN library._ag_label_vertex AS v2 ON e.end_id = v2.id
   ) TO '/mnt/c/Users/HP/data.csv' WITH CSV HEADER;
   ```
   Visit [this blog](https://dev.to/rafsun42/how-apache-age-turns-a-relational-dbms-into-a-graph-dbms-1i48) for more information.  


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org