{"id":3848,"date":"2021-12-16T14:32:01","date_gmt":"2021-12-16T14:32:01","guid":{"rendered":"http:\/\/truelogic.org\/wordpress\/?p=3848"},"modified":"2021-12-16T14:35:21","modified_gmt":"2021-12-16T14:35:21","slug":"convert-imdb-data-to-mysql-tables-using-c-part-1","status":"publish","type":"post","link":"https:\/\/truelogic.org\/wordpress\/2021\/12\/16\/convert-imdb-data-to-mysql-tables-using-c-part-1\/","title":{"rendered":"Convert IMDB data to MySql tables using C++ – Part 1"},"content":{"rendered":"\n

OVERVIEW<\/strong><\/p>\n\n\n\n

IMDB , being the world’s best known and largest movie and TV website, does not provide any API to use their data. However , they provide datasets of their entire database in TSV files (tab separated values). They contain all the data pertaining to movies, TV shows and their cast and ratings.<\/p>\n\n\n\n

The dataset can be downloaded from https:\/\/datasets.imdbws.com\/<\/a> The files can be converted into various formats and databases for any work to be done on them. The explanation of the format and fields are given here: https:\/\/www.imdb.com\/interfaces\/<\/a> . This data dump is updated daily.<\/p>\n\n\n\n

Please note that IMDB forbids any commercial usage of this data. It can be only used for personal and non-commercial use-cases.<\/strong><\/p>\n\n\n\n

In this blog series, we look at how to convert the tsv files into a normalized Mysql database. <\/p>\n\n\n\n

In Part 1 we will only capture all the raw data from the tsv files into mysql tables. In the next part we will look at adding indexes and optimizing the database to remove duplication and redundancy.<\/p>\n\n\n\n

WHY C++<\/strong><\/p>\n\n\n\n

Processing the TSV files is not complex and can be done in any high level language like Python or Java or PHP. However, because the size of the files go into hundreds of Mb and a couple of Gb, it can take very long to process all the files. I used C++ because thats probably the fastest performance one can get . Even on an old Intel Core2Duo machine, each of the tsv files did not take longer than 10-15 minutes at the most.<\/p>\n\n\n\n

OBJECTIVE OF PART 1<\/strong><\/p>\n\n\n\n

What we are doing here, is process each tsv file and create an sql file which contains INSERT statements for each tsv row. Then, each of these sql files is processed directly in MySQL. This is faster than trying to do a direct Mysql insert using C++ code.<\/p>\n\n\n\n

The code uses wxWidgets library for some of the functions. At some point in the future, I aim to make a GUI app to process and retrieve the mysql data so that is the reason why wxWidgets is part of the equation.<\/p>\n\n\n\n

CODE SETUP<\/strong><\/p>\n\n\n\n

I am using this on Ubuntu but it can be easily run on Windows as well with minimal changes since I am using STL and wxWidgets which is cross-compatible with Unix, Windows and MacOS.<\/p>\n\n\n\n

To set up wxWidgets on Windows, take a look at https:\/\/truelogic.org\/wordpress\/2021\/12\/06\/setting-up-wxwidgets-environment-for-c-in-windows-10\/<\/a><\/p>\n\n\n\n

To set up wxWidgets in Ubuntu or Debian Linux, take a look at https:\/\/truelogic.org\/wordpress\/2021\/10\/31\/setting-up-wxwidgets-environment-for-c-in-ubuntu-linux\/<\/a><\/p>\n\n\n\n

MYSQL TABLES<\/strong><\/p>\n\n\n\n

The mysql dump for the table structures is given below. This can be used to create the database<\/p>\n\n\n

\n-- Adminer 4.7.7 MySQL dump\n\nSET NAMES utf8;\nSET time_zone = '+00:00';\nSET foreign_key_checks = 0;\nSET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';\n\nDROP TABLE IF EXISTS `name_basics`;\nCREATE TABLE `name_basics` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `nconst` varchar(20) DEFAULT NULL,\n  `primaryName` varchar(255) DEFAULT NULL,\n  `birthYear` varchar(10) DEFAULT NULL,\n  `deathYear` varchar(10) DEFAULT NULL,\n  `primaryProfession` varchar(255) DEFAULT NULL,\n  `knownForTitles` text,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\nDROP TABLE IF EXISTS `title_akas`;\nCREATE TABLE `title_akas` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `titleid` varchar(20) NOT NULL,\n  `ordering` int(11) NOT NULL,\n  `title` varchar(255) CHARACTER SET utf8 NOT NULL,\n  `region` varchar(5) DEFAULT NULL,\n  `language` varchar(25) DEFAULT NULL,\n  `types` varchar(15) DEFAULT NULL,\n  `attributes` varchar(50) DEFAULT NULL,\n  `isOriginalTitle` smallint(6) DEFAULT NULL,\n  PRIMARY KEY (`ID`),\n  KEY `titleid_ordering` (`titleid`,`ordering`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\nDROP TABLE IF EXISTS `title_basics`;\nCREATE TABLE `title_basics` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `tconst` varchar(20) DEFAULT NULL,\n  `titletype` varchar(15) DEFAULT NULL,\n  `primaryTitle` varchar(255) DEFAULT NULL,\n  `originalTitle` varchar(255) DEFAULT NULL,\n  `isAdult` smallint(6) DEFAULT NULL,\n  `startYear` varchar(10) DEFAULT NULL,\n  `endYear` varchar(10) DEFAULT NULL,\n  `runtimeMinutes` varchar(5) DEFAULT NULL,\n  `genres` varchar(255) DEFAULT NULL,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\nDROP TABLE IF EXISTS `title_crew`;\nCREATE TABLE `title_crew` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `tconst` varchar(20) DEFAULT NULL,\n  `directors` varchar(1000) DEFAULT NULL,\n  `writers` varchar(1000) DEFAULT NULL,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\nDROP TABLE IF EXISTS `title_episode`;\nCREATE TABLE `title_episode` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `tconst` varchar(20) DEFAULT NULL,\n  `parenttconst` varchar(20) DEFAULT NULL,\n  `seasonNumber` varchar(5) DEFAULT NULL,\n  `episodeNumber` varchar(5) DEFAULT NULL,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\nDROP TABLE IF EXISTS `title_principals`;\nCREATE TABLE `title_principals` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `tconst` varchar(20) DEFAULT NULL,\n  `ordering` varchar(5) DEFAULT NULL,\n  `nconst` varchar(20) DEFAULT NULL,\n  `category` varchar(50) DEFAULT NULL,\n  `job` varchar(255) DEFAULT NULL,\n  `charac` varchar(100) DEFAULT NULL,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\nDROP TABLE IF EXISTS `title_ratings`;\nCREATE TABLE `title_ratings` (\n  `ID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `tconst` varchar(20) DEFAULT NULL,\n  `averageRating` decimal(5,2) DEFAULT NULL,\n  `numVotes` mediumtext,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n\n-- 2021-12-16 14:05:28\n\n<\/pre><\/div>\n\n\n
SOURCE CODE<\/strong><\/p>\n\n\n\n
read-tsv.h<\/pre>\n\n\n
\n#include <wx\/wxprec.h>\n#ifndef WX_PRECOMP\n\t#include <wx\/wx.h>\n#endif\n#include <wx\/textfile.h>\n#include <iostream>\n#include <fstream>\n\nclass TSVReader {\n\n\tprivate:\n\t   wxString mPath;\n\t   wxString mTable;\n\t   wxString mFieldListSQL;\n\t   wxString getFieldList(wxString tablename);\n\t   wxString mSQLFileName;\n\t   std::ofstream mFileOut;\n\t   \n\tpublic:\n\t   TSVReader(wxString path, wxString table);\n\t   void readData();\n\t   void processLine(std::wstring, int lineCount);\n\n};\n\n\/\/ for each tsv file, define mysql table name, \n\/\/ field count and name of fields. \n\/\/ The field info ignores the ID column which is an autoincremented PK \n\/\/\nconst static wxString TABLE_TITLE_AKAS = wxT("title_akas");\nconst static int TITLE_AKAS_FIELD_COUNT = 8;\nconst static wxString TITLE_AKAS_FIELDS[] = {wxT("titleid"), wxT("ordering"), wxT("title"), wxT("region"),\n\t\t\twxT("language"), wxT("types"), wxT("attributes"), wxT("isOriginalTitle")};\n\n\nconst static wxString TABLE_TITLE_BASICS = wxT("title_basics");\nconst static int TITLE_BASICS_FIELD_COUNT = 9;\nconst static wxString TITLE_BASICS_FIELDS[] = {wxT("tconst"), wxT("titleType"), wxT("primaryTitle"), wxT("originalTitle"), wxT("isAdult"),\n\t\t\twxT("startYear"), wxT("endYear"), wxT("runtimeMinutes"), wxT("genres")};\n\n\nconst static wxString TABLE_TITLE_CREW = wxT("title_crew");\nconst static int TITLE_CREW_FIELD_COUNT = 3;\nconst static wxString TITLE_CREW_FIELDS[] = {wxT("tconst"), wxT("directors"), wxT("writers")};\n\nconst static wxString TABLE_TITLE_EPISODE = wxT("title_episode");\nconst static int TITLE_EPISODE_FIELD_COUNT = 4;\nconst static wxString TITLE_EPISODE_FIELDS[] = {wxT("tconst"), wxT("parenttconst"), wxT("seasonNumber"), wxT("episodeNumber")};\n\nconst static wxString TABLE_TITLE_PRINCIPALS = wxT("title_principals");\nconst static int TITLE_PRINCIPALS_FIELD_COUNT = 6;\nconst static wxString TITLE_PRINCIPALS_FIELDS[] = {wxT("tconst"), wxT("ordering"), wxT("nconst"), wxT("category"), wxT("job"), wxT("charac")};\n\nconst static wxString TABLE_TITLE_RATINGS = wxT("title_ratings");\nconst static int TITLE_RATINGS_FIELD_COUNT = 3;\nconst static wxString TITLE_RATINGS_FIELDS[] = {wxT("tconst"), wxT("averageRating"), wxT("numVotes")};\n\nconst static wxString TABLE_NAME_BASICS = wxT("name_basics");\nconst static int NAME_BASICS_FIELD_COUNT = 6;\nconst static wxString NAME_BASICS_FIELDS[] = {wxT("nconst"), wxT("primaryName"), wxT("birthYear"), wxT("deathYear"), wxT("primaryProfession"),\n\t\t\twxT("knownForTitles")};\n\n\n\n<\/pre><\/div>\n\n\n
read-tsv.cpp<\/pre>\n\n\n
\n#include <codecvt>\n#include <sstream>\n#include <wx\/tokenzr.h>\n#include "read-tsv.h"\n\n\/**\n * Ctor\n * @param path - path to tsv file\n * @param table - name of mysql table\n *\/\nTSVReader::TSVReader(wxString path, wxString table) {\n\tmPath = path;\n\tmTable = table;\n\tmFieldListSQL = getFieldList(mTable);\n\tmSQLFileName = table.append(".sql");\n\n\t\/\/delete sql file if it already exists\n\tif (wxFileExists(mSQLFileName))\n\t\twxRemoveFile(mSQLFileName);\n}\n\n\/**\n * Get the INSERT sql fragment with list of mysql field names based on the table name\n * @param tablename - name of mysql table\n * @return retVal - SQL insert statement prefix\n *\/\nwxString TSVReader::getFieldList(wxString tablename) {\n\twxString retVal = wxT("(");\n\tif (mTable == TABLE_TITLE_AKAS) {\n\t\tfor(int i = 0; i < TITLE_AKAS_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(TITLE_AKAS_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\telse if (mTable == TABLE_TITLE_BASICS) {\n\t\tfor(int i = 0; i < TITLE_BASICS_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(TITLE_BASICS_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\telse if (mTable == TABLE_TITLE_CREW) {\n\t\tfor(int i = 0; i < TITLE_CREW_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(TITLE_CREW_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\telse if (mTable == TABLE_TITLE_EPISODE) {\n\t\tfor(int i = 0; i < TITLE_EPISODE_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(TITLE_EPISODE_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\telse if (mTable == TABLE_TITLE_PRINCIPALS) {\n\t\tfor(int i = 0; i < TITLE_PRINCIPALS_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(TITLE_PRINCIPALS_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\telse if (mTable == TABLE_TITLE_RATINGS) {\n\t\tfor(int i = 0; i < TITLE_RATINGS_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(TITLE_RATINGS_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\telse if (mTable == TABLE_NAME_BASICS) {\n\t\tfor(int i = 0; i < NAME_BASICS_FIELD_COUNT; i++) {\n\t\t\tretVal.Append(wxT("`")).Append(NAME_BASICS_FIELDS[i]).Append(wxT("`,"));\n\t\t}\n\t}\n\n\n\t\/\/ remove trailing comma\n\tif (retVal != wxT("("))\n\t\tretVal = retVal.Left(retVal.Len()-1);\n\tretVal.Append(wxT(") "));\n\treturn retVal;\n}\n\n\/**\n * Read data line by line from tsv file. Each line is then processed to generate an INSERT sql statement. \n * These statements are appended to an sql file.\n *\/\nvoid TSVReader::readData() {\n\n\tstd::wifstream infile(mPath);\n\tinfile.imbue(std::locale(infile.getloc(),\n\t       new std::codecvt_utf8<wchar_t, 0x10ffff>));\n\tstd::wstring line;\n\n\tmFileOut.open(mSQLFileName, std::ios_base::app);\n\t\n\tlong lineCount = 0;\n\twxPuts(wxT("Reading ") + mPath);\n\tbool isTitleRow = true; \/\/ first row is column titles\n\n\twhile (getline(infile, line)) {\n\t\tif (isTitleRow) {\n\t\t\tisTitleRow = false;\n\t\t\tcontinue;\n\t\t}\n\t\tprocessLine(line, lineCount);\n\t\tlineCount++;\n\n\t}\n\tmFileOut.close();\n\twxPuts(wxString::Format(wxT("%ld lines"), lineCount));\n}\n\n\/**\n * Convert each line in tsv file to an INSERT sql statement\n * @param line - tsv line \n * @param lineCount - counter of lines read from tsv file\n *\/\nvoid TSVReader::processLine(std::wstring line, int lineCount) {\n\twxString sql = wxT("insert into ");\n\tsql.Append(mTable).Append(mFieldListSQL).Append(wxT("values ("));\n\twxString token = wxT("");\n\twxStringTokenizer tokenizer(line, "\\t");\n\twhile (tokenizer.HasMoreTokens()) {\n\t\ttoken = tokenizer.GetNextToken();\n\t\ttoken.Replace(wxT("'"), wxT("\\\\;"));\n\t\tsql.Append("'").Append(token).Append("',");\n\t}\n\t\/\/ remove trailing comma\n\tif (token != wxT(""))\n\t     sql = sql.Left(sql.Len()-1);\n\tsql.Append(wxT(");"));\n\tmFileOut << sql << std::endl;\n\n}\n\n<\/pre><\/div>\n\n\n
main.cpp<\/pre>\n\n\n
\n#include "read-tsv.h"\n\n\/\/\/\n\/\/ First arg is the tsv filename, Second arg is the mysql table name\n\/\/\/\nint main(int argc, char **argv) {\n\twxInitialize();\n\twxString filename = wxT("");\n\twxString table = wxT("");\n\tif (argc > 2) {\n\t\tfilename = argv[1];\n\t\ttable = argv[2];\n\t} else {\n\t   wxPuts("No tsv file and\/or tablename specified");\n\t   return -1;\n\t}\n\twxPuts("Reading file.." + filename);\n\tTSVReader tsv(filename, table);\n\ttsv.readData();\n\twxPuts("Closed file");\n\twxUninitialize();\n}\n<\/pre><\/div>\n\n\n
To build the source under Windows change the sample makefile as provided in the Windows setup of wxWidgets blog post. For Ubuntu use <\/p>\n\n\n\n
g++ main.cpp read-tsv.cpp ``wx-config --cxxflags --libs<\/code>`` -o read-tsv\n<\/pre>\n\n\n\n
Use single backticks in the above command and not double backticks as shown above<\/p>\n\n\n\n
EXECUTION<\/strong><\/p>\n\n\n\n
For each tsv file, run the command<\/p>\n\n\n\n
.\/read-tsv <tsv filename> <table name><\/pre>\n\n\n\n
Eg. .\/read-tsv name.basics.tsv name_basics<\/pre>\n\n\n\n
Once you have run this for all the tsv files, you will end up with corresponding sql files for each table. Process each of these sql files in mysql . You can do either by indirection at the command prompt:<\/p>\n\n\n\n
mysql --user=username --password=mypassword imdb_db < name_basics.sql<\/pre>\n\n\n\n
Or you can do it using the source<\/em> command:<\/p>\n\n\n\n
mysql --user=username --password=mypassword \n> use imdb_db;\n> source 'name_basics.sql';\n<\/pre>\n\n\n\n
Note that if you run the sql file twice for some reason, be sure to truncate the table first, otherwise it will insert duplicate data.<\/p>\n\n\n\n
The table screenshots with the imported data are shown below:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
NEXT STEPS<\/strong><\/p>\n\n\n\n
In the next blog post, we will look into optimizing the database and cleaning it up a bit.<\/p>\n","protected":false},"excerpt":{"rendered":"
OVERVIEW IMDB , being the world’s best known and largest movie and TV website, does not provide any API to use their data. However , […]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":3849,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[302],"tags":[],"class_list":["post-3848","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cc"],"_links":{"self":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3848","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/comments?post=3848"}],"version-history":[{"count":14,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3848\/revisions"}],"predecessor-version":[{"id":3869,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3848\/revisions\/3869"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media\/3849"}],"wp:attachment":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media?parent=3848"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/categories?post=3848"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/tags?post=3848"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}