12 Replies Latest reply on Feb 15, 2013 4:30 PM by Richard Leeke

    Tableau API for reading from extract files

    Tamas Foldi

      Is it supported in Tableau 8 to read extracts files as well? I do not see any API related to pull out data from extracts, just writing.

       

      In the meanwhile I wrote my own library on the top of tdeapi.dll, the question is that may I release it?

       

      Message was edited since in the meanwhile I made my own API

        • 1. Re: Tableau API for reading from extract files
          Tamas Foldi

          No answer so far, so I did a look under the hood. My first feeling that I will able to read / write tableau extracts without the supplied API directly with the tdeapi.dll. Here what I discovered so far, tableau guys could correct me if I am wrong:

           

          • tdeserver is a generic database server which communicates thru named pipes with the client processes
          • this TDE server implements a query interface with functions like TDE_QueryExecute, TDE_RowsetHasMoreValues, etc.
          • It seems, that I can run this TDE server independently from tableau by starting and stoping from my own client process - thus I can use it as a standalone database server for other purposes
          • There are no update related functions in the API, just query and insert (as expected)
          • Globalization supported by standard ICU libraries so I can use those libraries directly to deal with unicode things

           

          Sorry for blogging here again, but sometimes I just had a feeling that it could help for others

          • 2. Re: Tableau API for reading from extract files
            Richard Leeke

            I'm sure you're right that there's no supported way of doing this.

             

            I've long been advocating more extensibility and integration points, so I'll be really interested to hear how you get on. I'm sure I'm not the only one...

            1 of 1 people found this helpful
            • 3. Re: Tableau API for reading from extract files
              Tamas Foldi

              Well, reverse engineer an API is only question of time, I don't see any complications with that. Extract engine (which is the database server itself) is a standalone executable called tdeserver.exe. With tools like MS Detours I can easily patch the Tableau Desktop to see what is the communication between the client and server and I can match it with the tdeserver.txt log file which also prints the command in human readable format.

               

              I think to hack a first initial version of tableau extract reader/writer app will take not more than a few days. However, if Tableau will release the API I don't have to invest any efforts.

              • 4. Re: Tableau API for reading from extract files
                Tamas Foldi

                Just some little update: I was able to manipulate Tableau extracts files without the API itself. First of all I made a nice named pipe sniffer to log communication between Tableau and Tableau Extract server (tdeserver.exe). After that, I've injected my DLL into Tableau, thus I was able to catch the communication and made some solid success:

                 

                tde_without_api.png

                What we see here, that I was able to execute plain text commands against the TDE database, for instance instruct it to create a new extract - without using the provided API. Just a plain socket, nothing else. The good news that this method will work also under Linux, however, the tdeserver still must run as service on windows side (and as there is no authentication in TDE server it's probably a bad idea).

                 

                Some other info what I obtained on the protocol:

                • every network/named pipe package should start with a magic number to check protocol errors
                • after the first package the server gives back a session id, which should be passed during the later calls.
                • all commands are simple unicode strings with a mixture of sql like and functional lang like syntax

                 

                Some TDE/SQL statements:

                • (create database C:\\foobar.tde")
                • (database "C:\\foobar.tde")
                • (show schemas)
                • (create schema [Extract])
                • (create table [Extract].[Extract])
                • (create column [Extract].[Extract].[Price] ( ( "builtin" "double" ) ( "factory" "builtin" ) ( "ordinal" "3" ) ))

                 

                How a typical SELECT statement look like:

                (restrict

                 

                (aggregate

                  (select

                    (project

                      (project

                        (table [Extract].[Extract])

                        (

                          ([Returns$_Order ID] [Order ID1])

                          ([Users$_Region] [Region1])

                          )

                        )

                      (

                        ([Product Category] [Product Category])

                        ([Product Sub-Category (group)] [Product Sub-Category (group)])

                        ([Region] [Region])

                        ([none:Calculation4:nk]

                          (= "Pat" [Manager])

                          )

                        ([yr:Date:ok]

                          (year [Date])

                          )

                        ([none:Customer State:nk] [Customer State])

                        ([none:Region:nk] [Region])

                        )

                      )

                    (and [none:Calculation4:nk]

                      (= [yr:Date:ok] 2010)

                      )

                    )

                  (

                    ([none:Customer State:nk] [none:Customer State:nk])

                    ([none:Region:nk] [none:Region:nk])

                    )

                  (

                    ([sum:Sales:qk]

                      (sum [Sales])

                      )

                    ([usr:Calculation1:qk]

                      (/

                        (sum [Profit])

                        (sum [Sales])

                        )

                      )

                    )

                  )

                ([none:Customer State:nk] [none:Region:nk] [sum:Sales:qk] [usr:Calculation1:qk])

                )

                 

                Pretty functional.

                 

                INSERT is not a statement, it's more like a bulk data upload operation after specifying the column names. If all records are uploaded one can instruct the tdeserver to "package" the data, which creates the tde file itself.

                 

                I did not played too much with the protocol, but now it starts to be fun. For example we can create MERGE like functionality (check before inserts if the record is exists in the extract or not) which improves the incremental appending possibilities in tableau or just check the contents easily from outside.

                • 5. Re: Tableau API for reading from extract files
                  Tamas Foldi

                  Today I wrote my own library on the top of tdeapi.dll like the one what Tableau Software released. However, my version deals with read-write access as well and provides direct command execution.

                   

                  I would release it as an open source library, my question is from Tableau team that may I upload my version of API to github or not? It has a fancy command line client for playing with TDE files as well so I suppose it will be useful for someone else.

                  • 6. Re: Tableau API for reading from extract files
                    Armando Ramirez

                    Hello Tamas

                    Any plans to release your library?

                    I am so interested in querying the extracts, for example, to pull data from DB based on any thing else (besides Date and KeyColumn) inside the extract...or pulling specific sets of data from an extract based on "where" clauses..etc..

                     

                    Looking forward to hear back from this...

                    Regards

                    • 7. Re: Tableau API for reading from extract files
                      Tamas Foldi

                      Sure I would release, but to be honest this is not really "my" API, it is the internal API what the tableau server and desktop uses for managing tde files. I just reverse engineered their DLL and made my own library which allows you to call their functions directly with some minor documentation.

                       

                      So for this I suppose I need some kind of acknowledge that I don't break their licenses or something like that.

                      • 8. Re: Tableau API for reading from extract files
                        Toby Erkson

                        I don't even need to read the EULA to tell you that what you're doing is not allowed.  However, you can check for yourself, just download and run the installer and the first item that comes up is the EULA.  Go to section 3. License, and in subsection 3.9 License Restrictions it will spell it all out for you that, no, what you're doing is against the licensing agreement.  Your only exception would be written authorization from Tableau Software.  Also note section 6, Term of Agreement, subsection 6.1 Term...basicly Tableau can terminate your Agreement!  You will also void your warranty.  I think you get the idea.

                         

                        You also run into the issue of support and, frankly, I've been in the industry for decades and it is EXTREMELY rare for hacked software to get continued support by the author years later.  We know the software will evolve and may likely go through some major revisions.  With people relying upon your hacks will you still be around and have the time in a few years?  It's a rehtorical question because you won't.

                         

                        I can appreciate your enthusiasim.  In my younger years I hacked software.  Heck, I've even hacked hardware (memory switching ROM game cartridge to the disc-drive on my Commodore-64...yeah baby I'm that old! ).  During my stint at Intel they finally realized that customizing every software package they used took more time and money than it was worth so they decided that what comes out of the box is how the software should be used (as-is).

                         

                        My hobby is wrenching on my air-cooled Volkswagens and my motorcycles.  There are lots of things one can do, like adding after-market parts, removing stock components, even remapping the fuel and ignition curves for better performance (okay, that was on my water-cooled VW, I have a few of those, too).  Though one could do everything, you don't.  It won't look good.  It won't perform well.  Some or all of a particular functionality will be lessened or lost.  Remember, just because you can doesn't mean you have to 

                        • 9. Re: Tableau API for reading from extract files
                          Tamas Foldi

                            Your only exception would be written authorization from Tableau Software.

                           

                          Good idea, I wrote a message to beta@tableausoftware to ask for permissions

                           

                          You also run into the issue of support and, frankly, I've been in the industry for decades and it is EXTREMELY rare for hacked software to get continued support by the author years later.

                          I do not really understand why you are saying "hacked" software. It is the same library what Tableau Desktop and Server uses - just in your own code. It does not alter, modify or interact with Tableau products so I this whole "your support is in danger" is not applicable here.

                           

                          But yes I understand your concerns

                           

                          If I got some news from Tableau on how can I release this then I will do that

                          • 10. Re: Tableau API for reading from extract files
                            Tamas Foldi

                            If I got some news from Tableau on how can I release this then I will do that

                            I have the answer from Tableau:  I cannot release the tools / API for reading data back from tableau extract files as it is prohibited by the licenses.

                             

                            So the answer is no.

                            • 11. Re: Tableau API for reading from extract files
                              Piyush Mittal

                              can anyone please post the link which has the orignal API for c/c++ 64 bit released by tableau to write data to TDE? Appreciate your help.

                              • 12. Re: Tableau API for reading from extract files
                                Richard Leeke

                                It's on the beta downloads page.