# TiDB Lightningを使用してCSVファイルからデータをインポートする ### 概要 + これらの演習では、`TiDB Lightning`を使用してCSVファイルからデータをTiDB Playgroundクラスタにインポートします。 ### 注意 + いくつかのステップでは、2つの異なるターミナルウィンドウでコマンドを入力する必要があります。2つのターミナルを起動し、演習の間ずっと開いておくと便利でしょう。 + この演習で利用する、`IP`、`port`、`username`、`password`、`directory`などは、あなたの環境と異なる場合があります。 + ターミナルウィンドウの"`$`"とはOSプロンプトです。 + ターミナルウィンドウ内の "`tidb>`" または "`mysql>`" は、SQL コマンドプロンプトです。 + これらの演習は、ノートパソコンなどの単一のマシン上で実行することができます。 ### 前提条件 + macOSまたはLinuxを使用していること。 + [lab repository](https://github.com/pingcap/tidb-course-201-lab.git)をクローン済みであること。 + 演習「テスト用にTiDBクラスタを作成する」を完了していること。 + macOSまたはLinux のハンズオン環境では、少なくとも10％の空きストレージが必要です。 ## 演習 1: CSVファイルからデータをTiDBに物理インポートモードでインポートする ### 概要 + 物理的なインポートモードは、SQLインターフェースを使用せずにデータをストレージレイヤーにインポートするための非常に効率的な方法です。このモードは、数百テラバイト以上の大規模なデータセットをインポートする場合に特に有効です。 ### 所要時間 + この演習は約10分程度かかります。 ### タスク 1. インポートするデータを確認します。 2. `TiDB Lightning`のインポートタスクの設定を確認します。 3. `TiDB Lightning`物理インポートモードを使用して、データをTiDBにインポートします。 ### 手順 1. データインポートタスクのために、ユーザー`imp`を作成します。 ``` $ cd tidb-course-201-lab/scripts/ $ ./connect-4000.sh ``` ```sql CREATE USER imp@'%' IDENTIFIED BY 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO imp@'%'; EXIT; ``` 2. ソースデータディレクトリ`misc/planets-csv-large-1`の内容をリストアップします。ターミナルプロンプトで次のコマンドを入力し、表示された結果を受け取ります。 ``` $ ls -l ./misc/planets-csv-large-1/ total 89632 -rw-r--r-- 1 username staff 100 Apr 6 08:05 universe-schema-create.sql -rw-r--r-- 1 username staff 1777 Nov 6 14:14 universe.planets-schema.sql -rw-r--r-- 1 username staff 45109100 Apr 6 18:06 universe.planets.000000000.csv -rwxr-xr-x 1 username staff 573 Apr 6 18:06 universe.stars-schema.sql -rw-rw-r-- 1 username staff 301 Apr 6 18:05 universe.stars.csv ``` 3. `universe-schema-create.sql`という名前のSQLスクリプトは、`universe`スキーマを作成するために使用されます。 ``` $ cat ./misc/planets-csv-large-1/universe-schema-create.sql ``` ```sql CREATE DATABASE `universe` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ ; ``` 4. `universe.planets-schema.sql`という名前のSQLスクリプトは、`planets`テーブルを作成するために使用されます。 ``` $ cat ./misc/planets-csv-large-1/universe.planets-schema.sql ``` ```sql CREATE TABLE `planets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL DEFAULT '', `mass` float NOT NULL DEFAULT '0.0' COMMENT '10**24 kg', `diameter` decimal(12,2) NOT NULL DEFAULT '0' COMMENT 'km', `density` int(11) NOT NULL DEFAULT '0' COMMENT 'kg/m**3', `gravity` decimal(8,1) NOT NULL DEFAULT '0.0' COMMENT 'm/s**2', `escape_velocity` decimal(8,1) NOT NULL DEFAULT '0.0' COMMENT 'km/s', `rotation_period` decimal(5,1) NOT NULL DEFAULT '0.0' COMMENT 'hours', `length_of_day` decimal(8,1) NOT NULL DEFAULT '0.0' COMMENT 'hours', `distance_from_sun` float NOT NULL DEFAULT '0.0' COMMENT '10**6 km', `perihelion` float DEFAULT NULL COMMENT '10**6 km', `aphelion` float DEFAULT NULL COMMENT '10**6 km', `orbital_period` decimal(12,1) NOT NULL DEFAULT '0.0' COMMENT 'days', `orbital_velocity` decimal(12,1) DEFAULT NULL COMMENT 'km/s', `orbital_inclination` decimal(8,1) DEFAULT NULL COMMENT 'degrees', `orbital_eccentricity` decimal(7,0) NOT NULL DEFAULT '0.0', `obliquity_to_orbit` decimal(10,4) DEFAULT NULL COMMENT 'degrees', `mean_temperature` int(11) NOT NULL DEFAULT '0' COMMENT 'C', `surface_pressure` float DEFAULT NULL COMMENT 'bars', `ring_systems` tinyint(1) NOT NULL DEFAULT '0', `global_magnetic_field` tinyint(1) DEFAULT '0', `sun_id` bigint(20) NOT NULL, `category_id` int(11) NOT NULL, `discover_date` datetime DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */, KEY `name` (`name`), CONSTRAINT `planet_sun_fk` FOREIGN KEY (`sun_id`) REFERENCES `stars` (`id`), CONSTRAINT `planet_cat_fk` FOREIGN KEY (`category_id`) REFERENCES `planet_categories` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=40510553; ``` 5. SQLスクリプト`universe.stars-schema.sql`は、テーブル`stars`を作成するために使用されます。 ``` $ cat ./misc/planets-csv-large-1/universe.stars-schema.sql ``` ```sql CREATE TABLE `stars` ( `id` bigint AUTO_RANDOM, `name` char(20) NOT NULL DEFAULT '', `mass` float NOT NULL DEFAULT 0.0 COMMENT '10**24 kg', `density` int NOT NULL DEFAULT 0 COMMENT 'kg/m**3', `gravity` decimal(20, 4) NOT NULL DEFAULT 0.0 COMMENT 'm/s**2', `escape_velocity` decimal(8, 1) DEFAULT NULL COMMENT 'km/s', `mass_conversion_rate` int DEFAULT NULL COMMENT '10**6 kg/s', `spectral_type` char(8) NOT NULL DEFAULT '', `distance_from_earth` float COMMENT 'light year', `discover_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY (`name`) ); ``` 6. ファイル `universe.planets.000000000.csv` と `universe.stars.csv` に、それぞれ327681行と3行が含まれていることを確認します。これらのファイルはデモ用であり、実世界のデータは含まれていません。 ``` $ head -5 ./misc/planets-csv-large-1/universe.planets.000000000.csv "id","name","mass","diameter","density","gravity","escape_velocity","rotation_period","length_of_day","distance_from_sun","perihelion","aphelion","orbital_period","orbital_velocity","orbital_inclination","orbital_eccentricity","obliquity_to_orbit","mean_temperature","surface_pressure","ring_systems","global_magnetic_field","sun_id","category_id","discover_date" 1,"Mercury",0.33,4879.00,5429,3.7,4.3,1407.6,4222.6,57.9,46,69.8,88.0,47.4,7.0,0,0.0340,167,0,0,1,7782220156096217089,1,\N 2,"Venus",4.87,12104.00,5234,8.9,10.4,-5832.5,2802.0,108.2,107.5,108.9,224.7,35.0,3.4,0,177.4000,464,92,0,0,7782220156096217089,1,\N 3,"Earth",5.97,12756.00,5514,9.8,11.2,23.9,24.0,149.6,147.1,152.1,365.2,29.8,0.0,0,23.4000,15,1,0,1,7782220156096217089,1,\N 4,"Mars",0.642,6792.00,3934,3.7,5.0,24.6,24.7,228,206.7,249.3,687.0,24.1,1.8,0,25.2000,-65,0.01,0,0,7782220156096217089,1,\N ``` ``` $ wc -l ./misc/planets-csv-large-1/universe.planets.000000000.csv 327681 ./misc/planets-csv-large-1/universe.planets.000000000.csv ``` ``` $ head -5 ./misc/planets-csv-large-1/universe.stars.csv "id","name","mass","density","gravity","escape_velocity","mass_conversion_rate","spectral_type","distance_from_earth","discover_date" 1,"Sun",1988500,1408,274.0000,617.6,4260,"G2 V",0.0000158,\N 3170534137668829186,"Proxima Centauri",244600,56800,0.0520,\N,\N,"M5.5 Ve",4.426,"1915-01-01 00:00:00" ``` ``` $ wc -l ./misc/planets-csv-large-1/universe.stars.csv 3 ./misc/planets-csv-large-1/universe.stars.csv ``` 7. `TiDB Lightning`タスク設定ファイルのコンテンツを確認し、`[tikv-importer]`セクション内の`backend`属性の値が`local`に設定されていることに注意します。この設定は、`TiDB Lightning`が物理インポートモードで動作し、SQLレイヤーを経由せずにデータを直接ストレージレイヤーにインポートしていることを示しています。`[mydumper]`セクションの下にある`data-source-dir`属性が正しく`./misc/planets-csv-large-1`ディレクトリを指していること、そして`[tidb]`セクションの資格情報がステップ1で作成したユーザー用のものと同じであることを確認します。 ``` $ cat ./misc/lightning-universe-planets-csv-physical.toml [lightning] level = "info" file = "../stage/tidb-lightning-universe.log" table-concurrency = 1 index-concurrency = 1 region-concurrency = 1 io-concurrency = 1 max-error = 0 task-info-schema-name = "lightning_task_info" meta-schema-name = "lightning_metadata" [checkpoint] enable = true driver = "file" dsn = "/tmp/tidb_lightning_checkpoint_physical.pb" [tikv-importer] backend = "local" incremental-import = false sorted-kv-dir = "../stage/sorted-kv-dir-1" [mydumper] data-source-dir = "./misc/planets-csv-large-1" filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*'] strict-format = true [mydumper.csv] separator = ',' delimiter = '"' terminator = '' header = true not-null = false null = '\N' backslash-escape = true trim-last-separator = false [tidb] host = "127.0.0.1" port = 4000 user = "imp" password = "q1w2e3R4_" status-port = 10080 pd-addr = "127.0.0.1:2379" ``` 8. インポートタスクを開始します。ターミナルプロンプトで以下のコマンドを入力し、表示された結果を受け取ります。環境がTiDB Playgroundクラスタであるため、チェック項目7の警告は無視します。 ``` $ tiup tidb-lightning --config ./misc/lightning-universe-planets-csv-physical.toml ... +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | # | CHECK ITEM | TYPE | PASSED | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 1 | Skip the csv size check, because config.StrictFormat is true | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 2 | the checkpoints are valid | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 3 | table schemas are valid | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 4 | all importing tables on the target are empty | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 5 | Cluster version check passed | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 6 | Lightning has the correct storage permission | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 7 | sorted-kv-dir:../stage/sorted-kv-dir-1 and data-source-dir:/Users/username/git/github/tidb-course-201-lab/scripts/misc/planets-csv | performance | false | | | -large-1 are in the same disk, may slow down performance | | | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 8 | local disk resources are rich, estimate sorted data size 14.34MiB, local available is 103.5GiB | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 9 | The storage space is rich, which TiKV/Tiflash is 310.5GiB/103.5GiB. The estimated storage space is 14.34MiB/0B. | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 10 | Cluster doesn't have too many empty regions | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 11 | Cluster region distribution is balanced | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 12 | no CDC or PiTR task found | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ tidb lightning exit successfully ``` 9. （macOSユーザーの場合は任意）前の手順で不正確な設定に関連する問題が発生した場合、インポートタスク「`Checkpoint for ... has invalid status`」を再実行する際にこのエラーメッセージが表示される場合があります。[PingCAP Training and Certification](https://pingcap.co.jp/education/)では、TiDBクラスタ管理に関連するツール（TiDB Lightning、Dumping、TiDB Data Migrationなど）の使用方法に関する詳細な手順とハンズオンラボが含まれるプライベートトレーニングコース「TiDBの運用」を提供しています。 ``` $ rm /tmp/tidb_lightning_checkpoint_physical.pb ``` 10. データがインポートされたことを確認します。 ``` $ ./connect-4000.sh ``` ```sql SELECT COUNT(*) FROM universe.planets; SELECT COUNT(*) FROM universe.stars; ``` ``` ... +----------+ | COUNT(*) | +----------+ | 327680 | +----------+ ... +----------+ | COUNT(*) | +----------+ | 2 | +----------+ ... ``` ## 演習 2: CSVファイルからデータをTiDBに論理インポートモードでインポートする ### 概要 + ロジカル論理インポートモード中、TiDB LightningはデータをSQL文にエンコードして、その後実行し、データをインポートします。 ### 所要時間 + この演習は約5分程度かかります。 ### タスク 1. `TiDB Lightning`のインポートタスクの設定を確認します。 2. `TiDB Lightning`物理インポートモードを使用して、TiDBにデータをインポートします。 ### 手順 1. 以前にインポートした両方のテーブルを切りつめます。 ``` $ cd tidb-course-201-lab/scripts/ $ ./connect-4000.sh ``` ```sql TRUNCATE universe.planets; TRUNCATE universe.stars; EXIT; ``` 2. 提供された`TiDB Lightning`タスク設定ファイルの内容を確認し、`[tikv-importer]`セクションの`backend`属性の値が`tidb`に設定されていることに注意します。この設定は、`TiDB Lightning`が論理インポートモードで動作していることを示しています。 ``` $ cat ./misc/lightning-universe-planets-csv-logical.toml [lightning] level = "info" file = "../stage/tidb-lightning-universe.log" table-concurrency = 1 index-concurrency = 1 region-concurrency = 1 io-concurrency = 1 max-error = 0 task-info-schema-name = "lightning_task_info" meta-schema-name = "lightning_metadata" [checkpoint] enable = true driver = "file" dsn = "/tmp/tidb_lightning_checkpoint_physical.pb" [tikv-importer] backend = "tidb" incremental-import = false sorted-kv-dir = "../stage/sorted-kv-dir-1" [mydumper] data-source-dir = "./misc/planets-csv-large-1" filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*'] strict-format = true [mydumper.csv] separator = ',' delimiter = '"' terminator = '' header = true not-null = false null = '\N' backslash-escape = true trim-last-separator = false [tidb] host = "127.0.0.1" port = 4000 user = "imp" password = "q1w2e3R4_" status-port = 10080 pd-addr = "127.0.0.1:2379" ``` 3. インポートタスクを開始します。ターミナルプロンプトに以下のコマンドを入力し、表示された結果を受け取ります。 ``` $ tiup tidb-lightning --config ./misc/lightning-universe-planets-csv-logical.toml ... +---+--------------------------------------------------------------+-------------+--------+ | # | CHECK ITEM | TYPE | PASSED | +---+--------------------------------------------------------------+-------------+--------+ | 1 | Skip the csv size check, because config.StrictFormat is true | performance | true | +---+--------------------------------------------------------------+-------------+--------+ | 2 | the checkpoints are valid | critical | true | +---+--------------------------------------------------------------+-------------+--------+ | 3 | Cluster version check passed | critical | true | +---+--------------------------------------------------------------+-------------+--------+ | 4 | Lightning has the correct storage permission | critical | true | +---+--------------------------------------------------------------+-------------+--------+ tidb lightning exit successfully ``` 4. データがインポートされたことを確認します。 ``` $ ./connect-4000.sh ``` ```sql SELECT COUNT(*) FROM universe.planets; SELECT COUNT(*) FROM universe.stars; ``` ``` ... +----------+ | COUNT(*) | +----------+ | 327680 | +----------+ ... +----------+ | COUNT(*) | +----------+ | 2 | +----------+ ... ``` 5. 本番環境では、特に移行の場合にTiDB Lightningが数十テラバイトのデータをTiDBに効率的にインポートするのは珍しくありません。本格的なテストやデモを実施する場合は、こちらを[クリック](https://www.pingcap.com/demo/?utm_source=edu&utm_medium=referral&utm_campaign=201.3.E2)して申し込んでください。 ## 追加情報 CSVファイル以外でも、TiDB LightningはMydumper SQLファイル、Amazon Aurora、またはApache HiveからエクスポートされたParquetファイルからのデータのインポートが可能です。[TiDB Lightning](https://docs.pingcap.com/ja/tidb/stable/tidb-lightning-overview)に関する詳細は、TiDBドキュメントを参照します。 ## 演習終わり