TD_GLM Examples | GLM | Teradata Vantage - Examples: How to Use TD_GLM - Analytics Database

Input Table for train_dataset

Example: TD_GLM Using Credit Data Set

The following credit data set is used in this example:

TD_GLM Call for Credit Data

CREATE VOLATILE TABLE td_glm_output_credit_ex AS (
    SELECT * FROM td_glm (
        ON credit_ex_merged AS InputTable
        USING
        InputColumns('a1', 'a2', 'a7', 'a10', 'a13', 'a14', 'a0_b', 'a0_a', 'a3_y', 'a3_u', 'a4_p', 'a4_g', 'a5_k', 'a5_cc', 'a5_d', 'a5_c', 'a5_aa', 'a5_m', 'a5_q', 'a5_w', 'a5_e', 'a5_ff', 'a5_j', 'a5_x', 'a5_i', 'a6_v', 'a6_h', 'a6_bb', 'a6_z', 'a6_ff', 'a6_j', 'a8_t', 'a8_f', 'a9_t', 'a9_f', 'a11_t', 'a11_f', 'a12_g', 'a12_s')
        ResponseColumn('Outcome')
        Family('Binomial')
        BatchSize(10)
        MaxIterNum(300)
        RegularizationLambda(0.02)
        Alpha(0.15)
        IterNumNoChange(50)
        Tolerance(0.001)
        Intercept('true')
        LearningRate('optimal')
        InitialEta(0.001)
        Momentum(0.0)
        LocalSGDIterations(0)
    ) AS dt
) WITH DATA
ON COMMIT PRESERVE ROWS
;

TD_GLM Output for Credit Data

Example: TD_GLM Using Housing Data

Raw Housing Data

TD_ScaleFit Call for Housing Data

SELECT * FROM TD_ScaleFit(
    ON cal_housing_ex_raw AS InputTable
    OUT VOLATILE TABLE OutputTable(scaleFitOut_cal_ex)
    USING
    TargetColumns('medinc', 'houseage', 'averooms', 'avebedrms', 'population', 'aveoccup', 'latitude', 'longitude')
    ScaleMethod('STD')
) AS dt2;

TD_ScaleFit Output for Housing Data

TD_ScaleTransform Call for Housing Data

CREATE MULTISET TABLE cal_housing_ex_scaled AS (
    SELECT * FROM TD_ScaleTransform(
        ON cal_housing_ex_raw AS InputTable
        ON scaleFitOut_cal_ex AS FitTable DIMENSION
        USING
        accumulate('id', 'MedHouseVal')
    ) AS dt1
) WITH data;

TD_ScaleTransform Output for Housing Data

TD_GLM Call for Housing Data

CREATE VOLATILE TABLE td_glm_cal_ex AS (
    SELECT * from TD_GLM (
        ON cal_housing_ex_scaled AS InputTable
        USING
        InputColumns('medinc', 'houseage', 'averooms', 'avebedrms', 'population', 'aveoccup', 'latitude', 'longitude')
        ResponseColumn('MedHouseVal')
        Family('Gaussian')
        BatchSize(10)
        MaxIterNum(300)
        RegularizationLambda(0.02)
        Alpha(0.15)
        IterNumNoChange(50)
        Intercept('true')
        LearningRate('invtime')
        InitialEta(0.05)
        Momentum(0)
        Nesterov('false')
        LocalSGDIterations(0)
    ) AS dt
) WITH DATA
ON COMMIT PRESERVE ROWS;

TD_GLM Output for Housing Data

Example: TD_GLM Call of Housing Data Using Forward Selection

SELECT * FROM TD_GLM (
ON cal_housing_ex_raw AS InputTable
USING
  InputColumns('[1:4]','[7:10]')
  ResponseColumn('price')
  Family('Gaussian')
  LearningRate('optimal')
  RegularizationLambda(0.02)
  Alpha(0.33)
  BatchSize(10)
  MaxIterNum (36)
  IterNumNoChange(100)
  Tolerance(0.0001)
  InitialEta(0.02)
  StepwiseDirection('forward')
  MaxStepsNum(10)
) as dt

TD_GLM Output of Housing Data Using Forward Selection

Example: TD_GLM Call of Housing Data Using Backward Deletion

SELECT * FROM TD_GLM (
ON cal_housing_ex_raw AS InputTable
OUT TABLE MetaInformationTable(logtable)
USING
  InputColumns('[3:6]')
  ResponseColumn('price')
  Family('Gaussian')
  LearningRate('optimal')
  RegularizationLambda(0.02)
  Alpha(0.33)
  BatchSize(10)
  MaxIterNum (36)
  IterNumNoChange(100)
  Tolerance(0.0001)
  InitialEta(0.02)
  StepwiseDirection('backward')
  MaxStepsNum(10)
) as dt;

TD_GLM Output of Housing Data Using Backward Deletion

Example: Regression Using One Table

TD_GLM Call with Family: Gaussian for Input Table

SELECT * FROM TD_GLM (
  ON housing_train_segment AS InputTable PARTITION BY partition_id ORDER BY sn
  USING
    Family('Gaussian')
    InputColumns('[3:10]')
    ResponseColumn('price')
    BatchSize(10)
    MaxIterNum (1000)
    IsDebug('true')
) AS dt;

TD_GLM Output with Family: Gaussian for Input Table

Example: Using an Attribute Table

TD_GLM Call with Family: Gaussian for Input Table and Attribute Table

SELECT * FROM TD_GLM (
  ON housing_train_segment AS InputTable PARTITION BY partition_id 
  ORDER BY sn
  ON housing_train_attribute AS AttributeTable PARTITION BY partition_id
  USING
    Family('Gaussian')
    InputColumns('[3:10]')
    ResponseColumn('price')
    MaxIterNum (100)
) AS dt;

TD_GLM Output for Attribute Table

Example: Using a Parameter Table

TD_GLM Call with Family: Gaussian for Input Table and Parameter Table

SELECT * FROM TD_GLM (
  ON housing_train_segment AS InputTable PARTITION BY partition_id
  ORDER BY sn
  ON housing_train_parameter AS ParameterTable PARTITION BY partition_id
  USING
    Family('Gaussian')
    InputColumns('[3:10]')
    ResponseColumn('price')
    MaxIterNum (100)
) AS dt;

TD_GLM Output for Parameter Table

Example: Classification Using TD_GLM Call with Family: Binomial for Input Table and Attribute Table

SELECT * FROM TD_GLM (
  ON housing_train_segment AS InputTable PARTITION BY partition_id
  ON housing_train_attribute AS AttributeTable PARTITION BY partition_id
  USING
    Family('Binomial')
    InputColumns('[3:10]')
    ResponseColumn('homestyle')
    MaxIterNum (100)
) AS dt;

TD_GLM Output with Family: Binomial for Input Table and Attribute Table