-
1. Re: Teiid and Google BigQuery
markaddleman Aug 13, 2012 8:05 PM (in response to mdahlman)1 of 1 people found this helpfulHi -
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.
-
2. Re: Teiid and Google BigQuery
rareddy Aug 14, 2012 10:54 AM (in response to mdahlman)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..
-
3. Re: Teiid and Google BigQuery
starschema Aug 31, 2012 6:38 AM (in response to mdahlman)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
-
4. Re: Teiid and Google BigQuery
dsteigne Aug 19, 2014 8:46 AM (in response to starschema)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
-
5. Re: Teiid and Google BigQuery
masilamani Aug 21, 2014 3:47 PM (in response to dsteigne)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
-
6. Re: Teiid and Google BigQuery
shawkins Aug 25, 2014 2:26 PM (in response to masilamani)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.
-
7. Re: Teiid and Google BigQuery
masilamani Aug 27, 2014 1:00 PM (in response to shawkins)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
-
8. Re: Teiid and Google BigQuery
masilamani Aug 27, 2014 2:44 PM (in response to 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.
-
9. Re: Teiid and Google BigQuery
shawkins Sep 3, 2014 7:44 AM (in response to masilamani)We can, but it will require a code change. Can you log an issue for this?
-
10. Re: Teiid and Google BigQuery
kulbhushanc Sep 1, 2017 5:24 AM (in response to rareddy)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.
-
11. Re: Teiid and Google BigQuery
rareddy Sep 1, 2017 8:44 AM (in response to kulbhushanc)That should be similar to connecting any database. For ex: DataSource configuration - WildFly 8 - Project Documentation Editor
-
12. Re: Teiid and Google BigQuery
kulbhushanc Sep 1, 2017 11:20 AM (in response to rareddy)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.
-
13. Re: Teiid and Google BigQuery
van.halbert Sep 1, 2017 11:53 AM (in response to kulbhushanc)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
-
14. Re: Teiid and Google BigQuery
rareddy Sep 1, 2017 12:29 PM (in response to kulbhushanc)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..