Group more tables in just one (for one month)

[giad]

Hi, I connect Knime to BigQuery and I can see one table with result for each day.
In order to analyze one entire month in only one table, how can I group all tables for March 2021 for example?

Otherwise I should make join for each day but it is too long…Is there a way to put all tables for march in only one? the structure is the same.

Thanks
Giad

[ScottF]

If the structure of all the tables is the same, what about just creating a new table using UNION statements in a relevant KNIME DB node?

select * from events_20210317
UNION
select * from events_20210318
UNION
[etc...]

[giad]

Thanks but it takes too much time because I need everyday add a new table in the union: each day corresponds to one table…now it is simple because I have few days but in some months I will have too much tables and I cannot use union.

Do you have other alternatives?
Thanks
Giad

[bruno29a]

The UNION method same as @ScottF 's is the only way I can think of. However, the trick here is to generate the statement dynamically - you’ll need a couple of loops.

Loop through the month (1-12, padding leading 0 for 1-9), loop through each month to get:

• last date of the month (does it end by 28, 29, 30, 31?)
• loop for each day (1-28/29/30/31, padding leading 0 for 1-9)
• create variable for dynamic table name
• generate SQL string by appending UNION or list of string with select from events_
• if you created a list, you can “glue” each element with " UNION " and create the SQL string

You can then execute that SQL string

In the SELECT statement, I would add the table name from which the data is coming, or at least the dynamic date, in case you need to target specific original tables, or need to do groupby some other conditions related to dates:
SELECT *, ‘<dynamic_original_table_name_or_dynamic_date>’ FROM events_…

[bruno29a]

Hi @giad , here’s a workflow I put together in less than 1hr, you can adapt it to what you need.

In my workflow, I take year as input, and it will generate a SELECT statement with the UNION for each day per month. You can change things around for specific year+month.

This is what it looks like:

Input:

Ouput:

You can then execute each of these rows as a query.

Here’s the workflow:
Group more tables in just one (for one month).knwf (51.9 KB)

[giad]

Thanks but I need db nodes because I Should make this union before db reader node…
In your workflow I see nodes from a table creator. How can I do your workflow starting from google big query connector?

Thanks
giad

[ipazin]

Hello @giad,

BigQuery supports querying multiple tables using wildcard table so something like this should work:

select * from xyz.events_202103*

to perform union on all tables from March.

See here for more info:

Querying multiple tables using a wildcard table  |  BigQuery

Br,
Ivan

[bruno29a]

Hi @giad You can pass that column in a db node, that was the point of generating this via Knime

[bruno29a]

Here’s a quick workflow to show you how you can execute these queries from a data node:

Input data:

Output as expected:

Here’s the workflow:use query from table.knwf (16.1 KB)

If what you are using supports what @ipazin suggested, then use it instead. But the above is good for general knowledge

[giad]

Thanks, but should I make the query in the BigQuery or also in the DB Query Reader Node into Knime?

[ipazin]

Hello @giad,

if you want to retrieve data from BigQuery and have it into KNIME you should write query in KNIME.

Note: I haven’t tested wildcard tables querying and have seen there are certain limitations/prerequisites to such queries so you should explore given link (and possibly other documentation) to make it work.

Br,
Ivan

[giad]

Hello @ipazin
I am in the db table selector and I want to write the query. How can I write it? I need a union between all tables starting with events_202103* (so I can analyze all March tables)
Below the image:

Thanks
Giad

[ipazin]

Hello @giad,

not a expert with BigQuery but would try something like this based on your screen shots and above link:

select * from analytics_223280726.events_202103*

Also would use DB Query Reader node if you are looking to get data into KNIME.

Br,
Ivan

[giad]

Hi, it does not work:

I have tried also with " " after from, or nothing, but I have the same error…

[bruno29a]

Hi @giad , as per @ipazin 's suggestion, can you use the DB Query Reader instead? You should not need db table selector when using the db query reader, you will just need a connection.
Also, do not enclose the table in any quotes

[giad]

I solved it! I used DB query reader with:
select * from analytics_223280726.events_202103*

Many thanks
Giad