-
1. Re: Continuous request against Procedure Execution
markaddleman Mar 29, 2013 3:21 PM (in response to divadatabase)I'm having trouble posting my response. I keep getting "forbidden content"
-
2. Re: Continuous request against Procedure Execution
markaddleman Mar 29, 2013 3:31 PM (in response to divadatabase)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
-
3. Re: Continuous request against Procedure Execution
divadatabase Mar 29, 2013 3:31 PM (in response to markaddleman)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
markaddleman Mar 29, 2013 3:32 PM (in response to divadatabase)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
divadatabase Mar 29, 2013 3:47 PM (in response to markaddleman)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
divadatabase Mar 29, 2013 4:41 PM (in response to 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
-
7. Re: Continuous request against Procedure Execution
markaddleman Mar 29, 2013 5:02 PM (in response to divadatabase)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.
-
8. Re: Continuous request against Procedure Execution
divadatabase Mar 30, 2013 1:11 AM (in response to markaddleman)test post
-
9. Re: Continuous request against Procedure Execution
divadatabase Mar 30, 2013 9:55 AM (in response to divadatabase)It's not letting me post my reply
-
10. Re: Continuous request against Procedure Execution
divadatabase Mar 30, 2013 10:27 AM (in response to 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.
-
11. Re: Continuous request against Procedure Execution
markaddleman Mar 31, 2013 12:13 PM (in response to divadatabase)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
divadatabase Apr 1, 2013 8:40 PM (in response to markaddleman)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
markaddleman Apr 1, 2013 8:48 PM (in response to divadatabase)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
divadatabase Apr 2, 2013 9:01 AM (in response to markaddleman)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