search

fivetran / dbt_linkedin_source

Fivetran's Linkedin Ads source dbt package
https://fivetran.github.io/dbt_linkedin_source/
Apache License 2.0
5 stars 5 forks source link

[Bug] dbt_utils.get_url_parameter does not work for databricks #55

Closed fivetran-joemarkiewicz closed 11 months ago

fivetran-joemarkiewicz commented 12 months ago

Is there an existing issue for this?

Describe the issue

When using Databricks the get_url_parameter macro does not work and results in the below error. This is the result of the get_url_parameter macro (and subsequently the split macro) attempting to pull the index 1. However, if the index 1 does not exist in the data then it will result in the below error. Other warehouses are able to circumvent this due to a safe offset. However, databricks does not have this function.

Relevant error log or model output

19:50:59  Runtime Error in model linkedin_ads__url_report (models/linkedin_ads__url_report.sql)
19:50:59    [INVALID_ARRAY_INDEX] The index 1 is out of bounds. The array has 1 elements. Use the SQL function `get()` to tolerate accessing element at invalid index and return NULL instead. If necessary set "ansi_mode" to "false" to bypass this error.
19:50:59    == SQL of VIEW stg_linkedin_ads__creative_history(line 289, position 5) ==
19:50:59        split(
19:50:59        ^^^^^^
19:50:59            click_uri,
19:50:59    ^^^^^^^^^^^^^^^^^^
19:50:59            
19:50:59    ^^^^^^^^
19:50:59    
19:50:59    
19:50:59            -- escape if starts with a special character
19:50:59    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
19:50:59            case when regexp_extract('utm_source=', '([^A-Za-z0-9])(.*)', 1) != '_'
19:50:59    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
19:50:59                then concat('\\', 'utm_source=')
19:50:59    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
19:50:59                else 'utm_source=' end
19:50:59    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
19:50:59    
19:50:59    
19:50:59        
19:50:59    ^^^^
19:50:59            )[(1)]
19:50:59    ^^^^^^^^^^^^^^

Expected behavior

The get_url_parameter works without failure

dbt Project configurations

Latestt

Package versions

Latest

What database are you using dbt with?

databricks

dbt Version

1.7.0

Additional Context

No response

Are you willing to open a PR to help address this issue?

fivetran-joemarkiewicz commented 11 months ago

The latest release of this package has been updated to use the custom linkedin_source.extract_url_parameter() macro. This addresses the immediate issue raised in this feature request. However, in the next breaking change of this package we will want to remove the custom macro and swap the package to leverage the fivetran_utils.extract_url_parameter() macro.

This was not implemented originally as it could possibly have caused breaking changes, we wanted to address the issue in a timely manner while ensuring we did not break any transformation pipelines.