Run Initial SQL
Note: Tableau Prep Builder version 2019.2.2 and later supports using Initial SQL, but doesn't yet support all of the same options supported by Tableau Desktop . For information about using Initial SQL with Tableau Prep Builder, see Use Initial SQL to query your connections(Link opens in a new window) in the Tableau Prep Builder online help.
When connecting to some databases, you can specify an initial SQL command that will run when a connection is made to the database, for example, when you open the workbook, refresh an extract, sign in to Tableau Server, or publish to Tableau Server. Initial SQL is not run when your refresh your view. Note that this initial SQL is different than a custom SQL connection. A custom SQL connection defines a relation (or table) to issue queries against. For more information, see Connect to a Custom SQL Query.
You can use this command to:
Set up temporary tables to use during the session.
Set up a custom data environment.
You have the option to add an initial SQL command in the Server Connection dialog box or on the Data Source page.
Note: If your data source supports running an initial SQL statement, an Initial SQL link appears in the lower-left corner of the Server Connection dialog box. For information about your data source, see Supported Connectors.
To use initial SQL
In the Server Connection dialog box, click Initial SQL. Or, on the Data Source page, select Data > Initial SQL or Data > Query Banding and Initial SQL depending on the database you connect to.
Enter the SQL command into the Initial SQL dialog box. You can use the Insert drop-down menu to pass parameters to your data source.
Note: Tableau does not examine the statement for errors. This SQL statement is simply sent to the database when you connect.
Your software license may restrict you from using initial SQL with your connection. If you publish to Tableau Server, the server must be configured to allow Initial SQL statements. By default, the server software is configured to allow these statements to run when the workbook is loaded in a web browser.
Administrators can configure server to ignore initial SQL statements by using the
tsm configuration set command:
tsm configuration set -k vizqlserver.initialsql.disabled -v true
If the server doesn't allow initial SQL statements, the workbook opens, but the initial SQL commands are not sent.
For more information about the
tsm configuration set command, see the Tableau Server Help(Link opens in a new window).
Parameters in an initial SQL statement
You can pass parameters to your data source in an initial SQL statement. There are several reasons why this is useful:
You can configure impersonation using the TableauServerUser or TableauServerUserFull parameters.
If your data source supports it, you can set up row-level security (for example, for Oracle VPD or SAP Sybase ASE) to make sure that users see only the data that they are authorized to see.
You can provide more details in logging, for example, the Tableau version or the workbook name.
The following parameters are supported in an initial SQL statement:
|Parameter||Description||Example of returned value|
|TableauServerUser||The user name of the current server user. Use when setting up impersonation on the server. Returns an empty string if the user is not signed in to Tableau Server.||jsmith|
|TableauServerUserFull||The user name and domain of the current server user. Use when setting up impersonation on the server. Returns an empty string if the user is not signed in to Tableau Server.||domain.lan\jsmith|
|TableauApp||The name of the Tableau application.||
Tableau Desktop Professional
|TableauVersion||The version of the Tableau application.||9.3|
|WorkbookName||The name of the Tableau workbook. Use only in workbooks with an embedded data source.||Financial-Analysis|
The following examples show different ways you can use parameters in an initial SQL statement.
- This example sets the security context on Microsoft SQL Server:
EXECUTE AS USER = [TableauServerUser] WITH NO REVERT;
This example shows how, on a DataStax data source, you can use parameters to add detail to logging or to set up a session variable to keep track of the data:
SET TABLEAUVERSION [TableauVersion];
This example can be used to help set up row-level security for Oracle VPD:
Note: Oracle PL/SQL blocks require a trailing semicolon to terminate the block. Consult Oracle documentation for the proper syntax.
Defer execution to the server
You can defer an initial SQL statement so that it is executed only on the server. One reason to defer execution to the server is if you don’t have permission to execute the commands that set up impersonation. Use <ServerOnly></ServerOnly> tags to enclose the commands to be executed only on the server.
CREATE TEMP TABLE TempTable(x varchar(25)); INSERT INTO TempTable VALUES (1); <ServerOnly>INSERT INTO TempTable Values(2);</ServerOnly>
Security and impersonation
If you use the TableauServerUser or TableauServerUserFull parameter in an initial SQL statement, you will create a dedicated connection that can’t be shared with other users. This will also restrict cache sharing, which can enhance security, but may also slow performance.
Troubleshoot 'create table' for MySQL and Oracle connections
For MySQL connections, tables are not listed after using initial SQL to create table
When you connect to MySQL, if you run an initial SQL statement like the following, tables might not show because of the way Tableau constructs the query:
CREATE TABLE TestV1.testtable77(testID int);
To resolve this issue, add
IF NOT EXISTS to the SQL statement:
CREATE TABLE IF NOT EXISTS TestV1.TestTable(testID int);
For Oracle connections, using initial SQL to create table causes Tableau to stall
When you connect to Oracle and run an initial SQL statement like the following, Tableau is stalled with a spinning wheel because of the way Tableau constructs the query:
CREATE TABLE TEST_TABLE (TESTid int)
To resolve this issue, use the following SQL statement:
BEGIN EXECUTE IMMEDIATE 'create table test_table(testID int)'; EXCEPTION WHEN OTHERS THEN NULL; END;