Import CSV from local

SingleStore Studio
#1

Hi, I am new to memsql and trying to import local csv from Windows computer using memsql studio.

My setup:

  1. Spun up a test cluster on AWS with 1 master node and 1 leaf node using the default cloudformation template.
  2. Have a local CSV on my windows computer and tried importing to a table on the cluster.

Tried the following and getting file not found error for csv(which kind of make sense because it is a local file).
LOAD DATA INFILE ‘C:/temp/brentwood_p01_nogeom.csv’
INTO TABLE brentwood_p01_nogeom
COLUMNS TERMINATED BY ‘,’;

LOAD DATA INFILE ‘C:\temp\brentwood_p01_nogeom.csv’
INTO TABLE brentwood_p01_nogeom
COLUMNS TERMINATED BY ‘,’;

  1. Tried this and getting a message that LOCAL file is not supported on memsql studio.
    LOAD DATA LOCAL INFILE ‘C:/temp/brentwood_p01_nogeom.csv’
    INTO TABLE brentwood_p01_nogeom
    COLUMNS TERMINATED BY ‘,’;

  2. Copied the file onto the master node and tried the following command. Still getting file not found error.

LOAD DATA INFILE ‘/tmp/brentwood_p01_nogeom.csv’
INTO TABLE brentwood_p01_nogeom
COLUMNS TERMINATED BY ‘,’;

I am missing something very trivial. Any suggestions to make it work?

Thanks!

#2

Hello @rtpsr,

MemSQL Studio cannot read from local files since it runs inside a browser and browsers don’t have access to the underlying filesystem.

For now, you’ll have to figure out an alternative such as loading from S3, or using a mysql CLI to load the file. We’re working on finding a way that Studio can be used for file upload as well.

#3

Thanks @david. I went with S3 option for testing.

#4

Hi rtpsr,
I am new to MemSQL and i have only MemSQL studio env . and no other memsql client or external database . i have access to aws S3 .
one question , if i want to load certain s3 files from aws s3 to MemSQL env . do we need to create pipeline , if yes where do i need to create this pipeline
and is there any privileges need to provide through Mem sql editor.

Regards,

Mangesh

#5

Hi,

Yes, you’ll need to create an S3 pipeline to load the data (https://docs.singlestore.com/v7.3/guides/use-memsql/load-data/s3/s3-pipelines-quickstart/). We don’t support LOAD DATA directly from S3 to do one time loads. pipelines have their own privileges (CREATE PIPELINE, DROP PIPELINE etc.) that the users your logged in with will need to create the pipeline. We are also working on improved UI support for this in studio (or portal if your using our managed service), so you will be able your data load via the UI shortly.

-Adam