SQL Procedures

Kinetica provides support for basic SQL procedures, as an executable batch of SQL statements. A procedure can be executed by two means:

  • on-demand - procedure is called directly by a user
  • scheduled execution - procedure is configured, upon creation, to execute at a user-specified interval

Note

Even if a procedure is configured for scheduled execution, it can still be executed directly by a user in on-demand fashion.

After the first run, the execution plan for all statements in the procedure will be created and cached to improve performance on future executions.

If there is an error executing any statement in the procedure, the procedure will stop immediately and report the error. If the procedure is invoked via scheduled execution, an alert will be sent to the alert monitor, as there is no interactive session through which the error could be reported to a user. Any database modifications prior to the error will not be rolled back.

Note

If any of the database objects referenced in the procedure are dropped or modified, the procedure will be dropped as well. This does not include any objects created by the procedure that are later referenced by it.

The ability to manage & execute procedures is available through SQL, using the following commands:

For procedure execute permission management, see:


Supported Statements

The following statement types are allowed within a SQL procedure:

  • SELECT
  • INSERT (inserting from data file not supported)
  • UPDATE
  • DELETE
  • <CREATE | DROP | SHOW CREATE> SCHEMA
  • <CREATE | TRUNCATE | DROP | SHOW CREATE> TABLE
  • CREATE TABLE...AS
  • <CREATE [MATERIALIZED] | REFRESH | DROP | SHOW CREATE> VIEW
  • DESCRIBE
  • SHOW PROCEDURE
  • SHOW SECURITY [FOR <USER | ROLE>]
  • SHOW RESOURCE GROUP

Note

Any unqualified table/view references made within the procedure will be assumed to exist in the procedure creator's default schema at the time of creation. Those references will be set upon creation and never re-evaluated afterwards, regardless of the execution type of the procedure, who executes it, or what EXECUTE AS option is specified.

CREATE PROCEDURE

CREATE PROCEDURE Syntax
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE [OR REPLACE] PROCEDURE [<schema name>.]<procedure name> [()]
[LANGUAGE SQL]
BEGIN
    <sql statements>
END
[
    EXECUTE FOR EVERY <number> <SECOND[S] | MINUTE[S] | HOUR[S] | DAY[S]>
    [STARTING AT '<YYYY-MM-DD [HH:MM[:SS]]>']
]
[WITH OPTIONS (<stored procedure property name> = '<stored procedure property value>'[,...])]
ParametersDescription
<schema name>Name of the schema in which this procedure will be created
<procedure name>Name to give to the created procedure; must adhere to the supported naming criteria for tables, and cannot be named the same as any existing table or view
<sql statements>Semicolon-separated list of supported SQL statements. If the final statement in the procedure is an output-generating statement (SQL query, SHOW command, etc.), the output of that statement alone will be returned to the user; all other statements that generate output will be ignored.
OR REPLACEDrop any existing procedure with the same name before creating this one
LANGUAGEOptional language specification for the procedure. Only SQL is supported at this time.
<number>Length of time, in the given number of units, between scheduled executions of the procedure. Fractional values are accepted.
WITH OPTIONS

Optional indicator that a comma-delimited list of option/value assignments will follow. The following options are available:

OptionDescription
EXECUTE AS

Executes the stored procedure as the given user with that user's privileges.

Note

If this user doesn't exist at the time of execution, the stored procedure will be executed as the creating user, and, failing that, the system administration user.

For example, to create a sqlp procedure:

CREATE PROCEDURE Example
1
2
3
4
5
6
7
8
9
CREATE PROCEDURE example.sqlp
BEGIN
  CREATE OR REPLACE TABLE example.sqlp_table_tmp AS
  SELECT *
  FROM example.sqlp_table_ref;

  SELECT *
  FROM example.sqlp_table_tmp;
END

To create a sqlp_weekly procedure that executes once per week:

CREATE PROCEDURE (Scheduled) Example
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE PROCEDURE example.sqlp_weekly
BEGIN
  CREATE OR REPLACE TABLE example.sqlp_table_tmp AS
  SELECT *
  FROM example.sqlp_table_ref;

  SELECT *
  FROM example.sqlp_table_tmp;
END 
EXECUTE FOR EVERY 7 DAYS
STARTING AT '2019-01-01 00:00:00'

ALTER PROCEDURE

The following facet of a view can be altered:

Set Execution User

A stored procedure can have its execution user modified.

Set Procedure Execution User Syntax
1
2
ALTER PROCEDURE [<schema name>.]<procedure name>
SET EXECUTE AS '<user name>'

For example, to set the execution user of the sqlp_ea procedure to spuser:

ALTER PROCEDURE SET EXECUTE AS Example
1
2
ALTER PROCEDURE example.sqlp_ea
SET EXECUTE AS 'spuser'

EXECUTE PROCEDURE

SQL procedures can be executed on-demand.

EXECUTE PROCEDURE Syntax
1
EXEC[UTE] [PROCEDURE] [<schema name>.]<procedure name> [()]

If the final statement in the procedure is an output-generating statement (SQL query, SHOW command, etc.), the output of that statement alone will be returned to the user; all other statements that generate output will be ignored.

If there is an error executing any statement in the procedure, the procedure will stop immediately and report the error. Any database modifications prior to the error will not be rolled back.

For example, to execute the sqlp procedure:

EXECUTE PROCEDURE Example
1
EXECUTE PROCEDURE example.sqlp

DROP PROCEDURE

When removing a SQL procedure from the database, there are two options available, which control how the removal takes place. Normally, an error will be reported if the table to drop doesn't exist; if IF EXISTS is specified, no error will be reported.

DROP PROCEDURE Syntax
1
DROP PROCEDURE [IF EXISTS] [<schema name>.]<procedure name>

For example, to drop the sqlp procedure:

DROP PROCEDURE Example
1
DROP PROCEDURE example.sqlp

SHOW PROCEDURE

The content of a SQL procedure can be displayed.

SHOW PROCEDURE Syntax
1
SHOW PROCEDURE [<schema name>.]<procedure name>

For example, to show the contents of the sqlp_weekly procedure:

SHOW PROCEDURE Example
1
SHOW PROCEDURE example.sqlp_weekly

Security

Permissions for managing procedures follow those for creating tables; e.g., if a user has the ability to create a table in a given schema, that user will also be able to create & drop procedures there.

Executing a procedure requires either the implicit execute permission that is granted to the creator of a procedure, or explicit execute permission, which can be granted to or revoked from any user or role, irrespective of whether the target user or role has the appropriate access to the database objects referenced within the SQL procedure.

When procedures are executed on-demand, they are run, by default, with invoker rights (caller permissions); while those run automatically by scheduled execution are executed with definer rights (creator permissions). These defaults can be overridden by creating the stored procedure with the EXECUTE AS option, which allows a user to be designated for permissions-checking when running the procedure. The EXECUTE AS user is automatically given EXECUTE permission on the corresponding SQL procedure.

For scheduled execution runs, if the EXECUTE AS user loses EXECUTE permission on the procedure or doesn't have permissions to run the commands within the procedure, the execution will fail accordingly. If the user doesn't exist, the procedure will be executed with definer rights; and if the creator no longer exists, it will be executed as the system adminstration user.

For on-demand runs, if the EXECUTE AS user doesn't have permissions to run the commands within the procedure, the execution will fail accordingly. If the user doesn't exist, the procedure will be executed with invoker rights.

Execute permission on a procedure also allows the grantee to see the contents of the procedure.