Macro isn't working -> unknown function

Help
, , ,
1

Hello everyone, I’d love to get some help. I reckon, I just have some gaps in understanding how the jinja works under the hood. many thanks in advance!

The problem I’m having

I’m tring to create a macro in order to generate all columns for staging tables by using a source table where $1 is a variant column (parquet file). however when i run the macro within a model it gives the following exception: SQL compilation error: Unknown function INFER_TABLE_SCHEMA

The context of why I’m trying to do this

I’d like to create an automated way to generate all columns with its matching types from a parquet file. I’m using snowflake as a dwh, and the source tables are as following:

  • $1: content
  • $2: filename
  • $3: date

What I’ve already tried

I’m using the infer_schema function to get all the rows i need. Afterwards, i’m trying to get all the rows as one string, so when i call the macro within a model, it spits out all rows as one string. (please see script below)
please don’t mind the log and print, i just wanna know what is the difference between the two

Some example code or error messages

macro (str, str, int):
{% macro infer_table_schema(X__file_stage_path, file_format_path, max_file_count) %}
    
    {{ print("Running infer_table_schema: stage_path: " ~ arg1 ~ ", file_format_path " ~ arg2) }}
    {{ log("Running infer_table_schema: stage_path: " ~ arg1 ~ ", file_format_path " ~ arg2, info=True) }}

    {% set query = """
                      select expression || ' AS ' || column_name
                      from table(infer_schema(
                                  LOCATION => \'""" ~ X__file_stage_path ~ """\'
                                , FILE_FORMAT => \'""" ~ file_format_path ~ """\'
                                , IGNORE_CASE => TRUE
                                , MAX_FILE_COUNT => """ ~ max_file_count ~ """
                              )
                            )

                  """
      %}

    {{ print("Query: " ~ query) }}

    {% set res_schema = run_query(query) %}
    
    {# Fetching the definition for each column in the parquet file #}
    {% set schema = res_schema.columns[0].values() %}

    {% set schema_str = schema|join(", ") %}
    
    {{ return(schema_str) }}

{% endmacro %}
model:
select

infer_table_schema('@<X_file_stage_path>', '<file_format_path>',  5)
  
from {{ source('X', 'TABLE_X') }}

Any help is highly appreciated!

2

Macros have to be called inside {{ }}

So

select

{{ infer_table_schema('@<X_file_stage_path>', '<file_format_path>',  5) }}
  
from {{ source('X', 'TABLE_X') }}

And just another point, if you want the result of the macro to be written as part of the sql, you should not use return, you should make the macro like

...
    {% set schema_str = schema|join(", ") %}
    
    {{ schema_str }}

{% endmacro %}
3

Hi Bruno,
thanks for the reply!

Grrrr, can’t believe i missed the {{ }}. however now, i’m getting another issue:

also, many thanks for the return remark. noted!

Compilation Error in model STG_TABLE_X (models/STG/SAP/STG_TABLE_X.sql)
  'None' has no attribute 'table'

any idea?

4

Yeah, give this page a read

It is exactly about your issue

5

will do, thanks again for the quick replies!

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.