Execute SQL commands directly in your database, data warehouse, etc.
SQL
.Field | Required | Description |
---|---|---|
Data provider | Yes | The database or data warehouse you want to execute your SQL commands in. |
Profile | Yes | When you add your credentials to the io_config.yaml file, you added them under a key. That key is called the profile. Choose which set of credentials you want this SQL block to use. |
Use raw SQL | No | You can write raw SQL and it’ll be executed as written in your data provider. |
Database | Depends on data provider | Some data warehouses require that we explicitly state the name of the database we want to write to. If this is present, it’s required. The name of the table that is created follows this convention: [database].[schema].[pipeline UUID]_[block UUID] . |
Schema to save | Yes | Every SQL block will save data to your data provider. The name of the table that is created follows this convention: [schema].[pipeline UUID]_[block UUID] . |
Table | No | SQL blocks will automatically name your table for you using a naming convention (see the section Automatic naming of tables for more information). You can override this automatic naming convention by filling in a value in this field. |
Write policy | Yes | How do you want to handle existing data with the same database, schema, and table name? See below for more information. |
Policy | Description |
---|---|
Append | Add rows to the existing table. |
Replace | Delete the existing data. |
Fail | Raise an error during execution. |
metadata.yaml
file. Each block has a configuration
field.
Example configuration
Field | Required | Description |
---|---|---|
limit | No | The maximum number of rows to return in notebook. |
limit_in_pipeline_run | No | The maximum number of rows to return when running the block in the pipeline run. |
0.9.69
or less and interpolating a SQL command from an upstream block that uses raw SQL:
if the downstream block’s SQL command is referencing the upstream block using a variable such as {{ df_1 }}
and ends the statement with a semi-colon (;
), there is an interpolation error.
To work around the error, remove the semi-colon ;
.
A fix will be available in 0.9.70
.CREATE TABLE
command and the
INSERT
command.
For example, if a table already exists then you can write the INSERT
statement:
CREATE TABLE
statement
and the INSERT
statement:
mage.users
with 2 columns: id
as a BIGINT
and username
as a VARCHAR(255)
.
Then, it’ll insert a single row into that table.
SELECT
INSERT
CREATE TABLE
DROP TABLE
UPDATE
;
).
Database
field is configured:
[database].[schema].[pipeline UUID]_[block UUID]
Database
field is configured: [schema].[pipeline UUID]_[block UUID]
{{ execution_date }}
today |
---|
2022-09-24 23:01:08.376057 |
{{ df_1 }}
{{ df_1 }}
{{ df_2 }}
{{ df_3 }}
{{ df_1 }}
, and every
upstream block added after that will have an incrementing number in the variable
name after the prefix df_
.
Example
id | username |
---|---|
1 | Sorcerer supreme |
0.9.59
block_output
function.
block_uuid
Optional | ✅ |
Type | str |
Example | 'data_loader_block' |
parse
parse
argument isn’t present, then the fetched data from the upstream block’s output
will be interpolated as is.
Optional | ✅ |
Type | function |
Example | lambda data, variables: data['runtime'] * variables['tries'] |
data
If the block_uuid
argument isn’t present, then the 1st argument in the lambda function is
a list of objects.
The list of objects contain the data from an upstream block’s output.
The positional order of the data in the list corresponds to the current block’s upstream blocks
order.
For example, if the current block has the following upstream blocks with the following output:
load_api_data
: [1, 2, 3]
load_users_data
: { 'mage': 'powerful' }
Optional | ❌ |
Type | If block_uuid argument is present, then the type depends on the output from that block. If block_uuid isn’t present, then the type is list . |
Example | { 'mage': 'powerful' } |
variables
A dictionary containing pipeline variables and runtime variables.
Optional | ❌ |
Type | dict |
Example | { 'fire': 40 } |
block_uuid
block_uuid
0.9.59