topic Re: Loading huge data into snowflake table in SAS Data Management

Loading huge data into snowflake table

samanvi — Fri, 15 Sep 2023 01:06:39 GMT

HI Every one, Need your help. We are pulling data from sqlserver and loading data into snowflake table. We are using proc append with force option but during the process we are getting CLI error and the job is getting failed. Here we are dealing with 60Mill records. I used bulk option too throwing some error on amount column seeing character data "ABC".

Re: Loading huge data into snowflake table

Patrick — Fri, 15 Sep 2023 01:22:47 GMT

Sounds like a data type mismatch but you will have to provide much more information for us to be of any help.

If this is just a replication of SQL server tables to Snowflake without any transformations then also consider if you could run a process that doesn't pass the data through SAS but directly loads from SQL Server to Snowflake. You could still use SAS to trigger the process. Such a direct load should perform better but you would of course need all the direct connectivity between SQL Server and Snowflake established.

4 Methods to Transfer Data from SQL Server to Snowflake

Re: Loading huge data into snowflake table

samanvi — Fri, 15 Sep 2023 01:29:42 GMT

forgot to add code

{code}

libname abc sql server uid-xxx pwd=xxx;

libname def sasiosnf insertbuffer=5000 server ='' uid='' pwd'';

proc sql;

create table have

select monotonic() as key,

from abc.test;

quit;

proc append base=def.want data=have force;

run;

Re: Loading huge data into snowflake table

samanvi — Fri, 15 Sep 2023 01:34:37 GMT

Thank for your response. There are other process even running in the code where we pull data from multiple files do data cleansing and loading them to the snowflake tables. Those are not causing any issue but this process we are creating a key column to the orginal data and adding data to the snowflake table .

Re: Loading huge data into snowflake table

SASKiwi — Fri, 15 Sep 2023 01:42:00 GMT

Please post the full SAS log so we can see notes and errors. I don't see any use of bulk loading.

Re: Loading huge data into snowflake table

samanvi — Fri, 15 Sep 2023 02:16:50 GMT

error:

Re: Loading huge data into snowflake table

SASKiwi — Fri, 15 Sep 2023 02:32:47 GMT

We need to see both the source code and the notes and errors together so we can see which statements are causing them.

Re: Loading huge data into snowflake table

Patrick — Fri, 15 Sep 2023 03:41:12 GMT

Do NOT use monotonic()! This is an undocumented and unsupported function and it will not necessarily return the expected result especially when used with a database table as source.

Re: Loading huge data into snowflake table

samanvi — Fri, 15 Sep 2023 11:51:40 GMT

Here contains log details. SAS is reading 80k records but why it's considering as nulls even though data is present?

Re: Loading huge data into snowflake table

SASKiwi — Fri, 15 Sep 2023 23:53:08 GMT

Does your program work with a small amount of data? Try just loading say 1000 rows. If that works try scaling up to 10K, 50K, 100K.

Also please don't screenshot your SAS logs, Just do a normal copy and paste using the </> menu option.

Re: Loading huge data into snowflake table

LinusH — Mon, 18 Sep 2023 12:09:38 GMT

For me, best practice is not to use FORCE.

You should have control of your ETL process, and explicitly tell what data should go where. Don't allow any warnings in your log for production pipelines.

Second, instead of monotonic(), consider using Snowflakes AUTOINCREMENT instead.

https://docs.snowflake.com/en/sql-reference/sql/create-table#syntax

You still haven't share the compete log with your program (including libname and option).

Re: Loading huge data into snowflake table

Quentin — Mon, 18 Sep 2023 14:22:03 GMT

Just curious, are you using SAS/ACCESS to Snowflake or perhap's using Snowflake's ODBC driver?

Agree with earlier suggestion to try pushing just 5 records (or even 1 record) to snowflake. Are you able to reliably query data from snowflake into SAS?

What happens if you try to create a new table in Snowflake, instead of append to an existing table, e.g.

proc append base=abc.NewSnowflakeTable data=AFFIL_1 (obs=5);
run;

The ? in the log don't mean the values are null, so I wouldn't worry about them.

Googling the error message for 400 errors from snowflake turns up plenty of hits, e.g.

https://community.snowflake.com/s/article/Solution-400-Bad-Request-Login-Errors-using-SAML-SSO-Federation-with-Snowflake

https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/SnowflakeSQLException-JDBC-Driver-encountered-communication/ta-p/586828

Re: Loading huge data into snowflake table

LinusH — Tue, 19 Sep 2023 07:05:04 GMT

Another source of information could be the log in Snowsight.

What do you see there?

Re: Loading huge data into snowflake table

samanvi — Wed, 20 Sep 2023 15:11:27 GMT

Thank you for all your help. We analyzed almost 59mill records and found some specials char which is coming at end of data and we are trying to insert into one of the column due to this getting error. we suppressed that and data is loading into the table.

But loading of this 59Mill is taking a lot of time. for every 1 Mill it's taking 20 min. We have 55 columns only.

Re: Loading huge data into snowflake table

Tom — Wed, 20 Sep 2023 15:16:29 GMT

Sounds like you have some truncation going on. Perhaps you are transcoding the data from single byte encoding to multi-byte encoding so the length of the target variables is too short and some mult-byte characters are being truncation causing them to be invalid.

for improved transfer use bulk loading. If your driver does not support it then roll your own by exporting to a delimited file and then using the Snowflake command (COPY FROM?) to bulk load the file.

Re: Loading huge data into snowflake table

samanvi — Wed, 20 Sep 2023 15:28:58 GMT

Tried to use bulk load in libname but we got problem if I have 2 columns with 1st is numeric which has nulls/nodata and 2nd with data(char). the second column is moving it's position to left. In this case the character column is moving to numeric side and getting failed with error data type mismatch.

Re: Loading huge data into snowflake table

Tom — Wed, 20 Sep 2023 15:31:26 GMT

@samanvi wrote:

Tried to use bulk load in libname but we got problem if I have 2 columns with 1st is numeric which has nulls/nodata and 2nd with data(char). the second column is moving it's position to left. In this case the character column is moving to numeric side and getting failed with error data type mismatch.

Using bulkload options from SAS? Raise a support ticket with SAS.

Writing your own code? Sounds like somewhere you forgot to tell SNOWFLAKE to use the equivalent of the DSD option of the FILE or INFILE statement in SAS.

Re: Loading huge data into snowflake table

samanvi — Wed, 20 Sep 2023 16:59:56 GMT

We are just remediating the existing codes. Our source is SQL server and creating a physical key and loading them to the snowflake table. SAS ia acting as bridge to load data.

Re: Loading huge data into snowflake table

Patrick — Thu, 21 Sep 2023 00:15:09 GMT

@samanvi wrote:

...

But loading of this 59Mill is taking a lot of time. for every 1 Mill it's taking 20 min. We have 55 columns only.

Look into libname options readbuff, insertbuff, and dbcommit

Re: Loading huge data into snowflake table

samrat1507 — Mon, 09 Sep 2024 08:53:05 GMT

Hi there! It sounds like you're dealing with a significant volume of data. For large-scale data migrations like pulling data from SQL Server into Snowflake, using the appropriate approach can make a big difference. You might want to explore a step-by-step guide on how to load data from SQL Server to Snowflake, which also highlights best practices to avoid common errors. Here's a comprehensive guide: SQL Server to Snowflake. It may help you resolve the CLI and data type issues you're encountering.