ClickHouse DB::Exception: Cannot read Array from text

The "DB::Exception: Cannot read Array from text" error in ClickHouse occurs when the server fails to parse an Array value from text input. The CANNOT_READ_ARRAY_FROM_TEXT error is raised during data insertion or when reading from text-based formats (like CSV, TSV, or JSONEachRow) when the text representation of an array does not match the expected syntax for the target Array column type.

Impact

The INSERT operation or query reading from the text source will fail at the row containing the malformed array. Depending on the input_format_allow_errors_num and input_format_allow_errors_ratio settings, ClickHouse may skip the bad row or abort the entire operation. Rows that were successfully parsed before the error may or may not be committed depending on the table engine and insert behavior.

Common Causes

1. Array values not enclosed in square brackets (e.g., 1,2,3 instead of [1,2,3])
2. Incorrect quoting or escaping within the array elements
3. Mismatched element types (e.g., providing strings for an Array(UInt32) column)
4. Empty or malformed array literal (e.g., [,], [1,,2], or just [] when the format expects a different empty representation)
5. Using JSON array syntax in a non-JSON format or vice versa
6. Extra whitespace or special characters within array literals that the parser does not expect

Troubleshooting and Resolution Steps

1. Check the expected array syntax for your input format. In TabSeparated/CSV formats, arrays use this syntax:

   [1,2,3]
   ['hello','world']
   

   In JSONEachRow format:

   {"arr": [1, 2, 3]}
   

2. Identify the problematic row by enabling error logging:

   SET input_format_allow_errors_num = 10;
   SET input_format_allow_errors_ratio = 0.1;
   

   This lets ClickHouse skip some bad rows while logging which ones failed.

3. Verify the array element types match the column definition:

   -- If the column is Array(UInt32), elements must be valid integers
   -- Correct: [1,2,3]
   -- Incorrect: ['a','b','c']
   

4. Check for quoting issues in CSV or TSV input. Array values in CSV may need to be quoted as a whole:

   "1","[1,2,3]","hello"
   

5. Inspect the raw input data to identify malformed array values:

   # Look at the problematic lines around where the error occurs
   head -n 100 your_data_file.csv
   

6. Test with a minimal example to verify the correct format:

   INSERT INTO your_table FORMAT JSONEachRow
   {"id": 1, "tags": ["a", "b", "c"]}
   

7. Use the input_format_csv_arrays_as_nested_csv setting if you are reading arrays from CSV and the default parsing does not work:

   SET input_format_csv_arrays_as_nested_csv = 1;
   

Best Practices

• Validate input data before sending it to ClickHouse, especially when dealing with complex types like arrays.
• Use JSONEachRow format for data containing arrays, as JSON has unambiguous array syntax.
• Set input_format_allow_errors_num to a reasonable value to avoid losing entire batches due to a few bad rows.
• When generating data for ClickHouse ingestion, use a ClickHouse client library that handles type serialization correctly rather than building text representations manually.

Frequently Asked Questions

Q: What is the correct format for nested arrays in text input?
A: Nested arrays use nested brackets: [[1,2],[3,4]]. Each level of nesting must be properly bracketed.

Q: Can I insert an empty array?
A: Yes. Use [] in text formats or an empty JSON array [] in JSONEachRow. The column type must still match -- [] is valid for any Array type.

Q: Why does my CSV file fail when it contains arrays?
A: CSV parsing of arrays can be tricky because commas serve as both field delimiters and array element separators. Make sure the entire array value is quoted in the CSV (e.g., "[1,2,3]") and consider using JSONEachRow or TabSeparated formats instead for data with complex types.