Hive SQL issue for On-Demand app generation

[Danny_Langley]

Hi everyone,

I know it's a Saturday, but I'm really hoping there are some losers like me out there doing some work/monitoring the Community posts - ha. I'm working on an on-demand app and got the Selection app template built and working. I'm now trying to get the Details app template working but running into a hiccup.

I'm connecting to a Hortonworks Hive table.

Basically, I'm trying to execute the SQL command below and I’m getting the following error:

SQL##f - SqlState: S1000, ErrorCode: 35, ErrorMsg: [Hortonworks][Hardy] (35) Error from server: error code: '0' error message: 'ExecuteStatement finished with operation state: ERROR_STATE'.

SQL SELECT

    mailpiece_id,

    induction_date,

    primary_year,

    primary_month,

    quarter,

    days_to_deliver,

    actual_delivery_days,

    piece_failed,

    area,

    district,

    facility

FROM HIVE.`default`.spm

$(WHERE_PART);

Initially, while debugging I kept parsing down the dynamically created WHERE clause [$(WHERE_PART)] until it was an extremely basic where clause (e.g., WHERE facility = "NEW YORK") and that was still producing the error. Then I even hard coded ‘WHERE facility = “New York”’ and received the same error. If I remove the where clause, the sql executes fine, so the problem seems to be within the Where clause.

I’m a little worried because I did some research and found the following Community post:

https://community.qlik.com/thread/173802

It seems like it’s the same (or very similar) issue and no one has mentioned a solution?

Any ideas out there???

[kaushiknsolanki]

Hi

Did you try putting where clause directly (without Variable) and see if it works.

Regards,

Kaushik Solanki

[Danny_Langley]

Hi Kaushik,

Thanks for the response. Yes, I tried without the variable and that did not work. That's what I meant when I said I hardcoded the where clause above.

[#4]

Hi Daniel,

I am facing the same issue. I cannot use a WHERE clause or GROUP BY or even a simple function on the query. I can only read the table with no modifications. Did you find any answer about this? I don't know if the problem is with the ODBC connector or it is a bug from some Qlikview version or what else...

Regards,

Jose

[Danny_Langley]

Hey Jose,

So it turned out the Hive data source itself was not optimized at all. Based on the way it was structured, when you tried to run the same query directly on the data source (outside of Qlik) it hung as well. The error I received within Qlik was just due to a query timeout. If you can, I would try to run the same query on the data set outside of Qlik. It took me a while to determine this because I didn't have access to the data source and was relying on someone else to run the query for me.