Re: Continuous request against Procedure Execution

[divadatabase]

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

[markaddleman]

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

[markaddleman]

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

[divadatabase]

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

[markaddleman]

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

[divadatabase]

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

[divadatabase]

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

[markaddleman]

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.

[divadatabase]

test post

[divadatabase]

It's not letting me post my reply

[divadatabase]

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.

[markaddleman]

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?

[divadatabase]

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

[markaddleman]

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

[divadatabase]

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