ClickHouse DB::Exception: Cannot read Map from text

The "DB::Exception: Cannot read Map from text" error in ClickHouse occurs when the parser cannot interpret a text value as a Map type. The CANNOT_READ_MAP_FROM_TEXT error is raised during data ingestion when the text representation of a Map does not conform to the expected syntax. Map is a relatively newer ClickHouse type, and its text serialization has specific formatting requirements that differ across input formats.

Impact

The INSERT or data reading operation fails at the row containing the invalid Map literal. If error tolerance settings are configured, individual rows may be skipped. Otherwise, the entire batch is rejected. This does not affect existing data in the target table.

Common Causes

1. Incorrect Map syntax -- using JSON object notation in a non-JSON format or vice versa
2. Key-value separator mismatch (e.g., using = instead of : or vice versa depending on the format)
3. Missing braces or brackets around the Map literal
4. Type mismatch between Map key/value types and the provided data (e.g., string keys for a Map(UInt32, String))
5. Improperly escaped special characters within Map keys or values
6. Empty or malformed Map literals

Troubleshooting and Resolution Steps

1. Understand the Map text format for your input type. In TabSeparated and CSV:

   {'key1':1,'key2':2}
   

   In JSONEachRow:

   {"map_col": {"key1": 1, "key2": 2}}
   

2. Check your column definition to confirm the Map key and value types:

   SELECT name, type
   FROM system.columns
   WHERE database = 'your_db' AND table = 'your_table' AND type LIKE 'Map%';
   

3. Verify the key-value syntax. For text formats, Maps use single quotes around string keys and colons as separators:

   {'key1':'value1','key2':'value2'}
   

4. For JSON formats, use standard JSON object notation:

   INSERT INTO your_table FORMAT JSONEachRow
   {"id": 1, "metadata": {"env": "production", "region": "us-east"}}
   

5. Handle empty Maps correctly:

   -- In text formats
   {}
   -- In JSON
   {}
   

6. Use error tolerance settings to identify and skip problematic rows:

   SET input_format_allow_errors_num = 10;
   INSERT INTO your_table FORMAT CSV ...
   

7. If the issue is widespread, consider pre-processing the data to fix Map formatting before ingestion, or use a binary format like Native or RowBinary that avoids text parsing altogether.

Best Practices

• Use JSONEachRow format when inserting data with Map columns, as JSON's object notation maps naturally to ClickHouse's Map type.
• Validate Map data in your application layer before sending it to ClickHouse.
• Use ClickHouse client libraries that handle Map serialization correctly rather than constructing text representations by hand.
• Consider whether a Map type is truly necessary -- in some cases, separate columns or an Array of Tuples may be simpler to work with.

Frequently Asked Questions

Q: What is the difference between Map syntax in JSON and TSV formats?
A: In JSON formats, Maps are represented as JSON objects: {"key": "value"}. In TSV and other text formats, Maps use single-quoted keys with colon separators: {'key':'value'}. Mixing these syntaxes will trigger the CANNOT_READ_MAP_FROM_TEXT error.

Q: Can Map keys be integers?
A: Yes, if the Map type is defined with an integer key type (e.g., Map(UInt32, String)). In that case, the text representation would be {1:'value1', 2:'value2'} in text formats.

Q: Is there a size limit on Maps?
A: There is no hard limit on the number of key-value pairs in a Map, but very large Maps can impact query performance and memory usage. Keep Maps reasonably sized for best results.