CREATE TABLE `def_feature` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`lastcomputed` datetime DEFAULT NULL,
`SourceQuery` text DEFAULT NULL,
`SourceQueryPk` varchar(255) DEFAULT NULL,
`InputTable1` varchar(255) DEFAULT NULL,
`InputFields1` text DEFAULT NULL,
`InputPK1` varchar(255) DEFAULT NULL,
`InputJoinMode1` varchar(255) DEFAULT NULL,
`InputTable2` varchar(255) DEFAULT NULL,
`InputFields2` text DEFAULT NULL,
`InputPK2` varchar(255) DEFAULT NULL,
`InputJoinMode2` varchar(255) DEFAULT NULL,
`InputTable3` varchar(255) DEFAULT NULL,
`InputFields3` text DEFAULT NULL,
`InputPK3` varchar(255) DEFAULT NULL,
`InputJoinMode3` varchar(255) DEFAULT NULL,
`ConstantTable` varchar(255) DEFAULT NULL,
`InputGlobals1` varchar(255) DEFAULT NULL,
`InputGlobals2` varchar(255) DEFAULT NULL,
`InputGlobals3` varchar(255) DEFAULT NULL,
`Module` varchar(255) DEFAULT NULL,
`ComputeSummary` longtext DEFAULT NULL,
`RaciSimulation` text DEFAULT NULL,
`RaciProduction` text DEFAULT NULL,
`RaciOutcome` text DEFAULT NULL,
`RaciDevelopment` text DEFAULT NULL,
PRIMARY KEY (`FeatureCode`,`Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_features` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Feature` varchar(128) NOT NULL,
`Module` varchar(255) DEFAULT NULL,
`FeatureType` varchar(50) DEFAULT NULL,
`FeatureDefinition` text DEFAULT NULL,
`FeatureDataType` varchar(50) DEFAULT NULL,
`Global` char(1) DEFAULT 'N',
`Active` char(1) DEFAULT 'Y',
`Notes` text,
`LineageDiagram` text DEFAULT NULL,
`ComputeOrder` int DEFAULT 0,
`ComputedSQL` text DEFAULT NULL,
`LastComputedAt` datetime DEFAULT NULL,
`DistinctValues` text DEFAULT NULL,
`DistinctCount` int DEFAULT NULL,
`InputTable` varchar(255) DEFAULT NULL,
`InputTablePk` varchar(255) DEFAULT NULL,
`InputTableNullStrategy` varchar(50) DEFAULT 'KEEP_NULL',
`InputTableConstants` text DEFAULT NULL,
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_expectation` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Feature` varchar(128) NOT NULL,
`ExpectationName` varchar(128) NOT NULL,
`ExpectationConfigJson` text DEFAULT NULL,
`Severity` varchar(50) DEFAULT 'ERROR',
`Notes` text DEFAULT NULL,
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`,`ExpectationName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_dependency` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Feature` varchar(128) NOT NULL,
`DependsOnFeature` varchar(128) NOT NULL,
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`,`DependsOnFeature`),
CONSTRAINT `fk_feature_dep_source` FOREIGN KEY (`FeatureCode`,`Package`,`Feature`)
REFERENCES `def_features` (`FeatureCode`,`Package`,`Feature`) ON DELETE CASCADE,
CONSTRAINT `fk_feature_dep_target` FOREIGN KEY (`FeatureCode`,`Package`,`DependsOnFeature`)
REFERENCES `def_features` (`FeatureCode`,`Package`,`Feature`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_version` (
`Guid` VARCHAR(64) DEFAULT NULL,
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Version` int NOT NULL,
`ThreeWords` VARCHAR(255) DEFAULT NULL,
`VersionDate` DATETIME DEFAULT NULL,
`NodeCount` INT DEFAULT 0,
`ChangedBy` VARCHAR(255) DEFAULT NULL,
`ChangeType` VARCHAR(50) DEFAULT 'COMPUTE',
`ChangeNotes` TEXT DEFAULT NULL,
`CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
`CreatedBy` varchar(100) DEFAULT NULL,
`IsActive` boolean DEFAULT FALSE,
`Notes` text,
`ConfigSnapshot` json,
`RaciSimulation` TEXT DEFAULT NULL,
`RaciProduction` TEXT DEFAULT NULL,
`RaciOutcome` TEXT DEFAULT NULL,
`RaciDevelopment` TEXT DEFAULT NULL,
PRIMARY KEY (`FeatureCode`,`Package`,`Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_statistics` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Feature` varchar(128) NOT NULL,
`ComputedAt` datetime DEFAULT CURRENT_TIMESTAMP,
`RowCount` bigint,
`NullCount` bigint,
`NullPct` float,
`DistinctCount` bigint,
`MinValue` varchar(255),
`MaxValue` varchar(255),
`MeanValue` float,
`MedianValue` float,
`StdDevValue` float,
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`,`ComputedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_lineage` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Feature` varchar(128) NOT NULL,
`SourceTable` varchar(191) NOT NULL,
`SourceColumn` varchar(191) NOT NULL,
`LineageType` varchar(50) DEFAULT 'DIRECT',
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`,`SourceTable`,`SourceColumn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_usage` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Feature` varchar(128) NOT NULL,
`AccessDate` date NOT NULL,
`RequestCount` bigint DEFAULT 0,
`UsedByModels` json DEFAULT NULL,
`UsedByApplications` json DEFAULT NULL,
`AvgResponseTimeMs` float DEFAULT NULL,
`ErrorCount` bigint DEFAULT 0,
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`,`AccessDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_feature_quality` (
`FeatureCode` varchar(64) NOT NULL,
`Package` varchar(64) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Feature` varchar(128) NOT NULL,
`AssessedAt` datetime DEFAULT CURRENT_TIMESTAMP,
`QualityScore` float DEFAULT NULL,
`CompletenessScore` float DEFAULT NULL,
`ConsistencyScore` float DEFAULT NULL,
`UniquenessScore` float DEFAULT NULL,
`TimelinessScore` float DEFAULT NULL,
`ValidityScore` float DEFAULT NULL,
`QualityReport` json DEFAULT NULL,
PRIMARY KEY (`FeatureCode`,`Package`,`Feature`,`AssessedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS
`track_feature_explain` (
`TrackId` INT AUTO_INCREMENT PRIMARY KEY,
`FeatureCode` VARCHAR(255) DEFAULT NULL,
`FeatureName` VARCHAR(255) DEFAULT NULL,
`ElapsedTime` DECIMAL(10,4) DEFAULT NULL,
`ExplainKey` VARCHAR(255) DEFAULT NULL,
`ExplainWarning` TEXT DEFAULT NULL,
`ExplainRows` INT DEFAULT NULL,
`Package` VARCHAR(255) DEFAULT NULL,
`CreatedDate` DATETIME
DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
INSERT INTO track_feature_explain
(FeatureCode, FeatureName, ElapsedTime,
ExplainKey, ExplainWarning, ExplainRows,
Package)
VALUES
('{feature_code}', '{feature_name}',
{elapsed_time}, '{explain_key}',
'{explain_warning}', {explain_rows},
'{package}')
SELECT SourceQuery, SourceQueryPk,
InputTable1, InputFields1, InputPK1, InputJoinMode1,
InputTable2, InputFields2, InputPK2, InputJoinMode2,
InputTable3, InputFields3, InputPK3, InputJoinMode3,
ConstantTable,
InputGlobals1, InputGlobals2, InputGlobals3
FROM def_feature
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
SELECT df.SourceQuery, df.SourceQueryPk,
df.InputTable1, df.InputFields1, df.InputPK1, df.InputJoinMode1,
df.InputTable2, df.InputFields2, df.InputPK2, df.InputJoinMode2,
df.InputTable3, df.InputFields3, df.InputPK3, df.InputJoinMode3,
df.ConstantTable,
df.InputGlobals1, df.InputGlobals2, df.InputGlobals3,
dfs.Feature, dfs.FeatureType, dfs.FeatureDefinition,
dfs.FeatureDataType, dfs.Global, dfs.InputTable, dfs.InputTablePk,
dfs.InputTableNullStrategy, dfs.InputTableConstants
FROM def_feature df
JOIN def_features dfs
ON df.FeatureCode = dfs.FeatureCode
AND df.Package = dfs.Package
WHERE df.FeatureCode = '{feature_code}'
AND df.Package = '{package}'
AND dfs.Active = 'Y'
ORDER BY dfs.Global DESC, dfs.ComputeOrder ASC
SELECT ExpectationName, ExpectationConfigJson, Severity, Notes, Feature
FROM def_feature_expectation
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature LIKE '{feature_pattern}';
UPDATE def_feature
SET lastcomputed = NOW()
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
UPDATE def_feature
SET lastcomputed = NOW(),
ComputeSummary = '{compute_summary_json}'
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
SELECT ComputeSummary
FROM def_feature
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
UPDATE def_features
SET ComputedSQL = '{computed_sql}',
LastComputedAt = NOW(),
DistinctValues = {distinct_values},
DistinctCount = {distinct_count}
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
SELECT DISTINCT `{feature}`
FROM `{table_name}`
WHERE `{feature}` IS NOT NULL
ORDER BY `{feature}`
LIMIT 20
CREATE TABLE `{table_name}` (
{pk_def_str},
{pk_str}
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
INSERT INTO `{table_name}` ({source_query_pk})
SELECT {source_query_pk} FROM {source_query} AS src
SELECT Feature, DependsOnFeature
FROM def_feature_dependency
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature IN ('{feature_list}')
UPDATE /*+ JOIN_ORDER(fst, st) */ `{table_name}` AS fst
STRAIGHT_JOIN `{source_query}` AS st ON {join_on_clause}
SET {set_clause_str};
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN ({source_query}) AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET {set_clause_str}
WHERE {filter_condition};
UPDATE `{table_name}`
SET {set_clause_str}
WHERE {filter_condition};
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN `{source_query}` AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature_name}` = st.`{source_column}`;
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN `{source_query}` AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature_name}` = ({sql_expression});
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN `{source_query}` AS st USE INDEX (PRIMARY)
ON {join_on_clause}
LEFT JOIN (
SELECT `{join_key}`, {agg_expr} AS agg_value
FROM `{related_table}`
GROUP BY `{join_key}`
) AS agg ON st.`{join_key}` = agg.`{join_key}`
SET fst.`{feature_name}` = COALESCE(agg.agg_value, 0);
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN (
SELECT {pk_list}, {window_definition} AS window_value
FROM `{source_query}` AS st
) AS win ON {join_on_clause}
SET fst.`{feature_name}` = win.window_value;
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN `{source_query}` AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature_name}` = JSON_UNQUOTE(JSON_EXTRACT(st.`{json_column}`, '{json_path}'));
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN `{source_query}` AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature_name}` = ({processed_expression});
SELECT COUNT(*) FROM `{table_name}`
SELECT COUNT(*) FROM `{table_name}`
WHERE `{feature}` IS NULL
SELECT COUNT(DISTINCT `{feature}`) FROM `{table_name}`
SELECT
MIN(`{feature}`),
MAX(`{feature}`),
AVG(`{feature}`),
STDDEV(`{feature}`)
FROM `{table_name}`
WHERE `{feature}` IS NOT NULL
INSERT INTO def_feature_statistics
(FeatureCode, Package, Feature, RowCount, NullCount, NullPct,
DistinctCount, MinValue, `MaxValue`, `MeanValue`, `StdDevValue`)
VALUES (
'{feature_code}',
'{package}',
'{feature}',
{row_count},
{null_count},
{null_pct},
{distinct_count},
{min_value},
{max_value},
{mean_value},
{std_dev}
)
SELECT * FROM `{feature_store_table}`
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN ({source_query}) AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature}` = st.`{feature_definition}`
WHERE {filter_condition};
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN ({source_query}) AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature}` = ({feature_definition})
WHERE {filter_condition};
UPDATE `{table_name}` AS fst USE INDEX (PRIMARY)
STRAIGHT_JOIN ({source_query}) AS st USE INDEX (PRIMARY)
ON {join_on_clause}
SET fst.`{feature}` = ({processed_expression})
WHERE {filter_condition};
UPDATE `{table_name}`
SET `{feature}` = `{feature_definition}`
WHERE {filter_condition};
UPDATE `{table_name}`
SET `{feature}` = ({feature_definition})
WHERE {filter_condition};
UPDATE `{table_name}`
SET `{feature}` = ({processed_expression})
WHERE {filter_condition};
CREATE TABLE IF NOT EXISTS `{table_name}_globals` (
feature_name VARCHAR(128) NOT NULL,
feature_value TEXT DEFAULT NULL,
PRIMARY KEY (feature_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
DELETE FROM `{globals_table}`;
INSERT INTO `{globals_table}` (feature_name, feature_value)
VALUES ('{feature_name}', {feature_value})
ON DUPLICATE KEY UPDATE feature_value = {feature_value};
SELECT feature_name, feature_value
FROM `{globals_table}`;
SELECT * FROM `{table_name}` WHERE {where_clause} LIMIT 1
¶ get_table_columns
SHOW COLUMNS FROM `{table_name}`
SELECT SourceTable, SourceColumn, LineageType
FROM def_feature_lineage
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
ORDER BY SourceColumn
¶ get_column_impact
SELECT Feature, LineageType
FROM def_feature_lineage
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND LOWER(SourceColumn) = LOWER('{column}')
ORDER BY Feature
SELECT Feature, SourceTable, SourceColumn, LineageType
FROM def_feature_lineage
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
ORDER BY Feature, SourceColumn
SELECT Feature, SourceTable, SourceColumn, LineageType
FROM def_feature_lineage
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
ORDER BY SourceColumn
SELECT Feature
FROM def_features
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
UPDATE def_features
SET LineageDiagram = '{lineage_diagram}'
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
SELECT COUNT(DISTINCT `{feature}`)
FROM `{table_name}`
WHERE `{feature}` IS NOT NULL
INSERT INTO def_feature_lineage
(FeatureCode, Package, Feature, SourceTable, SourceColumn, LineageType)
VALUES (
'{feature_code}',
'{package}',
'{feature}',
'{source_table}',
'{source_column}',
'{lineage_type}'
)
ON DUPLICATE KEY UPDATE LineageType = '{lineage_type}'
SELECT `{key_col}`, CAST(`{value_col}` AS CHAR)
FROM `{constant_table}`
UPDATE `{table_name}` AS fst
JOIN {formatted_source} AS st ON {join_on_clause}
SET fst.`{feature_name}` = st.`{source_column}`;
UPDATE `{table_name}` AS fst
JOIN {formatted_source} AS st ON {join_on_clause}
SET fst.`{feature_name}` = ({sql_expression});
UPDATE `{table_name}` AS fst
JOIN {formatted_source} AS st ON {join_on_clause}
LEFT JOIN (
SELECT `{join_key}`, {agg_expr} AS agg_value
FROM `{related_table}`
GROUP BY `{join_key}`
) AS agg ON st.`{join_key}` = agg.`{join_key}`
SET fst.`{feature_name}` = COALESCE(agg.agg_value, 0);
UPDATE `{table_name}` AS fst
JOIN (
SELECT {pk_list}, {window_definition} AS window_value
FROM {formatted_source} AS st
) AS win ON {join_on_clause}
SET fst.`{feature_name}` = win.window_value;
UPDATE `{table_name}` AS fst
JOIN {formatted_source} AS st ON {join_on_clause}
SET fst.`{feature_name}` = JSON_UNQUOTE(
JSON_EXTRACT(st.`{json_column}`, '{json_path}')
);
UPDATE `{table_name}` AS fst
JOIN {formatted_source} AS st ON {join_on_clause}
SET fst.`{feature_name}` = ({processed_expression});
SELECT RequestCount, UsedByModels, UsedByApplications,
AvgResponseTimeMs, ErrorCount
FROM def_feature_usage
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
AND AccessDate = '{access_date}'
UPDATE def_feature_usage
SET RequestCount = {request_count},
UsedByModels = '{used_by_models}',
UsedByApplications = '{used_by_applications}',
AvgResponseTimeMs = {avg_response_time_ms},
ErrorCount = {error_count}
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
AND AccessDate = '{access_date}'
INSERT INTO def_feature_usage
(FeatureCode, Package, Feature, AccessDate, RequestCount,
UsedByModels, UsedByApplications, AvgResponseTimeMs, ErrorCount)
VALUES (
'{feature_code}',
'{package}',
'{feature}',
'{access_date}',
1,
'{used_by_models}',
'{used_by_applications}',
{avg_response_time_ms},
{error_count}
)
SELECT Feature,
SUM(RequestCount) as TotalRequests,
AVG(AvgResponseTimeMs) as AvgResponseTime,
SUM(ErrorCount) as TotalErrors,
UsedByModels,
UsedByApplications
FROM def_feature_usage
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND AccessDate >= '{start_date}'
GROUP BY Feature, UsedByModels, UsedByApplications
ORDER BY TotalRequests DESC
SELECT Feature
FROM def_features
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
SELECT Feature, FeatureType, FeatureDataType
FROM def_features
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
SELECT Feature, FeatureType, FeatureDataType
FROM def_features
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
SELECT COUNT(*) FROM `{table_name}`
SELECT NullCount, NullPct, DistinctCount, MinValue, `MaxValue`,
`MeanValue`, `StdDevValue`, ComputedAt
FROM def_feature_statistics
WHERE FeatureCode = '{feature_code}'
AND Package = '{package}'
AND Feature = '{feature}'
ORDER BY ComputedAt DESC
LIMIT 1
INSERT INTO def_feature_quality
(FeatureCode, Package, Feature, QualityScore,
CompletenessScore, ConsistencyScore, UniquenessScore,
TimelinessScore, ValidityScore, QualityReport)
VALUES (
'{feature_code}',
'{package}',
'{feature}',
{quality_score},
{completeness_score},
{consistency_score},
{uniqueness_score},
{timeliness_score},
{validity_score},
'{quality_report}'
)