SNOW-733213: Add ability for write_pandas to truncate table instead of drop table

[sfc-gh-cconner]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   3.9.0

2. What operating system and processor architecture are you using?

macOS-13.1-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)?

snowflake-connector-python==2.9.0

4. What did you do?

Using write_pandas with overwrite=true, the table on Snowflake side is dropped and then recreated. This was decided in this git issue:

#1175

The decision was made in order to maintain the dataframe schema. However, drop tables loses grants on the table.

5. What did you expect to see?

We would like to have an option that truncates the table instead of dropping the table in order to maintain grants.

[sfc-gh-sfan]

Thanks for reporting! This is actually a good onboarding issue :)

[LeonMar97]

Hi, was that issue already resolved? seems like there is just discrepancy with the documentation regarding when the table truncates.

[sfc-gh-sfan]

This should have been resolved per this docstring:

snowflake-connector-python/src/snowflake/connector/pandas_tools.py

Lines 230 to 232 in 783732d

	overwrite: When true, and if auto_create_table is true, then it drops the table. Otherwise, it
	truncates the table. In both cases it will replace the existing contents of the table with that of the passed in
	Pandas DataFrame.

[ttzhou]

Reading the code though, it appears to be doing "drop and swap/rename", not truncating. I don't see any "TRUNCATE" string anywhere else in that file...

This is on 3.1.1

[andreqaugusto]

per my personal experience and as @ttzhou pointed out, the code does not behave as the docstring says it so... I submitted a (probable) fix to this issue that makes the code behave like the docstring says it would be.

I thought about only fixing the docstring, but I think that we should have the option of truncating the table instead of just dropping it (because we lose any comments or defaults or conditions of the original table).