Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@chibatty(Tatsuya Chiba)inNTTデータ先端技術

[TiDB] データロード

Posted at

はじめに

TiDBにデータベースを作成し、サンプルデータをLOAD DATAコマンドを使用してロードします。
クイックスタートガイドのImport Example Databaseの手順を参考に実施しました。

動作確認環境

本記事の手順は下記環境にて動作確認をしています。

[root@tisim ~]# tiup cluster display demo-cluster
tiup is checking updates for component cluster ...
Starting component `cluster`: /root/.tiup/components/cluster/v1.12.1/tiup-cluster display demo-cluster
Cluster type:       tidb
Cluster name:       demo-cluster
Cluster version:    v7.0.0
Deploy user:        tidb
SSH type:           builtin
Dashboard URL:      http://192.168.3.171:2379/dashboard
Grafana URL:        http://192.168.3.171:3000
ID                   Role        Host           Ports                            OS/Arch       Status   Data Dir                    Deploy Dir
--                   ----        ----           -----                            -------       ------   --------                    ----------
192.168.3.171:3000   grafana     192.168.3.171  3000                             linux/x86_64  Up       -                           /tidb-deploy/grafana-3000
192.168.3.171:2379   pd          192.168.3.171  2379/2380                        linux/x86_64  Up|L|UI  /tidb-data/pd-2379          /tidb-deploy/pd-2379
192.168.3.171:9090   prometheus  192.168.3.171  9090/12020                       linux/x86_64  Up       /tidb-data/prometheus-9090  /tidb-deploy/prometheus-9090
192.168.3.171:4000   tidb        192.168.3.171  4000/10080                       linux/x86_64  Up       -                           /tidb-deploy/tidb-4000
192.168.3.171:9000   tiflash     192.168.3.171  9000/8123/3930/20170/20292/8234  linux/x86_64  Up       /tidb-data/tiflash-9000     /tidb-deploy/tiflash-9000
192.168.3.171:20160  tikv        192.168.3.171  20160/20180                      linux/x86_64  Up       /tidb-data/tikv-20160       /tidb-deploy/tikv-20160
192.168.3.171:20161  tikv        192.168.3.171  20161/20181                      linux/x86_64  Up       /tidb-data/tikv-20161       /tidb-deploy/tikv-20161
192.168.3.171:20162  tikv        192.168.3.171  20162/20182                      linux/x86_64  Up       /tidb-data/tikv-20162       /tidb-deploy/tikv-20162
Total nodes: 8

データファイルダウンロード

2010年から2017年までのzipファイルをダウンロードして展開します。CSVファイルのサイズは、3GB弱程度です。

[root@tisim ~]# mkdir -p bikeshare-data && cd bikeshare-data
[root@tisim bikeshare-data]# curl -L --remote-name-all https://s3.amazonaws.com/capitalbikeshare-data/{2010..2017}-capitalbikeshare-tripdata.zip
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 2351k  100 2351k    0     0  1032k      0  0:00:02  0:00:02 --:--:-- 1032k
100 24.1M  100 24.1M    0     0  5235k      0  0:00:04  0:00:04 --:--:-- 5803k
100 41.4M  100 41.4M    0     0  3688k      0  0:00:11  0:00:11 --:--:-- 4008k
100 53.8M  100 53.8M    0     0  3211k      0  0:00:17  0:00:17 --:--:-- 4242k
100 63.4M  100 63.4M    0     0  5043k      0  0:00:12  0:00:12 --:--:-- 5174k
100 70.1M  100 70.1M    0     0  4473k      0  0:00:16  0:00:16 --:--:-- 4453k
100 74.6M  100 74.6M    0     0  4480k      0  0:00:17  0:00:17 --:--:-- 5672k
100 85.4M  100 85.4M    0     0  2377k      0  0:00:36  0:00:36 --:--:-- 1722k

[root@tisim bikeshare-data]# unzip \*-tripdata.zip
Archive:  2010-capitalbikeshare-tripdata.zip
  inflating: 2010-capitalbikeshare-tripdata.csv
(省略)
Archive:  2017-capitalbikeshare-tripdata.zip
  inflating: 2017Q1-capitalbikeshare-tripdata.csv
  inflating: 2017Q2-capitalbikeshare-tripdata.csv
  inflating: 2017Q3-capitalbikeshare-tripdata.csv
  inflating: 2017Q4-capitalbikeshare-tripdata.csv

8 archives were successfully processed.

データベース作成

mysqlクライアントからTiDBに接続して、データベースを作成します。

[root@tisim bikeshare-data]# mysql -h 192.168.3.171 -P 4000 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 409
Server version: 5.7.25-TiDB-v7.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE bikeshare;
Query OK, 0 rows affected (0.10 sec)

作成したbikeshareデータベースを選択します。

mysql> USE bikeshare
Database changed

テーブルを作成します。

mysql> CREATE TABLE trips (
    ->  trip_id bigint NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->  duration integer not null,
    ->  start_date datetime,
    ->  end_date datetime,
    ->  start_station_number integer,
    ->  start_station varchar(255),
    ->  end_station_number integer,
    ->  end_station varchar(255),
    ->  bike_number varchar(255),
    ->  member_type varchar(255)
    -> );
Query OK, 0 rows affected (0.09 sec)

LOAD DATA LOCAL文を使用してロードします。試しに、1ファイルのみロードしましたが、エラーが発生しました。

mysql> SET tidb_dml_batch_size = 20000;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '2017Q1-capitalbikeshare-tripdata.csv' INTO TABLE trips
    ->   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\r\n'
    ->   IGNORE 1 LINES
    -> (duration, start_date, end_date, start_station_number, start_station,
    -> end_station_number, end_station, bike_number, member_type);
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

mysql> SELECT @@local_infile ;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> exit
Bye

mysqlクライアントのlocal_infileを有効化して、bikeshareに接続します。

[root@tisim bikeshare-data]# mysql -h 192.168.3.171 -P 4000 -uroot bikeshare --local-infile=1
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| bikeshare  |
+------------+
1 row in set (0.00 sec)

再度LOAD DATA LOCAL文を実行します。今度は成功しました。

mysql> SET tidb_dml_batch_size = 20000;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '2017Q1-capitalbikeshare-tripdata.csv' INTO TABLE trips
    ->   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\r\n'
    ->   IGNORE 1 LINES
    -> (duration, start_date, end_date, start_station_number, start_station,
    -> end_station_number, end_station, bike_number, member_type);
Query OK, 646510 rows affected (17.02 sec)
Records: 646510  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM trips;
+----------+
| COUNT(*) |
+----------+
|   646510 |
+----------+
1 row in set (0.21 sec)

一旦、TRUNCATEします。

mysql> TRUNCATE TABLE trips;
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT COUNT(*) FROM trips;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> exit
Bye

すべてのCSVファイルをロードします。

[root@tisim bikeshare-data]# for FILE in *.csv; do
>  echo "== $FILE =="
> mysql -h 192.168.3.171 -P 4000 -uroot bikeshare --local-infile=1 -e "SET tidb_dml_batch_size = 20000; LOAD DATA LOCAL INFILE '${FILE}' INTO TABLE trips FIELDS TERMINATED BY ',' ENCLOSED BY '\
"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);"
> done;
== 2010-capitalbikeshare-tripdata.csv ==
== 2011-capitalbikeshare-tripdata.csv ==
== 2012Q1-capitalbikeshare-tripdata.csv ==
== 2012Q2-capitalbikeshare-tripdata.csv ==
== 2012Q3-capitalbikeshare-tripdata.csv ==
== 2012Q4-capitalbikeshare-tripdata.csv ==
== 2013Q1-capitalbikeshare-tripdata.csv ==
== 2013Q2-capitalbikeshare-tripdata.csv ==
== 2013Q3-capitalbikeshare-tripdata.csv ==
== 2013Q4-capitalbikeshare-tripdata.csv ==
== 2014Q1-capitalbikeshare-tripdata.csv ==
== 2014Q2-capitalbikeshare-tripdata.csv ==
== 2014Q3-capitalbikeshare-tripdata.csv ==
== 2014Q4-capitalbikeshare-tripdata.csv ==
== 2015Q1-capitalbikeshare-tripdata.csv ==
== 2015Q2-capitalbikeshare-tripdata.csv ==
== 2015Q3-capitalbikeshare-tripdata.csv ==
== 2015Q4-capitalbikeshare-tripdata.csv ==
== 2016Q1-capitalbikeshare-tripdata.csv ==
== 2016Q2-capitalbikeshare-tripdata.csv ==
== 2016Q3-capitalbikeshare-tripdata.csv ==
== 2016Q4-capitalbikeshare-tripdata.csv ==
== 2017Q1-capitalbikeshare-tripdata.csv ==
== 2017Q2-capitalbikeshare-tripdata.csv ==
== 2017Q3-capitalbikeshare-tripdata.csv ==
== 2017Q4-capitalbikeshare-tripdata.csv ==

ロードした行数を確認します。

mysql> SELECT COUNT(*) FROM trips;
+----------+
| COUNT(*) |
+----------+
| 19117643 |
+----------+
1 row in set (3.05 sec)

実行計画を確認します。

  • cop[tikv]のtaskがTiKVへオフロードしている処理を表しており、TiKVが、bike_number = 'W00742'に一致する行を取得して行数を集計していることが確認できます。
  • rootのtaskがTiDB側の処理を表しており、TiDBからの結果を集約していることが確認できます。
mysql> EXPLAIN SELECT COUNT(*) FROM trips WHERE bike_number = 'W00742';
+------------------------------+-------------+-----------+---------------+-------------------------------------------+
| id                           | estRows     | task      | access object | operator info                             |
+------------------------------+-------------+-----------+---------------+-------------------------------------------+
| HashAgg_13                   | 1.00        | root      |               | funcs:count(Column#12)->Column#11         |
| └─TableReader_14             | 1.00        | root      |               | data:HashAgg_6                            |
|   └─HashAgg_6                | 1.00        | cop[tikv] |               | funcs:count(1)->Column#12                 |
|     └─Selection_12           | 9386.78     | cop[tikv] |               | eq(bikeshare.trips.bike_number, "W00742") |
|       └─TableFullScan_11     | 19938837.00 | cop[tikv] | table:trips   | keep order:false                          |
+------------------------------+-------------+-----------+---------------+-------------------------------------------+
5 rows in set (0.01 sec)

おわりに

mysqlサーバにデータロードするのと同じ感覚で実行できました。ロード後の実行計画の確認では、TiKV側に処理をオフロードしている事を理解しました。TiKV側でフィルタリングされたデータのみが、TiDB側へ転送される事により、TiDB側のCPUリソースやデータ転送量の削減が期待できます。商用環境のようにTiDBノードとTiKVノードが、それぞれ異なるサーバで稼働している環境においては、大きな恩恵を受けられそうです。

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?