Aliases are added to the field names when loading from Cloudera with Data Gateway

[Andrea_Bertazzo]

The problem occurs when running a reload from Data Gateway to a Cloudera DB (Cloudera Hive).

The reload is completed correctly, but the fields are imported with aliases. This means that the reload imports the fields as 'TableName.FieldName' instead of importing them as just 'FieldName'.

For example, with this script:

LOAD meter_id,
Field1,
Field2,
Field3;

[meter_attribute]:
SELECT "meter_id",
"Field1",
"Field2",
"Field3"
FROM internaldb."meter_attribute";

Fields are imported as: meter_attribute.Field1, meter_attribute.Field2, meter_attribute.Field3.

Resolution

The problem can be fixed adding the row EnableUniqueColumnName=0 to the connection string.

1. Edit your Cloudera ODBC connection
2. Locate Connection string
3. Add EnableUniqueColumnName=0, separating it with a semicolon (;) from the previous parameters: 
   
   
   

 

Additional Notes:

• It is not necessary to select the  "Allow non-SELECT queries" option in the connector.
• In order to make Data Preview work reliably with any new Cloudera connections, we recommend adding the following syntax to its syntax.json file:
  
  
  1. Open C:\ProgramData\Qlik\Gateway\genericodbc_database_syntax.json in a text editor with elevated permissions (administrator)
     
     
  2. Add:
     

       {
         "SyntaxId": "ClouderaHive",
         "DisplayName": "Cloudera Hive syntax",
         "DelimiterStart": "`",
         "DelimiterEnd": "`",
         "DataPreviewSelectTemplate": "SELECT ${COLUMN_LIST} FROM ${TABLE_NAME} ${FILTER} LIMIT ${LIMIT_VALUE}",
         "DatabaseTerm": "catalog",
         "OwnerTerm": "schema"
       }

  3. Restart the Data Gateway service.
     
     This snippet re-configures all the custom syntax settings for the driver.
     
     Please, notice that the block must have a comma (,) either immediately before or after it as shown in the screenshot below.
     
     
     
     
  4. Once restarted, open the data connection editor again and the "Cloudera Hive syntax" option should appear, as in this image:
     
     

Internal Investigation ID

QB-26342

Environment

• Qlik Cloud
• Qlik Data Gateway Direct Access all versions