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 or USE sample.demo;, use the query SELECT * 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 as from_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).