[MCOL-3579] Manually set distribution key Created: 2019-10-29 Updated: 2021-03-25 Resolved: 2021-01-17 |
|
| Status: | Closed |
| Project: | MariaDB ColumnStore |
| Component/s: | N/A |
| Affects Version/s: | None |
| Fix Version/s: | N/A |
| Type: | New Feature | Priority: | Major |
| Reporter: | Assen Totin (Inactive) | Assignee: | Todd Stoffel (Inactive) |
| Resolution: | Won't Do | Votes: | 0 |
| Labels: | None | ||
| Issue Links: |
|
||||||||
| Description |
|
We want to have manual control over the distribution key in a multi-PM layout. The reason for this is to allow a JOIN between two large tables (which, while bad, is sometimes really needed in analytics) to be pushed down without redistribution. We want this to be available without the need to manually split the data and load it with cpimport (which split is not supported by its modern replacement, the bulk load API). The distribution key should be set in the CREATE TABLE statement - either by utilising the PARTITION BY (which is, AFAIK, currently unused in CS), by adding a new keyword for this, or simply by specifying the key in a comment (a technique long used in Spider engine, for example). The distribution key should be either a single column or a list of columns. If no key is specified, the current method should be retained (a full hash of the row, I think). It is OK to not support changing the distribution key via ALTER TABLE as this could be a lengthy process; alternatives include dump, drop and re-import. If a custom distribution key is set, it should be observed at least by the new bulk load API and its client library/bindings/utilities. Not backporting to cpimport could be OK. Changes will have to be made to the metadata storage (so that the distribution key would have an explicit definition) and to the import facilities (which will need to read and use the specified key). This feature implicitly requires that data redistribution on JOIN be turned off if both tables are partitioned by the same key. A similar requirement and mechanism when using replicated tables is described in |
| Comments |
| Comment by David Hall (Inactive) [ 2019-11-04 ] |
|
At first, I couldn't see the usefulness of such an arrangement. It sort of defeats the distributed query concept. But... This could allow for large table JOIN large table all done on the PMs if the join is against the aforementioned column. We might want to implement a sort-merge or nested loop join, as large JOIN large may not be good against hash join. Using this scheme, all specific join values for the tables are guaranteed to be on a given PM. A simple merge at the UM would be used to get the final result. This is of course, ingenious. But the implementation may be a bit. All code that could change values (cpimport, update, insert, API, redistribute) would need to be taught to keep the partitions correct. A whole new join type (even if we stay with hash join) would need to be implemented, and the connector would need to be aware that this type of join could be used, based on the common PARTITION BY in the table. |