How do I make a per-environment clickhouse SQL UDF?

[jwhitaker-gridcog]

Hey guys,

I'm using Clickhouse, and I want to pull out some shared code (in this case, JSON parsing) into a UDF. Running as a pre-statement works kind of ok at first glance:

model ( ..., kind view );

create or replace function my_function as (x) -> stuffWith(x);

select
  name, stuffWith(x)
from ...;

However, Clickhouse has no namespacing of functions to databases. :( So running any sqlmesh environment with this pattern would clobber the same global function my_function.

Any hints how I could do this? is there an off the shelf macro I could use - create or replace function @suffixize('my_function') as (x) -> stuffWith(x)? any other hints?

[georgesittas]

Off the top of my head, I see three possibilities:

• Use CREATE FUNCTION IF NOT EXISTS ... so that you don't have to overwrite / clobber the object as you say
• Hook into the context using the Python API and execute the CREATE FUNCTION OR REPLACE ... once after it's initialized
• Use SQLMesh macros to implement the UDF's logic

Do any of these work for you? There is no macro that can be used to peek into the current environment, because using something like that would invalidate the very concept of Virtual Data Environments, since running plan on different environments would always produce a diff.

[jwhitaker-gridcog]

I don't think the first two work, as I would like to have the function associated with a sqlmesh environment so I can iterate on it.

The third might? I'll play... having the UDF as a real UDF is preferable for interactive use though.