.::: create Tools Migration from MongoDB to MariaDB using bash shell script nocsv :::.

  

1. sample data edb & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

MongoDB
https://teguhth.blogspot.com/2020/12/study-kasus-praktis-belajar-query.html

Install 
yum install jq -y

2. data server A & B 
server A mongodb
ip : 10.10.10.9
db : teguhth
port 27017
user : admin
password : admin

server B Mariadb
ip : 10.10.10.90
db : mariamongo
port 3306
user : admin
password : admin

buat script shell untuk migrasi dari server A mariadb to server B mongodb

3. run script migrasi 

migrasi_barang_mongo_to_mariadb_nocsv.sh
migrasi_customer_mongo_to_mariadb_nocsv.sh
migrasi_pasok_mongo_to_mariadb_nocsv.sh
migrasi_pembelian_mongo_to_mariadb_nocsv.sh
migrasi_suplier_mongo_to_mariadb_nocsv.sh

[root@teguhth-all nocsvmongo]# pwd
/data/migrasi/mongo-to-maria-and-reserve/nocsvmongo
[root@teguhth-all nocsvmongo]#
[root@teguhth-all nocsvmongo]# ls | grep _nocsv
migrasi_barang_mongo_to_mariadb_nocsv.sh
migrasi_customer_mongo_to_mariadb_nocsv.sh
migrasi_pasok_mongo_to_mariadb_nocsv.sh
migrasi_pembelian_mongo_to_mariadb_nocsv.sh
migrasi_suplier_mongo_to_mariadb_nocsv.sh
[root@teguhth-all nocsvmongo]#
 

4. result after migrasi 
 

5. script migrasi table barang 

[root@teguhth-all nocsvmongo]# pwd
/data/migrasi/mongo-to-maria-and-reserve/nocsvmongo
[root@teguhth-all nocsvmongo]#
[root@teguhth-all nocsvmongo]# cat migrasi_barang_mongo_to_mariadb_nocsv.sh
#!/bin/bash

########################################
# CONFIG
########################################

# MongoDB
MONGO_HOST="10.10.10.9"
MONGO_PORT="27017"
MONGO_DB="teguhth"
MONGO_USER="admin"
MONGO_PASS="admin"
MONGO_COLLECTION="barang"

# MariaDB
MARIA_HOST="10.10.10.90"
MARIA_PORT="3306"
MARIA_DB="mariamongo"
MARIA_USER="admin"
MARIA_PASS="admin"
MARIA_TABLE="barang"

########################################
# CREATE TABLE (optional)
########################################

echo "== Prepare MariaDB table =="

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB" <<EOF

CREATE TABLE IF NOT EXISTS $MARIA_TABLE (
    KODE_BARANG CHAR(6) PRIMARY KEY,
    NAMA_BARANG VARCHAR(25),
    SATUAN_BARANG VARCHAR(20),
    STOK_BARANG DECIMAL(4)
);

EOF

########################################
# STREAM INSERT
########################################

echo "== MongoDB → MariaDB INSERT =="

mongoexport \
  --host "$MONGO_HOST" \
  --port "$MONGO_PORT" \
  -u "$MONGO_USER" \
  -p "$MONGO_PASS" \
  --authenticationDatabase admin \
  -d "$MONGO_DB" \
  -c "$MONGO_COLLECTION" \
  --type=json |
jq -r '

"REPLACE INTO '"$MARIA_TABLE"' VALUES (\"" +
.KODE_BARANG + "\",\"" +
.NAMA_BARANG + "\",\"" +
.SATUAN_BARANG + "\"," +
(.STOK_BARANG|tostring) +
");"

' |
mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB"

########################################
# DONE
########################################

echo "✅ Migrasi selesai — tanpa file CSV"

[root@teguhth-all nocsvmongo]#

6. script migrasi table suplier 

[root@teguhth-all nocsvmongo]# pwd
/data/migrasi/mongo-to-maria-and-reserve/nocsvmongo
[root@teguhth-all nocsvmongo]#
[root@teguhth-all nocsvmongo]# cat migrasi_suplier_mongo_to_mariadb_nocsv.sh
#!/bin/bash

########################################
# CONFIG
########################################

# MongoDB
MONGO_HOST="10.10.10.9"
MONGO_PORT="27017"
MONGO_DB="teguhth"
MONGO_USER="admin"
MONGO_PASS="admin"
MONGO_COLLECTION="suplier"

# MariaDB
MARIA_HOST="10.10.10.90"
MARIA_PORT="3306"
MARIA_DB="mariamongo"
MARIA_USER="admin"
MARIA_PASS="admin"
MARIA_TABLE="suplier"

########################################
# CREATE TABLE
########################################

echo "== Prepare MariaDB table suplier =="

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB" <<EOF

CREATE TABLE IF NOT EXISTS $MARIA_TABLE (
  KODE_SUPLIER CHAR(5) NOT NULL,
  NAMA_SUPLIER VARCHAR(30),
  ALAMAT_SUPLIER VARCHAR(30),
  KOTA_SUPLIER VARCHAR(15),
  TELEPON_SUPLIER VARCHAR(15),
  PRIMARY KEY (KODE_SUPLIER)
);

EOF

########################################
# STREAM INSERT
########################################

echo "== MongoDB → MariaDB INSERT suplier =="

mongoexport \
  --host "$MONGO_HOST" \
  --port "$MONGO_PORT" \
  -u "$MONGO_USER" \
  -p "$MONGO_PASS" \
  --authenticationDatabase admin \
  -d "$MONGO_DB" \
  -c "$MONGO_COLLECTION" \
  --type=json |
jq -r '

"REPLACE INTO '"$MARIA_TABLE"' VALUES (\"" +
.KODE_SUPLIER + "\",\"" +
.NAMA_SUPLIER + "\",\"" +
.ALAMAT_SUPLIER + "\",\"" +
.KOTA_SUPLIER + "\",\"" +
.TELEPON_SUPLIER + "\");"

' |
mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB"

########################################
# DONE
########################################

echo "✅ Migrasi suplier selesai — tanpa CSV"

[root@teguhth-all nocsvmongo]#

7. script migrasi table customer 

[root@teguhth-all nocsvmongo]# pwd
/data/migrasi/mongo-to-maria-and-reserve/nocsvmongo
[root@teguhth-all nocsvmongo]#
[root@teguhth-all nocsvmongo]# cat migrasi_customer_mongo_to_mariadb_nocsv.sh
#!/bin/bash

########################################
# CONFIG
########################################

# MongoDB
MONGO_HOST="10.10.10.9"
MONGO_PORT="27017"
MONGO_DB="teguhth"
MONGO_USER="admin"
MONGO_PASS="admin"
MONGO_COLLECTION="customer"

# MariaDB
MARIA_HOST="10.10.10.90"
MARIA_PORT="3306"
MARIA_DB="mariamongo"
MARIA_USER="admin"
MARIA_PASS="admin"
MARIA_TABLE="customer"

########################################
# CREATE TABLE
########################################

echo "== Prepare MariaDB table customer =="

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB" <<EOF

CREATE TABLE IF NOT EXISTS $MARIA_TABLE (
  KODE_CUSTOMER CHAR(6),
  NAMA_CUSTOMER VARCHAR(30),
  ALAMAT_CUSTOMER VARCHAR(30),
  KOTA_CUSTOMER VARCHAR(15),
  TELEPON_CUSTOMER VARCHAR(15),
  PRIMARY KEY (KODE_CUSTOMER)
);

EOF

########################################
# STREAM INSERT
########################################

echo "== MongoDB → MariaDB INSERT customer =="

mongoexport \
  --host "$MONGO_HOST" \
  --port "$MONGO_PORT" \
  -u "$MONGO_USER" \
  -p "$MONGO_PASS" \
  --authenticationDatabase admin \
  -d "$MONGO_DB" \
  -c "$MONGO_COLLECTION" \
  --type=json |
jq -r '

"REPLACE INTO '"$MARIA_TABLE"' VALUES (\"" +
.KODE_CUSTOMER + "\",\"" +
.NAMA_CUSTOMER + "\",\"" +
.ALAMAT_CUSTOMER + "\",\"" +
.KOTA_CUSTOMER + "\",\"" +
.TELEPON_CUSTOMER + "\");"

' |
mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB"

########################################
# DONE
########################################

echo "✅ Migrasi customer selesai — tanpa CSV"

[root@teguhth-all nocsvmongo]#

8. script migrasi table pasok 

[root@teguhth-all nocsvmongo]# pwd
/data/migrasi/mongo-to-maria-and-reserve/nocsvmongo
[root@teguhth-all nocsvmongo]#
[root@teguhth-all nocsvmongo]# cat migrasi_pasok_mongo_to_mariadb_nocsv.sh
#!/bin/bash

########################################
# CONFIG
########################################

# MongoDB
MONGO_HOST="10.10.10.9"
MONGO_PORT="27017"
MONGO_DB="teguhth"
MONGO_USER="admin"
MONGO_PASS="admin"
MONGO_COLLECTION="pasok"

# MariaDB
MARIA_HOST="10.10.10.90"
MARIA_PORT="3306"
MARIA_DB="mariamongo"
MARIA_USER="admin"
MARIA_PASS="admin"
MARIA_TABLE="pasok"

########################################
# CREATE TABLE
########################################

echo "== Prepare MariaDB table pasok =="

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB" <<EOF

CREATE TABLE IF NOT EXISTS $MARIA_TABLE (
  KODE_PASOK CHAR(10),
  KODE_BARANG CHAR(6),
  KODE_SUPLIER CHAR(5),
  TANGGAL_PASOK DATE,
  JUMLAH_PASOK DECIMAL(4),
  PRIMARY KEY (KODE_PASOK, KODE_BARANG, KODE_SUPLIER),
  FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
  FOREIGN KEY (KODE_SUPLIER) REFERENCES suplier(KODE_SUPLIER)
);

EOF

########################################
# STREAM INSERT WITH DATE FIX
########################################

echo "== MongoDB → MariaDB INSERT pasok =="

mongoexport \
  --host "$MONGO_HOST" \
  --port "$MONGO_PORT" \
  -u "$MONGO_USER" \
  -p "$MONGO_PASS" \
  --authenticationDatabase admin \
  -d "$MONGO_DB" \
  -c "$MONGO_COLLECTION" \
  --type=json |

jq -r '

def month:
  ascii_downcase |
  {jan:"01",feb:"02",mar:"03",apr:"04",may:"05",jun:"06",
   jul:"07",aug:"08",sep:"09",oct:"10",nov:"11",dec:"12"}[.];

def pad2:
  tostring | if length==1 then "0"+. else . end;

def fixdate:
  capture("(?<d>\\d+)-(?<m>[A-Za-z]+)-(?<y>\\d+)") as $x
  | "20\($x.y)-\($x.m|month)-\($x.d|pad2)";

"REPLACE INTO '"$MARIA_TABLE"' VALUES (\"" +
.KODE_PASOK + "\",\"" +
.KODE_BARANG + "\",\"" +
.KODE_SUPLIER + "\",\"" +
(.TANGGAL_PASOK|fixdate) + "\"," +
(.JUMLAH_PASOK|tostring) +
");"

' |

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB"

########################################
# DONE
########################################

echo "✅ Migrasi pasok selesai — tanggal sudah dikonversi"

[root@teguhth-all nocsvmongo]#

9. script migrasi table pembelian 

[root@teguhth-all nocsvmongo]# pwd
/data/migrasi/mongo-to-maria-and-reserve/nocsvmongo
[root@teguhth-all nocsvmongo]#
[root@teguhth-all nocsvmongo]# cat migrasi_pembelian_mongo_to_mariadb_nocsv.sh
#!/bin/bash

########################################
# CONFIG
########################################

# MongoDB
MONGO_HOST="10.10.10.9"
MONGO_PORT="27017"
MONGO_DB="teguhth"
MONGO_USER="admin"
MONGO_PASS="admin"
MONGO_COLLECTION="pembelian"

# MariaDB
MARIA_HOST="10.10.10.90"
MARIA_PORT="3306"
MARIA_DB="mariamongo"
MARIA_USER="admin"
MARIA_PASS="admin"
MARIA_TABLE="pembelian"

########################################
# CREATE TABLE
########################################

echo "== Prepare MariaDB table pembelian =="

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB" <<EOF

CREATE TABLE IF NOT EXISTS $MARIA_TABLE (
  KODE_PEMBELIAN CHAR(10),
  KODE_BARANG CHAR(6),
  KODE_CUSTOMER CHAR(6),
  TANGGAL_PEMBELIAN DATE,
  JUMLAH_PEMBELIAN DECIMAL(4),
  PRIMARY KEY (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER),
  FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
  FOREIGN KEY (KODE_CUSTOMER) REFERENCES customer(KODE_CUSTOMER)
);

EOF

########################################
# STREAM INSERT + DATE FIX
########################################

echo "== MongoDB → MariaDB INSERT pembelian =="

mongoexport \
  --host "$MONGO_HOST" \
  --port "$MONGO_PORT" \
  -u "$MONGO_USER" \
  -p "$MONGO_PASS" \
  --authenticationDatabase admin \
  -d "$MONGO_DB" \
  -c "$MONGO_COLLECTION" \
  --type=json |

jq -r '

def month:
  ascii_downcase |
  {jan:"01",feb:"02",mar:"03",apr:"04",may:"05",jun:"06",
   jul:"07",aug:"08",sep:"09",oct:"10",nov:"11",dec:"12"}[.];

def pad2:
  tostring | if length==1 then "0"+. else . end;

def fixdate:
  capture("(?<d>\\d+)-(?<m>[A-Za-z]+)-(?<y>\\d+)") as $x
  | "20\($x.y)-\($x.m|month)-\($x.d|pad2)";

"REPLACE INTO '"$MARIA_TABLE"' VALUES (\"" +
.KODE_PEMBELIAN + "\",\"" +
.KODE_BARANG + "\",\"" +
.KODE_CUSTOMER + "\",\"" +
(.TANGGAL_PEMBELIAN|fixdate) + "\"," +
(.JUMLAH_PEMBELIAN|tostring) +
");"

' |

mariadb -h "$MARIA_HOST" -P "$MARIA_PORT" \
-u "$MARIA_USER" -p"$MARIA_PASS" "$MARIA_DB"

########################################
# DONE
########################################

echo "✅ Migrasi pembelian selesai — tanggal sudah dikonversi"

[root@teguhth-all nocsvmongo]#