Load Aster Table to Teradata DB | Advanced SQL Engine | Teradata Vantage - Example: Loading Aster Table to Advanced SQL Engine Using ODBC - Teradata® Database

Code for Creating and Populating a Table on Aster Database

This code creates and populates a training table, aster_nb_trainerSC, and then uses the training table and Naive Bayes Classifier function to create a model table, aster_nb_modelSC.

/* Create training table */

DROP TABLE IF EXISTS aster_nb_trainerSC;

CREATE TABLE aster_nb_trainerSC (
  id     INT,
  year   INT,
  color  VARCHAR (100),
  type   VARCHAR (100),
  origin VARCHAR (100),
  stolen VARCHAR (100),
  PARTITION KEY (id)
);

/* Populate training table */

INSERT INTO aster_nb_trainerSC VALUES
  (1,3,'red','sports','domestic','Yes'),
  (2,9,'red','sports','domestic','No'),
  (3,1,'red','sports','domestic','Yes'),
  (4,8,'yellow','sports','domestic','No'),
  (5,2,'yellow','sports','imported','Yes');

/* Create model table from training table */

DROP TABLE IF EXISTS aster_nb_modelSC;

CREATE TABLE aster_nb_modelSC distribute by hash(class_nb) AS (
  SELECT * FROM naiveBayesReduce (
    ON (
      SELECT * FROM naiveBayesMap (
        ON aster_nb_trainerSC 
        Response ('stolen') 
        NumericInputs ('year') 
        CategoricalInputs ('color','origin','type')
      )
    ) PARTITION BY class_nb
  )
);

Code for Creating an Advanced SQL Engine User

CREATE USER sample_user AS
  PASSWORD = sample_user
  PERM = 10e6*(HASHAMP()+1);

GRANT ALL ON dbc TO sample_user;

tpt Script, astermodel.tpt

DEFINE JOB PRODUCT_SOURCE_LOAD
DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'
(
  DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
    DESCRIPTION 'PRODUCT INFORMATION SCHEMA'
    (
    class_nb VARCHAR(128),
    variable_nb VARCHAR(128),
    type_nb VARCHAR(128),
    category VARCHAR(32),
    cnt BIGINT,
    sum_nb FLOAT,
    sum_sq FLOAT,
    total_cnt BIGINT
    );

  STEP STEP_CREATE_DDL
  (
    APPLY
      ('DROP TABLE '||@TargetTable||';'),
      ('CREATE MULTISET TABLE '||@TargetTable||'(
         class_nb VARCHAR(128),
         variable_nb VARCHAR(128),
         type_nb VARCHAR(128),
         category VARCHAR(32),
         cnt BIGINT,
         sum_nb FLOAT,
         sum_sq FLOAT,
         total_cnt BIGINT) NO PRIMARY INDEX;
      ')
      TO OPERATOR ($DDL()[1]);
    );
    Step Insert Tables
    (
      APPLY
        ('Ins '||@TargetTable||'(
          :"class_nb"
          ,:"variable_nb"
          ,:"type_nb"
          ,:"category"
          ,:"cnt"
          ,:"sum_nb"
          ,:"sum_sq"
          ,:"total_cnt");'
        )
        TO OPERATOR ($LOAD()[1])

    SELECT * FROM OPERATOR ($ODBC(PRODUCT_SOURCE_SCHEMA)[1]);
  );
);

JobVariablesFile, attr.txt

DDLTdpid = 'td_host_name_or_ip'
,DDLUserName = 'alice'
,DDLUserPassword = 'alice'
,DDLErrorList = '[3807]'
,DDLPrivateLogName = 'DDL001S1'
,TargetTable = 'td_nb_modelsc'
,ODBCPrivateLogName = 'ODBC039P1'
,ODBCDSNName = 'shruti'
,TruncateData = 'Y'
,ODBCUserName = 'db_superuser'
,ODBCUserPassword = 'db_superuser'
,LOADPrivateLogName = 'ODBC039P1'
,LOADTDPID = 'td_host_name_or_ip'
,LOADUserName = 'alice'
,LOADUserPassword = 'alice'
,SelectStmt = 'SELECT * FROM aster_nb_modelsc;'
,LOADTargetTable = 'td_nb_modelsc'

Command for Running the tpt Script

tbuild -f aster_model.tpt -v attr.txt -j urr1