The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

ColumnStoreへの一括データロード

Contents

  1. 概要
  2. 構文
  3. cpimportモード
    1. モード1：一つのデータファイルから対象モジュールに一括読み込みを行います。
    2. モード2：複数の分散されたデータファイルから対象モジュールに一括読み込みを行います。
    3. モード3：並行分散一括読み込みを行います。
  4. 標準入力からの一括ロード
  5. SELECT FROMでテーブルから取得したデータの一括ロード
  6. 複数テーブルへの一括ロード
    1. colxml構文
    2. colxml使用例
  7. カラム順やデータの種類の違いの取り扱い
  8. バイナリ―データのインポート
    1. Date構造体
    2. DateTime構造体

概要

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が実行される場所は、ユーザーモジュールかパフォーマンスモジュールのいずれかになります。

cpimport-mode1

Example
cpimport -m1 mytest mytable mytable.tbl

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

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

cpimport-mode2

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

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

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

cpimport-mode3

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

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

DatatypeDescription
INT/TINYINT/SMALLINT/BIGINTLittle-endian format for the numeric data
FLOAT/DOUBLEIEEE format native to the computer
CHAR/VARCHARData padded with '\0' for the length of the field. An entry that is all '\0' is treated as NULL
DATEUsing the Date struct below
DATETIMEUsing the DateTime struct below
DECIMALStored using an integer representation of the DECIMAL without the decimal point. With precision/width of 2 or less 2 bytes should be used, 3-4 should use 3 bytes, 4-9 should use 4 bytes and 10+ should use 8 bytes

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

DatatypeSigned NULLUnsigned NULL
BIGINT0x8000000000000000ULL0xFFFFFFFFFFFFFFFEULL
INT0x800000000xFFFFFFFE
SMALLINT0x80000xFFFE
TINYINT0x800xFE
DECIMALAs equiv. INTAs equiv. INT
FLOAT0xFFAAAAAAN/A
DOUBLE0xFFFAAAAAAAAAAAAAULLN/A
DATE0xFFFFFFFEN/A
DATETIME0xFFFFFFFFFFFFFFFEULLN/A
CHAR/VARCHARFill with '\0'N/A

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
};
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.