1 Reply Latest reply on Mar 24, 2015 10:49 AM by Dmitry Chirkov

    Disable query for foreign keys on live Vertica SQL connection?

    Tracer Bullet

      Hi all,

       

      Running tableau server 8.2 using a custom SQL query Vertica connection. When published to the server, attempting to refresh will run a series of queries on the foreign keys table referencing a temporary table that Vertica created before the actual dashboard query is run.There are obviously no results because the table was just created.

       

      Attempting to create the connection directly referencing the underlying table produces the same queries even when it is a single table connection with no joins and no defined foreign keys.This happens regardless of whether 'assume referential integrity' or not is checked.


      These queries take a not insignificant amount of time to run (often more than the actual dashboard query!) and so we'd prefer if they never happened at all. Is there any way to prevent tableau from pointlessly querying these tables?

       

      Example of the query it runs:

      select * from (select 'database' as primary_key_catalog_name, primary_keys.table_schema as primary_key_schema_name, primary_keys.table_name as primary_key_table_name, primary_keys.column_name as primary_key_column_name, 'database' as foreign_key_catalog_name, foreign_keys.table_schema as foreign_key_schema_name, foreign_keys.table_name as foreign_key_table_name, foreign_keys.column_name as foreign_key_column_name, primary_keys.ordinal_position as key_seq, 3 as foreign_key_update_rule, 3 as foreign_key_delete_rule, foreign_keys.constraint_name as foreign_key_name, primary_keys.constraint_name as primary_key_name, -1 as deferrability from v_catalog.primary_keys join v_catalog.foreign_keys on primary_keys.table_name = foreign_keys.reference_table_name and primary_keys.table_schema = foreign_keys.reference_table_schema and primary_keys.column_name = foreign_keys.reference_column_name  order by primary_key_catalog_name, primary_key_schema_name, primary_key_table_name, key_seq ) as vmd where foreign_key_catalog_name ilike E'database' escape E'\\' and foreign_key_schema_name ilike E'' escape E'\\' and foreign_key_table_name ilike E'_tableau_node_numbers_connect' escape E'\\'

       

      Thanks!