Query Result To Grid
    • Dark
      Light

    Query Result To Grid

    • Dark
      Light

    Article Summary

    Query Result To Grid

    The Query Result To Grid component empowers users to query a table for data that is then loaded into a grid variable for use elsewhere. The component features both basic and advanced modes that allow users to set up a simple query via the component interface or to write their own SQL queries.

    This component is the grid equivalent of the Query Result To Scalar component.

    Note

    Do not end SQL statements with a semicolon in this component.

    Properties

    Snowflake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic/Advanced ModeSelectBasic: This mode will build a query for you using settings from the component's properties.
    Advanced: This mode will require you to write an SQL-like query, which is translated into one or more API calls.
    SQL QueryStringInput an SQL-like query, written according to the profile definition.
    This property is only available in "Advanced" Mode.
    DatabaseSelectChoose a Snowflake database from which to find the table to be queried.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
    Target TableStringSelect the table to be queried.
    Table ColumnsSelectSelect which columns to take from the table as part of the query.
    Order BySelectChoose 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.
    Note: An update to Snowflake has resulted in a change in the behaviour of this parameter. The LIMIT 5000 clause (which the Advanced Mode parameter automatically wraps the query in) no longer guarantees that the order specified by the ORDER BY in the sub-query is preserved.
    SortSelectSelect whether rows are sorted in Ascending or Descending order.
    LimitIntegerSet the limit of returned rows.
    Grid VariableSelectChoose the grid variable to be loaded with data resulting from the query.
    Grid Variable MappingSelectMap columns from the queried table to the columns of the grid variable.
    Filter ConditionsInput Column NameThe name of the input column.
    QualifierIs: 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.
    ComparatorSelect the comparator. Available comparators include:
    1. Less than
    2. Less than or equal to
    3. Equal to
    4. Greater than or equal to
    5. Greater than
    6. Null
    7. Blank
    8. Null or Blank
    9. Like
    "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" comparator allows the wildcard character % to be used at the start and end of a string value to match a column. The "Null" comparator matches only null values, ignoring whatever the value is set to.
    ValueSpecify the value to be compared.
    Combine FiltersSelectAND: all filters must be true.
    OR: at least one filter must be true.

    Redshift Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic/Advanced ModeSelectBasic: This mode will build a query for you using settings from the component's properties.
    Advanced: This mode will require you to write an SQL-like query, which is translated into one or more API calls.
    SQL QueryStringInput an SQL-like query, written according to the profile definition.
    This property is only available in "Advanced" Mode.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, see this article.
    Table NameStringSelect the table to be queried.
    Table ColumnsSelectSelect which columns to take from the table as part of the query.
    Order BySelectChoose the column(s) by which rows are to be sorted. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
    SortSelectSelect whether rows are sorted in Ascending or Descending order.
    LimitIntegerSet the limit of returned rows.
    Grid VariableSelectChoose the grid variable to be loaded with data resulting from the query.
    Grid Variable MappingSelectMap columns from the queried table to the columns of the grid variable.
    Filter ConditionsInput Column NameThe name of the input column.
    QualifierIs: 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.
    ComparatorSelect the comparator. Available comparators include:
    1. Less than
    2. Less than or equal to
    3. Equal to
    4. Greater than or equal to
    5. Greater than
    6. Null
    7. Blank
    8. Null or Blank
    9. Like
    "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" comparator allows the wildcard character % to be used at the start and end of a string value to match a column. The "Null" comparator matches only null values, ignoring whatever the value is set to.
    ValueSpecify the value to be compared.
    Combine FiltersSelectAND: all filters must be true.
    OR: at least one filter must be true.

    BigQuery Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic/Advanced ModeSelectBasic: This mode will build a query for you using settings from the component's properties.
    Advanced: This mode will require you to write an SQL-like query, which is translated into one or more API calls.
    SQL QueryStringInput an SQL-like query, written according to the profile definition.
    This property is only available in "Advanced" Mode.
    ProjectSelectSelect the target BigQuery project to load data from.
    DatasetSelectSelect the target BigQuery dataset to load data from.
    TableStringSelect the table to be queried.
    Table ColumnsSelectSelect which columns to take from the table as part of the query.
    Order BySelectChoose the column(s) by which rows are to be sorted. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
    SortSelectSelect whether rows are sorted in Ascending or Descending order.
    LimitIntegerSet the limit of returned rows.
    Grid VariableSelectChoose the grid variable to be loaded with data resulting from the query.
    Grid Variable MappingSelectMap columns from the queried table to the columns of the grid variable.
    Filter ConditionsInput Column NameThe name of the input column.
    QualifierIs: 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.
    ComparatorSelect the comparator. Available comparators include:
    1. Less than
    2. Less than or equal to
    3. Equal to
    4. Greater than or equal to
    5. Greater than
    6. Null
    7. Blank
    8. Null or Blank
    9. Like
    "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" comparator allows the wildcard character % to be used at the start and end of a string value to match a column. The "Null" comparator matches only null values, ignoring whatever the value is set to.
    ValueSpecify the value to be compared.
    Combine FiltersSelectAND: all filters must be true.
    OR: at least one filter must be true.

    Synapse Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Basic/Advanced ModeSelectBasic: This mode will build a query for you using settings from the component's properties.
    Advanced: This mode will require you to write an SQL-like query, which is translated into one or more API calls.
    SQL QueryStringInput an SQL-like query, written according to the profile definition.
    This property is only available in Advanced Mode.
    SchemaSelectSelect the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, please see the Azure Synapse documentation.
    TableSelectSelect the table to be queried.
    Table ColumnsColumn SelectSelect which columns to take from the table for the query.
    Order BySelectChoose the column(s) by which rows are to be sorted. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
    SortSelectSelect whether rows are sorted in Ascending or Descending order.
    LimitIntegerSpecify the maximum number of rows returned.
    Grid VariableSelectSelect the grid variable to be loaded with data resulting from the query.
    Grid Variable MappingMultiple SelectMap columns from the queried table to the columns of the grid variable.
    Filter ConditionsInput Column NameThe name of the input column.
    QualifierIs: 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.
    ComparatorSelect the comparator. Available comparators include:
    1. Less than
    2. Less than or equal to
    3. Equal to
    4. Greater than or equal to
    5. Greater than
    6. Null
    7. Blank
    8. Null or Blank
    9. Like
    "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" comparator allows the wildcard character % to be used at the start and end of a string value to match a column. The "Null" comparator matches only null values, ignoring whatever the value is set to.
    ValueSpecify the value to be compared.
    Combine FiltersSelectAND: all filters must be true.
    OR: at least one filter must be true.

    Delta Lake Properties

    PropertySettingDescription
    Basic / AdvancedSelectBasic: This mode will create a query for you based on the settings you assign to the component's properties.
    Advanced: This mode will require you to write an SQL-like query, which is translated into one or more API calls. This mode hides many of the component's properties.
    SQL QuerySQLInput an SQL-like query, written according to the profile definition. This property is only available when Basic/Advanced is set to Advanced.
    CatalogSelectSelect 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.
    DatabaseSelectSelect the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
    TableSelectSelect the table for data that will then be loaded into a grid variable. The available tables depends on the selected database.
    Table ColumnsColumn SelectorSelect which of the chosen table's columns will be included in the query. Move columns to the right-hand side to include them.
    Order BySelectChoose the column(s) by which rows are to be sorted. If multiple columns are selected, rows are sorted by the first-listed column first, then by the next listed column, and so on.
    SortSelectSelect whether rows are sorted in Ascending or Descending order.
    LimitIntegerSet the limit on the number of returned rows.
    Grid VariableSelectSelect an existing grid variable onto which the data from the query will be loaded. To learn more about grid variables, including their usage and how to create one, read Grid Variables.
    Grid Variable MappingSelectFor each column created for the grid variable, assign it a corresponding column from the selected table. We advise that grid variable columns are named logically for ease of mapping columns from the queried table to the grid variable.
    Filter ConditionsInput Column NameThe name of the input column.
    QualifierIs: 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.
    ComparatorSelect the comparator. Available comparators include:
    • Less than
    • Less than or equal to
    • Equal to
    • Greater than or equal to
    • Greater than
    • Null
    • Blank
    • Null or Blank
    • Like
    • Similar to

    "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" comparator allows the wildcard character % to be used at the start and the end of a string value to match a column. The "Null" comparator matches only null values, ignoring whatever the value is set to.
    ValueSpecify the value to be compared.
    Combine FiltersSelectAND: all filter conditions must be true.
    OR: at least one filter condition must be true.