[rareddy]

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..

[divadatabase]

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

[van.halbert]

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

[rareddy]

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.

[divadatabase]

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

[van.halbert]

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

[divadatabase]

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

[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

[van.halbert]

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

[divadatabase]

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!

[divadatabase]

Hi,

I was able to get it working properly and my VDBs are active.

Thank you,

Danah

[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

[van.halbert]

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

[divadatabase]

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