Using the BI Connector

In this tutorial we use the Neo4j Connector for BI to read graph data from an Aura instance using some common SQL clients and BI tools.

This tutorial includes instructions on the usage of third-party software, which may be subject to changes beyond our control. In case of doubt, please refer to the third-party software documentation.

Downloading the connector

Download the connector from the Download Center. Depending on the SQL client or BI tool it will be used with, you will need either the JDBC or the ODBC connector; see the usage examples for further details.

Preparing example data

Before trying the connector with any of the listed tools, some data needs to be loaded on Aura. This can be achieved by running the following Cypher query in the Neo4j Browser:

CREATE
  (john:Person {name: "John", surname: "Doe", age: 42}),
  (jane:Person {name: "Jane", surname: "Doe", age: 40}),
  (john)-[:KNOWS]->(jane)

Using command-line SQL clients

In order to run SQL queries, we need a SQL client that can use a custom driver. Common JDBC-based command-line SQL clients include sqlline and jdbcsql.

The correct protocol to use for connection via a JDBC driver is neo4j (not neo4j+s). Make sure to add the SSL=true parameter to the URL.

sqlline

sqlline is a command-line tool for issuing SQL queries to relational databases via JDBC. To clone and build it, run the following:

$ git clone https://github.com/julianhyde/sqlline
$ cd sqlline
$ ./mvnw package

We now need to make the BI connector driver available to sqllite. This can be done by extracting the Neo4jJDBC42.jar file from the downloaded JDBC BI connector into the sqlline/target folder.

The sqlline client can now be run as follows:

$ ./bin/sqlline -d com.simba.neo4j.neo4j.jdbc42.Driver

From the client prompt, it is possible to connect to the Aura instance by supplying the username and password when prompted to do so:

sqlline> !connect jdbc:neo4j://xxxxxxxx.databases.neo4j.io?SSL=true

When the connection is established, a list of tables can be obtained with the !tables command:

jdbc:neo4j://xxxxxxxx.databases.neo4j.io> !tables
+-----------+--------------+---------------------+------------+---------+----------+------------+-----------+--------+
| TABLE_CAT | TABLE_SCHEM  |     TABLE_NAME      | TABLE_TYPE | REMARKS | TYPE_CAT | TYPE_SCHEM | TYPE_NAME | SELF_R |
+-----------+--------------+---------------------+------------+---------+----------+------------+-----------+--------+
| neo4j     | Node         | Person              | TABLE      |         |          |            |           |        |
| neo4j     | Relationship | Person_KNOWS_Person | TABLE      |         |          |            |           |        |
+-----------+--------------+---------------------+------------+---------+----------+------------+-----------+--------+

It is also possible to run SQL queries:

jdbc:neo4j://xxxxxxxx.databases.neo4j.io> SELECT * FROM Person;
+----------+-----+------+---------+
| _NodeId_ | age | name | surname |
+----------+-----+------+---------+
| 0        | 42  | John | Doe     |
| 1        | 40  | Jane | Doe     |
+----------+-----+------+---------+

jdbcsql

jdbcsql is a command-line tool that can be used to connect to a DBMS via a JDBC driver.

After downloading the jdbcsql-1.0.zip file from SourceForge, extract it into the jdbcsql folder; then, copy the Neo4jJDBC42.jar file from the downloaded JDBC BI Connector into jdbcsql and make the following changes:

  1. Add the following lines to JDBCConfig.properties

    # neo4j settings
    neo4j_driver = com.simba.neo4j.neo4j.jdbc42.Driver
    neo4j_url = jdbc:neo4j://host?SSL=true
  2. Add Neo4jJDBC42.jar to Rsrc-Class-Path line in META-INF/MANIFEST.MF

Now run the following command (replacing xxxxxxxx.databases.neo4j.io with the Aura connection URI, and yyyyyyyy with the actual password):

$ java org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader -m neo4j -h xxxxxxxx.databases.neo4j.io -d neo4j -U neo4j -P yyyyyyyy 'SELECT * FROM Person'

The result of the query is:

"_NodeId_"	age	name	surname
0	42	John	Doe
1	40	Jane	Doe

Using BI tools

Commonly used BI tools include Tableau (which uses the JDBC driver) and Power BI (which uses the ODBC driver).

The correct protocol to use for connection via a JDBC driver is neo4j (not neo4j+s). Make sure to add the SSL=true parameter to the URL.

Tableau

This example requires Tableau Desktop.

After downloading the JDBC driver:

  • Close any running instances of Tableau Desktop.

  • Copy the Neo4j driver to the appropriate Tableau drivers folder (e.g. C:\Program Files\Tableau\Drivers on Windows, or ~/Library/Tableau/Drivers on macOS).

  • Start Tableau and search for the Other Databases (JDBC) option.

  • Insert the Aura URL as jdbc:neo4j://xxxxxxxx.databases.neo4j.io?SSL=true, leave the SQL dialect as SQL92, and complete the relevant credentials.

After the connection is established, it is possible to select the neo4j database and the Node schema to find the Person table. The table can then be explored to find the example data.

For more information on how to add a JDBC database to Tableau, please refer to the Tableau documentation.

Power BI

This example requires Microsoft Windows and Power BI Desktop.

After downloading and installing the ODBC driver:

  • Open Power BI Desktop.

  • Search for ODBC in the Get data from another source panel.

  • Select Simba Neo4j in the DSN dropdown menu.

  • Insert the connection string Host=xxxxxxxx.databases.neo4j.io;SSL=1 in the Advanced options section.

Once connected, open sequentially ODBCneo4jNodePerson in the Navigator window to see a preview of the table.

For more information on how to add an ODBC database to Power BI, check the Power BI documentation.