Described solution was done on Jasperserver 6.4.2 community edition using Simba JDBC Bigquery driver Jdbc 4.2. According to FAQ on drivers’ page current versions do not require license files.

Steps:

  • download Bigquery JDBC 4.2 driver and unzip it into some directory
  • Copy all jar files into
    • jasperreports-server-cp-6.4.2/apache-tomcat/lib/
    • or jasperreports-server-cp-6.4.2/apache-tomcat/webapps/jasperserver/WEB-INF/lib/
  • Bigquery datasource must be defined as JNDI datasource therefore add:
    • into “jasperreports-server-cp-6.4.2/apache-tomcat/webapps/jasperserver/WEB-INF/web.xml”
          <description>bigquery jdbc 4.2</description>
              <res-ref-name>jdbc/bigquery42</res-ref-name>
              <res-type>javax.sql.ConnectionPoolDataSource</res-type>
              <res-auth>Container</res-auth>
          </resource-ref>
      
    • into “jasperreports-server-cp-6.4.2/apache-tomcat/webapps/jasperserver/META-INF/context.xml”
        <Resource name="jdbc/bigquery42" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" driverClassName="com.simba.googlebigquery.jdbc42.Driver" accessToUnderlyingConnectionAllowed="true" defaultAutoCommit ="true" defaultReadOnly ="true" validationQuery="SELECT 1" testOnBorrow="true" url="jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=yourprojectname;OAuthType=........" factory="com.jaspersoft.jasperserver.tomcat.jndi.JSCommonsBasicDataSourceFactory" />
      

      Please check Simba docs about user account authentication or service account authentication to complete URL with proper OAuthType and necessary parameters for it.

  • restart Jasperserver and create new JNDI datasource using name “jdbc/bigquery42”
  • enjoy it… 🙂
  • If you will see problems with BQ job timeouts you must add it into “context.xml” file into “url” as additional parameter at the end like this “;Timeout=300” – timeout is in seconds