Hi everyone, Is there a way to create an in-memory table using the Table API? Specially with the SQL API. Something like

create table country_code_lookup_table (
   country_name VARCHAR, 
   country_code
)
with (
   'connector' = 'memory'
);

insert into country_code_lookup_table values (...), ...;

The idea is to create a table to replace a bunch of long CASE statements by joining to this table instead. I tried using a file table but it doesn’t work well.

Wouldn't it make sense to create a view over your source table?

Sorry, what do you mean? My use case is that I have to translate some custom country codes. So currently, the code looks like:

SELECT
	id,
	…
	CASE 
		WHEN country_name = '…' THEN '…'
        — repeat for every almost 200 countries
	END
FROM source_table_1;

SELECT
	id,
	…
	CASE 
		WHEN country_name = '…' THEN '…'
        — repeat for every almost 200 countries
	END
FROM source_table_2;

SELECT
	id,
	…
	CASE 
		WHEN country_name = '…' THEN '…'
        — repeat for every almost 200 countries
	END
FROM source_table_3;

I have the option to create an UDF but I don’t want to split the logic.

I'm thinking that you should create a view over your 3 source tables with a UNION ALL, then create another view where you apply those CASES over the view.

This is a very simplified version of what I have. I’m doing this lookup over 6 different sources. And there are multiple columns, like

birth_country

,

residence_country

,

past_country

and so on. So doing a UNION all would still require a lot of code. The current solution is working though, it’s just too cumbersome to work with. Hence the idea of creating a “temporary” table to simplify it

You want to actually materialize the intermediate results, incl snapshots during checkpointing etc to avoid data loss.

Basically joining them, grouping and sending them into a single JDBC sink

Hmmm. I'm not sure what you've already tried, it would be quite nice to get more insights into the actual business logic so that it could also be determined how that would be (potentially added to/in) Flink SQL