Snowflake automatically rounding number during COPY INTO transformation

user_10881188 0
Viewed 418

I am using an AWS S3 stage to load .csv data into my Snowflake database.

The .csv columns are as follows:

enter image description here

My COPY INTO command is this:

copy into MY_TABLE(tot_completions, tot_hov, parent_id) 
from (select t.$1, to_decimal(REPLACE(t.$2, ',')), 1 from @my_stage t) 
pattern='.*file_path.*' file_format = my_file_format ON_ERROR=CONTINUE;

The Tot. HOV column is being automatically rounded to 40 and 1 respectively. The data type is decimal, and I tried it as a float as well, even though they should both be able to store decimals.

My desired result is to store the decimal as is displayed on the .csv without rounding. Any help would be greatly appreciated.

snowflake-schema snowflake-cloud-data-platform sql database
1 Answers

You need to specify the precision and scale:

create or replace table number_conv(expr varchar);
insert into number_conv values ('12.3456'), ('98.76546');

select expr, to_number(expr),  to_number(expr, 10, 1), to_number(expr, 10, 8) from number_conv;

+----------+-----------------+------------------------+------------------------+
| EXPR     | TO_NUMBER(EXPR) | TO_NUMBER(EXPR, 10, 1) | TO_NUMBER(EXPR, 10, 8) |
|----------+-----------------+------------------------+------------------------|
| 12.3456  |              12 |                   12.3 |            12.34560000 |
| 98.76546 |              99 |                   98.8 |            98.76546000 |
+----------+-----------------+------------------------+------------------------+

and:

select column1,
       to_decimal(column1, '99.9') as d0,
       to_decimal(column1, '99.9', 9, 5) as d5,
       to_decimal(column1, 'TM9', 9, 5) as td5
from values ('1.0'), ('-12.3'), ('0.0'), ('  - 0.1   ');

+---------+-----+-----------+-----------+
| COLUMN1 |  D0 |        D5 |       TD5 |
|---------+-----+-----------+-----------|
| 1.0     |   1 |   1.00000 |   1.00000 |
| -12.3   | -12 | -12.30000 | -12.30000 |
| 0.0     |   0 |   0.00000 |   0.00000 |
| - 0.1   |   0 |  -0.10000 |  -0.10000 |
+---------+-----+-----------+-----------+

See more here

user_77985480
Related