【BULK INSERT】SQL Serverのバルクインサートについて手順と注意点を図解でわかりやすくご紹介します【大量取込】

header2

SHARE

目次 非表示

  1. まず初めに
  2. バルクインサートとは
  3. バルクインサートの実行手順
    1. 手順① 取込データの確認・配置
    2. 手順② 実行するSQL文の確認
    3. 手順③ SQLの実行・結果確認
  4. バルクインサートにおける注意点
    1. 取込項目数は明確に制御しておくのが大事
    2. エラーにならないが故の弊害
    3. 項目数チェックを必ず設定しておきましょう
  5. SQL Serverにおけるその他の取り込み方法
  6. まとめ
目次へ

まず初めに

みなさん、こんにちは。はねすけと申します。

本日はMicrosoft社が提供するデータベースであるSQLServerにおける、大量データの取り込み手法である「バルクインサート」について、使い方及び、その際の注意点についてご紹介したいと思います。

趣味レベルではそんなに大量のデータを扱うことは少ないのですが、実務では膨大なデータを扱うことも少なくありません。ぜひマスターして業務に活用してみてくださいね。

それでは早速行ってみましょう！

こんな方はぜひ読んでみてくださいね

・SQL Serverの使い方にまだ不慣れな方

・データの正しい取り込み方法に自信のない方

バルクインサートとは

header2-1

まずは基礎的な部分、そもそもBULK INSERT(バルクインサート)とは何なのかを簡単に振り返っていきたいと思います。
冒頭でも軽く触れましたが、バルクインサートはMicroSoft社が提供するRDB(リレーショナルデータベース)に対してSQLで大量のデータを取り込むための手法のことです。

大量というとどのくらい？と思うかもしれませんが、目安でいうと数十万件以上くらいでしょうか。
データベースにSQLでデータを取り込むと言えば、まず浮かぶのはINSERT文だと思います。INSERT文を使うためには、以下のように取り込みたい内容をその都度定義する必要がありますよね。

SQL
INSERT INTO users (user_id, username, email, birthdate)
VALUES (1, 'JohnDoe', 'john.doe@example.com', '1990-01-01');

しかし、バルクインサートを使えばCSVファイルなどからまとめてデータを取り込むことができるのです。
個人開発など小規模な場面ではなかなか登場しないかもしれませんが、実務などではけっこう活用する機会があるのがこのバルクインサートです。
効率的なデータ解析作業を進めるためには習得必須な技術だと思いますので、ぜひしっかりと習得していきましょう！

バルクインサートの実行手順

header2-2

次にバルクインサートの実行手順を簡単にご紹介していこうと思います。
ここでは、SQL ServerへのGUIベースでの操作を可能にするソフトウェアであるSQL Server Management Studio(SSMS)を使っていきます。
以下の記事でSQL Serverのインストール～起動までをご紹介していますので、まだ使ったことがないという方はぜひ触れてみてくださいね。

header17【python】DjangoでSQLServerへ接続する手順を図解で初心者にもわかりやすく解説【ライブラリ】

手順① 取込データの確認・配置

まずは今回取り込むサンプルデータを確認しましょう。

NoNameType
1applefruits
2bananafruits
3baseballsports
4glapefruits
5soccersports
6foxanimal

サンプルなのでデータ数は少ないですが、No/Name/Typeという３列の情報からなるデータです。
これをCSV形式で保存して、任意の場所に配置します。

tezhun1

今回はCドライブ直下に「bulkinsert」というフォルダを作成し、CSVファイルを配置しました。ファイルは「test_data.csv」ですね。
ここで１点注意点があります。ファイルパスに日本語が入らないようにしましょう。取込の際にファイルパスを指定するのですが、そこに日本語が入っていると文字コードの関係で取込が上手くいかないケースがあります。
特にこだわりがなければ日本語が入らないようなファイルパスにしておくのが無難ですね。
※今回のケースでは、ファイルパスは「C:\bulkinsert\test_data.csv」となります。

手順② 実行するSQL文の確認

次にバルクインサートを実行するためのSQL文の内容を確認していきましょう。

SQL
-- バルクデータをテーブルに挿入
BULK INSERT SampleTable
FROM 'C:\bulkinsert\test_data.csv'
WITH
(
    FIELDTERMINATOR = ',', -- データの区切り文字
    ROWTERMINATOR = '\n',  -- 行の区切り文字
    FIRSTROW = 2,          -- 最初の行はヘッダーなどがある場合にスキップ
    TABLOCK               -- バルクインサート対象のテーブルが他のトランザクションによって変更されないようにロック
)

上記が今回実行するバルクインサートのSQL文です。一行ずつ何を行っているのかを確認していきましょう！

  • １行目「BULK INSERT テーブル名」…今回は「SampleTable」というテーブルを指定していますね。もしテーブルが存在していなければ事前に作成しておきましょう。
  • ２行目「FROM 取込ファイルパス」…FROM句には取込ファイルパスを指定します。手順①で配置したCSVファイルのパスを指定しています。
  • ３行目～７行目「WITH～」…ここではバルクインサート実行時のオプションを指定します。上記SQL文のコメントで記載しているように、何行目から取り込むか・区切り文字を何にするかなど指定することができます。

実行時オプションについては、上記のほかにも様々なものがあります。もっと詳しく知りたい方は公式サイトで確認してみてくださいね。

参考
BULK INSERT (Transact-SQL) – SQL Server

手順③ SQLの実行・結果確認

諸々の準備ができたので、実行してみましょう。ここからは最初にお伝えした通りSSMSを使用していきます。

tezhun3
①SSMSを起動したら、事前に用意したバルクインサートのSQL文を貼り付けましょう。
②「Execute」ボタンを押して実行しましょう。
③実行が問題なく成功したら、以下のように取り込んだ内容が表示されるはずです。

上記の通りのデータ・SQL文であればエラー無く実行できると思います。
もし何かエラーが発生した場合はデータの内容が取り込みたいテーブルの形式に適していない・取込ファイルパスが正しくないなどが主に考えられます。いずれにせよエラーメッセージに詳細が記載されているはずですので、焦らずに読み取って解決していきましょう。

バルクインサートにおける注意点

header2-3

この記事の中で挙げたい注意点はたった一つだけ、「取り込み先のテーブル定義は確実に」ということです。

取込項目数は明確に制御しておくのが大事

一見問題なさそうに見えるが…

この章で挙げたい注意点はたった一つだけ、「取り込み先のテーブル定義は確実に」ということです。

バルクインサートは基本的にカンマなどを項目の区切り位置として、CSVファイル等が保持している情報をテーブルに取り込みます。

では例えばテーブルの列数が10である場合に、カンマ区切りで12項目あるデータを取り込むとどのようになってしまうのでしょう？

【イメージ】
　テーブル定義：[列1], [列2], [列3], [列4], [列5], [列6], [列7], [列8], [列9], [列10]
　取込項目定義：[列1], [列2], [列3], [列4], [列5], [列6], [列7], [列8], [列9], [列10], [列11], [列12]

その答えは「普通に取り込み自体は成功してしまう」です。
果たしてそのことがどのような影響をもたらすことになるのでしょうか…？

エラーにならないが故の弊害

成功するのであれば問題ないじゃん。というご意見もあるかと思いますが、そう単純なお話ではありません。なぜなら取り込まれたデータはけして正常な状態になっていないからです。

具体的には、過剰な分の項目がすべて最終列にまとめて入れられてしまう状態になります。

取込結果：[列1], [列2], [列3], [列4], [列5], [列6], [列7], [列8], [列9], [列10, 列11, 列12]

のような状態になってしまうわけです。

これでは結局その後にデータを活用する際に不都合が生じてしまいます。自動でエラーとなってくれればきづけるのですが、エラーとはならないため逆に厄介なのです。

個人開発のシステム程度であれば再度取込直しをすれば良いかもしれませんが、業務システムだと大きな影響・損失につながってしまう可能性もあります。

項目数チェックを必ず設定しておきましょう

ではどのようにして、上記のような項目数のズレに対処すれば良いのでしょうか。

対処としてはいくつか考えられますが、一番有効なのは取込前における取込ファイルに対してのバリデーションチェックでしょう。
バリデーションチェックとは入力値などに対して様々な確認作業を行うことを指します。
例えば入力された文字数のチェックや数字かアルファベットかの確認など、その内容は業務要件により多岐に渡ります。

今回のケースでいえば、取込ファイルの項目数に対して想定している数かどうかのチェックを行うわけですね。

アプリ側から実行する場合などであれば、必ず取り込みファイルの項目数チェックを入れるようにし、不適切なデータ状態にならないようしっかりと事前に対策を行うと良いでしょう。

SQL Serverにおけるその他の取り込み方法

header2-4

最後に余談にはなりますがバルクインサートを使用しないデータ取込の方法をご紹介します。
それほど大量のデータではないもののCSVからデータを取り込みたい、そういったケースも多々あると思います。BULK INSERTのSQL文はそれなりに複雑なので毎回用意するのはちょっと手間ですよね。そういった場合のためにSSMSの機能を使いサッと取り込む方法をお伝えします。

ssms_insert_1
①SSMS上での操作を行います。画面左に表示されている「Object Explorer」にて、取込を実施したいデータベースのメニューを選択していきます。対象のデータベースで右クリック＞「Tasks」＞「Import Flat File…」を選択。
ssms_insert_2
②上記のようなファイル取込画面が起動します。この画面では特に気にせず「Next＞」ボタンを押します。
ssms_insert_3
③「Browse…」ボタンを押し、取り込みたいファイルを指定します。
④取り込むと新しいテーブルにデータがセットされますので、そのテーブル名を任意に指定します。
⑤「Next＞」ボタンを押します。
ssms_insert_4
⑥取込の情報がプレビューされますので、問題ないか確認しましょう。ちなみにファイルデータの先頭行が作成するテーブルの列名として自動セットされます。
⑦プレビュー内容に問題なければ「Next＞」ボタンを押してください。
ssms_insert_5
⑧テーブルの各列について、型やキー情報の設定を変更できます。取込データの形式と合致していないと取込時にエラーとなりますので、慎重に正しい情報に設定しましょう。
⑨設定内容に問題なければ「Next＞」ボタンを押してください。
ssms_insert_6
⑩特に気にせず「Finish」ボタンを押します。
ssms_insert_7
⑪上画像のように「Success」と表示されていれば取込成功です。エラーが発生している場合は、原因も表示されますので、焦らず設定内容を調整していきましょう。
ssms_insert_8
上画像のように取り込んだテーブルに対してSELECT文を実行すると、問題なく取込できていることが確認できますね。

まとめ

いかがだったでしょうか？

本日お伝えしたかった内容を簡単にまとめると以下のようになります。

ぜひ何らかの形でお役に立てれば幸いです。

それでは次の記事でまたお会いしましょう♪

本日のまとめ

SQLServerのバルクインサート時には、取込項目数とテーブル定義を要確認！

大量データの取り込み＝バルクインサートの活用を意識しましょう

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

次の記事

【体験談】福岡在住のUターンエンジニアの働き方事例紹介！地方で…
header4