Identifiers: Comparing Advanced SQL Engine and ML Engine | Teradata Vantage - Identifiers - Teradata Vantage

Machine Learning User Guide

Product
Teradata Vantage
Release Number
9.01
1.3
Published
August 2020
Language
English (United States)
Last Update
2020-10-02
dita:mapPath
dci1595445931473.ditamap
dita:ditavalPath
dqp1599597541027.ditaval
dita:id
B700-4004
lifecycle
previous
Product Category
Teradata Vantage™

There are differences in the way Advanced SQL Engine and ML Engine interpret identifiers:

Identifier Advanced SQL Engine Interpretation ML Engine Interpretation
Defining regular identifiers. For example, mixedCaseIdentifier. Case is preserved: mixedCaseIdentifier. Case is not preserved. The identifier is stored as lowercase: mixedcaseidentifier.
Using regular identifiers. For example, defined as mixedCaseIdentifier. Not case sensitive. These are all equivalent: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER. Not case sensitive. These are all equivalent: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER.
Defining delimited identifiers. For example, "mixedCaseIdentifier". Case is preserved: mixedCaseIdentifier. Case is preserved: mixedCaseIdentifier.
Using delimited identifiers. For example, defined as "mixedCaseIdentifier". Not case sensitive. These are all equivalent: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER. Case sensitive. These are all different: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER.

When an ML Engine function execution request is sent to ML Engine, any column name of the input table is presented to ML Engine either implicitly as it is named in the input table, or explicitly in the column-list of the input subquery SELECT expression, and also in any function argument that expects a column name. A connector property named Column Name Handling controls the case-sensitivity of column names as they are presented to ML Engine. It takes one of two values, CASE-INSENSITIVE or CASE-SENSITIVE. The default value is CASE-INSENSITIVE. For information about setting connector properties, see Teradata QueryGrid Connector Properties.

  • CASE-INSENSITIVE:

    When the Column Name Handling connector property is set to CASE-INSENSITIVE, an input column name is treated as case-insensitive (output column names are always handled as case-sensitive), unless it must be quoted to be allowed as a valid column name in ML Engine. An unquoted column name in ML Engine is required to meet these restrictions:

    • The first character must be a letter or an underscore "_".
    • The remaining characters must be either a letter, a digit, an underscore, or a dollar sign.
    • It must not be a keyword that is invalid as a column name.

    These keywords are invalid as a column name:

    all datetime interval references
    analyse dec into result
    analyze decimal is right
    and default isnull select
    any deferrable join serial
    as desc leading serial4
    asc distinct left serial8
    asymmetric do like session_user
    authorization double limit similar
    autopartition else localtime smallint
    between end localtimestamp some
    bigint except national sql_no_cache
    bigserial false natural symmetric
    binary float nchar table
    bit float4 new text
    bool float8 not then
    boolean for notnull time
    both force null timestamp
    case foreign nulls timestamptz
    cast freeze numeric timetz
    cdistinct from off tinyint
    char full offset to
    character grant old trailing
    check group on true
    collate having only unique
    column ilike or user
    constraint in order using
    create initially outer varbit
    cross inner over varchar
    current_date int overlaps varying
    current_role int2 partition verbose
    current_time int4 placing when
    current_timestamp int8 precision where
    current_user integer primary with
    date intersect real without

    When a column name must be quoted to be valid for ML Engine, it will be treated as CASE-SENSITIVE regardless of Column Name Handling connector property, and there may be cases where the rules to avoid problems with delimited names will apply to this column name, as explained in the next section.

  • CASE-SENSITIVE:

    If you set the Column Name Handling property to CASE-SENSITIVE, when a function is executed, ML Engine will add quotes to delimit each column name and it will be treated as case-sensitive.

    To avoid problems with identifiers, keep the default setting of the connector property as CASE-INSENSITIVE. If that cannot be, follow these rules:

    • In tables to be used as input to an ML Engine function execution request, use only lowercase letters, digits, and underscores in column names.
    • If you cannot observe the preceding rule (for example, when an existing table has column names that break the rule), give rule-breaking columns correlation names that have only lowercase letters, digits, and underscores. Reference these columns by their correlation names in function argument clauses.

      For example, in the following query, the table ville_climatedata has two mixed-case column names, temp_F and dewpoint_F. They have lowercase correlation names in the SELECT list, and the InputColumns clause references them by their correlation names.

      SELECT * FROM DWT (
        ON (SELECT city, period, temp_F AS temp_f, pressure_mbar, 
              dewpoint_F AS dewpoint_f FROM ville_climatedata
            ) AS InputTable PARTITION BY 1
        OUT TABLE OutputTable (dwt_coef_table)
        OUT TABLE MetaTable (dwt_meta_table)
        USING 
        InputColumns ('temp_f','pressure_mbar','dewpoint_f')
        SortColumn ('period')
        PartitionColumns ('city')
        Wavelet ('db2')
        Level (2)
      ) AS dwtout;
      Alternatively, you can enclose a rule-breaking column name in double quotation marks if it meets these requirements:
      • It does not appear in an ON clause in the SQL syntax.
      • It is not passed to a function output table.
      For example, in the following query, Name and Period need correlation names because they appear in the ON clause, but StockPrice needs only to be enclosed in double quotation marks, because it appears only in an argument clause and is not passed to a function output table.
      SELECT * FROM SimpleMovAvg (
        ON (SELECT id, "Name" AS name, "Period" AS period, "StockPrice" 
              FROM ibm_stock_upper
           ) PARTITION BY name ORDER BY period
        USING
        TargetColumns ('"StockPrice"')
        WindowSize ('10')
        IncludeFirst ('true')
      ) AS dt;

Overriding the Column Name Handling Property

The Column Name Handling property can be overridden within a SQL session by setting a foreign server session attribute. For example:
set foreign server attr = 'servername=coprocessor;columnNameHandling=CASE-SENSITIVE;'for session volatile;
set foreign server attr = 'servername=coprocessor;columnNameHandling=CASE-INSENSITIVE;'for session volatile:

See Teradata® QueryGrid™ Installation and User Guide, B035-5991.