I am attempting to use the Redshift Package Macro for Unloading to S3. When I compile the code and run it directly in Redshift it works fine, but dbt keeps failing when I run model or build model.
Here is the dbt code:
{{
config(
{
"post-hook": [
"{{ redshift.unload_table('analytics_dev_josh',
'stg_payment_transaction',
s3_path='s3://bucket/folder',
iam_role='arn:aws:iam::xxxxxxxxx:role/xxxxxxxxxx',
header=True,
delimiter=',',
overwrite=true) }}"
]
}
)
}}
Here is the compiled code:
-- compile UNLOAD statement
UNLOAD ('SELECT * FROM "analytics_dev_josh"."stg_payment_transaction"')
TO 's3://bucket/folder'
IAM_ROLE 'arn:aws:iam::xxxxxxxxx:role/xxxxxxxxxx'
HEADER
DELIMITER AS ','
NULL AS ''
MAXFILESIZE AS 6 GB
ESCAPE
ALLOWOVERWRITE
PARALLEL OFF
Here is the error:
14:03:06 Began running node model.analytics.unload_test
14:03:06 1 of 1 START sql table model analytics_dev_josh.unload_test .................... [RUN]
14:03:06 Acquiring new redshift connection 'model.analytics.unload_test'
14:03:06 Began compiling node model.analytics.unload_test
14:03:06 Writing injected SQL for node "model.analytics.unload_test"
14:03:06 Timing info for model.analytics.unload_test (compile): 2023-02-27 14:03:06.181313 => 2023-02-27 14:03:06.184251
14:03:06 Began executing node model.analytics.unload_test
14:03:06 Writing runtime sql for node "model.analytics.unload_test"
14:03:06 Using redshift connection "model.analytics.unload_test"
14:03:06 On model.analytics.unload_test: BEGIN
14:03:06 Opening a new connection, currently in state closed
14:03:06 Redshift adapter: Connecting to Redshift using 'database' credentials
14:03:06 SQL status: BEGIN in 0 seconds
14:03:06 Using redshift connection "model.analytics.unload_test"
14:03:06 On model.analytics.unload_test: /* {"app": "dbt", "dbt_version": "1.4.3", "profile_name": "user", "target_name": "default", "node_id": "model.analytics.unload_test"} */
create table
"domusodatawarehouse"."analytics_dev_josh"."unload_test__dbt_tmp"
as (
);
14:03:06 Postgres adapter: Postgres error: syntax error at or near ")"
LINE 13: );
^
14:03:06 On model.analytics.unload_test: ROLLBACK
14:03:06 Timing info for model.analytics.unload_test (execute): 2023-02-27 14:03:06.184611 => 2023-02-27 14:03:06.337104
14:03:06 On model.analytics.unload_test: Close
14:03:06 Database Error in model unload_test (models/stage/unload_test.sql)
syntax error at or near ")"
LINE 13: );
^
compiled Code at target/run/analytics/models/stage/unload_test.sql
14:03:06 Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '5db3e73a-88f6-4c8c-bd6f-570e08d52b43', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fce41a11130>]}
14:03:06 1 of 1 ERROR creating sql table model analytics_dev_josh.unload_test ........... [ERROR in 0.16s]
14:03:06 Finished running node model.analytics.unload_test
When I cut and paste the compiled code from dbt into redshift directly, it works as required. But when I run the code in dbt, I get the error. Its looks as if dbt is trying to put data into a temp table with the unload statement and it is throwing a syntax error.
You're attempting to run this as a post-hook command on top of a DBT model. DBT will first run your unload_test model to build a table with the same name and then the post-hook runs after that first step is complete. Your model is empty which is what is causing the failure.
Usually, you would run a post-hook on a model to take action on the table after it's built. In your case, you should be running the post-hook in the stg_payment_transaction model. If you don't have a model for this (or don't have access to it because it's in another project), you can move your post-hook to on-run-end in dbt_project.yml