Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード

2018-02-11

EMRからS3にアクセス時のAPI発行回数を調べる

PySpark on EMR で S3 上の1オブジェクトの読込みに API 発行回数が1回か複数回か調べてみた。


CloudTrail で API 発行を捕捉する

$ export LANG=C
$ export TZ=UTC
$ export AWS_DEFAULT_REGION=ap-northeast-1
$ aws s3api create-bucket --bucket az-s3-trail-log \
	--create-bucket-configuration LocationConstraint=ap-northeast-1
$ cat <<EOF > policy.json
{
     "Version": "2012-10-17",
     "Statement": [
          {
               "Sid": "AWSCloudTrailAclCheck20180211",
               "Effect": "Allow",
               "Principal": {
                    "Service": "cloudtrail.amazonaws.com"
               },
               "Action": "s3:GetBucketAcl",
               "Resource": "arn:aws:s3:::az-s3-trail-log"
          },
          {
               "Sid": "AWSCloudTrailRead20180211",
               "Effect": "Allow",
               "Principal": {
                    "Service": "cloudtrail.amazonaws.com"
               },
               "Action": "s3:*",
               "Resource": "arn:aws:s3:::az-s3-trail-log/*",
               "Condition": {
                    "StringEquals": {
                         "s3:x-amz-acl": "bucket-owner-full-control"
                    }
               }
          }
     ]
}
EOF
$ aws s3api put-bucket-policy --bucket az-s3-trail-log --policy file://policy.json
$ aws s3api create-bucket --bucket az-s3-trail-test \
	--create-bucket-configuration LocationConstraint=ap-northeast-1
  • trail の作成
$ aws cloudtrail create-trail --name s3-trail --s3-bucket-name az-s3-trail-log
$ cat <<EOF >event_selector.json
[
    {
        "ReadWriteType": "All",
        "IncludeManagementEvents": false,
        "DataResources": [
            {
                "Type": "AWS::S3::Object",
                "Values": [
                    "arn:aws:s3:::az-s3-trail-test/"
                ]
            }
        ]
    }
]
EOF
$ aws cloudtrail put-event-selectors --trail-name s3-trail \
	--event-selectors file://event_selector.json
{
    "EventSelectors": [
        {
            "IncludeManagementEvents": false,
            "DataResources": [
                {
                    "Values": [
                        "arn:aws:s3:::az-s3-trail-test/"
                    ],
                    "Type": "AWS::S3::Object"
                }
            ],
            "ReadWriteType": "All"
        }
    ],
    "TrailARN": "arn:aws:cloudtrail:ap-northeast-1:**********:trail/s3-trail"
}
  • ロギングの開始
$ aws cloudtrail start-logging --name arn:aws:cloudtrail:ap-northeast-1:**********:trail/s3-trail

EMR から S3 にアクセスする

  • PySpark で S3 のファイルにアクセスする
$ perl -le 'print for 1..100000000' > number.txt
$ aws s3 cp number.txt s3://az-s3-trail-test/
$ pyspark
>>> rdd = sc.textFile("s3://az-s3-trail-test/number.txt")
>>> rdd.count()
>>> exit()
  • 行数を倍にしてみる
$ perl -le 'print for 1..200000000' > number2x.txt
$ aws s3 cp number2x.txt s3://az-s3-trail-test/
$ pyspark
>>> rdd = sc.textFile("s3://az-s3-trail-test/number.txt")
>>> rdd.count()
>>> exit()

EMR から S3 にアクセスした際の API 発行回数を調べる

  • Trail ログから API 発行回数を確認する。
$ aws s3 ls --recursive s3://az-s3-trail-log
$ aws s3 cp s3://az-s3-trail-log/AWSLogs/*********/CloudTrail/ap-northeast-1/2018/02/11/*********_CloudTrail_ap-northeast-1_20180211T1030Z_8UxnbO7KHiDJe42P.json.gz ./
$ gunzip *********_CloudTrail_ap-northeast-1_20180211T1030Z_8UxnbO7KHiDJe42P.json.gz
$ cat *********_CloudTrail_ap-northeast-1_20180211T1030Z_8UxnbO7KHiDJe42P.json|jq -r '.Records[]|select(.userAgent|contains("ElasticMapReduce"))|@text "\(.userAgent)\t\(.eventName)"'|head
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadBucket
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadObject
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadBucket
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadObject
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	GetObject
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	GetObject
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadBucket
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadBucket
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadObject
[ElasticMapReduce/1.0.0 emrfs/s3n {}, aws-sdk-java/1.11.129 Linux/4.9.70-25.242.amzn1.x86_64 OpenJDK_64-Bit_Server_VM/25.151-b12/1.8.0_151 scala/2.11.8]	HeadBucket
$ cat *********_CloudTrail_ap-northeast-1_20180211T1030Z_8UxnbO7KHiDJe42P.json|jq -r '.Records[]|select(.userAgent|contains("ElasticMapReduce"))|@text "\(.userAgent)\t\(.eventName)"'|perl -lane 'print $F[$#F]'|sort|uniq -c
  27 GetObject
  13 HeadBucket
  12 HeadObject
  • 行数を倍にした時は API 発行回数が増えている。
$ cat *.json|jq -r '.Records[]|select(.userAgent|contains("ElasticMapReduce"))|@text "\(.userAgent)\t\(.eventName)"'|perl -lane 'print $F[$#F]'|sort|uniq -c 
  57 GetObject
  13 HeadBucket
  17 HeadObject

AWS CLI で s3 cp した際の API 発行回数を調べる

$ time aws s3 cp s3://az-s3-trail-test/number.txt s3://az-to/
copy: s3://az-s3-trail-test/number.txt to s3://az-to/number.txt

real	0m5.046s
user	0m1.073s
sys	0m0.232s

参考

S3イベント通知はオブジェクト作成完了後に通知される

S3イベント通知を使うと、S3バケットオブジェクトが作成されれたタイミングでイベント・ドリブンでLambda関数を呼び出したりSNSトピックにメッセージを送ったりできるが、作成開始ではなく完了したタイミングでイベント通知されることを確認した。


事前準備

環境変数を設定する
$ export LANG=C
$ export TZ=UTC
$ export AWS_DEFAULT_REGION=ap-northeast-1
SNSトピックを作成する
  • トピック名が重複しないよう既存のを確認する
$ aws sns list-topics
  • SNSトピックを作成する
$ aws sns create-topic --name s3-event-test
$ aws sns subscribe --topic-arn arn:aws:sns:ap-northeast-1:*********:s3-event-test \
	--protocol email \
	--notification-endpoint ******@gmail.com
  • 指定したメールアドレスに確認メールが届くので本文のリンクをクリックする。
Subject: AWS Notification - Subscription Confirmation
Body:
You have chosen to subscribe to the topic: 
arn:aws:sns:ap-northeast-1:*********:s3-event-test

To confirm this subscription, click or visit the link below (If this was in error no action is necessary): 
Confirm subscription
  • トピックのポリシーを設定する
$ cat << EOF > sns_topic_policy.json
{
  "Version": "2008-10-17",
  "Id": "__default_policy_ID",
  "Statement": [
    {
      "Sid": "__default_statement_ID",
      "Effect": "Allow",
      "Principal": {
        "AWS": "*"
      },
      "Action": [
        "SNS:Publish",
        "SNS:RemovePermission",
        "SNS:SetTopicAttributes",
        "SNS:DeleteTopic",
        "SNS:ListSubscriptionsByTopic",
        "SNS:GetTopicAttributes",
        "SNS:Receive",
        "SNS:AddPermission",
        "SNS:Subscribe"
      ],
      "Resource": "arn:aws:sns:ap-northeast-1:********:s3-event-test",
      "Condition": {
        "ArnEquals": {
          "aws:SourceArn": "arn:aws:s3:::az-test-2018"
        }
      }
    }
  ]
}
EOF
$ aws sns set-topic-attributes \
	--topic-arn arn:aws:sns:ap-northeast-1:********:s3-event-test \
	--attribute-name Policy \
	--attribute-value file://sns_topic_policy.json
S3バケットを作成する
$ aws s3 mb s3://az-test-2018
  • SNSへの通知設定をファイルにJSONで記述する。
$ cat << EOF > topic_config.json
{
    "TopicConfigurations": [
        {
            "TopicArn": "arn:aws:sns:ap-northeast-1:********:s3-event-test",
            "Events": [
                "s3:ObjectCreated:*"
            ]
        }
    ]
}
$ aws s3api put-bucket-notification-configuration --bucket "az-test-2018" --notification-configuration file://topic_config.json

SNSに通知されるタイミングを確認する

  • ファイルを作成する
$ perl -le 'print for 1..100000000' > test.txt
$ ls -lh test.txt
-rw-r--r--  1 azekyohe  1896053708   848M Feb 11 10:22 test.txt
$ ls -l test.txt
-rw-r--r--  1 azekyohe  ...  888888898★ Feb 11 01:22 test.txt
$ date; aws s3 cp test.txt s3://az-test-2018/; date
Sun Feb 11 02:28:16 UTC 2018 ★開始時間
upload: ./test.txt to s3://az-test-2018/test.txt
Sun Feb 11 02:30:47 UTC 2018 ★終了時間
  • メールを確認する
Subject:  AWS Notifications
Body:
{"Records":[{"eventVersion":"2.0","eventSource":"aws:s3","awsRegion":"ap-northeast-1",
"eventTime":"2018-02-11T02:30:48.571Z"★,"eventName":"ObjectCreated:CompleteMultipartUpload", ... 
"object":{"key":"test.txt","size":888888898★,...

オブジェクトの作成が完了したタイムスタンプで通知されている。


参考

こんにちは、

"list-objects等で取得可能なキーの一覧には反映されておらず、(キーが)取得できない
(間も、オブジェクトはPUT時の戻り値のキーを引数にget-object等で取得できる)という状況は発生する可能性がある。"
と、理解しました。
つまり、PUT(新規書き込み)完了後、
・キーが取得できないことはあってもオブジェクトが取得できないケースは発生しない(オブジェクトは*必ず*取得できる)
という認識です。もし認識が誤っていればご指摘ください。

はい、そのようなご認識で問題はございません。

「書き込み後の読み取り整合性」は、PUT (新規書き込み)が正常に完了した直後に、GETで該当のキーを結果整合性の影響を受けずに取得できる (=404 NotFoundにはならない) ことを保証しています。

https://forums.aws.amazon.com/thread.jspa?threadID=225802

S3 イベント通知はオブジェクト作成完了後に通知されることを確認する

S3イベント通知を使うと、S3バケットにファイルが作成されたり削除されたのをトリガーにイベントドリブンで、Lambda関数をキックしたり、SNSやSQSにメッセージを送ったりすることができるが、作成や削除が開始したタイミングではなく完了したタイミングでイベント通知が行われることを確認した。S3は結果整合性のため、完了していてもアクセス時にファイルが見つからないことは起こり得る。


事前準備

export LANG=C
export TZ=UTC
export AWS_DEFAULT_REGION=ap-northeast-1
SNSトピックを作成する
  • 既存のSNSトピック名を確認する
aws sns list-topics
  • SNSトピックを作成する
aws sns create-topic --name s3-event-test
aws sns subscribe --topic-arn arn:aws:sns:ap-northeast-1:*********:s3-event-test \
	--protocol email \
	--notification-endpoint ******@gmail.com
  • 指定したメールアドレスに確認メールが届くので本文のリンクをクリックする。
Subject: AWS Notification - Subscription Confirmation
Body:
You have chosen to subscribe to the topic: 
arn:aws:sns:ap-northeast-1:*********:s3-event-test

To confirm this subscription, click or visit the link below (If this was in error no action is necessary): 
Confirm subscription
S3バケットを作成する
aws s3 mb s3://az-test-2018
  • S3 にファイルが copy されたら SNS トピックに通知されるよう設定する。
    • 以下の内容でファイルを作成する。
{
    "TopicConfigurations": [
        {
            "TopicArn": "arn:aws:sns:ap-northeast-1:********:s3-event-test",
            "Events": [
                "s3:ObjectCreated:*"
            ]
        }
    ]
}
aws s3api put-bucket-notification-configuration --bucket "az-test-2018" --notification-configuration file://topic_config.json

試してみる

  • ファイルを作成する
$ perl -le 'print for 1..100000000' > test.txt
$ ls -lh test.txt
-rw-r--r--  1 azekyohe  1896053708   848M Feb 11 10:22 test.txt

S3に copy 時に通知される時刻を確認する

date; aws s3 cp test.txt s3://az-test-2018/; date

2017-12-10

MySQL Connector/J(JDBC Driver) で接続時に任意の collation_connection をセットする

MySQL Connector/J(JDBC Driver) で接続時に任意の collation_connection をセットする には以下の用に JDBC URL に「connectionCollation=utf8mb4_bin」のように設定すれば良い。

jdbc:mysql://aurora01.cluster-*******.ap-northeast-1.rds.amazonaws.com:3306/mydb?connectionCollation=utf8mb4_bin

参考

MySQL クライアントプログラム mysql、mysqladmin、mysqlcheck、mysqlimport、および mysqlshow は、次のように、使用するデフォルトの文字セットを特定します。

C アプリケーションは、サーバーに接続する前に次のように mysql_options() を呼び出すことによって、OS 設定に基づいて文字セットの自動検出を使用できます。

mysql_options(mysql,
              MYSQL_SET_CHARSET_NAME,
              MYSQL_AUTODETECT_CHARSET_NAME);
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.4 接続文字セットおよび照合順序

Use characterEncoding=utf8mb4& for jdbc url

jdbc:mysql://x.x.x.x:3306/db?useUnicode=true&characterEncoding=utf8mb4
java - utf8mb4 in MySQL Workbench and JDBC - Stack Overflow

connectionCollation

If set, tells the server to use this collation via 'set collation_connection'

Since version: 3.0.13

MySQL :: MySQL Connector/J 5.1 Developer Guide :: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

2017-12-06

AWS CLI でEMRクラスターのリスト表示で "'ascii' codec can't encode characters in position 1-7: ordinal not in range(128)" と怒られる

事象

  • AWS CLI を実行すると、"'ascii' codec can't encode characters in position 1-7: ordinal not in range(128)" と怒られる。
% aws emr list-clusters
{
    "Clusters": [
        {
            "Status": {
                "Timeline": {
                    "ReadyDateTime": 1512532909.567,
                    "CreationDateTime": 1512532287.771,
                    "EndDateTime": 1512537171.682
                },
                "State": "TERMINATED",
                "StateChangeReason": {
                    "Message": "Terminated by user request",
                    "Code": "USER_REQUEST"
                }
            },
            "NormalizedInstanceHours": 48,
            "Id": "j-1PM1S2K26V0K",
            "Name":
'ascii' codec can't encode characters in position 1-7: ordinal not in range(128)
% echo $LANG
C

原因

  • 標準出力する内容にマルチバイト文字が含まれるが、環境変数 LANG に C がセットされているため、ascii で標準出力しようとしている。

一方でf.writeでは「文字コードの指定ないからようわからん。asciiでいいやろ。」と考えasciiに変換しようとするのですが、当然日本語には対応できずエラーとなります。

Pythonでの文字列「文字型」と「ユニコード型」についてザックリとまとめた。 - 小さな星がほらひとつ

解決策

  • 環境変数 LANG を UTF-8 にセットして実行する。
% export LANG=en_US.UTF-8
% aws emr list-clusters

2017-09-15

MySQL で NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値が入る

MySQL で SQL モードが STRICT モードでない場合、NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値(0とか空文字)が入る(1行インサートだとエラーで入らない)。


検証結果

  • Amazon Aurora with MySQL Compatibility に接続する
$ mysql -h ******.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  • データベースを選択する
mysql> use mydb;
Database changed
  • テーブルを作成する
mysql> create table `not_null_test` (
    ->   `id` int(10) unsigned not null,
    ->   `int_col` int(10) unsigned not null,
    ->   `char_col` char(10)  not null,
    ->   `ts_col` timestamp not null,
    ->   primary key (`id`)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
  • SQL モードは設定されていない
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.02 sec)
  • 1行インサートはエラーになる
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートは成功する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
Query OK, 2 rows affected, 4 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 4
  • テーブルのデータを確認する
mysql> select * from not_null_test;
+----+---------+----------+---------------------+
| id | int_col | char_col | ts_col              |
+----+---------+----------+---------------------+
|  1 |       0 |          | 2017-09-15 06:54:07 |
|  2 |       0 |          | 2017-09-15 06:54:07 |
+----+---------+----------+---------------------+
2 rows in set (0.02 sec)
  • SQLモードを STRICT_ALL_TABLES にする
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.03 sec)
  • 1行インサートは失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートも失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be nul

参考

単列インサートの場合はNOT NULLが指定されたカラムにNULL値が挿入されるとそのクエリはエラーとなって失敗するが、複数列インサートの場合は警告(warning)を発するものの、クエリは正常に受け付けられる。

その際、NULL値が指定された各カラムにはそれぞれのカラムのデータ型の暗黙的なデフォルト値が挿入される。(数値型なら0、文字列型なら空文字''、etc…)

MySQLにおけるNOT NULLカラムへのインサート時の挙動 - Sojiro’s Blog

NOT NULL として宣言されているカラムへの NULL の挿入。複数行の INSERT ステートメントまたは INSERT INTO ... SELECT ステートメントの場合、このカラムは、そのカラムデータ型の暗黙のデフォルト値に設定されます。これは、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では「0」の値です。サーバーは SELECT からの結果セットを検査して、それが単一行を返すかどうかを確認しないため、INSERT INTO ... SELECT ステートメントは複数行の挿入と同じ方法で処理されます。(単一行の INSERT の場合は、NULL が NOT NULL カラムに挿入されても警告は発生しません。代わりに、このステートメントがエラーで失敗します。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

  • 厳密な SQL モードを有効にした場合、トランザクションテーブルに対してエラーが発生し、ステートメントがロールバックされます。非トランザクションテーブルではエラーが起きるが、これが複数行ステートメントの 2 行目以降の行に対するエラーの場合、先行する行が挿入されています。
  • 厳密モードが有効でない場合、MySQL はカラムデータ型の暗黙的なデフォルト値にカラムを設定します。

(中略)

セクション5.1.7「サーバー SQL モード」を参照してください。

所定のテーブルに対して、SHOW CREATE TABLE ステートメントを使用すると、どのカラムに明示的な DEFAULT 句があるかを確認できます。

暗黙的なデフォルトは次のように定義されます。

  • 数値型のデフォルトは 0 です。ただし、例外として AUTO_INCREMENT 属性で宣言された整数型または浮動小数点型のデフォルトは、そのシーケンスの次の値になります。
  • TIMESTAMP 以外の日付と時間型のデフォルトには、「ゼロ」値が適切です。explicit_defaults_for_timestamp システム変数が有効な場合、これは TIMESTAMP にも当てはまります (セクション5.1.4「サーバーシステム変数」を参照してください)。それ以外の場合、テーブルの最初の TIMESTAMP カラムのデフォルト値は現在の日付と時間になります。セクション11.3「日付と時間型」を参照してください。
  • ENUM ではない文字列型のデフォルト値は空の文字列です。ENUM のデフォルトは、最初の列挙値です。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.6 データ型デフォルト値

厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。値はいくつかの理由で無効になることがあります。たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。(NULL カラムの場合、値が欠落しているときは NULL が挿入されます。)

厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください)。厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。

データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。

厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。(セクション5.1.4「サーバーシステム変数」を参照してください。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

2017-09-10

Oracle Database の PL/SQL を MySQL にどう移行するか

はじめに

Oracle Database の PL/SQL の MySQL への移行方法を説明します(MySQL 5.0 からストアドプロシージャに対応している)。

AWS Schema Conversion Tool (SCT) という GUI デスクトップアプリケーション(Windwos/Mac OS X/Linux版)をインストールし、移行元の Oracle Database に接続して自動的にPL/SQL を MySQL のルーチンに変換することができます。

SCT による変換方法は Oracle Database を Amazon Aurora に移行する方法 | Amazon Web Services ブログ をご覧ください。


移行先として適切なサービスを選ぶ

本題に入る前に Oracle Database からの移行先として MySQL、PostgreSQL、Redshift のどれが適切か説明します。

経験上、エンタープライズで Oracle Database を使っている場合は PostgreSQL が向いているケースが多いです。アクセスブロック数が少ないSQLのみで結合もネステッドループのみで問題ないシステムは MySQL が向いています。Exadata で OLTP(トランザクション) とOLAP(分析)を共存させている場合は PostgreSQL で OLTP、Redshift で OLAP(分析)という使い分けをオススメします。

MySQL が向いているケース
  • OLTP でシンプルでアクセスブロック数が少ないSQLが大半。
  • 結合はネステッドループのみで問題ない程度の軽い SQL のみ。
  • さらに結合も必要なく、キーのみにでアクセスするケースでは DynamoDB が向いています。
PostgreSQL が向いているケース
  • OLTP でも結合対象行数を絞れず HASH JOIN が必要な重いSQLが多い。
  • エンタープライズで Oracle Database を使っているシステムはこのケースが一番多い印象。
Redshift が向いているケース
  • Exadata で集計処理を投げていて Smart Scan を享受しているようなケースでは Redshift が向いている。
  • Exadata で OTLP(トランザクション処理)と分析の両方の用途で使っている場合は、OLTP は RDS で、分析は Redshift というように使い分けるアーキテクチャにする。

Oracle Database と MySQL のプロシージャ・ファンクションの構文の違い

SCT でプロシージャを変換した例

aws-database-migration-samples/generate_tickets.pls at master ? awslabs/aws-database-migration-samples ? GitHub を変換した例です。

  • Oracle Database(変換前)
procedure generate_tickets(P_event_id IN NUMBER) as
  CURSOR event_cur(P_ID NUMBER) IS
  SELECT id,location_id
  FROM   sporting_event
  WHERE  ID = P_ID;

  standard_price NUMBER(6,2);

BEGIN
  standard_price := DBMS_RANDOM.VALUE(30,50);

  FOR event_rec IN event_cur(P_event_id) LOOP
    INSERT /*+ APPEND */ INTO sporting_event_ticket(id,sporting_event_id,sport_location_id,seat_level,seat_section,seat_row,seat,ticket_price)
    SELECT sporting_event_ticket_seq.nextval
      ,sporting_event.id
      ,seat.sport_location_id
      ,seat.seat_level
      ,seat.seat_section
      ,seat.seat_row
      ,seat.seat
      ,(CASE
         WHEN seat.seat_type = 'luxury' THEN 3*standard_price
         WHEN seat.seat_type = 'premium' THEN 2*standard_price
         WHEN seat.seat_type = 'standard' THEN standard_price
         WHEN seat.seat_type = 'sub-standard' THEN 0.8*standard_price
         WHEN seat.seat_type = 'obstructed' THEN 0.5*standard_price
         WHEN seat.seat_type = 'standing' THEN 0.5*standard_price
      END ) ticket_price
    FROM sporting_event
       ,seat
    WHERE sporting_event.location_id = seat.sport_location_id
    AND   sporting_event.id = event_rec.id;
  END LOOP;
END;
  • MySQL(変換後)
    • コメントが入っている箇所は Oracle Database の構文に相当するものが MySQL になかった箇所で、この部分は自分でワークアラウンドを記述する必要があります。
CREATE PROCEDURE DMS_SAMPLE.GENERATE_TICKETS(IN par_P_EVENT_ID DOUBLE)
BEGIN
    DECLARE var$ID DOUBLE;
    DECLARE var$LOCATION_ID DOUBLE;
    DECLARE par_P_ID DOUBLE;
    DECLARE var_standard_price DECIMAL (6, 2);
    DECLARE done INT DEFAULT FALSE;
    DECLARE event_cur CURSOR FOR SELECT
        ID,a
        FROM SPORTING_EVENT
        WHERE ID = par_P_ID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done := TRUE
    /*
    [340 - Severity CRITICAL - MySQL doesn't support the SYS.DBMS_RANDOM.VALUE(NUMBER,NUMBER) function. Create a user-defined function.]
    standard_price := DBMS_RANDOM.VALUE(30,50)
    */;
    SET par_P_ID := par_P_event_id;
    OPEN event_cur;

    read_label:
    LOOP
        FETCH event_cur INTO var$ID, var$LOCATION_ID;

        IF done THEN
            LEAVE read_label;
        END IF;
        INSERT INTO SPORTING_EVENT_TICKET
            (ID, SPORTING_EVENT_ID, SPORT_LOCATION_ID, SEAT_LEVEL, SEAT_SECTION, SEAT_ROW, SEAT, TICKET_PRICE)
            SELECT
                aws_oracle_ext.sequence$nextval('SPORTING_EVENT_TICKET_SEQ', 'DMS_SAMPLE'), SPORTING_EVENT.ID, SEAT.SPORT_LOCATION_ID, SEAT.SEAT_LEVEL, SEAT.SEAT_SECTION, SEAT.SEAT_ROW, SEAT.SEAT, (CASE WHEN SEAT.SEAT_TYPE = 'luxury' THEN 3 * var_standard_price WHEN SEAT.SEAT_TYPE = 'premium' THEN 2 * var_standard_price WHEN SEAT.SEAT_TYPE = 'standard' THEN var_standard_price WHEN SEAT.SEAT_TYPE = 'sub-standard' THEN 0.8 * var_standard_price WHEN SEAT.SEAT_TYPE = 'obstructed' THEN 0.5 * var_standard_price WHEN SEAT.SEAT_TYPE = 'standing' THEN 0.5 * var_standard_price END) AS ticket_price
                FROM SPORTING_EVENT, SEAT
                WHERE SPORTING_EVENT.LOCATION_ID = SEAT.SPORT_LOCATION_ID AND SPORTING_EVENT.ID = var$ID;
    END LOOP;
    CLOSE event_cur;
END;


比較

CREATE PROCEDURE Statement

  • Converting stored procedures from Oracle to MySQL:
#OracleMySQL
1CREATE OR REPLACE PROCEDURE DROP PROCEDURE IF EXISTS and CREATE PROCEDURE
2param IN / OUT / IN OUT datatype Parameter definition IN / OUT / INOUT param datatype(length)
3IS / AS Removed
4Variable declaration is before BEGIN Variable declaration is after BEGIN
5END sp_name END

CREATE FUNCTION Statement

  • Converting user-defined functions from Oracle to MySQL:
#Oracle MySQL
1CREATE OR REPLACE FUNCTION DROP FUNCTION IF EXISTS and CREATE FUNCTION
2param IN / OUT / IN OUT datatype Parameter definition param datatype(length)
3RETURN datatype Return value RETURNS datatype(length)
4IS / AS Removed
5Variable declaration is before BEGIN Variable declaration is after BEGIN
6END func_name END

PL/SQL Statements

  • Converting PL/SQL statements and clauses from Oracle to MySQL:
#OracleMySQL
1variable datatype := value Variable declaration DECLARE variable datatype DEFAULT value
2variable := value Assignment statement SET variable = value
3CURSOR cur (params) IS SELECT Cursor declaration DECLARE cur CURSOR FOR SELECT
4Variable and cursor declarations can be mixed in any order Variable declarations must be before cursor and handlers
5FOR rec IN cursor LOOP Cursor loop OPEN cursor WHILE-FETCH-CLOSE
6IF THEN ELSIF ELSE END IF IF statement IF THEN ELSEIF ELSE END IF
7WHILE condition LOOP sql END LOOP A loop statement WHILE condition DO sql END WHILE
8EXIT WHEN condition Exit from a loop IF condition THEN LEAVE label END IF
  • EXCEPTION block:
#Oracle MySQL
1BEGIN stmts EXCEPTION … END Exception block structure BEGIN DECLARE HANDLER … stmts END
2WHEN DUP_VAL_ON_INDEX Duplicate key DECLARE EXIT HANDLER FOR SQLSTATE '23000'
3WHEN NO_DATA_FOUND No rows found DECLARE EXIT HANDLER FOR NOT FOUND
4WHEN OTHERS All exceptions DECLARE EXIT HANDLER FOR SQLEXCEPTION
http://www.sqlines.com/oracle-to-mysql#plsql-statements
  • パッケージ変数MySQL ではセション変数(@)で代用できる。
  • 結果セットの返却は MySQL では SELECT 文を書くだけ。
  • %TYPE および %ROWTYPE データ型定義は MySQL にはない。

SCTを使った変換手順例

$ git clone https://github.com/awslabs/aws-database-migration-samples.git
  • Instant Client for Linux x86-64 (64-bit) から Oracle Instant Client をダウンロードする。
    • oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
    • oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
  • Oracle Instant Client をインストールする。
$ sudo rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
$ sudo rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
  • インストールされたファイルを確認する。
$ rpm -ql oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64
/usr/bin/sqlplus64
/usr/lib/oracle/12.2/client64/bin/sqlplus
/usr/lib/oracle/12.2/client64/lib/glogin.sql
/usr/lib/oracle/12.2/client64/lib/libsqlplus.so
/usr/lib/oracle/12.2/client64/lib/libsqlplusic.so
$ rpm -ql oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
/usr/lib/oracle/12.2/client64/bin/adrci
/usr/lib/oracle/12.2/client64/bin/genezi
/usr/lib/oracle/12.2/client64/lib/libclntsh.so.12.1
/usr/lib/oracle/12.2/client64/lib/libclntshcore.so.12.1
/usr/lib/oracle/12.2/client64/lib/libipc1.so
/usr/lib/oracle/12.2/client64/lib/libmql1.so
/usr/lib/oracle/12.2/client64/lib/libnnz12.so
/usr/lib/oracle/12.2/client64/lib/libocci.so.12.1
/usr/lib/oracle/12.2/client64/lib/libociei.so
/usr/lib/oracle/12.2/client64/lib/libocijdbc12.so
/usr/lib/oracle/12.2/client64/lib/libons.so
/usr/lib/oracle/12.2/client64/lib/liboramysql12.so
/usr/lib/oracle/12.2/client64/lib/ojdbc8.jar
/usr/lib/oracle/12.2/client64/lib/xstreams.jar
$ vi ~.bashrc
export PATH=$PATH:/usr/lib/oracle/12.2/client64/bin
export NLS_LANG=American_America.UTF8
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/12.2/client64/lib
  • git をインストールする。
$ sudo yum -y install git
  • スクリプトを入手する。
$ git clone https://github.com/awslabs/aws-database-migration-samples.git
$ cd aws-database-migration-samples/oracle/sampledb/v1
$ sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL
SQL> install-rds.sql
  • SCT でソースとターゲットを指定して、変換する。

補足


参考