Qlik Community

Technology Partners Ecosystem Discussions

Qlik Technology Partners Ecosystem

docpuddu
New Contributor III

Qlik Sense Direct Query Cloudera Impala

Hello everybody,
if I use normal access to the data like the following, everything goes okay:

LOAD  
    chiamante,
    chiamato;

[cdr_newest]:
SELECT
    chiamante,
    chiamato
FROM rag.[cdr_newest]
;


However if I use Direct Query strategy I get the following error:

DIRECT QUERY

  DIMENSION
    chiamato,
    chiamante
 
FROM  rag.[cdr_newest]
;


-------------
Connector reply error: Unable to get column information for the fields that are used in the query:
    ERROR [42000] [Qlik][ODBC Impala Wire Protocol driver][Impala]AnalysisException: Syntax error in line 1:
... DISTINCT "chiamato" FROM "rag"."[cdr_newest]"
                             ^
Encountered: STRING LITERAL
Expected: DEFAULT, IDENTIFIER

CAUSED BY: Exception: Syntax error
----------------------------------------------------------------------

I can't figure out the reason. I google searched extensively, but no success.

Any idea regarding what's going on? Thanks a lot for your help.
Andrea



Qlik Sense Desktop (Feb 2018)
Cloudera Impala ODBC64 ver. 2.5.41

Labels (1)
2 Solutions

Accepted Solutions

Re: Qlik Sense Direct Query Cloudera Impala

There's not much point in having a Direct Query without a corresponding measure. If loading a Dimension table this way then at least include a dummy counter as a measure. You can then graph the number of CDR records created by different Callers using sum([CDR Count]).

[CDR]:
DIRECT QUERY
  DIMENSION
    chiamato  as [Called],
    chiamante as [Caller]
  MEASURE
    1 as [CDR Count] 
FROM  [rag].[cdr_newest];

Also, when using Direct Query, the Qlik Engine has to generate SQL that is properly formed for that database, including a WHERE filter when appropriate, and tries to push the sum() or count() aggregation functions to the database. In the Qlik Script you should set certain Variables with values that Qlik will use to properly quote or delimit the database identifiers otherwise field names are delimited with double quotes.

Refer Direct Discovery Variables 

I believe you should add this fragment above your DIRECT QUERY statement:

SET DirectDistinctSupport='true';
SET DirectIdentifierQuoteChar='[]';

Note that when using Direct Query you still have to perform a Document Reload to prime the associative indices with the distinct values of each Dimension. If you have a very volatile or high-cardinality dimension then this may not be feasible. Qlik strongly recommends using the ODAG approach rather than Direct Query in most use cases.

Highlighted
docpuddu
New Contributor III

Re: Qlik Sense Direct Query Cloudera Impala

Hi Michael, thank you for taking the time to read, analyse and respond to my post.

The snipped of code I posted has no production purpose, was just the minimal (syntax correct) piece to make Impala (and Direct tecnique) in place. However your extensive explanation adds very interesting didactics for me and for the community too I think.

The heart of the solution goes around the second part of your reply where you introduce Direct Discovery Variables

Just tonight I had the chance to complete succesfully the interaction with cloudera, using:
SET DirectIdentifierQuoteChar=' '

After your suggestion I'm trying the following too:
SET DirectDistinctSupport='true';
SET DirectIdentifierQuoteChar='[]';

and it works!

So, again setting Direct Discovery Variables is crucial to enable Qlik Sense interacting successfully against Hadoop/Cloudera

Thanks a lot for your help, I think this post will be an important point of reference for the community as
I searched a lot before with no success.

Best regards,
Andrea

6 Replies

Re: Qlik Sense Direct Query Cloudera Impala

Please post full script how your "Direct Query" strategy?

We required

1. Dimension

2. Measure

3. Detail

Not sure - How you have designed?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
docpuddu
New Contributor III

Re: Qlik Sense Direct Query Cloudera Impala

What you see is what I have in my script.

 Accondingly to the official syntax (https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/ScriptRegularStateme...)

Syntax:  

DIRECT QUERY DIMENSION fieldlist [MEASURE fieldlist[DETAIL fieldlist] FROM tablelist

[WHERE where_clause]

 

MEASURE and DETAIL are optional

Re: Qlik Sense Direct Query Cloudera Impala

If you talk about your script, I don't see any wrong. I wonder, whether Impala connection created successfully correct or not?

LOAD  
    chiamante,
    chiamato;

[cdr_newest]:
SELECT
    chiamante,
    chiamato
FROM rag.[cdr_newest];

 

Did you see any error only using this?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Qlik Sense Direct Query Cloudera Impala

There's not much point in having a Direct Query without a corresponding measure. If loading a Dimension table this way then at least include a dummy counter as a measure. You can then graph the number of CDR records created by different Callers using sum([CDR Count]).

[CDR]:
DIRECT QUERY
  DIMENSION
    chiamato  as [Called],
    chiamante as [Caller]
  MEASURE
    1 as [CDR Count] 
FROM  [rag].[cdr_newest];

Also, when using Direct Query, the Qlik Engine has to generate SQL that is properly formed for that database, including a WHERE filter when appropriate, and tries to push the sum() or count() aggregation functions to the database. In the Qlik Script you should set certain Variables with values that Qlik will use to properly quote or delimit the database identifiers otherwise field names are delimited with double quotes.

Refer Direct Discovery Variables 

I believe you should add this fragment above your DIRECT QUERY statement:

SET DirectDistinctSupport='true';
SET DirectIdentifierQuoteChar='[]';

Note that when using Direct Query you still have to perform a Document Reload to prime the associative indices with the distinct values of each Dimension. If you have a very volatile or high-cardinality dimension then this may not be feasible. Qlik strongly recommends using the ODAG approach rather than Direct Query in most use cases.

Highlighted
docpuddu
New Contributor III

Re: Qlik Sense Direct Query Cloudera Impala

Hi Michael, thank you for taking the time to read, analyse and respond to my post.

The snipped of code I posted has no production purpose, was just the minimal (syntax correct) piece to make Impala (and Direct tecnique) in place. However your extensive explanation adds very interesting didactics for me and for the community too I think.

The heart of the solution goes around the second part of your reply where you introduce Direct Discovery Variables

Just tonight I had the chance to complete succesfully the interaction with cloudera, using:
SET DirectIdentifierQuoteChar=' '

After your suggestion I'm trying the following too:
SET DirectDistinctSupport='true';
SET DirectIdentifierQuoteChar='[]';

and it works!

So, again setting Direct Discovery Variables is crucial to enable Qlik Sense interacting successfully against Hadoop/Cloudera

Thanks a lot for your help, I think this post will be an important point of reference for the community as
I searched a lot before with no success.

Best regards,
Andrea

TSquared
New Contributor

Re: Qlik Sense Direct Query Cloudera Impala

Thanks Andrea.  SET DirectIdentifierQuoteChar=' ' is the only thing that worked for me after an hour of searching.