Amazon RDS for MariaDB のデータを Fivetran で Snowflake に同期してみる

sudo dnf install -y mariadb105

curl -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
mysql -h <エンドポイント> -P 3306 -u <ユーザー> -p --ssl-verify-server-cert --ssl-ca=./global-bundle.pem

-- データベース（スキーマ）作成：mydb
CREATE DATABASE mydb;
USE mydb;

-- サンプルテーブル作成
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- レコードを追加
INSERT INTO users (name, email) VALUES
    ('山田太郎', 'yamada@example.com'),
    ('鈴木花子', 'suzuki@example.com'),
    ('田中一郎', 'tanaka@example.com');

-- サンプルデータ2
CREATE DATABASE shop;
USE shop;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price INT NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, category, price, stock) VALUES
    ('ノートPC', 'electronics', 98000, 10),
    ('マウス', 'electronics', 2980, 50),
    ('デスク', 'furniture', 45000, 5),
    ('チェア', 'furniture', 32000, 8),
    ('USBハブ', 'electronics', 1980, 30);

> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

> SHOW GLOBAL VARIABLES LIKE 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

> SHOW GLOBAL VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON    |
+-------------------------+-------+

gtid_mode = ON
gtid_strict_mode = ON

CALL mysql.rds_set_configuration('binlog retention hours', 24);

CALL mysql.rds_show_configuration();
+------------------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+        
| name                   | value | description                                                                                                                                                                                   |        
+------------------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+        
| binlog retention hours | 24    | binlog retention hours specifies the duration in hours before binary logs are automatically deleted.                                                                                          |        
| source delay           | 0     | source delay specifies the replication delay in seconds between this instance and its primary (master) DB instance.                                                                           |        
| target delay           | 0     | target delay specifies the replication delay in seconds between this instance and any future RDS-managed read replicas created from this instance. Ignored for non-RDS-managed read replicas. |        
+------------------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+        

-- ユーザー作成
CREATE USER 'fivetran'@'%' IDENTIFIED BY '<パスワード>';

-- 権限付与
-- ここではすべてのデータベースとに対する権限を付与
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'fivetran'@'%';
GRANT SELECT ON mysql.rds_heartbeat2 TO 'fivetran'@'%';
GRANT SELECT ON mysql.rds_configuration TO 'fivetran'@'%';
GRANT EXECUTE ON PROCEDURE mysql.rds_kill TO 'fivetran'@'%';

-- 確認
SHOW GRANTS FOR 'fivetran'@'%';

# グループを作成
sudo groupadd fivetran
# ユーザーを作成
sudo useradd -m -g fivetran fivetran
# ユーザーに切り替え
sudo su - fivetran
# .ssh ディレクトリを作成
mkdir ~/.ssh
# 権限設定
chmod 700 ~/.ssh
# 作成したディレクトリに移動
cd ~/.ssh
# ファイルを作成
touch authorized_keys
# ファイルの権限を設定
chmod 600 authorized_keys

vi authorized_keys

-- users テーブル
-- レコード追加
INSERT INTO mydb.users (name, email) VALUES
    ('佐藤次郎', 'sato@example.com'),
    ('高橋美咲', 'takahashi@example.com');

-- 更新
UPDATE mydb.users SET email = 'yamada_new@example.com' WHERE id = 1;

-- products テーブル
-- レコード追加：
INSERT INTO shop.products (name, category, price, stock) VALUES
    ('キーボード', 'electronics', 8980, 20),
    ('モニター', 'electronics', 35000, 7);

-- 更新
UPDATE shop.products SET price = 89000, stock = 8 WHERE id = 1;