I'm having trouble posting my response. I keep getting "forbidden content"
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:
- 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.
- 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.
- 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
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.
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
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!
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">
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;
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?
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.
It's not letting me post my reply
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.
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?
Can you look at my code?
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="TwitterStream" version="1">
<description>Updating Twitter data into MySQL database</description>
<property name="UseConnectorMetadata" value="true" />
<source name="tweettable" translator-name="mysql" connection-jndi-name="java:/mysql-ds"/>
I'm using MySQL:
<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 name="mysql" module="com.mysql">
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?
At first blush that seems right to me. Does the JBoss log indicate that your data source is being loaded?
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