DEV Community: Max Borysov

How to migrate data between AWS RDS and S3

Max Borysov — Sun, 11 Apr 2021 14:05:11 +0000

If you are using AWS Aurora MySQL/Postgres or AWS Redshift you have access to S3. Therefore, it is possible to export data from RDS to S3. And you can import data back from S3 to RDS.

How to export data from RDS to S3 file:

SELECT *
FROM users
INTO OUTFILE S3 's3://some-bucket-name/users';

This command only exports data, even without column names. No indexes or other information is present. Only data.

Import data from S3 to RDS:

LOAD DATA FROM S3 PREFIX 's3://some-bucket-name/users'
INTO TABLE users;

For me, they are one of the most frequently used features in day-to-day tasks, like:

export data from RDS to S3. Import S3 file to Google Sheets to prepare some reports, build charts and share with clients or teammates
having data in CVS/TSV format(from a client or any other source) as a file on S3, import file to RDS, perform some operations/aggregations/etc and export data to S3 as a new report

I strongly recommend checking this feature.

Useful links:

Official tutorial

How to manage different AWS profiles

Max Borysov — Sun, 11 Apr 2021 13:46:28 +0000

If you are using AWS tools, you probably know about IAM(Identity and Access Management). Generally, it makes it possible to separate uses/roles to manage different services.

The best practice: do not use the root account for daily tasks. Create different IAM roles with limited access instead.

For instance, you have three projects. Therefore, the best way would be to create three users: user1, user2, user3.

user1 can only manage S3
user2 can manage EC2 instances and Load Balancer
user3 can manage RDS

And you want to execute API calls to perform some actions.
Here is my favorite approach for that:

create ~/.aws/credentials file
add sections for each user/profile

[user1]
aws_access_key_id = xxx
aws_secret_access_key = xxx
[user2]
aws_access_key_id = xxx
aws_secret_access_key = xxx
[user3]
aws_access_key_id = xxx
aws_secret_access_key = xxx

run command with --profile param

aws rds create-db-instance --profile user1 ...

Run these commands to see the configurations:

aws configure list
aws configure list --profile user1
cat ~/.aws/credentials

You can also check these super helpful official articles.

How to use MySQL/Postgres with Docker

Max Borysov — Sat, 10 Apr 2021 21:46:34 +0000

Here are different ways you can use mysql with docker.

Start mysql-server in docker with a mounted volume

Let's say you are in a /home directory. Create docker-volumes/ folder and run the following command:

docker run \
       --name db-mysql \
       -e MYSQL_ROOT_PASSWORD=root \
       -v $(pwd)/docker-volumes/mysql:/var/lib/mysql \
       -d \
       -p 3306:3306 \
       mysql

Make sure the container is running:

docker ps

If all good you'll see smth like that:

CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                               NAMES
72aea1d629a6   mysql     "docker-entrypoint.s…"   2 seconds ago   Up 2 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   db-mysql

If there are any issues(for instance, STATUS: Exited), check the logs:

docker logs container-id

Or you can get container-id dynamically(where db-mysql is a name we gave to our container):

docker logs $(docker ps | grep db-mysql | awk '{ print $1 }')

This way you can check if docker volumes were created.

tree docker-volumes/mysql

Why do we need volumes? If you create a database, tables in a running container, and then you stop it, all your data will be lost.
It is completely ok you did it for demo or testing purposes.
In all other cases, use volumes to persist your data.

Connect to mysql-server using local mysql-client

mysql -u root -p -h 0.0.0.0 -P 3306

Connect to remote mysql-server using mysql-client from docker

docker run --rm -it mysql mysql -u user -h host -p database

Or you can step into the container:

docker run --rm -it mysql /bin/bash

And(inside the container) connect to the server using production server credentials:

root@container-id:/# mysql -u prod-user -h prod-host -p database-name

Connect to mysql-server in docker using mysql-client in docker

List running containers to get mysql container-id:

docker ps

Run mysql command inside the container:

docker exec -it container-id mysql -u root -h 0.0.0.0 -p

Or we can make it in two steps:

Step inside the container:

docker exec -it 6c9c46111f94 mysql /bin/bash

Connect to mysql in that container:

root@6c9c46111f94:/# mysql -u root -h 0.0.0.0 -p

Use docker compose tool

docker compose is a really nice tool to configure, group, and manage your containers/services.

Create docker-compose.yml file.

The example how it might look like (it includes example for postgres as well):

version: "3.7"
services:
  db-mysql:
    image: mysql:5.7
    container_name: db-mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - "3306:3306"
  db-psql:
    image: postgres:9
    container_name: db-postgres
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
    ports:
      - "5432:5432"

How to run mysql service:

docker-compose up -d db-mysql

Check status:

docker-compose ps

If you have any issue, check the logs of the service:

docker-compose logs db-mysql

Useful links:

Practical example for Ranking with Window Functions

Max Borysov — Thu, 08 Apr 2021 20:57:24 +0000

In this article, Mysql 5.7 Window Functions Workaround, I put some examples of how we can solve problems if we need window functions in MySQL 5.7(which lacks such functionality).

In this post, we can achieve the same result, but with less code, and it looks more readable.

What we want to do: given we have a list of tweets, we want to find SUM of the latest comments(only 3 latest items) for each user.

SELECT
  user_id,
  SUM(likes)
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY published_at DESC ) AS rank
      FROM twitter_comments
     ORDER BY user_id ASC, published_at DESC
  ) AS data
 WHERE rank <= 3
 GROUP BY user_id;

For each user's tweet, we generate a unique row number, which is later used to limit rows with aggregation functions(like SUM or AVG)

The intermediate result(with rank for each tweet per user) looks like this:

 id | user_id | likes |    published_at     | rank
----+---------+-------+---------------------+------
  6 |       1 |   200 | 2021-04-05 14:01:00 |    1
  5 |       1 |   100 | 2021-04-05 13:01:00 |    2
  4 |       1 |   200 | 2021-04-05 12:01:00 |    3
  3 |       1 |   300 | 2021-04-05 11:01:00 |    4
  2 |       1 |   200 | 2021-04-05 10:01:00 |    5
  1 |       1 |   100 | 2021-04-05 09:01:00 |    6
 12 |       2 |   100 | 2021-05-05 14:01:00 |    1
 11 |       2 |   200 | 2021-05-05 13:01:00 |    2
 10 |       2 |   300 | 2021-05-05 12:01:00 |    3
  9 |       2 |   100 | 2021-05-05 11:01:00 |    4
  8 |       2 |   300 | 2021-05-05 10:01:00 |    5
  7 |       2 |   200 | 2021-05-05 09:01:00 |    6
(12 rows)

SQL Fiddle example

Helpful links:

Mysql 5.7 Window Functions Workaround

Max Borysov — Mon, 05 Apr 2021 20:51:15 +0000

If you work a lot with data in SQL, sooner or later you will have a task to find top N results/row per group. Window functions provide great help there. But MySQL 5.7 does not support them, only MySQL 8.0.

There are several ways we can solve this task.

Let's say we have the following task: we have a list of tweets/posts (with user_id, pub date, likes count) and we want to get SUM likes for top-N tweets for each user.

Table structure:

CREATE TABLE twitter_comments (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  user_id INT,
  likes INT,
  published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

There are at least three ways we can do that.

Solution 1

SELECT
  tw.user_id,
  SUM(tw.likes) AS total_likes
  FROM twitter_comments AS tw
  JOIN (
    SELECT
      user_id,
      GROUP_CONCAT(id ORDER BY published_at DESC) AS comments_ids
      FROM twitter_comments
    GROUP BY user_id
  ) AS t ON t.user_id = tw.user_id
 WHERE FIND_IN_SET(tw.id, t.comments_ids) BETWEEN 1 AND 3
 GROUP BY tw.user_id;

GROUP_CONCAT will return a list of comments ids per user:

+---------+----------------+
| user_id | comments_ids   |
+---------+----------------+
|       1 | 6,5,4,3,2,1    |
|       2 | 12,11,10,9,8,7 |
+---------+----------------+

FIND_IN_SET will return the index of a current comment id in a list of all comments(for a specific user). In our case, an index value BETWEEN 1 AND 3 should match the latest three comments.

Possible issues: GROUP_CONCAT has a maximum length and if a user has many comments, you can easily hit the limit. But since we are interested only in the top 3,5 or 10 results, I do not think it should be a problem.

SQL Fiddle example

Solution 2

Similar to solution#1, but this time we'll use SUBSTRING_INDEX and LOCATE.

SELECT
  tw.user_id,
  SUM(tw.likes) AS total_likes
  FROM twitter_comments AS tw
  JOIN (
    SELECT
      user_id,
      SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY published_at DESC), ',', 3) AS comments_ids
      FROM twitter_comments
    GROUP BY user_id
  ) AS t ON t.user_id = tw.user_id
 WHERE LOCATE(tw.id, t.comments_ids) > 0
 GROUP BY tw.user_id;

SUBSTRING_INDEX in combination with GROUP_CONCAT will return already trimmed result(latest comments ids)

+---------+--------------+
| user_id | comments_ids |
+---------+--------------+
|       1 | 6,5,4        |
|       2 | 12,11,10     |
+---------+--------------+

And LOCATE will help us to check if a current comment id is in that list:

SELECT LOCATE('6', '6,5,4');
+----------------------+
| LOCATE('6', '6,5,4') |
+----------------------+
|                    1 |
+----------------------+

SQL Fiddle example

Solution 3

Use session variables to generate rank/row number for each comment(per user). Smth like that:

+----+---------+-------+---------------------+------+--------------+
| id | user_id | likes | published_at        | rank | curr_user_id |
+----+---------+-------+---------------------+------+--------------+
|  6 |       1 |   200 | 2021-04-05 14:01:00 |    1 |            1 |
|  5 |       1 |   100 | 2021-04-05 13:01:00 |    2 |            1 |
|  4 |       1 |   200 | 2021-04-05 12:01:00 |    3 |            1 |
|  3 |       1 |   300 | 2021-04-05 11:01:00 |    4 |            1 |
|  2 |       1 |   200 | 2021-04-05 10:01:00 |    5 |            1 |
|  1 |       1 |   100 | 2021-04-05 09:01:00 |    6 |            1 |
| 12 |       2 |   100 | 2021-05-05 14:01:00 |    1 |            2 |
| 11 |       2 |   200 | 2021-05-05 13:01:00 |    2 |            2 |
| 10 |       2 |   300 | 2021-05-05 12:01:00 |    3 |            2 |
|  9 |       2 |   100 | 2021-05-05 11:01:00 |    4 |            2 |
|  8 |       2 |   300 | 2021-05-05 10:01:00 |    5 |            2 |
|  7 |       2 |   200 | 2021-05-05 09:01:00 |    6 |            2 |
+----+---------+-------+---------------------+------+--------------+

Full example:

SET @curr_count = 0, @curr_user_id = 0;
SELECT
  user_id,
  SUM(likes) AS total_likes
  FROM (
    SELECT
      *,
      ( @curr_count := IF ( @curr_value = user_id, @curr_count + 1, 1 ) ) AS rank,
      ( @curr_value := user_id ) AS curr_user_id
      FROM twitter_comments
     ORDER BY user_id ASC, published_at DESC
  ) AS comments
 WHERE rank <= 3
 GROUP BY user_id;

SQL Fiddle example

Here is how we can achieve the same result using Window Functions in Postgres: Practical example for Ranking with Window Functions

Helpful links:

stackoverflow discussion
ranking example
many people recommend this article How to Select the First/Least/Max Row per Group in SQL