11.2.2.5 Lakehouse Limitations for the Parquet File Format

MySQL HeatWave Lakehouse has the following limitations for Parquet files.

• NaN values in Parquet files are loaded as NULL.

• As of MySQL 9.3.2, Lakehouse supports the VECTOR data type. Consider the following limitations:

  • To load Parquet files with the VECTOR data type (from Parquet strings or from Parquet (numeric) Lists), you must load the table manually with is_strict_mode set to false. For loading simple lists as JSON, you can load the data manually or automatically. To learn more, see Vector Data Type and External Table Syntax.

    Tip

    If you need to manually create a table but do not have the appropriate table definition, you can load the table automatically in dryrun mode and review the load script. You also need to convert columns from JSON to VECTOR as needed. See the example commands below to do this.

    mysql> SET @input_list = '[{
      "db_name": "data_db",
      "tables": [{
        "table_name": "table_1",
        "engine_attribute": {"dialect": {"format": "parquet" "embed_model_id": "minilm"},
                             "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/data_file_1.parquet"}]}
    }]}]';
    mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), JSON_OBJECT('mode', 'dryrun'));
    mysql> SELECT log->>"$.sql" AS "Load Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id;

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

  • 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.

• As of MySQL 9.1.2, you can load Parquet files with row groups that total up to 10GB with large MySQL HeatWave Cluster shapes.

• Lakehouse does not support the following data types in Parquet files. Lakehouse marks columns with these data types as NOT SECONDARY, and does not load them.

  • BSON

  • ENUM

  • Interval

  • List

    • Not supported before MySQL 9.3.2. As of MySQL 9.3.2, limited support is available for Parquet Lists in Lakehouse. Simple (non-nested) lists of the following Parquet data types are now supported: INT_8, INT_16, INT_32, INT_64, UINT_8, UINT_16, UINT_32, UINT_64, FLOAT (32-bit), DOUBLE, STRING, and BOOL.

  • Map

  • Unknown

  • UUID

• Do not use strict SQL mode if the inferred schema differs from the table schema.