How does Tableau work on Redshift? (And / or why Redshift cannot display Tableau queries?)

Question

I wear tires on BI tools, including, of course, Tableau. Part of my assessment involves correlating the SQL generated by the BI tool with my actions in the tool.

The table puzzled me. My database has 2 billion things; however, no matter what I do in Tableau, the Redshift query reports that it was running: "Fetch 10000 in SQL_CURxyz", that is, a cursor operation. In the screenshot below, you can see that the cursor changes, indicating that new queries are running, but you do not see the original queries.

Is this a Redshift or Tableau quirk? Any idea how to see what actually works under the hood? And why does Tableau always run 10,000 records at a time?

Answer 1

I just ran into the same problem and wrote this simple query to get all queries for active cursors:

SELECT usr.usename AS username , min(cur.starttime) AS start_time , DATEDIFF(second, min(cur.starttime), getdate()) AS run_time , min(cur.row_count) AS row_count , min(cur.fetched_rows) AS fetched_rows , listagg(util_text.text) WITHIN GROUP (ORDER BY sequence) AS query FROM STV_ACTIVE_CURSORS cur JOIN stl_utilitytext util_text ON cur.pid = util_text.pid AND cur.xid = util_text.xid JOIN pg_user usr ON usr.usesysid = cur.userid GROUP BY usr.usename, util_text.xid; 

Answer 2

Ah, this has already been asked on the AWS forums.

https://forums.aws.amazon.com/thread.jspa?threadID=152473

The Redshift console does not appear to display a query for cursors. To get this, you can request STV_ACTIVE_CURSORS: http://docs.aws.amazon.com/redshift/latest/dg/r_STV_ACTIVE_CURSORS.html

Answer 3

Alternatively, you can modify your .TWB file (which is really just an xml file) and add the following parameters to the odbc-connect-string-extras property.

• UseDeclareFetch = 0;
• FETCH = 0;

You would have something like:

 <connection class='redshift' dbname='yourdb' odbc-connect-string-extras='UseDeclareFetch=0;FETCH=0' port='0000' schema='schm' server='any.redshift.amazonaws.com' [...] > 

Unfortunately, there is no way to change this behavior through the application, you have to edit the file directly.

You should be aware of the spectacular effects of this. Although this greatly improves debugging, there should be a reason why Tableau chose not to allow these settings to be modified through the application.