Example code for using neo4j-etl with Microsoft SQL Server on local Docker

[peternorbeck]

Hello,

I've had a discussion on the neo4j #help-import slack channel about using neo4j-etl to export data from a local Microsoft SQL Server running on a Docker. With help from the great people there I got it working. Because there ara lacking documentation about this I wrote some documentation about what I did that maybe could be useful for others.

My settings:

• OSX
• Docker installed
• neo4j-etl version neo4j-etl-cli-1.2.0-BETA01
• Java version: 1.8.0_131
• Java $JAVA_HOME /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home
• Neo4j enterprise

Setup:

1. Setup Docker for Microsoft SQL Server

create file: docker-compose.yml

version: "3"

services:
  mssql:
    image: microsoft/mssql-server-linux
    container_name: Test_Mssql_Server
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Password
    ports:
      - 1433:1433
    volumes:
      - mssqldata:/var/opt/mssql

volumes:
  mssqldata:

Start docker with $ docker-compose up

2. Import Northwind demo sql data to mssql db (or use your own)

• Download Northwind.Ms.SQL.2005.sql from here
• Import SQL to MsSQL ( I used Aqua Data Studio)

3. Download neo4j-etl

• Download latest here

4. Install Microsoft SQL driver

• Download here
• Add driver .jar file (mssql-jdbc-6.2.2.jre8.jar) to /lib folder

5. Start Neo4j server

Example code

Generate-metadata-mapping

./bin/neo4j-etl generate-metadata-mapping \
 --rdbms:url "jdbc:sqlserver://localhost:1433;databaseName=Northwind" \
 --rdbms:user SA --rdbms:password Password \
 --rdbms:schema Northwind.dbo > ./mapping.json

Import to Neo4j database

Only tested with --using cypher:direct

./bin/neo4j-etl export \
 --rdbms:url "jdbc:sqlserver://localhost:1433;databaseName=Northwind" \
 --rdbms:user SA --rdbms:password Password --rdbms:schema Northwind.dbo \
 --using cypher:direct \
 --neo4j:url bolt://localhost:7687 --neo4j:user <neo4j-user> --neo4j:password <neo4j-pass> \
 --import-tool <path-to-neo4j-home>/bin \
 --csv-directory <path-to-neo4j-home>/import"

[jexp]

Thanks so much @peternorbeck!

@albertodelazzari can you add it to the docs please?

[mariobastande]

Thanks for your example, was really helpful.

Is there a way to run the ETL tool into the Desktop for SQL Server? Tried everything in the documentation but seems made for server only.

I'm running Enterprise version on a server and desktop in local.

Thanks!

[vesko50]

Thanks for great article. I have problem establishing my connection. I am using Windows 10 64-bit, MS SQL Server 2017, and JDK 8. According to Microsoft documentation, SQL Server 2017 is supported by JDBC 6.2 and 6.4 drivers, but this does not work for me. I am getting 'Connection Error'. I this case i am using mssql-jdbc-6.22jre8.jar Any suggestion? Thanks.

[albertodelazzari]

Hi @vesko50, a new ETL version with a better message reporting when facing connection issues should be released soon. In the meantime you can try something like that:

java -cp "<PATH_TO_ETL_TOOL_JAR/neo4j-etl.jar:<PATH_TO_YOUR_JDBC_DRIVER>/mssql-jdbc-6.1.0.jre8.jar" org.neo4j.etl.rdbms.Support "jdbc:sqlserver://localhost:1433;databaseName=<DATABASE_NAME>" <USERNAME> <PASSWORD>

You should be able to see the full stack trace