Graph Data Import

This section introduces an example to import graph data from external foreign files.

Example

  1. Install the extension ‘file_fdw’ necessary to use the AgensGraph foreign-data wrapper to interface with files on the server’s filesystem.

CREATE EXTENSION file_fdw;

  1. Create the data import server.

CREATE SERVER import_server FOREIGN DATA WRAPPER file_fdw;

  1. Create the foreign table to receive data from a foreign file.

CREATE FOREIGN TABLE vlabel_profile (id graphid, properties text)
SERVER import_server
OPTIONS( FORMAT 'csv', HEADER 'false', FILENAME '/path/file.csv', delimiter E'\t');

CREATE FOREIGN TABLE elabel_profile (id graphid, start graphid, "end" graphid, properties text)
SERVER import_server
OPTIONS( FORMAT 'csv', HEADER 'false', FILENAME '/path/file.csv', delimiter E'\t');

This ‘properties text’ item should be written differently depending on the situation. See the following example.

comments.csv

id|creationDate|locationIP|browserUsed|content|length		// This is the HEADER of CSV
2473901162497|1316237219961|77.240.75.197|Firefox|yes|3
2473901162498|1316225421570|213.180.31.8|Firefox|thanks|6
4123168604165|1345407771283|41.203.141.129|Firefox|LOL|3

In this case, the schema of foreign table should be as follows.

create foreign table comments_profile
(
   id int8,
   creationDate int8,
   locationIP varchar(80),
   browserUsed varchar(80),
   content varchar(2000),
   length int4
)
server import_server
options
(
 FORMAT 'csv',
 HEADER 'true',		//Indicates the presence of a csv header
 DELIMITER '|',
 NULL '',
 FILENAME '/path/to/comments.csv'
 );

  1. Execute the import. The data must be cast as type JSONB, since vertices and edges in AgensGraph are stored in the JSONB format.

CREATE VLABEL test_vlabel;

LOAD FROM vlabel_profile AS profile_name 
CREATE (a:test_vlabel =to_jsonb(row_to_json(profile_name)));

CREATE ELABEL test_elabel;

LOAD FROM elabel_profile AS profile_name
MATCH (a:test_vlabel), (b:test_vlabel)
WHERE (a).id = to_jsonb(profile_name).start AND (b).id = to_jsonb(profile_name).end
CREATE (a)-[:test_elabel]->(b);

Following the example above, you can do the following:

CREATE VLABEL comments;

LOAD FROM comments_profile AS ROW
CREATE (:comments =to_jsonb(row_to_json(row)));

Indexes with Data Import

The cost of maintaining indexes during bulk insertion is very expensive. AgensGraph provides grammars to toggle indexes by disabling them temporarily and reindexing them later. The disabled indexes do not interfere with bulk inserts. After bulk data import, the option REINDEX LABEL can be used. It will take some time but it is much faster than bulk insert with valid indexes.

CREATE VLABEL test_vlabel DISABLE INDEX;
OR
CREATE VLABEL test_vlabel;

ALTER VLABEL test_vlabel DISABLE INDEX;

-- DATA IMPORT 
REINDEX VLABEL test_vlabel;