ERROR: relation "sfo_prepared" does not exist

Options
thondeboer
thondeboer Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 8 ✭✭✭✭
edited July 16 in General Discussion

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?

Best Answer

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    Answer ✓
    Options

    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.

Answers

  • thondeboer
    thondeboer Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 8 ✭✭✭✭
    Options

    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...

Setup Info
    Tags
      Help me…