We recently built a data processing pipeline that uses Clickhouse as our big data database.
In the pipeline, data is continually ingested into a huge table (let's call it table A) with ~60 columns. It now has billions of rows of data.
Table A is the data source for multiple dependent target tables that perform aggregation on the data. The target tables are connected to Table A via the Incremental type of Materialised views (MV).
We recently need to connect additional Incremental MVs + target tables to Table A.
However, since the MVs are incremental, the new target tables are not populated.
To solve this problem, we've considered:
- Renaming Table A to Table B
- Recreate an empty Table A, and then
- Re-insert the data from Table B back to Table A batch by batch
However, this seems excessively troublesome and time-consuming.
What would be the best approach to handle this scenario?