Custom ETL OrientDB from PostgreSQL

Question

I am working on a Rails project, the database is OrientDB graph database. I need to transfer data from Postgres to OrientDB graph. I have written scripts to in Ruby to fetch data from postgres and load it into the graph structure by creating relevant edges and nodes.

However this process is very slow and is taking months to enter million records. The graph is somewhat a densely connected graph.

I wanted to use the inbuilt ETL configuration provided by OrientDB but it seems relatively complex since I need to create multiple vertexes from fields in the same table and then connect them. I referred to this documentation.

Can I write custom ETL to load data into OrientDB with the same speed as done by inbuilt ETL tool?

Also, are there any benchmarks for the speed of data loading into OrientDB.

Hi, can you try with teleporter, new OrientDB feature? Hope it helps. Regards — Michela Bonizzi
I am using Community Edition. No teleporter feature there. Only available in Enterprise edition. However I don't think that would be much different from ETL module. — nishant
For just one migration you can use Teleporter of Enterprise Edition by downloading the 45 days trial ;-) It's different if you want to keep OrientDB synchronized from Postgres, in that case, you'd need an Enterprise license. — Lvca
How is Teleporter of Enterprise Edition different from ETL module? Can I write Custom SQL quries for loading by applying checks on data. Also this migration is not one time task. It will be a continuous process where any changes in postgres has to be reflected in OrientDB. — nishant

Roberto Franchini Roberto Franchini · Accepted Answer · 2017-05-19T07:53:28

if ETL doesn't fit your needs you can write a custom importer using Java or any other JVM language of your choice. If you only need to import the db once, the best way is to use plocal access (embedded) and then move the resulting database under the server. With this approach, you can achieve the best performances, because the network isn't involved. The code should be something like this snippet:

    OrientGraphFactory fc = new OrientGraphFactory("plocal:./databases/import", "admin", "admin");

Connection conn =  DriverManager.getConnection("jdbc....");

Statement stmt = conn.createStatement();

ResultSet resultSet = stmt.executeQuery("SELECT * from table ");

while (resultSet.next()) {
  OrientGraph graph = fc.getTx();

  OrientVertex vertex1 = graph.addVertex("class:Class1", "name", resultSet.getString("name"));
  OrientVertex vertex2 = graph.addVertex("class:Class2", "city", resultSet.getString("city"));

  graph.addEdge(null, vertex1, vertex2, "class:edgeClass");
  graph.shutdown();

}


fc.close();
resultSet.close();

stmt.close();

conn.close();

It is more pseudo code than working code, but take it as a template for operations needed to import a single query/table from the original RDBMS. About performance, it is quite hard to give numbers, it depends on many factors: schema complexity, type of access (plocal, remote), lookups, connection's speed of the data source.

Few more words about teleporter. It will import the original database schema and data inside OrientDB, automatically. AFAIK you have a working OrientDB and for sure Teleporter will not create the same schema on OrientDb you did.

Custom ETL OrientDB from PostgreSQL

1 Answers