Modules

In GoogleSQL for SecOps, a module is a collection of reusable data definition language (DDL) statements. The purpose of a module is to keep related logic in one location that you and other users can maintain centrally and reference repeatedly.

Modules have the following key characteristics:

• Modules can't create persistent objects like tables, and they can't use data manipulation language (DML) statements to insert or modify data.
• Each module has its own namespace. This namespace is empty until CREATE statements in the module add objects to the module namespace.
• Modules allow public and private object definitions for proper encapsulation.

GoogleSQL limits the duration and side effects of module objects to the invoking session. In the context of GoogleSQL modules, a session is a set of related statements and objects that form a unit of work.

Create a module

To create a module, you create a file that contains a MODULE statement and subsequent IMPORT and CREATE statements. The file extension .sqlm must appear at the end of the filename of the module file.

Modules support the following statements:

• MODULE
• IMPORT MODULE
• CREATE { PUBLIC | PRIVATE } [ { TABLE | AGGREGATE } ] FUNCTION
• CREATE { PUBLIC | PRIVATE } CONSTANT

Modules don't support statements that return results or have side effects. Modules only support defining an object once and don't support modifying an object after it's defined.

Declare a module

The first statement in a module must be a valid MODULE statement which defines the module name:

MODULE identifier_path [ OPTIONS (...)];

Each module file must contain only one MODULE statement.

By convention, an IMPORT MODULE statement for this module generally includes the same identifier_path in the module file's MODULE statement. This identifier_path should reflect the path to the module file.

For example, if a module file is stored at .../x/y/z.sqlm, then the MODULE statement will be:

MODULE x.y.z;

And the IMPORT statement will be:

IMPORT MODULE x.y.z;

Caveats:

• The IMPORT statement shouldn't include the .sqlm file extension.
• By convention, if you import module x.y.z, GoogleSQL looks for the module at .../x/y/z.sqlm. If the module isn't found, GoogleSQL looks for the module at .../x/y/z/z.sqlm and you can import it with either IMPORT MODULE x.y.z or IMPORT MODULE x.y.z.z.
• If any segment of identifier_path contains special characters, such as hyphens, that segment must be enclosed in backticks (for example, x.y.`my-module`). For more information about identifier syntax, see Lexical structure and syntax.

Create objects within modules

Modules can contain CREATE statements to create objects within the module.

Specify public vs. private objects

All CREATE statements must indicate if the created object is available outside of the module in the importing session (public), or only available internally within the module (private). To specify these properties, use the PUBLIC or PRIVATE modifier in the CREATE statement.

Examples

The following example creates a public function, which the invoking session can execute.

CREATE PUBLIC FUNCTION Foo(a INT64)
AS (
  a + 1
);

The following example creates a private function, which only statements within the same module can execute.

CREATE PRIVATE FUNCTION Bar(b INT64)
AS (
  b - 1
);

Create constants

Modules support the creation of constants.

The TEMP keyword isn't allowed when creating a constant in a module. TEMP objects in a module aren't meaningful, since the lifetime of the object is the lifetime of the module.

Example

Create a constant, DEFAULT_HEIGHT:

CREATE PUBLIC CONSTANT DEFAULT_HEIGHT = 25;

Use it in a statement:

SELECT (DEFAULT_HEIGHT + 5) AS result;

/*--------+
 | result |
 +--------+
 | 30     |
 +--------*/

Create UDFs and TVFs

Modules support creation of UDFs (user-defined functions), including TVFs (table-valued functions) with scalar and templated arguments.

The TEMP keyword isn't allowed in CREATE ( PUBLIC | PRIVATE ) FUNCTION statements in modules. TEMP objects aren't meaningful within a module since the lifetime of the object is the lifetime of the module.

Note that SQL UDFs/TVFs defined in modules can't directly access any database schema tables, and therefore can't rely on the existence of tables in a database. To reference a database table in a module TVF, the table must be passed in as a TVF argument of type ANY TABLE.

Examples

The following example creates a public UDF.

CREATE PUBLIC FUNCTION SampleUdf(a INT64)
AS (
  a + 1
);

The following example creates a public templated UDF with a scalar argument.

CREATE PUBLIC FUNCTION ScalarUdf(a ANY TYPE)
AS (
  a + 1
);

The following example creates a public TVF with a scalar argument using a public UDF defined in the same module.

CREATE PUBLIC TABLE FUNCTION ScalarTvf(a INT64)
AS (
  SELECT a, SampleUdf(a) AS b
);

The following example creates a public TVF with a table argument.

CREATE PUBLIC TABLE FUNCTION ScalarTvf(SomeTable TABLE<a STRING, b INT64>)
AS (
  SELECT a, SUM(b) AS sum_b FROM SomeTable GROUP BY a
);

The following example creates a public templated TVF.

CREATE PUBLIC TABLE FUNCTION TemplatedTvf(a ANY TYPE, SomeTable ANY TABLE)
AS (
  SELECT a, b.* FROM SomeTable
);

Reference module objects from within the same module

Statements in a module can reference other objects in the same module. Statements can reference objects whose CREATE statements appear before or after that referencing statement.

Example

The following example module declares the name of the module, and creates one public function and two private functions. The public function references the other two private functions.

MODULE a.b.c;

CREATE PRIVATE FUNCTION Foo(x INT64)
AS (
  x
);

CREATE PRIVATE FUNCTION Bar(y INT64)
AS (
  y
);

CREATE PUBLIC FUNCTION Baz(a INT64, b INT64)
AS (
  Foo(a) + Bar(b)
);

Object references can't be circular: if a function directly or indirectly references a second function, then that second function can't reference the original function.

Use an existing module

You can use an existing module by importing it into a session or into another module.

Import a module into a session

To import a module into a session, use the IMPORT MODULE statement.

Syntax

IMPORT MODULE identifier_path [AS alias];

This imports a module and creates a namespace visible to the importing session containing public objects exported from the module.

The identifier_path is a unique module name that corresponds to the path ID in the module declaration.

The alias provides the namespace that the IMPORT MODULE statement creates. If alias is absent, then the namespace will be the last name in the identifier_path.

Examples

The following example statement imports the module with the identifier path x.y.z into namespace z:

IMPORT MODULE x.y.z;

The following example statement imports the same module but with the alias some_module into namespace some_module.

IMPORT MODULE x.y.z AS some_module;

Reference module objects from a session

Once you have imported a module into a session, you can reference the public objects in that module from the session. Use the namespace of the module or its alias to reference the objects in the module.

Example

In the following example, the IMPORT statement imports the module with the identifier path x.y.z into namespace z, and then executes a public function Baz from inside of that module.

IMPORT MODULE x.y.z;

SELECT z.Baz(a, b);

If the IMPORT statement includes an alias, then the statement creates the namespace with that alias. Use that alias as the identifier path prefix for the referenced object.

Example

In the following example, the IMPORT statement assigns alias some_module to the module with the identifier path x.y.z, and the SELECT statement executes a public function Baz from inside of that module.

IMPORT MODULE x.y.z AS some_module;

SELECT some_module.Baz(a, b);

Import a module into another module

To import a module into another module, use the same syntax as when importing a module into a session.

• Imports can't be circular. For example, if module1 imports module2, then module2 can't directly or indirectly import module1.
• A module can't import itself.

Reference module objects from another module

Once you have imported a module into another module, you can reference the public objects that the imported module creates. Use the same syntax as in an invoking session.

Example

In the following example, the IMPORT statement imports the module with the identifier path x.y.z, and then creates a function Foo which references the public function Baz from inside of the imported module.

MODULE a.b.c;
IMPORT MODULE x.y.z;

CREATE PUBLIC FUNCTION Foo(d INT64, e INT64)
AS (
  z.Baz(d, e)
);

If the IMPORT statement includes an alias, you can reference objects from the imported module inside the importing module using the alias as the identifier path.

MODULE a.b.c;
IMPORT MODULE x.y.z AS some_module;

CREATE PUBLIC FUNCTION Foo(d INT64, e INT64)
AS (
  some_module.Baz(d, e)
);