Unknown system variable using SET parameter in LOAD DATA FROM S3 expression

Question

I'm trying to fill Aurora MySQL DB from CSV in an S3 bucket using the manual [Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html).

In general, everything works fine, but when I’m using **SET** to modify data from CSV, I can’t do it, looks like SET parameter doesn’t work at all.

Running this query

``` SQL
LOAD DATA FROM S3 's3://mybucket/source_data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '
'
IGNORE 1 LINES
(order_id, @order_name)
SET  order_name = UPPER(@order_name);
```
I get `Unknown system variable 'order_name'` error.

Moreover there are strange syntax issues highlighted by my IntelliJ IDEA, on the other hand you can see that **SET** is fine with the `LOAD DATA INFILE`

![Enter image description here](/media/postImages/original/IMVNFV9CxYQ06-lX5oXKniFA)

Can I use SET as it described in the manual, so what am I doing wrong with it?

Answer

I would like to inform you, that you are using the order_name as the user variable, which can be the reason for this error.

LOAD DATA FROM S3 's3://mybucket/source_data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '
'
IGNORE 1 LINES
(order_id, @order_name)
SET  order_name = UPPER(@order_name);

As per the AWS documentation [+]:

col_name_or_user_var, ... – Specifies a comma-separated list of one or more column names or user variables that identify which columns to load by name. The name of a user variable used for this purpose must match the name of an element from the text file, prefixed with @. You can employ user variables to store the corresponding field values for subsequent reuse.
For example, the following statement loads the first column from the input file into the first column of table1, and sets the value of the table_column2 column in table1 to the input value of the second column divided by 100.

LOAD DATA FROM S3 's3://mybucket/data.txt'
    INTO TABLE table1
    (column1, @var1)
    SET table_column2 = @var1/100;

SET – Specifies a comma-separated list of assignment operations that set the values of columns in the table to values not included in the input file.
For example, the following statement sets the first two columns of table1 to the values in the first two columns from the input file, and then sets the value of the column3 in table1 to the current time stamp.

LOAD DATA FROM S3  's3://mybucket/data.txt'
    INTO TABLE table1
    (column1, column2)
    SET column3 = CURRENT_TIMESTAMP;

Hence, with the LOAD DATA INFILE you are using the columns, as I do not have access to your data, I will request you to check the above syntax and accordingly use the prefix “@”, with order_name. And in case you are still facing issue, I will suggest you to open a AWS Support case for appropriate troubleshooting.

[+] Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket - Parameters - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html#AuroraMySQL.Integrating.LoadFromS3.Text.Parameters

Unknown system variable using SET parameter in LOAD DATA FROM S3 expression

Relevant content