Teiid and Google BigQuery

[mdahlman]

It seems safe to say that there is currently no Teiid connector for Google BigQuery. I get no hits on any of my searches, but I'm looking for advice on building such a connector.

• Has anyone looked into creating such a connector?
• Does anyone have advice about what level of effort would be required?
• It has no JDBC driver, but it uses SQL (more or less). It authenticates using OAth2. Does this impose strange restrictions that will make it hard to create a Teiid connector? Or does this sound like a "standard custom connector" to the extent that phrase makes any sense?
• Would it make sense to refer to Extending The JDBC Translator, despite the fact that this is not a JDBC data source, because it uses relatively standard SQL?

[markaddleman]

Hi -

I'm not part of the Teiid team but I have written plenty of custom translators.  I'm curious to hear what the 'official' answer is but mine is that you'd be better off extending from the base ExecutionFactory rather than extending the JDBC translator.  The JDBC translator relies on a lot of the JDBC standard, most particularly, the metadata aspects of JDBC.  My guess is that you're better off with a custom translator extending directly from ExecutionFactory.

The execution factories will receive an abstract syntax tree of SQL from the Teiid query planner.  The execution and execution factory (together, these are referred to as the translator) work in concert to convert that SQL into something that the underlying translator understands, execute the query and return the result in the form that Teiid understands.  To the degree that BigQuery speaks SQL, 1/3 of that job is easy.  I can attest that manipulating the SQL AST using Teiid's visitor library is pretty straightforward once you get the hang of it.  I found that the biggest learning curve was understanding the peculiarities of SQL vocabulary.  I would suggest becoming familiar with the BNF grammar in the Teiid Reference Guide as well as writing plenty of test cases that you can iterate on quickly.

[rareddy]

Matthew,

Currently there is nothing planned on BigQuery translator in the Teiid, however we are  interested this integration. Currently our big data focus has been integration with Infinispan, Cassendra, Mongo DB, Big Table etc. It goes with out saying that the core team does not expertise in all the technologies and looks for community to step in provide such hooks.  So, we will help you anyway in development, if you can contribute back to the community -:)

• Looks like BigQuery is based on the Rest API based the link you provided, so extending the "web-service" connector may be appropriate here
• As per the OAuth2, I believe picketlink already provides the JAAS extension on it, that can be used without much hassle with configuration in the JBoss AS7/Teiid 8.x.
• As per the effort, it may be couple days to couple weeks or more depending upon source and its queryability and complexity. Can you post some sample queries and expected output format to show us a sample?
• Take a look at some of the simple translators like File or Loopback to get a hang of the API.

+1 on Mark's comments.

Thank you.

Ramesh..

[starschema]

Matthew,

I've got good news:

As a spinoff from one of our projects we've released an opensource JDBC driver for Google BigQuery:

http://code.google.com/p/starschema-bigquery-jdbc/

Check it out and let me know if you have any questions.

Have a great day,

Endre

[dsteigne]

Endre,

I have a customer who is trying to use the JDBC driver for BigQuery: http://code.google.com/p/starschema-bigquery-jdbc/

The issue is in the JBoss datasource password field, it doesn't like the path to the key file it's expecting the actual password for the datasource how did you work around this?  Or have you not used this driver in JBoss Application Server?

Thank you

Debbie

[masilamani]

Hello,

I am trying to access BigQuery using Teiid with starsschema-bigquery-jdbc driver bqjdbc-1.4.jar but it's not working in teiid. Interesting is that it works fine when i try to access using SQuirrel SQL client with both below authentication / authorization method.

- Using service account in Google

- Using Client ID for native application

Just wondering whether anyone from teiid community tried already to connect and query successfully using starsschema-bigquery-jdbc driver ?  Note that in teiid,  i tried with various options but still it doesn't work.

- Native Query

- Using Translator jdbc-simple or jdbc-ansi

- Translator override to set false to "Use Bind Variables"

- Also Teiid Designer importes metadata with Name In Source (e.g. \"gplus_post_comment\") incorrectly for bigquery instead of [schema name.gplus_post_comment] so manually updated Name In Source 

Thanks, Mani

[shawkins]

It sounds like you got import working, was this through the Teiid Connection Importer or through a generic JDBC approach in Teiid Designer?

What specifically isn't working on the server?  - In valid source queries? Errors connecting? etc.

[masilamani]

Hi Steven,

The Import in Designer and Connecting to BigQuery in Teiid Server works fine but running any query fails with below error message.   I was able to contact BigQuery JDBC Support team and shared the bqjdbc.log file and confirmed from them that Teiid query runs fine and the reason it's failing in Teiid is due to they haven't implemented ClearWarnings and that's why it throws an exception.   So they asked me to see whether in Teiid whether we can configure to skip these warning checks to make it to work.

14-08-22 13:08:42,314 BQJDBC [Worker0_QueryProcessorQueue15] DEBUG net.starschema.clouddb.jdbc.BQSQLException  - Not implemented.clearWarnings()net.starschema.clouddb.jdbc.BQSQLException: Not implemented.clearWarnings()

  at net.starschema.clouddb.jdbc.BQStatementRoot.clearWarnings(BQStatementRoot.java:139)

  at org.jboss.resource.adapter.jdbc.WrappedStatement.clearWarnings(WrappedStatement.java:208)

  at org.teiid.translator.jdbc.JDBCBaseExecution.addStatementWarnings(JDBCBaseExecution.java:212)

  at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:88)

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276)

  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

  at java.util.concurrent.FutureTask.run(FutureTask.java:138)

  at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:121)

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:245)

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)

  at java.lang.Thread.run(Thread.java:662)

Thanks, Mani

[masilamani]

Hi Steven,

If i use SQuirrel SQL client to connect to BigQuery using BigQuery JDBC Driver,  i am able to run the query successfully so looks like generic JDBC driver in SQuirrel SQL client ignores these type of warnings.

[shawkins]

We can, but it will require a code change.  Can you log an issue for this?

[kulbhushanc]

I am newbie to the google cloud and BigQuery area.

I am able to connect to BigQuery using jdbc with the help of simba driver Simba Drivers for Google BigQuery  |  BigQuery  |  Google Cloud Platform

Now I am willing to connect it through WildFly-Teiid, for that first of all I need to create a data-source which will connect to BigQuery using OAuth2.0.

So my question is:

How to create/configure data-source on WildFly to connect BigQuery?

Thanks,

Kulbhushan Chaskar.

[rareddy]

That should be similar to connecting any database. For ex: DataSource configuration - WildFly 8 - Project Documentation Editor

[kulbhushanc]

I think I tried the same way you suggested, but getting error

Please have a look @ datasoruces I created:

these are the code snippet I tried, but it failed to

DS 1:

               <datasource jta="true" jndi-name="java:/DSCloud" pool-name="DSCloud" enabled="true" use-ccm="true">

                    <connection-url>jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=Project-Id;;OAuthType=1</connection-url>

                    <driver>simba</driver>

                </datasource>

here it says:

20:51:23,246 WARN  [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (management task-7)  IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: IJ031084: Unable to create connection

at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:343)

at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:350)

at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:285)

at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1319)

at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:496)

at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:1061)

at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:93)

at org.jboss.as.connector.subsystems.common.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:234)

at org.jboss.as.connector.subsystems.common.pool.PoolOperations$1.execute(PoolOperations.java:90)

at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:890)

at org.jboss.as.controller.AbstractOperationContext.processStages(AbstractOperationContext.java:659)

at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:370)

at org.jboss.as.controller.OperationContextImpl.executeOperation(OperationContextImpl.java:1344)

at org.jboss.as.controller.ModelControllerImpl.internalExecute(ModelControllerImpl.java:392)

at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:217)

at org.jboss.as.domain.http.server.DomainApiHandler.handleRequest(DomainApiHandler.java:212)

at io.undertow.server.handlers.encoding.EncodingHandler.handleRequest(EncodingHandler.java:72)

at org.jboss.as.domain.http.server.security.SubjectDoAsHandler$1.run(SubjectDoAsHandler.java:72)

at org.jboss.as.domain.http.server.security.SubjectDoAsHandler$1.run(SubjectDoAsHandler.java:68)

at java.security.AccessController.doPrivileged(Native Method)

at javax.security.auth.Subject.doAs(Subject.java:422)

at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:92)

at org.jboss.as.domain.http.server.security.SubjectDoAsHandler.handleRequest(SubjectDoAsHandler.java:68)

at org.jboss.as.domain.http.server.security.SubjectDoAsHandler.handleRequest(SubjectDoAsHandler.java:63)

at io.undertow.server.handlers.BlockingHandler.handleRequest(BlockingHandler.java:56)

at org.jboss.as.domain.http.server.DomainApiCheckHandler.handleRequest(DomainApiCheckHandler.java:95)

at io.undertow.security.handlers.AuthenticationCallHandler.handleRequest(AuthenticationCallHandler.java:52)

at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202)

at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:793)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.sql.SQLException: [JDBC Driver]null

at java.awt.GraphicsEnvironment.checkHeadless(GraphicsEnvironment.java:207)

at java.awt.Window.<init>(Window.java:536)

at java.awt.Frame.<init>(Frame.java:420)

at javax.swing.JFrame.<init>(JFrame.java:233)

at com.simba.googlebigquery.client.BQClient.userAccountOAuth(Unknown Source)

at com.simba.googlebigquery.core.BQConnection.connect(Unknown Source)

at com.simba.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)

at com.simba.jdbc.common.AbstractDriver.connect(Unknown Source)

at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:319)

... 31 more

Caused by: java.awt.HeadlessException

... 40 more

20:51:23,248 ERROR [org.jboss.as.controller.management-operation] (management task-7)  WFLYCTL0013: Operation ("test-connection-in-pool") failed - address: ([

    ("subsystem" => "datasources"),

    ("data-source" => "DSCloud")

]) - failure description: "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid"

DS 2:

                <datasource jta="true" jndi-name="java:jboss/root/jdbc/GoogleBigQuery" pool-name="GCloud" enabled="false" use-ccm="true">

                    <connection-url>jdbc:googlebigquery:DataSetId=MyDataSetId;ProjectId=Project-Id;InitiateOAuth=GETANDREFRESH</connection-url>

                    <driver-class>com.simba.googlebigquery.jdbc41.Driver</driver-class>

                    <driver>simba</driver>

                    <security>

                         <user-name>me@email_domain.com</user-name>

                        <password>mypassword</password>

                    </security>

                </datasource>

here it says :

20:50:10,919 ERROR [org.jboss.as.controller.management-operation] (management task-3)  WFLYCTL0013: Operation ("test-connection-in-pool") failed - address: ([

    ("subsystem" => "datasources"),

    ("data-source" => "GCloud")

]) - failure description: "WFLYJCA0040: failed to invoke operation: WFLYJCA0042: failed to match pool. Check JndiName: java:jboss/root/jdbc/GoogleBigQuery"

here I am not sure about the field InitiateOAuth=GETANDREFRESH

Thanks,

Kulbhushan Chaskar.

[van.halbert]

Since the JNDI names are using the java: context, add the attribute:  use-java-context="true"

Also, on the DS 2, it shows enabled="false", that should be "true"

Hope that helps.

Van

[rareddy]

You are using wrong OAuth type. For general instructions see http://cdn.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/using/connectionurl.htm

You need to use a service account for your OAuth. See http://cdn.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/authenticating/serviceaccount.htm  Using the user based authentication requires one to present with UI where you can enter your user id and password, that is not possible with Teiid as Teiid has NO UI for runtime.

So, create a service account in google big query, get their key files and follow above to create correct URL then it will work.

Ramesh..