Runtime Error PrestoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 1:1: Target table 'hive.table_name' already exists", query_id=20201130_043832_05538_63uev)

[WangSleep]

I have the same problem as this one issues/38 :)

but I'm using dbt-presto

This is the environment I installed:

I tried to make the following changes。

path : dbt/include/presto/macros/materializations/table.sql

then it is ok !

I hope you can give me some advice on whether it's useful or not ~~

thanks a lot !

[jtcohen6]

@WangSleep The underlying issue in dbt-labs/dbt-spark#38, addressed in dbt-labs/dbt-spark#52, was that Spark only has two levels of hierarchy (schema/database + identifier) rather than three (database, schema, identifier). That caused adapter.get_relation() to miss objects in custom schemas and set old_relation as None, because it was still looking in a nonexistent database. Rather than adding an extra adapter.drop_relation(), the real fix was to reimplement get_relation within dbt-spark.

It sounds like you're using Presto with a Hive connector—is that right? What is the config for your model hive.table_name? Does that object have a top-level catalog/database name, or does the namespace have two components only?

[WangSleep]

@WangSleep The underlying issue in fishtown-analytics/dbt-spark#38, addressed in fishtown-analytics/dbt-spark#52, was that Spark only has two levels of hierarchy (schema/database + identifier) rather than three (database, schema, identifier). That caused adapter.get_relation() to miss objects in custom schemas and set old_relation as None, because it was still looking in a nonexistent database. Rather than adding an extra adapter.drop_relation(), the real fix was to reimplement get_relation within dbt-spark.

It sounds like you're using Presto with a Hive connector—is that right? What is the config for your model hive.table_name? Does that object have a top-level catalog/database name, or does the namespace have two components only?

yes !
i am using presto with a hive connetor. and the namespace is "hive"."database_name"."schema_name"

error message:
Error while running: macro rename_relation
Error while running: alter table "hive"."datebase"."temp_user_info_tmp" rename to "hive"."datebase"."temp_user_info"
Target table 'hive.test_user_info.temp_user_info' already exists"

so i changed the file about table.sql

[jtcohen6]

The operative question for me here is: why was old_relation none here? That is, why didn't adapter.get_relation find that a table named 'hive.test_user_info.temp_user_info' already existed here?