topic Re: Where condition not working in HIVE connection in App Development

Where condition not working in HIVE connection

vinodkumar — Fri, 15 Nov 2024 21:38:09 GMT

Hello All,

I'm facing a very typical problem with where condition in my Load Script. Below load script i'm using, without where condition it is working fine but with where condition only it is getting failed, i'm using HIVE connection.

I have tried by changing the createdtime datatype but no luck

Load Script:

LOAD createdtime

from [Source_Table]

Where createdtime>'$(latesttime)';

It would be very helpful if someone guides me with the where condition.

Thanks!

Re: Where condition not working in HIVE connection

marcus_sommer — Wed, 28 Jun 2023 14:48:55 GMT

At first replace the variable with a fixed value - of course with a valid value and with the right formatting/datatype for your createdtime. If this worked you could put a Trace '$(latesttime)'; before the load-statement - if it's exactly the same like your fixed value the entire approach should work.

Re: Where condition not working in HIVE connection

vinodkumar — Wed, 28 Jun 2023 15:13:48 GMT

Thanks for the replay Marcus,

Tried that way also, passed value instead of variable and the format also same, but not working, getting error like timeout error.

Re: Where condition not working in HIVE connection

marcus_sommer — Wed, 28 Jun 2023 15:34:08 GMT

This could have several causes, for example the value/format/datatype isn't valid and/or the used driver/connector couldn't handle it properly respectively is the wrong one and/or also that the database needs to much time to return any data within the available time-frame. Such timeout may not be mandatory caused from the data-base or the Qlik side else it might be your network by terminating the connection after n seconds without any traffic.

Before diving deeper in driver/network topics I would play with simple filters on other fields and/or applying any cast-statements, for example to filter against a year/month and/or using top/first statements to reduce the number of records. Each trial may give valuable hints to your current issue.

Re: Where condition not working in HIVE connection

vinodkumar — Wed, 05 Jul 2023 11:25:10 GMT

Hi Marcus,

Thanks for replay, I have tried to pass different conditions like taken the other columns instead of datetime in where but that is also not working, getting below error.

Error : ERROR [HYT00] [Qlik][Hardy] (72) Query execution timeout expired.

Re: Where condition not working in HIVE connection

marcus_sommer — Wed, 05 Jul 2023 12:45:30 GMT

If querying an entire table without any transformation worked but not with a simple filtering like > date it indicates an issue with the driver (unable to handle the query properly) or too small responsive times within the network or an unsuitable configured data-base (queuing the task or waiting of finishing the complete query before providing the results, ...).

Like above already hinted trying various queries with/without where-clauses and/or direct transformation in the query on different tables/fields would provide a lot of valuable hints to what worked or not and where and when (after which time) occur errors. If you have answers to a few dozens of such trials you may look for another driver and/or get in touch with the responsible data-base and network guys (they will probably ask the same questions to you respectively your attempts).