Clickhouse: clickhouse-client interactive mode save to file

[bseng]

Hi,

Can we get an option to save results from interactive mode to a file?

e.g. "SELECT * FROM temp_table FORMAT CSV TOFILE '~/results.csv'"

[alexey-milovidov]

Probably
SELECT * FROM temp_table FORMAT CSV INTO OUTFILE '~/results.csv'
(for those who familiar with MySQL)

It will be client only option; client will send query to server without this modifier, receive result and write to outfile. If server will receive query with INTO OUTFILE, that was sent directly, it will understand syntax, but throw an exception. (Also possible to allow server to write data to local filesystem, but need special respect to some security considerations.)

[bseng]

Client only option will be great! Thanks

[alexey-milovidov]

Maybe we need to place INTO OUTFILE before FORMAT.
SELECT * FROM temp_table INTO OUTFILE '~/results.csv' FORMAT CSV

Because maybe we later would like to add support for something like FROM FILE for INSERT query, and in INSERT query, everything after FORMAT section is parsed as data for insertion by separate parser, so in INSERTs, FORMAT must be last.

[bseng]

I don't think INTO OUTFILE before FORMAT is necessary as INTO is already a keyword in INSERT so you can't use it twice, moreover I can't think of a scenario where you insert data to a database and also output that to a file.

[alexey-milovidov]

I mean different thing.
If we have an option to run clickhouse-client interactively, and then save query result to file,
than, for symmetry,
we also would like to have an option to run clickhouse-client interactively, and then use a file to read as source of data for INSERT query.

[bseng]

okay then the INTO OUTFILE before FORMAT will work as well.

[ztlpn]

in stable now