You can translate the question and the replies:

Denodo consuming Impala array datatype

We are trying to connect to a Impala table through Denodo. We are able to connect to Impala and see the underlying table. However when we create a base view, the "array" field in underlying Impala table is coming as "text" field. For example below "testarray" field testarray:- array<struct< abc:string, date:string, detail:struct< xyz:string, name:string >, duration:string, id:string, methodname:string, tidname:string, compid:string, payid:string, mats:array<struct< xid:string, xilid:string, xname:string >> >> comes as just "testarray" text field in base view. Consequently, when we try to run this base view it gives the error that "Only scalar types are allowed in the select list." How can we connect to Impala so the underlying complex types comes as complex types in Denodo also?
Impala
user
28-08-2020 12:35:10 -0400
code

3 Answers

Hi, By introspection, Denodo should be able to identify the data type of the field to array type. Potentially, it could be that the version of Impala you are connecting to is higher than the Impala 2.3. Therefore I would suggest downloading the respective JDBC drivers for Impala from vendor’s site and placing it into the directory <DENODO_HOME>/lib-external/jdbc-drivers/impala-2.3. Choose “Impala 2.3” in the Database Adapter list when creating the datasource connection and try creating the base view again. Hope this helps!
Denodo Team
31-08-2020 03:22:31 -0400
code
Hi, I had followed the same process as you mentioned in your answer (downloaded the driver, put it in Imapala 2.3 directory and use that adaptor). The only thing is that I downloaded the lastest JDBC driver from Cloudera site as my understanding is the drivers are backwards compatible. Our Impala DB is: impala version 2.12.0-cdh5.15.1. Does the JDBC version make a difference here?
user
01-09-2020 11:04:46 -0400
Hi, It seems that Impala queries the complex data types in a different manner and querying the fields with complex data directly will raise an error, Impala does not support this type of queries due their limited support to complex data. The following workaround, that uses Impala features to query compound types and Denodo JDBC base views from query would be suggested: 1. Execute a describe on the column with the array type (where my_table is the table that has the array type column: `DESCRIBE my_table.array_column` The following result or similar is expected: ``` +------+--------+ | name | type | +------+--------+ | item | string | | pos | bigint | +------+--------+ ``` 1. Create a base view from query like the following query: ``` SELECT double_column, string_column, array_column.pos, array_column.item FROM my_database.my_table, my_database.my_table.array_column; ``` Where the **from** clause contains a join between the table and its own column, and any table fields can be projected (like *double_column* and *string_column* in the example) A VDP base view that retrieves a flatten of the array and the rest of the projected fields should be obtained. Hope this helps!
Denodo Team
10-09-2020 03:13:47 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here