The Ultimate Guide to High Availability with MariaDB

ColumnStore LOAD DATA INFILE

You are viewing an old version of this article. View the current version here.

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

LOAD DATA [LOCAL] INFILE 'file_name' 
  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']
]
  • ColumnStore ignores the ON DUPLICATE KEY clause.
  • Non-transactional LOAD DATA INFILE is directed to ColumnStores cpimport tool by default, which significantly increases performance.
  • Transactional LOAD DATA INFILE statements (that is with AUTOCOMMIT off or after a START TRANSACTION) are processed through normal DML processes.
  • When using LOAD DATA LOCAL INFILE with the mcsmysql utility , use the --local-infile command-line option.
  • Use cpimport for importing UTF-8 data that contains multi-byte values

The following example loads data into the a simple 5 column table: A file named /simpletable.tbl has the following data in it.

1|100|1000|10000|Test Number 1|
2|200|2000|20000|Test Number 2|
3|300|3000|30000|Test Number 3|

The data can then be loaded into the simpletable table with the following syntax:

LOAD DATA INFILE 'simpletable.tbl' INTO TABLE simpletable FIELDS TERMINATED BY '|'

If the default mode is set to use cpimport internally any output error files will be written to /usr/local/mariadb/columnstore/mysql/db directory (or equivalent directory for non root install). These can be consulted for troubleshooting any errors reported.

See Also

LOAD DATA INFILE

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.