Query Result To Scalar
The Query Result To Scalar component enables users to write any custom SQL query that returns a scalar value. This value can then be mapped to an environment variable or job variable for use in other Matillion ETL functions.
This component is the scalar equivalent of the Query Result To Grid component.
Properties
Name = string
A human-readable name for the component.
Basic/Advanced Mode = drop-down
- Basic: This mode will build a query for you using settings from the Data Source, Data Selection, and Data Source Filter parameters. In most cases, this mode will be sufficient.
- Advanced: This mode will require you to write an SQL-like query to call data from Snowflake. The available fields and their descriptions are documented in the data model.
Database = drop-down
The Snowflake database. The special value, [Environment Default], will use the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.
Schema = drop-down
The Snowflake schema. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.
Table Name = string
The name of the table to be queried.
Table Columns = dual listbox
Select which columns to take from the table as part of the query.
Order By = dual listbox
Choose the columns by which to sort rows. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
Sort = drop-down
Select whether rows are sorted in Ascending or Descending order.
Limit = integer
Set the limit of returned rows.
Filter Conditions = column editor
- Input Column Name: Select an input column. The available input columns vary depending upon the data source.
- Qualifier:
- Is: Compares the column to the value using the comparator.
- Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
- Comparator: Choose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null". "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than" and "Less than", will work only with numerics. The "Like" operator allows the wildcard character [%] to be used at the start and end of a string value to match a column. The Null operator matches only null values, ignoring whatever the value is set to. Not all data sources support all comparators, meaning that it is likely that only a subset of the above comparators will be available to choose from.
- Value: The value to be compared.
Combine Conditions = drop-down
Select whether to use the defined filters in combination with one another according to either And or Or.
SQL Query = code editor
This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both environment and job variables are also listed in the bottom-left.
SQL queries can be written in the main panel and tested using the Sample button, which will display results below.
This property is only available when Basic/Advanced Mode is set to Advanced.
Warning
Do not end SQL statements with a semicolon in this component.
Scalar Variable Mapping = column editor
Scalar results from the SQL query can be mapped to environment and job variables.
Returned scalars are available from the Input Column Name dropdown, and can be mapped to an environment or job variable in the Scalar Variable Name drop-down.
Name = string
A human-readable name for the component.
Basic/Advanced Mode = drop-down
- Basic: This mode will build a query for you using settings from the Data Source, Data Selection, and Data Source Filter parameters. In most cases, this mode will be sufficient.
- Advanced: This mode will require you to write an SQL-like query to call data from Delta Lake. The available fields and their descriptions are documented in the data model.
Catalog = drop-down
Select a Databricks Unity Catalog. The special value, [Environment Default], will use the catalog specified in the Matillion ETL environment setup. Selecting a catalog will determine which databases are available in the next parameter.
Database = drop-down
Select the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
Table = string
The name of the table to be queried.
Table Columns = dual listbox
Select which columns to take from the table as part of the query.
Order By = dual listbox
Choose the columns by which to sort rows. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
Sort = drop-down
Select whether rows are sorted in Ascending or Descending order.
Limit = integer
Set the limit of returned rows.
Filter Conditions = column editor
- Input Column Name: Select an input column. The available input columns vary depending upon the data source.
- Qualifier:
- Is: Compares the column to the value using the comparator.
- Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
- Comparator: Choose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null". "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than" and "Less than", will work only with numerics. The "Like" operator allows the wildcard character [%] to be used at the start and end of a string value to match a column. The Null operator matches only null values, ignoring whatever the value is set to. Not all data sources support all comparators, meaning that it is likely that only a subset of the above comparators will be available to choose from.
- Value: The value to be compared.
Combine Conditions = drop-down
Select whether to use the defined filters in combination with one another according to either And or Or.
SQL Query = code editor
This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both environment and job variables are also listed in the bottom-left.
SQL queries can be written in the main panel and tested using the Sample button, which will display results below.
This property is only available when Basic/Advanced Mode is set to Advanced.
Warning
Do not end SQL statements with a semicolon in this component.
Scalar Variable Mapping = column editor
Scalar results from the SQL query can be mapped to environment and job variables.
Returned scalars are available from the Input Column Name dropdown, and can be mapped to an environment or job variable in the Scalar Variable Name drop-down.
Name = string
A human-readable name for the component.
Basic/Advanced Mode = drop-down
- Basic: This mode will build a query for you using settings from the Data Source, Data Selection, and Data Source Filter parameters. In most cases, this mode will be sufficient.
- Advanced: This mode will require you to write an SQL-like query to call data from Redshift. The available fields and their descriptions are documented in the data model.
Schema = drop-down
Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, read Schemas.
Table Name = string
The name of the table to be queried.
Table Columns = dual listbox
Select which columns to take from the table as part of the query.
Order By = dual listbox
Choose the columns by which to sort rows. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
Sort = drop-down
Select whether rows are sorted in Ascending or Descending order.
Limit = integer
Set the limit of returned rows.
Filter Conditions = column editor
- Input Column Name: Select an input column. The available input columns vary depending upon the data source.
- Qualifier:
- Is: Compares the column to the value using the comparator.
- Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
- Comparator: Choose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null". "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than" and "Less than", will work only with numerics. The "Like" operator allows the wildcard character [%] to be used at the start and end of a string value to match a column. The Null operator matches only null values, ignoring whatever the value is set to. Not all data sources support all comparators, meaning that it is likely that only a subset of the above comparators will be available to choose from.
- Value: The value to be compared.
Combine Conditions = drop-down
Select whether to use the defined filters in combination with one another according to either And or Or.
SQL Query = code editor
This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both environment and job variables are also listed in the bottom-left.
SQL queries can be written in the main panel and tested using the Sample button, which will display results below.
This property is only available when Basic/Advanced Mode is set to Advanced.
Warning
Do not end SQL statements with a semicolon in this component.
Scalar Variable Mapping = column editor
Scalar results from the SQL query can be mapped to environment and job variables.
Returned scalars are available from the Input Column Name dropdown, and can be mapped to an environment or job variable in the Scalar Variable Name drop-down.
Name = string
A human-readable name for the component.
Basic/Advanced Mode = drop-down
- Basic: This mode will build a query for you using settings from the Data Source, Data Selection, and Data Source Filter parameters. In most cases, this mode will be sufficient.
- Advanced: This mode will require you to write an SQL-like query to call data from BigQuery. The available fields and their descriptions are documented in the data model.
Project = drop-down
Select the Google Cloud project. The special value, [Environment Default], will use the project defined in the environment. For more information, read Creating and managing projects.
Dataset = drop-down
Select the Google BigQuery dataset to load data into. The special value, [Environment Default], will use the dataset defined in the environment. For more information, read Introduction to datasets.
Table = string
The name of the table to be queried.
Table Columns = dual listbox
Select which columns to take from the table as part of the query.
Order By = dual listbox
Choose the columns by which to sort rows. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
Sort = drop-down
Select whether rows are sorted in Ascending or Descending order.
Limit = integer
Set the limit of returned rows.
Filter Conditions = column editor
- Input Column Name: Select an input column. The available input columns vary depending upon the data source.
- Qualifier:
- Is: Compares the column to the value using the comparator.
- Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
- Comparator: Choose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null". "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than" and "Less than", will work only with numerics. The "Like" operator allows the wildcard character [%] to be used at the start and end of a string value to match a column. The Null operator matches only null values, ignoring whatever the value is set to. Not all data sources support all comparators, meaning that it is likely that only a subset of the above comparators will be available to choose from.
- Value: The value to be compared.
Combine Conditions = drop-down
Select whether to use the defined filters in combination with one another according to either And or Or.
SQL Query = code editor
This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both environment and job variables are also listed in the bottom-left.
SQL queries can be written in the main panel and tested using the Sample button, which will display results below.
This property is only available when Basic/Advanced Mode is set to Advanced.
Warning
Do not end SQL statements with a semicolon in this component.
Scalar Variable Mapping = column editor
Scalar results from the SQL query can be mapped to environment and job variables.
Returned scalars are available from the Input Column Name dropdown, and can be mapped to an environment or job variable in the Scalar Variable Name drop-down.
Name = string
A human-readable name for the component.
Basic/Advanced Mode = drop-down
- Basic: This mode will build a query for you using settings from the Data Source, Data Selection, and Data Source Filter parameters. In most cases, this mode will be sufficient.
- Advanced: This mode will require you to write an SQL-like query to call data from Synapse. The available fields and their descriptions are documented in the data model.
Schema = drop-down
Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, read the Azure Synapse documentation.
Table = string
The name of the table to be queried.
Table Columns = dual listbox
Select which columns to take from the table as part of the query.
Order By = dual listbox
Choose the columns by which to sort rows. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
Sort = drop-down
Select whether rows are sorted in Ascending or Descending order.
Limit = integer
Set the limit of returned rows.
Filter Conditions = column editor
- Input Column Name: Select an input column. The available input columns vary depending upon the data source.
- Qualifier:
- Is: Compares the column to the value using the comparator.
- Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
- Comparator: Choose a method of comparing the column to the value. Possible comparators include: "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Like", "Null". "Equal to" can match exact strings and numeric values, while other comparators, such as "Greater than" and "Less than", will work only with numerics. The "Like" operator allows the wildcard character [%] to be used at the start and end of a string value to match a column. The Null operator matches only null values, ignoring whatever the value is set to. Not all data sources support all comparators, meaning that it is likely that only a subset of the above comparators will be available to choose from.
- Value: The value to be compared.
Combine Conditions = drop-down
Select whether to use the defined filters in combination with one another according to either And or Or.
SQL Query = code editor
This property opens an editor. On the left, users can explore tables and their metadata from environments currently recognised by the Matillion ETL client. Both environment and job variables are also listed in the bottom-left.
SQL queries can be written in the main panel and tested using the Sample button, which will display results below.
This property is only available when Basic/Advanced Mode is set to Advanced.
Warning
Do not end SQL statements with a semicolon in this component.
Scalar Variable Mapping = column editor
Scalar results from the SQL query can be mapped to environment and job variables.
Returned scalars are available from the Input Column Name dropdown, and can be mapped to an environment or job variable in the Scalar Variable Name drop-down.
| Snowflake | Delta Lake on Databricks | Amazon Redshift | Google BigQuery | Azure Synapse Analytics |
|---|---|---|---|---|
| ✅ | ✅ | ✅ | ✅ | ✅ |