Some dbt_utils macros do not work with Spark SQL

[bdelamotte]

Describe the bug

It appears that many dbt_utils macros are not supported with Spark SQL due to how a lot of the macros are casting with :: instead of cast

Steps to reproduce

1. Add dbt_utils to your packages.yml file:

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.2

2. Create a simple model with the following SQL:

select {{ dbt_utils.current_timestamp() }}

Expected results

A model should be created just fine.

Actual results

Runtime Error in model dbt_test (models/staging/dbt_test.sql)
  Database Error
    Error running query: org.apache.spark.sql.catalyst.parser.ParseException: 
    mismatched input ':' expecting <EOF>(line 6, pos 21)
    
    == SQL ==
    /* {"app": "dbt", "dbt_version": "0.18.1", "profile_name": "databricks", "target_name": "dev", "node_id": "model.dbt_databricks.dbt_test"} */
    create or replace view brian_dev_stg.dbt_test
      
      as
        select 
        current_timestamp::
    ---------------------^^^
        timestamp
    

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Screenshots and log output

System information

The contents of your packages.yml file:
packages:

• package: fishtown-analytics/dbt_utils
  version: 0.6.2

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: Spark SQL)

The output of dbt --version:

installed version: 0.18.1
   latest version: 0.18.1

Up to date!

Plugins:
  - bigquery: 0.18.1
  - snowflake: 0.18.1
  - redshift: 0.18.1
  - postgres: 0.18.1
  - spark: 0.18.0

The operating system you're using:
macOS Catalina Version 10.15.7
The output of python --version:
Python 3.7.6

Additional context

Here is one place where casting is done by :: instead of cast()
https://github.com/fishtown-analytics/dbt-utils/blob/9feaccc327a7409298a2bc362db53c2e597024fa/macros/cross_db_utils/current_timestamp.sql#L6

Are you interested in contributing the fix?

Yes! I am happy to change all the :: to cast() or provide another solution. I would love to contribute to dbt-utils!

[emilieschario]

@jtcohen6 I know that dbt utils is outside of core, but do you have any thoughts here on where the core-supported plugins start and end vs things like this? I was under the impression the dbt core --> has plugins and if that was the case, dbt utils would support those core plugins. But maybe that's not true?

Or maybe Spark is just behind so this is the first time this has been caught?

[clrcrl]

I'm not a spark expert, but I do know that there's a separate spark-utils package that is likely useful here!

[jtcohen6]

IMO "plugin packages" are definitely the answer here, and spark-utils is the proof point.

Thanks to adapter-based macro dispatching, introduced in v0.18.0, you can install a package that "shims" support for another package's macros. So far, I've created spark__ implementations of a subset of dbt-utils macros that don't natively work on Spark.

As long as you install both packages, and define a var in dbt_project.yml like:

vars:
  dbt_utils_dispatch_list: ['spark_utils']

Throughout your project, you can call macros such as dbt_utils.dateadd, and dbt will actually use spark_utils.spark__dateadd behind the scenes.

[otosky]

just want to update the note above to v0.20.0 syntax for anyone stumbling across this issue like I did

dispatch:
 - macro_namespace: dbt_utils
   search_order: ['spark_utils', 'dbt_utils']

This is a super cool feature btw!

dispatch docs

[jtcohen6]

And it works even more "out of the box" in v1.2: dbt-labs/dbt-spark#359

Closing this one, which has been open for a while :)