generate_source TRINO can not use ilike

[tekkisse]

Describe the bug

running generate_source against TRINO connector returns error

20:28:00.233549 [debug] [MainThread]: On macro_generate_source: select distinct table_schema as "table_schema", table_name as "table_name",

        case table_type
            when 'BASE TABLE' then 'table'
            when 'EXTERNAL TABLE' then 'external'
            when 'MATERIALIZED VIEW' then 'materializedview'
            else lower(table_type)
        end as "table_type"

    from iceberg.information_schema.tables
    where table_schema ilike 'loadpedwt'
    and table_name ilike '%'
    and table_name not ilike ''

20:28:00.233655 [debug] [MainThread]: Opening a new connection, currently in state init 20:28:00.278016 [debug] [MainThread]: Trino adapter: Trino query id: 20230104_202800_00513_ivciy 20:28:00.278376 [debug] [MainThread]: Trino adapter: Trino error: TrinoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 13:28: mismatched input 'ilike'. Expecting: '%', '', '+', '-', '.', '/', 'AND', 'AT', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', 'WINDOW', '[', '||', <EOF>, ", query_id=20230104_202800_00513_ivciy) 20:28:00.278892 [debug] [MainThread]: On macro_generate_source: ROLLBACK 20:28:00.279226 [debug] [MainThread]: On macro_generate_source: Close 20:28:00.280299 [error] [MainThread]: Encountered an error while running operation: Database Error TrinoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 13:28: mismatched input 'ilike'. Expecting: '%', '', '+', '-', '.', '/', 'AND', 'AT', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', 'WINDOW', '[', '||', <EOF>, ", query_id=20230104_202800_00513_ivciy) 20:28:00.281273 [

TRINO does not support ILIKE - code works if it could be changed to just LIKE

[owenprough-sift]

Same general issue as #90, but for a different adapter. This same issue affects dbt-athena as well (which uses Presto/Trino under the hood).

[donatobarone]

Same issue happens for dremio as well, where the ilike is a function taking 2 arguments. I believe something like adapter.ilike() or something like that should be used at this level

[wtfzambo]

The issue does not come directly from here tho but from the dbt utils package. That's where the ilike part is defined. A simple fix is to add in your own local macros the following (replace Athena with whatever your adapter is)

{% macro athena__get_tables_by_pattern_sql(
    schema_pattern, table_pattern, exclude="", database=target.database
) %}

    select distinct
        table_schema as {{ adapter.quote("table_schema") }},
        table_name as {{ adapter.quote("table_name") }},
        {{ dbt_utils.get_table_types_sql() }}
    from {{ database }}.information_schema.tables
    where
        table_schema like lower('{{ schema_pattern }}')
        and table_name like lower('{{ table_pattern }}')
        and table_name not like lower('{{ exclude }}')

{% endmacro %}

[gwenwindflower]

Thanks for the patience on addressing this 🙏🏻 the above is correct, this is actually a macro in dbt-utils being called, which is now being weighed as something to port into dbt-core. https://github.com/dbt-labs/dbt-core/issues/6789 -- will leave this open for now as when these changes go through, they'll be reflected in dbt-utils and we'll pull them through here, and this will serve as a reminder to update the utils version. Thanks for flagging!

[damian3031]

Currently, the best solution would be to install additional package - trino_utils, which provides a Trino-compatible implementation of get_tables_by_pattern_sql macro.

Please install trino_utils package according to Installation Instructions, and it should work.