Loading multiple CSV files into mariadb table using LOAD DATA INFILE and shell script

Question

I'm attempting to load CSV data into a mariadb table. MariaDB is running in a docker container and the CSV files have already been copied in the container as well. I'm already able to successfully load the data using LOAD DATA INFILE statement, in DBeaver, as follows:

LOAD DATA INFILE '/journey/2021-07.csv' 
INTO TABLE journey
CHARACTER SET utf8 
FIELDS
  TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 ROWS 
 (departure, `return`,departure_station_id,departure_station_name,return_station_id,return_station_name,@distance_covered,duration)
 SET distance_covered = IF(@distance_covered='' OR @distance_covered='\r' OR @distance_covered='\n' OR @distance_covered='\r\n' ,NULL,@distance_covered)
  ;

There are three CSV files that I need to load, and I'd like to automate that process using a shell script. I massaged up the following script for the purpose:

#!/bin/sh
for f in /journey/*.csv
do
    mysql -e "use client" -e "LOAD DATA INFILE '"$f"'
    INTO TABLE journey
    CHARACTER SET utf8
      FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        IGNORE 1 ROWS
        (departure, `return`,departure_station_id,departure_station_name,return_station_id,return_station_name,@distance_covered,duration)
         SET distance_covered = IF(@distance_covered='' OR @distance_covered='\r' OR @distance_covered='\n' OR @distance_covered='\r\n' ,NULL,@distance_covered)"
echo "Done: '"$f"' at $(date)"
done

But,when I run the script like docker exec -i citybike-mariadb sh < config/mariadb-load-data.sh, I get the following error:

docker exec -i citybike-mariadb sh < config/mariadb-load-data.sh
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO TABLE journey
    CHARACTER SET utf8
      FIELDS
        TERMINATED BY ...' at line 1
Done: '/journey/2021-05.csv' at Sun May 21 12:00:11 EEST 2023
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO TABLE journey
    CHARACTER SET utf8
      FIELDS
        TERMINATED BY ...' at line 1
Done: '/journey/2021-06.csv' at Sun May 21 12:00:11 EEST 2023
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INTO TABLE journey
    CHARACTER SET utf8
      FIELDS
        TERMINATED BY ...' at line 1
Done: '/journey/2021-07.csv' at Sun May 21 12:00:11 EEST 2023

Can somebody please help me with this and point me towards the right direction?

EDIT:

I got it to work with the following modification in the script:

#!/bin/sh
for f in /journey/*.csv
do
    mysql -e "use client" -e "
    LOAD DATA INFILE '$f'
    INTO TABLE journey
    CHARACTER SET utf8
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS"
echo "Done: '"$f"' at $(date)"
done

But addition of

    (departure,`return`,departure_station_id,departure_station_name,return_station_id,return_station_name,@distance_covered,duration)
SET distance_covered = IF(@distance_covered='' OR @distance_covered='\r' OR @distance_covered='\n' OR @distance_covered='\r\n' ,NULL,@distance_covered)

gives me the following error:

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'return,departure_station_id,departure_station_name,return_station_id,return_s...' at line 8
Done: '/journey/2021-05.csv' at Sun May 21 13:18:55 EEST 2023
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'return,departure_station_id,departure_station_name,return_station_id,return_s...' at line 8
Done: '/journey/2021-06.csv' at Sun May 21 13:18:55 EEST 2023
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'return,departure_station_id,departure_station_name,return_station_id,return_s...' at line 8
Done: '/journey/2021-07.csv' at Sun May 21 13:18:55 EEST 2023

Answer 1

It's really messy to have more than two levels of quotes in a shell script. I suggest you avoid one level by doing

mysql ... <<ZZZ
    LOAD DATA ...
    ...
ZZZ

Note: the final ZZZ (or whatever string) must be in column 1, not indented. Without quoting the first ZZZ, your $f will be substituted. (Fortunately, your SQL does not need any dollar signs.)

(ref: "here doc")

Answer 2

Note shell scripts, ending in .sh, can be included in /docker-entrypoint-initdb.d. You can leave the .csv files in the same path or a relative path below.

If you leave off the execute permissions on the script, the script it sourced in the same context as the entrypoint and can access its functions. The functions beginning with docker_* are stable interface ones. Following same heredocs as Rick James' answer for minimal escaping.

This obviously assumes csv files in a directory below the script.

2_load_data.sh:

for f in journey/*.csv
do
   mysql_note "Starting $f"
   docker_process_sql <<-EOSQL
   LOAD DATA ....

   EOSQL
   mysql_note "Completed $f"
done

By default, this will load the data into the MARIADB_DATABASE. The order is processed alphabetically, so to create the journal table with an alphabetical earlier SQL file like 1_create_table_journal.sql containing the SQL create table for journal.