ClickHouse DB::Exception: INTO OUTFILE not allowed

Q: Where does INTO OUTFILE write the file -- on the server or the client?

INTO OUTFILE writes the file on the client machine (where clickhouse-client is running), not on the server. This is different from the file() table function, which writes to the server filesystem.

Q: Can I compress the output with INTO OUTFILE?

Yes. From a supported client you can add a COMPRESSION 'gzip' clause (other types include 'lz4' , 'zstd' , 'br' , 'xz' ). The COMPRESSION clause must come before FORMAT . For example: SELECT * FROM my_table INTO OUTFILE 'data.csv.gz' COMPRESSION 'gzip' FORMAT CSV .

The "DB::Exception: INTO OUTFILE is not allowed" error in ClickHouse occurs when a query uses the INTO OUTFILE clause in a context where it is not permitted. The error code is INTO_OUTFILE_NOT_ALLOWED. INTO OUTFILE is a client-side feature that writes query results to a file on the client machine, so it is only available in the clickhouse-client command-line client and in clickhouse-local. A query that uses INTO OUTFILE over the HTTP interface (or any other interface that does not enable this feature) is rejected with this error.

Impact

The query fails and no output file is created. The query results are not lost -- the query itself is valid but the output method is blocked. You will need to use an alternative method to export the data.

Common Causes

Query sent over the HTTP interface -- The most common cause. INTO OUTFILE is not supported over HTTP because the file would have to be written on the client side, which the HTTP protocol does not provide. This is the typical trigger for the error.
Query run from a driver or BI tool -- Most native-protocol drivers and BI tools do not enable client-side file output, so an INTO OUTFILE clause submitted through them is rejected.
Query embedded in a view, subquery, or another statement -- INTO OUTFILE is only valid as a clause of a top-level SELECT executed by a client that supports it, not inside other query contexts.
ClickHouse Cloud or managed service -- When you connect to a managed service through the HTTP endpoint or a web SQL console, INTO OUTFILE is generally not available; use the supported export mechanisms instead.

Troubleshooting and Resolution Steps

Run the query from the command-line client. INTO OUTFILE works in clickhouse-client and clickhouse-local:
```
clickhouse-client --query "SELECT * FROM your_table INTO OUTFILE 'output.csv' FORMAT CSV"
```
If you are using the HTTP interface, drop the INTO OUTFILE clause and redirect the response instead:
```
curl "http://localhost:8123/?query=SELECT+*+FROM+your_table+FORMAT+CSV" > output.csv
```

From clickhouse-client, you can also redirect output without INTO OUTFILE:

clickhouse-client --query "SELECT * FROM your_table FORMAT CSV" > output.csv

Use the file() table function if you need server-side file output (writes on the server, requires appropriate permissions):
```
INSERT INTO FUNCTION file('output.csv', 'CSV')
SELECT * FROM your_table;
```
For managed services or BI tools, use the platform's supported export mechanism (HTTP download, native client, or object-storage export) rather than INTO OUTFILE.

Best Practices

Use clickhouse-client output redirection or the HTTP interface for exporting data, as these approaches work from any client without relying on INTO OUTFILE.
Reserve INTO OUTFILE for interactive use in clickhouse-client or clickhouse-local; for automated pipelines prefer HTTP downloads, the file()/s3() table functions, or clickhouse-client redirection.
For large data exports, consider using clickhouse-client with appropriate format and compression settings for better performance.
Document your organization's data export policies and approved methods for exporting query results.

Frequently Asked Questions

Q: Where does INTO OUTFILE write the file -- on the server or the client?
A: INTO OUTFILE writes the file on the client machine (where clickhouse-client is running), not on the server. This is different from the file() table function, which writes to the server filesystem.

Q: Is INTO OUTFILE available in ClickHouse Cloud?
A: It depends on the specific managed service. Some ClickHouse Cloud providers disable INTO OUTFILE because the server filesystem is not user-accessible. Use the HTTP interface or clickhouse-client output redirection instead.

Q: Can I compress the output with INTO OUTFILE?
A: Yes. From a supported client you can add a COMPRESSION 'gzip' clause (other types include 'lz4', 'zstd', 'br', 'xz'). The COMPRESSION clause must come before FORMAT. For example: SELECT * FROM my_table INTO OUTFILE 'data.csv.gz' COMPRESSION 'gzip' FORMAT CSV.