13 Replies Latest reply on Mar 25, 2014 9:08 AM by Ramesh Reddy

    Teiid Connector Example

    Mubasshar Ahmad Newbie

      Hi Friends,

      I have to write a connector using teiid api for my client. He needs a custom connector for connecting oracle database with his JavaEE application hosted in JBoss AS. I have studied the guides but totally confused how to code the connector. For this purpose, I think, if there is some small scaled example showing all the steps involved in this process, it may help a lot. Or you have any step by step tutorial that is also fine.

       

      So please help me to take this initiative.

       

      Thanks & Regards,

       

      Mubashar Ahmad

        • 1. Re: Teiid Connector Example
          Ramesh Reddy Master

          Mubasshar,

           

          Why do you need to write Oracle translator? The built-in Oracle translator not suffice your needs? If not what is the feature it is lacking?

           

          Now for how to write a connector

          * Using Teiid's Translator and Connector Archetypes  See this to create a template projects for connectors and translators

          * Developer's Guide - See this document as to what each interface/classes mean and explain the details

          * You can look at source code for all the connector/translator that Teiid provides for your reference. Especially I recommend the loopback translator teiid/connectors/translator-loopback at master · teiid/teiid · GitHub as this is one of the simplest or teiid/connectors/sandbox/translator-yahoo at master · teiid/teiid · GitHub which is translator to Yahoo search engine.

           

          You can also ask specific questions if you do not understand something. If you think these can be improved, then please give suggestions or help us creating a simple "how to create a translator" document.

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: Teiid Connector Example
            Ashish N Newbie

            Ramesh,

             

            I am very new to teiid and yes i have seen built-in translator available for Oracle, db2, cassandara etc. but is there any built-in for kairosdb?

            I have seen on net theres built-in for even mongodb from teiid 8.4 onwards.

             

            Basically, i have to create adaptor which could connect to oracle db and kairos db and run queries on them.

            Any help is completely appreciated.

             

            -Ashish

            • 3. Re: Teiid Connector Example
              Ramesh Reddy Master

              Ashish,

               

              Teiid does *not* currently have a translator or connector for "kairosdb". It is not on our roadmap either. You log an feature request in Teiid JIRA, but I can not promise when it will be available. If there is lot of demand for it we will look at it sooner.

               

              Looks like it is similar to Cassendra which Teiid does have translator for. So, it feasible to develop one for "KairosDB". If you want to write one and contribute back to community I can help you guiding the development of it. There are various threads on developing a new translator and lot of sample code. I suggest you take a look at them and understand how they work and let us how if you have questions.

               

              Ramesh..

              • 4. Re: Teiid Connector Example
                Laxmi Rathore Newbie

                Hello Ramesh,

                I am also new to teiid. I also searching for translator of kairos db.Looks like it is similar to Cassendra which Teiid does have translator for.So can you guide me how to develope translator of kairos db.

                 

                 

                Thanks & Regards,

                Laxmi Rathore

                • 5. Re: Teiid Connector Example
                  Ramesh Reddy Master

                  Laxmi, Anantha & Asish,

                   

                  Please see links provided in this thread to start writing a translator/connector. What would be really good is we all can collaborate in development, that way I can accept as contribution from you at the end of the development and make sure it works with future versions of the Teiid.  I can set up the basic project in my git repo, then you can fork and provide me with implementation updates I will review as I pull the changes. Let me now if you guys up for task.

                   

                  Ramesh..

                  • 6. Re: Teiid Connector Example
                    Laxmi Rathore Newbie

                    Hello Ramesh,

                    Please set up the basic project in git. I will like to work upon this and will try to implement kairos db translator.

                     

                    Laxmi....

                    • 7. Re: Teiid Connector Example
                      anantha gottipati Newbie

                      Hello Ramesh,

                       

                      Please setup the basic project in git.I will try to implement kairosdb translator.

                       

                      Anantha.

                      • 8. Re: Teiid Connector Example
                        Ramesh Reddy Master

                        Anantha,

                         

                        Please first enter JIRA for this request at Teiid - JBoss Issue Tracker, also mean while you can start with my suggestions above in terms of development. I will try have this setup hopefully today.

                         

                        Ramesh..

                        • 9. Re: Teiid Connector Example
                          Ramesh Reddy Master

                          Anantha,

                           

                          Here is Git Repo branch where I added KairosDB projects https://github.com/rareddy/teiid/tree/KariosDB

                           

                          • connector-kairosdb
                          • translator-kairiosdb

                           

                          I added all the deployment details, so you need to fork this repo and start adding the code to both projects. When you are done, you can send the update as pull request to me, then I will review and schedule to pull into the Teiid master.

                           

                          I did not find maven repo where I could find kairosdb client libraries, to add them to the maven repository. You can build kairosdb java client it locally start the development of the translator. To set up the Teiid build see Teiid Eclipse Dev Environment Set Up and Building Runtime Artifacts

                           

                          Read the developer guide and look at other sample connectors and translators and start writing code. Ask any specific questions if you have on Teiid forums. Good Luck.

                           

                          Ramesh..

                          • 10. Re: Teiid Connector Example
                            Laxmi Rathore Newbie

                            Hello Ramesh,

                            You had   provided Basic project in Github for the development of kairosdb translator . As i could see that the template classes you have provided is totally inspired from cassandra . But as per my understanding kairosdb and cassandra are very different . Kairosdb interact in the form of JSON calls.  i am not able to decide that which pre developed translator i should prefer as reference to proceed for my work. can you guide me for the same.

                             

                             

                            Thnx and Reagrds,

                            • 11. Re: Teiid Connector Example
                              Ramesh Reddy Master

                              Laxmi,

                               

                              It is no inspiration from Cassandra, that is the basic template framework of Teiid translator and connector. Main glue points with Teiid system have been covered. So, it does not matter what is the underlying source, this is most typical starting point.

                               

                              For connector, use the "KairosDB" java client libraries and figure out how a java process connects to KairosDB. Define the connection properties in ra.xml, and their setters and getters in the ConnectionFactory class, and build a connection object. Then expose the your connection interface that to be used by the translator. 

                               

                              For Translator, "execution" object is one executing a query using defined connection interface. So, here you need to decide how you are going to convert incoming SQL into a JSON request (look at visitor classes in other translators, how to walk a SQL comamnd). Then use Connection to execute it and grab the response, parse and feed it back to Teiid in "next" call as array of objects for each row at a time.

                               

                              BTW, there is already JSON library (simple-json) in the build if want to use it, you have add it to your pom.xml though.

                               

                              I suggest you read Developer's Guide, and take look at simple translators like "loopback" and "yahoo" and learn how translators work, then go to KairosDB one.

                               

                              HTH

                               

                              Ramesh..

                              • 12. Re: Teiid Connector Example
                                Ashish N Newbie

                                Ramesh,

                                 

                                Thanks for creating a template.

                                It would be great if you would help on following queries-

                                 

                                     1. a. I was facing issue while building from template you have shared for kairosdb through maven, was failing in dependencies in pom.xml.. basically was unable to develop connector archetype through maven. could you provide some inputs here..?

                                          b. However I builded by manually downloading jars required using sbt and got the jars for connector and translator. So as i did by sbt, jar has manifest file with no dependencies defined, will that work runtime?

                                          c. If that will work, could you please provide me steps to deploy these custom connector and translator as i am completely new to teiid, so that i can proceede further to vdb.

                                 

                                     2. I have developed sample connector and translator for single sample query, is there any way or could you provide your valuable pointers such that it will convert SQL format queries into kairos understandable json format. Is there any API or some existing framework that would fit here?

                                 

                                for example:

                                SQL query-                                              select metrics from kairos_table where key = 11119_200093133 and timestamp > 200 and  timestamp < 10;


                                Equivalent Kairosdb Json query-                {"metrics": [{"name": "11119_200093133"}],"cache_time": 0,"start_relative": {"value": "200","unit": "days"},"end_relative": {"value": "10","unit": "days"}}


                                     3. Just for curiosity itself, After develoiping translator for kairosdb, when we will run join queries for oracle and kairosdb, how it will work? Meaning that as oracle understands normal SQL format and kairosdb understands JSOn format and as we are running joins over them. And will queries with group-by, order-by works or even hierarchical queries like 'start-with .. connect by' works.. ?


                                Help is completely appreciated. Thanks.


                                -Ashish

                                • 13. Re: Teiid Connector Example
                                  Ramesh Reddy Master

                                  Ashish,

                                   

                                  1a) To build using maven, Teiid provides a settings.xml file. so execute like

                                   

                                  cd teiid

                                  maven -s settings.xml clean install -P release

                                   

                                  1b) It should, I believe.

                                   

                                  1c) I have already included all the needed scripts to build the deployments. When you are done with the build the "jboss-dist.zip" file will have the your custom translator in it.

                                   

                                  2) Take a look at one of sample connectors, like loopback or yahoo. See the "visitor" classes, they provide you mechanism to walk the SQL language objects, while walking then you can construct the needed JSON request based on it. Once done, submit that request using the execution object and return results one row at time using "next" method.

                                   

                                  3) Yes, it can do join queries over Oracle and KairosDB. Based on the metadata from Oracle and KairosDB and any of view definitions, Teiid can parse your in coming user query and figure out where the data is and split the SQL command to individual queries and execute them using the translators. Once the results comeback it will put the results together before returning them to the user. GroupBy, OrderBy works, not ConnectBy.

                                   

                                  Ramesh..