ClickHouse DB::Exception: Invalid usage of input

The "DB::Exception: Invalid usage of input" error in ClickHouse occurs when the input() table function is used in a context other than an INSERT ... SELECT statement. The error code is INVALID_USAGE_OF_INPUT. The input() function is specifically designed to transform input data during INSERT operations and is not valid in standalone SELECT queries or other contexts.

Impact

The query fails immediately. No data is inserted or read. This is a query syntax issue that requires restructuring the query to use input() correctly within an INSERT ... SELECT statement.

Common Causes

1. Using input() in a standalone SELECT -- Running SELECT * FROM input(...) without an INSERT target.
2. Using input() in a subquery outside INSERT context -- Embedding input() inside a CTE, JOIN, or nested SELECT that is not directly part of an INSERT ... SELECT.
3. Copy-paste error -- Copying just the SELECT portion of an INSERT ... SELECT query and running it independently.
4. Confusion with other table functions -- Mistaking input() for file(), url(), or s3(), which can be used in standalone SELECT queries.
5. Using input() in CREATE TABLE AS SELECT -- The input() function is not valid in this context either.

Troubleshooting and Resolution Steps

1. Ensure input() is inside an INSERT ... SELECT statement. The correct pattern is:

   INSERT INTO target_table
   SELECT transform_expression
   FROM input('col1 UInt64, col2 String')
   FORMAT CSV
   

   Then pipe or send the data as the request body.

2. If you want to preview the transformation without inserting, use file() or other table functions instead:

   -- Instead of input(), use file() for testing:
   SELECT col1 * 2, upper(col2)
   FROM file('data.csv', 'CSV', 'col1 UInt64, col2 String')
   LIMIT 10;
   

3. For HTTP interface, send data in the request body with the INSERT query:

   cat data.csv | curl 'http://localhost:8123/?query=INSERT+INTO+target_table+SELECT+col1*2,upper(col2)+FROM+input(%27col1+UInt64,+col2+String%27)+FORMAT+CSV' --data-binary @-
   

4. For clickhouse-client, pipe data to stdin:

   cat data.csv | clickhouse-client --query="INSERT INTO target_table SELECT col1*2, upper(col2) FROM input('col1 UInt64, col2 String') FORMAT CSV"
   

5. If you need to use input() in a complex transformation, keep it within the INSERT ... SELECT scope:

   INSERT INTO target_table
   SELECT
       a.col1,
       b.lookup_value
   FROM input('col1 UInt64, key String') AS a
   INNER JOIN lookup_table AS b ON a.key = b.key
   FORMAT JSONEachRow
   

Best Practices

• Only use input() within INSERT ... SELECT statements. For all other data reading needs, use file(), url(), s3(), or other appropriate table functions.
• When developing INSERT pipelines, test your transformations first using file() with sample data, then convert to input() for the production INSERT.
• Document the expected input format alongside the INSERT query so consumers know what data format to send.
• Use input() when you need to apply transformations, type conversions, or JOINs to incoming data before it is stored.

Frequently Asked Questions

Q: What is the difference between input() and file()?
A: input() reads data from the query's input stream (stdin or HTTP request body) and can only be used inside INSERT ... SELECT. file() reads from a file on the server filesystem and can be used in any query context. Use input() when data is being sent from a client and you want to transform it on the fly during insertion.

Q: Can I use input() with clickhouse-client?
A: Yes. Pipe data to clickhouse-client via stdin, and use input() in the INSERT ... SELECT query. The client sends the piped data as the input stream.

Q: Can I use input() to read data from multiple formats?
A: Each input() call reads from a single input stream in a single format (specified by the FORMAT clause). If you need to combine data from multiple formats, use separate INSERT operations or use file()/url() table functions instead.