5.2.3 Loading Data Manually

5.2.3.1 Manually Loading Data from External Storage

As of MySQL 8.4.0, Lakehouse supports primary key and unique key constraint validation. Multiple primary key and unique key constraints can increase load time. Set check_constraints to false to avoid primary key and unique key constraint validation. See: Section 5.2.2, “Lakehouse External Table Syntax”.

To load data manually, follow these steps:

1. Choose whether to use a pre-authenticated request or a resource principal, see: Section 5.2.3.1, “Manually Loading Data from External Storage”.

2. Use the Lakehouse ENGINE_ATTRIBUTE with CREATE TABLE statements, see: Section 5.2.2, “Lakehouse External Table Syntax”.

3. Review these steps: Section 2.2.2, “Loading Data Manually”.

Lakehouse extends Guided Load for external tables, see: Section 2.2.2, “Loading Data Manually”. This employs Autopilot to perform a series of pre-load validation checks, and includes the following:

• Detects any errors with ENGINE_ATTRIBUTE and reports them.

• Infers the schema and performs similar schema adjustments to those performed by Autopilot during Lakehouse Auto Parallel Load. If the inferred schema is not compatible with the defined schema, then Guided Load aborts the load. See: Section 5.2.4.1, “Lakehouse Auto Parallel Load Schema Inference”.

• Predicts the amount of memory required, and checks that this is available. If the required memory is not available, Guided Load aborts the load.

• Loads CSV files without having to specify the exact record and field delimiters. For tables that are already created, an ALTER TABLE statement is issued to update the ENGINE_ATTRIBUTE option for the table, which replaces values of delimiter parameters that are set to auto with the detected delimiters. For more information, see Section 5.2.2, “Lakehouse External Table Syntax”. This is supported from MySQL 9.2.0.

To monitor any issues encountered during this pre-load validation process, run the SHOW WARNINGS statement after the load command has finished.