Aggregation on top of ndv based column in analytical view is not working

Analytical Views
1

I have created an analytical view which uses aggregation as NDV on a column.

Example query:

create analytical view testdb.testAV AS select bucketdate, username, ndv(questionid) as noofquestionsasked from testdb.testsourceA group by bucketdate, username;

When I am trying to read data from the above analytical view using a select query, the measure column “noofquestionsasked” shows random symbols.

image
image.png733×157 8.39 KB

I need to execute a query on this analytical view as below:

select bucketdate, username, sum(noofquestionsasked) from testdb.testAV group by bucketdate, username;

This gives the error as below:

image
image.png947×96 2.52 KB

Kindly let me know if any other information is needed.

Thanks,
Soumya

2

@soumyaawasthi analytical views are NOT to be accessed directly, for the very reason you are seeing.

Analytical views are indirectly (transparently) leveraged by the arcadia platform.

Your queries should always be against the base (source) table. So your query should actually like the below:

select bucketdate, username, ndv(questionid)
from testdb.testsourceA 
group by bucketdate, username;

Behind the scenes, Arcadia’s execution engine will leverage the Analytical View which has partial results already precomputed for the ndv(questionid).

1 Like
3

@shaun, I tried the suggestion mentioned above. My use case is to read data from external application.

The issue I am facing now is that when I try to execute the query on the base table through an external application with Impala connection, AV is not used.

Although, on running same query inside Arcadia (direct query), AV is used.

Kindly suggest.

Thanks,
Soumya

4

@soumyaawasthi you’ll need to configure your external application to point at the Arcadia Analytics Engine service instead of Impala if you plan to use AVs. I’m not sure how your Impala connection is being managed/generated, but for 3rd party BI tools typically the ODBC driver for Impala and Arcadia Analytics Engine can be interchanged except you will point the connection to port 31050 instead of 21050 to use Arcadia Analytics Engine.

5

@soumyaawasthi Also please refer to this thread for more info:

1 Like