prehook sql throw exception

[hagai-arad]

I wanted to exceute a pre_hook query like this
{{ config(materialized='view',pre_hook="drop table if exists my_first_dbt_model") }}
But I found that in athena , the sql become
-- /* {"app": "dbt", "dbt_version": "1.2.1", "profile_name": "local", "target_name": "dev", "node_id": "model.dbtest.my_first_dbt_model"} */ drop table if exists my_first_dbt_model
since all the sql stay in one line , the sql had been miss as a comment , It failed

[nicor88]

@hagai-arad hey the issue it's most likely due to this https://github.com/dbt-athena/dbt-athena/blob/main/dbt/adapters/athena/query_headers.py#L19 I will include drop statement in there too.

[nicor88]

@hagai-arad could you try this branch? #109 should fix your issue, I didn't fully tested, but I had something similar with vacuum in the past.

[hagai-arad]

@nicor88 thanks for the quick fix!
It works for drop command, but not for create command. Can you add it as well?

I don't have others in mind, but I think any supported command should be available in the pre/post-hook

[nicor88]

why will you run a create command in a pre/post hook? can't you use dbt models for that?

[hagai-arad]

A few reasons:

• Does dbt supports all functionalities and properties I can achieve with a create table statement?
• If I use a dbt model to create a table, the data types are defined by the data itself and not by the table definition
• I'm pretty sure this is supported in other dbt connectors (e.g. postgres)

[nicor88]

Does dbt supports all functionalities and properties I can achieve with a create table statement?

it should, if not, please create other issues in order to implement

If I use a dbt model to create a table, the data types are defined by the data itself and not by the table definition

You can use explicit casting to overcome this issue, same for other dbt-adapter

I'm pretty sure this is supported in other dbt connectors (e.g. postgres)

Yes, could be, but I still feel that is wrong to use a create statement as a post hook, I mean there code be edge cases reason why we want to have that. Still I didn't have the edge case why you want to do so, so please maybe provide a your specific use cases for me/us to understand the issue, as other user might have the same, and maybe we are able to provide more elegant solutions :)

[hagai-arad]

I totally get your point.
I don't have such a case at the moment. I'll share if I have one :)
Thanks for the support!