1 2 Previous Next 15 Replies Latest reply on Apr 2, 2013 9:53 AM by Steven Hawkins Branched from an earlier discussion.

    Re: Continuous request against Procedure Execution

    Danah Warren Newbie

      Hey guys,

       

      You all seem to be very savvy with this topic.

       

      If you could provide some feedback that would be awesome.

       

      I have a virtual procedure in my VDB that creates a Twitter table as a View.

       

      I then have that View created into a physical table in the H2 database.

       

      My next step is to obtain a live Twitter stream, and I was thinking of using the Continuous Execution feature. Is that possible?

       

      I have Teiid 8.3, JBoss 713, and I'm using Eclipse JDBC and SQuirreL.

       

      Thanks in advance!

       

      Danah

        • 1. Re: Continuous request against Procedure Execution
          Mark Addleman Master

          I'm having trouble posting my response.  I keep getting "forbidden content"

          • 2. Re: Continuous request against Procedure Execution
            Mark Addleman Master

            I have been thinking of writing a blog post discussing the ins-and-outs of continuous executions using a Twitter stream as my example.  I've just been swamped with work and haven't gotten around to it. The short answer is, yes.

             

            A continuous query is an option that you specify when you submit your SQL to Teiid.  The option is only available through a proprietary Teiid Java API (discussed somewhere in the docs) so you won't be able to start it using Squirrel.  I don't recall the Twitter API in detail but I believe you can register a listener and have it push new tweets to you.  This maps very closely to the Teiid continuous execution API.  To lay out a basic approach:

             

            1. Create a Teiid view, TWEET_STREAM.  This view is backed by a translator that maps Twitter's push capability to Teiid's continuous execution API.
            2. Create an H2 table (exposed in your Teiid model) called TWEETS.  The schema for TWEETS and TWEET_STREAM will probably be identical.  The difference is that TWEET_STREAM is a volatile data stream of tweets while TWEETS is a stable store of the tweets that your application can query.
            3. Your application creates a continuous query against TWEET_STREAM and inserts each row into TWEETS.  You would be creating the moral equivalent of INSERT INTO TWEETS SELECT * FROM TWEET_STREAM but, unfortunately, Teiid won't allow this in continuous mode.

             

            I'm happy to provide further guidance if you have more questions

            • 3. Re: Continuous request against Procedure Execution
              Danah Warren Newbie

              Wonderful!

               

              Any guesstimation of when you can expect a guide of some sort to be available?

               

              May you offer any suggestions to get me started? I've been researching the feature but not sure how I can utilize it with what I have been doing thus far.

               

              Thanks,

              Danah

              • 4. Re: Continuous request against Procedure Execution
                Mark Addleman Master

                I had a heck of a time posting my full response.  Please read through that and see if that is enough to get you started

                • 5. Re: Continuous request against Procedure Execution
                  Danah Warren Newbie

                  Trust me, I know the feeling. This website always use to give me that error. I was under the impression that I was not welcome here

                   

                  Thank you so much Mark, I'll get started with your basic approach shortly.

                   

                  I will be working on this today and part of the weekend, so I'm sure I may need your assistance in the near future.

                   

                  Again, thanks for the start-up!

                   

                  Much apprecation,

                   

                  Danah

                  • 6. Re: Continuous request against Procedure Execution
                    Danah Warren Newbie

                    Hi Mark,

                     

                    Quick question:

                     

                    What goes in my twitter-vdb.xml and what goes in my Java class?

                     

                    Here's the model from my VDB:

                     

                    <model name="twitterview" type="VIRTUAL">

                             <metadata type="DDL"><![CDATA[

                                                    CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), from_user varchar(25), 

                                      text varchar(140)) AS

                                    select tweet.* from

                                        (call twitter.invokeHTTP(action => 'GET', endpoint =>querystring('',query as "q"))) w,

                                        XMLTABLE('results' passing JSONTOXML('myxml', w.result) columns

                                        created_on string PATH 'created_at',

                                        from_user string PATH 'from_user',

                                        text string PATH 'text') tweet;

                                                   CREATE VIEW Tweet AS select * FROM twitterview.getTweets;

                                                  

                                        ]]> </metadata>

                        </model>

                     

                    Do I keep any part of this or do I start from scratch?

                     

                    Or maybe keep the View in the VDB and then create the physical table in Java?

                     

                    Much appreciation,

                     

                    Danah

                    • 7. Re: Continuous request against Procedure Execution
                      Mark Addleman Master

                      I'm not very familiar with the Twitter API.  It seems like this HTTP request will obtain all the tweets that match a query?  If so, I don't think you want to marry this up to the continuous execution since this view would give you a stable set of tweets. 

                       

                      Maybe you could describe what you're trying to do.

                      • 10. Re: Continuous request against Procedure Execution
                        Danah Warren Newbie

                        Yes, that is exactly how it works. I'm familiar with the static Twitter table represented as a View wih the help of the "web service as a data source" example in the quick start guide.

                        • 11. Re: Continuous request against Procedure Execution
                          Mark Addleman Master

                          I guess I still don't understand what user-facing behavior you're shooting for.  Since the twitter API you're using always returns a compete data set, what are you looking for from the continuous query?

                          • 12. Re: Continuous request against Procedure Execution
                            Danah Warren Newbie

                            Hi Mark,

                             

                            Can you look at my code?

                             

                            twitterstream-vdb.xml

                             

                            <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

                            <vdb name="TwitterStream" version="1">

                             

                                <description>Updating Twitter data into MySQL database</description>

                             

                                 <model name="Stream">

                                     <property name="UseConnectorMetadata" value="true" />

                                          <source name="tweettable" translator-name="mysql" connection-jndi-name="java:/mysql-ds"/>

                                </model>

                             

                            </vdb>

                             

                            I'm using MySQL:

                             

                            standalone-teiid.xml

                             

                                       <datasource jndi-name="java:/mysql-ds" pool-name="mysqlDS" enabled="true" use-java-context="true">

                                                <connection-url>jdbc:mysql://localhost:3306/LiveTwitter;INIT=RUNSCRIPT FROM '../standalone/deployments/twitter/twittertable-schema.sql'\</connection-url>

                                                <driver>mysql</driver>

                                                <security>

                                                    <user-name>danah</user-name>

                                                    <password>warren</password>

                                                </security>

                                            </datasource>

                                            <drivers>

                                                <driver name="mysql" module="com.mysql">

                                                    <driver-class>com.mysql.jdbc.Driver</driver-class>

                                                </driver>

                                           </drivers>

                             

                            but I get the following error

                             

                            TwitterStream.1 model "Stream" metadata failed to load. Reason...Connection Factory (no data source found) provided is null; Can not proceed with metadata load.

                             

                            What could it be?

                             

                            Thanks,

                            Danah

                            • 13. Re: Continuous request against Procedure Execution
                              Mark Addleman Master

                              At first blush that seems right to me.  Does the JBoss log indicate that your data source is being loaded?

                              • 14. Re: Continuous request against Procedure Execution
                                Danah Warren Newbie

                                Hi Mark,

                                 

                                Both the connector jar and the VDB was deployed, but it says the metadata failed to load so its not set to Active.

                                 

                                08:58:00,683 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50036 VDB

                                TwitterStream.1 model "Stream" metadata failed to load. Reason:TEIID31097 Conne

                                ction Factory (no data source found) provided is null; Can not proceed with meta

                                data load.

                                 

                                Thanks,

                                Danah

                                1 2 Previous Next