1 2 Previous Next 17 Replies Latest reply on Jan 2, 2014 10:03 AM by Phillip Burger

    Is it possible to create and use temp tables?

      I would like to use Custom SQL when importing data into an extract. It looks like Tableau won't support this?

       

      Basically I want to do something like this:

       

      - Create the temp table

      - Insert data into the temp table

      - run a series of unioned queries against the temp table.... create the extract from this

      - drop the temp table

       

      Am I up a creek, or is there a way to make this work?

       

      I've got the query working beautifully... returns exactly the data I want to go into my extract, but when I paste it into the MySQL Connection window in Tableau, I receive the following error:

       

       

      MySQL database error 0x80040E14: [MySQL][ODBC 3.51 Driver][mysqld-5.1.50-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE rt_temp
      
           (company_name VARCHAR(45),
          region VARC' at line 3
      Unable to connect to the server "10.122.224.183". Check that the server is running and that you have access privileges to the requested database.
      
      
      
      
      
        • 1. Re: Is it possible to create and use temp tables?

          Forgot to click the "notify me about replies" checkbox

          • 2. Re: Is it possible to create and use temp tables?
            Robert Morton

            Hi Jamie,

            Tableau does not yet offer tools to help you manage ETL operations on your database, even if this is simply a means to creating an extract.  I personally see a need to help users manage their data in more complex ways, but I do not know what the priority or possible timeline is for such functionality.

            -Robert

            • 3. Re: Is it possible to create and use temp tables?

              Hi Robert,

               

              Thanks for the quick reply. I hope this gets onto the roadmap pretty soon... it's a pretty big gap in Tableau's offering. At my last company (a clinical trial automation firm) the lack of support for temp tables and stored procedures was the single biggest reason the company chose Spotfire over Tableau. I'm really not sure how my current company will work around this issue... if we come up with something clever, I'll post it. In the meantime, if your product team would like to some specific scenarios that illustrate the importance of supporting the use of temp tables in Custom SQL, I'll be happy to jump on the phone or exchange some emails.

               

              Cheers,

              Jamie

              • 4. Re: Is it possible to create and use temp tables?
                Robert Morton

                Hi Jamie,

                I've noted your concerns and offer for feedback, and I hope we can take you up on it soon.  I also encourage you to bring this up with your sales rep, since a customer's standing and growth potential both factor in to prioritizing features on our roadmap.

                -Robert

                • 5. Re: Is it possible to create and use temp tables?
                  Dean Glasener

                  Hi Robert,

                   

                  Tableau is a fine product with outstanding attributes today.  Yet there are many benefits in allowing/endowing Tableau to interact more with underlying databases / temp tables - allowing CRUD activity under Tableau programmatic control.

                   

                  Tableau pre and post - [ Click/Filter Events ]

                   

                  Run a Tableau Routine/Stored Procedure on the Temp Results of Filters/Queries to the underlying databases.

                   

                  in dbase or foxpro -  that could be:  replace all  TEMP.SALARY  with temp.salary  X  1.08 for  department = 100

                  in Coldfusion there are infinite  pre and post QUERYING  loops/processing/updates possible - and then Querying of the Query Temp Data is allowed.

                  there is also extensive capability to cache the  TEMP datastores.

                   

                  The above additions of capability would greatly extend and enhance the Tableau business application domain.

                   

                  Regards,

                  Dean

                  • 6. Re: Is it possible to create and use temp tables?
                    Robert Morton

                    Hi Dean,

                     

                    Thanks for your feedback.  We have heard requests like this before and are interested in supporting these use cases, and we are balancing this with other feature requests that will require substantial work.  One advantage of Tableau's current read-only analytical query engine is that DBAs can trust the use of our products with their carefully tuned and maintained systems.  We will have to carefully consider how to introduce updates in a way that is isolated (and can be disabled), and can be tested and audited by customers who need precise control of potentially damaging operations.

                     

                    As I mentioned to Jamie, please also give this feedback to your sales rep so they can track these requests in association with specific customers.

                     

                    Thanks,

                    Robert

                    • 8. Re: Is it possible to create and use temp tables?

                      Hi,

                       

                      Is there any solution to this problem ?

                      I had same number error.Please find the screen shot of it to this post.

                       

                      When i was trying to use the following query under CustomSQL ,I am facing this issue with Tableau.

                       

                      Please find the query below.

                       

                      select Asset_Manager, sum(LastBD_Count) as LastBD_Count, sum(MTD_Count) as MTD_Count from (

                      Select a.unparsed_name as Asset_Manager ,COUNT(sale_price) as LastBD_Count, 0 as MTD_Count

                      from fact_reo_sales f,dim_aset_manager a,dim_date d

                      where f.dim_asset_manager_key=a.dim_asset_manager_key and f.dim_date_wire_date_key=d.dim_date_key and d.date_value=(select max(d.date_value) from dim_date d,fact_reo_sales f,dim_aset_manager a where f.dim_asset_manager_key=a.dim_asset_manager_key and f.dim_date_wire_date_key=d.dim_date_key)

                      group by a.unparsed_name

                      UNION

                      SELECT a.unparsed_name,0 as LastBD_Count, COUNT(sale_price) as MTD_Count

                      FROM fact_reo_sales f,dim_aset_manager a,dim_date d

                      where f.dim_asset_manager_key=a.dim_asset_manager_key and f.dim_date_wire_date_key=d.dim_date_key and month =(select max(d.month) from dim_date d,fact_reo_sales f,dim_aset_manager a where f.dim_asset_manager_key=a.dim_asset_manager_key and f.dim_date_wire_date_key=d.dim_date_key)

                      group by a.unparsed_name) x group by  Asset_Manager;

                       

                      It is working fine in MySQL Perfectly fine.

                       

                      Any help much appreciated.

                       

                      Thanks & Regards,

                       

                      Sushma

                      • 9. Re: Is it possible to create and use temp tables?
                        Robert Morton

                        Hi koyisush,

                         

                        The issue you described is not related to this thread, though the error messages are similar. The original posted wanted to incorporate DDL statements in their custom SQL, which is not possible because MySQL cannot support them within subqueries; also, it would not be useful for a custom SQL connection since the DDL would be run on any query issued in response to modifying a visualization.

                         

                        For your issue, please contact support so they may work with you collect detailed information about this problem.

                         

                        -Robert

                        • 10. Re: Is it possible to create and use temp tables?

                          Robert,

                           

                          Would i start new thread ? But the same query is running in Actual My SQL Query Browser.

                           

                          You mean to say is that tableau My SQL will not support nested queries.

                           

                          Am i correct ?

                           

                          Thanks & Regards,

                          Sushma K

                          • 11. Re: Is it possible to create and use temp tables?

                            Sushma,

                             

                            Tableau's Custom SQL prompt supports a subset of the SQL that will run in a MySQL Query Browser.

                            * you cannot call stored procedures

                            * you cannot call a series of queries

                             

                            I had to figure out the limitations through trial and error...

                             

                            - Jamie

                            • 12. Re: Is it possible to create and use temp tables?
                              Robert Morton

                              Hi Jamie,

                               

                              Tableau will always wrap a custom SQL connection as a subquery when issuing a query generating from a visualization. The reason for this is so that Tableau may add additional filters, grouping columns and select expressions from user calculated fields -- doing so requires that any existing filters, grouping columns, etc. from the custom SQL won't collide, which can be achieved with a subquery.

                               

                              Tableau does not impose any limitations on the custom SQL connection, but the database system may impose limitations on the use of subqueries. MySQL does not support DDL operations in a subquery -- such as creating temp tables -- nor does it allow using multiple statements.

                               

                              -Robert

                              • 13. Re: Is it possible to create and use temp tables?
                                Phillip Burger

                                Hi, Robert. Does the information in this thread still apply today, November 2013 and Tableau 8.1? That is, it's not possible to create temp tables.

                                 

                                It's ok if so. I understand the read-only architecture and the trust issues with DBAs.

                                 

                                This isn't documented anywhere that I could find and am just wanting to make sure I know the latest. Armed with the latest, I can pursue and discuss options within my shop.

                                • 14. Re: Is it possible to create and use temp tables?
                                  Robert Morton

                                  Hi Phillip,

                                  Certain data sources in Tableau support the Initial SQL feature in the connection dialog, which allows you to perform up-front operations like creating a temporary table that serves as the basis of your connection. Over time we may continue enabling Initial SQL support for various data sources. Look for that option (sometimes buried in an 'Advanced...' sub-dialog within the connection dialog) in your preferred data source to see if anything has changed.

                                  -Robert

                                  1 2 Previous Next