How to schedule a query in couchbase to run daily

Hi

Can any one please help me how to schedule a job in couchbase using N1QL that runs daily.

For example i want to run the below N1QL query

SELECT * FROM ‘bucket1’

This query needs to run daily basis at 5:00 AM
I dont want to make use of cron job to hit the query api services of couchbase. I want to schedule this job internal to couchbase itself.

Pls do the needful

using Rest api call you can schedule this as a cron job .
look into this for examples:
https://docs.couchbase.com/server/current/n1ql/n1ql-rest-api/exsuccessful.html
Now create your rest api call with proper credentials and save it in a shell script and modify the script to executable and then test your script . if it is successfull then you can schedule the cronjob with script.

Hi Viswanathan

You can use Eventing and Timers. Since a timer in Eventing is based on a a Javascript date the EM6 code could perform a rounding function examples (obviously the second form is more interesting as you want to trigger things in the future)

var d = new Date();
d.setHours(0,0,0,0); // last midnight
var d = new Date();
d.setHours(24,0,0,0); // next midnight

You need an initial mutation to kick things off . So the first time Eventing responds to a mutation it just sets a timer (similar to above to the proper time) when the time matures and fires the Eventing code is called and the ACTION is taken then JS can generate another timer for the next day.

I has attached a complete Eventing Handler or Function that can do what you want entirely within Couchbase via the Eventing feature. Note 6.5 iCurrently in Beta you can run N1QL directly from Eventing prior to 6.5 you have normal KV operations available from the exposed Javascript map of a required bucket. Below I aliased ‘travel-sample’ to the binding ‘ts’ in your final function you would could multiple aliases to access different buckets.

I even put in a N1QL query (a 6.5+ feature) to show how it is done.

This example creates a poor man’s crontab in Couchbase if you want to control multiple functions you might but another field in type="recurring_event " perhaps called “action_name” and use a switch statement based on its value or even decode the action you want from the field itself - I leave expansion as a user exercise.

/* 
Create a "poor man's" cron system using Eventing allows a recurring function to execute activity
a specified tie every day.  We used the 'travel-sample' and placed a control document 
of type=="recurring_event" for this example.  Note for testing uncomment out the line 
after TEST_ONLY.  Important you will need to alias 'travel-sample'  to 'ts' when you add this 
function into Eventing.  Note by default the Test Doc is NOT active.

Test Doc:    {"active":false,"hour":14,"min":54,"id":1,"type":"recurring_event"}

Activate:    set active = true
	     UPDATE `travel-sample` SET active = true WHERE type = 'recurring_event' AND id = 1
	     
Deactive:    set active = false (or delete the document)
	     UPDATE `travel-sample` SET active = false WHERE type = 'recurring_event' AND id = 1
	     DELETE from `travel-sample` WHERE type = 'recurring_event' AND id = 1
*/
function getNextRecurringDate(hour, min) {
  var date_now = new Date();
  var date_ret = new Date();
  date_ret.setHours(hour, min, 0, 0); // prototype Date.setHours(hour, min, sec, millisec)
  // make sure date_timer is in the future at least 1 minute in the future
  if ((date_ret.getTime() + (1 * 60 * 1000)) < date_now.getTime()) {
    // schedule for tommorow
    date_ret.setDate(date_ret.getDate() + 1);
  }

  // TEST_ONLY just add 15 seconds (in millis) to quickly verify functionality
  // date_ret.setTime(date_now.getTime() + 5 * 1000);

  return date_ret;
}

function OnUpdate(doc, meta) {
  // Check if further analysis is needed we only trigger on a recurring_event that is active
  if (doc.type !== "recurring_event" || doc.active != true) return;

  var id = doc.type + "_" + doc.id;
  var hour = doc.hour;
  var min = doc.min;

  var date_timer = getNextRecurringDate(hour, min);
  log("OnUpdate (sched date) " + date_timer);
  log('OnUpdate create recuring timer for id ', id);

  // Schedule an Eventing timer
  createTimer(RecurringCallback, date_timer, id, doc);
}

function RecurringCallback(doc) {
  // Check if further analysis is needed we only trigger on a recurring_event that is active
  if (doc.type !== "recurring_event") return;
  // will check 'doc.active' later, but not from this context, we will read it from KV

  var id = doc.type + "_" + doc.id;

  var tst = null;
  try {
    tst = ts[id];
  } catch (e) {} // needed for pre 6.5, note pure 6.5+ deployment returns null without an exception
  if (!tst) {
    log("No Re-Arm src document controlling recurring timer missing, id was", id);
    return; // will work for pre 6.5 and post 6.5+ logic
  }
  // look at the current doc, not the context we have been passing around to Re-arm
  if (tst.active != true) {
    log("No Re-Arm src document controlling recurring timer has 'active' = false, id is", id);
    return;
  }

// ===================================================================================================
/* BEG ANYTHING YOU WANT HERE (pre 6.5 you have KV ops, for 6.5+ also N1QL AND CURL) will show N1QL */
  
  // this is a 6.5 N1QL query (feature not available prior to 6.5)
  try {
    // Issue SELECT statement to get the counts.
    var ginfo = SELECT country, count( * ) cnt FROM `travel-sample` WHERE `type` = 'airline' GROUP BY country;
    // loop through the result set
    for (var val of ginfo) {
      log('N1QL country' + val.country + " cnt", val.cnt);
    }
  } catch (e) {
    log('Error exception NIQL:', e);
  }
  
  /* END ANYTHING YOU WANT HERE */
  // ===================================================================================================

  var hour = doc.hour;
  var min = doc.min;

  var date_timer = getNextRecurringDate(hour, min);
  log("RecurringCallback (sched date) " + date_timer);
  log('RecurringCallback create recurring timer for id ', id);

  // Re-arm e.g. Re-schedule an Eventing timer
  createTimer(RecurringCallback, date_timer, id, doc);
}

For the above example the Eventing settings were as follows:

1 Like

Thank you very much @jon.strabala, That solved the problem. You had saved my day.
Really Appreciate your detailed explanation along with the code.

I have One small clarification: why do we need this piece of code inside RecurringCallback(doc) function


  var tst = null;
  try {
    tst = ts[id];
  } catch (e) {} // needed for pre 6.5, note pure 6.5+ deployment returns null without an exception
  if (!tst) {
    log("No Re-Arm src document controlling recurring timer missing, id was", id);
    return; // will work for pre 6.5 and post 6.5+ logic
  }
  // look at the current doc, not the context we have been passing around to Re-arm
  if (tst.active != true) {
    log("No Re-Arm src document controlling recurring timer has 'active' = false, id is", id);
    return;
  }```

Thanks @rvuppala. Yes thats also a solution i will try with, But the issue is, when i curl it inside the shell script , i need to give the Authentication Header anyway. So it will be easily exposed and it becomes vulnerable.
Any other approach you have to make the curl even more secure.

1 Like

in that case source your passwords using different file .