Skip to content

Training View

Experimental

The Workbench View classes are currently in experimental mode so have fun but expect issues and API changes going forward.

TrainingView Class: A View with an additional training column that marks holdout ids

TrainingView

Bases: CreateView

TrainingView Class: A View with an additional training column (80/20 or holdout ids). The TrainingView class creates a SQL view that includes all columns from the source table along with an additional boolean column named "training". This view can also include a SQL filter expression to filter the rows included in the view.

Common Usage 
# Create a default TrainingView
fs = FeatureSet("test_features")
training_view = TrainingView.create(fs)
df = training_view.pull_dataframe()

# Create a TrainingView with a specific filter expression
training_view = TrainingView.create(fs, id_column="auto_id", filter_expression="age > 30")
df = training_view.pull_dataframe()

# Query the view
df = training_view.query(f"SELECT * FROM {training_view.table} where training = TRUE")
Source code in src/workbench/core/views/training_view.py 
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
class TrainingView(CreateView):
    """TrainingView Class: A View with an additional training column (80/20 or holdout ids).
    The TrainingView class creates a SQL view that includes all columns from the source table
    along with an additional boolean column named "training". This view can also include
    a SQL filter expression to filter the rows included in the view.


    Common Usage:
        ```python
        # Create a default TrainingView
        fs = FeatureSet("test_features")
        training_view = TrainingView.create(fs)
        df = training_view.pull_dataframe()

        # Create a TrainingView with a specific filter expression
        training_view = TrainingView.create(fs, id_column="auto_id", filter_expression="age > 30")
        df = training_view.pull_dataframe()

        # Query the view
        df = training_view.query(f"SELECT * FROM {training_view.table} where training = TRUE")
        ```
    """

    @classmethod
    def create(
        cls,
        feature_set: FeatureSet,
        *,  # Enforce keyword arguments after feature_set
        id_column: str = None,
        holdout_ids: Union[list[str], list[int], None] = None,
        filter_expression: str = None,
        source_table: str = None,
    ) -> Union[View, None]:
        """Factory method to create and return a TrainingView instance.

        Args:
            feature_set (FeatureSet): A FeatureSet object
            id_column (str, optional): The name of the id column. Defaults to None.
            holdout_ids (Union[list[str], list[int], None], optional): A list of holdout ids. Defaults to None.
            filter_expression (str, optional): SQL filter expression (e.g., "age > 25 AND status = 'active'").
                                               Defaults to None.
            source_table (str, optional): The table/view to create the view from. Defaults to None.

        Returns:
            Union[View, None]: The created View object (or None if failed to create the view)
        """
        # Instantiate the TrainingView with "training" as the view name
        instance = cls("training", feature_set, source_table)

        # Drop any columns generated from AWS
        aws_cols = ["write_time", "api_invocation_time", "is_deleted", "event_time"]
        source_table_columns = get_column_list(instance.data_source, instance.source_table)
        column_list = [col for col in source_table_columns if col not in aws_cols]

        # Sanity check on the id column
        if not id_column:
            instance.log.important("No id column specified, we'll try the auto_id_column ..")
            if not instance.auto_id_column:
                instance.log.error("No id column specified and no auto_id_column found, aborting ..")
                return None
            else:
                if instance.auto_id_column not in column_list:
                    instance.log.error(
                        f"Auto id column {instance.auto_id_column} not found in column list, aborting .."
                    )
                    return None
                else:
                    id_column = instance.auto_id_column

        # Enclose each column name in double quotes
        sql_columns = ", ".join([f'"{column}"' for column in column_list])

        # Build the training assignment logic
        if holdout_ids:
            # Format the list of holdout ids for SQL IN clause
            if all(isinstance(id, str) for id in holdout_ids):
                formatted_holdout_ids = ", ".join(f"'{id}'" for id in holdout_ids)
            else:
                formatted_holdout_ids = ", ".join(map(str, holdout_ids))

            training_logic = f"""CASE
                WHEN {id_column} IN ({formatted_holdout_ids}) THEN False
                ELSE True
            END AS training"""
        else:
            # Default 80/20 split using modulo
            training_logic = f"""CASE
                WHEN MOD(ROW_NUMBER() OVER (ORDER BY {id_column}), 10) < 8 THEN True
                ELSE False
            END AS training"""

        # Build WHERE clause if filter_expression is provided
        where_clause = f"\nWHERE {filter_expression}" if filter_expression else ""

        # Construct the CREATE VIEW query
        create_view_query = f"""
        CREATE OR REPLACE VIEW {instance.table} AS
        SELECT {sql_columns}, {training_logic}
        FROM {instance.source_table}{where_clause}
        """

        # Execute the CREATE VIEW query
        instance.data_source.execute_statement(create_view_query)

        # Return the View
        return View(instance.data_source, instance.view_name, auto_create_view=False)

    @classmethod
    def create_with_sql(
        cls,
        feature_set: FeatureSet,
        *,
        sql_query: str,
        id_column: str = None,
    ) -> Union[View, None]:
        """Factory method to create a TrainingView from a custom SQL query.

        This method takes a complete SQL query and adds the default 80/20 training split.
        Use this when you need complex queries like UNION ALL for oversampling.

        Args:
            feature_set (FeatureSet): A FeatureSet object
            sql_query (str): Complete SELECT query (without the final semicolon)
            id_column (str, optional): The name of the id column for training split. Defaults to None.

        Returns:
            Union[View, None]: The created View object (or None if failed)
        """
        # Instantiate the TrainingView
        instance = cls("training", feature_set)

        # Sanity check on the id column
        if not id_column:
            instance.log.important("No id column specified, using auto_id_column")
            if not instance.auto_id_column:
                instance.log.error("No id column specified and no auto_id_column found, aborting")
                return None
            id_column = instance.auto_id_column

        # Default 80/20 split using modulo
        training_logic = f"""CASE
            WHEN MOD(ROW_NUMBER() OVER (ORDER BY {id_column}), 10) < 8 THEN True
            ELSE False
        END AS training"""

        # Wrap the custom query and add training column
        create_view_query = f"""
        CREATE OR REPLACE VIEW {instance.table} AS
        SELECT *, {training_logic}
        FROM ({sql_query}) AS custom_source
        """

        # Execute the CREATE VIEW query
        instance.data_source.execute_statement(create_view_query)

        # Return the View
        return View(instance.data_source, instance.view_name, auto_create_view=False)

create(feature_set, *, id_column=None, holdout_ids=None, filter_expression=None, source_table=None) classmethod

Factory method to create and return a TrainingView instance.

Parameters:

Name Type Description Default
feature_set FeatureSet

A FeatureSet object

 required
id_column str

The name of the id column. Defaults to None.

 None
holdout_ids Union[list[str], list[int], None]

A list of holdout ids. Defaults to None.

 None
filter_expression str

SQL filter expression (e.g., "age > 25 AND status = 'active'"). Defaults to None.

 None
source_table str

The table/view to create the view from. Defaults to None.

 None

Returns:

Type Description
Union[View, None]

Union[View, None]: The created View object (or None if failed to create the view)
Source code in src/workbench/core/views/training_view.py 
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
@classmethod
def create(
    cls,
    feature_set: FeatureSet,
    *,  # Enforce keyword arguments after feature_set
    id_column: str = None,
    holdout_ids: Union[list[str], list[int], None] = None,
    filter_expression: str = None,
    source_table: str = None,
) -> Union[View, None]:
    """Factory method to create and return a TrainingView instance.

    Args:
        feature_set (FeatureSet): A FeatureSet object
        id_column (str, optional): The name of the id column. Defaults to None.
        holdout_ids (Union[list[str], list[int], None], optional): A list of holdout ids. Defaults to None.
        filter_expression (str, optional): SQL filter expression (e.g., "age > 25 AND status = 'active'").
                                           Defaults to None.
        source_table (str, optional): The table/view to create the view from. Defaults to None.

    Returns:
        Union[View, None]: The created View object (or None if failed to create the view)
    """
    # Instantiate the TrainingView with "training" as the view name
    instance = cls("training", feature_set, source_table)

    # Drop any columns generated from AWS
    aws_cols = ["write_time", "api_invocation_time", "is_deleted", "event_time"]
    source_table_columns = get_column_list(instance.data_source, instance.source_table)
    column_list = [col for col in source_table_columns if col not in aws_cols]

    # Sanity check on the id column
    if not id_column:
        instance.log.important("No id column specified, we'll try the auto_id_column ..")
        if not instance.auto_id_column:
            instance.log.error("No id column specified and no auto_id_column found, aborting ..")
            return None
        else:
            if instance.auto_id_column not in column_list:
                instance.log.error(
                    f"Auto id column {instance.auto_id_column} not found in column list, aborting .."
                )
                return None
            else:
                id_column = instance.auto_id_column

    # Enclose each column name in double quotes
    sql_columns = ", ".join([f'"{column}"' for column in column_list])

    # Build the training assignment logic
    if holdout_ids:
        # Format the list of holdout ids for SQL IN clause
        if all(isinstance(id, str) for id in holdout_ids):
            formatted_holdout_ids = ", ".join(f"'{id}'" for id in holdout_ids)
        else:
            formatted_holdout_ids = ", ".join(map(str, holdout_ids))

        training_logic = f"""CASE
            WHEN {id_column} IN ({formatted_holdout_ids}) THEN False
            ELSE True
        END AS training"""
    else:
        # Default 80/20 split using modulo
        training_logic = f"""CASE
            WHEN MOD(ROW_NUMBER() OVER (ORDER BY {id_column}), 10) < 8 THEN True
            ELSE False
        END AS training"""

    # Build WHERE clause if filter_expression is provided
    where_clause = f"\nWHERE {filter_expression}" if filter_expression else ""

    # Construct the CREATE VIEW query
    create_view_query = f"""
    CREATE OR REPLACE VIEW {instance.table} AS
    SELECT {sql_columns}, {training_logic}
    FROM {instance.source_table}{where_clause}
    """

    # Execute the CREATE VIEW query
    instance.data_source.execute_statement(create_view_query)

    # Return the View
    return View(instance.data_source, instance.view_name, auto_create_view=False)

create_with_sql(feature_set, *, sql_query, id_column=None) classmethod

Factory method to create a TrainingView from a custom SQL query.

This method takes a complete SQL query and adds the default 80/20 training split. Use this when you need complex queries like UNION ALL for oversampling.

Parameters:

Name Type Description Default
feature_set FeatureSet

A FeatureSet object

 required
sql_query str

Complete SELECT query (without the final semicolon)

 required
id_column str

The name of the id column for training split. Defaults to None.

 None

Returns:

Type Description
Union[View, None]

Union[View, None]: The created View object (or None if failed)
Source code in src/workbench/core/views/training_view.py 
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
@classmethod
def create_with_sql(
    cls,
    feature_set: FeatureSet,
    *,
    sql_query: str,
    id_column: str = None,
) -> Union[View, None]:
    """Factory method to create a TrainingView from a custom SQL query.

    This method takes a complete SQL query and adds the default 80/20 training split.
    Use this when you need complex queries like UNION ALL for oversampling.

    Args:
        feature_set (FeatureSet): A FeatureSet object
        sql_query (str): Complete SELECT query (without the final semicolon)
        id_column (str, optional): The name of the id column for training split. Defaults to None.

    Returns:
        Union[View, None]: The created View object (or None if failed)
    """
    # Instantiate the TrainingView
    instance = cls("training", feature_set)

    # Sanity check on the id column
    if not id_column:
        instance.log.important("No id column specified, using auto_id_column")
        if not instance.auto_id_column:
            instance.log.error("No id column specified and no auto_id_column found, aborting")
            return None
        id_column = instance.auto_id_column

    # Default 80/20 split using modulo
    training_logic = f"""CASE
        WHEN MOD(ROW_NUMBER() OVER (ORDER BY {id_column}), 10) < 8 THEN True
        ELSE False
    END AS training"""

    # Wrap the custom query and add training column
    create_view_query = f"""
    CREATE OR REPLACE VIEW {instance.table} AS
    SELECT *, {training_logic}
    FROM ({sql_query}) AS custom_source
    """

    # Execute the CREATE VIEW query
    instance.data_source.execute_statement(create_view_query)

    # Return the View
    return View(instance.data_source, instance.view_name, auto_create_view=False)

Questions?

The SuperCowPowers team is happy to answer any questions you may have about AWS and Workbench. Please contact us at workbench@supercowpowers.com or on chat us up on Discord