When you try to Read or Write to a Spark or Databricks view, errors might occur.
When running a SAS session that does not use ECODING=UTF-8, an error similar to the following occurs:
ERROR: Could not transcode table metadata to session encoding.
Note: If you can access the tables that are part of that view directly, then you can access them normally.
If you attempt to access that same view in a SAS session that uses ECODING=UTF-8, the following error might occur:
ERROR: The data type 'string collate UTF8_LCASE_RTRIM' for column 'column-name' is unsupported.
This issue is similar to the one that is documented in SAS KB0060258. However, in this scenario, there might not be any STRING columns.
Some "garbage" characters in the COMMENTs attributes of one or more columns in the view causes this issue.
These characters are either incomplete or are characters that cannot be transcoded to a single-byte encoding. Examples include, but are not limited to the following:
Although these characters can be acceptable in UNICODE, they are not acceptable in a singe-byte encoding like LATIN1.
With the hot fix, the ERROR changes to a WARNING message. However, this solution does not set SYSCC > 0.
SAS kept the WARNING message to ensure that you know something in your VIEW is incorrect.
By using SQL pass-through code, SAS never needs to interact with the problem metadata. So, this workaround avoids the problem entirely
Note: It can be difficult to identify the problem characters. For example, a non-breaking-space (also known as NBSP or "a0"x) might not be displayed at all.
To identify the problem characters, run the following code in the database and send the results for SAS Technical Support for review:
SELECT table_catalog, table_schema, table_name, column_name, comment
,length(column_name) as name_chars, octet_length(column_name) as name_bytes, hex(cast(column_name as binary)) as name_hex_value
,length(comment) as comment_chars, octet_length(comment) as comment_bytes, hex(cast(comment as binary)) as comment_hex_value
FROM system.information_schema.columns
WHERE table_catalog = 'catalog-name'
AND table_schema = 'schema-name'
AND table_name = 'view-name'
AND ( column_name RLIKE '[^\\u0000-\\u00FF]' or comment RLIKE '[^\\u0000-\\u00FF]'
) ;
If the problem characters are not obvious, you might need to remove the last AND expression from WHERE.