Importing from RDBMS to OrientDB: load edges only

Question

using Community Edition 2.1.11

I saw some similar questions on the Internet (ex., import edges to OrientDB using etl or orient-database.narkive.com/d8c4b82y/orientdb-etl-edge-creation-help), but they are not really resolved yet.

I'm implementing the flight connection search system. I have RDBMS (SQL Server) with two related tables - Locations and Flights. Each flight carries two locationIDs - locationFrom and locationTo.

When I import it to the graph, I want to see locations as vertices, connected with flights as edges. As I understood from the manual (Import-from-DBMS, I cannot post more than two links due to newbie limitations...), I should write two different JSONs for this purpose and run them by ETL. So, I can import the locations without any problem with this code:

    {
  "config": {
    log : "debug"
  },
  "extractor" : {
    "jdbc": { "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
              "url": "jdbc:sqlserver://localhost:1434;databaseName=mydb;integratedSecurity=true;",
              "userName": "root",
              "userPassword": "root",
              "query": "select * from locations" }
  },

  "transformers" : [
    { "vertex": { "class": "Location"} }
  ],
   "loader" : {
    "orientdb": {
      "dbURL": "plocal:C:\orientdb-community-2.1.11\databases\Test",
      dbUser: "admin",
      dbPassword: "admin",
      dbAutoDropIfExists: false,
      dbAutoCreate: true,
      tx: false,
      wal: false,
      batchCommit: 1000,
      dbType: "graph",
      indexes: [{class:"Location", fields:["id:string"], type:"UNIQUE_HASH_INDEX" }]
    }
  }
}

But when I try to import flights, I get into a problem, that I couldn't resolve even with the Google's help: the ETL does not want to import edges only. As a first intuitive purpose, I wrote something like that:

{
  "config": {
    log : "debug"
  },
  "extractor" : {
    "jdbc": { "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
              "url": "jdbc:sqlserver://localhost:1434;databaseName=mydb;integratedSecurity=true;",
              "userName": "root",
              "userPassword": "root",
              "query": "select * from flights" }
  },

  "transformers" : [
    { "edge": { "class": "flight", "direction" : "out", 
            "joinFieldName": "flightFromLocation",
            "lookup":"locationID", "unresolvedLinkAction":"CREATE"}

            { "class": "flight", "direction" : "in", 
            "joinFieldName": "flightToLocation",
            "lookup":"locationID", "unresolvedLinkAction":"CREATE"}
    }
  ],
   "loader" : {
    "orientdb": {
      "dbURL": "plocal:C:\orientdb-community-2.1.11\databases\Test",
      dbUser: "admin",
      dbPassword: "admin",
      dbAutoDropIfExists: false,
      dbAutoCreate: true,
      tx: false,
      wal: false,
      batchCommit: 1000,
      dbType: "graph",
      indexes: [{class:"flight", fields:["id:string"], type:"UNIQUE_HASH_INDEX" }]
    }
  }
}

In one of the threads in OrientDB's GoogleGroups I've found a post from Luca from OrientDB, that says that load only edges IS possible through ETL, but I still can not figure out, how to achieve it :( the only idea I have after two days of reading docs and googling is to import them as vertices, and then write some console JS function that will create the proper edges with the same properties...

Or maybe I'm missing something very basic? I'm totally new to Orient...

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

a simple method to do what you need is to import both your tables into two vertex classes with normal ETL process and then create the edges using a js function.

I made this dataset to recreate your situation after the import of both tables:

Here is the JS function:

parameters: flights_V_class, edge_class, location_class

var g=orient.getGraphNoTx();


var flightsV_table = g.command("sql","select from " + flights_V_class);

for(i=0; i < flightsV_table.length; i++){
  
  var id_from = flightsV_table[i].getProperty("locationFrom");

  var id_to = flightsV_table[i].getProperty("locationTo");
  
  var select_from = "select from "+location_class+" where id = "+id_from;
  var select_to = "select from "+location_class+" where id = "+id_to;
  
  g.command("sql","create edge " + edge_class + " from (" + select_from + ") to (" + select_to + ")");
}

After executing the function here is my data:

Then, after all, you can delete the temporary flights_V class.

Hope it helps. Bye.

Ivan

Ivan and @Alessandro, thanks a lot for your suggestions, I thought about JS too, but still wanted (based on Luka's post) to make it via ETL. But I realized (after posting a question) that I actually need a tool for synchronization, not just for import; so for now I write one based on Java API. Hope that in future the [link](https://github.com/orientechnologies/orientdb-labs/blob/master/Teleporter-Index.md) will be more customizable :) — grreeenn, Mar 03 '16 at 09:44

score 0 · Answer 2 · answered Mar 01 '16 at 14:39

I have tried with MySQL

I have created Location and Flight

Location.json

{
  "config": {
    log : "debug"
  },
  "extractor" : {
    "jdbc": { "driver": "com.mysql.jdbc.Driver",
              "url": "jdbc:mysql://localhost:3306/flights",
              "userName": "user",
              "userPassword": "password",
              "query": "select * from Location" 
              }
  },
  "transformers" : [
    { "vertex": { "class": "Location"} }
  ],
   "loader" : {
    "orientdb": {
      "dbURL": "yourPath",
      "dbUser": "admin",
      "dbPassword": "admin",
      "dbAutoDropIfExists": false,
      "dbAutoCreate": true,
      "tx": false,
      "wal": false,
      "batchCommit": 1000,
      "dbType": "graph",
      "indexes": [{class:"Location", fields:["id:string"], type:"UNIQUE_HASH_INDEX" }]
    }
  }
}

Flight.json

{
  "config": {
    log : "debug"
  },
  "extractor" : {
    "jdbc": { "driver": "com.mysql.jdbc.Driver",
              "url": "jdbc:mysql://localhost:3306/flights",
              "userName": "user",
              "userPassword": "password",
              "query": "select * from flight" 
              }
  },
  "transformers" : [
    { "vertex": { "class": "Fligth"} }
  ],
   "loader" : {
    "orientdb": {
      "dbURL": "yourPath",
      "dbUser": "admin",
      "dbPassword": "admin",
      "dbAutoDropIfExists": false,
      "dbAutoCreate": true,
      "tx": false,
      "wal": false,
      "batchCommit": 1000,
      "dbType": "graph",
      "indexes": [{class:"flight", fields:["id:string"], type:"UNIQUE_HASH_INDEX" }]
    }
  }
}

the etl process has imported the following records

You can use this JavaScript function

var g=orient.getGraphNoTx();
g.command("sql","CREATE CLASS Fligth2 EXTENDS E");
var fligth = g.command("sql","select from Fligth");
for(i=0;i<fligth.length;i++){
    var idFrom=fligth[i].getProperty("idFrom");
    var idTo=fligth[i].getProperty("idTo");
    var name=fligth[i].getProperty("name");
    print(name);
    var from=g.command("sql","select from Location where id = " +  idFrom);
    var to=g.command("sql","select from Location where id = " +  idTo);
    g.command("sql","create edge Fligth2 from " + from[0].getId() + " to " + to[0].getId() + " set name = '" + name + "'");
}
g.command("sql","drop class Fligth unsafe");
g.command("sql","UPDATE Location REMOVE id");

And you should have this structure

Importing from RDBMS to OrientDB: load edges only

2 Answers2