Get your own copy of wikidata

[WolfgangFahl]

https://wiki.bitplan.com/index.php/WikiData_Import_2022-01-29 now has a report of the successful indexing.
Thx for making this happen. I'll now try to run an endpoint on this dataset and see what queries will work.
Here is a first draft of a script to analyze to log file with a spreadsheet - i had to use Excel this time since libreoffice wouldn't work as expected on my Mac. The formulas might not be compatible with libreoffice.

This script could be extended to do ETA calculations and still needs some love for mill/h calculation per phase an total.

#!/bin/bash
# WF 2022-02-07
# get the qlever log and convert it to csv
host=merkur
home=/hd/jurob/qlever
slog=$home/qlever-indices/wikidata/wikidata.index-log.txt
isodate=$(date "+%Y-%m-%d")
tlog=wikidata.index-log_$isodate.txt
csv=wikidata-index-stats_$isodate.csv

calc() {
  local l_file=$(realpath $1)
  case $(uname) in
    Darwin)
      #/Applications/LibreOffice.app/Contents/MacOS/soffice --calc $1&
      /Applications/Microsoft\ Excel.app/Contents/MacOS/Microsoft\ Excel  $l_file&
      #open $1
    ;;
    *) libreoffice --calc $l_file&
    ;;
  esac
}
scp -p $host:$slog $tlog
cat $tlog | gawk '
BEGIN {
  print("phase;timestamp;count;unit;days;hours;mill/h")
  row=2
}
/Processing input triples from/                   { phase="Processing triples"}
/Merging partial vocabularies in byte order/      { phase="Merging words by byte"}
/Merging partial vocabularies in Unicode order/   { phase="Merging words by unicode"}
/Converting triples from local IDs to global IDs/ { phase="Local to global IDs"}
# 2022-02-02 05:59:07.200	- INFO:  Input triples processed: 100,000,000
/Input triples processed/ {
   logline(phase,$1,$2,$8,"triples")
   next
}
# 2022-02-02 21:40:46.606	- INFO:  Words merged: 100,000,000
/Words merged/ {
  logline(phase,$1,$2,$7,"words")
  next
}
#2022-02-03 06:07:03.695	- INFO:  Triples converted: 100,000,000
/Triples converted/ {
  logline(phase,$1,$2,$7,"triples")
  next
}
# other lines
{
  info=""
  delim=""
  for (i=5;i<=NF;i++)
    info=info delim $i
    delim=" "
  logline(info,$1,$2,0,"")
}
function logline(pinfo,date,time,count,unit) {
  #gsub("-",";",date)
  #gsub(":",";",time)
  gsub("\\.",",",time)
  gsub(",","",count)
  #printf("%s %s;=DATE(A%d,B%d,C%d);%s\n",date,time,row,row,row,triples)
  if (count==0) {
    printf("%s;%s %s;;;=(B%d-B$2);=E%s*24;=C%d/F%d/1000000\n",pinfo,date,time,row,row,row,row)
  } else {
    printf("%s;%s %s;%s;%s;=(B%d-B$2);=E%s*24;=C%d/F%d/1000000\n",pinfo,date,time,count,unit,row,row,row,row)
  }
  row=row+1
}
' > $csv
calc $csv&

[WolfgangFahl]

Here is a formatted version of the csv file with the current state of the script:
wikidata-index-stats_2022-02-06.xlsx

[bonelli]

@WolfgangFahl I'm really interested in this work, thank you.
I'm trying to get a Wikidata mirror on my Kubernetes cluster and I'm a bit frustrated by the current Blazegraph process.
As soon as your Qlever is a feasible solution I'd like to try it.

How would you tackle incremental updates after the initial load?

[WolfgangFahl]

@bonelli Thx for getting in touch. QLever is officially in the wikidata short list see the discussion in https://www.wikidata.org/wiki/Category:WDQS_backend_update and the details in https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/WDQS_backend_update/WDQS_backend_alternatives

The roadmap for QLever is something I'd be interested in myself. Hannah Bast might be able to shed some light on that question. As far as i understand the incrementatl update feature is part of that roadmap.

[bonelli]

Well I do hope QLever get chosen, it looks very promising, especially performance-wise.

@hannahbast I didn't find any github issue specifically pointing to the update problem, but AFAIK it's the main missing step that would allow somebody to use QLever to self-host a Wikidata mirror that works on most common SPARQL queries.

thanks

[hannahbast]

@bonelli There is a page on the QLever Wiki describing how we will implement SPARQL 1.1 Updates: https://github.com/ad-freiburg/qlever/wiki/QLever-support-for-SPARQL-1.1-Update . There are also other pages on the Wiki describing the current status of QLever. I am continuously adding more. If you have a specific request and it's urgent for you, please let us know. Maybe we can implement it quickly or help you to implement it yourself and create a pull request. Several small features that are currently still missing are relatively easy to implement.

I have a question concerning your update remark. QLever can load/index data very fast. A complete index for the complete Wikidata can be built in 14 hours on a standard PC (AMD Ryzen 9 5900X, 128 GB RAM, 2 TB HDD not even SSD), even faster if you have better hardware. That is, you can easily rebuild the index every day. This is much faster than Wikidata provides new data dumps on https://dumps.wikimedia.org/wikidatawiki, which is every 10 days or so. And in our experience that is good enough for almost all users. If that's not good enough for you, we would like to understand why.

We will eventually implement SPARQL 1.1 Update, as described on the Wiki, probably some time this year. But it will not happen in the next few weeks.

[bonelli]

Thank you for your reply, I'll explain my desired use case.

I'm providing my clients with custom software solutions based on custom ontologies pulled down from Wikidata. I do not plan to use Wikidata's ontology directly because depending on the project we must adapt the topology or add indices and full-text search capabilities to the graph.

At the moment our solutions connect directly to the public Wikidata SPARQL endpoint, but I wish this to stop because:

1. I reckon we're causing a heavy load on the endpoint
2. Current rate limits on the public endpoint make full downloads very slow (we're talking weeks sometimes)

Since these are business projects I think it's not fair to keep using the public endpoint, therefore I wish to have our own private Wikidata mirror on our Kubernetes cluster constantly up-to-date with the public endpoint. The custom solutions will then download all updates from the private endpoint, each using their own ETL process to keep their own custom ontology up-to-date with Wikidata's info.

My need for the update process is because I encourage my clients to complete public data directly on Wikidata when they find something is missing rather than updating just their local graph: I feel this is the right way to embrace the sharing philosophy underlying Wikipedia. If they contribute to Wikidata's entries with the missing info, then my ETL process updates the custom graph on the nightly schedule, and in a matter of hours they have everything in place.

I cannot suggest this to clients if my solutions are pulling data from a source that gets updates just once every couple weeks, when Wiki publishes a new dump.

thanks