load csv file from s3 to db

LOAD DATA
FROM
S3 ‘s3://xxxx/load/vop_cnv_mst.csv’
INTO TABLE INSVJSK_CMN.VOP_CNV_MST FIELDS TERMINATED BY
‘,’ ENCLOSED BY
‘"’ LINES TERMINATED BY
‘\r\n’

mysql -h xxxx.cluster-crfum7txw65z.ap-northeast-1.rds.amazonaws.com insvjsk_maker -u user -ppassword

LOAD DATA INFILE ‘/home/ec2-user/insert.csv’ INTO TABLE rev_data FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\r\n’
LOAD DATA INFILE ‘/home/insert.csv’ INTO TABLE rev_data FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\r\n’

LOAD DATA
FROM
S3 ‘s3://xxxx/load/1000000.csv’
INTO TABLE insvjsk_maker.slip_data_lst FIELDS TERMINATED BY
‘,’ ENCLOSED BY
‘"’ LINES TERMINATED BY
‘\r’

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch
-e “select * from yourtable”
| sed ‘s/\t/","/g;s/^/"/;s/$/"/;s/\n//g’ > yourlocalfilename
User fpalero provides an alternative and supposedly simpler approach, if you know and specify the fields upfront:

mysql -uroot -ppassword --database=dbtest
-e “select concat(field1,’,’,field2,’,’,field3) FROM tabletest” > tabletest.csv

mysql -h xxxx.cluster-crfum7txw65z.ap-northeast-1.rds.amazonaws.com insvjsk_maker -u vjsk_admin_prod -pvjsk_admin_prod
–batch -e “select * from insvjsk_cmn.vop_hco_view” | sed ‘s/\t/","/g;s/^/"/;s/$/"/;s/\n//g’ > vop_hco_view.csv

SELECT * FROM users
INTO OUTFILE ‘/tmp/users.csv’
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘"’;