CODEWITHSUNDEEP
sqlamazon-redshifttableau-api
user717847
user717847

Reputation: 756

How does Tableau run queries on Redshift? (And/or why can't Redshift display Tableau queries?)

I'm kicking tires on BI tools, including, of course, Tableau. Part of my evaluation includes correlating the SQL generated by the BI tool with my actions in the tool.

Tableau has me mystified. My database has 2 billion things; however, no matter what I do in Tableau, the query Redshift reports as having been run is "Fetch 10000 in SQL_CURxyz", i.e. a cursor operation. In the screenshot below, you can see the cursor ids change, indicating new queries are being run -- but you don't see the original queries.

Is this a Redshift or Tableau quirk? Any idea how to see what's actually running under the hood? And why is Tableau always operating on 10000 records at a time?

Redshift console

Upvotes: 20

Views: 11335

Answers (3)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

I just ran into the same problem and wrote this simple query to get all queries for currently 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;

Upvotes: 26

MateusBR
MateusBR

Reputation: 324

Also, you can alter 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 end up with 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's no way of changing this behavior trough the application, you must edit the file directly.

You should be aware of the performance implications of doing so. While this greatly enhances debugging there must be a reason why Tableau chose not to allow modification of these parameters trough the application.

Upvotes: 4

user717847
user717847

Reputation: 756

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

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

Redshift's console apparently doesn't display the query behind cursors. To get that, you can query STV_ACTIVE_CURSORS: http://docs.aws.amazon.com/redshift/latest/dg/r_STV_ACTIVE_CURSORS.html

Upvotes: 8

Related Questions