17.05 - Loading Aster Tables to Advanced SQL Engine Using load_to_teradata - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K

For load_to_teradata instructions, see Teradata Aster® Database User Guide and the following usage notes.

load_to_teradata Usage Notes

  • If a table column name includes a keyword, enclose the name in double quotation marks and alias it.
  • In SELECT statements, enclose every camel-case table column name in double quotation marks.

    This example shows both aliased columns and camel-case column names:

    SELECT * FROM load_to_teradata (
      ON (
        SELECT "class" AS class_col,
               "variable" AS variable_col,
               "type" AS type_col,
               category,
               cnt,
               "sum" AS sum_col,
               "sumSq",
               "totalCnt"
        FROM aster_nb_modelSC
      )
      tdpid ('sdt12432.labs.teradata.com')
      username ('sample_user')
      password ('sample_user')
      target_table ('td_nb_modelSC')
    );
  • Cast every REAL column to DOUBLE PRECISION.

    For example:

    SELECT * FROM load_to_teradata (
      ON (
        SELECT attribute,
               predictor,
               category,
               CAST (estimate AS DOUBLE PRECISION) AS estimate,
               CAST (std_err AS DOUBLE PRECISION) AS std_err,
               CAST (z_score AS DOUBLE PRECISION) AS z_score,
               CAST (p_value AS DOUBLE PRECISION) AS p_value,
               significance,
               "family"
        FROM glm_housing_model
      )
      tdpid ('sdt12432.labs.teradata.com')
      username ('sample_user')
      password ('sample_user')
      target_table ('glm_housing_model')
    );
  • If a model table column name contains Advanced SQL Engine reserved keywords or special characters— characters other than letters, digits, or underscore (_)—enclose it in double quotation marks.

    This rule applies to the following model column names:

    AA 7.00 Function Model Column Name
    Single_Tree_Drive
    • node_gini(p)
    • node_entropy(p)
    • node_chisq_pv(p)
    • split_gini(p)
    • split_entropy(p)
    • split_chisq_pv(p)
    NaiveBayesReduce
    • class
    • variable
    • type
    • sum
    • sumSq
    • totalCnt

    For example:

    CREATE SET TABLE NBUSER.td_glass_modelPD1,
      FALLBACK,
      NO BEFORE JOURNAL,
      NO AFTER JOURNAL,
      CHECKSUM = DEFAULT,
      DEFAULT MERGEBLOCKRATIO,
      MAP = TD_MAP1 (
        node_id BIGINT,
        node_size BIGINT,
        "node_gini(p)" FLOAT,
        node_entropy FLOAT,
        node_chisq_pv FLOAT,
        node_label VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        node_majorvotes BIGINT,
        split_value FLOAT,
        "split_gini(p)" FLOAT,
        split_entropy FLOAT,
        split_chisq_pv FLOAT,
        left_id BIGINT,
        left_size BIGINT,
        left_label VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        left_majorvotes BIGINT,
        right_id BIGINT,
        right_size BIGINT,
        right_label VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        right_majorvotes BIGINT,
        left_bucket VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        right_bucket VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        left_label_problist VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        right_label_problist VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        prob_label_order VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
        attribute VARCHAR(2048) CHARACTER SET UNICODE NOT CASESPECIFIC,
      )
      PRIMARY INDEX (node_id);