Code Blocks

CoginitiScript was designed to encourage good software engineering practices. One of the guiding principles of high-quality software is the DRY principle - Don’t Repeat Yourself, which basically means that you should never write the same code twice. You should reuse and build upon existing code as much as possible.

“Code Blocks” are the most basic building blocks that allow code reuse. Packages (see below) are the next step into code reusability. They help you organize related CoginitiScript source files together into a single unit, making them modular, reusable, and maintainable.

 

Block Definition

SQL block which calculates total sales amount per each store:

#+src sql SalesPerStore()
#+begin
  SELECT
    s.name AS store_name,
    SUM(s.quantity * s.price) AS total_sales_amount
  FROM
    fact_sales AS s
  INNER JOIN dim_store AS s
    ON s.id = s.store_id
  GROUP BY
    s.name
#+end

To invoke block of code from another block, reference it via ${block-name(args)} in a context where you would normally reference a database table.

SELECT * FROM {{ SalesPerStore() }} WHERE store_name = 'Main Store';

 

Block Execution

When you run whole script anonymous blocks executed immediately. Execution of named block happens when they are referenced using ${block-name(args)} syntax.

Following block will be executed immediately.

#+src sql
#+begin
  SELECT 1;
#+end

But this block will not be executed until you call it via ${selectOne()}.

#+src sql selectOne()
#+begin
  SELECT 1;
#+end

SELECT * FROM {{ selectOne() }}; -- this is a line where actual execution of block happens.

When your cursor inside the block and you execute it by run at cursor the block is executed, even if it is named. It is done for better development experience, because when you are working on a certain block it is handy to run it in this way and see the result.

 

Block Formal Parameters

Blocks can take zero or many parameters. The following example is a basic block which takes a country and email domain parameter and returns a customers which has given email domain and live in the given country.

#+src sql GetCustomerByEmail(country, domain)
#+begin
  SELECT
    *
  FROM
    dim_customers
  WHERE
    country = '{{ country }}'
    AND email LIKE '%{{ domain }}'
#+end

When you call a block you can pass values to block formal parameters as positional arguments:

SELECT * FROM {{ GetCustomerByEmail("USA", "gmail.com") }};

Or you can pass values as named arguments:

SELECT * FROM {{ GetCustomerByEmail(country="USA", domain="gmail.com") }}

The order of these arguments matters when they’are passed positionally but it doesn’t matter when they’re passed by their name, since it’s the name that matters, not the position:

SELECT * FROM {{ GetCustomerByEmail(domain="gmail.com", country="USA") }}

Note that you can’t mix positional and named arguments in a single block call.

 

Block Return Type

When you define a block you can optionally specify a type it returns. Available options: “recordset”, “void”. By default “recordset” is used.

Recordset

SQL blocks with recordset return type execute an arbitrary list of SQL statements, returning the result of the last query in the list which must be a SELECT or WITH statement.

Void

If you want to define a SQL block that performs actions without returning any values, you can define it as returning void. For example, this block generates a table customers with random data:

#+src sql GenCustomersTable(cnt, minAge, maxAge): void
#+meta {
  :doc "Generate customers table",
}
#+begin
  DROP TABLE IF EXISTS customers;
  CREATE TABLE customers(
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
  );

  INSERT INTO customers
  WITH tmp AS(
    SELECT
      generate_series AS id,
      FLOOR(RANDOM() * 5)::INTEGER + 1 AS idx,
      FLOOR(RANDOM() * ({{ maxAge }} - {{ minAge }} + 1))::INTEGER + {{ minAge }} AS age
    FROM
      generate_series(1, {{ cnt }})
  )
  SELECT
    id,
    (SELECT name FROM names WHERE id = idx) AS name,
    age
  FROM
    tmp
#+end

You can call given block using our regular syntax: 

{{ GenCustomersTable(100, 18, 80) }}

 

What happens under the hood

When CoginitiScript execute a block which has been called from another block it creates a temporary table with results of the last SELECT statement from the block and then use the name of that temporary table in a place where block has been referenced.

For example, given CoginitiScript code:

#+src sql selectOne()
#+begin
  SELECT 1 AS one;
#+end

SELECT * FROM {{ selectOne() }};

Will be executed on database platform as following SQL script:

CREATE TEMPORARY TABLE _random_name_ AS
SELECT 1 AS one;

SELECT * FROM _random_name_;

Please note, that your block SQL code is not limited to just single SELECT statement. It could have complex scripts with several statements, with steps where you create your own temporary tables, etc. The only requirement that you should follow if you want to reference this block is to have SELECT statement which produces result as your last statement in the block SQL.
#+src sql foo()
#+begin
  DROP TABLE IF EXIST tmp_table_name;
  CREATE TEMPORARY TABLE tmp_table_name AS
  SELECT * FROM ...;

  -- any other transformation logic

  -- SELECT statement to produce final results
  SELECT * FROM ...
#+end

SELECT * FROM {{ foo() }} WHERE name = 'foo';
In future versions CoginitiScript might not use temporary tables for all cases when block is referenced. For blocks which has single SELECT statements in it, it might just embed it as a subquery into the block from which it has been referenced.
 

Limitations

In the current version of CoginitiScript it has following limitations:

  • BigQuery platform is not supported (support will be added in next release)
  • Database user used to execute CoginitiScript should have permissions to create temporary tables in the given database / schema.
  • On Snowflake platform it is required to have public schema on the given database with permissions for user to create temporary tables in it.
  • On Snowflake platform is it required to have explicit column aliases specified on SELECT statement in block which is referenced from other places due to limitation of the platform itself which is not allow unnamed columns in queries when creating temporary tables.
  • Explain plan cannot be executed for statement with block reference
  • Named block cannot be called from execute to file statement
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request