Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature request: support user-defined macros written as SQL #2505

Open
plaflamme opened this issue Apr 24, 2024 · 5 comments
Open

Feature request: support user-defined macros written as SQL #2505

plaflamme opened this issue Apr 24, 2024 · 5 comments
Labels
Improvement Improves existing functionality

Comments

@plaflamme
Copy link
Contributor

Currently, user-defined macros need to be defined as python. An alternative SQL-based syntax would be more consistent with MODEL and AUDIT. That is, having a MACRO function to define user-defines macros in .sql files:

MACRO(
  name forall,
  defaults (
    condition NULL
  )
);
SELECT *
FROM @this_model
WHERE
  @AND(
    @REDUCE(@criteria, (l,r) -> l AND r),
    @condition,
  )

For supporting named arguments, perhaps a more general arguments could work, i.e.: you'd have to define each argument and its default value (and maybe its type for coercion)?:

MACRO(
  name accepted_range,
  arguments (
    min_v NULL,
    max_v NULL,
    inclusive TRUE,
    condition NULL,
  )
);
...
@tobymao
Copy link
Contributor

tobymao commented Apr 24, 2024

@tobymao
Copy link
Contributor

tobymao commented Apr 24, 2024

closing for now but happy to discuss alternatives

@tobymao tobymao closed this as completed Apr 24, 2024
@plaflamme
Copy link
Contributor Author

But those cannot be reused across models. This feature-request is about having something like this:

macros/
  `- hash_to_string.sql

With the following content

MACRO(
  name hash_to_string,
  arguments (columns),
)
TO_BASE64(
  MD5(
    @EACH(@columns, c -> c),
    "|",
    "",
  )
)

And then in any model, do something like

SELECT @HASH_TO_STRING([a,b,c]) AS my_hash

I realize now that my original example wasn't great since it referred to writing audits, but the ask is for writing reusable macros using SQL.

@tobymao tobymao reopened this Apr 24, 2024
@treysp
Copy link
Contributor

treysp commented Apr 24, 2024

We do currently support reusable SQL macros written with jinja

https://sqlmesh.readthedocs.io/en/stable/concepts/macros/jinja_macros/#user-defined-macro-functions

@plaflamme
Copy link
Contributor Author

Which, presumably, is an argument for having an equivalent in SQL directly. As a SQLMesh user that doesn't have to deal with dbt or jinja, it would be a more consistent experience to be able to write these as SQL. Writing them in python is okay, but less accessible to non-developer types of users (e.g.: "data analysts" or similar SQL-mostly roles).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Improvement Improves existing functionality
Projects
None yet
6 participants