How to update and insert 100k CSV lines into the 10M records MS SQL Server database table by a cron job in a few seconds?

 How to update and insert 100k CSV lines into the 10M records MS SQL Server database table by a cron job in a few seconds?

Context

In my application, there is a CUSTOMER table containing more than 10 million records. And daily, another application will export CRM data to a CSV file and upload it to an S3 bucket. This CSV file usually contains about 30 thousand lines. In there, more than 1 thousand lines are new customers and the remaining are existing customers in the CUSTOMER table need to be updated their information. 1 cron job reads the CSV file and updates/inserts it to the CUSTOMER table. The issue here is if we update each of nearly 30k existing records into the CUSTOMER table with 10M records existing in there because it will take a few hours to complete. After googling for some solutions, I got a suggestion with the concept INSERT ON DUPLICATE KEY UPDATE. But this solution is usually only used in MySQL or PostgreSQL and not supported by MS SQL Server, instead of this, MS SQL Server has another solution to resolve this issue, which is the MERGE concept. Then, I have applied to my application then I take notes in a short demo here to reuse later and share someone finding solutions to resolve similar issues.

Idea

1. Just insert all CSV lines to a temporary table `CUSTOMER_TEMP` by using the MyBatis batch insert.
2. Call a SQL script by MyBatis ScriptRunner to merge data from the `CUSTOMER_TEMP` to the `CUSTOMER` table by the internal database engine.

In the real project, I'm using spring-boot-start-batch, but for demo purposes, I only write a short Java class to read the CSV file customers_100k_lines.csv from the current project folder instead of the S3 bucket, then batch insert to CUSTOMER_TEMP and call ScriptRunner to MERGE the data to the CUSTOMER table. The CSV file contains 50k lines are existing records in the CUSTOMER table should be updated and 500k are new ones, should be inserted. I'm also prepared 2 SQL scripts to create the CUSTOMER_TEMP and CUSTOMER table with 10M records existing. If you want to run this source code, please correct the database information in the application.yml file, then you can check the total time spent, and the time spent for batch insert and data merging in log files. Hope this is helpful for someone.

Logs:

Total records in CUSTOMER table before: 10000000
Total CSV lines: 100000. Total inserted to CUSTOMER_TEMP table: 100000 by 10 batchs. Time spent: 15589 (ms).
Time spent to merge data from CUSTOMER_TEMP to CUSTOMER table: 1325 (ms).
Total records in CUSTOMER table after: 10100000

Link to GitHub source code: https://github.com/koacervate/update-100k-csv-lines-to-10M-records-database-table 

References:

https://ichihedge.wordpress.com/2020/01/12/mybatis-save-or-update/

https://www.sqlshack.com/understanding-the-sql-merge-statement/

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

Your container's CPU usage is more than 100%, "kdevtmpfsi" the cryptomining malware is running | So how to resolve

How to resolve when "kdevtmpfsi" the crypto-mining malware is running and taking all CPU load of your server (container).

One day your server (container) running with CPU load so high, more than 100% then you check the processes with "top" command and short by CPU load with shortcut keys "Shift + p", it looks like below:

So, I'm sorry your server is infected the crypto-mining malware that named "kdevtmpfsi", similar "kdevtmpfs" a system Linux process.

I will list some links about the malware, the cause, and it's propagating mechanism end of this post.

Now I go to the detail steps to resolve this issue in my server,  I'm using Amazon Linux 2 AMI.

Step 1: As you can see in the image above, the process is running by the root user, so I need to check the root's cronjob.

As you can see, this cronjob will download script unk.sh from the hacker's server 195.3.146.118 and execute it in your server. So you need to remove the cronjob by the following command.

sudo crontab -u root -r

You should re-check to make sure the cronjob is already removed.

Step 2: Find path to 2 scripts "kdevtmpfsi" and "kinsing" with commands:

sudo find / -name kdevtmpfsi

sudo find / -name kinsing

You will get the results like the paths: "/tmp/kdevtmpfsi" and "/var/tmp/kinsing", so please remove all access permissions to the files.

sudo chmod 000 /tmp/kdevtmpfsi

sudo chmod 000 /var/tmp/kinsing

Note: remove access permissions to the files, not remove the files because they can self-propagate again via "kinsing" daemon.

Step 3: Kill process "kdevtmpfsi" first, you can check the process id from the "top" command above.

sudo kill -9 14867

Step 4: Now check with "top" command you can see process "kinsing" is running, its trying to re-initialize "kdevtmpfsi" process, check it's process id or with command "sudo netstat -lnp | grep kinsing" then kill it.

sudo kill -9 32222

Note: if you not yet kill process "kdevtmpfsi" you can not see process "kinsing".

Step 5: Come back with "top" command and make sure the both processes are not running anymore. If they still running, so kill again with order: "kdevtmpfsi" first, then "kinsing" second, and wait for moments about 5 - 10 minutes to make sure they have not come back. Ok, now you can delete for both scripts "/tmp/kdevtmpfsi" and "/var/tmp/kinsing".

sudo rm -rf  /tmp/kdevtmpfsi

sudo rm -rf /var/tmp/kinsing

Ok, with these steps I already resolve the issue with "kdevtmpfsi" the cryptomining malware running in my server, Amazon Linux 2 AMI. Hope that these steps helpful for you.

Here are links about malware "kdevtmpfsi": the cause, and it's propagating mechanism:

https://blog.aquasec.com/threat-alert-kinsing-malware-container-vulnerability

https://cnasolution.com/questions/96651/perfect-solution-for-kdevtmpfsi-mining-virus-and-his-daemon-kinsing-100-cpu-tmp-kdevtmpfsi-var-tmp-kinsing

QuickStart: The fastest and easiest way to run an Local SMTP Server for testing or development.

Run Local SMTP Server on your development environment.

Your code needs to run the test cases with send email from SMTP server but you have no SMTP Mail Server on your local and you get the error below:

> :bootRun

org.springframework.mail.MailSendException: Mail server connection failed; nested exception is com.sun.mail.util.MailConnectException: Couldn't connect to host, port: localhost, 25; timeout -1;

  nested exception is:

        java.net.ConnectException: Connection refused (Connection refused). Failed messages: com.sun.mail.util.MailConnectException: Couldn't connect to host, port: localhost, 25; timeout -1;

  nested exception is:

        java.net.ConnectException: Connection refused (Connection refused)

        at org.springframework.mail.javamail.JavaMailSenderImpl.doSend(JavaMailSenderImpl.java:448)

        at org.springframework.mail.javamail.JavaMailSenderImpl.send(JavaMailSenderImpl.java:361)

        at org.springframework.mail.javamail.JavaMailSenderImpl.send(JavaMailSenderImpl.java:356)

Option 1: Via Docker

- Install Docker If you not yet.
- Register a Docker ID https://hub.docker.com/ if you don't have.
- Login Docker Hub.

- Skip this step if Docker already installed on your Mac/PC and you already logged in Docker Hub.

Pull and run djfarrelly/maildev Image from Docker Hub.

- Search for "djfarrelly/maildev" in Docker Hub and Checkout the document for more details.

- Run the command below to starting a Local SMTP Mail Server Instance as a Docker container named "MailDev" and mapping port 25(SMTP) and 80(Web) from the container to port 25 and 1080 on your Docker host (your PC/Mac). This will take you a few minutes to download the image and Starting the container.

docker run -d -it --name MailDev -p 1080:80 -p 25:25  djfarrelly/maildev

- Now re-run your test cases again:

2019-11-23 23:26:43.499 DEBUG 7573 --- [sbrgprjs-task-1] me.koacervate.aop.logging.LoggingAspect  : Enter: me.koacervate.service.MailService.sendCreationEmail() with argument[s] = [User{login='koacervate', firstName='Khue', lastName='Duke', email='koacervate@googlemail.com', imageUrl='null', activated='true', langKey='en', activationKey='null'}]

2019-11-23 23:26:43.500 DEBUG 7573 --- [sbrgprjs-task-1] me.koacervate.service.MailService        : Sending creation email to 'koacervate@googlemail.com'

2019-11-23 23:26:43.526 DEBUG 7573 --- [sbrgprjs-task-1] me.koacervate.service.MailService        : Send email[multipart 'false' and html 'true'] to 'koacervate@googlemail.com' with subject 'jsbrgprjs account activation' and content=<!DOCTYPE html>

<html lang="en">

    <head>

        <title>jsbrgprjs account activation</title>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

        <link rel="shortcut icon" href="http://127.0.0.1:8080/favicon.ico" />

    </head>

    <body>

        <p>Dear koacervate</p>

        <p>Your jsbrgprjs account has been created, please click on the URL below to access it:</p>

        <p>

            <a href="http://127.0.0.1:8080/account/reset/finish?key=RCTVtLkilrCdawcCDYKf">http://127.0.0.1:8080/account/reset/finish?key=RCTVtLkilrCdawcCDYKf</a>

        </p>

        <p>

            <span>Regards,</span>

            <br/>

            <em>jsbrgprjs Team.</em>

        </p>

    </body>

</html>

2019-11-23 23:26:44.073 DEBUG 7573 --- [sbrgprjs-task-1] me.koacervate.service.MailService        : Sent email to User 'koacervate@googlemail.com'

2019-11-23 23:26:44.073 DEBUG 7573 --- [sbrgprjs-task-1] me.koacervate.aop.logging.LoggingAspect  : Exit: me.koacervate.service.MailService.sendCreationEmail() with result = null

- You can see the sent email in Web UI http://localhost:1080/#/email/JevGMzCd

Option 2: Via install maildev node package as below:

$ npm install -g maildev
$ maildev

with options as below:

Usage

maildev [options]

  -h, --help                      output usage information
  -V, --version                   output the version number
  -s, --smtp <port>               SMTP port to catch emails [1025]
  -w, --web <port>                Port to run the Web GUI [1080]
  --ip <ip address>               IP Address to bind SMTP service to
  --outgoing-host <host>          SMTP host for outgoing emails
  --outgoing-port <port>          SMTP port for outgoing emails
  --outgoing-user <user>          SMTP user for outgoing emails
  --outgoing-pass <password>      SMTP password for outgoing emails
  --outgoing-secure               Use SMTP SSL for outgoing emails
  --auto-relay [email]            Use auto-relay mode. Optional relay email address
  --auto-relay-rules <file>       Filter rules for auto relay mode
  --incoming-user <user>          SMTP user for incoming emails
  --incoming-pass <pass>          SMTP password for incoming emails
  --web-ip <ip address>           IP Address to bind HTTP service to, defaults to --ip
  --web-user <user>               HTTP user for GUI
  --web-pass <password>           HTTP password for GUI
  --base-pathname <path>          base path for URLs
  --disable-web                   Disable the use of the web interface. Useful for unit testing
  --hide-extensions <extensions>  Comma separated list of SMTP extensions to NOT advertise
                                  (STARTTLS, SMTPUTF8, PIPELINING, 8BITMIME)
  -o, --open                      Open the Web GUI after startup
  -v, --verbose
  --silent

Please refer to documents at https://hub.docker.com/r/djfarrelly/maildev/ for more details.

Option 3: Integrate via API or SMTP with https://sendgrid.com/

You can register a free account for testing and choose to integrate via API or SMTP then follow the instructions in the website you will have all necessary information to send emails with your code.

  mail:
    host: smtp.sendgrid.net
    port: 465
    username: apikey
    password: SG.t2L6qpv3TjmvgcasoksVbg.DRsZrTwq3LmApU1uaQ6mjv-koafl0NaSzogCW6X9***
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true

References:
- https://docs.docker.com
- https://hub.docker.com/r/djfarrelly/maildev/
- https://github.com/maildev/maildev

QuickStart: The fastest and easiest way to run an Instance of Oracle Database on Mac OS.