Genghis Khan's family tree

From Knowledge Kitchen
Jump to navigation Jump to search


Based on this data on Genghis Khan's family, pulled from Wikipedia, the following two CSV files contain Genghis Khan's family tree in a format that is ready to be imported into a relational database such as MySQL.

Data set

CSV format

CSV formatted text files are most convenient for importing data into a relational database, such as MySQL.

The following data is intended to be stored in a file named "khans.csv" that will then be imported into a relational database table named "khans".

Note that \N in a CSV indicates a NULL value when it is imported into MySQL.

khan_id,name,born,died,father_id,mother_id
1,Yesugei,\N,1171,\N,\N
2,Hoelun,\N,\N,\N,\N
3,Temüjin,1162,1227,1,2
4,Börte,1161,1230,\N,\N
5,Jochi,1181,1227,3,4
6,Batu,1207,1255,5,\N
7,Sartaq,\N,1256,6,\N
8,Berke,\N,1266,5,\N
9,Chagatai,1183,1242,3,4
10,Baidar,\N,\N,9,\N
11,Ögedei,1186,1241,3,4
12,Töregene Khatun,1242,1246,\N,\N
13,Güyük,1206,1248,11,12
14,Kashin,\N,\N,11,12
15,Kaidu,1230,1301,14,\N
16,Tolui,1192,1232,3,4
17,Sorghaghtani Beki,\N,1252,\N,\N
18,Möngke,1209,1259,16,17
19,Kublai,1215,1294,16,17
20,Zhenjin,1243,1286,19,\N
21,Temür,1265,1307,20,\N
22,Hulagu,1218,1265,16,17
23,Abaqa Khan,1234,1282,22,\N
24,Arghun,1258,1291,23,\N
25,Ariq Böke,1219,1266,16,17
26,Hasar,\N,\N,1,2
27,Hachiun,\N,\N,1,2
28,Temüge,1168,1246,1,2
29,Belgutei,\N,\N,1,\N
30,Behter,\N,1180,1,\N


The following data is intended to be saved in a file named "khagans.csv" that will then imported into a MySQL table named, "khagans":

khagan_id,khan_id,name
1,3,Genghis Khan
2,11,\N
3,13,\N
4,18,\N
5,19,\N
6,21,\N

JSON format

This same data set could be represented in a single JSON text file. This is useful if importing into a document database, such as MongoDB:

The following data is intended to be saved in a file named "khans.json" that will then imported into a MongoDB collection named, "khans":

{"_id": 1, "name": "Yesugei", "born": null, "died": 1171, "father_id": null, "mother_id": null}
{"_id": 2, "name": "Hoelun", "born": null, "died": null, "father_id": null, "mother_id": null}
{"_id": 3, "name": "Temüjin", "born": 1162, "died": 1227, "father_id": 1, "mother_id": 2, "khagan": true, "khagan_name": "Genghis Khan"}
{"_id": 4, "name": "Börte", "born": 1161, "died": 1230, "father_id": null, "mother_id": null}
{"_id": 5, "name": "Jochi", "born": 1181, "died": 1227, "father_id": 3, "mother_id": 4}
{"_id": 6, "name": "Batu", "born": 1207, "died": 1255, "father_id": 5, "mother_id": null}
{"_id": 7, "name": "Sartaq", "born": null, "died": 1256, "father_id": 6, "mother_id": null}
{"_id": 8, "name": "Berke", "born": null, "died": 1266, "father_id": 5, "mother_id": null}
{"_id": 9, "name": "Chagatai", "born": 1183, "died": 1242, "father_id": 3, "mother_id": 4}
{"_id": 10, "name": "Baidar", "born": null, "died": null, "father_id": 9, "mother_id": null}
{"_id": 11, "name": "Ögedei", "born": 1186, "died": 1241, "father_id": 3, "mother_id": 4, "khagan": true}
{"_id": 12, "name": "Töregene Khatun", "born": 1242, "died": 1246, "father_id": null, "mother_id": null}
{"_id": 13, "name": "Güyük", "born": 1206, "died": 1248, "father_id": 11, "mother_id": 12, "khagan": true}
{"_id": 14, "name": "Kashin", "born": null, "died": null, "father_id": 11, "mother_id": 12}
{"_id": 15, "name": "Kaidu", "born": 1230, "died": 1301, "father_id": 14, "mother_id": null}
{"_id": 16, "name": "Tolui", "born": 1192, "died": 1232, "father_id": 3, "mother_id": 4}
{"_id": 17, "name": "Sorghaghtani Beki", "born": null, "died": 1252, "father_id": null, "mother_id": null}
{"_id": 18, "name": "Möngke", "born": 1209, "died": 1259, "father_id": 16, "mother_id": 17, "khagan": true}
{"_id": 19, "name": "Kublai", "born": 1215, "died": 1294, "father_id": 16, "mother_id": 17, "khagan": true}
{"_id": 20, "name": "Zhenjin", "born": 1243, "died": 1286, "father_id": 19, "mother_id": null}
{"_id": 21, "name": "Temür", "born": 1265, "died": 1307, "father_id": 20, "mother_id": null, "khagan": true}
{"_id": 22, "name": "Hulagu", "born": 1218, "died": 1265, "father_id": 16, "mother_id": 17}
{"_id": 23, "name": "Abaqa Khan", "born": 1234, "died": 1282, "father_id": 22, "mother_id": null}
{"_id": 24, "name": "Arghun", "born": 1258, "died": 1291, "father_id": 23, "mother_id": null}
{"_id": 25, "name": "Ariq Böke", "born": 1219, "died": 1266, "father_id": 16, "mother_id": 17}
{"_id": 26, "name": "Hasar", "born": null, "died": null, "father_id": 1, "mother_id": 2}
{"_id": 27, "name": "Hachiun", "born": null, "died": null, "father_id": 1, "mother_id": 2}
{"_id": 28, "name": "Temüge", "born": 1168, "died": 1246, "father_id": 1, "mother_id": 2}
{"_id": 29, "name": "Belgutei", "born": null, "died": null, "father_id": 1, "mother_id": null}
{"_id": 30, "name": "Behter", "born": null, "died": 1180, "father_id": 1, "mother_id": null}


Importing data into MySQL

To import the data into MySQL tables, you must first create the tables that will store the data, and then import the CSV file into these tables.

Creating appropriate tables in MySQL

The following script creates a khans table in a MySQL database that will store the data in khans.csv:

CREATE TABLE khans (
	 khan_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	 name VARCHAR(50) NOT NULL,
	 born INT(4) NULL DEFAULT NULL,
	 died INT(4) NULL DEFAULT NULL,
	 father_id INT(11) NULL DEFAULT NULL,
	 mother_id INT(11) NULL DEFAULT NULL,
	 PRIMARY KEY (khan_id)
);

The following script creates a khagans table in a MySQL database that will store the data in khagans.csv:

CREATE TABLE khagans (
	 khagan_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	 khan_id INT(11) NOT NULL,
	 name VARCHAR(50) NULL DEFAULT NULL,
	 PRIMARY KEY (khagan_id)
);


Importing CSV data

The following script imports the khans.csv data into the khans table:

LOAD DATA LOCAL INFILE 'khans.csv' 
 INTO TABLE khans 
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY ''
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (khan_id, name, born, died, father_id, mother_id);

The following script imports the khagans.csv data into the khagans table:

LOAD DATA LOCAL INFILE 'khagans.csv' 
 INTO TABLE khagans 
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY ''
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (khagan_id, khan_id, name);


Importing data into MongoDB

To import data into a MongoDB collection, you do not need to set up the collection in advance. A collection will be created automatically if it doesn't yet exist.

The following *NIX command imports the khans.json data into the khans collection:

mongoimport --db your_database_name --collection khans --host class-mongodb.cims.nyu.edu --username your_username --password your_password --type json --file khans.json 

Where your_database_name, your_username, and your_password are the credentials you received when creating an account on our MongoDB database server.


What links here

{{Special:WhatLinksHere/Genghis_Khan%27s_family_tree}}