Data import

Overview

Direct Load is a fast loading mechanism that provides fast data loading capabilities for X-Engine column store tables. Direct Load supports the following three import methods: importing data from OSS, importing data from local files, and importing data in CSV format (the default format). In addition to Direct Load, X-Engine full column store also supports the following data import methods:

• Native MySQL import

• DTS import (select X-Engine as the destination engine)

Session parameters

Direct Load provides the following session-level parameters to control import behavior.

Syntax

Direct Load uses the LOAD DATA syntax and supports importing data from OSS or local files.

LOAD DATA [OSS|LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var [, ...])]
    [SET col_name={expr | DEFAULT} [, ...]]

Limits

When you use Direct Load to import data, take note of the following limits.

• Table requirements:

  • Direct Load supports only tables with ENGINE=XENGINE and TABLE_FORMAT=COLUMN. The table must be empty.

  • The table must be a regular table (non-partitioned).

    Note

    For partitioned tables, if you need to import data by partition, you can first import a single partition into a temporary table, and then use the EXCHANGE PARTITION syntax to convert the temporary table into a partition of the target partitioned table. The EXCHANGE PARTITION syntax only exchanges metadata and is very fast. HASH partitions are not supported.

• Index support:

  • Tables with ORDER KEY are supported.

  • Secondary indexes, foreign keys, and hidden keys are not supported.

    Note

    The hidden key limit applies when the xengine_enable_ctable_hidden_key=ON parameter is set to ON.

• Binary log: When Direct Load is enabled, binary log writing is automatically disabled by temporarily clearing the OPTION_BIN_LOG flag, which does not affect the global configuration.

• Triggers: Tables with triggers do not support Direct Load.

• Concurrency limits:

  • Only one Direct Load statement is allowed per table at a time.

  • An exclusive metadata lock (MDL) is held during execution.

  • DML (data manipulation) and DDL (data definition) operations on the table are prohibited.

• File wildcards:

  • OSS mode supports the wildcard character (*). Example: /home/t4/data/*.csv.

  • LOCAL mode and non-Direct Load mode do not support wildcards.

Examples

DROP SERVER IF EXISTS my_server;
CREATE SERVER my_server FOREIGN DATA WRAPPER oss OPTIONS (
  EXTRA_SERVER_INFO '{"oss_endpoint":"oss-cn-xxxx-internal.aliyuncs.com","oss_bucket":"xxxx","oss_access_key_id":"xxxx","oss_access_key_secret":"xxxx"}'
);
SELECT * FROM mysql.servers;

CREATE TABLE lineitem (
    l_orderkey BIGINT NOT NULL,
    l_partkey BIGINT NOT NULL,
    l_suppkey BIGINT NOT NULL,
    l_linenumber INT NOT NULL,
    l_quantity DECIMAL(15,2) NOT NULL,
    l_extendedprice DECIMAL(15,2) NOT NULL,
    l_discount DECIMAL(15,2) NOT NULL,
    l_tax DECIMAL(15,2) NOT NULL,
    l_returnflag CHAR(1) NOT NULL,
    l_linestatus CHAR(1) NOT NULL,
    l_shipdate DATE NOT NULL,
    l_commitdate DATE NOT NULL,
    l_receiptdate DATE NOT NULL,
    l_shipinstruct CHAR(25) NOT NULL,
    l_shipmode CHAR(10) NOT NULL,
    l_comment VARCHAR(44) NOT NULL,
    PRIMARY KEY (l_orderkey, l_linenumber, l_shipdate),
    ORDER KEY(l_orderkey)
) ENGINE=XENGINE TABLE_FORMAT=COLUMN;

-- Set the degree of parallelism (optional).
SET xengine_dload_parallel = 2;

-- Use wildcards to import data (OSS mode supports wildcards).
LOAD DATA OSS INFILE 'my_server/lineitem/lineitem_test*.csv'
INTO TABLE lineitem
FIELDS TERMINATED BY '|';

-- Verify the import result.
SELECT * FROM lineitem ORDER BY l_orderkey, l_partkey DESC LIMIT 10;
SELECT COUNT(1) FROM lineitem;

CREATE TABLE lineitem (
    l_orderkey       BIGINT NOT NULL,
    l_partkey        BIGINT NOT NULL,
    l_suppkey        BIGINT NOT NULL,
    l_linenumber     BIGINT NOT NULL,
    l_quantity       DECIMAL(15,2) NOT NULL,
    l_extendedprice  DECIMAL(15,2) NOT NULL,
    l_discount       DECIMAL(15,2) NOT NULL,
    l_tax            DECIMAL(15,2) NOT NULL,
    l_returnflag     CHAR(1) NOT NULL,
    l_linestatus     CHAR(1) NOT NULL,
    l_shipdate       DATE NOT NULL,
    l_commitdate     DATE NOT NULL,
    l_receiptdate    DATE NOT NULL,
    l_shipinstruct   CHAR(25) NOT NULL,
    l_shipmode       CHAR(10) NOT NULL,
    l_comment        VARCHAR(44) NOT NULL,
    PRIMARY KEY (l_orderkey, l_linenumber, l_shipdate),
    ORDER KEY(l_orderkey)
) ENGINE=XENGINE TABLE_FORMAT=COLUMN;

SET xengine_dload_parallel = 1;

-- Import data from a local file (wildcards are not supported).
LOAD DATA LOCAL INFILE '/home/data/lineitem/lineitem_test.csv'
INTO TABLE lineitem
FIELDS TERMINATED BY '|';

-- Verify the import result.
SELECT * FROM lineitem ORDER BY l_orderkey, l_partkey DESC LIMIT 10;
SELECT COUNT(1) FROM lineitem;

TPC-H 100 GB import example

The following example shows how to use Direct Load to import TPC-H data of approximately 100 GB from OSS.

SET xengine_enable_ctable_hidden_key = OFF;

DROP DATABASE IF EXISTS tpch100;
CREATE DATABASE tpch100;
USE tpch100;

-- Example: Create the lineitem table.
CREATE TABLE lineitem (
    L_ORDERKEY    INTEGER NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
    L_SUPPKEY     INTEGER NOT NULL,
    L_LINENUMBER  INTEGER NOT NULL,
    L_QUANTITY    DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
    L_DISCOUNT    DECIMAL(15,2) NOT NULL,
    L_TAX         DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG  CHAR(1) NOT NULL,
    L_LINESTATUS  CHAR(1) NOT NULL,
    L_SHIPDATE    DATE NOT NULL,
    L_COMMITDATE  DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE     CHAR(10) NOT NULL,
    L_COMMENT      VARCHAR(44) NOT NULL,
    PRIMARY KEY (L_ORDERKEY, L_LINENUMBER, L_SHIPDATE)
) ENGINE=XENGINE TABLE_FORMAT=COLUMN;

DROP SERVER IF EXISTS my_server;

CREATE SERVER my_server
FOREIGN DATA WRAPPER oss OPTIONS
(
  EXTRA_SERVER_INFO '{"oss_endpoint": "oss-cn-xxxx.aliyuncs.com","oss_bucket": "xxxx","oss_access_key_id": "xxxx","oss_access_key_secret": "xxxx"}'
);

#!/usr/bin/env bash
set -u

SOCK=/tmp/mysql29.sock
OUT=output.txt
PREFIX="my_server/imci/tpch100g"
DB=tpch100
PARA=64

: > "$OUT"

tables=(lineitem nation region part supplier partsupp customer orders)

run() {
  local t="$1"
  (
    echo "===== [$t] START $(date '+%F %T') ====="
    sql="SET xengine_dload_parallel=${PARA};
         LOAD DATA OSS INFILE '${PREFIX}/${t}.*'
         INTO TABLE ${DB}.${t}
         FIELDS TERMINATED BY '|';"
    { time mysql -uroot -S "$SOCK" -e "$sql"; } 2>&1
    rc=${PIPESTATUS[0]:-0}
    echo "===== [$t] END   $(date '+%F %T') rc=$rc ====="
    echo
    exit "$rc"
  ) >>"$OUT" 2>&1 &
  echo "[$t] pid=$!"
}

for t in "${tables[@]}"; do
  run "$t"
done

wait
echo "ALL DONE $(date '+%F %T')" >> "$OUT"

Error handling

When you use Direct Load to import data, you may encounter the following common errors.

Best practices

• Binary log automatically disabled: Direct Load automatically disables binary log writing. No manual operation is required. Binary log writing is automatically restored after the import, which does not affect the global configuration.

• Set an appropriate degree of parallelism: For OSS import scenarios, we recommend that you set xengine_dload_parallel to 32 to 64. For local file import, we recommend that you set a lower value. Example:

  SET xengine_dload_parallel = 64;

• Make sure that the table is empty: Direct Load requires that the destination table be empty. Make sure that the table contains no data before the import.

• Remove secondary indexes before the import: Direct Load does not support secondary indexes. We recommend that you delete secondary indexes before the import and recreate them after the import is complete.

• Use wildcards in OSS mode: Use the wildcard character (*) to match multiple files at a time to simplify large-scale data import. Example:

  LOAD DATA OSS INFILE 'my_server/lineitem/lineitem_test*.csv'
  INTO TABLE lineitem
  FIELDS TERMINATED BY '|';