Oracle Analytics Cloud and Server

Products Banner

DVD Trying to pull all records from Snowflake table

Question
23
Views
0
Comments

Summary

Count query tries to pull all records from the table

Content

Hello,

I am experimenting connecting Data Visualization Desktop Version 12.2.5.3.0-20190529084035 to Snowflake.

I was able to successfully create connection and create a dataset based on a TRIPS table that Snowflake uses for it's hands on demos.

When trying to create a simple visualization, I created a calculated column Count_Rows with the expression Count(BIKEID).

I am expecting a sql similar to : select count(bikeid) from trips to be submitted to Snowflake. 

Instead, I get an error as follows:

Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
(HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.
(HY000)
State: HY000. Code: 43121. [nQSError: 43121] Max Row Limit Exceeded
(HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed: 
(HY000)
State: HY000. Code: 60008. [nQSError: 60008] The query for user 'weblogic' exceeded the maximum query governing rows 5000000 from the database ''weblogic'.'fdc_snowflake_test''. (HY000)
SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1; SELECT
0 s_0,
COUNT(XSA('weblogic'.'TRIPS')."Columns"."BIKEID") s_1
FROM XSA('weblogic'.'TRIPS')
FETCH FIRST 5000001 ROWS ONLY

 

Note the COUNT(XSA('weblogic'.'TRIPS')."Columns"."BIKEID") s_1

But on the Snowflake server the physical sql submitted seems to be:

select T1000002.BIKEID as BIKEID from (select TRIPDURATION, BIKEID, BIRTH_YEAR, END_STATION_ID, END_STATION_LATITUDE, END_STATION_LONGITUDE, END_STATION_NAME, GENDER, MEMBERSHIP_TYPE, START_STATION_ID, START_STATION_LATITUDE, START_STATION_LONGITUDE, START_STATION_NAME, STARTTIME, STOPTIME, USERTYPE from PUBLIC.TRIPS) T1000002

The means that DVD is trying to cache all rows from the remote data source and then would perform the count by itself.

Why does DVD do this? Is there a way to force DVD to submit physical count query? 

Thanks,

Manish

Version

Data Visualization Desktop Version 12.2.5.3.0-20190529084035

Code Snippet

SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1; SELECT
0 s_0,
COUNT(XSA('weblogic'.'TRIPS')."Columns"."BIKEID") s_1
FROM XSA('weblogic'.'TRIPS')
FETCH FIRST 5000001 ROWS ONLY
Tagged: