OHDSI Home | Forums | Wiki | Github

Broadsea installation using BigQuery

This is independent of WebAPI running. The Postgres database via the “atlasdb” container should be up and running, is it?

If it is running, next you’d need to verify that the connection details you’ve provided to the webapi container are valid. If running on your localhost, try initiating a JDBC connection to it. With R, you can use this:

connectionDetails <- DatabaseConnector::createConnectionDetails(server = "127.0.0.1/postgres",
                                                                    dbms = "postgresql",
                                                                    user = "<your user name>",
                                                                    password = "<your password>",
                                                                    pathToDriver = "<path to your postgres jdbc driver")
connection <- DatabaseConnector::connect(connectionDetails = connectionDetails)

Next, see that your webapi schema is available.

The atlasdb container is up and running. However, I cannot from localhost using the default username and password. For Broadsea 3.0 these should match the WEBAPI_DATASOURCE_USERNAME and WEBAPI_DATASOURCE_PASSWORD in the .env file right? In R I get the following error:

Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect",  : 
  org.postgresql.util.PSQLException: Connection to 127.0.0.1:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Also when I check the WebAPI logs, I now notice the following error:

ERROR main org.springframework.boot.SpringApplication - [] - Application startup failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'oidcConfCreator': Injection of autowired dependencies failed; nested exception is java.lang.IllegalArgumentException: Circular placeholder reference 'security.oid.customParams' in property definitions

Edit: I’m able to connect to the database directly from within the atlasdb container, using: psql --host localhost --username postgres --password

It should be the same credentials, yes. So when you say within the container, you mean that using docker exec or using the terminal provided by the Docker GUI for that container, right?

From outside of the container, from your actual local machine, you cannot get the psql command to run? Do you have any firewall settings enabled that prevent port 5432?

That circular reference is worrying, we just added a PR that handles custom params, so I’m hoping we didn’t introduce an issue with that.

@Ajit_Londhe Yes, I mean using docker exec. You were also right about the firewall, which still prevented local access. Now I’ve updated that, I am able to establish a postgres connection locally via R. However, the WebAPI logs still give the same authentication failed error. Could it have something to do with the postgres driver, since it also says Unable to obtain Jdbc connection from DataSource?

To be clear, the above errors are for Broadsea 2.0. Atm I’m trying lots of different stuff, so the circular reference error I mentioned is for Broadsea 3.0.

@Chris_Knoll - can you clarify the ‘circular reference’ you mentioned?

The error is discussed in this stack overflow thread.

However, we use the same key-token pairs in many different places in the app, and it does not cause an issue, we define things like this in pom.xml and reference it in application.properties.

So, while people report they can’t be the same, we make them the same all the time. So I think it is something different.

This was just recently added (2 weeks ago) so I’m not sure why Broadsea 2.0 would even have a reference to this).

So, I’m sorry, I don’t have an explanation for this, but that error is definitely a problem that will cause the process startup to fail. @Ajit_Londhe could you confirm that Broadsea 2.0 launches in your env, I do not have a docker environment to test with.

The approach for WebAPI isn’t actually different between Broadsea 2 and 3, the flyway parameters are the same.

@timeeftink – can we reset here, now that you have the firewall resolved for the postgres container? Can you please share your .env file for Broadsea 3, redacted?

Ok, just to be clear, this isn’t a flyway param thing, this is just the application settings…in this case it had to do with the security.oid.customParams property that was introduced in this commit.

This was only made 2 weeks ago, so it’s odd that this is being reported in Broadsea 2.0. I wonder if it’s possible that the issue is that the code (the most recent commit in master) is looking for a property, but the config from Broadsea doesn’t have it specified. The reason why I doubt this is the case is that the pom.xml file from that commit does provide a default so if it’s running with new code looking for the security.oid.customparams value (which it seems to be) then it would also have been built with the latest pom.xml that defines that field value. So, it is a bit of a mystery, but it would be helpful if a few people from the community could try to launch broadsea 2.0 or 3.0 and see if they are running into the issue.I don’t think I’m seeing this on my local env.

Hi!

As Chris suspects this error indeed shows up when the property is not defined, however it is defined in the latest pom.xml… so mysterious indeed. What I’m guessing is that Docker is using a cached layer when copying the pom.xml, this should not happen but it does sometimes (speaking from personal experience…) perhaps try building with the --no-cache option and/or add a RUN echo “whatever” somewhere in the top of the Dockerfile to force the rebuild. Either way check the output when running the build.

@Chris_Knoll I’m sorry for the confusion, the security.oid.customParams issue only appears in Broadsea 3.0. But since I didn’t get that to work, I also made an attept with Broadsea 2.0, which gave me the Postgres authorization issue I discussed with Ajit.

However, I think I figured what was wrong. It seems my pom.xml file was out of sync with the latest changes on the WebAPI, because I had to make some changes to get the BigQuery drivers to work.

@timeeftink - where do you see this in Broadsea 3? I don’t see it in our env template nor the docker yaml file for webapi.

@Ajit_Londhe Do you mean the security.oid.customParams value? It is in the pom.xml file in the WebAPI: https://github.com/OHDSI/WebAPI/blob/master/pom.xml

So it’s not being set by Broadsea currently or ever (it’s just taking the default value from the pom as we’re not passing along any env variables). Do you need it to be something you can set (e.g. override the pom)?

I don’t think so, the point of putting a value in pom.xml is that it will be used by default without additional profile settings.

Right, so I’m wondering what is needed here. Broadsea 2.0 nor 3.0 does anything to alter this value. We’re not overriding it or asserting it in any way, so it’s just using what’s in the pom.

timeeftink I’ve been using this thread to get Achilles working with a Bigquery based Atlas/WebApi installation. Thank you for documenting your steps.

I’m currently stuck with a Bigquery error when running Achilles: “Transaction control statements are supported only in scripts or sessions”. This is because Achilles using the Simba driver includes a ‘begin transation’ statement.

Were you able to work around this?
Or perhaps I did not understand the workarounds you eventually needed to get Achilles tables setup in WebApi?

Thanks, Dennis

t