{"id":2013,"date":"2020-07-04T21:08:28","date_gmt":"2020-07-04T19:08:28","guid":{"rendered":"https:\/\/blog.factgrid.de\/?p=2013"},"modified":"2020-07-04T21:08:28","modified_gmt":"2020-07-04T19:08:28","slug":"filling-a-wikibase-instance-with-millions-of-data","status":"publish","type":"post","link":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/archives\/2013","title":{"rendered":"Filling a Wikibase instance with millions of data"},"content":{"rendered":"<p>As more and more Wikibase instances are cropping up we are seeing attempts to start them with masses of data from already existing data bases that want to switch to the new software.<\/p>\n<p>Experimenting I tried to find a faster way to insert a huge amount of items into a Wikibase instance. I have not been able to insert more than two or three statements per second using the &#8216;official&#8217; tools, such as QuickStatements or the WDI library.<\/p>\n<p>Therefore, I am inserting the data directly into the MySQL database used by Wikibase.<\/p>\n<p>The process consists of these steps:<\/p>\n<ul>\n<li>generate the data for an item in JSON<\/li>\n<li>determine the next Q number and update the JSON item data accordingly<\/li>\n<li>insert data into the various database tables<\/li>\n<\/ul>\n<p>However, if you do this without a transaction it is still terrible slow. In my setup only 120 items per minute. However, if I wrap the inserts into a transaction I was able to insert 33,000 items\/minute.<\/p>\n<h2><a id=\"user-content-steps-to-run-the-experiment\" class=\"anchor\" href=\"https:\/\/github.com\/jze\/wikibase-insert\/#steps-to-run-the-experiment\" aria-hidden=\"true\"><\/a>Steps to run the experiment<\/h2>\n<ul>\n<li>Download the\u00a0<code>docker-compose.yml<\/code>\u00a0file from\u00a0<a href=\"https:\/\/github.com\/wmde\/wikibase-docker\">https:\/\/github.com\/wmde\/wikibase-docker<\/a><\/li>\n<li>Expose port 3306 from the MariaDB container by inserting two lines:<\/li>\n<\/ul>\n<div class=\"highlight highlight-source-yaml\">\n<pre>  <span class=\"pl-ent\">mysql<\/span>:\n    <span class=\"pl-ent\">image<\/span>: <span class=\"pl-s\">mariadb:10.3<\/span>\n    <span class=\"pl-ent\">restart<\/span>: <span class=\"pl-s\">unless-stopped<\/span>\n    <span class=\"pl-ent\">ports<\/span>:\n      - <span class=\"pl-s\"><span class=\"pl-pds\">\"<\/span>3306:3306<span class=\"pl-pds\">\"<\/span><\/span>\n    <span class=\"pl-ent\">volumes<\/span>:<\/pre>\n<\/div>\n<ul>\n<li>Start the containers:\u00a0<code>docker-compose up<\/code>\u00a0and wait until you see lines ending like:<\/li>\n<\/ul>\n<pre><code>[main] INFO  o.w.q.r.t.change.RecentChangesPoller - Got no real changes\n[main] INFO  org.wikidata.query.rdf.tool.Updater - Sleeping for 10 secs\n<\/code><\/pre>\n<ul>\n<li>Create a new item\u00a0<a href=\"http:\/\/localhost:8181\/wiki\/Special:NewItem\" rel=\"nofollow\">http:\/\/localhost:8181\/wiki\/Special:NewItem<\/a>\u00a0with an English label\u00a0<code>test entry<\/code>.<\/li>\n<li>Create a new property\u00a0<a href=\"http:\/\/localhost:8181\/wiki\/Special:NewProperty\" rel=\"nofollow\">http:\/\/localhost:8181\/wiki\/Special:NewProperty<\/a>\u00a0with an English label\u00a0<code>instance of<\/code>\u00a0and data type\u00a0<code>Item<\/code>.<\/li>\n<li>Create a new property\u00a0<a href=\"http:\/\/localhost:8181\/wiki\/Special:NewProperty\" rel=\"nofollow\">http:\/\/localhost:8181\/wiki\/Special:NewProperty<\/a>\u00a0with an English label\u00a0<code>my property<\/code>\u00a0and data type\u00a0<code>External identifier<\/code>.<\/li>\n<li>Run the Java program:\u00a0<code>mvn exec:java<\/code><\/li>\n<\/ul>\n<p>For me it took a minute to insert 100 items without a transaction and 25 seconds to insert 10,000 items with a transaction.<\/p>\n<hr \/>\n<p>first published at <a href=\"https:\/\/github.com\/jze\/wikibase-insert\/\">https:\/\/github.com\/jze\/wikibase-insert\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>As more and more Wikibase instances are cropping up we are seeing attempts to start them with masses of data from already existing data bases that want to switch to the new software. Experimenting I tried to find a faster way to insert a huge amount of items into a Wikibase instance. I have not &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/archives\/2013\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Filling a Wikibase instance with millions of data&#8221;<\/span><\/a><\/p>\n","protected":false},"author":27,"featured_media":2017,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[346,436],"ppma_author":[482],"class_list":["post-2013","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tools","tag-quickstatements","tag-wikibase"],"authors":[{"term_id":482,"user_id":27,"is_guest":0,"slug":"jesper-zedlitz","display_name":"Jesper Zedlitz","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/9136a05ab82d4decf73c048b7a5370c40796a3cfdc6c1e3b11db6a7baa229d5f?s=96&d=mm&r=g","author_category":"","first_name":"Jesper","last_name":"Zedlitz","user_url":"","job_title":"","description":""}],"_links":{"self":[{"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/posts\/2013","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/comments?post=2013"}],"version-history":[{"count":0,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/posts\/2013\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/media\/2017"}],"wp:attachment":[{"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/media?parent=2013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/categories?post=2013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/tags?post=2013"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/factgrid-tools.geschichte.uni-halle.de\/blog\/wp-json\/wp\/v2\/ppma_author?post=2013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}