Load Data from Local Files

Example Using a Local CSV File

Using this table format:

SQL

CREATE TABLE employees(emp_id INT,
  emp_lastname VARCHAR(25),emp_firstname VARCHAR(25),
  emp_title VARCHAR(25),dept VARCHAR(25),emp_city VARCHAR(25),
  emp_state VARCHAR(5),emp_ccode VARCHAR(4));

Load data from a local file location:

SQL

LOAD DATA LOCAL INFILE '/tmp/emp_data.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

Verify the file loaded correctly:

SQL

SELECT * FROM employees;

+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
| emp_id | emp_lastname | emp_firstname | emp_title     | dept      | emp_city     | emp_state | emp_ccode |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
|    102 | Karras       | Damien        | Doctor        | R&D       | NYC          | NY        | US        |
|    110 | Strode       | Laurie        | VP Sales      | Sales     | Haddonfield  | IL        | US        |
|     89 | Whateley     | Wilbur        | CEO           | Sen_Mgmt  | Dunwich      | MA        | US        |
|    817 | Eriksson     | Oskar         | Support       | IT        | Stockholm    | NULL      | SE        |
|    298 | Denbrough    | Bill          | Salesperson   | Sales     | Bangor       | ME        | US        |
|    399 | Torrance     | Jack          | PR Dir        | PR        | Estes Park   | CO        | US        |
|    410 | Wilkes       | Annie         | HR Mgr        | HR        | Silver Creek | CO        | US        |
|    312 | Cady         | Max           | IT Dir        | IT        | New Essex    | FL        | US        |
|    691 | Grevers      | Nick          | Rep           | PR        | Grimnetz     | NULL      | CH        |
|     14 | Bateman      | Patrick       | Prod_Mgr      | prod_dev  | NYC          | NY        | US        |
|     75 | White        | Carrie        | Receptionist  | HR        | Chamberlain  | ME        | US        |
|    263 | MacNeil      | Regan         | R&D Mgr       | R&D       | Washington   | DC        | US        |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+

Stream Data with LOAD DATA LOCAL INFILE

Instead of loading data from a file on the local filesystem, applications can stream data directly from memory into LOAD DATA LOCAL INFILE. This is useful when:

Data is generated programmatically (for example, by an ETL script) and would otherwise need to be written to a temporary file first.
Data is produced incrementally from another source (for example, another database, a queue, or a stream processor) and must be loaded without buffering the full dataset on disk.
The application already has the rows in memory and does not need file-based persistence.

Compared with multi-row INSERT, streaming can reduce client and network overhead by avoiding the construction and transmission of a large SQL statement. It can also use compression to reduce the amount of data sent over the network. For large datasets, LOAD DATA can load the data with a single statement, whereas INSERT statements may need to be split into multiple statements because the SQL query size is limited by max_allowed_packet.

Streaming uses the same MySQL protocol mechanism as file-based LOAD DATA LOCAL INFILE, SingleStore requests the file contents from the client, and the client responds with in-memory bytes. All standard LOAD DATA clauses (FIELDS TERMINATED BY, ENCLOSED BY, SET, SKIP DUPLICATE KEY ERRORS, FORMAT AVRO, FORMAT JSON, FORMAT PARQUET, etc.) are supported.

The application must use a SingleStore client library that supports stream-based LOAD DATA LOCAL INFILE. Supported clients include:

SingleStore Python client (singlestoredb)
SingleStore JDBC driver
SingleStore ADBC connector
SingleStore .NET connector
SingleStore Go driver

Each supported client provides its own mechanism for attaching an in-memory stream to LOAD DATA LOCAL INFILE.

Supported clients and connectors expose stream-based LOAD DATA LOCAL INFILE through their own APIs:

SingleStore Python client (singlestoredb): Use :stream: as the INFILE value and pass the in-memory iterator or stream through the infile_stream argument.
SingleStore JDBC driver: When an input stream is registered with Statement.setNextLocalInfileInputStream(InputStream), the filename is ignored; no specific filename format is required.
SingleStore .NET connector: Use SingleStoreBulkLoader to load the stream. A filename is not required.
SingleStore Go driver: Use Reader::<name>, where <name> is the exact string passed to RegisterReaderHandler.

Refer to the documentation for the specific client or connector for the API used to attach the in-memory stream.

For example, the following example uses the SingleStore Python client to stream CSV data generated dynamically into a table:

Python

import singlestoredb as s2

conn = s2.connect('user:password@host:3306/my_db?local_infile=true')
cur = conn.cursor()

cur.execute('CREATE TABLE generator (a INT, b INT, c INT)')

def upload_csv():
    yield '1,2,3\n'
    yield '4,5,6\n'
    yield '7,8,9\n'
    # Chunks do not need to align to row boundaries.
    yield '10,11,12\n13,14,15\n'

cur.execute(
    """
    LOAD DATA LOCAL INFILE ':stream:' INTO TABLE generator
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    """,
    infile_stream=upload_csv(),
)

The generator streams chunks to the server. Each chunk contains raw CSV bytes. Row boundaries are determined by the line terminator (\n in this example), not by chunk boundaries.

For patterns where the data producer runs in a different thread from the SQL client, use a queue. For example:

Python

import singlestoredb as s2
from queue import Queue
from threading import Thread

conn = s2.connect('user:password@host:3306/my_db?local_infile=true')
cur = conn.cursor()

cur.execute('CREATE TABLE queue_load (a INT, b INT, c INT)')

data_feeder = Queue()

t = Thread(
    target=cur.execute,
    args=(
        """
        LOAD DATA LOCAL INFILE ':stream:' INTO TABLE queue_load
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        """,
    ),
    kwargs=dict(infile_stream=data_feeder),
)
t.start()

data_feeder.put('1,2,3\n')
data_feeder.put('4,5,6\n')
data_feeder.put('7,8,9\n')
data_feeder.put('10,11,12\n13,14,15\n')

# An empty string ends the stream.
data_feeder.put('')

t.join()

Streamed data is subject to the same parsing, error handling, and load data error logging as file-based loads. Error rows can be captured with ERRORS HANDLE '<handle>' and inspected in information_schema.LOAD_DATA_ERRORS.

Client-side connection settings that disable LOAD DATA LOCAL INFILE (for example, local_infile=0) reject stream-based loads with the same client-side error as file-based loads.

CSV FILE DATA

Below is the contents of the csv file used in the examples above.

CSV

014, Bateman, Patrick,Prod_Mgr, prod_dev, NYC, NY, US
102, Karras, Damien, Doctor,R&D, NYC, NY, US
298, Denbrough, Bill, Salesperson,Sales, Bangor, ME, US
399, Torrance, Jack, PR Dir, PR,Estes Park,CO, US
410, Wilkes, Annie, HR Mgr,HR,Silver Creek, CO, US
110, Strode, Laurie, VP Sales,Sales, Haddonfield, IL, US
312, Cady, Max, IT Dir, IT, New Essex,FL, US
089, Whateley, Wilbur, CEO, Sen_Mgmt, Dunwich, MA, US
075, White, Carrie, Receptionist, HR,Chamberlain, ME, US
263, MacNeil, Regan, R&D Mgr,R&D, Washington, DC, US
817, Eriksson, Oskar, Support, IT, Stockholm, NULL, SE
691, Grevers, Nick, Rep, PR, Grimnetz, NULL, CH