Failure to Migrate Data from Postgres to OrientDB


#1

Newbie to OrientDB.
I have roughly 12Million rows in a PostgresDB and I need to migrate them into OrientDB (both databases are running under Docker – if that matters?). I have carefully read through the documentation (v3.0.14) and must be missing something simple.

PostgresDB has roughly 12Million rows and I need to move them into OrientDB for a comparison/contrast as the developers start running tests. I created an OrientDB json transformer file:
{ "config": { "log": "debug" }, "extractor" : { "jdbc": { "driver": "org.postgres.jdbc.Driver", "url": "jdbc:postgresql://localhost/rides", "userName": "rootbeer", "userPassword": "", "query": "select * from rides" } }, "transformers" : [ { "vertex": { "class": "rides"} } ], "loader" : { "orientdb": { "dbURL": "plocal:/temp/databases/orientdbcrm", "dbAutoCreate": true } } }
–and then created the conversion command–
oetl.sh -jdriver postgresql -jurl jdbc:postgresql://localhost:5432/rides -juser postgres -jpasswd '' -ourl plocal:$ORIENTDB_HOME/databases/rides -s naive -nr java -v 1

which fell flat on it’s face as detailed below:
/orientdb/bin # oetl.sh -jdriver postgresql -jurl jdbc:postgresql://localhost:5432/rides -juser postgres -ourl plocal:$ORIENTDB_HOME/databases/rides -s naive -nr java -v 1 OrientDB etl v.3.0.13 - Veloce (build ac16faac50ef66f4ca5ae5f792b2145b9d0b53d4, branch 3.0.x) https://www.orientdb.com

`2019-01-24 15:54:26:925 INFO Detected limit of amount of simultaneously open files is 1048576, limit of open files for disk cache will be set to 523776 [ONative]Exception in thread “main” com.orientechnologies.orient.core.exception.OConfigurationException: Error on loading config file: postgresql at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:79) at com.orientechnologies.orient.etl.OETLProcessor.main(OETLProcessor.java:112)

Caused by: java.io.FileNotFoundException: postgresql (No such file or directory)

at java.io.FileInputStream.open0(Native Method)`

at java.io.FileInputStream.open(FileInputStream.java:195)

at java.io.FileInputStream.<init>(FileInputStream.java:138)

at com.orientechnologies.common.io.OIOUtils.readFileAsString(OIOUtils.java:145)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:69)`

Any guidance will be greatly appreciated – second day on the job with ORIENTDB :slight_smile:


#2

Hi,
to import data from RDBMS (Postgres in this case) to OrientDB, you can use Teleporter (free for community edition).
More info: http://orientdb.com/docs/3.0.x/teleporter/Teleporter-Home.html

Hope it helps
Regards,
Michela


#3

Thanks for the reply. Alas, Teleporter does not work for me either. In my instance, the only script in the bin directory is oetl.sh which appears to have been created in 2014. Go figure.

Following the Teleporter-Home.html examples, I tried the command below – I am still trying to understand why/how to deal with the error regarding too many open files.

/orientdb/bin # oetl.sh -jurl jdbc:postgresql://localhost:5432 -juser postgres -ourl plocal:$ORIENTDB_HOME/databases/rides

OrientDB etl v.3.0.13 - Veloce (build ac16faac50ef66f4ca5ae5f792b2145b9d0b53d4, branch 3.0.x) https://www.orientdb.com

2019-01-24 19:27:07:188 INFO Detected limit of amount of simultaneously open files is 1048576, limit of open files for disk cache will be set to 523776 [ONative]Exception in thread “main” com.orientechnologies.orient.core.exception.OConfigurationException: Error on loading config file: jdbc:postgresql://localhost:5432

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:79)

at com.orientechnologies.orient.etl.OETLProcessor.main(OETLProcessor.java:112)

Caused by: java.io.FileNotFoundException: jdbc:postgresql:/localhost:5432 (No such file or directory)

at java.io.FileInputStream.open0(Native Method)

at java.io.FileInputStream.open(FileInputStream.java:195)

at java.io.FileInputStream.<init>(FileInputStream.java:138)

at com.orientechnologies.common.io.OIOUtils.readFileAsString(OIOUtils.java:145)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:69)

... 1 more

And then I went to try this – but or course there isn’t an oteleporter.sh in the bin directory. Switching to oetl.sh, well that gave the same error

                ./oteleporter.sh -jdriver postgresql -jurl jdbc:postgresql://localhost:5432/rides -juser postgres -jpasswd '' -ourl plocal:$ORIENTDB_HOME/databases/rides
-s naive -nr java -v 2

/orientdb/bin # ./oetl.sh -jdriver postgresql -jurl jdbc:postgresql://localhost:5432/rides -juser postgres -ourl:$ORIENTDB_HOME/databases/rides -s naive -nr java -v 2

OrientDB etl v.3.0.13 - Veloce (build ac16faac50ef66f4ca5ae5f792b2145b9d0b53d4, branch 3.0.x) https://www.orientdb.com

2019-01-25 11:43:50:492 INFO Detected limit of amount of simultaneously open files is 1048576, limit of open files for disk cache will be set to 523776 [ONative]Exception in thread “main” com.orientechnologies.orient.core.exception.OConfigurationException: Error on loading config file: postgresql

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:79)

at com.orientechnologies.orient.etl.OETLProcessor.main(OETLProcessor.java:112)

Caused by: java.io.FileNotFoundException: postgresql (No such file or directory)

at java.io.FileInputStream.open0(Native Method)

at java.io.FileInputStream.open(FileInputStream.java:195)

at java.io.FileInputStream.<init>(FileInputStream.java:138)

at com.orientechnologies.common.io.OIOUtils.readFileAsString(OIOUtils.java:145)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:69)

And lastly, I even tried to run the migration using a ConvertText/JSON
Converter Text.

{

“config”: {

"log": "debug"

},

“extractor” : {

"jdbc": { "driver": "org.postgres.jdbc.Driver",

          "url": "jdbc:postgresql://localhost/rides",

          "userName": "root",

          "userPassword": "",

          "query": "select * from rides" }

},

“transformers” : [

{ “vertex”: { “class”: “rides”} }

],

“loader” : {

"orientdb": {

  "dbURL": "plocal:/temp/databases/orientdbcrm",

  "dbAutoCreate": true

}

}

}

It just occurred to me that I am running a timescale/timescaledb database – which should be the same as postgres. I’ll have to read up on that too.

Thanks again for the ideas…when/if I get this running, I’ll let you know

-Mike


#4

I was able to find the Migration Tools on my local instance of OreintDB (http://localhost:2480/studio/index.html#/dashboard/importers) and I worked through the Teleporter

UI, but it refused to allow me to input any type of Database Driver. The UI shows that there should be a list, but it is empty and I can’t type a value into that. Similarly, I entered ‘localhost’ for the database host value – as it is a docker image.

All attempts fail (command not found: teleporter/test) and then I tried the ETL. So this is my last attempt. Thanks in advance for your help…I’ll try another Graph database.
`{
“config”: {

“log”: “debug”

},

“extractor” : {

“jdbc”: { “driver”: “org.postgresql.Driver”,

“url”: “jdbc:postgresql://localhost/mysqlcrm”,

“userName”: “root”,

“userPassword”: “”,

“query”: “select * from rides” }

},

“transformers” : [

{ “vertex”: { “class”: “ride”} }

],

“loader” : {

“orientdb”: {

“dbURL”: “plocal:/temp/databases/orientdbcrm”,

“dbAutoCreate”: true

}

}

}`

/orientdb/bin # oetl.sh mike_ride_test.json

OrientDB etl v.3.0.13 - Veloce (build ac16faac50ef66f4ca5ae5f792b2145b9d0b53d4, branch 3.0.x) https://www.orientdb.com

2019-01-25 14:18:24:420 INFO Detected limit of amount of simultaneously open files is 1048576, limit of open files for disk cache will be set to 523776 [ONative]Exception in thread “main” com.orientechnologies.orient.core.exception.OConfigurationException: Error on creating ETL processor

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parse(OETLProcessorConfigurator.java:153)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parseConfigAndParameters(OETLProcessorConfigurator.java:92)

at com.orientechnologies.orient.etl.OETLProcessor.main(OETLProcessor.java:112)

Caused by: com.orientechnologies.orient.core.exception.OConfigurationException: [JDBC extractor] error on connecting to JDBC url ‘jdbc:postgresql://localhost/mysqlcrm’ using user ‘root’ and the password provided

at com.orientechnologies.orient.etl.extractor.OETLJDBCExtractor.configure(OETLJDBCExtractor.java:76)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.configureComponent(OETLProcessorConfigurator.java:158)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.configureExtractor(OETLProcessorConfigurator.java:216)

at com.orientechnologies.orient.etl.OETLProcessorConfigurator.parse(OETLProcessorConfigurator.java:115)

... 2 more

Caused by: org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:280)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)

at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)

at org.postgresql.Driver.makeConnection(Driver.java:454)

at org.postgresql.Driver.connect(Driver.java:256)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at com.orientechnologies.orient.etl.extractor.OETLJDBCExtractor.configure(OETLJDBCExtractor.java:73)

... 5 more

Caused by: java.net.ConnectException: Connection refused (Connection refused)

at java.net.PlainSocketImpl.socketConnect(Native Method)

at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)

at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)

at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

at java.net.Socket.connect(Socket.java:589)

at org.postgresql.core.PGStream.<init>(PGStream.java:70)

at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)

... 12 more

/orientdb/bin #