ERROR: relation "sfo_prepared" does not exist

Solved!
thondeboer
Level 2
ERROR: relation "sfo_prepared" does not exist

Getting this error in the DSS Tutorial "Python and Dataiku DSS" in the hands on section...

The full exception is:

Exception: None: b'During query \'\n    select      "Aircraft Manufacturer" as manufacturer,\n                sum("Landing Count") as count\n            from sfo_prepared\n            group by "Aircraft Manufacturer"\n            order by count desc limit 5\n\': ERROR: relation "sfo_prepared" does not exist\n  Position: 122, caused by: PSQLException: ERROR: relation "sfo_prepared" does not exist\n  Position: 122'

 And here is my notebook version of it...

Screen Shot 2021-01-17 at 8.10.43 PM.png

Something tells me that the "FROM SFO_PREPARED" is not being "translated" to the actual database name.

Am I missing something?

0 Kudos
1 Solution
fchataigner2
Dataiker

Hi,

the query you pass to `query_to_df()` is executed as is, so in this case the error is the database saying that the sfo_prepared table doesn't exist.

The most likely reason is that the sfo_prepared dataset isn't stored in the sfo_prepared table. If you created the dataset from the recipe creation modal, then DSS has used the table ${projectKey}_sfo_prepared, where ${projectKey} is replaced by the project key (which you can see in the location bar of the browser). For you this would translate to DKU_TUTORIAL_SQLINPYTHON_sfo_prepared

The second possibility is that the database has case-sensitive identifiers, or is uppercase by default, and you need to quote the table name.

View solution in original post

2 Replies
fchataigner2
Dataiker

Hi,

the query you pass to `query_to_df()` is executed as is, so in this case the error is the database saying that the sfo_prepared table doesn't exist.

The most likely reason is that the sfo_prepared dataset isn't stored in the sfo_prepared table. If you created the dataset from the recipe creation modal, then DSS has used the table ${projectKey}_sfo_prepared, where ${projectKey} is replaced by the project key (which you can see in the location bar of the browser). For you this would translate to DKU_TUTORIAL_SQLINPYTHON_sfo_prepared

The second possibility is that the database has case-sensitive identifiers, or is uppercase by default, and you need to quote the table name.

thondeboer
Level 2
Author

Yes, indeed I have a table with that name in my PostgresQL tablespace...

But nowhere in the tutorial is it clear that you have to "figure out" what the table space name is so that the SQL statement works...

Guess I can use this: 

"sfo_prepared.get_location_info()["info"]['table']"

(Needed the quotes, since DSS makes it upper case so is case sensitive)

Thanks for the help though, I figured that the query was exectuted as is, but wanted to make sure that that was the reason...

0 Kudos