CODEWITHSUNDEEP
postgresqltableau-api
Caullyn
Caullyn

Reputation: 166

tableau handle partitioned tables

I have a load of partitioned tables which I would like to consume into Tableau. This worked really well with Qlik sense, because it would consume each table into it's own memory, then processes it.

In Tableau I can't see a way to UNION tables (though you can UNION files). If I try to union it as custom sql, it just loads for hours, so I'm assuming it's just pulling all the data at once, which is 7GB of data and won't perform well on the db or Tableau. Database is PostgreSQL.

The partitions are pre-aggregated, so when I do the custom query union it looks like this:

SELECT user_id, grapes, day FROM steps.steps_2016_04_02 UNION
SELECT user_id, grapes, day FROM steps.steps_2016_04_03 UNION
SELECT user_id, grapes, day FROM steps.steps_2016_04_04 UNION

Upvotes: 0

Views: 580

Answers (1)

MatheusOl
MatheusOl

Reputation: 11855

If you can guarantee that the data of each table is unique, then don't use UNION, because it has to an extra work to make distinct rows out of it.

Use UNION ALL instead, which is basically an append of rows. UNION or UNION DISTINCT (the same) like you showed is somewhat equivalent to:

SELECT DISTINCT * FROM (
    SELECT user_id, grapes, day FROM steps.steps_2016_04_02 UNION ALL
    SELECT user_id, grapes, day FROM steps.steps_2016_04_03 UNION ALL
    SELECT user_id, grapes, day FROM steps.steps_2016_04_04
) t;

And the DISTINCT can be a very slow operation.

Another simpler option is to use PostgreSQL's partitioning with table inheritance and work on Tableau as a single table.

Upvotes: 1

Related Questions