Couchbase SQL Importer

This tool allows you to copy the content of a your tables into Couchbase.

The import

Import all rows as JSON document. All table columns are JSON attributes.
Optionnaly it is possible to create views, the import tool create 2 types of views:
- A view all/by_type that allows to query document by type (table)
- Views for each type (table) to query on Primary Key (type/by_pk)
- All the views are using the _count reduce function.

If you do not want to build it from source, you can download the JAR here

Usage

Configure an import.properties file with all the parameters

 ## SQL Information ##
 sql.connection=jdbc:mysql://192.168.99.19:3306/world?zeroDateTimeBehavior=convertToNull
 sql.username=root
 sql.password=password

 ## Couchbase Information ##
 cb.uris=http://localhost:8091/pools
 cb.bucket=default
 cb.password=

 ## Import information
 import.tables=ALL
 import.createViews=true
 import.typefield=type
 import.fieldcase=lower

Download the JDBC driver for your database

Run the following command

 java -cp "./CouchbaseSqlImporter.jar:./mysql-connector-java-5.1.25-bin.jar" com.couchbase.util.SqlImporter import.properties

Notes

This tool is a small utility that I have developed in few hours as a demonstrator.

I have tested the tool mainly on MySQL with some sample schemas: World (few hundreds of records), Employees (4 millions records); but I have not tried to optimize the import "speed" (for example multithreading, divide select is chunks, ...).

I addition to the tests that I need to do with multiple databases types, I also need to create a test suite.

To do

Implement a test suite
Provide "Report/Logging"
Create Document keys using PK columns
- Error/Log for long keys (>250char)
Test multiple databases, at least Oracle

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

Hello,
first thanks for your simple app,
I tried it in

OS: CentOS7, ojdbc7.jar, Oracle11g,
and, i didnot installed oracle-client on my host but copied necessary files and libs to able connect to oracle. I tested connection with sqldeveloper and sqlplus, they are OK!
i useing below oracle URL in my properties file
sql.connection=jdbc:oracle:thin://IP:Port/SID
and this command to run
java -cp "./CouchbaseSqlImporter.jar:./ojdbc7.jar" com.couchbase.util.SqlImporter import.properties;
but i faced below Exception:

COUCHBASE SQL IMPORTER

Importing table(s)
from : jdbc:oracle:thin://10.200.6.253:1521/focusdb
to : [http://localhost:8091] - default

Exporting Table : focuscities
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.couchbase.util.SqlImporter.getConnection(SqlImporter.java:397)
at com.couchbase.util.SqlImporter.createViewsForPrimaryKey(SqlImporter.java:294)
at com.couchbase.util.SqlImporter.importTable(SqlImporter.java:216)
at com.couchbase.util.SqlImporter.importData(SqlImporter.java:192)
at com.couchbase.util.SqlImporter.main(SqlImporter.java:87)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:506)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
... 10 more
Caused by: java.net.ConnectException: 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 oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:161)
at oracle.net.nt.ConnOption.connect(ConnOption.java:159)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:428)
... 15 more
IO Error: The Network Adapter could not establish the connection

Create Couchbase views for 'types' ....
com.couchbase.client.vbucket.ConnectionException: Connection URI is either incorrect or invalid as it cannot be parsed.
at com.couchbase.client.vbucket.config.ConfigurationParserJSON.parseBase(ConfigurationParserJSON.java:59)
at com.couchbase.client.vbucket.ConfigurationProviderHTTP.readPools(ConfigurationProviderHTTP.java:216)
at com.couchbase.client.vbucket.ConfigurationProviderHTTP.getBucketConfiguration(ConfigurationProviderHTTP.java:147)
at com.couchbase.client.CouchbaseConnectionFactory.getVBucketConfig(CouchbaseConnectionFactory.java:231)
at com.couchbase.client.CouchbaseClient.(CouchbaseClient.java:237)
at com.couchbase.client.CouchbaseClient.(CouchbaseClient.java:175)
at com.couchbase.util.SqlImporter.getCouchbaseClient(SqlImporter.java:384)
at com.couchbase.util.SqlImporter.createTableViews(SqlImporter.java:377)
at com.couchbase.util.SqlImporter.importData(SqlImporter.java:197)
at com.couchbase.util.SqlImporter.main(SqlImporter.java:87)
          FINISHED

more: there is no firewall issue and when I checked firewall log, i found

MyIP OracleIP Accepted TCP 1521 MyIP OracleIP Close - TCP FIN Traffic Traffic Log Info thfwz1
MyIP OracleIP Accepted TCP 1521 MyIP OracleIP Creation Traffic Traffic Log Info thfwz1

could you please assist me?

tgrall / couchbase-sql-importer Goto Github PK