9 Replies Latest reply on Nov 9, 2017 11:23 AM by Ramesh Reddy

    Does teiid support Google's BigQuery enterprise data warehouse?

    Kulbhushan Chaskar Expert

      Hi,

       

      I am using teiid-9.1.3 admin api and WildFly 10.0.0

       

      As mentioned on Teiid Homepage · Teiid datasource can be of the type Saas and Cloud application.

      I am curios to know if teiid has support for Google's BigQuery enterprise data warehouse?

      If yes, what are the steps user need to follow to integrate a BigQuery with teiid?

       

      Thanks,

      Kulbhushan Chaskar.

        • 1. Re: Does teiid support Google's BigQuery enterprise data warehouse?
          Ramesh Reddy Master

          Currently, there is no translator for it. It has been logged at [1]. Let us know if you want to write and contribute to Teiid.

           

          [1] [TEIID-2355] BigQuery integration - JBoss Issue Tracker

          • 2. Re: Does teiid support Google's BigQuery enterprise data warehouse?
            Kulbhushan Chaskar Expert

            Thanks Ramesh!!

             

            Can I use jdbc-simple or jdbc-ansi translators for Google's BigQuery enterprise data warehouse to perform simple sql operations? like select, insert, update, delete

             

            Thanks,

            Kulbhushan Chaskar.

            • 3. Re: Does teiid support Google's BigQuery enterprise data warehouse?
              Ramesh Reddy Master

              If there is an existing JDBC driver for BigQuery then you can try. The success of this depends upon how much SQL support this JDBC drivers provide. Since I do not know about driver capabilities, I do not know for sure.

              • 4. Re: Does teiid support Google's BigQuery enterprise data warehouse?
                Kulbhushan Chaskar Expert

                Thanks Ramesh,

                 

                I am able to connect to BigQuery via wildfly datasource using Simba driver Simba Drivers for Google BigQuery  |  BigQuery  |  Google Cloud Platform  but when I am trying to deploy VDB by importing the BigQuery data-source it failed, below is the stack-trace.

                I tried scenario with jdbc-simple, and jdbc-ansi translators both are pointing to the same error, My suspect is it's failing due to the translators are incompatible with Simba driver or vice versa, Please confirm.

                 

                Stack-trace:

                13:23:31,758 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-3)  WFLYSRV0027: Starting deployment of "SvcSourceVdb_cloudGCPKul-vdb.xml" (runtime-name: "SvcSourceVdb_cloudGCPKul-vdb.xml")

                13:23:31,811 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-3)  TEIID40118 VDB SvcSourceVdb_cloudGCPKul.1 added to the repository

                13:23:31,812 INFO  [org.teiid.RUNTIME] (MSC service thread 1-3)  TEIID50029 VDB SvcSourceVdb_cloudGCPKul.1 model "cloudGCPKul" metadata is currently being loaded. Start Time: 9/4/17 1:23 PM

                13:23:31,820 INFO  [org.teiid.CONNECTOR] (Worker2_async-teiid-threads2)  SimpleJDBCExecutionFactory Commit=true;DatabaseProductName=Google BigQuery;DatabaseProductVersion=2.0;DriverMajorVersion=1;DriverMajorVersion=1;DriverName=SimbaJDBCDriverforGoogleBigQuery;DriverVersion=01.01.00.1000;IsolationLevel=1

                13:23:31,880 INFO  [org.jboss.as.server] (management task-7)  WFLYSRV0010: Deployed "SvcSourceVdb_cloudGCPKul-vdb.xml" (runtime-name : "SvcSourceVdb_cloudGCPKul-vdb.xml")

                13:23:32,638 WARN  [org.teiid.RUNTIME] (Worker2_async-teiid-threads2)  TEIID50036 VDB SvcSourceVdb_cloudGCPKul.1 model "cloudGCPKul" metadata failed to load. Reason:TEIID11010 java.sql.SQLException: [Simba][JDSI](20560) Unexpected error while sorting metadata.: org.teiid.translator.TranslatorException: TEIID11010 java.sql.SQLException: [Simba][JDSI](20560) Unexpected error while sorting metadata.

                at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:308)

                at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:69)

                at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:96)

                at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62)

                at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)

                at org.teiid.jboss.VDBService$6.run(VDBService.java:360)

                at org.teiid.jboss.VDBService$7.run(VDBService.java:411)

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

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

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

                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: [Simba][JDSI](20560) Unexpected error while sorting metadata.

                at com.simba.dsi.dataengine.impl.DSIMetadataResultSet.<init>(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIDataEngine.getMetadataResultImplementation(Unknown Source)

                at com.simba.googlebigquery.dataengine.BQDataEngine.getMetadataResultImplementation(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIDataEngine.makeNewMetadataResult(Unknown Source)

                at com.simba.jdbc.jdbc41.S41DatabaseMetaData.createMetaDataResult(Unknown Source)

                at com.simba.jdbc.common.SDatabaseMetaData.getTables(Unknown Source)

                at org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:312)

                at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:166)

                at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:306)

                at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:69)

                at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:96)

                at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62)

                at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)

                at org.teiid.jboss.VDBService$6.run(VDBService.java:360)

                at org.teiid.jboss.VDBService$7.run(VDBService.java:411)

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

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

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

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

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

                Caused by: com.simba.support.exceptions.GeneralException: [Simba][JDSI](20560) Unexpected error while sorting metadata.

                ... 20 more

                Caused by: java.lang.NullPointerException

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

                at com.simba.googlebigquery.dataengine.metadata.BQMetadataSource.moveToNextCatalog(Unknown Source)

                at com.simba.googlebigquery.dataengine.metadata.BQMetadataSource.fetchNextSetOfSchemas(Unknown Source)

                at com.simba.googlebigquery.dataengine.metadata.BQMetadataSource.moveToNextSchema(Unknown Source)

                at com.simba.googlebigquery.dataengine.metadata.BQMetadataSource.fetchNextSetOfTables(Unknown Source)

                at com.simba.googlebigquery.dataengine.metadata.BQMetadataSource.moveToNextTable(Unknown Source)

                at com.simba.googlebigquery.dataengine.metadata.BQTablesMetadataSource.moveToNextRow(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIMetadataResultSet.moveToNextUnsortedRow(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIMetadataResultSet.initializeRows(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIMetadataResultSet.sortData(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIMetadataResultSet.<init>(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIDataEngine.getMetadataResultImplementation(Unknown Source)

                at com.simba.googlebigquery.dataengine.BQDataEngine.getMetadataResultImplementation(Unknown Source)

                at com.simba.dsi.dataengine.impl.DSIDataEngine.makeNewMetadataResult(Unknown Source)

                at com.simba.jdbc.jdbc41.S41DatabaseMetaData.createMetaDataResult(Unknown Source)

                at com.simba.jdbc.common.SDatabaseMetaData.getTables(Unknown Source)

                at org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:312)

                at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:166)

                at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:306)

                at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:69)

                at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:96)

                at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62)

                at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)

                at org.teiid.jboss.VDBService$6.run(VDBService.java:360)

                at org.teiid.jboss.VDBService$7.run(VDBService.java:411)

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

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

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

                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)

                • 5. Re: Does teiid support Google's BigQuery enterprise data warehouse?
                  Ramesh Reddy Master

                  Kulbhushan,

                   

                  Simba folks may have provided the JDBC driver to Google Big Query, but it may not be full compatible as a relational database. i.e. JDBC access != RDBMS

                   

                  "jdbc-ansi" and "jdbc-simple" translators in Teiid use JDBC's DatabaseMetadata calls to read metadata from the source in a Dynamic VDB scenario. I see that process is failing in Simba side of the code. Without knowing lot more about Big Query and Simba driver functionality I can not fully comment what needs to be done. So, here are your options.

                   

                  1) You can work with Simba to solve the above NulltPointerException to see if that resolves the problem

                  2) Write your own translator where you can control the metadata loading

                  3) Define the metadata of Big Query's model in the -vdb.xml in the form of DDL. Of course, this DDL needs to match up to table structure in Big Query and Simba can execute it. There are many examples of this in the Teiid documentation.

                   

                  (3) may be simplest and easy to try out without any coding.

                   

                  Ramesh..

                  • 6. Re: Does teiid support Google's BigQuery enterprise data warehouse?
                    Kulbhushan Chaskar Expert

                    Thanks Ramesh!!

                     

                    I tried option 3 and I am able to fire SELECT and INSERT queries on BigQuery.

                    Can we add metadata in VDB problematically using Admin API? e.g. I have added importProps_tbl.put("importer.tableTypes", "TABLE") to fetch only tables also some translators I added dynamically while creating VDB. following the same way Can I add Metadata dynamically?

                     

                    Thanks,

                    Kulbhushan Chaskar.

                    • 8. Re: Does teiid support Google's BigQuery enterprise data warehouse?
                      Kulbhushan Chaskar Expert

                      I went with your suggestion and it helped. Thanks a lot!

                       

                      I am trying to write insert query in teiid for array of string and record data type (eventually translated to big query syntax).We are using jdbc-ansi translator for big query. Below is the syntax of big query for both the data types:

                       

                      • record: insert into TM.EMP2 (name, age, vendor) values (('rob','van'),44,'m'); //name is record type in big query which has two fields.
                      • repeatable: insert into TM.EMP2 (id,name,department) values('1','john',['HR','QA']); //department is repeatable type.

                       

                      Along with insert query syntax could you help me in knowing the DDL representation for the same in vdb.

                      • 9. Re: Does teiid support Google's BigQuery enterprise data warehouse?
                        Ramesh Reddy Master

                        I am not familiar with Google Query terms, so, unfortunately, I can't be much help there.