topic Reading Unicode from Snowflake into SAS via ODBC in SAS Programming

Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Thu, 31 Aug 2023 17:46:01 GMT

In a SAS Unicode session, data from Snowflake is coming across as iso-8859-1, even though the SAS data set marks the data set as Unicode.

I connect using

libname mysf odbc
complete="DRIVER={SnowflakeDSIIDriver};SERVER=...";

I can use PROC DATASETS with CORRECTENCODING to change the data set to iso-8559-1, and then compatible characters (like Spanish and German) look okay. However, non-compatible characters (like Japanese) are always mojibaked or missing.

I am on SAS Unicode 32-bit and Windows 10 Enterprise .

NOTE: SAS (r) Proprietary Software 9.4 (TS1M7 MBCS3170)
NOTE: This session is executing on the W32_10PRO platform.

Snowflake 32-bit ODBC driver: 2.25.04.00

I would rather not pay for a Snowflake connector license just for this one feature.

Re: Reading Unicode from Snowflake into SAS via ODBC

LinusH — Fri, 01 Sep 2023 08:50:14 GMT

I'm bit confused since Snowflake claims that they always have the data transferred in UTF-8 using their ODBC driver.

https://community.snowflake.com/s/article/Does-the-Snowflake-ODBC-Driver-support-non-Latin-characters

What is the actual ENCODING setting in your SAS session?

Make sure that you download the data to a clean location to avoid encoding miss-match.

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Fri, 01 Sep 2023 16:21:45 GMT

@LinusH

"proc options option=encoding; run;" tells me my encoding is UTF-8, and when I check the properties of the SAS data set under WORK (created by querying Snowflake), the encoding is UTF-8 too.

What do you mean a clean location? I am creating a v9 SAS data in WORK.

Re: Reading Unicode from Snowflake into SAS via ODBC

Tom — Fri, 01 Sep 2023 20:11:26 GMT

Sounds like the data in the Snowflake database is not UTF-8?

Try a small query with some characters that you know are non-7bit ASCII characters and print the actual hex codes.

data test;
  set mysf.have(encoding=any);
  put (_character_) (=$hex. /);
run;

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Fri, 01 Sep 2023 20:48:16 GMT

I created a Unicode table in Snowflake via DBeaver with this code

create or replace TABLE zzz_unicode ( language_code int, text varchar(100) );
insert into zzz_unicode (language_code, text) values
(1, 'Ich kann Glasssplitter essen, es tut mir nicht weh'),
(2, 'Je peux manger du verre, ça ne me fait pas mal'),
(3, 'Posso mangiare vetro, non mi fa male'),
(4, 'Eu posso comer vidro, não me faz mal'),
(5, 'Puedo comer vidrio, no me hace daño'),
(6, 'Я могу есть битое стекло, оно мне не вредит'),
(7, 'ฉันสามารถกินแก้วแตกได้ มันไม่ทำให้ฉันเจ็บปวด'),
(8, '私は割れたガラスを食べることができます、それは私を傷つけません'),
(9, 'እኔ የተሰነጠቀ ብረት መብላት እችላለሁ፣ አይጎዳኝም'),
(10, 'ငါ ብስጭት መብላት እችላለሁ, ጎጂ አይደለም');
insert into zzz_unicode (language_code, text) values
(11, 'Я могу есть битое стекло, оно мне не вредит'),
(12, '私は割れたガラスを食べることができます、それは私を傷つけません'),
(13, '我可以吃碎玻璃，它不会伤害我');

select *
from zzz_unicode
;

This shows how the table looks in DBeaver (looks good) vs SAS PROC PRINT (looks bad).

Here is the SAS log for the HEX test you suggested. The character 1A is SUB, and 20 is space, so we see the mojibake in HEX too. Also, it doesn't like encoding=any.

348
349 data test;
350 set jet.zzz_unicode(encoding=any);
--------
76
WARNING 76-63: The option ENCODING is not implemented in the ODBC engine.

351 if language_code in (6,13);
352 put (text) (=$hex. /);
353 run;

TEXT=1A201A1A1A1A201A1A1A1A201A1A1A1A1A201A1A1A1A1A1A2C201A1A1A201A1A1A201A1A201A1A1A1A1A1A20202020202020202020202020202020202020202020202020202020202
0202020202020202020202020202020202020202020202020202020
TEXT=1A1A1A1A1A1A1A1A1A1A1A1A1A1A202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202
0202020202020202020202020202020202020202020202020202020

Re: Reading Unicode from Snowflake into SAS via ODBC

Tom — Fri, 01 Sep 2023 21:23:01 GMT

Try tweaking the ODBC definition.

https://communities.sas.com/t5/SAS-Programming/ODBC-to-Postgres-DB-is-translating-UTF-8-data-into-Latin1/td-p/747543

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Fri, 01 Sep 2023 22:50:51 GMT

@Tom

How would I change the ODBC definition for Snowflake? The URL you posted was about PostgreSQL, and in the last comment, the author (not the original poster) wrote that he solved by adding "SET CLIENT_ENCODING TO 'UTF8'" to his connection string, but I see no such option for Snowflake.

@LinusH posted a link to Snowflake documentation that states: "the Snowflake ODBC Driver will always use UTF-8 encoding for any STRING/VARCHAR/VARIANT type column data." This is what I need, and I can't change it. Also, I do not see any encoding related options in Snowflake session parameters or ODBC parameters. This is consistent with the documentation the the "ODBC driver will always use UTF-8" because there is no way to change it.

Because Snowflake always uses UTF-8 leads me to conclude that the problem, whether SAS configuration or SAS bug, is with SAS's ODBC integration.

Re: Reading Unicode from Snowflake into SAS via ODBC

Tom — Fri, 01 Sep 2023 23:18:12 GMT

Did you raise a ticket with SAS support?

I am also surprised to see that you are using 32-bit version of SAS.

Do you have access to 64-bit version of SAS and 64-bit ODBC drivers for snowflake that you can test?

Re: Reading Unicode from Snowflake into SAS via ODBC

Tom — Fri, 01 Sep 2023 23:37:19 GMT

The other work around is to dump the data to a text file and then read the text file using a data step.

Looks like Snowflake has a COPY TO command for generating text files from data.

https://docs.snowflake.com/en/sql-reference/sql/copy-into-location

Steps would be:
1) Generate delimited text file.

2) Make the text file available to SAS. Might involve copying the file from S3 to work directory.

2) Create dataset with same structure as original table (could be as simple as using OBS=0 dataset option) and then read in the text file.

* Step 3 ;
data want;
   if 0 then set snow.mytable (obs=0);
   infile csv dsd truncover ;
   input (_all_) (+0);
run;

For large files you might find that it works much faster than trying to suck data though that tiny ODBC straw.

Re: Reading Unicode from Snowflake into SAS via ODBC

LinusH — Mon, 04 Sep 2023 11:34:39 GMT

Yes, that's is as clean as it gets.
Following @Tom here, SAS Support could be your next step.
Another thing to try is to open this data from another application on your computer, like Excel and compare.

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Thu, 07 Sep 2023 00:19:10 GMT

OK, I opened a ticket with SAS support. Let's see how that goes.

The COPY TO is a creative suggestion. I already do the opposite for bulk loading data into Snowflake, so if I get desperate, I will check into that.

I could also write a Python program to read the data from Snowflake and pass it to SAS. Python can write SAS format via sas7bdat, but I am guessing it doesn't support Unicode. Maybe XLSX or Access would be the easiest way. This doesn't seem fun, either.

Re: Reading Unicode from Snowflake into SAS via ODBC

Quentin — Thu, 07 Sep 2023 13:33:48 GMT

Interesting thread, thanks. Looking forward to hearing what SAS support says. Also just want to say, XLSX is never the answer, and it's usually the problem. 😀

To the big picture, have you tried using snowflake's ODBC driver with some other client (e.g. python, or, ok, even Excel), to see what comes down? If other clients have the same problem, then I think you've got to point back to snowflake support. Just because their docs say they always serve UTF-8 doesn't mean they are.

Re: Reading Unicode from Snowflake into SAS via ODBC

Quentin — Mon, 15 Jul 2024 20:38:25 GMT

Any response from tech Support on this? I'm having the same issue (I think). Running SAS 9.4M7 (64-bit) on Windows.

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Mon, 15 Jul 2024 20:56:56 GMT

I've pursued this with official SAS support, Snowflake support, unofficial channels like this online forum, and our IT department. SAS and Snowflake pointed fingers at each other, and I haven't gotten anywhere.

Based on some tests like the Microsoft ODBC Test utility in the MDAC package, the problem seems to be that the Snowflake driver never returns UTF-8, despite what Snowflake documentation states. This low-level utility is not user friendly, but easier ways to demonstrate that the problem is with the Snowflake ODBC driver are to use other ODBC clients such as Microsoft Excel or Microsoft Access.

The test procedure is simple: just create a table with a little bit of data, and then use a client like Excel to query it.

create or replace TABLE zzz_unicode ( language_code int, text varchar(100) );

insert into zzz_unicode (language_code, text) values

(1, 'Ich kann Glasssplitter essen, es tut mir nicht weh'),

(2, 'Je peux manger du verre, ça ne me fait pas mal'),

(3, 'Posso mangiare vetro, non mi fa male'),

(4, 'Eu posso comer vidro, não me faz mal'),

(5, 'Puedo comer vidrio, no me hace daño'),

(6, 'Я могу есть битое стекло, оно мне не вредит'),

(7, 'ฉันสามารถกินแก้วแตกได้ มันไม่ทำให้ฉันเจ็บปวด'),

(8, '私は割れたガラスを食べることができます、それは私を傷つけません'),

(9, 'እኔ የተሰነጠቀ ብረት መብላት እችላለሁ፣ አይጎዳኝም'),

(10, 'ငါ ብስጭት መብላት እችላለሁ, ጎጂ አይደለም');

insert into zzz_unicode (language_code, text) values

(11, 'Я могу есть битое стекло, оно мне не вредит'),

(12, '私は割れたガラスを食べることができます、それは私を傷つけません'),

(13, '我可以吃碎玻璃，它不会伤害我');

 

select *

from zzz_unicode

;

If you make progress, let me know please.

Re: Reading Unicode from Snowflake into SAS via ODBC

SASKiwi — Mon, 15 Jul 2024 23:30:48 GMT

Is your session encoding UTF-8?

proc options option = encoding;
run;

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Tue, 16 Jul 2024 21:21:44 GMT

Quentin, I did extensive testing on this issue in SAS and other tools. In SAS, I used SAS Unicode (utf-8) and SAS English (wlatin1).

My workaround in SAS Unicode is to run PROC DATASETS like below every time I pull in data from Snowflake, but it only gives me iso-8859-1, which seems to be a limitation of the Snowflake ODBC driver.

proc datasets library=&lib noprint;
modify &ds / correctencoding='iso-8859-1';
quit;

Re: Reading Unicode from Snowflake into SAS via ODBC

Tom — Wed, 17 Jul 2024 19:03:24 GMT

@AndrewZ wrote:

Quentin, I did extensive testing on this issue in SAS and other tools. In SAS, I used SAS Unicode (utf-8) and SAS English (wlatin1).

My workaround in SAS Unicode is to run PROC DATASETS like below every time I pull in data from Snowflake, but it only gives me iso-8859-1, which seems to be a limitation of the Snowflake ODBC driver.

proc datasets library=&lib noprint;
modify &ds / correctencoding='iso-8859-1';
quit;

I am not sure what your second paragraph means.

Did you look at the hexcodes in the dataset? Were they the valid UTF-8 bytes you expected?

That PROC DATASETS code will just change the metadata attribute that indicates the encoding used to create the file. Changing the metadata about the encoding of the text in the dataset will not change what is in the dataset. It just tells future users of the data what to expect to find when they look at the data.

Re: Reading Unicode from Snowflake into SAS via ODBC

AhmedAl_Attar — Wed, 17 Jul 2024 20:13:16 GMT

@AndrewZ

Have you tried this Libname option?

DBCLIENT_MAX_BYTES= LIBNAME Statement Option

One think to keep in mind, Variable length in Snowflake are based on character count, while in SAS, they are based on Byte count!

Therefore what could be stored in Snowflake within a char/varchar (1) may require a SAS variable of length 2+ in order to correctly display the values.

Hope this helps

Re: Reading Unicode from Snowflake into SAS via ODBC

AndrewZ — Wed, 17 Jul 2024 20:32:58 GMT

Did you look at the hexcodes in the dataset? Were they the valid UTF-8 bytes you expected?

You mean use a hex editor on the .sas7bdat file? No. Based on my other tests (like one in the next paragraph), Snowflake was not sending UTF-8.

That PROC DATASETS code will just change the metadata attribute that indicates the encoding used to create the file. Changing the metadata about the encoding of the text in the dataset will not change what is in the dataset. It just tells future users of the data what to expect to find when they look at the data.

Yes, I understand. The PROC DATASETS step fixed encoding for Spanish characters (like á, é, í, ó, ú, ñ), German characters ( like ä, ö, ü), and "smart" quotation marks usually made by Microsoft Office, but not other texts like Korean, so that implies Snowflake ODBC was sending the text as iso-8859-1 instead of utf-8.

If Snowflake ODBC were sending UTF-8, PROC DATASETS would not have this effect in SAS.

Re: Reading Unicode from Snowflake into SAS via ODBC

Quentin — Thu, 18 Jul 2024 01:48:57 GMT

Thanks much for your response. Really appreciate it. Your diagnostic steps sounds good to me. Unfortunately I can't test myself, as I currently have only read access to a table that was recently migrated from SQL Server to Snowflake. In my quick PROC COMPARE I noticed the problems caused by the encoding, even after I ran the query against snowflake in a SAS UTF-8 session. Then google led me to your post. Unfortunately, I have characters in many many multi-byte languages, so sounds like the iso-8859-1 encoding won't fix it all. I'll probably open up a ticket with the internal IT group that owns the snowflake instance, but they'll probably just tell me it's a SAS problem and close the ticket, because they don't know SAS. : ) In the end it's okay, I don't really need the character strings that have issues, was just hoping for an easy way to make the PROC COMPARE return 0 differences. Thanks again.