Import Data with `LOAD DATA LOCAL INFILE`

This page is part of MariaDB's Documentation.

The parent of this page is: Data Import / Load

Topics on this page:

Overview
Compatibility
Import Schema
Enable Local Infiles
- Enable Local Infiles on the Client or Connector
- Enable Local Infiles in SkySQL
Import Data

Overview

MariaDB SkySQL customers can import data into a SkySQL service using the LOAD DATA LOCAL INFILE statement:

The LOAD DATA LOCAL INFILE statement can import data from TSV and CSV files
The LOAD DATA LOCAL INFILE statement can be executed by any client or connector

Compatibility

Enterprise Server Single Node
Enterprise Server With Replica(s)
Xpand Distributed SQL
ColumnStore Data Warehouse

Import Schema

Determine the connection parameters for your MariaDB SkySQL service.
Use MariaDB Client with the connection information to import your schema (the DDL statements defining the structure of the databases, tables, and other objects that should be created in the SkySQL service) into your MariaDB SkySQL database service:
```
$ mariadb --host FULLY_QUALIFIED_DOMAIN_NAME --port TCP_PORT \
      --user DATABASE_USER --password \
      --ssl-verify-server-cert \
      --ssl-ca ~/PATH_TO_PEM_FILE \
      --default-character-set=utf8 \
      < mariadb-ddl.sql
```
- Replace FULLY_QUALIFIED_DOMAIN_NAME with the Fully Qualified Domain Name of your service
- Replace TCP_PORT with the read-write or read-only port of your service
- Replace DATABASE_USER with the default username for your service, or the username you created
- Replace ~/PATH_TO_PEM_FILE with the path to the certificate authority chain (.pem) file
After the command is executed, you will be prompted for a password. Enter the default password for your default user, the password you set for the default user, or the password for the database user you created.

Enable Local Infiles

Support for local infiles must be enabled on the client side and on the SkySQL service.

Enable Local Infiles on the Client or Connector

To execute the LOAD DATA LOCAL INFILE statement, most clients and connectors require a specific option to be enabled.

If you are using mariadb client, the --local-infile option must be specified.

If you are using a MariaDB Connector, then you must select the method for the specific connector from the list below.

If you are using MariaDB Connector/C, the MYSQL_OPT_LOCAL_INFILE option can be set with the mysql_optionsv() function:

/* enable local infile */
unsigned int enable_local_infile = 1;
mysql_optionsv(mysql, MYSQL_OPT_LOCAL_INFILE, (void *) &enable_local_infile);

If you are using MariaDB Connector/J, the allowLocalInfile parameter can be set for the connection:

Connection connection = DriverManager.getConnection("jdbc:mariadb://FULLY_QUALIFIED_DOMAIN_NAME:TCP_PORT/test?user=DATABASE_USER&password=DATABASE_PASSWORD&allowLocalInfile=true");

If you are using MariaDB Connector/Node.js, the permitLocalInfile parameter can be set for the connection:

mariadb.createConnection({
   host: 'FULLY_QUALIFIED_DOMAIN_NAME',
   port: 'TCP_PORT',
   user:'DATABASE_USER',
   password: 'DATABASE_PASSWORD',
   permitLocalInfile: 'true'
 });

If you are using MariaDB Connector/Python, the local_infile parameter can be set for the connection:

conn = mariadb.connect(
   user="DATABASE_USER",
   password="DATABASE_PASSWORD",
   host="FULLY_QUALIFIED_DOMAIN_NAME",
   port=TCP_PORT,
   local_infile=true)

Enable Local Infiles in SkySQL

Support for local infiles must be enabled on the SkySQL service.

For SkySQL services that use MariaDB Enterprise Server and MariaDB Enterprise ColumnStore, the local_infile system variable must be enabled:

For Multi-Node Analytics and Single Node Analytics services, the local_infile system variable is ON by default
For Replicated Transactions and Single Node Transactions services, the local_infile system variable is OFF by default

Configuration Manager can be used to modify the value of the local_infile system variable.

Import Data

Determine the connection parameters for your MariaDB SkySQL service.
Connect with the mariadb client and specify the --local-infile option, which is needed by the next step:
```
$ mariadb --host FULLY_QUALIFIED_DOMAIN_NAME --port TCP_PORT \
      --user DATABASE_USER --password \
      --ssl-verify-server-cert \
      --ssl-ca ~/PATH_TO_PEM_FILE \
      --default-character-set=utf8 \
      --local-infile
```
- Replace FULLY_QUALIFIED_DOMAIN_NAME with the Fully Qualified Domain Name of your service
- Replace TCP_PORT with the read-write or read-only port of your service
- Replace DATABASE_USER with the default username for your service, or the username you created
- Replace ~/PATH_TO_PEM_FILE with the path to the certificate authority chain (.pem) file
After the command is executed, you will be prompted for a password. Enter the default password for your default user, the password you set for the default user, or the password for the database user you created.
For each table that you want to import, execute the LOAD DATA LOCAL INFILE statement to import the data from the TSV or CSV file into your MariaDB SkySQL database service.

For a TSV file:
```
LOAD DATA LOCAL INFILE 'contacts.tsv'
INTO TABLE accounts.contacts;
```
For a CSV file:
```
LOAD DATA LOCAL INFILE 'contacts.csv'
INTO TABLE accounts.contacts
FIELDS TERMINATED BY ',';
```

Import Data with LOAD DATA LOCAL INFILE