Pushdown Processing Advanced Function Mapping

Mike Pearson
Mike Pearson Contributor
in General

Evening all,

We're looking at implementing pushdown with Snowflake and have hit a couple of functions where we've had to workaround to avoid blocking the pushdown process. However, this advanced function mapping (as per documentation screenshot below) may help us fill some of those gaps and keep our workflows simpler. 3 questions:

  1. Does anyone have an example of the JSON file so I can understand how to structure it?
  2. Can it be applied to Native Functions which have no default mapping?
  3. Can it override the SQL behind the already supported Native Functions?
image.png

Example for Q2: the In Range function isn't supported, but rather than force users to have to replace all these with More Than AND Less Than, I'd like to make this as a custom mapping.

Example use case for Q3: The After function uses REGEX_INSTR and so full stop is treated as a special character - and even trying to escape that character it still didn't work correctly - so it may be more appropriate for our use case to overwrite and use Snowflakes POSITION function instead.

Thanks
Mike

Tagged:

Best Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited October 16 Answer ✓

    Example JSON mapping 4 Functions:

    {  "functions": [
    {"token": "LowerCase", "sql": "UPPER(#v1#)"},
    {"token": "IfThenElse", "sql": "case when #v1# then #v2# else #v3# end"},
    {"token": "MoreThan", "sql": "#v1# > #v4#"}, 
    {"token": "GetJobTitleFromID", "sql": "DECLARE @Title VARCHAR (50) EXEC dbo.spReturnJobTitle @EmployeeID  =ID, @JobTitle  = @Title OUTPUT SELECT  @Title" }
  ]}

    token - is the External label value of a Custom Function or the Native Function name as shown in the Function script editor or Show script window.

    Hopefully that clarifies 2&3 that you can map native functions and override native function mappings.

    Where you think native mappings for Snowflake functions can be improved do keep letting us know and it can be investigated.

  • Mike Pearson
    Mike Pearson Contributor
    Answer ✓

    Thanks Josh - given me the confidence to give it a whirl!

    Not sure I fully understand how you know how the variables map, but I assume in the MoreThan example: the Compare Type is not a "real" variable in this case, so ignoring that, it's purely the order they appear in the UI?

    image.png
  • Josh Boxer
    Josh Boxer Administrator
    edited October 17 Answer ✓

    If you open the Function script editor you will find it easier to determine the variables and the order:

    image.png

    Also the team provided a load more examples which will be useful:

    https://cdn.edq.com/static/aperture-data-studio/sql.snowflake.user.json

Answers

Categories