0

What do I have:

  1. I have several Google Accounts and their corresponding Google Analytics accounts. New ones will be added in the future.
  2. There is also a ClickHouse database, which is located in a cluster on Yandex Cloud. (Yandex Managed Service for ClickHouse)
  3. And there is a connector that uploads data from Google Analytics to a separate ClickHouse table.

Problem: The problem is that, due to the specifics of this table connector, several tables are created when only one is needed.

My idea: I can get the name of all tables and group them into an array. Since all tables have the same columns, I can join tables using UNION ALL. But I don't know how to iterate through the array and connect each table to each other. (As far as I understand, Yandex Cloud does not provide for the creation of custom functions). Can I loop through the array somehow? Are there any other ways to join tables?

Kagistar
  • 1
  • How many tables would you UNION ALL? Asking because UNION ALL over a list of tables first I don't think it's possible to do it dynamically and second does not look like a great idea in terms of performance. In case you want to go that path have you considered using a MATERIALIZED VIEW to have all the records on a single table? If you need to filter by source table you could have a column with the table_name and just solve it by `SELECT * FROM MATERIALIZED_VIEW WHERE table_name IN (list_of_tables)` – alrocar Feb 02 '23 at 12:12

0 Answers0