Tableau如何在Redshift上运行查询？ （和/或为什么Redshift不能显示Tableau查询？）

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.

我正在为BI工具开玩笑,当然包括Tableau。我的部分评估包括将BI工具生成的SQL与我在工具中的操作相关联。

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.

Tableau让我神秘莫测。我的数据库有20亿个东西;但是,无论我在Tableau中做什么,查询Redshift报告已经运行的是“在SQL_CURxyz中获取10000”,即光标操作。在下面的屏幕截图中,您可以看到光标ID已更改,表示正在运行新查询 - 但您没有看到原始查询。

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还是Tableau的怪癖?任何想法如何看看实际上在幕后运行的是什么?为什么Tableau总是一次运行10000条记录?

3 个解决方案

#1

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;

#2

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

啊,这已在AWS论坛上提出过。

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

Redshift的控制台显然不会在游标后面显示查询。为此,您可以查询STV_ACTIVE_CURSORS:http://docs.aws.amazon.com/redshift/latest/dg/r_STV_ACTIVE_CURSORS.html

#3

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.

此外,您可以更改.TWB文件(实际上只是一个xml文件)并将以下参数添加到odbc-connect-string-extras属性中。

• 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.

您应该了解这样做的性能影响。虽然这极大地增强了调试功能,但Tableau选择不允许通过应用程序修改这些参数。