3 Replies Latest reply on Mar 16, 2018 8:26 AM by Casper Rademaker

    Extracting Custom SQL Query contents from Tableau Server Repository

    Casper Rademaker

      Hello there,

       

      We're trying to find the contents of custom SQL queries from the Tableau Server repository i.e. SELECT ....  FROM....

       

      In the table rec_connection_tables we did find a column "Custom SQL" that looks like a foreign key referring to another table, but I couldn't find to which field in which table this seeming foreign key refers to, which would help us to find the associated query statement.

       

      Thanks in advance for any assistance.

        • 1. Re: Extracting Custom SQL Query contents from Tableau Server Repository
          patrick.byrne.0

          Hello Casper,

           

          There is a method to view the underlying SQL queries in Tableau. I am including a link to the process below.

           

          Viewing Underlying SQL Queries | Tableau Software

           

          Hope this helps!

           

          Cheers,

          Byrne, Patrick

          1 of 1 people found this helpful
          • 2. Re: Extracting Custom SQL Query contents from Tableau Server Repository
            Matt Coles

            Custom SQL is kept in blob data form in the PostgreSQL, along with the rest of the workbook XML. It is not in an easy-to-use table. To get at it in the Repository, you will need to convert the blob data in pg_largeobject to XML, then parse the XML to find the query used. Steps will be:

             

            CAUTION / ACHTUNG! THIS IS TOTALLY UNSUPPORTED. IF YOU RUN A BAD QUERY AGAINST YOUR REPOSITORY, YOU CAN BREAK TABLEAU SERVER. ALSO, THIS MAY NOT WORK IN THE FUTURE.

             

            1. Obtain elevated permissions on the database through the tblwgadmin user, whose password is encrypted in your config. It can be retrieved with "tabadmin get pgsql.adminpassword"

            2. Connect to the database using a client like pgAdmin4, using the user from (1) and the password you obtained.

            3. Replace "FILEPATH" with your output filepath, then run the following query:

              select 
                lo_export(oid, 'FILEPATH//' || repo_temp.id || '_' || repo_temp.repository_url ||  lo_temp.filetype)
              from 
                (
                select 
                  w.id,
                  w.repository_url, 
                  rd.content as "oid" 
                from workbooks w inner join repository_data rd 
                  on COALESCE(w.data_id, w.reduced_data_id) = rd.tracking_id
                ) as "repo_temp"
              left join
                (
                select
                  loid,
                  case when substring(data from 1 for 4) = 'PK\003\004' then '.twbx' else '.twb' end as "filetype"
                from pg_largeobject 
                where pg_largeobject.pageno = 0
                ) as "lo_temp"
              on repo_temp.oid = lo_temp.loid;
            

             

            4. You will need to use a script to bulk-unzip the .twbx files to extract their .twb (the XML file that contains the info you see).

            5. Write a script to parse the XML of the .twb files and extract the custom SQL.

             

             

            If you want to get really fancy, Tamas Foldi has a project that will let you access the workbooks and such in your repository as if it was a file system.

            2 of 2 people found this helpful