Open tekkisse opened 1 year ago
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).
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
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 %}
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!
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.
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",
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