Oracle Analytics Cloud and Server
DVD Trying to pull all records from Snowflake table
Count query tries to pull all records from the table
I am experimenting connecting Data Visualization Desktop Version 184.108.40.206.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.
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.
State: HY000. Code: 43121. [nQSError: 43121] Max Row Limit Exceeded
State: HY000. Code: 43119. [nQSError: 43119] Query Failed:
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
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?
Data Visualization Desktop Version 220.127.116.11.0-20190529084035
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