topic Re: split_part(col_name,delimiter, index) in Impala Vs. SAS in SAS Programming

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

Yen — Mon, 06 May 2024 17:22:00 GMT

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;

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

Tom — Mon, 06 May 2024 17:31:41 GMT

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.

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

Yen — Mon, 06 May 2024 18:47:26 GMT

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?

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

Reeza — Mon, 06 May 2024 19:23:28 GMT

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?

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

Yen — Mon, 06 May 2024 19:26:58 GMT

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

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

Reeza — Mon, 06 May 2024 19:30:46 GMT

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.

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

Tom — Mon, 06 May 2024 19:37:31 GMT

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.

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

Yen — Mon, 06 May 2024 20:05:31 GMT

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

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

Yen — Tue, 07 May 2024 13:16:37 GMT

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!

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

Yen — Tue, 07 May 2024 13:17:30 GMT

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.