o U|%il@sdZddlZddlZddlmZddlmZmZmZm Z ddl Z ddl Z ddlmZmZmZmZmZmZmZmZmZmZmZmZmZddlmZGdddeZGd d d ZGd d d eZ dS) zHeatWave ML model utilities for MySQL Connector/Python. Provides classes to manage training, prediction, scoring, and explanations via MySQL HeatWave stored procedures. N)Enum)AnyDictOptionalUnion) VAR_NAME_SPACEatomic_transaction convert_to_df execute_sqlformat_value_sqlget_random_name source_schemasql_response_to_dfsql_table_from_dfsql_table_to_df table_existstemporary_sql_tables validate_name)MySQLConnectionAbstractc@sJeZdZdZdZdZdZdZdZdZ dZ e d e e dfd e fd d Zd S)ML_TASKz/Enumeration of supported ML tasks for HeatWave.classification regression forecastinganomaly_detectionlog_anomaly_detectionrecommendationtopic_modelingtaskreturncCst|tr|S|jS)a' Return the string representation of a machine learning task. Args: task (Union[str, ML_TASK]): The task to convert. Accepts either a task enum member (ML_TASK) or a string. Returns: str: The string value of the ML task. ) isinstancestrvalue)rr"N/home/air/sos_test/back/venv/lib/python3.10/site-packages/mysql/ai/ml/model.pyget_task_stringGs zML_TASK.get_task_stringN)__name__ __module__ __qualname____doc__CLASSIFICATION REGRESSION FORECASTINGANOMALY_DETECTIONLOG_ANOMALY_DETECTIONRECOMMENDATIONTOPIC_MODELING staticmethodrr r$r"r"r"r#r<s rc @s*eZdZdZejdfdedeeefde efddZ de fd d Z dede e fd d Zde e fd dZdede fddZd$ddZdefddZde fddZdede ede e ddfddZdedede e ddfddZdededede e def d d!Zdedede e dejfd"d#ZdS)%_MyModelCommona8 Common utilities and workflow for MySQL HeatWave ML models. This class handles model lifecycle steps such as loading, fitting, scoring, making predictions, and explaining models or predictions. Not intended for direct instantiation, but as a superclass for heatwave model wrappers. Attributes: db_connection: MySQL connector database connection. task: ML task, e.g., "classification" or "regression". model_name: Identifier of model in MySQL. schema_name: Database schema used for operations and temp tables. N db_connectionr model_namecCs||_t||_t||_t|j }t|dWdn1s#wY|dur1t|j }t d||_ |j d|_ ||_ t|t|j}t|d|j d|fdWddS1sewYdS)a Instantiate _MyMLModelCommon. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-train.html A full list of supported tasks can be found under "Common ML_TRAIN Options" Args: db_connection: MySQL database connection. task: ML task type (default: "classification"). model_name: Name to register the model within MySQL (default: None). Raises: ValueError: If the schema name is not valid DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. Returns: None z(CALL sys.ML_CREATE_OR_UPGRADE_CATALOG();N.z.scorezSET @z = %s;params)r2rr$rr schema_namerr r _is_model_name_availabler model_varmodel_var_scorer3r)selfr2rr3cursorr"r"r#__init__is      "z_MyModelCommon.__init__rcCsh|}d|d}d|d|j}t|j}t|||jdkWdS1s-wYdS)a% Deletes the model from the model catalog if present Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. Returns: Whether the model was deleted ML_SCHEMA_.MODEL_CATALOGz DELETE FROM  WHERE model_handle = @rN) _get_userr9rr2r rowcount)r; current_userqualified_model_catalog delete_modelr<r"r"r# _delete_models   $z_MyModelCommon._delete_modelc sdtdtfdd|}d|d}d|d}t|j4}t|||fd t|}|jr1d }n|jd d }t |d } fdd| D}|Wd S1sUwYd S)a Retrieves the model info from the model_catalog Args: model_var: The model alias to retrieve Returns: The model info from the model_catalog (None if the model is not present in the catalog) Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. elemrcSs6t|trzt|}W|StjyY|Sw|SN)rr jsonloadsJSONDecodeError)rGr"r"r# process_cols  z3_MyModelCommon._get_model_info..process_colr>r?SELECT * FROM z WHERE model_handle = %sr5Nrecords)orientrcsi|] \}}||qSr"r").0keyrGrLr"r# sz2_MyModelCommon._get_model_info..) rrArr2r remptyto_jsonrIrJitems) r;r3rCrD model_existsr< model_info_dfresultunprocessed_resultunprocessed_result_jsonr"rRr#_get_model_infos"     $z_MyModelCommon._get_model_infocCs ||jS)a Checks if the model name is available. Model info is present in the catalog only if the model was previously fitted. Returns: True if the model name is not part of the model catalog Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. )r\r3)r;r"r"r#get_model_infos z_MyModelCommon.get_model_infocCs||duS)a1 Checks if the model name is available Returns: True if the model name is not part of the model catalog Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. N)r\)r;r3r"r"r#r8s z'_MyModelCommon._is_model_name_availablecCsHt|j}d|jd}t||WddS1swYdS)a Loads the model specified by `self.model_name` into MySQL. After loading, the model is ready to handle ML operations. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-model-load.html Raises: DatabaseError: If the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. Returns: None zCALL sys.ML_MODEL_LOAD(@z, NULL);N)rr2r9r )r;r<load_model_queryr"r"r# _load_models  "z_MyModelCommon._load_modelcCsVt|j}|d|ddd}t|WdS1s$wYdS)a Fetch the current database user (without host). Returns: The username string associated with the connection. Raises: DatabaseError: If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the user name includes unsupported characters zSELECT CURRENT_USER()r@N)rr2executefetchonesplitr)r;r<rCr"r"r#rAs $z_MyModelCommon._get_usercCsx|t|j)}|}d|d}d|d|j}t||t|}|jdWdS1s5wYdS)a Get model explanations, such as detailed feature importances. Returns: dict: Feature importances and model explainability data. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-model-explanations.html Raises: DatabaseError: If the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the model does not exist in the model catalog. Should only occur if model was not fitted or was deleted. r>r?zSELECT model_explanation FROM r@rrN)r_rr2rAr9r riloc)r;r<rCrD explain_querydfr"r"r# explain_model#s   $z_MyModelCommon.explain_model table_nametarget_column_nameoptionscCst||durt|d|d}nd}|duri}t|}|j|d<|t|j(}t|\}}t|d|j d|d|d|d |j d |d WddS1sYwYdS) a2 Fit an ML model using a referenced SQL table and target column. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-train.html A full list of supported options can be found under "Common ML_TRAIN Options" Args: table_name: Name of the training data table. target_column_name: Name of the target/label column. options: Additional fit/config options (may override defaults). Raises: DatabaseError: If provided options are invalid or unsupported. If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the table or target_column name is not valid Returns: None N'NULLrzCALL sys.ML_TRAIN('r4', , z, @)r5) rcopydeepcopyrrFrr2r r r7r9)r;rirjrktarget_col_stringr< placeholders parametersr"r"r#_fitEs8    "z_MyModelCommon._fitoutput_table_namecCst|t||t|j,}t|\}}t|d|jd|d|jd|jd|d|d |dWddS1s@wYdS) a Predict on a given data table and write results to an output table. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-predict-table.html A full list of supported options can be found under "ML_PREDICT_TABLE Options" Args: table_name: Name of the SQL table with input data. output_table_name: Name for the SQL output table to contain predictions. options: Optional prediction options. Returns: None Raises: DatabaseError: If provided options are invalid or unsupported, or if the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the table or output_table name is not valid zCALL sys.ML_PREDICT_TABLE('r4', @, 'rnrpr5N)rr_rr2r r r7r9)r;rirwrkr<rtrur"r"r#_predict~s.  "z_MyModelCommon._predictmetricc Cst|t|t||t|j@}t|\}}t|d|jd|d|d|jd|jd|d |g|dt|d |jt |}|j d Wd S1sXwYd S) aI Evaluate model performance with a scoring metric. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-score.html A full list of supported options can be found under "Options for Recommendation Models" and "Options for Anomaly Detection Models" Args: table_name: Table with features and ground truth. target_column_name: Column of true target labels. metric: String name of the metric to compute. options: Optional dictionary of further scoring options. Returns: float: Computed score from the ML system. Raises: DatabaseError: If provided options are invalid or unsupported, or if the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the table or target_column name or metric is not valid zCALL sys.ML_SCORE('r4z', 'rxz, %s, @rorpr5zSELECT @rdN) rr_rr2r r r7r9r:rre) r;rirjr{rkr<rtrurgr"r"r#_scores6"   $z_MyModelCommon._scorecCst|t||durddi}|t|j=}t|\}}t|d|jd|d|jd|jd|d|d |d t|d |jd|t|}|WdS1sYwYdS) a Produce explanations for model predictions on provided data. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-explain-table.html A full list of supported options can be found under "ML_EXPLAIN_TABLE Options" Args: table_name: Name of the SQL table with input data. output_table_name: Name for the SQL table to store explanations. options: Optional dictionary (default: {"prediction_explainer": "permutation_importance"}). Returns: DataFrame: Prediction explanations from the output SQL table. Raises: DatabaseError: If provided options are invalid or unsupported, or if the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. ValueError: If the table or output_table name is not valid Nprediction_explainerpermutation_importancezCALL sys.ML_EXPLAIN_TABLE('r4rxryrnrpr5rM) rr_rr2r r r7r9r)r;rirwrkr<rtrurgr"r"r#_explain_predictionss8   $z#_MyModelCommon._explain_predictions)rN)r%r&r'r(rr)rrr rr=boolrFdictr\r]r8r_rArhrvrzfloatr|pd DataFramerr"r"r"r#r1Zsp  ./ " 9 . <r1c @seZdZdZ ddeejejfde eejejfde e ddfddZ ddeejejfde e dejfd d Z ddeejejfdeejejfd e de e def d d Z ddeejejfdedejfddZdS)MyModelz Convenience class for managing the ML workflow using pandas DataFrames. Methods convert in-memory DataFrames into temp SQL tables before delegating to the _MyMLModelCommon routines, and automatically clean up temp resources. NXyrkrc stt|}t|jr}t|jT}|durEt|tjr%|jd}ntfdd}|jvr:t d|d }|||<|}nd}}t ||j |\} } | |j | f|| ||Wdn1skwYWddSWddS1swYdS)a Fit a model using DataFrame inputs. If an 'id' column is defined in either dataframe, it will be used as the primary key. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-train.html A full list of supported options can be found under "Common ML_TRAIN Options" Args: X: Features DataFrame. y: (Optional) Target labels DataFrame or Series. If None, only X is used. options: Additional options to pass to training. Returns: None Raises: DatabaseError: If provided options are invalid or unsupported. If a database connection issue occurs. If an operational error occurs during execution. Notes: Combines X and y as necessary. Creates a temporary table in the schema for training, and deletes it afterward. Nrc |jvSrHcolumnsnamerr"r#U zMyModel.fit..zTarget column y with name z' already present in feature dataframe X)r rr2rrrrrr ValueErrorrqrr7appendrv) r;rrrkr<temporary_tablesrj df_combinedfinal_df_rir"rr#fit(s4!     Pz MyModel.fitc st|}tjatjK}tj|\}}|j|ftfdd}|j|f|||t j|}|d t j |d<|WdWdS1s]wYWddS1smwYdS)a Generate model predictions using DataFrame input. If an 'id' column is defined in either dataframe, it will be used as the primary key. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-predict-table.html A full list of supported options can be found under "ML_PREDICT_TABLE Options" Args: X: DataFrame containing prediction features (no labels). options: Additional prediction settings. Returns: DataFrame with prediction results as returned by HeatWave. Raises: DatabaseError: If provided options are invalid or unsupported, or if the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. Notes: Temporary SQL tables are created and deleted for input/output. ctj| SrHrr7rir<r;r"r#rz!MyModel.predict.. ml_resultsN) r rr2rrr7rr rzrmaprIrJ)r;rrkrrrirw predictionsr"rr#predictjs$  RzMyModel.predictr{c stt|}t|jR}t|j<}tfdd}}|||<|} t||j| \} } ||j| f| | |||} | WdWdS1sSwYWddS1scwYdS)a Score the model using X/y data and a selected metric. If an 'id' column is defined in either dataframe, it will be used as the primary key. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-score.html A full list of supported options can be found under "Options for Recommendation Models" and "Options for Anomaly Detection Models" Args: X: DataFrame of features. y: DataFrame or Series of labels. metric: Metric name (e.g., "balanced_accuracy"). options: Optional ml scoring options. Raises: DatabaseError: If provided options are invalid or unsupported, or if the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. Returns: float: Computed score. crrHrrrr"r#rrzMyModel.score..N) r rr2rr rqrr7rr|) r;rrr{rkr<rrjrrrriscorer"rr#rs #Rz MyModel.scorec st|}tjPtj:}tj|\}}|j|ftfdd}|j|f|||}|WdWdS1sLwYWddS1s\wYdS)a  Explain model predictions using provided data. If an 'id' column is defined in either dataframe, it will be used as the primary key. References: https://dev.mysql.com/doc/heatwave/en/mys-hwaml-ml-explain-table.html A full list of supported options can be found under "ML_EXPLAIN_TABLE Options" Args: X: DataFrame for which predictions should be explained. options: Optional dictionary of explainability options. Returns: DataFrame containing explanation details (feature attributions, etc.) Raises: DatabaseError: If provided options are invalid or unsupported, or if the model is not initialized, i.e., fit or import has not been called If a database connection issue occurs. If an operational error occurs during execution. Notes: Temporary input/output tables are cleaned up after explanation. crrHrrrr"r#rrz-MyModel.explain_predictions..N) r rr2rrr7rr r)r;rrkrrrirw explanationsr"rr#explain_predictionss$  RzMyModel.explain_predictionsrH)r%r&r'r(rrrnpndarrayrrrrr rrrrr"r"r"r#r sP  E = 8r)!r(rqrIenumrtypingrrrrnumpyrpandasrmysql.ai.utilsrrr r r r r rrrrrrmysql.connector.abstractsrrr1rr"r"r"r#s < I