<jupyter_start><jupyter_text>teradataml preparing the features and training the model.  Connect to Vantage<jupyter_code>#import the teradataml package for Vantage access
from teradataml import *
import getpass
from teradataml import display
#display.print_sqlmr_query=True
from sqlalchemy.sql.expression import select, case as case_when, func
from sqlalchemy import TypeDecorator, Integer, String
import warnings
warnings.filterwarnings('ignore')
Vantage = 'tdap1627t2.labs.teradata.com'
User = 'alice'
Pass = '<PASSWORD>'
Vantage = '10.25.251.121'
User = 'USER10'
Pass = '<PASSWORD>'
#Pass = getpass.getpass(prompt="pwd: ")
print(Vantage,User)
con = create_context(Vantage, User, Pass)<jupyter_output><empty_output><jupyter_text>Prepare the explain text feature<jupyter_code># Data Set Selection
dbqlog = DataFrame.from_table(in_schema("dbc", "dbqlogtbl")).drop("ZoneId", axis = 1)
dbqlexplain = DataFrame.from_table(in_schema("dbc", "dbqlexplaintbl")).drop("ZoneID", axis = 1)
dbqldata = dbqlog.join(other = dbqlexplain, on = ["QueryID"], lsuffix = "t1", rsuffix = "t2") \
    .select(['t1_QueryID','ExplainText','QueryBand','QueryText'])
# Workaround until ELE-2072.
dbqldata.to_sql('prediction_sentiment', if_exists="replace")
dbqldata = DataFrame.from_table('prediction_sentiment')<jupyter_output><empty_output><jupyter_text>Setup training and join condition features<jupyter_code># Feature Extraction
df_select_query_column_projection = [
     dbqldata.t1_QueryID.expression.label("queryid"),
     dbqldata.ExplainText.expression.label("explaintext"),
     dbqldata.QueryBand.expression.label("queryband"),
     func.REGEXP_SUBSTR(dbqldata.QueryBand.expression, 
                        '(collected_statistics|no_statistics)', 1, 1, 'i').label("training"),
     func.REGEXP_SUBSTR(dbqldata.QueryText.expression, 
                        'SELECT', 1, 1, 'i').label("select_info"),
     func.REGEXP_SUBSTR(func.REGEXP_SUBSTR(dbqldata.ExplainText.expression, 
                        '(joined using a *[A-z \-]+ join,)', 1, 1, 'i'), 
                            '[A-z]+', 15, 1, 'i').label("join_condition")]
training_data = DataFrame.from_query(str(select(df_select_query_column_projection)
                                 .where(Column('join_condition') != None)
                                 .where(Column('training') != None)
                                 .compile(compile_kwargs={"literal_binds": True})))<jupyter_output><empty_output><jupyter_text>Filter to provide the training set and testing set<jupyter_code># Establish training data
training_data = DataFrame.from_query(str(select(df_select_query_column_projection)
                                 .compile(compile_kwargs={"literal_binds": True})))
data_filter = (training_data.join_condition != None) & (training_data.training != None)  \
        & (training_data.select_info != None)
data_set = training_data[data_filter]
# Split data set into training and testing sets
training_set = Sampling(data = data_set, sample_fraction = 0.5, seed = 2).result
testing_set = data_set.join(other = training_data, on = ["queryid<>queryid"], 
                            lsuffix = "t1", rsuffix = "t2")
training_set[training_set.training == 'collected_statistics'].count()
#testing = tsample.result.join(other = training_data, on = ["queryid<>queryid"], lsuffix = "t1", rsuffix = "t2")
#training_set = tsample.result
# Custom sentiment dictionary
dictionary = DataFrame.from_table('dbql_sentiment')
# Extract Confidience 
Features = SentimentExtractor(
    #dict_data = dictionary,
    newdata = training_set,
    level = "document",
    text_column = "explaintext",
    accumulate = ['queryid','join_condition','training']
).result
#help(SentimentExtractor)
Features.count()<jupyter_output><empty_output><jupyter_text>These are the features for training the model<jupyter_code>#features = td_sentiment_extractor_out.result<jupyter_output><empty_output><jupyter_text>Training and saving the model<jupyter_code># Train model
stats_model = NaiveBayes(
    formula="training ~ out_polarity + join_condition", 
    data=Features)
stats_model
td_save_model(model = stats_model, name = "Stats_collection_model_final")
stats_model.result.to_sql("stats_model_final", if_exists="replace")
target_collection = NaiveBayesPredict(newdata=Features,
                                       modeldata = stats_model,
                                       formula="training ~ out_polarity + join_condition", 
                                       id_col = "queryid",
                                       responses = ["collected_statistics","no_statistics"]
                                       )
summary = target_collection.result.join(other = Features, on = ["queryid"], lsuffix = "t1", 
                                        rsuffix = "t2")
ConfusionMatrix(data = summary, prediction = 'prediction', reference = 'training').accuracytable
ConfusionMatrix(data = summary, prediction = 'prediction', reference = 'join_condition').accuracytable
accuracy.accuracytable
help(NaiveBayes)<jupyter_output>Help on class NaiveBayes in module teradataml.analytics.mle.NaiveBayes:

class NaiveBayes(builtins.object)
 |  NaiveBayes(formula=None, data=None, data_sequence_column=None, data_order_column=None)
 |  
 |  Methods defined here:
 |  
 |  __init__(self, formula=None, data=None, data_sequence_column=None, data_order_column=None)
 |      DESCRIPTION:
 |          The NaiveBayesMap and NaiveBayesReduce functions generate a model from
 |          training data. A virtual data frame of training data is input to
 |          the NaiveBayesMap function, whose output is the input to
 |          NaiveBayesReduce function, which outputs the model.
 |      
 |      
 |      PARAMETERS:
 |          formula:
 |              Required Argument.
 |              A string consisting of "formula". Specifies the model to be fitted. Only
 |              basic formula of the "col1 ~ col2 + col3 +..." form is supported and
 |              all variables must be from the same virtual data frame object. The
 |    [...]
{'fraction_non_alphanumeric': 0.11116617112652792, 'fraction_numerical': 0.007928642220019821, 'mean_word_length': 3.629204892966361, 'pattern_counts': {'":': 2, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '511837', 'license': 'Apache-2.0', 'n_tokens_mistral': 1871, 'n_tokens_neox': 1811, 'n_words': 379, 'path': 'MoveModel.ipynb', 'repo_name': 'dougfy/teradataml_examples_1794', 'size': '6045'}