SYMPTOM:
When using a multi-sourced report that selects Varchar() data from Teradata and inserts into Impala, an error message will be thrown indicating a loss of precision while attempting to insert a String value. The error will also note that the requirement to cast the String values to Varchar().
STEPS TO REPRODUCE:
Using a project configured for multi-sourcing to both a Teradata and Impala database, run a report that will select Varchar() data from Teradata.
"Possible loss of precision for target table 'x'...(type: STRING) would need to be cast to VARCHAR(y) for column 'z'.
CAUSE:
This is classified as an issue from both Impala and Strategy:
Impala -https://issues.apache.org/jira/browse/IMPALA-1333
Strategy - logged as a defect
ACTION:
Currently this issue is being reviewed for an upcoming Strategy release.
WORKAROUND:
1.) Make Impala database read-only so that temp tables are generated in Teradata. Note that his will affect performance.
2.) Follow the below steps to edit the DTMAPPING.PDS:
1. Search for section "MSI_TO_DB"
2. Change the IMPALA section to the following:
<DATABASE NAME="IMPALA" VERSION="">
<MSI TYPE="DssDataTypeReserved" DBTYPE="" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeInteger" DBTYPE="SMALLINT" MAXPRECISION="2" NEXTTYPE="NO_OF_DSS_TYPE"/>
<MSI TYPE="DssDataTypeUnsigned" DBTYPE="SMALLINT" MAXPRECISION="2" NEXTTYPE="DssDataTypeInteger"/>
<MSI TYPE="DssDataTypeNumeric" DBTYPE="DECIMAL(#p, #s)" MAXPRECISION="0" NEXTTYPE="NO_OF_DSS_TYPE+2"/>
<MSI TYPE="DssDataTypeDecimal" DBTYPE="DECIMAL(#p, #s)" MAXPRECISION="0" NEXTTYPE="NO_OF_DSS_TYPE+3"/>
<MSI TYPE="DssDataTypeReal" DBTYPE="DOUBLE" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeDouble" DBTYPE="DOUBLE" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeFloat" DBTYPE="FLOAT" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeChar" DBTYPE="STRING" MAXPRECISION="255" NEXTTYPE="DssDataTypeVarChar"/>
<MSI TYPE="DssDataTypeVarChar" DBTYPE="STRING" MAXPRECISION="255" NEXTTYPE="DssDataTypeLongVarChar"/>
<MSI TYPE="DssDataTypeLongVarChar" DBTYPE="STRING" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeBinary" DBTYPE="BOOLEAN" MAXPRECISION="1" NEXTTYPE="DssDataTypeVarBin"/>
<MSI TYPE="DssDataTypeVarBin" DBTYPE="STRING" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeLongVarBin" DBTYPE="STRING" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeDate" DBTYPE="TIMESTAMP" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeTime" DBTYPE="TIMESTAMP" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeTimeStamp" DBTYPE="TIMESTAMP" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeNChar" DBTYPE="STRING" MAXPRECISION="255" NEXTTYPE="DssDataTypeVarChar"/>
<MSI TYPE="DssDataTypeNVarChar" DBTYPE="STRING" MAXPRECISION="255" NEXTTYPE="DssDataTypeLongVarChar"/>
<MSI TYPE="DssDataTypeInteger" DBTYPE="INTEGER" MAXPRECISION="4" NEXTTYPE="NO_OF_DSS_TYPE+1"/>
<MSI TYPE="DssDataTypeInteger" DBTYPE="BIGINT" MAXPRECISION="8" NEXTTYPE="DssDataTypeNumeric"/>
<MSI TYPE="DssDataTypeNumeric" DBTYPE="DECIMAL(#p, #s)" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
<MSI TYPE="DssDataTypeDecimal" DBTYPE="DECIMAL(#p, #s)" MAXPRECISION="DDT_NO_MAXPRECISION" NEXTTYPE="DDT_NULL_NEXTTYPE"/>
</DATABASE>
3. Change the "StringColumnLength" property for the Impala DSN within the Advanced Options on Windows ODBC Administrator, or in the odbc.ini file on Linux, to the max string column length on the affected table. By default, this value is set to "32767", but a recommended value to start with is "4000" as the default value may be too large.
4. Restart I-Server.
5. Re-execute the report.
