The HDS functionality not working correctly when using Qlik Compose 2021.8, Data Lake project

[Nanda_Ravindra]

Qlik Compose 2021.8, Data Lake project, AWS EMR Parquet, the HDS functionality only shows the latest record without history.

Resolution

The issue is isolated to EMR versions 6.2 or greater. Anything less than 6.2 (in the 6. x family) is working as expected.

Workaround

Pass the hive.auto.convert.join=false parameter in the connection URL of the client (DBeaver, DBViz, etc) that is querying the data, not the EMR cluster config

Example: jdbc:hive2://my.cluster.ip:10000/;hive.auto.convert.join=false

Note: The above parameter is only recognized by later versionsof the JDBC drive (Hive JDBC - 2.6.19.1022)

Cause

We've been able to pinpoint it to an issue with the following view query:

CREATE OR REPLACE VIEW `demo_storage_v_internal`.`demo_customers_applied_archive` AS WITH `last_applied_ts` AS ( (SELECT COALESCE(MAX(`apply_time`), TIMESTAMP('1780-01-01')) `hdr__last_applied_ts` FROM `demo_storage`.`qlik_cmps_status` WHERE `table_name` = 'demo_customers' AND `table_owner` = 'demo_landing' AND `compose_task_name` = 'Demo_CDC' ) ) , `archive_with_max_partition` AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY `customer_id`, `hdr__ts` ORDER BY `hdr__modified_batch` DESC ) `hdr__rank` FROM `demo_storage`.`demo_customers_archive` FULL JOIN `last_applied_ts` ) SELECT `hdr__modified_batch` , `hdr__oper` , `hdr__ts` , `customer_id` , `cust_first_name` , `cust_last_name` , `cust_street_address1` , `cust_street_address2` , `cust_city` , `cust_state` , `cust_postal_code` , `phone_number1` , `phone_number2` , `credit_limit` , `cust_email` , `hdr__to_ts` , `hdr__archive_ts` FROM `archive_with_max_partition` WHERE UNIX_TIMESTAMP(`hdr__archive_ts`) <= UNIX_TIMESTAMP(`hdr__last_applied_ts`) AND `hdr__rank` = 1;

The clause "FULL JOIN `last_applied_ts`" makes the last_applied_ts come back null even though it always should have a value.

The issue is isolated to EMR versions 6.2 or greater. Anything less than 6.2 (in the 6.x family) is working as expected.

Environment

Qlik Compose DataLake projects - 2021.8, x