Aloha mai e nā kamaʻāina o Khabrovsk. I ka hoʻomaka ʻana o ka papa Ua hoʻomākaukau mākou i kahi unuhi o nā mea hoihoi.
I nā hihia hoʻohana he nui a mākou e makemake ai E like me kā mākou e ʻike ai ma nā pūnaewele o kā mākou mea kūʻai aku, hūnā ʻia ka ʻike pili i nā pilina ma waena o nā hui, no ka laʻana i ka nānā ʻana i nā pilina ma waena o nā mea hoʻohana, hilinaʻi ma waena o nā mea, a i ʻole nā pilina ma waena o nā mea ʻike. Hoʻohālikelike ʻia ia mau mea hoʻohana ma ka pakuhi. Ma mua o kēia makahiki, ua hoʻokuʻu ʻo Amazon i kāna ʻikepili kiʻi hou, ʻo Neptune. Ma kēia pou makemake mākou e kaʻana like i kā mākou mau manaʻo mua, nā hana maikaʻi a me nā mea e hiki ke hoʻomaikaʻi ʻia i ka manawa.
No ke aha mākou i makemake ai iā Amazon Neptune
Hoʻohiki ʻo Graph databases e mālama i nā pūʻulu ʻikepili pili ʻoi aku ka maikaʻi ma mua o ko lākou mau pilina pili. I loko o ia mau waihona, mālama mau ʻia ka ʻike pili i nā pilina ma waena o nā mea. Ua hoʻohana mākou i kahi papahana ʻikepili wehe kupaianaha e hoʻāʻo iā Neptune . ʻOhi ʻo MusicBrainz i kēlā me kēia ʻano metadata mele e noʻonoʻo ʻia, e like me ka ʻike e pili ana i nā mea pena kiʻi, nā mele, nā hoʻokuʻu album a i ʻole nā ʻaha mele, a me ka mea i hui pū ʻia ma hope o ke mele a i ka wā i hoʻokuʻu ʻia ai ka album ma kēlā ʻāina. Hiki ke ʻike ʻia ʻo MusicBrainz ma ke ʻano he pūnaewele nui o nā hui e pili ana i ka ʻoihana mele.
Hāʻawi ʻia ka waihona MusicBrainz ma ke ʻano he CSV dump o kahi waihona pili. Ma ka huina, aia ma kahi o 93 miliona mau lālani i loko o 157 mau papa. ʻOiai aia kekahi o kēia mau papa ʻikepili maʻamau e like me nā mea pena kiʻi, nā hanana, nā hoʻopaʻa leo, nā hoʻokuʻu a i ʻole nā mele, nā mea ʻē aʻe nā papa loulou — hale kūʻai i nā pilina ma waena o nā mea pena kiʻi a me nā hoʻopaʻa leo, nā mea pena kiʻi ʻē aʻe a i hoʻokuʻu ʻia, etc. I ka hoʻololi ʻana i ka ʻikepili i mau ʻekolu mau RDF, loaʻa iā mākou ma kahi o 500 miliona mau manawa.
Ma muli o ka ʻike a me nā manaʻo o nā hoa hana a mākou e hana nei, hōʻike mākou i kahi hoʻonohonoho e hoʻohana ʻia ai kēia kumu ʻike e loaʻa ai ka ʻike hou. Eia hou, manaʻo mākou e hoʻonui mau ʻia, no ka laʻana ma ka hoʻohui ʻana i nā mea hou a i ʻole ka hoʻonui ʻana i nā lālā o ka hui.
hoʻoponopono
E like me ka mea i manaʻo ʻia, maʻalahi ka hoʻokomo ʻana iā Amazon Neptune. He kiko'ī loa ia . Hiki iā ʻoe ke hoʻomaka i kahi waihona kiʻi ma nā kaomi liʻiliʻi wale nō. Eia naʻe, i ka wā e pili ana i ka hoʻonohonoho kikoʻī, paʻakikī ke loaʻa. No laila, makemake mākou e kuhikuhi i kahi hoʻonohonoho hoʻonohonoho.
Kiʻi kiʻi hoʻonohonoho no nā pūʻulu ʻāpana
Ua ʻōlelo ʻo Amazon e pili ana ʻo Neptune i nā haʻahaʻa haʻahaʻa haʻahaʻa haʻahaʻa haʻahaʻa, ʻo ia ke kumu he 120 kekona ka manawa noi paʻamau. Ua hoʻāʻo naʻe mākou i nā hihia hoʻohana analytical i hiki pinepine mākou i kēia palena. Hiki ke hoʻololi ʻia kēia manawa manawa ma ka hana ʻana i kahi hui hoʻohālikelike hou no Neptune a me ka hoʻonohonoho neptune_query_timeout kapu pili.
Hoʻouka ʻikepili
Ma lalo nei e kūkākūkā mākou i nā kikoʻī pehea mākou i hoʻouka ai i ka ʻikepili MusicBrainz i Neptune.
ʻO nā pilina ma ʻekolu
ʻO ka mea mua, ua hoʻololi mākou i ka ʻikepili MusicBrainz i nā triples RDF. No laila, no kēlā me kēia pākaukau, ua wehewehe mākou i kahi laʻana e wehewehe i ke ʻano o ka hōʻike ʻia ʻana o kēlā me kēia kolamu i ka ʻekolu. Ma kēia laʻana, ua paʻi ʻia kēlā me kēia lālani mai ka papa hana hoʻokani i ʻumikūmālua mau kolu RDF.
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/gid> "${gid}"^^<http://www.w3.org/2001/XMLSchema#string> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/name> "${name}"^^<http://www.w3.org/2001/XMLSchema#string> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/sort-name> "${sort_name}"^^<http://www.w3.org/2001/XMLSchema#string> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/begin-date> "${begin_date_year}-${begin_date_month}-${begin_date_day}"^^xsd:<http://www.w3.org/2001/XMLSchema#date> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/end-date> "${end_date_year}-${end_date_month}-${end_date_day}"^^xsd:<http://www.w3.org/2001/XMLSchema#date> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/type> <http://musicbrainz.foo/artist-type/${type}> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/area> <http://musicbrainz.foo/area/${area}> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/gender> <http://musicbrainz.foo/gender/${gender}> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/comment> "${comment}"^^<http://www.w3.org/2001/XMLSchema#string> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/edits-pending> "${edits_pending}"^^<http://www.w3.org/2001/XMLSchema#int> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/last-updated> "${last_updated}"^^<http://www.w3.org/2001/XMLSchema#dateTime> .
<http://musicbrainz.foo/artist/${id}> <http://musicbrainz.foo/ended> "${ended}"^^<http://www.w3.org/2001/XMLSchema#boolean> .
Hoʻouka nui
ʻO ke ala i manaʻo ʻia e hoʻouka i ka nui o ka ʻikepili i Neptune ma o ke kaʻina hoʻouka nui ma o S3. Ma hope o ka hoʻouka ʻana i kāu mau faila ʻekolu i S3, hoʻomaka ʻoe i ka hoʻouka ʻana me ka noi POST. I kā mākou hihia, lawe ʻia ma kahi o 24 mau hola no 500 miliona triplets. Ua manaʻo mākou e ʻoi aku ka wikiwiki.
curl -X POST -H 'Content-Type: application/json' http://your-neptune-cluster:8182/loader -d '{
"source" : "s3://your-s3-bucket",
"format" : "ntriples",
"iamRoleArn" : "arn:aws:iam::your-iam-user:role/NeptuneLoadFromS3",
"region" : "eu-west-1",
"failOnError" : "FALSE"
}'I mea e pale aku ai i kēia kaʻina hana lōʻihi i kēlā me kēia manawa a mākou e hoʻomaka ai iā Neptune, ua hoʻoholo mākou e hoʻihoʻi i ka hiʻohiʻona mai kahi kiʻi paʻi i hoʻoili ʻia kēia mau triplets. ʻOi aku ka wikiwiki o ka holo ʻana mai kahi kiʻi paʻi, akā e lōʻihi ana ma kahi o hoʻokahi hola a hiki i ka loaʻa ʻana o Neptune no nā noi.
I ka hoʻouka mua ʻana i nā triplets i Neptune, loaʻa iā mākou nā hewa like ʻole.
{
"errorCode" : "PARSING_ERROR",
"errorMessage" : "Content after '.' is not allowed",
"fileName" : [...],
"recordNum" : 25
}ʻO kekahi o lākou he mau kuhi hewa, e like me ka mea i hōʻike ʻia ma luna. I kēia lā, ʻaʻole mākou i ʻike i ka mea i hewa i kēia manawa. ʻO kahi kikoʻī hou aʻe e kōkua maoli ma aneʻi. Ua loaʻa kēia hewa no kahi o 1% o nā ʻekolu i hoʻokomo ʻia. Akā no ka hoʻāʻo ʻana iā Neptune, ua ʻae mākou i ka hana wale ʻana me 99% o ka ʻike mai MusicBrainz.
ʻOiai he mea maʻalahi kēia no ka poʻe kamaʻāina me SPARQL, e makaʻala pono e hoʻokaʻawale ʻia nā ʻekolu RDF me nā ʻano ʻikepili kikoʻī, a hiki ke hoʻopiʻi hou i nā hewa.
Hoʻoiho hoʻoheheʻe
E like me ka mea i ʻōlelo ʻia ma luna nei, ʻaʻole mākou makemake e hoʻohana iā Neptune ma ke ʻano he hale kūʻai ʻikepili static, akā ma ke ʻano he kumu ʻike maʻalahi a ulu. No laila pono mākou e ʻimi i nā ala e hoʻolauna ai i nā ʻekolu hou i ka wā e loli ai ka waihona ʻike, no ka laʻana ke paʻi ʻia kahi album hou a i ʻole ke makemake mākou e hoʻokō i ka ʻike i loaʻa.
Kākoʻo ʻo Neptune i nā mea hoʻokomo ma o nā nīnau SPARQL, ma ka maka a me ka laʻana. E kūkākūkā mākou i nā ala ʻelua ma lalo nei.
ʻO kekahi o kā mākou pahuhopu ʻo ia ka hoʻokomo ʻana i ka ʻikepili ma ke ʻano streaming. E noʻonoʻo e hoʻokuʻu i kahi album ma kahi ʻāina hou. Mai ka manaʻo o MusicBrainz, ʻo ia hoʻi, no ka hoʻokuʻu ʻana e loaʻa nā albums, singles, EPs, etc., ua hoʻohui ʻia kahi mea hou i ka papaʻaina. ʻāina hoʻokuʻu. Ma RDF, hoʻohālikelike mākou i kēia ʻike me ʻelua mau ʻekolu hou.
INSERT DATA { <http://musicbrainz.foo/release-country/737041> <http://musicbrainz.foo/release> <http://musicbrainz.foo/release/435759> };INSERT DATA { <http://musicbrainz.foo/release-country/737041> <http://musicbrainz.foo/date-year> "2018"^^<http://www.w3.org/2001/XMLSchema#int> };ʻO kekahi pahuhopu ka loaʻa ʻana o ka ʻike hou mai ka pakuhi. E ʻōlelo mākou makemake mākou e loaʻa ka helu o nā hoʻokuʻu i paʻi ʻia e kēlā me kēia mea pena i kā lākou ʻoihana. Paʻakikī loa kēlā nīnau a ʻoi aku ma mua o 20 mau minuke ma Neptune, no laila pono mākou e hoʻokō i ka hopena i mea e hoʻohana hou ai i kēia ʻike hou i kekahi nīnau ʻē aʻe. No laila, hoʻohui mākou i nā ʻekolu me kēia ʻike i ka pakuhi, e komo ana i ka hopena o ka subquery.
INSERT {
?artist_credit <http://musicbrainz.foo/number-of-releases> ?number_of_releases
} WHERE {
SELECT ?artist_credit (COUNT(*) as ?number_of_releases)
WHERE {
?artist_credit <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist-credit> .
?release_group <http://musicbrainz.foo/artist-credit> ?artist_credit .
?release_group <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/release-group> .
?release_group <http://musicbrainz.foo/name> ?release_group_name .
}
GROUP BY ?artist_credit
}ʻO ka hoʻohui ʻana i nā triple hoʻokahi i ka pakuhi he mau milliseconds, ʻoiai ʻo ka manawa hoʻokō no ka hoʻokomo ʻana i ka hopena o kahi subquery e pili ana i ka manawa hoʻokō o ka subquery ponoʻī.
ʻOiai ʻaʻole mākou i hoʻohana pinepine iā ia, ʻae ʻo Neptune iā ʻoe e wehe i nā triplets e pili ana i nā laʻana a i ʻole ka ʻikepili kikoʻī, hiki ke hoʻohana ʻia e hoʻonui i ka ʻike.
Nā nīnau SPARQL
Ma ka hoʻokomo ʻana i ka subsample mua, e hoʻihoʻi i ka helu o nā hoʻokuʻu no kēlā me kēia mea pena, ua hoʻolauna mua mākou i ke ʻano nīnau mua a mākou e makemake ai e pane me ka hoʻohana ʻana iā Neptune. He maʻalahi ke kūkulu ʻana i kahi nīnau ma Neptune - e hoʻouna i kahi noi POST i ka SPARQL endpoint, e like me ka hōʻike ʻana ma lalo nei:
curl -X POST --data-binary 'query=SELECT ?artist ?p ?o where {?artist <http://musicbrainz.foo/name> "Elton John" . ?artist ?p ?o . }' http://your-neptune-cluster:8182/sparqlEia hou, ua hoʻokō mākou i kahi nīnau e hoʻihoʻi ana i kahi ʻaoʻao kiʻi kiʻi i loaʻa ka ʻike e pili ana i ko lākou inoa, makahiki, a i ʻole ʻāina hānau. E hoʻomanaʻo, ʻo nā mea hoʻokani paha he mau kanaka, hui, a i ʻole nā orchestras. Eia hou, hoʻohui mākou i kēia ʻikepili me ka ʻike e pili ana i ka helu o nā hoʻokuʻu i hoʻokuʻu ʻia e nā mea pena i ka makahiki. No nā mea pena kiʻi solo, hoʻohui pū mākou i ka ʻike e pili ana i nā hui a ka mea pena i komo ai i kēlā me kēia makahiki.
SELECT
?artist_name ?year
?releases_in_year ?releases_up_year
?artist_type_name ?releases
?artist_gender ?artist_country_name
?artist_begin_date ?bands
?bands_in_year
WHERE {
# Bands for each artist
{
SELECT
?year
?first_artist
(group_concat(DISTINCT ?second_artist_name;separator=",") as ?bands)
(COUNT(DISTINCT ?second_artist_name) AS ?bands_in_year)
WHERE {
VALUES ?year {
1960 1961 1962 1963 1964 1965 1966 1967 1968 1969
1970 1971 1972 1973 1974 1975 1976 1977 1978 1979
1980 1981 1982 1983 1984 1985 1986 1987 1988 1989
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
2010 2011 2012 2013 2014 2015 2016 2017 2018
}
?first_artist <http://musicbrainz.foo/name> "Elton John" .
?first_artist <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist> .
?first_artist <http://musicbrainz.foo/type> ?first_artist_type .
?first_artist <http://musicbrainz.foo/name> ?first_artist_name .
?second_artist <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist> .
?second_artist <http://musicbrainz.foo/type> ?second_artist_type .
?second_artist <http://musicbrainz.foo/name> ?second_artist_name .
optional { ?second_artist <http://musicbrainz.foo/begin-date-year> ?second_artist_begin_date_year . }
optional { ?second_artist <http://musicbrainz.foo/end-date-year> ?second_artist_end_date_year . }
?l_artist_artist <http://musicbrainz.foo/entity0> ?first_artist .
?l_artist_artist <http://musicbrainz.foo/entity1> ?second_artist .
?l_artist_artist <http://musicbrainz.foo/link> ?link .
optional { ?link <http://musicbrainz.foo/begin-date-year> ?link_begin_date_year . }
optional { ?link <http://musicbrainz.foo/end-date-year> ?link_end_date_year . }
FILTER (!bound(?link_begin_date_year) || ?link_begin_date_year <= ?year)
FILTER (!bound(?link_end_date_year) || ?link_end_date_year >= ?year)
FILTER (!bound(?second_artist_begin_date_year) || ?second_artist_begin_date_year <= ?year)
FILTER (!bound(?second_artist_end_date_year) || ?second_artist_end_date_year >= ?year)
FILTER (?first_artist_type NOT IN (<http://musicbrainz.foo/artist-type/2>, <http://musicbrainz.foo/artist-type/5>, <http://musicbrainz.foo/artist-type/6>))
FILTER (?second_artist_type IN (<http://musicbrainz.foo/artist-type/2>, <http://musicbrainz.foo/artist-type/5>, <http://musicbrainz.foo/artist-type/6>))
}
GROUP BY ?first_artist ?year
}
# Releases up to a year
{
SELECT
?artist
?year
(group_concat(DISTINCT ?release_name;separator=",") as ?releases)
(COUNT(*) as ?releases_up_year)
WHERE {
VALUES ?year {
1960 1961 1962 1963 1964 1965 1966 1967 1968 1969
1970 1971 1972 1973 1974 1975 1976 1977 1978 1979
1980 1981 1982 1983 1984 1985 1986 1987 1988 1989
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
2010 2011 2012 2013 2014 2015 2016 2017 2018
}
?artist <http://musicbrainz.foo/name> "Elton John" .
?artist_credit_name <http://musicbrainz.foo/artist-credit> ?artist_credit .
?artist_credit_name <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist-credit-name> .
?artist_credit_name <http://musicbrainz.foo/artist> ?artist .
?artist_credit <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist-credit> .
?release_group <http://musicbrainz.foo/artist-credit> ?artist_credit .
?release_group <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/release-group> .
?release_group <http://musicbrainz.foo/name> ?release_group_name .
?release <http://musicbrainz.foo/release-group> ?release_group .
?release <http://musicbrainz.foo/name> ?release_name .
?release_country <http://musicbrainz.foo/release> ?release .
?release_country <http://musicbrainz.foo/date-year> ?release_country_year .
FILTER (?release_country_year <= ?year)
}
GROUP BY ?artist ?year
}
# Releases in a year
{
SELECT ?artist ?year (COUNT(*) as ?releases_in_year)
WHERE {
VALUES ?year {
1960 1961 1962 1963 1964 1965 1966 1967 1968 1969
1970 1971 1972 1973 1974 1975 1976 1977 1978 1979
1980 1981 1982 1983 1984 1985 1986 1987 1988 1989
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
2010 2011 2012 2013 2014 2015 2016 2017 2018
}
?artist <http://musicbrainz.foo/name> "Elton John" .
?artist_credit_name <http://musicbrainz.foo/artist-credit> ?artist_credit .
?artist_credit_name <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist-credit-name> .
?artist_credit_name <http://musicbrainz.foo/artist> ?artist .
?artist_credit <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/artist-credit> .
?release_group <http://musicbrainz.foo/artist-credit> ?artist_credit .
?release_group <http://musicbrainz.foo/rdftype> <http://musicbrainz.foo/release-group> .
?release_group <http://musicbrainz.foo/name> ?release_group_name .
?release <http://musicbrainz.foo/release-group> ?release_group .
?release_country <http://musicbrainz.foo/release> ?release .
?release_country <http://musicbrainz.foo/date-year> ?release_country_year .
FILTER (?release_country_year = ?year)
}
GROUP BY ?artist ?year
}
# Master data
{
SELECT DISTINCT ?artist ?artist_name ?artist_gender ?artist_begin_date ?artist_country_name
WHERE {
?artist <http://musicbrainz.foo/name> ?artist_name .
?artist <http://musicbrainz.foo/name> "Elton John" .
?artist <http://musicbrainz.foo/gender> ?artist_gender_id .
?artist_gender_id <http://musicbrainz.foo/name> ?artist_gender .
?artist <http://musicbrainz.foo/area> ?birth_area .
?artist <http://musicbrainz.foo/begin-date-year> ?artist_begin_date.
?birth_area <http://musicbrainz.foo/name> ?artist_country_name .
FILTER(datatype(?artist_begin_date) = xsd:int)
}Ma muli o ka paʻakikī o ia nīnau, hiki iā mākou ke hana i nā nīnau kikoʻī no kahi mea pena kikoʻī, e like me Elton John, akā ʻaʻole no nā mea pena a pau. ʻAʻole ʻike ʻia ʻo Neptune i kahi nīnau e like me ka waiho ʻana i nā kānana i nā subselects. No laila, pono e kānana lima ʻia kēlā me kēia koho e ka inoa mea pena.
Loaʻa iā Neptune nā uku i kēlā me kēia hola a me ka-I/O. No kā mākou hoʻāʻo ʻana, ua hoʻohana mākou i ka mea liʻiliʻi loa ʻo Neptune, ʻo ia ke kumukūʻai $0,384/hola. Ma ka hihia o ka nīnau ma luna, e helu ana i ka ʻaoʻao no ka mea hana hoʻokahi, hoʻopiʻi ʻo Amazon iā mākou i ʻumi kaukani o nā hana I/O, e hōʻike ana i ke kumukūʻai o $0.02.
hopena
ʻO ka mea mua, mālama ʻo Amazon Neptune i ka hapa nui o kāna mau ʻōlelo hoʻohiki. Ma ke ʻano he lawelawe i hoʻokele ʻia, ʻo ia kahi ʻikepili kiʻi i maʻalahi loa e hoʻokomo a hiki ke kū a holo me ka nui o ka hoʻonohonoho ʻana. Eia kā mākou ʻike nui ʻelima:
- He maʻalahi ka hoʻouka nui akā lohi. Akā hiki ke paʻakikī me nā memo hewa ʻaʻole kōkua nui.
- Kākoʻo ka hoʻoiho ʻana i nā mea āpau a mākou i manaʻo ai a wikiwiki loa
- He maʻalahi nā nīnau, akā ʻaʻole lawa ka launa pū ʻana e holo i nā nīnau noiʻi
- Pono e hoʻoponopono lima ʻia nā nīnau SPARQL
- Paʻakikī ke koho ʻana i nā uku Amazon no ka mea paʻakikī ke koho i ka nui o ka ʻikepili i nānā ʻia e kahi nīnau SPARQL.
ʻo ia wale nō. E kakau inoa no .
Source: www.habr.com