Mysql CONCAT error when loading data

Question

I am developing a sql script that loads a .csv file stored in an S3 bucket into an RDS DB. The LOAD command works perfectly when not using CONCAT. I am using CONCAT to include variables with the statement. I believe the issue is with one or more of the clauses. I significantly shorted the fields for troubleshooting purposes. I have tried several different things, but can't seem to get the correct syntax.

set @s3intotemp = CONCAT('
LOAD DATA FROM S3 PREFIX "`',@workingdir, '`"
INTO TABLE `',@rndtable, '` 
FIELDS TERMINATED BY ","
ESCAPED BY ""
ENCLOSED BY "\""
LINES TERMINATED BY "\n"
(@col1,
@col2) 
SET customer=@col1,
firstname=@col2;
');
PREPARE stmt from @s3intotemp;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I am getting the following error:

SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" (@col1, @col2 ) SET customer=@col1, firstname=@col2' at line 6

Barmar · Accepted Answer · 2018-07-20 16:06:38Z

1

You need to escape the backslashes in the string, so they'll make it into the result of the concatenation.

set @s3intotemp = CONCAT('
LOAD DATA FROM S3 PREFIX "`',@workingdir, '`"
INTO TABLE `',@rndtable, '` 
FIELDS TERMINATED BY ","
ESCAPED BY ""
ENCLOSED BY "\\\""
LINES TERMINATED BY "\\n"
(@col1,
@col2) 
SET customer=@col1,
firstname=@col2;
');

answered Jul 20, 2018 at 16:06

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2917347 Over a year ago

That is producing another error. SQL Error: This command is not supported in the prepared statement protocol yet. I thought maybe the issue might be the statement is trying to interpret the at sign in col1 and col2 as a variable. I tried escaping \@col1 and \@col2, but that is not working either.

Barmar Over a year ago

I think that means you can't use LOAD DATA FROM S3 with a prepared statement.

user2917347 Over a year ago

I believe you are correct regarding the prepared statement. Is the \\\ correct in the ENCLOSED BY or should it just be \\

Collectives™ on Stack Overflow

Mysql CONCAT error when loading data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related