Loading Data into MemSQL Cloud
MemSQL Cloud supports the following methods to loading data into a table:
- Loading the contents of a file using the LOAD DATA command
- Creating a Pipeline to ingest data from a supported data source
- Using INSERT … SELECT to insert rows from an existing table
This topic describes each method and their limitations when used in MemSQL Cloud.
Using the LOAD DATA Command in MemSQL Cloud
To load a the contents of a text file with delimited fields, you can use the LOAD DATA command to populate an existing table with the file’s data.
Since you cannot access the host file system on a MemSQL Cloud cluster, you can only use the
LOCAL clause of
LOAD DATA, which reads files from the client and sends them to the server.
LOAD DATA INFILE without the
LOCAL clause is not supported.
Follow the steps below to load data from a local file into a MemSQL Cloud table using a MySQL-compatible command-line client:
In a MySQL-compatible command-line client, enter the following connection string, replacing the
<cluster-endpoint>with the DDL endpoint available from the MemSQL Cloud console, and a
<username>for an account with permissions to load data:
mysql --user=<username> --host=<cluster-endpoint> --port=3306 --password --prompt="memsql> "
Press Enter. You will be prompted for the account’s password.
After providing the account’s password, press Enter. You are now connected to your MemSQL Cloud cluster.
Depending on the contents and formatting of your text file, ensure that you have created a destination table with the appropriate fields for each field in the file. Also take note of the delimiters used to separate each field in the file, such as CSV or TSV, as you may need to specify additional
LOAD DATAclauses such as
FIELDS TERMINATED BY. For more information, including how to handle errors during loading, see the LOAD DATA topic.
Note the filepath of the file you want to load into your table. The file must be stored on the local filesystem. If you want to load a file from a path other than the data directory for the MySQL client, ensure that the MySQL client has permissions to access each directory in the path to the file. Finally, execute the
LOAD DATAcommand, such as the following example:
memsql> LOAD DATA LOCAL INFILE 'my_data.txt' INTO TABLE my_database.my_table;
The data will be loaded into the destination table.