On Fri, Mar 11, 2016 at 11:57 AM, Stas Malyshev <smalyshev@wikimedia.org> wrote:

Hi!

> Hi! I'm Felipe Hoffa at Google, and I've been playing with Wikidata's
> data in BigQuery:

This looks pretty cool!

Thanks!

 

> (Wikidata has 54 cats)

query.wikidata.org has 59:

http://tinyurl.com/ju7xsn2

I wonder why the difference?

Also, looks like your query:

    WHERE JSON_EXTRACT_SCALAR(item,
'$.claims.P31[0].mainsnak.datavalue.value.numeric-id')='146' #cats

only checks the first claim. But there could be more than one P31 claims
in Wikidata.

Exactly, that's the problem (with a solution):

- JSON_EXTRACT_SCALAR() is not expressive enough to allow me to visit all elements in an array.

- But being able to write JS code gives me the flexibility to go and visit each of the claims.

- Instead of running JS code each time, I can create these derived tables that allow me to write code like:

  SELECT en_wiki
  FROM [fh-bigquery:wikidata.latest_en_v1]
  OMIT RECORD IF EVERY(instance_of.numeric_id!=146)

That looks into every claim of each item (that this table has as nested elements in each row).

But now I only get 41 cats - because these are the cats that have an enwiki page.

I could do the same for the whole Wikidata, instead of only the elements connected to Wikipedia, but to GerardM's point, why is this so bad? I'm interested in linking Wikidata to external sources, hence I derive a table for that purpose. I'm happy to derive other tables for different purposes, if someone sees the use case.

> - Advice, feedback?

More cool examples! :)

Ok, ok. Fictional characters and superheroe:

https://github.com/fhoffa/code_snippets/blob/master/wikidata/fictional_and_superheroes.csv

 SELECT en_wiki, SUM(requests) requests,
  FIRST(superhero) superhero, FIRST(fictional_character) fictional_character, FIRST(occupation_superhero) occupation_superhero,
  VARIANCE(LOG(requests)) varlog
FROM [fh-bigquery:wikipedia.pagecounts_201602_en_top365k] a
JOIN (
  SELECT en_wiki,
    EVERY(instance_of.numeric_id!=188784) WITHIN RECORD superhero,
    EVERY(instance_of.numeric_id!=95074) WITHIN RECORD fictional_character,
    EVERY(occupation.numeric_id!=188784) WITHIN RECORD occupation_superhero,
  FROM [wikidata.latest_en_v1]
  OMIT RECORD IF (EVERY(instance_of.numeric_id!=188784)
  AND EVERY(instance_of.numeric_id!=95074)
  AND EVERY(occupation.numeric_id!=188784))
) b
ON a.title=b.en_wiki
GROUP BY 1
HAVING varlog<0.5
ORDER BY 2 DESC
LIMIT 100

What's interesting about this query:

- Some are instance of fictional characters.

- Some are instance of superhero.

- Some have occupation superhero.

Notice that I'm filtering by VARIANCE(LOG(pageviews)), because I don't like spiky data. Makes my results look prettier, but it also shows how having hourly data instead of monthly summary of pageviews allow me to do some fancier filtering.

 

> - My "raw to table" javascript code is incomplete and not very pretty -
> which columns would you want extracted?
> https://github.com/fhoffa/code_snippets/blob/master/wikidata/create_wiki_en_table.sql

That's hard to say - properties can vary a lot, especially depending on
kind of items (birth date useful for humans, less so for buildings or
paintings :) So it probably has to be rather flexible to cover varied
cases.

Something I need to figure out now: How to navigate the tree of sub-classes. Probably I'll create a 'convolution table', but maybe there's something out there I can use?

Thanks!

 

--
Stas Malyshev
smalyshev@wikimedia.org

_______________________________________________
Wikidata mailing list
Wikidata@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata

_______________________________________________
Wikidata mailing list
Wikidata@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata