Can I use snowflake snowsql !spool command to overwrite existing file?

Question

When I use the !spool command, the target file is appended with the results. Example:

$ touch current.spool
$ cat curr_ts.sql
!spool current.spool
select CURRENT_TIMESTAMP;

$ snowsql -f  curr_ts.sql  <--- 1st execution of the script
* SnowSQL * v1.1.86
Type SQL statements or !help
+-------------------------------+
| CURRENT_TIMESTAMP             |
|-------------------------------|
| 2020-04-21 13:35:59.983 -0400 |
+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.096s
Goodbye!

$ cat current.spool
+-------------------------------+
| CURRENT_TIMESTAMP             |
|-------------------------------|
| 2020-04-21 13:35:59.983 -0400 |
+-------------------------------+

$ snowsql -f  curr_ts.sql  <--- 2nd (supposedly independent) execution of the script
* SnowSQL * v1.1.86
Type SQL statements or !help
+-------------------------------+
| CURRENT_TIMESTAMP             |
|-------------------------------|
| 2020-04-21 13:36:17.629 -0400 |
+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.098s
Goodbye!
[1019] bjs13b@igloo:/home/bjs13b/snowsql $ cat current.spool
+-------------------------------+
| CURRENT_TIMESTAMP             |
|-------------------------------|
| 2020-04-21 13:35:59.983 -0400 |
+-------------------------------+
+-------------------------------+
| CURRENT_TIMESTAMP             |   <--- file NOT replaced by 2nd execution!
|-------------------------------|
| 2020-04-21 13:36:17.629 -0400 |
+-------------------------------+

[edit to hopefully clarify the problem]

If each execution is supposed to create an hourly file, then each execution ADDS DATA to the hourly file ... so much for an hourly dataset.

I know plenty of workarounds, but this is a specific question about spool. I'd prefer option that changes the behavior of the spool command. In other systems, I'm used to the file being overwritten and this keeps biting me!

Answer 1

A dtrace/strace confirms that the spool feature flag always opens the file in an O_APPEND mode, leading to the behaviour you're observing. There's no documented override to this behaviour as of this post's date.

~> cat test.sql
!spool filename.txt
SELECT 1;

~> dtruss snowsql -f test.sql
…
open("filename.txt\0", 0x1000209, 0x1B6)
                       ^^^^^^^^^
         (Flags include O_CREAT and O_APPEND)
…

~> dtruss snowsql -f test.sql
…
open("filename.txt\0", 0x1000209, 0x1B6)
…

SnowSQL, being a custom client to Snowflake DB, does not follow a specific, standard behaviour (such as some of Snowflake DB's other connectors do - JDBC, ODBC, SQL Alchemy (Python), etc.). In relation to the comment comparing it with Oracle's SQL*Plus features however, it makes sense to request a similar feature. If you have a support account with Snowflake, I'd recommend raising a feature request.