11.2.2.3 Lakehouse Limitations for the CSV File Format

MySQL HeatWave Lakehouse has the following limitations for CSV files.

• MySQL does not recognise NaN values in CSV files. Replace all NaN values with NULL.

• Lakehouse does not support CSV files with more than 4MB per line.

• As of MySQL 9.3.2, Lakehouse supports the VECTOR data type for CSV and Parquet files. Consider the following limitations:

  • You cannot give the VECTOR data type a default value other than NULL.

  • Vectors that are in binary or hex format are not supported.

  • You cannot directly convert VECTOR to JSON or JSON to VECTOR.

    • To convert VECTOR to JSON, first convert VECTOR to a STRING data type by using the VECTOR_TO_STRING() function. Then, cast this string as JSON.

      For example, if col_1 in table_1 is a VECTOR column that you want to convert to JSON, you can do the following:

      SELECT CAST(VECTOR_TO_STRING(col_1) as JSON) FROM table_1;

    • To convert JSON to VECTOR, first convert JSON to a STRING data type, and then use the STRING_TO_VECTOR() function to convert to VECTOR.

      For example, if col_1 in table_1 is a JSON column that you want to convert to VECTOR, you can do the following:

      SELECT STRING_TO_VECTOR(CAST(col_1 as NCHAR)) FROM table_1;

  • If you run Vector_to_string(col)as a Vector_to_string() function, it fails if there are any 0x (default for invalid vectors) values. To avoid errors, filter out these values.