topic SAS, Athena, and Hyphens in SAS Data Management

SAS, Athena, and Hyphens

SASUser_86 — Fri, 25 Sep 2020 20:56:30 GMT

Hi, I am running the following statements and received an error:

LIBNAME athena ODBC DATAsrc=athena schema="aws-catalog-test";
proc sql;
SELECT *
FROM athena.table;

Error:
SQL statement: SELECT * FROM aws-catalog-test.table

My guess is that SAS does not like the hyphens in the name of the schema? Could anyone confirm? Thank you.

Re: SAS, Athena, and Hyphens

SASKiwi — Fri, 25 Sep 2020 22:48:38 GMT

Is that the complete error message? If not please post it.

Re: SAS, Athena, and Hyphens

SASUser_86 — Mon, 28 Sep 2020 16:31:07 GMT

This is the full error message:

ERROR: CLI prepare error: Unable to retrieve error message.
SQL statement: SELECT * FROM aws-catalog-test.table.

Re: SAS, Athena, and Hyphens

SASKiwi — Mon, 28 Sep 2020 20:58:52 GMT

The fact that database error messages can't be retrieved suggests to me that there is a configuration problem in the ODBC interface. I suggest you open a track on this with SAS Tech Support as they are in the best position to diagnose what is happening.

Re: SAS, Athena, and Hyphens

SASUser_86 — Mon, 28 Sep 2020 21:01:45 GMT

Typically, I would think such as well. However, when I change the schema name to one without a hyphen, this works just fine. For example:

LIBNAME athena ODBC DATAsrc=athena schema="aws";
proc sql;
SELECT *
FROM athena.table;

Even underscores are acceptable.

Re: SAS, Athena, and Hyphens

SASKiwi — Mon, 28 Sep 2020 23:54:22 GMT

Try this to see if it provides anything useful.

options sastrace= ',,,ds' sastraceloc=saslog nostsuffix sql_ip_trace=(note,source) msglevel=i;

options mprint  source details;

%put %quote(%sysget(LD_LIBRARY_PATH));

LIBNAME athena ODBC DATAsrc=athena schema="aws" libdebug;

I still suggest you track your hyphen issue to Tech Support.

Re: SAS, Athena, and Hyphens

Tom — Tue, 29 Sep 2020 01:50:42 GMT

Read the discussion on this old thread about similar issue with variable names.

https://communities.sas.com/t5/SAS-Enterprise-Guide/Working-with-DBMS-column-names-that-have-a-quot-quot-in-them-in/td-p/269560

You might try some of the options/settings mentioned there, but I suspect you will end up using the same solution. Use passthru SQL so that your code is written in the remote databases dialect of SQL.

Re: SAS, Athena, and Hyphens

JBailey — Wed, 30 Sep 2020 13:12:12 GMT

Hi @SASUser_86

I think it is Athena (aka Presto) that doesn't like the hyphens. SAS is passing the name to Athena; Athena is returning the error message.

In Athena a schema=database.

The following text comes from this page: https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html (I have highlighted the relevant section.

Database, Table, and Column Names

When you create schema in AWS Glue to query in Athena, consider the following:

A database name cannot be longer than 252 characters.
A table name cannot be longer than 255 characters.
A column name cannot be longer than 128 characters.
The only acceptable characters for database names, table names, and column names are lowercase letters, numbers, and the underscore character.

You can use the AWS Glue Catalog Manager to rename columns, but at this time table names and database names cannot be changed using the AWS Glue console. To correct database names, you need to create a new database and copy tables to it (in other words, copy the metadata to a new entity). You can follow a similar process for tables. You can use the AWS Glue SDK or AWS CLI to do this.

Best wishes,

Jeff

Re: SAS, Athena, and Hyphens

SASUser_86 — Wed, 30 Sep 2020 17:52:10 GMT

@JBailey You are correct. It does appear that the limitation is within Athena. Oddly enough, the majority of our current databases/schema in Athena have hyphens in the name. So, we assumed that since it was allowed in Athena, it was a SAS issue. We landed on the similar doc you linked related to naming.

I did try using a pass through query with the hyphens in the schema/database and this works -- in case others encounter this issue and you cannot modify the schema name.