dbt get value from agate.Row to string

Question

I want to run a macro in a COPY INTO statement to S3 bucket. Apparently in snowflake I can't do dynamic path. So I'm doing a hacky way to solve this.

{% macro unload_snowflake_to_s3() %}

    {# Get all tables and views from the information schema. #}
    {%- set query -%}
        select concat('COPY INTO @MY_STAGE/year=', year(current_date()), '/my_file FROM (SELECT OBJECT_CONSTRUCT(*) from my_table)');
    {%- endset -%}

    -- {%- set final_query = run_query(query) -%}
    -- {{ dbt_utils.log_info(final_query) }}
    -- {{ dbt_utils.log_info(final_query.rows.values()[0]) }}


    {%- do run_query(final_query.columns.values()[0]) -%}

    -- {% do final_query.print_table() %}

{% endmacro %}

Based on above macros, what I'm trying to do is:

1. Use CONCAT to add year in the bucket path. Hence, the query becomes a string.
2. Use the concatenated query to do run_query()again to actually run the COPY INTO statement.

Output and error I got from dbt log:

09:06:08  09:06:08 + | column                                                                                                                                                                                                                                                          | data_type |
| ----------------------------------------------------------------------------------------------------------- | --------- |
| COPY INTO @MY_STAGE/year=', year(current_date()), '/my_file FROM (SELECT OBJECT_CONSTRUCT(*) from my_table) | Text      |

09:06:08  09:06:08 + <agate.Row: ('COPY INTO @MY_STAGE/year=2022/my_file FROM (SELECT OBJECT_CONSTRUCT(*) from my_table)')>
09:06:09  Encountered an error while running operation: Database Error
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 0 unexpected '<'.
root@2c50ba8af043:/dbt# 

I think the error is that I didn't extract the row and column specifically which is in agate format. How can I convert/extract this to string?

Answer 1

You might have better luck with dbt_utils.get_query_results_as_dict.

But you don't need to use your database to construct that path. The jinja context has a run_started_at variable that is a Python datetime object, so you can build your string in jinja, without hitting the database:

{% set yr = run_started_at.strftime("%Y") %}
{% set query = 'COPY INTO @MY_STAGE/year=' ~ yr ~ '/my_file FROM (SELECT OBJECT_CONSTRUCT(*) from my_table)' %}

Finally, depending on how you're calling this macro you probably want to gate this whole thing with an {% if execute %} flag, so dbt doesn't do the COPY when it's parsing your models.