6.2.2.3 Bulk Ingest Limitations

The following are some limitations to the Bulk Ingest Feature of HeatWave on AWS when using the LOAD DATA FROM S3 statement with ALGORITHM=BULK:

• • The target table must be empty. The state of the table should be as though it has been freshly created. If the table has instantly added or dropped column, run the TRUNCATE TABLE statement before running LOAD DATA FROM S3 with ALGORITHM=BULK.
  • The target table must not be partitioned.
  • The target table must not contain secondary indexes.
  • The target table must be in a file-per-table tablespace—it must not be in a shared tablespace.
  • The target table must have the default row format ROW_FORMAT=DYNAMIC. If needed, use ALTER TABLE to change the table's row format after running LOAD DATA FROM S3 with ALGORITHM=BULK.
  • The target table must contain a primary key.
  • Prefix indexing for the primary key is not supported.
  • The target table must not contain virtual or stored generated columns.
  • The target table must not contain foreign keys.
  • The target table must not contain CHECK constraints.
  • The target table must not contain triggers.
  • The target table must not be replicated to other nodes.
  • The target table must not use a secondary engine. If needed, set the secondary engine after running LOAD DATA FROM S3 with ALGORITHM=BULK.
  • The INFILE and URL clauses are not supported for LOAD DATA FROM S3 with ALGORITHM=BULK.
  • LOAD DATA FROM S3 with ALGORITHM=BULK locks the target table exclusively and does not allow other operations on the table when the query is running.
  • Automatic rounding or truncation of the input data is not supported. LOAD DATA FROM S3 with ALGORITHM=BULK will fail if the input data requires rounding or truncation in order to be loaded.
  • The target table cannot be a temporary table.
  • LOAD DATA FROM S3 with ALGORITHM=BULK is atomic but not transactional. It commits any transaction that is already running. On failure, the LOAD DATA FROM S3 statement is completely rolled back.
  • LOAD DATA FROM S3 with ALGORITHM=BULK cannot be executed when the target table is explicitly locked by a LOCK TABLES statement.