Troubleshooting SQL #
This section provides suggestions for common scenarios when writing in SQL queries and troubleshooting syntax and access issues.
Cluster, catalog, and schema context and access #
Numerous problems are caused when a SQL query is run in the wrong context, or with the wrong access rights. The following lists common errors and different approaches to attempt to solve your specific issue.
Errors #
- Catalog must be specified when session catalog is not set.
- Schema must be specified when session schema is not set.
- Access Denied: Cannot access catalog
.
Solutions #
In order to run a query all objects in the query must be accessible. The following steps can help you to make sure that the context of cluster, catalog, schema is correct and that you have access to the references objects such as tables and views.
- Access the correct cluster that contains the relevant catalogs. This is achieved with the location drop-downs in the query editor. For connections with other client tools, ensure you use the correct cluster URL.
- Set the correct catalog and schema context with the
USE catalog.schema;
statement - see USE documentation. In the query editor, you can use the location drop-downs. - Without specific catalog and schema
context, or to override
the context, use fully qualified entities in the query. For example, instead
of the query
SELECT * FROM astronauts;
after setting the context with the user interface orUSE sample.demo;
, use the querySELECT * FROM sample.demo.astronauts;
- Ensure the catalog exists in the list of catalogs.
- Ensure the catalog is spelled correctly.
- Ensure the current user has access to the catalog.
- Ensure the catalog is assigned to the cluster being used to query the catalog.
Invalid names and resolving problems #
Errors #
- Column
cannot be resolved. - Table
cannot be resolved.
Solutions #
- Quote the column and table names. For example,
SELECT "First Name" AS firstname FROM users
. - Check the column and table names for typos and special characters, such as spaces.
Data type problems #
The data types determine what functions you can use in your queries.
Errors #
- Cannot apply operator:
date < varchar(NN)
.
Solutions #
- There is a data type mismatch in the compared columns and values. When
specifying the date as a string such as
2022-01-01
, use a date function such asfrom_iso8601_date('2020-05-11');
.
Object storage file problems #
Errors #
- Opening Hive split gs://filename (offset=0, length=13977): Malformed ORC file. Invalid postscript.
Solutions #
- Ensure the file format matches the expected format (ORC).
Is the information on this page helpful?
Yes
No