Certificate issue while retrieving open data using apoc.load.json

The following open data URL in any browser will retrieve a sample of air pollution in JSON format, and display it in your browser:


I would like to include this URL in a Neo4j query to retrieve the data directly in Neo4j. So I turned to APOC. Below is a query that calls apoc.load.json, which you can paste into your Neo4j Desktop query window for testing:

WITH "https://open.stavanger.kommune.no/api/3/action/package_search?q=luftmaling-stavanger&rows=1" AS url
CALL apoc.load.json(url) YIELD value
RETURN value

It fails for me. Does it fail for you? It seems to be related to the JVM used by Neo4j and the website's self assigned certification. Here is the error message:

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure apoc.load.json: Caused by: java.lang.RuntimeException: Can't read url or key https://open.stavanger.kommune.no/api/3/action/package_search?q=luftmaling-stavanger&rows=1 as json: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Is there an easy work-around?

Don't remember 100% but I think `apoc.load.json´ relies on the JVM for certifcate checking. What JVM in which version are you using?

I remember some glitches on non-existent root certificates in some JVM versions.

https://stackoverflow.com/a/25953317/158701 should point you how to use JVM's keytool to add a specific certificate.

checking on my command line...
$ java -version
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

But I don't think that's the version used by Neo4j Desktop, which I am using here. Neo4j Desktop installs Neo4j Enterprise, and Neo4j Enterprise supplies its own JDK, which I read somewhere is Zulu OpenJDK. (I can't remember the source.)

I tried finding that jdk but didn't manage to find it.
Someone else apparently tried too: Confused on version and installing Java JDK for Mac to support Enterprise for OnLine learning class

An answer to the question in that post would help answer this one. If I can find how to swap out Neo4j Enterprise's JDK I would experiment with different JDKs and the website certificate issue of this post.

You're right, Neo4j Desktop is bundled with Zulu JVM. On my Linux box it's located in $HOME/.config/Neo4j Desktop/Application/distributions/java/zulu8.21.0.1-jdk8.0.131. Inside that folder there's a bin/keytool - I guess this is the one to be used for adding certificates.

If you download Neo4 server (both enterprise and community) from https://neo4j.com/download-center/ it will use the system's JVM.

1 Like

I found a work-around for using Neo4j to directly pull the data from the Open Data site, by using an alternative url. The original url I used when stating the question invokes an API provided by the organization CKAN. And that requires a certificate. But near the api link there is also a download button that fetches it in .csv format. That link does not require Neo4j to negotiate with the above API. So here is the working cypher query with the download url.

CALL apoc.load.csv('https://opencom.no/dataset/d473bc01-8d6e-4771-a946-e19d3fff3691/resource/97c4b945-6bb9-477e-a394-fae534dff195/download/nilu.csv',{sep:","}) YIELD map

I use apoc.load.csv instead of apoc.load.json. To see it in JSON format I just 'YIELD map'.

Okay, so it is a work-around; it does not solve the API certification problem as I defined it. Stephan's guidance is surely on the correct path for that. Thanks Stephan.