10 Replies Latest reply on Nov 19, 2019 4:12 PM by William Ayd

    Union two extracts

    Matan Zuckerman

      Hi, I'm using tableau version 10.5.3 and I need an automated procedure for union two extracts (this feature is available in Tableau Prep but without the ability to do it automatically)

      Is there a way (using Tableau API for python) writing a script  that does it? I couldn't understand how to do this using the API.

      Thanks for all responders.

        • 1. Re: Union two extracts
          Ken Flerlage

          When you say you want to union two "extracts", I assume you are talking about Tableau extracts (TDE or HYPER)?

          • 2. Re: Union two extracts
            Matan Zuckerman

            Yes, I have two Tableau extracts (TDE) with same columns and I want to union those two.

            I know that using the python extract API will convert the TDE format to hyper but tableau 10.5 knows how to read hyper so it doesn't matter to with which file i'll have in the end.

            • 3. Re: Union two extracts
              Ciaran Bource

              HI Matan,

               

              In Tableau Desktop, in the top left "Data" pane, right click on your extract, then select "Extract", the "Append from File"

               

              That should do it !

               

              More information in the link below

               

              Add Data to Extracts

               

              Ciaran

              3 of 3 people found this helpful
              • 4. Re: Union two extracts
                Matan Zuckerman

                Hi, Thank you for the response but I need an automated process (each day) and I don't want to do it manually.

                 

                Matan

                • 5. Re: Union two extracts
                  Ciaran Bource

                  Hi Matan,

                   

                  I feel that this is more of a feature request then.

                   

                  I found a similar idea here, which you can vote for :

                   

                  https://community.tableau.com/ideas/2163

                   

                  Alternatively, you can post your own idea in the Ideas section, the more votes you get, the better chances you idea will have to be considered for a possible implementation.

                   

                  Ideas

                   

                  Ciaran

                  • 6. Re: Union two extracts
                    Alexander Mou

                    Matan Zuckerman

                     

                    There are two scenarios for union: 1.accumulation of data 2.analysis and comparison between 2 or more data sets.

                     

                    There are automation solutions for both scenarios without extra tools:

                    1.using cumulative data extraction

                    2.case by case. If you tell me what specific analysis you want to do, what data sets are, etc. I can tell you what the tableau solution is.

                     

                    I frequent less the forum these days. If you wish, contact me at alexandermou2000 at gmail.

                    • 7. Re: Union two extracts
                      Suraj  Kumar

                      Actually as of now, In Tableau Desktop Cross Database Union is not possible.

                       

                      But you can use python extract API to read the data from both the sources and union them and then take the extract ie. to create a tde or .hyper file.

                       

                      You can check EXTRACT API 2.0 in the Developer Options.

                       

                      Hope this idea helps you in moving forward to the solution.

                       

                      Regards,

                      Suraj Kr.

                      • 8. Re: Union two extracts
                        Narendrakumar Manica

                        Hi Matan,

                         

                        Using Tableau REST API and Tableau prep (Till 2019.2)

                         

                        You can use Tableau Rest API to download the extract using "Download Data Source" method which will down the content in .tdsx format. Then you can unzip the file to get the extract. once you get the extract  using Tableau Prep make union of both the extract.

                         

                        Tableau Prep v 2019.3

                        In  Tableau Prep v 2019.3  currently in beta . you can directly connect to extract from the Server , No need to download and make union of extracts and automate the extract using command line

                         

                        Hope this answers your question

                         

                        Regards,

                        Naren Manica

                        • 9. Re: Union two extracts
                          Adrian Vogelsgesang

                          Hi Matan,

                           

                          Is there a particular reason why you are still using TDE instead of Hyper-based extracts?
                          For Hyper extracts, you can easily automate unioning using the Tableau Hyper API.
                          This also works neatly in headless environments without any user interactions necessary.


                          You probably want to adjust "input_files" and "output_file" in the script below.

                          The script would look something like this (Python 3):

                           

                          from tableauhyperapi import HyperProcess, Connection, Telemetry, TableDefinition, TableName, SchemaName, Inserter, CreateMode

                          from glob import glob

                          from time import time

                          import os

                           

                          input_files = glob("WorldIndicators_*.hyper")

                          table_name = TableName('Extract','Extract')

                          output_file = "WorldIndicatorsMerged.hyper"

                           

                          # Let's delete the output file to make sure we can rerun this script

                          # even if the output already exists.

                          if os.path.exists(output_file):

                              os.remove(output_file)

                           

                          # Start a new Hyper instance

                          start_time = time()

                          with HyperProcess(Telemetry.SEND_USAGE_DATA_TO_TABLEAU, 'unionfiles_inefficient') as hyper:

                              # Create a connection without any connected databases

                              with Connection(hyper.endpoint) as connection:

                                  # Connect to all our input databases

                                  for i, file in enumerate(input_files):

                                      connection.catalog.attach_database(file, alias=f"input{i}")

                                  # Prepare the output database

                                  connection.catalog.create_database(output_file)

                                  connection.catalog.attach_database(output_file, alias="output")

                                  connection.catalog.create_schema(SchemaName("output", table_name.schema_name))

                                  # Build the CREATE TABLE AS command which unions all our inputs

                                  union_query = ' UNION ALL\n'.join(

                                      f'SELECT * FROM "input{i}".{table_name}' for i in range(len(input_files)))

                                  create_table_sql = f'CREATE TABLE "output".{table_name} AS \n{union_query}'

                                  # And execute it

                                  connection.execute_command(create_table_sql)

                          • 10. Re: Union two extracts
                            William Ayd

                            You can use pantab to achieve this pretty easily. One option is to append to an existing table in a Hyper extract:

                             

                            Usage Examples — pantab 0.0.1b5 documentation

                             

                            Alternately you can read data into DataFrames and use pandas.concat to combine them before using pantab to write to a Hyper extract

                             

                            pandas.concat — pandas 0.25.3 documentation