Importing a TiDB Sample Database

Tags:

The examples used in the TiDB manual use Capital Bikeshare system data, released under the Capital Bikeshare Data License Agreement.

Download All Data Files

The system data can be downloaded as yearly .zip files. Downloading and extracting all files requires about 3 GB of disk space. To download all files from 2010 through 2017 with a bash script:

mkdir -p bikeshare-data && cd bikeshare-data

curl -L --remote-name-all https://s3.amazonaws.com/capitalbikeshare-data/{2010..2017}-capitalbikeshare-tripdata.zip
unzip \*-tripdata.zip

Load Data into TiDB

The system data can be imported into TiDB using the following schema.

CREATE DATABASE bikeshare;
USE bikeshare;

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)
);

In this example, you can import files individually with the LOAD DATA command, or import all files with a bash loop as shown below.

SET tidb_dml_batch_size = 20000;
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);

Import All Files

To import all *.csv files into TiDB with a bash loop:

for FILE in *.csv; do
 echo "== $FILE =="
 mysql 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;

Import Example Database last modified 2022-06-06 17:02:15: remove aliases for v6.1 docs (#8795)