Knowledge Drop
Last tested: Feb 2021
This error message can appear when a table no longer exists but still shows in the list of tables for this schema.
Error Message
Error Running SQL ERROR: relation "schema.table" does not exist
Troubleshooting
Go to the gear option next to the connection name and hit "Refresh Tables and Schema" if the table has been renamed/deleted then it will no longer show up under the reference name that is causing the error.
This can also happen if the table is defined with mixed-case spelling and you try to query it without double-quotes. See this stackoverflow post
In other words, if hotelList was created with mixed-case spelling, this won't work:
play_arrow
SELECT *
FROM hotellist
LIMIT 10
Because the tablename is converted to lowercase when the query is run, this also won't work:
play_arrow
SELECT *
FROM hotelList
LIMIT 10
To avoid this issue, use double-quotes to use the specific mixed-case spelling as the table is defined. This will work:
play_arrow
SELECT *
FROM "hotelList"
LIMIT 10
It is also important to check for fields that reference a field from a joined view WITHOUT ${} syntax, in this case the error surfaces because Looker does not know where to look for the referenced field.
For Snowflake, specifically, ensure that the table names are all UPPERCASE in the database. As per this doc, unquoted values are defaulted to uppercase in Snowflake. That's why we'll usually see the error:
SQL compilation error: Object 'DATABASE.SCHEMA.TABLE_NAME' does not exist when we run
SELECT Procfile README.md all_cards.json body_after.txt body_before.txt body_during.txt ids.txt jq main.sh tmp FROM schema.table_name
If we run:
SELECT Procfile README.md all_cards.json body_after.txt body_before.txt body_during.txt ids.txt jq main.sh tmp FROM "schema"."table_name"
we should see the query run successfully because the lower case will be preserved.
LinkedIn
Twitter