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
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.