Timing Task in SAP HANA
we have a Chinese version of this blog
Assume that you will receive a batch of file every other minutes, and then you need to import these files into SAP HANA. Traditionally , you have to write a script program to import these files and then set up a timing task to execute this program. And you may wonder if SAP HANA can support timing task ?
Before SAP HANA SPS07 , this is not supported. After SAP HANA SPS07, this is simple, and very cool, as you will see, you can tell SAP HANA to execute some timing task autocratically, just SAP HANA, no other components like “crontab” of linux.
Now I will show you with an example.
1 Set up the environment.
first ,you need to configure SAP HANA in HANA Studio, like the following figure:
In default situation, the repository section, the sqlscript_mode is ‘default’ , here I recommend to change it to ‘UNSECURE’. If you do not change this ,then the task may not able to do the ‘insert’ or ‘update’ job.
On the other hand, for the SCHEMA which you will handle, you should assign the related privilege. Or you may see the following error when activate the procedure.
when this happens, you should assign the privilege to _SYS_REPO for the schema WEIYY. This is because when you commit to the repository, and then activate, it is the _SYS_REPO’s job to generate the related object. So ,_SYS_REPO, must have the permission.
at last, you also should configure the xsengine, like the following figure, in the xsengine.ini, add a section named scheduler, and add the enabled argument with the value true.
2 Timing Task
First we create a XS Project in SAP HANA Studio, and then create a .xsapp file. And then create a jsjobtest.xsjs file , this is a “xsjs” file, inside it there is a function myjsjob, this function is the job we will do. Here we just insert a record to he table “TESTTBL”, and record the time information.
Then we need a xsjob file, to descibe when the job will be scheduled.
“description”: “run every 5 seconds”,
“xscron“: “* * * * * * 0:59/5”
description: the description of the task.
action:the task of the file,and the entrance of the function.
schedules: the schedule information of the task ,the grammar is similar to the grammar of “crontab”, here we let the task run every 5 seconds.
And now we add a SQLScript task, it also insert record to the “TESTTBL” ,but the information is a little different.
CREATE PROCEDURE sqljobtest ( ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER --DEFAULT SCHEMA <schema> AS BEGIN /***************************** Write your procedure logic *****************************/ insert into WEIYY.TESTTBL(T,INFO) VALUES(NOW(),'insertted from SQLScript job'); END;
Here we let it run every 10 seconds.
“description”: “my first SQLScript job”,
“description”: “run every 10 seconds”,
“xscron“: “* * * * * * 0:59/10”
[Note: the action part is a little different between js task and SQLScript task ]
3 Launch the timing task
After add the timing task ,the next step is to launch the task. We need to use the “XS Administration Tool”, and you need to assign the role “sap.hana.xs.admin.roles::JobAdministrator” to XS Engine. For different HANA instance ,the web address is like this:
For the SQLScript task, do the same:
then go back to SAP HANA Studio
In the “_SYS_XS”.”JOBS” table you can see the task information.
In the “_SYS_XS”.”JOB_LOG” you can see the log information.
[SAP HANA version of the test cases used here is SAP HANA SPS7 Revision 70.00. ]