はじめに
TerraformでRDSなどデータベースを構築するときに、CREATEコマンドでテーブルを作成したり、INSERTコマンドでデータを追加したりするようなSQLのスクリプトも実行したい場合があるかと思います。この記事ではTerraformでSQLコマンドを実行する方法について紹介します。
適用したい SQL 文
例えば購買データを蓄積するデータベースを構築するため、以下のようなddl.sqlをDBリソースに適用したいとします。
ddl.sql
CREATE TABLE IF NOT EXISTS items
(
id char(36) NOT NULL,
name varchar(36) NOT NULL,
description varchar(255) NOT NULL,
price int NOT NULL,
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS sales
(
id int UNSIGNED NOT NULL AUTO_INCREMENT,
item_id char(36) NOT NULL,
amount int UNIQUE NOT NULL,
purchased_at bigint(14) UNSIGNED NOT NULL
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE items ADD FOREIGN KEY (item_id) REFERENCES items (id) ON DELETE RESTRICT ON UPDATE RESTRICT;
また、テーブルを作成後にdata.sqlのようなサンプルデータを追加したいとします。
data.sql
INSERT INTO items (id, name, description, price)
VALUES
(
'5b0338ac-b12e-42cb-b2c5-a5a4c6533403',
'Pencil',
'This is a pencil.',
12
),
(
'839156ab-6320-4c4b-a38a-43d34aa57aaa',
'Eraser',
'This is an eraser.',
11
);
INSERT INTO sales (item_id, amount, purchased_at)
VALUES
(
'5b0338ac-b12e-42cb-b2c5-a5a4c6533403',
5,
1661089540000
),
(
'839156ab-6320-4c4b-a38a-43d34aa57aaa',
1,
1661089540000
);
null_resource
今回ははデータベースとしてAurora Serverless v1を構築し、そこからData API経由でSQLを発行するTerraformコードを紹介します。ディレクトリ構成は以下のようになっています。
.
├── rds.tf
├── ddl.sql
└── data.sql
TerraformでSQLを実行するにはnull_resourceというリソースを使用し、SQLファイル、実行コマンド、環境変数を指定します。今回はData API経由でSQLを実行するので以下のようなコードになります。
rds.tf
resource "aws_rds_cluster" "main" {
.
.
.
}
resource "null_resource" "db_ddl" {
triggers = {
file = filesha1("ddl.sql")
}
provisioner "local-exec" {
command = <<-EOF
while read line; do
echo "$line"
aws rds-data execute-statement --resource-arn "$DB_ARN" --database "$DB_NAME" --secret-arn "$SECRET_ARN" --sql "$line"
done < <(awk 'BEGIN{RS=";\n"}{gsub(/\n/,""); if(NF>0) {print $0";"}}' ddl.sql)
EOF
environment = {
DB_ARN = aws_rds_cluster.main.arn
DB_NAME = aws_rds_cluster.main.database_name
SECRET_ARN = aws_secretsmanager_secret.rds.arn
}
interpreter = ["bash", "-c"]
}
}
resource "null_resource" "sample_data" {
triggers = {
file = filesha1("data.sql")
}
provisioner "local-exec" {
command = <<-EOF
while read line; do
echo "$line"
aws rds-data execute-statement --resource-arn "$DB_ARN" --database "$DB_NAME" --secret-arn "$SECRET_ARN" --sql "$line"
done < <(awk 'BEGIN{RS=";\n"}{gsub(/\n/,""); if(NF>0) {print $0";"}}' data.sql)
EOF
environment = {
DB_ARN = aws_rds_cluster.main.arn
DB_NAME = aws_rds_cluster.main.database_name
SECRET_ARN = aws_secretsmanager_secret.rds.arn
}
interpreter = ["bash", "-c"]
}
depends_on = [
null_resource.db_ddl
]
}
Data API経由ではなくMySQLに接続してSQLを発行したい場合は以下のようなコードになります。
resource "null_resource" "db_ddl" {
triggers = {
file = filesha1("ddl.sql")
}
- provisioner "local-exec" {
- command = <<-EOF
- while read line; do
- echo "$line"
- aws rds-data execute-statement --resource-arn "$DB_ARN" --database "$DB_NAME" --secret-arn "$SECRET_ARN" - -sql "$line"
- done < <(awk 'BEGIN{RS=";\n"}{gsub(/\n/,""); if(NF>0) {print $0";"}}' ddl.sql)
- EOF
- environment = {
- DB_ARN = aws_rds_cluster.main.arn
- DB_NAME = aws_rds_cluster.main.database_name
- SECRET_ARN = aws_secretsmanager_secret.rds.arn
- }
- interpreter = ["bash", "-c"]
- }
+ provisioner "local-exec" {
+ command = "mysql --host=${aws_rds_cluster.main.endpoint} --port=3306 --user=${aws_rds_cluster.main.master_username} --password=${aws_rds_cluster.main.master_password} --database=${aws_rds_cluster.main.database_name} < ${file(${ddl.sql)}"
+ }
}
また、dev環境の場合のみdata.sqlを実行したい場合は以下のようにcountを追加します。
resource "null_resource" "sample_data" {
+ count = terraform.workspace == "dev" ? 1 : 0
triggers = {
file = filesha1("data.sql")
}
provisioner "local-exec" {
command = <<-EOF
while read line; do
echo "$line"
aws rds-data execute-statement --resource-arn "$DB_ARN" --database "$DB_NAME" --secret-arn "$SECRET_ARN" --sql "$line"
done < <(awk 'BEGIN{RS=";\n"}{gsub(/\n/,""); if(NF>0) {print $0";"}}' data.sql)
EOF
environment = {
DB_ARN = aws_rds_cluster.main.arn
DB_NAME = aws_rds_cluster.main.database_name
SECRET_ARN = aws_secretsmanager_secret.rds.arn
}
interpreter = ["bash", "-c"]
}
depends_on = [
null_resource.db_ddl
]
}
完成コード
最終的にコードは以下のようになります。
resource "aws_rds_cluster" "main" {
cluster_identifier = local.aurora_cluster_identifier
database_name = local.aurora_database_name
master_username = local.aurora_master_username
master_password = local.aurora_master_password
db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.main.name
db_subnet_group_name = data.aws_db_subnet_group.main.name
deletion_protection = true
skip_final_snapshot = false
engine = "aurora-mysql"
engine_version = "5.7.mysql_aurora.2.07.1"
engine_mode = "serverless"
backup_retention_period = 7
storage_encrypted = true
iam_database_authentication_enabled = false
enable_http_endpoint = true
scaling_configuration {
min_capacity = 2
max_capacity = 256
auto_pause = true
}
lifecycle {
ignore_changes = [
availability_zones,
engine_version,
master_username,
master_password,
]
}
}
resource "null_resource" "db_ddl" {
triggers = {
file = filesha1("ddl.sql")
}
provisioner "local-exec" {
command = <<-EOF
while read line; do
echo "$line"
aws rds-data execute-statement --resource-arn "$DB_ARN" --database "$DB_NAME" --secret-arn "$SECRET_ARN" --sql "$line"
done < <(awk 'BEGIN{RS=";\n"}{gsub(/\n/,""); if(NF>0) {print $0";"}}' ddl.sql)
EOF
environment = {
DB_ARN = aws_rds_cluster.main.arn
DB_NAME = aws_rds_cluster.main.database_name
SECRET_ARN = aws_secretsmanager_secret.rds.arn
}
interpreter = ["bash", "-c"]
}
}
resource "null_resource" "sample_data" {
count = terraform.workspace == "dev" ? 1 : 0
triggers = {
file = filesha1("data.sql")
}
provisioner "local-exec" {
command = <<-EOF
while read line; do
echo "$line"
aws rds-data execute-statement --resource-arn "$DB_ARN" --database "$DB_NAME" --secret-arn "$SECRET_ARN" --sql "$line"
done < <(awk 'BEGIN{RS=";\n"}{gsub(/\n/,""); if(NF>0) {print $0";"}}' data.sql)
EOF
environment = {
DB_ARN = aws_rds_cluster.main.arn
DB_NAME = aws_rds_cluster.main.database_name
SECRET_ARN = aws_secretsmanager_secret.rds.arn
}
interpreter = ["bash", "-c"]
}
depends_on = [
null_resource.db_ddl
]
}
参考
AlloyDB
Amazon Cognito
Amazon EC2
Amazon ECS
Amazon QuickSight
Amazon RDS
Amazon Redshift
Amazon S3
API
Autonomous Vehicle
AWS
AWS API Gateway
AWS Chalice
AWS Control Tower
AWS IAM
AWS Lambda
AWS VPC
BERT
BigQuery
Causal Inference
ChatGPT
Chrome Extension
CircleCI
Classification
Cloud Functions
Cloud IAM
Cloud Run
Cloud Storage
Clustering
CSS
Data Engineering
Data Modeling
Database
dbt
Decision Tree
Deep Learning
Descriptive Statistics
Differential Equation
Dimensionality Reduction
Discrete Choice Model
Docker
Economics
FastAPI
Firebase
GIS
git
GitHub
GitHub Actions
Google
Google Cloud
Google Search Console
Hugging Face
Hypothesis Testing
Inferential Statistics
Interval Estimation
JavaScript
Jinja
Kedro
Kubernetes
LightGBM
Linux
LLM
Mac
Machine Learning
Macroeconomics
Marketing
Mathematical Model
Meltano
MLflow
MLOps
MySQL
NextJS
NLP
Nodejs
NoSQL
ONNX
OpenAI
Optimization Problem
Optuna
Pandas
Pinecone
PostGIS
PostgreSQL
Probability Distribution
Product
Project
Psychology
Python
PyTorch
QGIS
R
ReactJS
Regression
Rideshare
SEO
Singer
sklearn
Slack
Snowflake
Software Development
SQL
Statistical Model
Statistics
Streamlit
Tabular
Tailwind CSS
TensorFlow
Terraform
Transportation
TypeScript
Urban Planning
Vector Database
Vertex AI
VSCode
XGBoost
Ryusei Kakujo
Focusing on data science for mobility
Bench Press 100kg!