-
30. Re: Twitter VDB
rareddy Mar 27, 2013 4:42 PM (in response to divadatabase)Danah,
My sincere suggestion ignore the maven based invocation, and use the SquirreL. It calls your VDB same way as the other one, Also this shows in nice windows application.
Ramesh..
-
31. Re: Twitter VDB
divadatabase Mar 27, 2013 4:48 PM (in response to rareddy)Hi Ramesh,
I've already downloaded it and will begin using it shortly.
Can I use that tool to access a remote server? I want to deploy (and activate of course) my VDBs from a remote Teiid server and save my Twitter data (the virtual table created from the twitter-vdb) to Hadoop or H2 and use Teiid to query it through SQuirreL.
Is that possible? If not, what's the best route you suggest I take to accomplish that?
As always, thanks...
Danah
-
32. Re: Twitter VDB
van.halbert Mar 27, 2013 4:52 PM (in response to divadatabase)Danah,
Ah, I see what you did. You need to add C:\Teiid 8.3\Maven\apache-maven-3.0.5\bin to your PATH environment variable.
Then set M2_HOME to a directory where you want the artifacts stored. On linux/mac, the repo directory is named: /user/home/.m2
Van
-
33. Re: Twitter VDB
rareddy Mar 27, 2013 4:53 PM (in response to divadatabase)Yes. You can call a remote Teiid server with SquirreL. You only need to delve into anything else, when you start using the VDB in your own java program.
-
34. Re: Twitter VDB
divadatabase Mar 27, 2013 5:05 PM (in response to rareddy)Van:
Okay, I knew it was something weird going on. I will do that now. Should my M2_HOME still be just the 'bin' folder or the entire maven directory (with the bin, boot, conf, lib, ect...)?
Also my OS is Windows 7.
Ramesh:
Perfect!
> You only need to delve into anything else, when you start using the VDB in your own java program.
When you say that, do you mean that I can do everything I listed with SQuirreL alone? But when I use my own Java program, like a JDBCClient in Eclipse to use the VDBs, I need additional features/plugins that Eclipse doesn't provide?
Thanks,
Danah
-
35. Re: Twitter VDB
van.halbert Mar 27, 2013 5:13 PM (in response to divadatabase)Dinah,
Your M2_HOME can be any directory. But make separate from any tool or program library you're using. Its only going to be storing the artifacts it downloads for running the examples.
Van
-
36. Re: Twitter VDB
divadatabase Mar 27, 2013 5:30 PM (in response to van.halbert)Hi Van,
Thanks a lot! That made things better but I still get a Build Failure error (I need a pom file in the simpleclient directory it says)
Not to worry though, I will use SQuirreL instead.
You've been so helpful.
Please don't go too far, I may need your help again soon! =)
Have a wonderful day!
Danah
-
37. Re: Twitter VDB
divadatabase Mar 28, 2013 1:48 PM (in response to divadatabase)Hello again,
Im trying to put the data from the Twitter View directly into a table within my twitter-vdb.
Can I do this?
<model name="twitterview" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), from_user varchar(25), to_user varchar(25),
profile_image_url varchar(25), source 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',
to_user string PATH 'to_user',
profile_image_url string PATH 'profile_image_url',
source string PATH 'source',
text string PATH 'text') tweet;
CREATE VIEW Tweet AS select * INTO TweetTable FROM twitterview.getTweets;
]]> </metadata>
</model>
If not, what's an alternative? I'm also using SQuirreL now, nice tool!
I wanna put the new table, TweetTable, into a H2 database.
Thanks,
Danah
-
38. Re: Twitter VDB
van.halbert Mar 28, 2013 5:03 PM (in response to divadatabase)Danah,
That's not the correct way. You have a couple of options, depending on what you're trying to achieve:
- use materialization to persist data: see here: https://docs.jboss.org/author/display/teiid830final/DDL+Metadata and https://docs.jboss.org/author/display/teiid830final/Materialized+Views
- could use a Select * INTO {tablename} from twitterview.Tweet where {tablename} needs to be a physical table that maps to the H2 data source
In either case, you will need a physical model for your H2 datasource.
Van
-
39. Re: Twitter VDB
divadatabase Mar 28, 2013 5:46 PM (in response to van.halbert)Hi Van,
Okay, thanks, I'll check those links out shortly.
I' tried to create another model in the twitter-vdb that uses the H2 driver that would import a SQL file to create the table but I got an error (as usual).
Can I do this?
-------------twitter-vdb.xml file----------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Twitter" version="1">
<description>Gets Data From the Twitter API</description>
<property name="UseConnectorMetadata" value="cached" />
<model name="twitter">
<source name="twittersource" translator-name="rest" connection-jndi-name="java:/twitterDS"/>
</model>
<model name="twitterview" type="VIRTUAL">
<metadata type="DDL"><![CDATA[
CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), from_user varchar(25),
profile_image_url 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',
profile_image_url string PATH 'profile_image_url',
text string PATH 'text') tweet;
CREATE VIEW Tweet AS select * FROM twitterview.getTweets;
SELECT * INTO TweetTable FROM twitterview.Tweet;
]]> </metadata>
</model>
<model name="twittertable">
<property name="importer.useFullSchemaName" value="false"/>
<source name="table" translator-name="h2" connection-jndi-name="java:/tableDS"/>
</model>
<translator name="rest" type="ws">
<property name="DefaultBinding" value="HTTP"/>
<property name="DefaultServiceMode" value="MESSAGE"/>
</translator>
</vdb>
--------------------------------------------------------------------------------
--------------twittertable-schema.sql file--------------------------
CREATE TABLE TweetTable (
created_on varchar(25),
from_user varchar(25),
to_user varchar(25),
profile_image_url varchar(25),
source varchar(25),
text varchar(140));
-------------------------------------------------------------------------
And then I would add H2 as a data source in the datasources section of standalone-teiid and map it to the SQL file, right?
Thanks again!
-
40. Re: Twitter VDB
divadatabase Mar 29, 2013 11:01 AM (in response to divadatabase)Hi,
I was able to get it working properly and my VDBs are active.
Thank you,
Danah
-
41. Re: Twitter VDB
divadatabase Mar 29, 2013 2:23 PM (in response to divadatabase)Hello again,
Here's my model:
<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;
SELECT * INTO TweetTable FROM twitterview.Tweet;
]]> </metadata>
</model>
It was working at first, but now I'm getting this error each time I run my standalone file:
TEIID50036 VDB
Twitter.1 model "twitterview" metadata failed to load. Reason:TEIID30386 org.te
iid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encounte
red "FROM twitterview.getTweets; [*]SELECT[*] * INTO" at line 11, column 26.
Was expecting: <EOF> | "alter" | "create" | "set"
How can I accomodate this exception that it is throwing?
I've been to docs.jboss.org/teiid/7.7.0.Final/admin-guide/en-US/html_single/#d0e119 but it wasn't much help.
Thanks,
Danah
-
42. Re: Twitter VDB
van.halbert Mar 29, 2013 3:42 PM (in response to divadatabase)Danah,
You cannot put a Select * INTO in -vdb.xml. Please look at the VDB section in the refernce guide where it explains the supported DDL syntax when defining a dynamic vdb.
Van
-
43. Re: Twitter VDB
divadatabase Mar 29, 2013 4:16 PM (in response to van.halbert)So where does it go? because the documentation - http://docs.jboss.org/teiid/7.7.0.Final/reference/en-US/html/ddl_support.html - says that I can. However, when I tried it I got the QueryParsing error again.
Would I put it in my twittertable-schema.sql file?
Danah