split_part(col_name,delimiter, index) in Impala Vs. SAS

[Yen]

The function split_part(col_name,delimiter, index) worked well with select statement in the HUE editor when connected to impala.

The same function throws an error when running in SAS EG. Below is the log.

I found a workaround and delivered the file. However, I want to know if anyone here knows a way to submit the code via SAS without being losing the ability to use Impala functions that SAS Access doesn’t support. 

Log:

66         proc sql noprint;

67             connect to impala as XXX (dsn=XXX user=XXXpw = "XXX");

68             create table temp(compress=yes) as

69                    select *

70                           from connection to XXX (

71                                  select

72                                         split_part(col, '~', 1) as col1

73                                  from table1 a , table2 c , table3 b

74                                          where condition);

NOTE: Compression was disabled for data set XXX because compression overhead would increase the size of the data set.

ERROR: CLI cursor extended fetch error: [Cloudera][Support] (40550) Invalid character value for cast specification. :

       [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character

       value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. :

       [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid charact

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

80             disconnect from  XXX;

NOTE: Statement not executed due to NOEXEC option.

81         quit;

[Tom]

What makes you think the error message has anything to do with that function call?

Looks like it is just complaining that that something or other could not be cast from one type to another.

[Yen]

I had similar experiences with other functions. So, from your experience or understanding it has nothing to do with split_part function. If so, can you guide me how to troubleshoot it?

[Reeza]

The type of SQL you're using here is commonly called pass thru SQL, which means the commands are literally passed on to the server. So anything that runs on the server should run here. Have you tested that these functions/SQL works in Impala?

[Yen]

Yes (the SQL select statement), worked in the HUE editor.

[Reeza]

I would open a track with SAS Tech support to debug the conversion issue then to understand why certain fields cannot be mapped back to SAS.

Can you test this but selecting just the column and not using the functions?

If that does work, you could use the SCAN() function on the SAS side which is the equivalent of the split_part function. It's a workaround, which isn't ideal, but may help you debug the issue.

[Yen]

• simple select statement on the column worked. 
• Scan failed.
• The workaround that I used worked "SUBSTR(col_name, 1, instr(col_name, '~')-1)"

I will work with tech team and see if I can get some guidance.

I appreciate your help and time!

[Tom]

Try just taking the value of COL without passing it through the function?  Does that work?

Try passing a CONSTANT thru the function instead of the value of COL? Does that work?

Also try only pulling SOME of the values to see if it is some specific value that is causing the error.

And another trivial thing is check and make sure that the hyphen in that character string is not some other strange non-ASCII character like an en-dash or em-dash that has someone gotten into your SAS code and is confusing the remote database.

[Yen]

I am working on all the ideas suggested and will let you know my findings. I appreciate you Tom and Reeza for your thoughts.

[Yen]

Try just taking the value of COL without passing it through the function?  Does that work? Yes

Try passing a CONSTANT thru the function instead of the value of COL? Does that work? It failed with the same error message.