Google FormからSQL Serverへデータを移行するには

　Google フォームから SQL Server にデータを移行する作業が続いたので，その際に得られた知見を公開する．

Googleフォーム側の設計

「質問を必須にする」にチェックしておこう

　何はともあれ，これが基本だ．空白だらけのアンケートなど何の意味もない．データベースに登録する際に NULL を許容すればするほど，データベースは破綻に一歩近づくと知るべきである．

１ページ目は回答者の基本情報を問え

　後から解析する際，回答者の基本的属性を聞いておくのは基本のきである．学術論文ならば Table 1 に載せる内容である．母集団との乖離を述べる際にも，どういう標本から物を言っているのか，明確にしておかないと信頼に足るものとはならない．

何を聞くか？は回答者を特定する必要があるか？で変わってくる

　回答をネットで募るなら，回答を送付するためのメールアドレスや SNS の ID など，個人を特定できる手段がよい．電話番号などを問われるのは抵抗が大きいという人もいるだろうが，ここら辺りは要件とのバランスになる．

　逆に施設内など範囲が限定されている場合なら，顧客 ID など回答を求める範囲で回答者を特定できるものがよいだろう．社内のデータベースとの結合で年齢や住所など後から分かるものは一々聞かなくてもよい．

回答者を特定する情報

　メールアドレス，SNS の ID, 顧客 ID, 電話番号，保険証番号，マイナンバーなど．後へ行くほど法律上の制約が大きくなる．個人情報保護法をよく読んでから決めて欲しい．

年齢

　生年月日を他で入手可能なら問う必要はないが，入手できないならここで聞くしかなくなる．生年月日を問うと個人情報保護法が…

　一般にはテキストボックスに入力させるが，回答の検証が必要になる．スマホだとリストにしておいてスクロールで選択させる方法もある．

性別

　男性か女性の二択だが，性的マイノリティに配慮するとなると話がややこしくなる．日本では戸籍上の性別が一般的だが，今後は変わっていく可能性もあるため，対応可能なようにラジオボタンやリストの選択肢を増やせるようにしておくべきだろう．

住所

　個人情報保護法に該当する項目である．郵便番号で代用する手もあるが，おそらくこれも法律の制約を受ける．調べてみると分かるが，郵便番号から住所への変換はかなり精度が高く，居住地をほぼ特定できる．

 　Google Map など，地図や運行情報の必要なサービスなら問うておくべき情報だろうな．他にも気象庁のデータと結合して当地の気象状況を知りたいとか．

連絡先

　メールアドレス，SNS の ID, 電話番号など．ネット上で完結させるなら電話番号までは不要だろう．

顧客担当者

　顧客に直接入力してもらうのか，あるいは担当者が代理で入力するかにより必要か否かが分かれるのだが，代理入力の場合は責任を明確にするために必須だ．ログイン認証の際の ID などを残しておく必要があるかもしれない．

IDの誤入力に注意

　ID を手入力する際に一番よくあるのが誤入力だ．QR コードなどで機械式に入力できる環境なら問題ないが，予算や設備に限度がある場合はそうも行かない．この場合，社内の顧客マスターと突き合わせながら入力することになる．人間が入力している時点でミスは避けられないのだが．

検証には正規表現を使うといい

　回答の検証にはいくつかの方法があるが，最も強力なのは正規表現だ．メールアドレス，電話番号，郵便番号，URL など，特定の規則に従った文字列は正規表現で検証できる．

項目	正規表現
メール	[-a-z0-9]+(\.[-a-z0-9]+)*\@[-a-z0-9]+(\.[-a-z0-9]+)*\.[a-z0-9]{2,6}
電話番号	[0-9]{10,11}
郵便番号	[0-9]{7}
URL	https?://[a-z0-9][-a-z0-9]{0,62}(\.[a-z0-9][-a-z0-9]{0,62})*\.[a-z0-9][a-z0-9]{0,62}

日付は必ずつけておけ

　日付は必ず入力させるようにしておきたい．Google フォームの場合はリンクしているスプレッドシートの 1 列目にタイムスタンプが付くようになっているが，これは入力時だけではなく，フォームの回答を触った時にも更新される．入力日を特定するのには役に立たないのである．

個人情報保護法との兼ね合い

　さて，法律のことはあまり詳しくないのだが，企業や自治体など，それなりの立場の組織がアンケートを実施する場合などには，個人情報保護法に配慮する必要がある．一般的には目的外に利用しない，他の組織にむやみに流さないど法律に準拠してますよという姿勢を表明しておくことだ．

条件分岐しない場合，する場合

　条件分岐をさせずに最後まで一本道で貫けるならそれに越したことはない．しかし，往々にしてそうは行かないこともある．

まず，フローチャートを作れ

　アルゴリズムともいうが，想定する範囲内で回答者を分岐させていく．しかし，最終的には最後まで到達させなければならない．あたかも川の流れが最後には海に行き着くように．

　例として国際標準化身体活動質問票 (IPAQ) のフローチャートを示しておく．レイアウトの都合で折り返してあるが，実際には縦長のフローチャートになる．

　フローチャートを作る際に留意すべき点は，次の二つだけだ．行き止まりや無限ループがないか？である．

行き止まりはないか？

　行き止まりとは，それ以上回答を続けられない状態に陥ることだ．技術的な不備でも，論理的な不備でも起こりうる．回答者は行き止まりに陥ると，そこで入力を諦め，フォームを閉じてしまう．これは明らかにフォーム作成側の責任だ．

　フォームは作っただけではだめで，実際に回答し，様々な条件を自分で選択し，分岐先に実際に飛んでみて，最後まで到達できることを確認しなければならない．テストが重要ということだ．

無限ループはないか？

　同様に，無限ループは永久に回答が終わらないため，回答者は怒り出してフォームを閉じることになる．これも技術的な不備が原因だ．

　具体的には，以前のセクションに戻すような分岐をさせてはいけない．形態的には同じでも，以前とは違うセクションに誘導するのである．問うていることは同じでも，セクションは進行している．フローチャートとはそういうものだ．

条件分岐はセクション単位で

　最初にはっきりさせておく．条件分岐させるセクションでは，質問は一つだけにしておくこと．

　なぜなら，同一セクションに複数の質問があって，最初の質問で条件分岐させ，次の質問への回答が必須であったら，そこで行き止まりを発生させることになるからだ．そこで回答者はフォームから離脱してしまう．

条件分岐しないセクションには複数の質問をまとめてもよい

　アンケートなどではいくつかの分野にまたがって質問をすることもあるだろう．その際，同じ分野の質問は自然に同じセクションに集まることになる．条件分岐をしないなら，同一分野の複数の質問を一つのセクションにまとめた方が回答者としては答えやすいだろう．

　ただし，あまりセクションを長くするのは考えものだ．スマートフォンの縦長の画面をスクロールしていくのは意外と神経を使う．回答必須だが入力していない項目があると画面遷移が起こらず，回答者が戸惑うことになる．現実的には，スマホの一画面に収まるように設計するのがよいデザインということになるだろう．

EXCELでの加工

タイムスタンプは不要なら削除

　Google ドライブから EXCEL 形式でダウンロードしたスプレッドシートを開くと A 列がタイムスタンプになっている．不要なら削除して構わない．

何はともあれ，スプレッドシートをテーブルに変換しよう

　次の作業はデータの入っている領域全体をテーブルに変換することだ．これにより以後の作業が楽になる．

時刻型のデータには注意

　現時点でそのまま SQL Server にインポートできないデータ型は時刻型である．本来時間に関するデータはシリアル値と言って，1 日に 1 だけ進むように決められている．時刻や経過時間もその範囲で対応するため，時刻型のデータは 0 から 1 の範囲に収まる．

　SQL Server 2017 では time 型というデータ型が定義されており，このままスプレッドシートからインポートできるものと思っていたが，何故かできなかった．そのため EXCEL 側で分単位の整数型に変換する必要に迫られた．

= IF(@Time = "","", ROUND(@Time * 1440, 0))

　@Time というのは EXCEL でテーブルを挿入した際に定義される時刻型の変数で，配列数式として扱われているようである．スプレッドシートのままだと数式をコピペする必要があるが，テーブルに変換するとこの辺りの手間が省ける．

ファイル名，列名を加工する

Google Form	EXCEL	SQL Server
フォーム名（回答）	ファイル名	テーブル名
質問文全文	列名	列名

　やってみると分かるが， csv ファイル名がテーブル名になり，列のタイトル名がテーブルの列名になる．重複しないように，半角英字で csv ファイルにあらかじめ EXCEL 側で作業しておくのが安全だ．

　Google フォームでは質問文がそのままスプレッドシートの列名になっている．かなり長文になることもあるので，わかりやすい変数名に変更しておくのがよいだろう．もちろん，他の列と重複してはいけない．

　回答のスプレッドシート名はデフォルトで「フォーム名（回答）」などとなっているが，後々データベースにインポートすることを考えると，回答スプレッドシート名そのものをここでテーブル名にしておくのがよいだろう．

　ここらあたりは命名規則，という言葉で検索するといろいろ出てくる．

csvファイルへの出力

　「名前をつけて保存」から「csv ファイル（UTF-8 形式）」を選んで保存する．

SQL Serverへのインポートに際して注意すること

条件分岐と回答必須は衝突しない…フォームでは

　条件分岐により，入力の行われないセクションが発生する．これは最初に述べたところの「すべての回答を必須にする」という原則と衝突するように思えるかもしれないが，実際にはそうはならない．

　しかし，データベース側では話は違ってくる．

条件分岐とはデータベースでNULLを許容するということ

　条件分岐させると，必然的に NULL を許容することになる．IPAQガイドラインをクエリで表現するにはでも述べたが，NULL の扱いは非常に面倒なものとなる．クエリを組む際には精鋭のデータベースエンジニアを担当させるべきだ．

　もっとも，優れたデータベースエンジニアならセクションごとにテーブルも分けて対応するだろう．そうなると，フォームとデータベースの関係が密接なものとなり，フォームのインターフェースの変更がデータベースとの接続の関係に直接影響してくることになる．

インポートにはウィザードを使おう…初心者なら

　T-SQL という SQL Server 専用の言語があるが，初心者には扱いづらい．分かって使うならいいのだが，初心者ならウィザードを使ったほうがいい．

　データベースを右クリックして「タスク」から「データのインポート…」を選び，ウィザードを起動する（直上の「フラットファイルのインポート…」ではない！）．ここをよく間違える．

気をつけるのはデータソースと変換先の選択，それにマッピングの編集だ

　テキストファイルから SQL Server に気象データをインポートするでも述べたが，SQL Server に csv ファイルをインポートする場合には 3 点ほど注意が必要だ．データソース，インポート先，マッピングである．

「データソース」は Flat File Source 一択だ

　選択リストから Flat File Source を選択する．他に選択肢はない．

　厳密に言うと，SQL Server Integration Services がインストールしてあれば他の選択肢もありうる．だが何らかの原因で俺の環境ではインストールできなかった．まっさらな環境でクリーンインストールすれば可能だったのだろう．

インポート時に「データの切り捨てエラー」？詳細設定を見直せ

　超巨大なファイルをインポートしている時に出くわす謎のエラーだが，原因が分かったので追記する．

　フラットファイルソースの選択時に下図のようにオプションで詳細設定できるのだが，このデータ長 (OutputColumnWidth) が短すぎてインポートできずに切り捨てエラーが発生することがある．データ長を十分に大きく取ることだ．

「変換先の選択」は SQL Server Native Client 一択だ

　データソース同様，ここも他に選択肢はない．リストの最下部にある．

「マッピングの編集」には気を使う

　ここは元の csv ファイルからデータベースのテーブルに変換する作業の核心部分である．ここで指定するのはデータ型，データ長および NULL の許可の有無だ．

　国際標準化身体活動質問票の場合，下図のようになる．文字列のデータ型は csv ファイルが UTF-8 の場合，nvarchar 型または nchar 型が無難である．varchar 型や char 型はエラーが発生してインポートが止まる．エラーが発生してから後戻りしても訂正は不可能で，データベースごと作り直す必要があるため，慎重に．

増分を繰り返しインポートするなら

　初めてインポートする際には「列マッピング」画面では「変換先テーブルを作成する」にチェックが入っている．

　しかし 2 回目以降のインポートでは，デフォルトで「変換先テーブルに行を追加する」にチェックが入っている．これは増分のみを追加するオプションである．

　Google フォームでは前回インポートしたデータもスプレッドシートでダウンロードすることになるため，それでは前回のデータと重複してしまう．「変換先テーブル内の行を削除する」をチェックして選択する．一旦テーブルから全データを削除し，改めてインポートし直すわけだ．

主キーがない！しかしクエリは動く

　最初，テーブルに主キーがないことに違和感を感じた．しかし，クエリは問題なく動作する．問題があるとすればデータの重複をチェックできないことである．実際に業務で使用するには支障があるが，個人が分かって使うなら問題はない…かも知れない．

　SQL Server Integration Services でインポートすればテーブルの主キーを指定できるようだが，あいにくそこまでできる環境にない．

名前のデータ長を 50 文字まで拡張してはどうか？

　外国人の氏名など，いろいろな公的機関で本名が登録できずにトラブルが発生しているようである．文字数制限はシステム側の責任なのだが，システムの更新には費用がかかって…という言い訳はいいから，nvarchar (50) と一気に拡張してしまえばいい．