Wikidata statement count¶
Counts item statements over one weekly wmf.wikidata_entity snapshot, for
T437880. Run the cells top to bottom on
the analytics cluster. Heavy steps are cached per snapshot (HDFS table partition, CSV files), so a
kernel restart never recomputes what already exists and a new SNAPSHOT computes
everything afresh beside the old results.
SAMPLE_ONE_IN is the sample switch: 1 is the census, 100 keeps one item in a
hundred (by id hash) for a quick smoke run. Parameters are introduced in the cell that
first needs them. Scoped metrics show the all-item table, and the same numbers under each
exclusion fold out below it.
Parameters¶
SNAPSHOT = "2026-09-14"
# Sample rate as a count: keep one item in N, chosen by id hash. 1 is the census,
# 100 keeps one percent of items for a smoke run.
SAMPLE_ONE_IN = 1
Setup¶
Two modules live next to this notebook. helpers.py is plumbing: the cached Spark
session, run_hql for the files under hql/, the class-list loader and the CSV cache
behind metric. display.py is rendering: show formats counts with thousands
separators and named columns as percentages, show_by_scope prints the all-item
table and folds the exclusion scopes under it. Autoreload
picks up edits to both without a kernel restart.
import sys
from functools import partial
from pathlib import Path
sys.path.insert(0, str(Path("~/wikidata-statement-count").expanduser()))
%load_ext autoreload
%autoreload 2
import pandas as pd
import display
import helpers
Snapshot¶
Confirms the requested snapshot exists.
spark = helpers.get_spark("yarn-large" if SAMPLE_ONE_IN == 1 else "yarn-regular")
partitions = spark.sql("SHOW PARTITIONS wmf.wikidata_entity").toPandas()
print(partitions.tail(5).to_string(index=False))
assert f"snapshot={SNAPSHOT}" in set(partitions.iloc[:, 0]), (
f"{SNAPSHOT} not loaded yet"
)
partition snapshot=2026-08-17 snapshot=2026-08-24 snapshot=2026-08-31 snapshot=2026-09-07 snapshot=2026-09-14
Astronomical object classes¶
The subclass closure of astronomical object (Q6999), with a flag for the artificial celestial body (Q11375966) subtree. Fetched from the query service only when the committed CSV is missing, so the census stays tied to the list in the repo.
astronomical_classes = helpers.load_astronomical_classes()
print(
len(astronomical_classes),
"classes,",
astronomical_classes["artificial"].sum(),
"artificial",
)
1604 classes, 559 artificial
Flatten pass¶
The one heavy job: every claim of every sampled item becomes one row in an Iceberg
table partitioned by snapshot (hql/insert_statements.hql), with the rank, the
best-rank flag and the three subject flags defined in the README's data notes. When the
partition already holds rows nothing is computed. Otherwise this cell starts
flatten.py under spark3-submit in a detached tmux session on the stat host and
waits for it, so a dropped connection or a kernel restart does not lose the run; rerun
the cell to wait again, or tmux attach -t census in a terminal to watch.
HDFS_TABLE = helpers.DEFAULT_TABLE
statements_table, statement_rows = helpers.flatten_in_tmux(
SNAPSHOT, SAMPLE_ONE_IN, HDFS_TABLE
)
print(f"{statement_rows:,} statement rows in {statements_table} for {SNAPSHOT}")
metric = partial(
helpers.load_metric,
sample_one_in=SAMPLE_ONE_IN,
table=statements_table,
snapshot=SNAPSHOT,
)
Flatten pass launched in tmux session census, log in /srv/home/itamar/wikidata-statement-count/data/cache/flatten-2026-09-14.log 5 minutes, still running 1,779,722,803 statement rows in itamar.wikidata_statements for 2026-09-14
Subject flags and categories¶
Metric 6: subjects and statements per category. The two scholarly definitions sit side
by side here and nowhere else: the P31 rule takes any instance of scholarly article
statement whatever its rank, the WDQS rule (the graph split's) takes a non-deprecated
one or a non-deprecated publication type of scholarly work, so it drops subjects
whose only such statement is deprecated and adds those carrying only the publication
type, a few tens of thousands more in net. Every later metric uses the WDQS rule. The last row is the overlap between scholarly articles and astronomical
objects. share columns are the category's part of all item statements and of their
subjects.
by_category = metric("by_category")
for column in ("subjects", "statements"):
by_category[f"{column}_share"] = by_category[column] / by_category[column].iloc[0]
display.show(by_category, percent=("subjects_share", "statements_share"))
| Category | Subjects | Statements | Subjects share | Statements share |
|---|---|---|---|---|
| All item statements | 121,029,588 | 1,779,722,803 | 100.000% | 100.000% |
| Scholarly (P31) | 46,006,591 | 986,807,378 | 38.013% | 55.447% |
| Scholarly (WDQS) | 46,041,178 | 987,410,356 | 38.041% | 55.481% |
| Astronomical | 8,406,244 | 151,236,426 | 6.946% | 8.498% |
| Scholarly (WDQS) and astronomical | 2 | 33 | 0.000% | 0.000% |
Metric 1: total statements¶
Statements, distinct subjects, best-ranked and deprecated counts by scope: everything, and with each category of subjects left out, so the impact of an exclusion reads directly. Property statements are counted from the snapshot itself, since the table holds item statements only.
def compute_total_statements():
return pd.concat(
[
helpers.run_hql(
"total_statements", table=statements_table, snapshot=SNAPSHOT
).toPandas(),
helpers.run_hql("property_statements", snapshot=SNAPSHOT).toPandas(),
],
ignore_index=True,
)
total_statements = helpers.load_or_compute(
"total_statements.csv", SAMPLE_ONE_IN, SNAPSHOT, compute_total_statements
)
display.show(total_statements)
| Scope | Statements | Subjects | Best-ranked | Deprecated |
|---|---|---|---|---|
| All item statements | 1,779,722,803 | 121,029,588 | 1,740,429,679 | 12,516,190 |
| Excl. scholarly (WDQS) | 792,312,447 | 74,988,410 | 754,712,181 | 10,961,590 |
| Excl. astronomical | 1,628,486,377 | 112,623,344 | 1,618,065,346 | 2,471,168 |
| Excl. scholarly (WDQS) and astronomical | 641,076,054 | 66,582,168 | 632,347,881 | 916,568 |
| Property statements | 324,114 | 13,929 | 2,934 |
The WDQS graph-split figures posted on T434870 (2026-09-11, read from the live graph, so later than the dump) under our rows for comparison. They are not part of the result file. Excluding scholarly articles by the WDQS rule should land close to the split's main graph.
wdqs_split_2026_09_11 = pd.DataFrame(
{
"scope": ["wdqs_main_graph", "wdqs_scholarly_graph", "wdqs_total"],
"statements": [797_082_912, 990_953_282, 1_788_036_194],
"best_ranked": [759_496_349, 989_259_590, 1_748_755_939],
"deprecated": [10_956_669, 1_555_425, 12_512_094],
}
)
display.show(pd.concat([total_statements, wdqs_split_2026_09_11], ignore_index=True))
| Scope | Statements | Subjects | Best-ranked | Deprecated |
|---|---|---|---|---|
| All item statements | 1,779,722,803 | 121,029,588 | 1,740,429,679 | 12,516,190 |
| Excl. scholarly (WDQS) | 792,312,447 | 74,988,410 | 754,712,181 | 10,961,590 |
| Excl. astronomical | 1,628,486,377 | 112,623,344 | 1,618,065,346 | 2,471,168 |
| Excl. scholarly (WDQS) and astronomical | 641,076,054 | 66,582,168 | 632,347,881 | 916,568 |
| Property statements | 324,114 | 13,929 | 2,934 | |
| WDQS main graph | 797,082,912 | 759,496,349 | 10,956,669 | |
| WDQS scholarly graph | 990,953,282 | 989,259,590 | 1,555,425 | |
| WDQS total | 1,788,036,194 | 1,748,755,939 | 12,512,094 |
Metric 2: statements per data type¶
Statements, best-ranked and deprecated counts per property data type and scope. The
data type is the property's declared type as carried on the main snak, so novalue and
somevalue statements count under their property's type; metric 3 splits them out by
value type. A (missing) type means the snak carries no datatype field, which happens
for statements whose property was later deleted.
share is the type's part of the statements in that scope; the folded table gives the
statements per type under each exclusion with that share in parentheses.
by_data_type = metric("by_data_type").fillna({"data_type": "(missing)"})
by_data_type["share"] = by_data_type["statements"] / by_data_type.groupby("scope")[
"statements"
].transform("sum")
display.show_by_scope(
by_data_type, "data_type", percent=("share",), pivot_percent="share"
)
| Data type | Statements | Best-ranked | Deprecated | Share |
|---|---|---|---|---|
| wikibase-item | 883,728,869 | 879,216,835 | 3,557,457 | 49.655% |
| string | 330,521,707 | 329,192,464 | 304,846 | 18.572% |
| external-id | 270,207,677 | 269,628,130 | 479,506 | 15.183% |
| quantity | 106,578,426 | 76,152,424 | 6,559,262 | 5.988% |
| time | 75,516,649 | 75,067,305 | 45,241 | 4.243% |
| monolingualtext | 68,359,320 | 66,655,598 | 1,466,690 | 3.841% |
| url | 23,575,340 | 23,447,459 | 83,064 | 1.325% |
| globe-coordinate | 12,608,445 | 12,555,070 | 11,155 | 0.708% |
| commonsMedia | 8,281,176 | 8,169,743 | 8,837 | 0.465% |
| math | 147,751 | 147,689 | 27 | 0.008% |
| tabular-data | 70,605 | 70,596 | 0 | 0.004% |
| geo-shape | 67,695 | 67,406 | 11 | 0.004% |
| wikibase-property | 34,795 | 34,680 | 69 | 0.002% |
| wikibase-lexeme | 11,667 | 11,604 | 24 | 0.001% |
| wikibase-form | 11,374 | 11,369 | 1 | 0.001% |
| musical-notation | 1,052 | 1,052 | 0 | 0.000% |
| entity-schema | 250 | 250 | 0 | 0.000% |
| wikibase-sense | 4 | 4 | 0 | 0.000% |
| (missing) | 1 | 1 | 0 | 0.000% |
Under each exclusion
| Data type | Excl. scholarly (WDQS) | Excl. astronomical | Excl. scholarly (WDQS) and astronomical |
|---|---|---|---|
| wikibase-item | 369,987,160 (46.697%) | 854,700,236 (52.484%) | 340,958,541 (53.185%) |
| string | 61,552,758 (7.769%) | 300,548,875 (18.456%) | 31,579,936 (4.926%) |
| external-id | 182,398,070 (23.021%) | 256,813,247 (15.770%) | 169,003,643 (26.362%) |
| quantity | 104,370,000 (13.173%) | 27,878,503 (1.712%) | 25,670,077 (4.004%) |
| time | 25,888,991 (3.268%) | 75,398,749 (4.630%) | 25,771,094 (4.020%) |
| monolingualtext | 19,369,874 (2.445%) | 68,358,050 (4.198%) | 19,368,606 (3.021%) |
| url | 7,518,639 (0.949%) | 23,575,178 (1.448%) | 7,518,477 (1.173%) |
| globe-coordinate | 12,608,385 (1.591%) | 12,607,896 (0.774%) | 12,607,836 (1.967%) |
| commonsMedia | 8,273,587 (1.044%) | 8,260,450 (0.507%) | 8,252,862 (1.287%) |
| math | 147,746 (0.019%) | 147,750 (0.009%) | 147,745 (0.023%) |
| tabular-data | 70,605 (0.009%) | 70,605 (0.004%) | 70,605 (0.011%) |
| geo-shape | 67,695 (0.009%) | 67,695 (0.004%) | 67,695 (0.011%) |
| wikibase-property | 34,647 (0.004%) | 34,795 (0.002%) | 34,647 (0.005%) |
| wikibase-lexeme | 11,609 (0.001%) | 11,667 (0.001%) | 11,609 (0.002%) |
| wikibase-form | 11,374 (0.001%) | 11,374 (0.001%) | 11,374 (0.002%) |
| musical-notation | 1,052 (0.000%) | 1,052 (0.000%) | 1,052 (0.000%) |
| entity-schema | 250 (0.000%) | 250 (0.000%) | 250 (0.000%) |
| wikibase-sense | 4 (0.000%) | 4 (0.000%) | 4 (0.000%) |
| (missing) | 1 (0.000%) | 1 (0.000%) | 1 (0.000%) |
Metric 3: statements per value type¶
The same tables keyed by the kind of value a statement holds. For value snaks this is
the value type (wikibase-entityid, string, time, quantity, ...); novalue and
somevalue snaks carry no value, so they appear under their snak type instead. This is
the split by value type that T437880 asks for, and it is where the store's
wbst_value_type column and its null-value case get their sizes; metric 7 looks at
novalue and somevalue per property type.
share is the kind's part of the statements in that scope; the folded table gives
the statements per kind under each exclusion with that share in parentheses.
by_value_type = metric("by_value_type")
by_value_type["share"] = by_value_type["statements"] / by_value_type.groupby("scope")[
"statements"
].transform("sum")
display.show_by_scope(
by_value_type, "value_kind", percent=("share",), pivot_percent="share"
)
| Value kind | Statements | Best-ranked | Deprecated | Share |
|---|---|---|---|---|
| wikibase-entityid | 883,098,954 | 878,589,998 | 3,556,932 | 49.620% |
| string | 632,786,841 | 630,639,141 | 875,668 | 35.555% |
| quantity | 106,388,503 | 76,089,054 | 6,433,000 | 5.978% |
| time | 75,401,077 | 74,952,664 | 44,973 | 4.237% |
| monolingualtext | 68,352,526 | 66,648,855 | 1,466,680 | 3.841% |
| globecoordinate | 12,588,205 | 12,534,925 | 11,115 | 0.707% |
| somevalue | 885,908 | 755,442 | 127,217 | 0.050% |
| novalue | 220,789 | 219,600 | 605 | 0.012% |
Under each exclusion
| Value kind | Excl. scholarly (WDQS) | Excl. astronomical | Excl. scholarly (WDQS) and astronomical |
|---|---|---|---|
| wikibase-entityid | 369,362,306 (46.618%) | 854,070,647 (52.446%) | 340,334,013 (53.088%) |
| string | 259,944,632 (32.808%) | 589,398,719 (36.193%) | 216,556,524 (33.780%) |
| quantity | 104,180,080 (13.149%) | 27,688,617 (1.700%) | 25,480,194 (3.975%) |
| time | 25,773,426 (3.253%) | 75,283,187 (4.623%) | 25,655,539 (4.002%) |
| monolingualtext | 19,363,097 (2.444%) | 68,351,258 (4.197%) | 19,361,831 (3.020%) |
| globecoordinate | 12,588,145 (1.589%) | 12,587,658 (0.773%) | 12,587,598 (1.964%) |
| somevalue | 881,193 (0.111%) | 885,749 (0.054%) | 881,034 (0.137%) |
| novalue | 219,568 (0.028%) | 220,542 (0.014%) | 219,321 (0.034%) |
Metric 4: distinct values and distinct (property, value) pairs¶
How much of the value column repeats. Per value type and scope: statements, distinct
values, and distinct (property, value) pairs, counting value snaks only. Two values
are the same when their value_key matches: the entity id for entity values, the raw
value JSON otherwise, so a quantity written with a different precision is a different
value. These are the sizes a value table keyed by value, or by property and value,
would have beside the statements table.
repeated_values is the share of statements whose value also occurs on another
statement of the same type; repeated_pairs the same for the (property, value) pair.
Both are computed here from the counts, not stored. The folded table gives the
statements per type under each exclusion with repeated_values in parentheses.
distinct_values = metric("distinct_values")
distinct_values["repeated_values"] = (
1 - distinct_values["distinct_values"] / distinct_values["statements"]
)
distinct_values["repeated_pairs"] = (
1 - distinct_values["distinct_pairs"] / distinct_values["statements"]
)
display.show_by_scope(
distinct_values,
"value_type",
percent=("repeated_values", "repeated_pairs"),
pivot_percent="repeated_values",
)
| Value type | Statements | Distinct values | Distinct pairs | Repeated values | Repeated pairs |
|---|---|---|---|---|---|
| wikibase-entityid | 883,098,954 | 42,698,660 | 55,572,809 | 95.165% | 93.707% |
| string | 632,786,841 | 299,458,736 | 384,016,188 | 52.676% | 39.313% |
| quantity | 106,388,503 | 39,663,061 | 41,605,264 | 62.719% | 60.893% |
| time | 75,401,077 | 373,402 | 1,242,888 | 99.505% | 98.352% |
| monolingualtext | 68,352,526 | 62,702,717 | 62,961,642 | 8.266% | 7.887% |
| globecoordinate | 12,588,205 | 12,312,983 | 12,317,597 | 2.186% | 2.150% |
Under each exclusion
| Value type | Excl. scholarly (WDQS) | Excl. astronomical | Excl. scholarly (WDQS) and astronomical |
|---|---|---|---|
| wikibase-entityid | 369,362,306 (94.893%) | 854,070,647 (95.038%) | 340,334,013 (94.551%) |
| string | 259,944,632 (28.540%) | 589,398,719 (54.986%) | 216,556,524 (30.012%) |
| quantity | 104,180,080 (61.928%) | 27,688,617 (89.267%) | 25,480,194 (88.337%) |
| time | 25,773,426 (98.562%) | 75,283,187 (99.504%) | 25,655,539 (98.556%) |
| monolingualtext | 19,363,097 (21.777%) | 68,351,258 (8.266%) | 19,361,831 (21.778%) |
| globecoordinate | 12,588,145 (2.186%) | 12,587,658 (2.186%) | 12,587,598 (2.186%) |
Metric 5: statements per rank¶
Statements per rank and scope, with the best-ranked count beside each: every preferred
statement is best, a normal one is best only when its subject has no preferred
statement for the property, a deprecated one never is. share is the rank's part of
the statements in that scope, best_share the part of the rank that is best-ranked, so
the normal row shows how many normal statements a preferred sibling shadows.
A fifth scope, scholarly_wdqs, holds the subjects the WDQS graph split moves to the
scholarly graph, so the split table on T434870 can be rebuilt from excl_scholarly_wdqs
(main), scholarly_wdqs (scholarly) and all_item_statements (total). It is not one
of the exclusions, so it appears only in the next cell. The scope sums are checked
against metric 1.
by_rank = metric("by_rank")
by_rank["share"] = by_rank["statements"] / by_rank.groupby("scope")[
"statements"
].transform("sum")
by_rank["best_share"] = by_rank["best_ranked"] / by_rank["statements"]
scope_sums = by_rank.groupby("scope")["statements"].sum()
checked = total_statements[total_statements["scope"].isin(scope_sums.index)]
assert (scope_sums[checked["scope"]].values == checked["statements"].values).all()
display.show_by_scope(
by_rank, "rank", percent=("share", "best_share"), pivot_percent="share"
)
| Rank | Statements | Best-ranked | Share | Best-ranked share |
|---|---|---|---|---|
| normal | 1,760,711,229 | 1,733,934,295 | 98.932% | 98.479% |
| deprecated | 12,516,190 | 0 | 0.703% | 0.000% |
| preferred | 6,495,384 | 6,495,384 | 0.365% | 100.000% |
Under each exclusion
| Rank | Excl. scholarly (WDQS) | Excl. astronomical | Excl. scholarly (WDQS) and astronomical |
|---|---|---|---|
| normal | 774,989,612 (97.814%) | 1,623,423,013 (99.689%) | 637,701,429 (99.474%) |
| deprecated | 10,961,590 (1.383%) | 2,471,168 (0.152%) | 916,568 (0.143%) |
| preferred | 6,361,245 (0.803%) | 2,592,196 (0.159%) | 2,458,057 (0.383%) |
The preferred row of the split table on T434870 (2026-09-11) does not add up as posted: main and scholarly sum to 6,494,097, not to the 6,373,097 in the total column. Our preferred counts beside the posted ones, same later snapshot caveat as in metric 1.
posted_preferred_2026_09_11 = pd.DataFrame(
{
"scope": ["excl_scholarly_wdqs", "scholarly_wdqs", "all_item_statements"],
"wdqs_split_column": ["main", "scholarly", "total"],
"posted_preferred": [6_360_022, 134_075, 6_373_097],
}
)
display.show(
posted_preferred_2026_09_11.merge(
by_rank[by_rank["rank"] == "preferred"][["scope", "statements"]].rename(
columns={"statements": "preferred"}
),
on="scope",
)
)
| Scope | WDQS split column | Posted preferred | Preferred |
|---|---|---|---|
| Excl. scholarly (WDQS) | main | 6,360,022 | 6,361,245 |
| Scholarly (WDQS) | scholarly | 134,075 | 134,139 |
| All item statements | total | 6,373,097 | 6,495,384 |
Metric 7: novalue and somevalue¶
Statements without a value, per property data type and scope: novalue says the
property is known to have no value, somevalue that the value exists but is unknown.
Metric 3 gave their totals; this is where they land per data type, which is what a
store needs to know before deciding how a null value is represented per type.
One row per data type, sorted by its no-value statements, with a column per snak
type; the share in parentheses is the snak type's part of all statements of that data
type in the scope, joined from metric 2, so a type where unknown values are common
stands out.
snak_types = metric("snak_types").fillna({"data_type": "(missing)"})
snak_types = snak_types.merge(
by_data_type[["scope", "data_type", "statements"]].rename(
columns={"statements": "statements_of_type"}
),
on=["scope", "data_type"],
)
snak_types["share_of_type"] = (
snak_types["statements"] / snak_types["statements_of_type"]
)
display.show_by_scope(
snak_types, "data_type", pivot_percent="share_of_type", columns="snak_type"
)
| Data type | novalue | somevalue |
|---|---|---|
| wikibase-item | 126,000 (0.014%) | 561,949 (0.064%) |
| quantity | 1,898 (0.002%) | 188,025 (0.176%) |
| time | 5,113 (0.007%) | 110,459 (0.146%) |
| external-id | 66,306 (0.025%) | 1,698 (0.001%) |
| globe-coordinate | 1,938 (0.015%) | 18,302 (0.145%) |
| string | 10,005 (0.003%) | 3,550 (0.001%) |
| monolingualtext | 5,700 (0.008%) | 1,094 (0.002%) |
| url | 2,206 (0.009%) | 247 (0.001%) |
| commonsMedia | 1,572 (0.019%) | 565 (0.007%) |
| wikibase-lexeme | 25 (0.214%) | 11 (0.094%) |
| math | 13 (0.009%) | 1 (0.001%) |
| wikibase-property | 11 (0.032%) | 3 (0.009%) |
| wikibase-form | 1 (0.009%) | 4 (0.035%) |
| wikibase-sense | 1 (25.000%) |
Under each exclusion
| Data type | Excl. scholarly (WDQS) | Excl. astronomical | Excl. scholarly (WDQS) and astronomical | |||
|---|---|---|---|---|---|---|
| novalue | somevalue | novalue | somevalue | novalue | somevalue | |
| wikibase-item | 124,982 (0.034%) | 557,700 (0.151%) | 125,806 (0.015%) | 561,817 (0.066%) | 124,788 (0.037%) | 557,568 (0.164%) |
| quantity | 1,895 (0.002%) | 188,025 (0.180%) | 1,878 (0.007%) | 188,008 (0.674%) | 1,875 (0.007%) | 188,008 (0.732%) |
| time | 5,109 (0.020%) | 110,456 (0.427%) | 5,106 (0.007%) | 110,456 (0.146%) | 5,102 (0.020%) | 110,453 (0.429%) |
| external-id | 66,194 (0.036%) | 1,675 (0.001%) | 66,284 (0.026%) | 1,693 (0.001%) | 66,172 (0.039%) | 1,670 (0.001%) |
| globe-coordinate | 1,938 (0.015%) | 18,302 (0.145%) | 1,936 (0.015%) | 18,302 (0.145%) | 1,936 (0.015%) | 18,302 (0.145%) |
| string | 9,931 (0.016%) | 3,118 (0.005%) | 10,004 (0.003%) | 3,549 (0.001%) | 9,930 (0.031%) | 3,117 (0.010%) |
| monolingualtext | 5,690 (0.029%) | 1,087 (0.006%) | 5,699 (0.008%) | 1,093 (0.002%) | 5,689 (0.029%) | 1,086 (0.006%) |
| url | 2,206 (0.029%) | 247 (0.003%) | 2,206 (0.009%) | 247 (0.001%) | 2,206 (0.029%) | 247 (0.003%) |
| commonsMedia | 1,572 (0.019%) | 564 (0.007%) | 1,572 (0.019%) | 565 (0.007%) | 1,572 (0.019%) | 564 (0.007%) |
| wikibase-lexeme | 25 (0.215%) | 11 (0.095%) | 25 (0.214%) | 11 (0.094%) | 25 (0.215%) | 11 (0.095%) |
| math | 13 (0.009%) | 1 (0.001%) | 13 (0.009%) | 1 (0.001%) | 13 (0.009%) | 1 (0.001%) |
| wikibase-property | 11 (0.032%) | 3 (0.009%) | 11 (0.032%) | 3 (0.009%) | 11 (0.032%) | 3 (0.009%) |
| wikibase-form | 1 (0.009%) | 4 (0.035%) | 1 (0.009%) | 4 (0.035%) | 1 (0.009%) | 4 (0.035%) |
| wikibase-sense | 1 (25.000%) | 1 (25.000%) | 1 (25.000%) | |||
Metric 8: statement id forms¶
Wikibase mints statement ids as <subject id>$<uppercase v4 uuid>, accepts any
prefix that parses as an item id regardless of case, keeps the prefix as written, and
never validates the uuid part. A store that keys rows by GUID needs to know how far
the minted shape holds. The forms are exclusive: canonical is what the generator
writes; lowercase_uuid and mixed_case_uuid are well shaped ids minted by clients,
which a BINARY(16) column would fold onto the canonical ones and a string column
would keep apart; lowercase_prefix ids match their subject ignoring case only;
prefix_mismatch ids name another item; malformed ids have no single separator, a
prefix that is not an item id, or a uuid part that is not 8-4-4-4-12 hex, so they
cannot be packed into 16 bytes at all. share is the form's part of all item
statements, distinct_ids counts distinct ids within the form only, and the lengths
are characters. The identity questions do not depend on the category exclusions, so
there is a single scope here.
identity_guid = metric("identity_guid")
identity_guid["share"] = identity_guid["statements"] / identity_guid["statements"].sum()
assert identity_guid["statements"].sum() == total_statements.loc[
total_statements["scope"] == "all_item_statements", "statements"
].item()
display.show(identity_guid, percent=("share",))
| Id form | Statements | Best-ranked | Distinct ids | Min length | Max length | Average length | Share |
|---|---|---|---|---|---|---|---|
| Canonical | 1,622,965,314 | 1,587,202,957 | 1,622,965,311 | 39 | 47 | 46 | 91.192% |
| Lowercase uuid | 140,492,371 | 137,007,918 | 140,492,371 | 39 | 47 | 46 | 7.894% |
| Lowercase prefix | 16,265,065 | 16,218,753 | 16,265,065 | 39 | 47 | 45 | 0.914% |
| Malformed | 51 | 51 | 51 | 47 | 47 | 47 | 0.000% |
| Prefix mismatch | 2 | 0 | 1 | 47 | 47 | 47 | 0.000% |
Metric 9: identity column bytes¶
What an identity column costs per row across the whole table, for the two widths
under discussion: 60 bytes is the VARBINARY(60) statement id column of the proposed
store, 16 bytes a packed uuid; the third option, no identity column, costs nothing.
The two populations are all item statements and the best-ranked ones only, since a
store might hold only the latter. The numbers are metric 1 times the width, so the
widths are upper bounds: a VARBINARY(60) stores the actual id length plus one, which
metric 8 puts at about 47 bytes on average. Gigabytes are decimal (10^9), gibibytes
binary (2^30).
def compute_identity_bytes():
totals = total_statements.loc[
total_statements["scope"] == "all_item_statements"
].iloc[0]
populations = {
"all_item_statements": int(totals["statements"]),
"best_ranked": int(totals["best_ranked"]),
}
return pd.DataFrame(
[
{
"population": population,
"bytes_per_row": width,
"statements": statements,
"bytes": statements * width,
}
for population, statements in populations.items()
for width in (60, 16)
]
)
identity_bytes = helpers.load_or_compute(
"identity_bytes.csv", SAMPLE_ONE_IN, SNAPSHOT, compute_identity_bytes
)
identity_bytes["gigabytes"] = identity_bytes["bytes"] / 10**9
identity_bytes["gibibytes"] = identity_bytes["bytes"] / 2**30
display.show(identity_bytes)
| Population | Bytes per row | Statements | Bytes | Gigabytes | Gibibytes |
|---|---|---|---|---|---|
| All item statements | 60 | 1,779,722,803 | 106,783,368,180 | 107 | 99 |
| All item statements | 16 | 1,779,722,803 | 28,475,564,848 | 28 | 27 |
| Best-ranked | 60 | 1,740,429,679 | 104,425,780,740 | 104 | 97 |
| Best-ranked | 16 | 1,740,429,679 | 27,846,874,864 | 28 | 26 |