Automate or Schedule VDB Deployment

[kisienya]

Hi,

I have a working VDB that I use to insert data from CSV file to MySQL.

I want to automate this task and schedule it to run everyday at midnight.

How can I do this?

Regards

Morgan

[van.halbert]

Teiid only provides a TTL notion for refreshing, it doesn't have a date/time scheduler option.    However, any tool that can make the SYSADMIN procedure call can be used.   You can use your favorite scheduler (i.e, cron, JBoss JON, etc.) or create one for yourself using the EAP example (http://www.jboss.org/quickstarts/eap/ejb-timer/).  

[kisienya]

Hi Van,

Thanks for your reply,

Do you have a sample command or template script I can call from cron in order to insert data from CSV to MySQL using the VDB?

Regards

Morgan

[van.halbert]

Hi Morgan,

There are a several aspects to think about.   First the modeling, how would you model the CSV and MySQL tables?  Could model the MySQL as a materialized table of the CSV table.   And then use the SYSADMIN procedure call to trigger the refreshing.   Or you could issue the SELECT INTO yourself to perform the insert.    I would suggest using the materialized table approach for large volumes of data, especially if its an all or nothing (rollback on exception) transaction.

As for running a cron job, an option would be to write a jdbc client (or could use the simple client quick start to get started) to issue the sql command to Teiid. 

Example cron:

# run everyday

0 0 * * * java -jar clientjdbc.jar param1 param2 ...

Depending on how you write your client, you could pass in or hardcode your sql command to be executed. 

Hope this helps.

Van

[kisienya]

Hi Van,

I do appreciate the prompt and detailed response.

You are right, the materialized approach would eventually come in handy. I am reading about it. In the meantime, below is the command that I execute from Teiid.

select * from ( exec "NameofProcedure"() ) AS X_X

This executes a procedure that successfully inserts bulk data from CSV to MySQL.

What values would I use for parameter1/parameter2 on the cron job

Regards

Morgan

[van.halbert]

The parameters will be dependent on how you wish to control / trigger what you want to run.  Or you don't have to have any parameters.  That's totally up to how you wish to manage execution.

Van

[georgespurlock]

Thanks for your reply