- First step: build the SPARQL query<\/a><\/li>
- Second step: enrich the SPARQL query with aggregation<\/a><\/li>
- Third step: generate a data feed<\/a><\/li><\/ol><\/li>
- To MS Azure Data Factory<\/a><\/li>
- To AWS Glue<\/a><\/li>
- To GCP<\/a><\/li>
- My conclusion<\/a><\/li><\/ol>\n\n\n
From Wikipedia<\/h2>\n\n\n
Data description: women’s profiles that are less tagged, less connected to groups, and therefore quite lost within the information system. I limited my target to the French-written part of Wikipedia<\/p>\n\n\n\n
What I learned (the hard way) on Wikipedia: APIs connect to articles’ management metadata<\/a> and SPARQL connect to data and categories<\/a><\/p>\n\n\n
First step: build the SPARQL query<\/h3>\n\n\n
I won’t give a course on Wikidata’s architecture: the Wikidata Query Service<\/a> offers a Query Helper to help finding the parameters for building the right query.<\/p>\n\n\n\n
![\"\"](\"https:\/\/aubertm.me\/wp-content\/uploads\/2024\/10\/image.png?w=800\")
<\/figure>\n\n\n\nI combined :<\/p>\n\n\n\n
- \n
- parameter P21 sex or gender: category Q6581072 female<\/li>\n\n\n\n
- parameter P1412 languages spoken, written or signed: category Q150 French<\/li>\n\n\n\n
- parameter of wikibase:language “fr”<\/li>\n<\/ul>\n\n\n\n
In order to check that the query runs, it’s better to first count results.<\/p>\n\n\n\n
SELECT (COUNT (?item) as ?count)\nWHERE {\n ?item wdt:P21 wd:Q6581072;\n wdt:P1412 wd:Q150. \n SERVICE wikibase:label { bd:serviceParam wikibase:language \"fr\".} \n}<\/code><\/pre>\n\n\n\nResult: 70349 records (view live<\/a>)<\/p>\n\n\n\n
Then the query can be checked with data vizualisation: let’s limit results to 100.<\/p>\n\n\n\n
SELECT ?item ?itemLabel\nWHERE {\n ?item wdt:P21 wd:Q6581072;\n wdt:P1412 wd:Q150. \n SERVICE wikibase:label { bd:serviceParam wikibase:language \"fr\".} \n}\nLIMIT 100<\/code><\/pre>\n\n\n\nResult: 100 results in 3422 ms (view live<\/a>)<\/p>\n\n\n
Second step: enrich the SPARQL query with aggregation<\/h3>\n\n\n
Let’s then aggregate the fields “has part(s)” and “part of”:<\/p>\n\n\n\n
SELECT ?item ?itemLabel\n(COUNT (?has_part_s_) as ?count_has_part_s_)\n(COUNT (?part_of) as ?count_part_of)\nWHERE {\n ?item wdt:P21 wd:Q6581072;\n wdt:P1412 wd:Q150.\n SERVICE wikibase:label { bd:serviceParam wikibase:language \"fr\".} \n OPTIONAL { ?item wdt:P527 ?has_part_s_. }\n OPTIONAL { ?item wdt:P361 ?part_of. }\n}\nGROUP BY ?item ?itemLabel<\/code><\/pre>\n\n\n\nResults: 70349 r\u00e9sultats en 43383\u202fms (view live<\/a>)<\/p>\n\n\n\n
NB: to get ?itemLabel<\/em> appearing in the results, it has to be mentionned in GROUP BY.<\/p>\n\n\n
Third step: generate a data feed<\/h3>\n\n\n
The Wikidata SPARQL sandbox offers many different ways of sharing:<\/p>\n\n\n\n
- \n
- Sharing the query via: URL,HTML, Wikilink, PHP, JavaScript (jQuery), JavaScript (modern), Java, Perl, Python, Python (Pywikibot), Ruby, R, Matlab, listeria, mapframe<\/li>\n\n\n\n
- Download results via: JSON file, JSON file (verbose), TSV file, TSV file (verbose), CSV file, HTML table<\/li>\n\n\n\n
- Get a link to: Short URL to query, Short URL to result, SPARQL Endpoint, Embed result<\/li>\n<\/ul>\n\n\n\n
We’ll check with each technologie what’s the best feed format to use.<\/p>\n\n\n
To MS Azure Data Factory<\/h2>\n\n\n
MS Azure does not handle SPARQL<\/a>. It is therefore necessary to export the dataflow in another format: I chose as a JSON feed. The good practices ask to import it into a JSON file in an Azure blob storage before transforming it into another file format. Finally, I chose PARQUET as an an handier end file format.<\/p>\n\n\n\n
- \n
- Create a new pipeline in Azure Data Factory<\/a> \n
- \n
- To copy the JSON feed into a JSON file<\/li>\n\n\n\n
- Mention Wikidata time limit: 60 seconds; retry after 120seconds<\/li>\n\n\n\n
- Import its schema<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Source: create a new datasource with a GET connection to HTTP anonymous URL\n
- \n
- as base URL https:\/\/query.wikidata.org\/<\/a> <\/li>\n\n\n\n
- as relative URL as REST in JSON format and the SPARQL query\n
- \n
- \/sparql?format=json&query=SELECT ?item ?itemLabel (COUNT (?has_part_s_) as ?count_has_part_s_) (COUNT (?part_of) as ?count_part_of) WHERE { ?item wdt:P21 wd:Q6581072; wdt:P1412 wd:Q150. SERVICE wikibase:label { bd:serviceParam wikibase:language “fr”.} OPTIONAL { ?item wdt:P527 ?has_part_s_. } OPTIONAL { ?item wdt:P361 ?part_of. } } GROUP BY ?item ?itemLabel<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n
- and a header to announce you’re not a bot; for example:\n
- \n
- User-Agent\u202f: AzureLearning\/0.0 (https:\/\/www.aubertm.me marina@aubertm.me) <\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
- \n
- Destination: create a storage account<\/a> then a blob container to store the JSON output dataset.<\/li>\n\n\n\n
- Map the source fields for the destination fields.<\/li>\n\n\n\n
- \/!\\ Important<\/a>: validate all, publish all, then import a second time the output dataset format.
Then again : validate all, publish all
Validate the pipeline, Validate runtime, Debug<\/li>\n\n\n\n- Create a second pipeline in Azure Data Factory\n
- \n
- To copy and flatten the JSON file into a PARQUET file<\/li>\n\n\n\n
- Source: the JSON file you just created<\/li>\n\n\n\n
- Destination: you can use the same blob container as before.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- again :
\/!\\ Important: validate all, publish all, then import a second time the output dataset format.
Then again : validate all, publish all
Validate the pipeline, Validate runtime, Debug<\/li>\n<\/ol>\n\n\n\nYou can visualize data from the datasets.<\/p>\n\n\n\n
![\"\"](\"https:\/\/aubertm.me\/wp-content\/uploads\/2024\/10\/image-3.png?w=800\")
<\/figure>\n\n\n\nCode is here<\/a><\/p>\n\n\n
To AWS Glue<\/h2>\n\n\n
AWS does not handle SPARQL either. Let’s keep on using our Wikidata’s JSON feed.<\/p>\n\n\n\n
- \n
- In AWS Glue Studio<\/a>, configure IAM<\/li>\n\n\n\n
- In Amazon S3 storage<\/a>, create a bucket and its endpoint<\/li>\n\n\n\n
- In AWS Glue Studio,\n
- \n
- create a Python notebook to connect the Wikidata’s JSON feed to the S3 storage space<\/li>\n\n\n\n
- create a Visual ETL\n
- \n
- Source: select the S3 location of the JSON file you just created with the notebook; pick JSON format
\/!\\ leave Recursive blank<\/li>\n\n\n\n - Transform: Flatten<\/li>\n\n\n\n
- Target: same S3 location; pick PARQUET format<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Hit RUN<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Vizualise your new PARQUET file in S3 container<\/li>\n<\/ol>\n\n\n\n
![\"\"](\"https:\/\/aubertm.me\/wp-content\/uploads\/2024\/10\/image-2.png?w=800\")
<\/figure>\n\n\n\nCode is here<\/a><\/p>\n\n\n
To GCP<\/h2>\n\n\n
Google Cloud Service only accepts TSV files, but then Wikidata refuses the googlebot’s request. So I had to go through BigQuery Studio throught a Python request.<\/p>\n\n\n\n
- \n
- In Cloud Storage<\/a>, create a bucket<\/li>\n\n\n\n
- In BigQuery Studio<\/a>, \n
- \n
- create a Python notebook to transfer the Wikidata’s JSON feed to your bucket as JSON file<\/li>\n\n\n\n
- create a dataset and table from the JSON file, with automatic detection of its scheme<\/li>\n\n\n\n
- export the new table to Google Cloud Storage as a PARQUET file<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Download your files in Google Cloud Storage, and vizualise them locally<\/li>\n<\/ol>\n\n\n\n
![\"\"](\"https:\/\/aubertm.me\/wp-content\/uploads\/2024\/10\/image-4.png?w=800\")
<\/figure>\n\n\n\nCode <\/a>is <\/a>here<\/a><\/p>\n\n\n
My conclusion<\/h2>\n\n\n
Criteria<\/th> MS Azure<\/th> AWS Glue<\/th> GCP<\/th><\/tr><\/thead> Direct connection to the feed<\/td> No: from JSON feed
to JSON file<\/td>Yes: work directly from the JSON feed<\/td> No: from JSON feed to JSON file<\/td><\/tr> Data vizualisation<\/td> Yes: from input feed to output dataset<\/td> Yes: only output dataset<\/td> No: have to download output dataset<\/td><\/tr> Easy to manipulate<\/td> Yes, but details to validate and deploy again and again<\/td> Yes, but still with some specific terms to learn<\/td> Yes, but a bunch of old tools in the way<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n AWS Glue’s visual interface is much easier to understand than the multiple validations and publications needed in MS Azure Data Factory, or the estonishing non-web native manipulation in GCP.<\/p>\n","protected":false},"excerpt":{"rendered":"
In order to compare how technologies handle data, I set up an ETL funnel in each environement, extracting data from Wikipedia and transforming it into a PARQUET file.<\/p>\n","protected":false},"author":2,"featured_media":4815,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[3],"tags":[9],"class_list":["post-4220","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-architecture-governance","tag-en"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/aubertm.me\/wp-content\/uploads\/2024\/10\/image-3.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/posts\/4220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/comments?post=4220"}],"version-history":[{"count":1,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/posts\/4220\/revisions"}],"predecessor-version":[{"id":4821,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/posts\/4220\/revisions\/4821"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/media\/4815"}],"wp:attachment":[{"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/media?parent=4220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/categories?post=4220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aubertm.me\/index.php\/wp-json\/wp\/v2\/tags?post=4220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- In BigQuery Studio<\/a>, \n
- In Cloud Storage<\/a>, create a bucket<\/li>\n\n\n\n
- Source: select the S3 location of the JSON file you just created with the notebook; pick JSON format
- In Amazon S3 storage<\/a>, create a bucket and its endpoint<\/li>\n\n\n\n
- In AWS Glue Studio<\/a>, configure IAM<\/li>\n\n\n\n
- Destination: create a storage account<\/a> then a blob container to store the JSON output dataset.<\/li>\n\n\n\n
- and a header to announce you’re not a bot; for example:\n
- as relative URL as REST in JSON format and the SPARQL query\n
- as base URL https:\/\/query.wikidata.org\/<\/a> <\/li>\n\n\n\n
- Create a new pipeline in Azure Data Factory<\/a> \n
- Second step: enrich the SPARQL query with aggregation<\/a><\/li>