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', '[', '||', , ", 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', '[', '||', , ", 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. dbt-labs/dbt-core#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.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.