0

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!

Beege
  • 665
  • 4
  • 18
  • Just curious, but can you give an example of another system that spool overwrites the file on each command? The purpose is to write the output to a file until you close the spool. I've just never seen an example where a single spool command would write only the last output to the file. Granted, my experience is limited with many tools. – Mike Walton Apr 21 '20 at 19:29
  • Maybe there's a misunderstanding. You said "... the last output to the file." As long as spool is "on", then I expect everything -- multiple statements' results -- to go to the spool-file until I either spool off or exit the session. If repeat my process, I don't want the spool-file to have 2x results. My concern for snowsql is this appending to the spool file. Oracle default spool behavior is to replace the file. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SPOOL.html – Beege Apr 21 '20 at 21:02
  • No 'spool' for MariaDB, but the 'select into outfile' seems very close. – Beege Apr 21 '20 at 21:10
  • 1
    But in your example above, you don't exit your spool. If you exit your spool, and then start a new spool, does it not overwrite the file? – Mike Walton Apr 21 '20 at 21:44
  • Also, I'm curious as to your use-case here. There are certainly much easier ways to create hourly datasets to files in Snowflake. A task, for example, that runs a COPY INTO location. I know you said there were workarounds that you weren't interested in, but COPY INTO location is actually the "right way" to get data out of Snowflake. – Mike Walton Apr 21 '20 at 21:49
  • My examle does exit the entire snowsql session. When I start a new spool, the file is NOT overwritten. This is the specific issue. – Beege Apr 22 '20 at 13:59
  • Does that behavior change if you add `!spool off` to your .sql file? – Mike Walton Apr 22 '20 at 14:29
  • No. It just stops the current session's spooling; just like exit. Subsequent sessions have no knowledge of previous sessions. – Beege Apr 23 '20 at 21:26
  • The use of ! commands inside of snowsql make this seem more confusing. In some environments, using the ! creates a sub-shell where an o/s command can be called. The snowsql command: !spool is NOT running a shell command called spool. It's telling the snowsql interpreter to do something with the output. – Beege Apr 23 '20 at 21:29

1 Answers1

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.