- Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathOML4SQL Nested Columns.dsnb
executable file
·1 lines (1 loc) · 28.5 KB
/
OML4SQL Nested Columns.dsnb
1
[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Nested Columns","description":null,"readOnly":false,"type":"low","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":["%md"," "],"enabled":true,"result":{"startTime":1714430822715,"interpreter":"md.low","endTime":1714430823129,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","# OML4SQL Nested Columns","","Oracle Machine Learning can work with the nested column format that enables building models with more than 1000 predictors. Machine learning projects can involve datasets with high dimensional features. Such features are usually encoded using one-hot encoding and can make for very wide data sets. Oracle Database provides a convenient data format called nested columns to handle such wide data. Such data can be stored in transactional form, or exist externally such as a CSV file. When in transactional format, it is straightforward to create nested columns. ","","In this notebook, we use the customer insurance lifetime value dataset as an example to show how to generate nested columns for model building in OML4SQL. While this table clearly fits in the database, we first illustrate converting this to transactional format before focusing on creating nested columns. We also provide examples of how to handle multiple nested columns in both numerical and categorical types. The entire workflow of model training and testing are also demonstrated for the dataset with nested columns. ","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>"],"enabled":true,"result":{"startTime":1714430823213,"interpreter":"md.low","endTime":1714430823319,"results":[{"message":"<h1 id=\"oml4sql-nested-columns\">OML4SQL Nested Columns<\/h1>\n<p>Oracle Machine Learning can work with the nested column format that enables building models with more than 1000 predictors. Machine learning projects can involve datasets with high dimensional features. Such features are usually encoded using one-hot encoding and can make for very wide data sets. Oracle Database provides a convenient data format called nested columns to handle such wide data. Such data can be stored in transactional form, or exist externally such as a CSV file. When in transactional format, it is straightforward to create nested columns.<\/p>\n<p>In this notebook, we use the customer insurance lifetime value dataset as an example to show how to generate nested columns for model building in OML4SQL. While this table clearly fits in the database, we first illustrate converting this to transactional format before focusing on creating nested columns. We also provide examples of how to handle multiple nested columns in both numerical and categorical types. The entire workflow of model training and testing are also demonstrated for the dataset with nested columns.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information ...","message":["%md","","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a>","* <a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/21/adobj/multilevel-collection-types.html\" target=\"_blank\">Oracle Nested Columns Table Documentation<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/machine-learning/oml4sql/23/dmapi/use-nested-data.html\" target=\"_blank\">OML using Nested Data Documentation<\/a>"],"enabled":true,"result":{"startTime":1714430823410,"interpreter":"md.low","endTime":1714430823488,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/21/adobj/multilevel-collection-types.html\" target=\"_blank\">Oracle Nested Columns Table Documentation<\/a><\/li>\n<li><a href=\"https://docs.oracle.com/en/database/oracle/machine-learning/oml4sql/23/dmapi/use-nested-data.html\" target=\"_blank\">OML using Nested Data Documentation<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Data overview","message":["%sql","","SELECT * ","FROM CUSTOMER_INSURANCE_LTV","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714430823568,"interpreter":"sql.low","endTime":1714430825268,"results":[{"message":"MARITAL_STATUS\tSTATE\tCREDIT_BALANCE\tCUSTOMER_TENURE\tMORTGAGE_AMOUNT\tBANK_FUNDS\tNUM_DEPENDENTS\tHAS_CHILDREN\tINCOME\tCUSTOMER_ID\tGENDER\tPROFESSION\tCREDIT_CARD_LIMITS\tREGION\tHOME_OWNERSHIP\tNUM_ONLINE_TRANS\tBUY_INSURANCE\tMONTHLY_CHECKS\tNUM_TRANS_KIOSK\tAGE\tMONEY_MONTLY_OVERDRAWN\tLTV\tTOTAL_AUTOM_PAYMENTS\tNUM_TRANS_TELLER\tCHECKING_BALANCE\tNUM_TRANS_ATM\tLTV_BIN\tFIRST_NAME\tNUM_MORTGAGES\tCAR_OWNERSHIP\tLAST_NAME\nSINGLE\tCA \t0\t3\t0\t0\t3\t0\t65871\tCU15154 \tM \tNurse\t1000\tWest\t0\t0\tNo\t0\t1\t24\t53.06\t14367.75\t0\t0\t25\t0\tMEDIUM\tGAYLE\t0\t0\tDURANT\nSINGLE\tNY \t0\t4\t0\t290\t4\t0\t68747\tCU15155 \tM \tProgrammer/Developer\t700\tNorthEast\t0\t0\tYes\t1\t1\t35\t53.84\t14686.75\t287\t2\t25\t4\tMEDIUM\tQUINTON\t0\t1\tMASSEY\nMARRIED\tMI \t0\t3\t1000\t550\t3\t0\t68684\tCU15157 \tM \tProgrammer/Developer\t1000\tMidwest\t1\t1000\tYes\t14\t1\t26\t53.48\t25271\t132\t2\t25\t4\tHIGH\tANIBAL\t1\t1\tJIMENEZ\nMARRIED\tUT \t0\t5\t1200\t1000\t5\t0\t59354\tCU15286 \tF \tFireman\t2500\tSouthwest\t1\t1200\tNo\t4\t5\t24\t53.08\t19738.5\t628\t3\t619\t1\tMEDIUM\tJUNITA\t1\t1\tROBERTSON\nMARRIED\tUT \t0\t4\t1800\t0\t3\t0\t84801\tCU15287 \tF \tPROF-26\t2500\tSouthwest\t1\t1800\tNo\t0\t5\t47\t53.06\t31900.25\t0\t0\t25\t0\tVERY HIGH\tCHASITY\t1\t1\tELLIS\nMARRIED\tUT \t0\t1\t1400\t0\t1\t0\t73987\tCU15289 \tM \tProfessor\t2500\tSouthwest\t1\t1400\tNo\t0\t5\t46\t53.06\t31596.75\t0\t0\t25\t0\tVERY HIGH\tFRANKLIN\t1\t1\tKNOX\nSINGLE\tUT \t0\t3\t578\t0\t3\t0\t51452\tCU15290 \tM \tSales Representative\t2500\tSouthwest\t1\t578\tNo\t0\t5\t33\t53.06\t21663\t0\t0\t25\t0\tMEDIUM\tLINCOLN\t1\t1\tMATTSON\nSINGLE\tUT \t0\t3\t0\t0\t3\t0\t63181\tCU15291 \tM \tConstruction Laborer\t2500\tSouthwest\t0\t0\tNo\t1\t5\t49\t53.07\t16195.25\t0\t0\t25\t1\tMEDIUM\tSTEPHEN\t0\t0\tCARROLL\nSINGLE\tUT \t0\t5\t117\t0\t5\t0\t66654\tCU15292 \tF \tPROF-3\t2500\tSouthwest\t1\t117\tNo\t0\t5\t21\t53.06\t21263.5\t0\t0\t25\t0\tMEDIUM\tCEOLA\t1\t1\tHARRISON\nSINGLE\tUT \t0\t3\t0\t250\t3\t0\t61716\tCU15294 \tM \tProgrammer/Developer\t1500\tSouthwest\t0\t0\tNo\t3\t5\t26\t53.04\t13529\t0\t2\t25\t2\tLOW\tLLOYD\t0\t0\tHOLLEY\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Select some categorical columns","message":["%sql","","SELECT CUSTOMER_ID, PROFESSION, MARITAL_STATUS, GENDER, LTV_BIN, STATE","FROM CUSTOMER_INSURANCE_LTV","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714430825366,"interpreter":"sql.low","endTime":1714430825461,"results":[{"message":"CUSTOMER_ID\tPROFESSION\tMARITAL_STATUS\tGENDER\tLTV_BIN\tSTATE\nCU15154 \tNurse\tSINGLE\tM \tMEDIUM\tCA \nCU15155 \tProgrammer/Developer\tSINGLE\tM \tMEDIUM\tNY \nCU15157 \tProgrammer/Developer\tMARRIED\tM \tHIGH\tMI \nCU15286 \tFireman\tMARRIED\tF \tMEDIUM\tUT \nCU15287 \tPROF-26\tMARRIED\tF \tVERY HIGH\tUT \nCU15289 \tProfessor\tMARRIED\tM \tVERY HIGH\tUT \nCU15290 \tSales Representative\tSINGLE\tM \tMEDIUM\tUT \nCU15291 \tConstruction Laborer\tSINGLE\tM \tMEDIUM\tUT \nCU15292 \tPROF-3\tSINGLE\tF \tMEDIUM\tUT \nCU15294 \tProgrammer/Developer\tSINGLE\tM \tLOW\tUT \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Unpivot categorical columns to create transaction-like records","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE CUST_CATS';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","CREATE TABLE CUST_CATS AS ","SELECT customer_id, key, val","FROM CUSTOMER_INSURANCE_LTV","UNPIVOT("," val"," FOR key"," IN (PROFESSION,"," MARITAL_STATUS, "," GENDER,"," LTV_BIN,"," STATE)",");"],"enabled":true,"result":{"startTime":1714430825536,"interpreter":"script.low","endTime":1714430826538,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable CUST_CATS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Overview of the transactional form ","message":["%sql","","SELECT * ","FROM CUST_CATS","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714430826615,"interpreter":"sql.low","endTime":1714430826711,"results":[{"message":"CUSTOMER_ID\tKEY\tVAL\nCU15154 \tPROFESSION\tNurse\nCU15154 \tMARITAL_STATUS\tSINGLE\nCU15154 \tGENDER\tM \nCU15154 \tLTV_BIN\tMEDIUM\nCU15154 \tSTATE\tCA \nCU15155 \tPROFESSION\tProgrammer/Developer\nCU15155 \tMARITAL_STATUS\tSINGLE\nCU15155 \tGENDER\tM \nCU15155 \tLTV_BIN\tMEDIUM\nCU15155 \tSTATE\tNY \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create a nested column for categorical columns","message":["%script ","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE CUST_NESTED_COL_CAT';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","CREATE TABLE CUST_NESTED_COL_CAT "," NESTED TABLE cat_nested store AS cat_nested_tab AS ","SELECT m.customer_id, m.BUY_INSURANCE, cat_nested"," FROM CUSTOMER_INSURANCE_LTV m,"," ( SELECT features.customer_id,"," cast(COLLECT(dm_nested_categorical(features.key,features.val)) "," as dm_nested_categoricals) cat_nested"," FROM CUST_CATS features"," GROUP BY features.customer_id) n"," WHERE m.customer_id = n.customer_id(+) "],"enabled":true,"result":{"startTime":1714430826792,"interpreter":"script.low","endTime":1714430834300,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable CUST_NESTED_COL_CAT created.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Unpivot numerical columns to create transaction like records","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE CUST_NUMS';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","CREATE TABLE CUST_NUMS AS ","SELECT customer_id, key, val","FROM CUSTOMER_INSURANCE_LTV","UNPIVOT("," val "," FOR key "," IN (credit_balance, "," bank_funds,"," mortgage_amount,"," income,"," num_dependents)",");"],"enabled":true,"result":{"startTime":1714430834377,"interpreter":"script.low","endTime":1714430835026,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable CUST_NUMS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Check the transactional form","message":["%sql","","SELECT * ","FROM CUST_NUMS","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714430835106,"interpreter":"sql.low","endTime":1714430835205,"results":[{"message":"CUSTOMER_ID\tKEY\tVAL\nCU15154 \tCREDIT_BALANCE\t0\nCU15154 \tBANK_FUNDS\t0\nCU15154 \tMORTGAGE_AMOUNT\t0\nCU15154 \tINCOME\t65871\nCU15154 \tNUM_DEPENDENTS\t3\nCU15155 \tCREDIT_BALANCE\t0\nCU15155 \tBANK_FUNDS\t290\nCU15155 \tMORTGAGE_AMOUNT\t0\nCU15155 \tINCOME\t68747\nCU15155 \tNUM_DEPENDENTS\t4\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create nested column for numerical columns","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE CUST_NESTED_COL_NUM';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","CREATE TABLE CUST_NESTED_COL_NUM"," NESTED TABLE num_nested store AS num_nested_tab AS ","SELECT m.customer_id, m.BUY_INSURANCE, num_nested"," FROM CUSTOMER_INSURANCE_LTV m,"," ( SELECT features.customer_id,"," cast(COLLECT(dm_nested_numerical(features.key,features.val)) "," as dm_nested_numericals) num_nested"," FROM CUST_NUMS features"," GROUP BY features.customer_id) n"," WHERE m.customer_id = n.customer_id(+) "],"enabled":true,"result":{"startTime":1714430835281,"interpreter":"script.low","endTime":1714430841788,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable CUST_NESTED_COL_NUM created.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"Create a view that combines both the numerical and categorical columns","message":["%sql","","CREATE OR REPLACE VIEW CUST_NESTED_V AS","SELECT c.CUSTOMER_ID, c.BUY_INSURANCE, c.CAT_NESTED, n.NUM_NESTED","FROM CUST_NESTED_COL_CAT c","join CUST_NESTED_COL_NUM n","on c.CUSTOMER_ID = n.CUSTOMER_ID"],"enabled":true,"result":{"startTime":1714430841866,"interpreter":"sql.low","endTime":1714430842019,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create train/test split views","message":["%script","","CREATE OR REPLACE VIEW TRAIN_DATA_CLAS AS ","SELECT * from CUST_NESTED_V","WHERE CUSTOMER_ID < 'CU8';","","CREATE OR REPLACE VIEW TEST_DATA_CLAS AS ","SELECT * from CUST_NESTED_V","WHERE CUSTOMER_ID >= 'CU8';"],"enabled":true,"result":{"startTime":1714430842101,"interpreter":"script.low","endTime":1714430842255,"results":[{"message":"\nView TRAIN_DATA_CLAS created.\n\n\n---------------------------\n\nView TEST_DATA_CLAS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Generalized Linear Model","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('NESTED_GLM_CLAS');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;","","BEGIN"," "," v_setlst(dbms_data_mining.algo_name) := dbms_data_mining.algo_generalized_linear_model;"," v_setlst(dbms_data_mining.prep_auto) := dbms_data_mining.prep_auto_on;"," "," DBMS_DATA_MINING.CREATE_MODEL2("," model_name => 'NESTED_GLM_CLAS',"," mining_function => dbms_data_mining.classification,"," data_query => 'select * from TRAIN_DATA_CLAS',"," set_list => v_setlst,"," case_id_column_name => 'CUSTOMER_ID',"," target_column_name => 'BUY_INSURANCE');","END;","/ "],"enabled":true,"result":{"startTime":1714430842338,"interpreter":"script.low","endTime":1714430847146,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Score on test data","message":["%sql","","SELECT CUSTOMER_ID, "," ROUND(PREDICTION_PROBABILITY(NESTED_GLM_CLAS, 'Yes' USING *),4) prob, "," PREDICTION(NESTED_GLM_CLAS USING *) PRED, buy_insurance","FROM TEST_DATA_CLAS","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714430847224,"interpreter":"sql.low","endTime":1714430847378,"results":[{"message":"CUSTOMER_ID\tPROB\tPRED\tBUY_INSURANCE\nCU8 \t0.1354\tNo\tNo\nCU80 \t0.2431\tNo\tYes\nCU8000 \t0.0619\tNo\tNo\nCU8001 \t0.0957\tNo\tNo\nCU8002 \t0.3008\tNo\tNo\nCU8003 \t0.3512\tNo\tNo\nCU8004 \t0.3092\tNo\tNo\nCU8005 \t0.267\tNo\tNo\nCU8006 \t0.3269\tNo\tNo\nCU8007 \t0.0524\tNo\tNo\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Check the columns in the model","message":["%sql","","SELECT attribute_name, attribute_type","FROM user_mining_model_attributes","WHERE model_name = 'NESTED_GLM_CLAS'","ORDER BY attribute_name;"],"enabled":true,"result":{"startTime":1714430847454,"interpreter":"sql.low","endTime":1714430847529,"results":[{"message":"ATTRIBUTE_NAME\tATTRIBUTE_TYPE\nBUY_INSURANCE\tCATEGORICAL\nCAT_NESTED\tCATEGORICAL\nNUM_NESTED\tNUMERICAL\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Check model coefficients","message":["%sql","","SELECT TARGET_VALUE, ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME, "," ATTRIBUTE_VALUE, ROUND(COEFFICIENT,4) COEFFICIENT","FROM DM$VDNESTED_GLM_CLAS","ORDER BY ABS(COEFFICIENT) DESC","FETCH FIRST 10 ROWS ONLY"," "],"enabled":true,"result":{"startTime":1714430847604,"interpreter":"sql.low","endTime":1714430847703,"results":[{"message":"TARGET_VALUE\tATTRIBUTE_NAME\tATTRIBUTE_SUBNAME\tATTRIBUTE_VALUE\tCOEFFICIENT\nYes\tCAT_NESTED\tSTATE\tAZ \t-6.7106\nYes\tCAT_NESTED\tPROFESSION\tPROF-58\t-2.6967\nYes\t\t\t\t-2.2626\nYes\tCAT_NESTED\tPROFESSION\tPROF-29\t-1.8415\nYes\tCAT_NESTED\tPROFESSION\tPROF-41\t-1.7417\nYes\tCAT_NESTED\tPROFESSION\tPROF-60\t-1.5123\nYes\tCAT_NESTED\tPROFESSION\tPROF-39\t-1.4629\nYes\tCAT_NESTED\tPROFESSION\tPROF-54\t-1.4328\nYes\tCAT_NESTED\tPROFESSION\tPROF-52\t-1.3154\nYes\tCAT_NESTED\tSTATE\tUT \t-1.2761\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get prediction details","message":["%sql","","SELECT CUSTOMER_ID,"," PREDICTION,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE","FROM ("," SELECT CUSTOMER_ID,"," PREDICTION(NESTED_GLM_CLAS USING *) PREDICTION,"," PREDICTION_DETAILS(NESTED_GLM_CLAS, 'Yes' USING *) PD"," FROM TEST_DATA_CLAS"," ORDER BY CUSTOMER_ID"," ) OUT,"," XMLTABLE('/Details'"," PASSING OUT.PD"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]') "," OUTPRED"," FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1714430847788,"interpreter":"sql.low","endTime":1714430848017,"results":[{"message":"CUSTOMER_ID\tPREDICTION\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\tTHIRD_ATTRIBUTE\nCU8 \tNo\t\""CAT_NESTED".MARITAL_STATUS\" actualValue=\"DIVORCED\" weight=\".061\" \t\""NUM_NESTED".INCOME\" actualValue=\"60883\" weight=\".05\" \t\""NUM_NESTED".MORTGAGE_AMOUNT\" actualValue=\"3000\" weight=\"-.002\" \nCU80 \tNo\t\""CAT_NESTED".MARITAL_STATUS\" actualValue=\"MARRIED\" weight=\".076\" \t\""NUM_NESTED".INCOME\" actualValue=\"51631\" weight=\".071\" \t\""NUM_NESTED".BANK_FUNDS\" actualValue=\"3320\" weight=\".048\" \nCU8000 \tNo\t\""NUM_NESTED".INCOME\" actualValue=\"64707\" weight=\".025\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"HIGH\" weight=\".014\" \t\""NUM_NESTED".NUM_DEPENDENTS\" actualValue=\"3\" weight=\".002\" \nCU8001 \tNo\t\""NUM_NESTED".INCOME\" actualValue=\"66248\" weight=\".039\" \t\""CAT_NESTED".MARITAL_STATUS\" actualValue=\"MARRIED\" weight=\".034\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"MEDIUM\" weight=\".018\" \nCU8002 \tNo\t\""CAT_NESTED".PROFESSION\" actualValue=\"Programmer/Developer\" weight=\".17\" \t\""NUM_NESTED".INCOME\" actualValue=\"57709\" weight=\".092\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"HIGH\" weight=\".054\" \nCU8003 \tNo\t\""CAT_NESTED".PROFESSION\" actualValue=\"Programmer/Developer\" weight=\".192\" \t\""NUM_NESTED".INCOME\" actualValue=\"69082\" weight=\".119\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"HIGH\" weight=\".059\" \nCU8004 \tNo\t\""CAT_NESTED".MARITAL_STATUS\" actualValue=\"DIVORCED\" weight=\".123\" \t\""NUM_NESTED".INCOME\" actualValue=\"61964\" weight=\".099\" \t\""CAT_NESTED".GENDER\" actualValue=\"F \" weight=\".068\" \nCU8005 \tNo\t\""NUM_NESTED".INCOME\" actualValue=\"62276\" weight=\".09\" \t\""CAT_NESTED".MARITAL_STATUS\" actualValue=\"MARRIED\" weight=\".082\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"HIGH\" weight=\".05\" \nCU8006 \tNo\t\""CAT_NESTED".PROFESSION\" actualValue=\"Programmer/Developer\" weight=\".182\" \t\""NUM_NESTED".INCOME\" actualValue=\"61397\" weight=\".102\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"LOW\" weight=\".047\" \nCU8007 \tNo\t\""NUM_NESTED".INCOME\" actualValue=\"63429\" weight=\".021\" \t\""CAT_NESTED".LTV_BIN\" actualValue=\"MEDIUM\" weight=\".01\" \t\""NUM_NESTED".NUM_DEPENDENTS\" actualValue=\"1\" weight=\".001\" \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","## End of Script"],"enabled":true,"result":{"startTime":1714430848106,"interpreter":"md.low","endTime":1714430848168,"results":[{"message":"<h2 id=\"end-of-script\">End of Script<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%script"],"enabled":true,"result":{"startTime":1714430848246,"interpreter":"script.low","endTime":1714430848307,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]
