Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

see all currently running scripts

martijn_dib
Contributor

Hi all,

Currently I am writing a python script to start off a script in Exasol using the Exasol Websocket (in order to fill in the lack of a scheduler in Exasol atm).

 

However, I would like to get some conditional logic involed saying please do not start the script when it is already running. Problem is that I cannot really find a system table that shows all currently running scripts. I can find the currently running sql statements, but the script executes a bunch of different sql statements (which are also variable, so i cannot just check if any of these statements is running). 

 

Is there a way to check if anything in the scope of the script is currently running in Exasol?? The script that I want to execute from my python script is written in LUA. 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

martijn_dib
Contributor

Hi Johannes, we had to add some logging of the sessions that start off these scripts. When the session is stopped due to connection issues the STATUS is not updated in the job log. Hence we also now check to see if the session of the last script is still alive when the status is still running. Quite helpfull for situations where i.e. the database is shut down at night for cost reasons. (then this situation occurs a lot!). In the code example you can see what we do to check. To register the session ID that runs the script we added the session id to the job_detail log that registers the job nr .... in line 254 of the query wrapper. 

 

  -- row 247 of the query wrapper script
      local success, res = pquery( [[SELECT MAX( run_id ) FROM ::MAIN_LOG_TABLE]], {MAIN_LOG_TABLE = main_log_table} )
        if not success then
            self:log( 'WARNING', 'Failed to retrieve job id: [' .. res.error_code .. '] ' .. res.error_message )
            pquery( [[ROLLBACK]] )
            return nil
        end
        self.run_id = res[1][1]
        self:log( 'INFO', 'Job nr. ' .. self.run_id .. ' registered session id =' .. self.session_id )

 

 

CREATE LUA SCRIPT "XXX" () RETURNS ROWCOUNT AS
import('etl.query_wrapper_test','qw')

wrapper = qw.new('etl.job_log', 'etl.job_details', exa.meta.script_name)

suc, res  = wrapper:query([[SELECT 
								jl.RUN_ID,
								SUBSTRING(LOG_MESSAGE, LOCATE('=', LOG_MESSAGE)+1) prev_session_id,
								CASE WHEN jl2.status = 'STARTED...' THEN TRUE 
								ELSE FALSE 
								END script_is_running, -- IF FALSE THEN SCRIPT can GO ahead
								CASE WHEN edas.SESSION_ID IS NULL THEN FALSE
								ELSE TRUE
								END session_is_active -- IF FALSE THEN SCRIPT can GO ahead
							from							
								(SELECT 
									max(run_id) run_id 
								FROM etl.JOB_LOG jl 
									WHERE SCRIPT_NAME = ']] ..exa.meta.script_name.. [[' AND RUN_ID < (SELECT MAX(RUN_ID) FROM etl.JOB_LOG 
									WHERE SCRIPT_NAME = ']] ..exa.meta.script_name.. [[')) jl
							LEFT JOIN etl.JOB_DETAILS jd ON jd.RUN_ID = jl.RUN_ID AND REGEXP_INSTR(jd.LOG_MESSAGE, 'registered session id =[0-9]{19}') <> 0
							LEFT JOIN etl.JOB_LOG jl2 ON jl.RUN_ID = jl2.RUN_ID
							LEFT JOIN EXA_STATISTICS.EXA_DBA_AUDIT_SESSIONS edas ON TO_CHAR(edas.SESSION_ID) = SUBSTRING(LOG_MESSAGE, LOCATE('=', LOG_MESSAGE)+1); ]])
	
							
if res[1][3] == false then 
	goto continue
elseif res[1][4] == false then
	goto continue
else 
	wrapper:log('WRAP_LOG', exa.meta.script_name..' FAILURE: PREVIOUS SCRIPT STILL RUNNING')
	goto exit
end -- followed by actual script 

 

 

View solution in original post

6 REPLIES 6

exa-Aleksandr
Team Exasol
Team Exasol

Hi @martijn_dib ,

If you are using pyexasol, you could consider using custom client_name for that purpose, for example:

https://github.com/exasol/pyexasol/blob/master/examples/c05_session_params.py#L21

 

johannes
Contributor

You could INSERT or UPDATE something into a table (and commit) to indicate that the script is currently running. After finishing the script, unset this lock again. Maybe also add a timestamp to unlock if the lock is older than a specific timeout value.

The Query Wrapper does similar things: https://github.com/exasol/etl-utils
It inserts log entries into a log table to indicate when a script starts, which queries it executes, whether they were sucessful, how many rows they affected or returned, and custom log and debug messages. When you use this script, you can query the JOB_LOG table and search for SCRIPT_NAME = 'Your_Script' and STATUS = 'RUNNING'

martijn_dib
Contributor

Hi Aleksandr,

 

Not sure how naming my client would help me identify if a random LUA script is running. At the moment we solved it by using the Job_log and Job_detail tables to store sessions IDs and check if both the script are marked as running + the session that started this script is also still active. Sometimes the query wrapper will not 'close' a run due to connectivity issues. Then the job_log table is not suitable. Hence we need to double check to see if the session that started a script is also still alive. We managed to do this by some simple SQL to check system tables for the previous job & the session that went along with it. 

martijn_dib
Contributor

Hi Johannes, we had to add some logging of the sessions that start off these scripts. When the session is stopped due to connection issues the STATUS is not updated in the job log. Hence we also now check to see if the session of the last script is still alive when the status is still running. Quite helpfull for situations where i.e. the database is shut down at night for cost reasons. (then this situation occurs a lot!). In the code example you can see what we do to check. To register the session ID that runs the script we added the session id to the job_detail log that registers the job nr .... in line 254 of the query wrapper. 

 

  -- row 247 of the query wrapper script
      local success, res = pquery( [[SELECT MAX( run_id ) FROM ::MAIN_LOG_TABLE]], {MAIN_LOG_TABLE = main_log_table} )
        if not success then
            self:log( 'WARNING', 'Failed to retrieve job id: [' .. res.error_code .. '] ' .. res.error_message )
            pquery( [[ROLLBACK]] )
            return nil
        end
        self.run_id = res[1][1]
        self:log( 'INFO', 'Job nr. ' .. self.run_id .. ' registered session id =' .. self.session_id )

 

 

CREATE LUA SCRIPT "XXX" () RETURNS ROWCOUNT AS
import('etl.query_wrapper_test','qw')

wrapper = qw.new('etl.job_log', 'etl.job_details', exa.meta.script_name)

suc, res  = wrapper:query([[SELECT 
								jl.RUN_ID,
								SUBSTRING(LOG_MESSAGE, LOCATE('=', LOG_MESSAGE)+1) prev_session_id,
								CASE WHEN jl2.status = 'STARTED...' THEN TRUE 
								ELSE FALSE 
								END script_is_running, -- IF FALSE THEN SCRIPT can GO ahead
								CASE WHEN edas.SESSION_ID IS NULL THEN FALSE
								ELSE TRUE
								END session_is_active -- IF FALSE THEN SCRIPT can GO ahead
							from							
								(SELECT 
									max(run_id) run_id 
								FROM etl.JOB_LOG jl 
									WHERE SCRIPT_NAME = ']] ..exa.meta.script_name.. [[' AND RUN_ID < (SELECT MAX(RUN_ID) FROM etl.JOB_LOG 
									WHERE SCRIPT_NAME = ']] ..exa.meta.script_name.. [[')) jl
							LEFT JOIN etl.JOB_DETAILS jd ON jd.RUN_ID = jl.RUN_ID AND REGEXP_INSTR(jd.LOG_MESSAGE, 'registered session id =[0-9]{19}') <> 0
							LEFT JOIN etl.JOB_LOG jl2 ON jl.RUN_ID = jl2.RUN_ID
							LEFT JOIN EXA_STATISTICS.EXA_DBA_AUDIT_SESSIONS edas ON TO_CHAR(edas.SESSION_ID) = SUBSTRING(LOG_MESSAGE, LOCATE('=', LOG_MESSAGE)+1); ]])
	
							
if res[1][3] == false then 
	goto continue
elseif res[1][4] == false then
	goto continue
else 
	wrapper:log('WRAP_LOG', exa.meta.script_name..' FAILURE: PREVIOUS SCRIPT STILL RUNNING')
	goto exit
end -- followed by actual script 

 

 

exa-Aleksandr
Team Exasol
Team Exasol

Hi @martijn_dib ,

If you you run one script per session, then setting unique client name (say, derived from the script name) will let you find a running session in exa_all_sessions via client_name column.

martijn_dib
Contributor

Ah got it. Yeah it would have simplified our work a bit with the adapting the query wrapper and the sql statement to find that session. It has been good LUA practise and good monitoring experience with Exasol nonetheless like this ;).

 

An AUDIT view monitoring scripts would still be a Nice To Have similar to how sql statements are monitored.