Loading huge data into snowflake table

[Tom]

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.

[samanvi]

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.

[Tom]

---

@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.

[samanvi]

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.

[Patrick]

If your SQL server can directly connect toh Snowflake then I'd be using SAS only as "remote control" meaning you'd only send explicit SQL passthrough syntax to SQL server and/or Snowflake to trigger a process that exchanges the data directly between these two DB's without piping it through an intermediary SAS server. 

If you really must transfer the data via the intermediary SAS Server then - depending on load mechanism - I'd investigate if there is a way for bulkunload / load of tables without reading the data into SAS tables.

[Patrick]

---

@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

[samrat1507]

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.

[Patrick]

@samanvi 

If you pipe the data through SAS 9.4 as intermediary then look into SAS Access for .... options like readbuff, insertbuff, dbcommit etc. Changing the default values can significantly increase performance. 

If these are full unloads/loads then also investigate the options related to bulk unload and load.

All the options are documented under the SAS/Access engine you are using. 

Also: If you are creating intermediary SAS tables then also check in what length character variables get created. In the case you know that your DB only stores single byte characters but the encoding is set to something like UTF-8 then on the SAS side each character will get stored with 4 bytes. This not only will impact on performance but it can also lead to string truncation if you've got very long character variables in source. There are also options to influence this behaviour. At a minimum set option compress=yes to ensure that the intermediary SAS table gets stored compressed. 

In case you're using SAS Viya there might be additional options to speed up the process if you explain a bit more in detail how your architecture looks like.