Working with Databases

Use the following blocks to work with databases:

• SQL query
• Select rows by SQL query
• Insert rows
• Update rows
• Get table schema
• Create a View
• Create a table
• Remove the table
• Rename the table
• Clear the table

SQL Query

Use the block to create, edit, and delete tables in a database, as well as retrieve information from them.

The required parameters for the SQL query block are the query itself and the database connection (when accessing a database).

Example of deleting data from a table using a query:

1. We are going to use the case_table_en table.
2. Add the SQL query block to the script and write the query in the corresponding field.
3. Test the block and return to the table to verify that the data has been deleted.

Select Rows by SQL Query

The block allows you to create a custom SQL query for a specific package.

Parameters of the block:

• SQL query — SELECT instructions
• Limit — number of rows
• Offset — number of rows to skip
• Order by — sorting order

Insert rows

Use the block to populate and create tables with the output data from preceding blocks.

Required parameters of the block:

• Connection (when accessing a DB)
• Table — can be selected from the list or entered manually
• Columns (name, value)

To automatically fill fields with data from previous blocks, click Autocomplete fields with output data. More details are available in Visual Mapping.

If NULL values appear in the data but the target column does not allow NULL, enable Autoconversion of NULL values. In this case, the column will automatically be set to its default value.

Use the table editor in the left panel to modify, add, and delete columns. Changes affect the workspace tables.

To create a table:

1. Click the Table field and select Add table.
2. Choose Create manually and fill in the fields:
   • Table name
   • Column name
   • Data type
   To add a new column, click + Add column. By clicking Select from output fields, you can add columns based on the output of the preceding blocks.
   • Create from output. When you select this option, you see the list of the columns from the previous blocks. You can choose the columns one by one or check the block and select all its columns automatically
   
3. Click Add to save, or Cancel to discard changes.

You can use visual mapping in the block.

Update rows

The block updates table data using mapping.

Required parameters of the block:

• Connection (when accessing a DB)
• Table
• Column key — the column used to compare values
• Key value — the value of the column key
• New column values

The column key and the key value help locate rows to update. If o matching row is found but you need to add one, activate the Add missing rows switch.

If NULL values appear in the data but the target column does not allow NULL, enable Autoconversion of NULL values. In this case, the column will automatically be set to its default value.

Once a table is selected, you can auto-populate its columns with output data from previous blocks. See more details in Visual Mapping.

Get Table Schema

The block retrieves the name and schema of a specific table from a database.

Specify the table from the database in the block parameters. Select it from the list or enter its name manually.

By default, the output shows the data of the first row by columns, as well as the name and schema of the table.

Create a View

This block creates a view in ClickHouse — a special virtual table that helps simplify complex queries. It works in both standard and cluster ClickHouse modes.

Use this block to:

• Simplify working with data — define a complex SQL query once and then refer to it as if it were a regular table
• Enhance security — views allow you to hide complex business logic and restrict access to sensitive data, exposing only the necessary columns and rows to users
• Ensure compatibility across ClickHouse modes — the block enables you to create workspace templates that function identically in both standard and cluster modes without additional configuration

The block supports two types of views:

• Regular View — a virtual table that does not store data. Each time it is queried, it executes the underlying SQL query and returns its result. This simplifies data access and restricts exposure to underlying tables.
• Materialized view — unlike a regular view, it stores the query result in a separate physical table. This enables fast data retrieval without re-executing the query and simplifies working with complex or frequently used computations.

Block parameters:

• View type:
  • View
  • Materialized View
• Action type:
  • Create — create
  • Create if it does not exist — create if not exists
  • Create or replace (View only) — create or replace
• View name
• Select query — defines the column structure of the view
• Target table — destination table where data will be stored (Materialized View only)
• Comment

Creating a View

Let’s configure a script that generates a sales report for the current month.

First, in a separate script, create a view that returns only data from the current month. This simplifies other scripts — instead of filtering by date each time, they can simply query the view:

1. Add a Create a view block to the script.
2. In the block parameters, select the view type: View.
3. Choose the action type: Create or replace.
4. Set the view name to current_month_sales.
5. In the Select request field, enter the following query:
   SELECT *
   FROM sales
   WHERE toStartOfMonth(sale_date) = toStartOfMonth(today())
   

   
6. Test and publish the script.

Now, configure the Generate Sales Report script, which prepares the sales report for the business team:

1. Add a Select rows by SQL query block to the script.
2. Enter the following query to retrieve current-month data:
   SELECT * FROM current_month_sales
   

   
3. Add two more blocks to the script:
   • Generate CSV — to convert the data into a report
   • Send email — to send the report file to stakeholders
   
4. Test and publish the script. Now, only current-month data will be returned when the query is executed.

Create a Table

The block allows you to create tables in the workspace database. It works in both standard and cluster ClickHouse modes.

Block parameters:

• Action type — choose a query option from the list:
  • Create
  • Create if it does not exist
  • Create or replace
• Table name — enter manually or via mapping
• Columns — create a list of table columns, specifying for each:
  • Column name
  • Data type — select from the list or define manually
• Order by — set the column(s) used for sorting
• Partition by — configure table partitions

The block does not return data.

Advanced Mode

Advanced mode allows you to define the table structure directly via SQL.

Parameters:

• Specify table structure — enable the toggle to specify columns manually
• Column description — define columns and indexes in SQL format
• Select request — enter a query to populate or define the table structure (if Specify table structure is disabled)
• Table engine — select the engine to use. In cluster mode, the replicated version is used automatically.

Table Engine

The supported engines and their additional parameters are listed below.

All MergeTree engines also support the following parameters:

• Order by
• Partition by
• Primary key
• Sample by
• TTL
• Settings — defined as key-value pairs

Settings

To add additional table parameters in the Settings section, click Add and specify the key and value.

Supported parameters:

• allow_nullable_key — allows using Nullable values in primary keys:
  • 0 (default) — disallow
  • 1 — allow
• allow_experimental_json_type:
  • 0 — disabled
  • 1 — enable JSON type support
• log_queries:
  • 0 — disabled
  • 1 — enable query logging for the table

Example: Creating a Table

1. Select the action type Create if it does not exist.
2. Insert the table name into the field using mapping.
3. Add the required columns and specify their names and data types.
4. In Order by, set the column to sort by.
5. Test the block.
6. If successful, the Test tab shows a confirmation message.

Example: Creating a Table with Settings

1. Select the action type Create.
2. Set the table name to users.
3. Enable Advanced mode.
4. Enable Specify table structure. This allows you to manually define columns and indexes in SQL format.
5. In Column description, specify the table structure:
   user_id Nullable(UInt32),
   username String,
   registration_date Date
   

6. Leave Select query empty.
7. Choose the table engine — MergeTree.
8. Set Order by to user_id.
9. Go to Settings, click Add, and configure the parameter:
   • Key — allow_nullable_key
   • Value — 1
   This allows using the user_id column (Nullable) as a sorting key.
10. Test the block.
11. The Test tab confirms successful execution.

• Now the user_id column may contain NULL values, even as part of the primary key
• ClickHouse correctly indexes and sorts data that includes NULL values
• Queries with NULL conditions in the primary key are supported

Remove the Table

Use this block to delete selected tables from the workspace database when working in cluster mode.

In the block parameters, use visual mapping to specify the table that needs to be removed.

This block does not Return data.

Rename the Table

Use this block to rename tables in the workspace database when working in cluster mode.

Parameters:

• Table (specify using mapping)
• New name

This block does not Return data.

Clear the Table

Use this block to clear the data from selected tables in the workspace database when working in cluster mode.

In the block parameters, use visual mapping to specify the table to clear.

This block does not return data.

Session Mode

All blocks in the Workspace and ClickHouse packages support session mode.
This mode allows you to send queries within a ClickHouse session and enables working with temporary tables and parameters.

By default, the session mode is disabled. To activate it, set the switch to On. When enabled, the session is activated for the time specified in the connection settings.

Session duration for ClickHouse blocks is configured when creating a connection in the workspace.

Session duration for Workspace blocks is configured in the data storage settings.

Blocks that support session mode:

• SQL query
• Insert rows
• Select rows by SQL query
• Update rows
• Get table schema