MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

21.4.14 ndb_import — Import CSV Data Into NDB

ndb_import imports CSV-formatted data, such as that produced by mysqldump --tab, directly into NDB using the NDB API. ndb_import requires a connection to an NDB management server (ndb_mgmd) to function; it does not require a connection to a MySQL Server.

Usage

ndb_import db_name file_name options

ndb_import requires two arguments. db_name is the name of the database where the table into which to import the data is found; file_name is the name of the CSV file from which to read the data; this must include the path to this file if it is not in the current directory. The name of the file must match that of the table; the file's extension, if any, is not taken into consideration. Options supported by ndb_import include those for specifying field separators, escapes, and line terminators, and are described later in this section. ndb_import must be able to connect to an NDB Cluster management server; for this reason, there must be an unused [api] slot in the cluster config.ini file.

To duplicate an existing table that uses a different storage engine, such as InnoDB, as an NDB table, use the mysql client to perform a SELECT INTO OUTFILE statement to export the existing table to a CSV file, then to execute a CREATE TABLE LIKE statement to create a new table having the same structure as the existing table, then perform ALTER TABLE ... ENGINE=NDB on the new table; after this, from the system shell, invoke ndb_import to load the data into the new NDB table. For example, an existing InnoDB table named myinnodb_table in a database named myinnodb can be exported into an NDB table named myndb_table in a database named myndb as shown here, assuming that you are already logged in as a MySQL user with the appropriate privileges:

  1. In the mysql client:

    mysql> USE myinnodb;
    
    mysql> SELECT * INTO OUTFILE '/tmp/myndb_table.csv'
         >  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
         >  LINES TERMINATED BY '\n'
         >  FROM myinnodbtable;
    
    mysql> CREATE DATABASE myndb;
    
    mysql> USE myndb;
    
    mysql> CREATE TABLE myndb_table LIKE myinnodb.myinnodb_table;
    
    mysql> ALTER TABLE myndb_table ENGINE=NDB;
    
    mysql> EXIT;
    Bye
    shell>
    

    Once the target database and table have been created, a running mysqld is no longer required. You can stop it using mysqladmin shutdown or another method before proceeding, if you wish.

  2. In the system shell:

    # if you are not already in the MySQL bin directory:
    shell> cd path-to-mysql-bin-dir
    
    shell> ndb_import myndb /tmp/myndb_table.csv --fields-optionally-enclosed-by='"' \
        --fields-terminated-by="," --fields-escaped-by='\\'
    

    The output should resemble what is shown here:

    job-1 import myndb.myndb_table from /tmp/myndb_table.csv
    job-1 [running] import myndb.myndb_table from /tmp/myndb_table.csv
    job-1 [success] import myndb.myndb_table from /tmp/myndb_table.csv
    job-1 imported 19984 rows in 0h0m9s at 2277 rows/s
    jobs summary: defined: 1 run: 1 with success: 1 with failure: 0
    shell>
    

The following table includes options that are specific to ndb_import. Additional descriptions follow the table. For options common to most NDB Cluster programs (including ndb_import), see Section 21.4.32, “Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs”.

Table 21.341 Command-line options for the ndb_import program

Format Description Added, Deprecated, or Removed

--abort-on-error

Dump core on any fatal error; used for debugging

ADDED: NDB 7.6.2

--ai-increment=#

For table with hidden PK, specify autoincrement increment. See mysqld

ADDED: NDB 7.6.2

--ai-offset=#

For table with hidden PK, specify autoincrement offset. See mysqld

ADDED: NDB 7.6.2

--ai-prefetch-sz=#

For table with hidden PK, specify number of autoincrement values that are prefetched. See mysqld

ADDED: NDB 7.6.2

--connections=#

Number of cluster connections to create

ADDED: NDB 7.6.2

--continue

When job fails, continue to next job

ADDED: NDB 7.6.2

--db-workers=#

Number of threads, per data node, executing database operations

ADDED: NDB 7.6.2

--errins-type=name

Error insert type, for testing purposes; use "list" to obtain all possible values

ADDED: NDB 7.6.2

--errins-delay=#

Error insert delay in milliseconds; random variation is added

ADDED: NDB 7.6.2

--fields-enclosed-by=char

Same as FIELDS ENCLOSED BY option for LOAD DATA statements. For CSV input this is same as using --fields-optionally-enclosed-by

ADDED: NDB 7.6.2

--fields-escaped-by=name

Same as FIELDS ESCAPED BY option for LOAD DATA statements

ADDED: NDB 7.6.2

--fields-optionally-enclosed-by=char

Same as FIELDS OPTIONALLY ENCLOSED BY option for LOAD DATA statements

ADDED: NDB 7.6.2

--fields-terminated-by=char

Same as FIELDS TERMINATED BY option for LOAD DATA statements.

ADDED: NDB 7.6.2

--idlesleep=#

Number of milliseconds to sleep waiting for more to do

ADDED: NDB 7.6.2

--idlespin=#

Number of times to re-try before idlesleep

ADDED: NDB 7.6.2

--ignore-lines=#

Ignore first # lines in input file. Used to skip a non-data header.

ADDED: NDB 7.6.2

--input-type=name

Input type: random or csv

ADDED: NDB 7.6.2

--input-workers=#

Number of threads processing input. Must be 2 or more if --input-type is csv.

ADDED: NDB 7.6.2

--keep-state

Preserve state files

ADDED: NDB 7.6.4

--lines-terminated-by=name

Same as LINES TERMINATED BY option for LOAD DATA statements

ADDED: NDB 7.6.2

--log-level=#

Set internal logging level; for debugging and development

ADDED: NDB 7.6.4

--max-rows=#

Import only this number of input data rows; default is 0, which imports all rows

ADDED: NDB 7.6.2

--monitor=#

Periodically print status of running job if something has changed (status, rejected rows, temporary errors). Value 0 disables. Value 1 prints any change seen. Higher values reduce status printing exponentially up to some pre-defined limit.

ADDED: NDB 7.6.2

--no-asynch

Run database operations as batches, in single transactions

ADDED: NDB 7.6.2

--no-hint

Do not use distribution key hint to select data node (TC)

ADDED: NDB 7.6.2

--opbatch=#

A db execution batch is a set of transactions and operations sent to NDB kernel. This option limits NDB operations (including blob operations) in a db execution batch. Therefore it also limits number of asynch transactions. Value 0 is not valid

ADDED: NDB 7.6.2

--opbytes=#

Limit bytes in execution batch (default 0 = no limit)

ADDED: NDB 7.6.2

--output-type=name

Output type: ndb is default, null used for testing

ADDED: NDB 7.6.2

--output-workers=#

Number of threads processing output or relaying database operations

ADDED: NDB 7.6.2

--pagesize=#

Align I/O buffers to given size

ADDED: NDB 7.6.2

--pagecnt=#

Size of I/O buffers as multiple of page size. CSV input worker allocates a double-sized buffer

ADDED: NDB 7.6.2

--polltimeout=#

Timeout per poll for completed asynchonous transactions; polling continues until all polls are completed, or error occurs

ADDED: NDB 7.6.2

--rejects=#

Limit number of rejected rows (rows with permanent error) in data load. Default is 0 which means that any rejected row causes a fatal error. The row exceeding the limit is also added to *.rej

ADDED: NDB 7.6.2

--resume

If job aborted (temporary error, user interrupt), resume with rows not yet processed

ADDED: NDB 7.6.2

--rowbatch=#

Limit rows in row queues (default 0 = no limit); must be 1 or more if --input-type is random

ADDED: NDB 7.6.2

--rowbytes=#

Limit bytes in row queues (0 = no limit)

ADDED: NDB 7.6.2

--state-dir=name

Where to write state files; currect directory is default

ADDED: NDB 7.6.2

--stats

Save performance and statistics information in *.sto and *.stt files

ADDED: NDB 7.6.4

--tempdelay=#

Number of milliseconds to sleep between temporary errors

ADDED: NDB 7.6.2

--temperrors=#

Number of times a transaction can fail due to a temporary error, per execution batch; 0 means any temporary error is fatal. Such errors do not cause any rows to be written to .rej file

ADDED: NDB 7.6.2

--verbose=#,

-v

Enable verbose output

ADDED: NDB 7.6.2


As with LOAD DATA, options for field and line formatting much match those used to create the CSV file, whether this was done using SELECT INTO ... OUTFILE, or by some other means. There is no equivalent to the LOAD DATA statement STARTING WITH option.

ndb_import was added in NDB 7.6.2.