ColumnStoreへの一括データロード

概要

cpimportはColumnStoreのテーブルへ高速で効率的なデータ挿入を実現する一括ロードユーティリティです。このユーティリティはデータをデリミタ(区切り文字)で区切ったテキストファイルを入力とします。既定の区切り文字は('|')ですが、コンマなどを使うことも可能です。データは、CREATE TABLE文と同じ順番に並んでいる必要があり、日付の値は'yyyy-mm-dd'形式で入力する必要があります。

cpimport – MariaDB ColumnStoreデータベースへデータをインポートする際に以下の操作を行います。

• データを指定されたフラットファイルから読み込む。
• データをColumnStoreのカラム型ストレージに合うように変換する。
• 冗長データは符号化され、論理的に圧縮される。
• データがディスクに書き込まれる。

以下が重要なポイントとなります。

• 一括ロードはテーブルにに対する追加操作となるため、既存のデータは読み込み可能で、処理中は影響を受けません。
• 一括ロードはトランザクションログに出力しません。これらはトランザクション処理ではありませんが、アトミックな操作とみなすことは可能です。しかし、バルク操作が行われたという目印はログに出力されるため、バルク操作が行われたことを判別することは可能です。
• ロード処理が終了すると、それぞれのカラムのハイウォータマークはアトミックな操作の中で移動され、新たに読み込んだデータを読み込むことが可能となります。この追加処理は、一貫性のある読み込みをサポートしつつ、データロギングのオーバーヘッドを発生させません。

cpimportユーティリティを使用するには二つの主なステップがあります。

1. (オプション) ジョブファイルを作成し、一つのフラットファイルから複数テーブルへロードするようにします。
2. cpimportを実行しデータをインポートします。

構文

最もシンプルなcpimportコマンドの実行方法は以下の通りです。

cpimport dbName tblName [loadFile]

cpimportのオプションの詳細を以下に示します。

cpimport dbName tblName [loadFile]
[-h] [-m mode] [-f filepath] [-d DebugLevel]
[-c readBufferSize] [-b numBuffers] [-r numReaders]
[-e maxErrors] [-B libBufferSize] [-s colDelimiter] [-E EnclosedByChar]
[-C escChar] [-j jobID] [-p jobFilePath] [-w numParsers]
[-n nullOption] [-P pmList] [-i] [-S] [-q batchQty]

positional parameters:
	dbName     Name of the database to load
	tblName    Name of table to load
	loadFile   Optional input file name in current directory,
			unless a fully qualified name is given.
			If not given, input read from STDIN.
Options:
	-b	Number of read buffers
	-c	Application read buffer size(in bytes)
	-d	Print different level(1-3) debug message
	-e	Max number of allowable error per table per PM
	-f	Data file directory path.
			Default is current working directory.
			In Mode 1, -f represents the local input file path.
			In Mode 2, -f represents the PM based input file path.
			In Mode 3, -f represents the local input file path.
	-l	Name of import file to be loaded, relative to -f path,
	-h	Print this message.
	-q	Batch Quantity, Number of rows distributed per batch in Mode 1
	-i	Print extended info to console in Mode 3.
	-j	Job ID. In simple usage, default is the table OID.
			unless a fully qualified input file name is given.
	-n	NullOption (0-treat the string NULL as data (default);
			1-treat the string NULL as a NULL value)
	-p	Path for XML job description file.
	-r	Number of readers.
	-s	'c' is the delimiter between column values.
	-B	I/O library read buffer size (in bytes)
	-w	Number of parsers.
	-E	Enclosed by character if field values are enclosed.
	-C	Escape character used in conjunction with 'enclosed by'
			character, or as part of NULL escape sequence ('\N');
			default is '\'
	-I	Import binary data; how to treat NULL values:
			1 - import NULL values
			2 - saturate NULL values
	-P	List of PMs ex: -P 1,2,3. Default is all PMs.
	-S	Treat string truncations as errors.
	-m	mode
			1 - rows will be loaded in a distributed manner across PMs.
			2 - PM based input files loaded onto their respective PM.
			3 - input files will be loaded on the local PM.

cpimportモード

モード1：一つのデータファイルから対象モジュールに一括読み込みを行います。

このモードでは、対象モジュール(ユーザーモジュールかパフォーマンスモジュールのいずれか)の場所でcpimportを実行します。ソースファイルはそのモジュールの場所にあり、cpimportは全てのパフォーマンスモジュールに分散されます。もしモードの指定がない場合、このモード1が規定の動作となります。 cpimportが実行される場所は、ユーザーモジュールかパフォーマンスモジュールのいずれかになります。

Example
cpimport -m1 mytest mytable mytable.tbl

モード2：複数の分散されたデータファイルから対象モジュールに一括読み込みを行います。

このモードでは、対象モジュール(ユーザーモジュールかパフォーマンスモジュールのいずれか)の場所でcpimportを実行します。ソースファイルは既に分割された状態でパフォーマンスモジュール上に存在しています。それぞれのパフォーマンスモジュールは同名のファイルを持ち、パフォーマンスモジュールごとに分割されたデータを持っています。

Example
cpimport -m2 mytest mytable /home/mydata/mytable.tbl

モード3：並行分散一括読み込みを行います。

このモードでは、cpimportをそれぞれのパフォーマンスモジュール上で個別に実行します。ソースファイルはパフォーマンスモジュール上に存在し、並列して、パフォーマンスモジュール上の同じテーブルにデータがロードされます。

Example
cpimport -m3 mytest mytable /home/mydata/mytable.tbl

メモ：

• 一括ロードはテーブルにに対する追加操作となるため、既存のデータは読み込み可能で、処理中は影響を受けません。
• 一括ロードはトランザクションログに出力しません。これらはトランザクション処理ではありませんが、アトミックな操作とみなすことは可能です。しかし、バルク操作が行われたという目印はログに出力されるため、バルク操作が行われたことを判別することは可能です。
• ロード処理が終了すると、それぞれのカラムのハイウォータマークはアトミックな操作の中で移動され、新たに読み込んだデータを読み込むことが可能となります。この追加処理は、一貫性のある読み込みをサポートしつつ、データロギングのオーバーヘッドを発生させません。

標準入力からの一括ロード

loadFileパラメータを指定しないことにより、標準入力からColumnStoreへ一括ロードすることが可能です。

例：

cpimport db1 table1

SELECT FROMでテーブルから取得したデータの一括ロード

標準入力へは、パイプを通して、SELECT文実行結果を直接cpimportへ取り込むことが可能です。SELECT文はMyISAMやInnoDBなどColumnStore以外のテーブルからとってくることが可能です。以下の例では、db2.source_tableからデータが取得され、-Nフラグを使ってデータがない部分を処理しています。-qフラグによりmysqlクライアントに結果をキャッシュしないように指定しています(タイムアウトやロード失敗を避けるため)。

mcsmysql -q -e 'select * from source_table;' -N db2 | 
/usr/local/mariadb/columnstore/cpimport -j501 -s '\t' -f STDIN

複数テーブルへの一括ロード

複数テーブルへ一括でデータを投入するには2通りあります。

1. 複数のcpimport jobを実行します。importごとのテーブルはユニークであるか、またはそれぞれのインポートごとのユーザーモジュールはユニークである必要があります。

1. colxmlユーティリティを使用：colxmlはデータをインポートする前にデータベーススキーマのXML jobファイルを作成します。 多くのテーブルはいずれかのスキーマ内で全テーブルか、またはある指定テーブルを、colxmlで-tオプションを使ってインポートします。その後、colxmlから生成されたjobファイル、cpimportを使います。ここに、全てのテーブルデータをcolxmlとcpimportでインポートする例を示します。

colxml mytest -j299
cpimport -m1 -j299

colxml構文

Usage: colxml [options] dbName

Options: 
   -d Delimiter (default '|')
   -e Maximum allowable errors (per table)
   -h Print this message
   -j Job id (numeric)
   -l Load file name
   -n "name in quotes"
   -p Path for XML job description file that is generated
   -s "Description in quotes"
   -t Table name
   -u User
   -r Number of read buffers
   -c Application read buffer size (in bytes)
   -w I/O library buffer size (in bytes), used to read files
   -x Extension of file name (default ".tbl")
   -E EnclosedByChar (if data has enclosed values)
   -C EscapeChar
   -b Debug level (1-3)

colxml使用例

次のテーブルはtpch2というデータベース名です。

MariaDB[tpch2]> show tables;
+---------------+
| Tables_in_tpch2 |
+--------------+
| customer    |
| lineitem    |
| nation      |
| orders      |
| part        |
| partsupp    |
| region      |
| supplier    |
+--------------+
8 rows in set (0.00 sec)

1. 最初に/usr/local/mariadb/columnstore/data/bulk/data/importにそれぞれのテーブルの入力となるデータファイルを配置します。それぞれのファイルは<テーブル名>.tblとします。
2. colxmlを実行し、tpch2データベースへロードします。

/usr/local/mariadb/columnstore/bin/colxml tpch2 -j500
Running colxml with the following parameters:
2015-10-07 15:14:20 (9481) INFO :
Schema: tpch2
Tables:
Load Files:
-b 0
-c 1048576
-d |
-e 10
-f CSV
-j 500
-n
-p /usr/local/mariadb/columnstore/data/bulk/job/
-r 5
-s
-u
-w 10485760
-x tbl
File completed for tables:
tpch2.customer
tpch2.lineitem
tpch2.nation
tpch2.orders
tpch2.part
tpch2.partsupp
tpch2.region
tpch2.supplier
Normal exit.

ここでは実際にcpimportを実行し、colxmlによってセイセされたジョブファイルを使用します。

/usr/local/mariadb/columnstore/bin/cpimport -j 500
Bulkload root directory : /usr/local/mariadb/columnstore/data/bulk
job description file : Job_500.xml
2015-10-07 15:14:59 (9952) INFO : successfully load job file /usr/local/mariadb/columnstore/data/bulk/job/Job_500.xml
2015-10-07 15:14:59 (9952) INFO : PreProcessing check starts
2015-10-07 15:15:04 (9952) INFO : PreProcessing check completed
2015-10-07 15:15:04 (9952) INFO : preProcess completed, total run time : 5 seconds
2015-10-07 15:15:04 (9952) INFO : No of Read Threads Spawned = 1
2015-10-07 15:15:04 (9952) INFO : No of Parse Threads Spawned = 3
2015-10-07 15:15:06 (9952) INFO : For table tpch2.customer: 150000 rows processed and 150000 rows inserted.
2015-10-07 15:16:12 (9952) INFO : For table tpch2.nation: 25 rows processed and 25 rows inserted.
2015-10-07 15:16:12 (9952) INFO : For table tpch2.lineitem: 6001215 rows processed and 6001215 rows inserted.
2015-10-07 15:16:31 (9952) INFO : For table tpch2.orders: 1500000 rows processed and 1500000 rows inserted.
2015-10-07 15:16:33 (9952) INFO : For table tpch2.part: 200000 rows processed and 200000 rows inserted.
2015-10-07 15:16:44 (9952) INFO : For table tpch2.partsupp: 800000 rows processed and 800000 rows inserted.
2015-10-07 15:16:44 (9952) INFO : For table tpch2.region: 5 rows processed and 5 rows inserted.
2015-10-07 15:16:45 (9952) INFO : For table tpch2.supplier: 10000 rows processed and 10000 rows inserted.

カラム順やデータの種類の違いの取り扱い

入力データとテーブル定義に何らかの違いがある場合、colxmlユーティリティで対応することができます。

• 入力ファイルとテーブル定義のカラム順の違い
• 入力データファイルのカラム読み飛ばし、または無視
• ターゲットのテーブル定義をデフォルトに設定

これらのケースでは、colxmlユーティリティ(引数-tを指定して指定テーブルのみのファイルを生成することも可能です)を実行してジョブxmlファイルを生成し、そのファイルをテンプレートとして使って編集し、そのジョブファイルに対してcpimportを実行します。

以下のようなシンプルなテーブルで考えます。

create table emp (
emp_id int, 
 dept_id int,
name varchar(30), 
salary int, 
hire_date date) engine=columnstore;

このテーブルに対するcolxmlファイルは以下のようなテーブル要素となります。

<Table tblName="test.emp" 
      loadName="emp.tbl" maxErrRow="10">
   <Column colName="emp_id"/>
   <Column colName="dept_id"/>
   <Column colName="name"/>
   <Column colName="salary"/>
   <Column colName="hire_date"/>
 </Table>

もし入力ファイルが、"salary"カラムの前に"hire_date"カラムがあるようなフォーマットであった場合、以下のように正しくロードできるよう修正します。(最後の二つのカラムの順序を変更しています)

<Table tblName="test.emp" 
      loadName="emp.tbl" maxErrRow="10">
   <Column colName="emp_id"/>
   <Column colName="dept_id"/>
   <Column colName="name"/>
   <Column colName="hire_date"/>
   <Column colName="salary"/>
 </Table>

また、以下のケース絵は、ファイルの最後のカラムを無視し、"salary"カラムにはデフォルト値を設定しています。

<Table tblName="test.emp"        
           loadName="emp.tbl" maxErrRow="10">
      <Column colName="emp_id"/>
      <Column colName="dept_id"/>
      <Column colName="name"/>
      <Column colName="hire_date"/>
      <IgnoreField/>
      <DefaultColumn colName="salary"/>
    </Table>

• IgnoreFieldsは、cpimportでファイル内で当該位置にあるフィールドを無視することを意味します。
• DefaultColumnは、cpimportで現在のテーブルカラムに対して、デフォルト値を設定することを意味し、次のカラムへ移動しません。

双方の指定は独立に行うことができ、テーブル定義に合わせたデータのインポートに活用することができます。

バイナリ―データのインポート

CSVファイルの代わりに固定長行のバイナリーファイルを読み込むことが可能です。これを行うには、'-I'フラグを使用します。'-I'フラグには二つのモードがあります。

• -I1 - NULLを許容するバイナリーモード

NULLを含むフィールドでは、デフォルト値が指定されていなければNULLとして扱われます。

• -I2 - NULL値が変換されるバイナリーモード

数値フィールドのNULL値は変換されます。

Example
cpimport -I1 mytest mytable /home/mydata/mytable.bin

次の表は、バイナリーデータのフォーマットを示します。

NULL値は以下の値を使用します。

Date構造体

struct Date
{
  unsigned spare : 6;
  unsigned day : 6;
  unsigned month : 4;
  unsigned year : 16
};

Date構造体の"Spare"ビットには必ず0x3Eをセットします。

DateTime構造体

struct DateTime
{
  unsigned msecond : 20;
  unsigned second : 6;
  unsigned minute : 6;
  unsigned hour : 6;
  unsigned day : 6;
  unsigned month : 4;
  unsigned year : 16
};