GDPRを遵守するためにアプリケーションに必要となる機能

あるシステムがEU域内の個人データを扱う場合、GDPRの遵守はシステムの運用主体が負うことになるわけだけれど、とはいえGDPRに対応するための機能自体がシステムに入ってないと問題になることは見えているので、機能要件に織り込んでおく必要があるわけです。

そこで、「個人データの取扱いと関連する自然人の保護に関する、及び、そのデータの自由な移転に関する、並びに、指令95/46/EC を廃止する欧州議会及び理事会の2016 年4 月27 日の規則(EU) 2016/679（一般データ保護規則）」の前文と条文の中から、アプリケーションの機能に関わりそうな箇所をピックアップしてみました。

前文

(30) 自然人は、インターネットプロトコルアドレス、クッキー識別子、又は、無線識別タグのようなその他の識別子といったような、当該自然人のデバイス、アプリケーション、ツール及びプロトコルによって提供されるオンライン識別子と関連付けられうる。

IPアドレスやJSESSIONIDをログ出力する際に注意が必要。同意が取れていない場合はログにIPアドレスやJSESSIONIDを保存できないことになる。Logbackの場合、MDCで同意が取れているリクエストのみIPアドレスやJSESSIONIDをログ出力するよう制御できる。

(32) この同意は、インターネット Web サイトを訪問する際にボックスをチェックすること、情報社会サービスのための技術的な設定を選択すること、又は、この文脈において、自己の個人データについて提案された取扱いについてのデータ主体の承諾を明確に示す上記以外の陳述又は行為を含みうる。それゆえ、沈黙、予めチェック済みのボックス又は不作為は、同意を構成するものとしてはならない。

同意のためのチェックボックスは、デフォルトOFFにする。

条文

第5条

1 . (f) 無権限による取扱い若しくは違法な取扱いに対して、並びに、偶発的な喪失、破壊又は損壊に対して、適切な技術上又は組織上の措置を用いて行われる保護を含め、個人データの適切な安全性を確保する態様により、取扱われる。

個人データに対するアクセス制御の実装が必要。

第7条

1 . 取扱いが同意に基づく場合、管理者は、データ主体が自己の個人データの取扱いに同意していることを証明できるようにしなければならない。

同意したことの記録はDB上に永続化する。または、特定のセッションIDのユーザが取り扱いに同意したことをログへ出力する。

3 . データ主体は、自己の同意を、いつでも、撤回する権利を有する。

同意したことの記録はDB上で更新できるようにする。または、特定のセッションIDのユーザが取り扱いの同意を撤回したことをログへ出力し、以降はログ出力時の識別子をマスクする *1。

第8条

1 . ... その子どもが16 歳未満の場合、そのような取扱いは、その子どもの親権上の責任のある者によって同意が与えられた場合、又は、その者によってそれが承認された場合に限り、かつ、その範囲内に限り、適法である。 加盟国は、その年齢が 13 歳を下回らない限り、法律によって、それらの目的のためのより低い年齢を定めることができる。

記録の対象が16歳（または導入先の国の法律によって設定された年齢）未満か以上かで処理を分ける必要がある。または、単純に16歳未満の利用を禁止できるようにする。

第12条

2 . ... 第15条から第22条に基づくデータ主体の権利の行使を容易にするものとする。

記録している個人データは照会可能にする必要がある。保存しっぱなしはマズい。

3 . ... 第15条から第22条に基づく要求に基づいて行われた行為に関する情報を提供する。

個人データに対するアクセスログを監査証跡として保存する必要がある。第30条も同じ。プレーンテキストのログへアクセス記録を出力する場合は、個人データの主キー*2を併せて出力しておかないと識別が難しい。

第13条

2 . (a) その個人データが記録保存される期間、又は、それが不可能なときは、その期間を決定するために用いられる基準。

個人情報の保存時刻をレコードのカラムとして持った上で、保存期限を過ぎたデータは消去が必要。

2 . (b) 個人データへのアクセス、個人データの訂正又は消去、又は、データ主体と関係する取扱いの制限を管理者から得ることを要求する権利、又は、取扱いに対して異議を述べる権利、並びに、データポータビリティの権利が存在すること。

個人データはまとめてエクスポートできる必要がある。

第15条

1 . (c) 個人データが開示された、又は、個人データが開示される取得者若しくは取得者の類型

個人データに対するアクセスログを監査証跡として保存する必要がある。第30条も同じ。

第30条

1 . 個々の管理者、及び、該当する場合、管理者の代理人は、その責任において、取扱活動の記録を保管する。

個人データに対するアクセスログを監査証跡として保存する必要がある。実装方法としては、DBのテーブルにアクセス記録用のカラムを設ける、DBの監査証跡機能を利用する、などが思いつく。

*1:注意: 同意を撤回した場合、記録は遡って消去する必要がある？

*2:個人を識別する情報そのものではマズいので、内部的なサロゲートキー的なIDが必要となる。

Read from .xlsx in PowerShell (x64) without using Excel

This article shows how to read .xlsx in PowerShell without Excel. In Windows 10 (x64), you will have to do additional work.

• You have to install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe) when you use Windows PowerShell (x64). You will have to install AccessDatabaseEngine.exe when you use Windows PowerShell (x86).
• You have to use Provider=Microsoft.ACE.OLEDB.12.0 instead of Provider=Microsoft.Jet.OLEDB.4.0.
• You have to use Extended Properties=Excel 12.0 instead of Extended Properties=Excel 8.0.
• ADO can handle unicode characters in .xlsx files. Note that PowerShell ISE, cmd.exe and powershell.exe has some limitations:
  • The console in PowerShell ISE cannot display the characters not included in the default locale.
  • The terminal window for cmd.exe and powershell.exe cannot display the surrogate pairs.

$fileName = "C:\tmp\UnicodeTest.xlsx"
$sheetName = "sheet1$"
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$query = "Select * from [$sheetName]"

$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($query)
$sqlCommand.Connection = $conn
$conn.open()
$dataReader = $sqlCommand.ExecuteReader()

$list = New-Object System.Collections.ArrayList
While ($dataReader.read())
{
    $list.Add($dataReader[1].ToString()) > $null
}
$list | % { $_ } | Out-File -Encoding UTF8 c:\tmp\UnicodeTest.txt

$dataReader.close()
$conn.close()

Launch PowerShell process in non-default culture (locale)

In PowerShell on Windows 10, you cannot change the current culture (locale) with chcp command.

If you want to launch a new PowerShell process in another culture, once run cmd.exe from PowerShell, execute chcp, and launch powershell.exe from cmd.exe.

This is useful when you want to get an error message in en-US culture to ask questions in forums like Stack Overflow.

PS C:\> [Threading.Thread]::CurrentThread.CurrentUICulture.Name
ja-JP
PS C:\> cmd.exe
Microsoft Windows [Version 10.0.18362.836]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\>chcp 437
Active code page: 437

C:\>powershell.exe
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

Try the new cross-platform PowerShell https://aka.ms/pscore6

PS C:\> [Threading.Thread]::CurrentThread.CurrentUICulture.Name
en-US
PS C:\>

pgbenchでINSERT中心のトランザクションのTPSをできるだけ高くする

動機

DBのチューニング方法なんかの書籍を見てみても、性能向上方法として語られているのはSELECTのチューニングが主で、INSERTに関する記載はあまり見当たりませんでした。ですが、大量のデバイスから飛んできたデータをMQTTやらHTTPやらで受けて、それを生のままDBMSに登録する……というワークロードの場合、INSERTがトランザクションの中心になるので、DBMSのベンチマークもINSERT中心のワークロードにする必要があります。

DBMSがPostgreSQLの場合、性能単価の測定にはpgbenchを使うと思いますが、ここで設定を誤っていたりすると、PostgreSQLの実力値を見誤ったりします（結果、DBサーバにオーバースペックなマシンを用意してしまったりする）。ハードウェアのリソース設計はそれはそれで行うとして、ここでは主にpgbenchによる性能単価測定の際にTPSをできるだけ高くする方法（＝pgbenchの設定がマズくてPostgreSQLの性能を過少に見積らない方法）をまとめておきます。

前提とするワークロード

• テーブルはpgbench組込みのトランザクションで使われるpgbench_historyを使います。
• 1回のトランザクションで8レコードをINSERTします。
• INSERT以外のトランザクションは実行しません（無風測定です）。
• 同時接続クライアント数は20、実行時間20秒で確認しました。
• ここではオプションの有効性の確認を目的として、pgbenchとPostgreSQLを同じマシンで実行しています。正確なTPSを知りたい場合はpgbenchはPostgreSQLとは別マシンで実行してください（後述）。
• OSはWindows 10です。
• CPUはIntel(R) Core(TM) i7-8750H CPU @ 2.20GHz（6コア12スレッド）、ストレージはADATA SU800NS38を使っています。

何もしない場合

以下のようなスクリプトファイルを作成して実行します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

pgbenchの実行オプションは以下の通りです。

pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert.pgbench test

この場合のTPSは3863TPSでした。

transaction type: C:\tmp\insert.pgbench
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
duration: 20 s
number of transactions actually processed: 77323
latency average = 5.177 ms
tps = 3863.290450 (including connections establishing)
tps = 3873.891699 (excluding connections establishing)

いろいろ対策をほどこす

CPUの割り当てコア数を調整する（プロセッサアフィニティを設定している場合・DBと同じマシンでpgbenchを実行する場合）

PostgreSQLインスタンスに対するCPUの割り当てに応じて、TPSが高くなります。プロセッサアフィニティを設定している場合、PostgreSQLに割り当てるCPUコアを増やします。ただし、DBと同じマシンでpgbenchを実行する場合、すべてのCPUをPostgreSQLに割り当てると逆にTPSが下がるようです（pgbenchに割り当てるCPUがなくなるせい？）。そのためCPUアフィニティを調整して、いちばん高い性能が出るポイントを探ります。

Windowsの場合、サービスプロセスにはプロセッサアフィニティは設定できないので、PostgreSQLサービスは停止し、ユーザプロセスとしてPostgreSQLインスタンスを実行します。以下のようなPowerShellスクリプトを作成し、割り当てるCPUのスレッド数を1～12まで変化させて実行しました。

@('1','3','7','F','1F','3F','7F','FF','1FF','3FF','7FF','FFF') | ForEach-Object {
  Write-Host "`nCPU Affinity:" $_
  cmd.exe /c "start /affinity $_ /B c:\opt\PostgreSQL\12\bin\pg_ctl.exe start -w -s -D C:\opt\PostgreSQL\12\data"
  $env:PGPASSWORD='postgres'
  c:\opt\PostgreSQL\12\bin\pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert2.pgbench test
  c:\opt\PostgreSQL\12\bin\pg_ctl.exe stop -D "C:\opt\PostgreSQL\12\data"
}

結果は以下のグラフのようになりました。以下、設定を変えながら上記のスクリプトを実行していきます。 何もしない場合、いちばん性能がよいのは全6コア・12スレッドを使った場合で、3863TPSでした。

設定ごとのpgbench TPS数

バルクインサート

スクリプトファイルを修正し、レコードごとにINSERTを実行していたところを、バルクインサートを行うよう修正します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、9375TPSになりました。

pgbenchのオプションに--protocol=preparedを指定

pgbenchとPostgreSQLの間の通信プロトコルは、デフォルトでは簡易問い合わせプロトコルになっています。ここで--protocol=extendedを設定すると拡張問い合わせプロトコルが、--protocol=preparedを指定するとプリペアドステートメントを伴う拡張問い合わせプロトコルが使用されます。ここではもっとも性能が高くなる--protocol=preparedを指定します。

バルクインサートと合わせて指定した場合、PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、11823TPSになりました。

postgresql.confでsynchronous_commit = offを指定

postgresql.confでsynchronous_commit = offを指定することで、DBがクラッシュした際に直近のコミット分のデータがロストするのと引き換えに、PostgreSQLの性能を上げることができます（データ破壊のリスクはありません）。

バルクインサート・--protocol=preparedと合わせて指定した場合、PostgreSQLに4コア・7スレッドを使った場合が最も性能が高く、13349TPSになりました。

pgbenchのオプションに-j nを指定

pgbenchはデフォルトではシングルスレッドで動作します。-jオプションで同時実行スレッド数を変更できます。 pgbenchのスレッド数を変更しながら試した結果、pgbenchに4スレッド、PostgreSQLに4コア・8スレッドを割り当てた場合が最も性能が高く、24007TPSになりました。

pgbenchをDBと別マシン（または別のCPUコア）で実行する

pgbenchでは10000TPS程度で1CPUを1スレッド使いきってしまうようです（パフォーマンスモニタでpgbenchのProcessor %が100程度になる）。上記の通りINSERTの性能はCPU-boundなようです。pgbenchがCPUを食うとそれだけベンチマークの結果に影響するので、（今回は行えませんでしたが）pgbenchはDBとは別マシン（または別のCPUコア）で実行した方がよいTPSが出るようです。上記のグラフが8スレッド以降で伸びていないのは、pgbenchの実行にまわせるCPU数が少なくなってしまっているためと思われます。

常用漢字表を新旧漢字変換の根拠資料に使う場合の制約

文化庁の出している常用漢字表 *1 には康煕字典体と常用漢字の対応が記載されている。ただ詳しく調べてみたら、対応の記載自体に以下のようなコーナーケースがあるみたい。

• 「著しい差異のないものは省」かれているので「頬⇄頰」のような対応は記載がない。
• 康熙字典体と常用漢字が一対一対応しない場合に、記載のないケースが見受けられる。たとえば「辺⇄邊」はあっても「辺⇄邉」はない、「闘⇄鬭」はあっても「闘⇄鬪」はない。
• 当用漢字表と常用漢字表で字体が変わったものは記載がない。たとえば「礼⇄禮」はあっても「礼⇄礼」はない。

ほかにも、出典が常用漢字表なので、新字が常用漢字でないものは記載がない。たとえば「鴈⇄雁」はないし、「禎⇄禎」もない *2 。 新字・旧字の変換に使おうと思ったら、常用漢字表自体を全数として処理する場合以外は注意が必要そう。

*1:https://www.bunka.go.jp/kokugo_nihongo/sisaku/joho/joho/kijun/naikaku/pdf/joyokanjihyo_20101130.pdf

*2:この手の漢字は安岡孝一「人名用漢字の新字旧字」（ https://dictionary.sanseido-publ.co.jp/columncat/%e6%bc%a2%e5%ad%97/%e4%ba%ba%e5%90%8d%e7%94%a8%e6%bc%a2%e5%ad%97%e3%81%ae%e6%96%b0%e5%ad%97%e6%97%a7%e5%ad%97 ）にたくさん例がある。