6 Replies Latest reply on Jun 12, 2015 8:20 AM by georgespurlock

    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

        • 1. Re: Automate or Schedule VDB Deployment
          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/).  

          • 2. Re: Automate or Schedule VDB Deployment
            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

            • 3. Re: Automate or Schedule VDB Deployment
              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

              • 4. Re: Automate or Schedule VDB Deployment
                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

                • 5. Re: Automate or Schedule VDB Deployment
                  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

                  • 6. Re: Automate or Schedule VDB Deployment
                    georgespurlock

                    Thanks for your reply