Salute, Khabrovites. Before the start of the course prepared a translation of interesting material.
In many of the use cases we like , we see on the websites of our clients, relevant information is hidden in relationships between entities, for example, when analyzing relationships between users, dependencies between elements or connections between sensors. Such use cases are usually modeled on a graph. Earlier this year, Amazon released a new Neptune graph database. In this post, we want to share our first ideas, good practices, and what can be improved over time.
Why we need Amazon Neptune
Graph databases promise to handle highly connected datasets better than their relational counterparts. In such data sets, relevant information is usually stored in relationships between objects. We used an amazing open data project to test Neptune . MusicBrainz collects every conceivable metadata about music, such as information about artists, songs, album releases or concerts, who the artist who created the song collaborated with, or when the album was released in which country. MusicBrainz can be seen as a huge network of entities that are somehow connected to the music industry.
The MusicBrainz dataset is provided as a relational database CSV dump. In total, the dump contains about 93 million rows in 157 tables. While some of these tables contain basic data such as artists, events, recordings, releases, or tracks, others are link tables - store relationships between artists and records, other artists or releases, etc... They demonstrate the graph structure of a data set. When converting the dataset to RDF triples, we got approximately 500 million instances.
Based on the experience and impressions of the project partners with whom we work, we present a setting in which this knowledge base is used to obtain new information. In addition, we expect it to be updated regularly, for example by adding new releases or updating group members.
Setting
As expected, installing Amazon Neptune is easy. She is quite detailed. . You can launch a graph database with just a few clicks. However, when it comes to more detailed configuration, hard to find. Hence, we want to point to one configuration parameter.
Configuration screenshot for parameter groups
Amazon claims that Neptune focuses on low-latency transactional workloads, which is why the default request timeout is 120 seconds. We have, however, tested many analytics use cases in which we regularly hit this limit. This timeout can be changed by creating a new parameter group for Neptune and setting neptune_query_timeout the corresponding restriction.
Loading data
Below we will discuss in detail how we uploaded MusicBrainz data to Neptune.
Relationships in threes
First, we converted the MusicBrainz data into RDF triplets. Therefore, for each table, we have defined a template that determines how each column is represented in a triple. In this example, each row in the artist table is mapped to twelve RDF triples.
<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> .
bulk upload
The suggested way to upload large amounts of data to Neptune is through the bulk upload process via S3. After uploading your triple files to S3, you start uploading with a POST request. In our case, it took about 24 hours for 500 million triples. We expected it to be faster.
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"
}'To avoid this lengthy process every time we start Neptune, we decided to restore an instance from a snapshot that already had these triples loaded. Starting from a snapshot is significantly faster, but still takes about an hour until Neptune is available for requests.
When initially loading triplets into Neptune, we ran into various bugs.
{
"errorCode" : "PARSING_ERROR",
"errorMessage" : "Content after '.' is not allowed",
"fileName" : [...],
"recordNum" : 25
}Some of them were parsing errors, as shown above. To date, we still haven't figured out exactly what went wrong at this point. A little more detail would definitely help here. This error occurred for about 1% of the inserted triplets. But when it comes to testing Neptune, we have accepted the fact that we only work with 99% of the information from MusicBrainz.
Even if it's easy for people familiar with SPARQL, keep in mind that RDF triples must be annotated with explicit data types, which again can cause errors.
streaming download
As mentioned above, we don't want to use Neptune as a static data store, but rather as a flexible and evolving knowledge base. So we needed to find ways to introduce new triples when the knowledge base changes, such as when a new album is published or when we want to materialize derived knowledge.
Neptune supports input statements through SPARQL queries with both raw data and selections. We will discuss both approaches below.
One of our goals was to enter data in streaming mode. Consider releasing an album in a new country. From MusicBrainz's point of view, this means that for a release that includes albums, singles, EPs, etc., a new entry is added to the table release-country. In RDF, we map this information to two new triples.
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> };Another goal was to gain new knowledge from the graph. Let's say we want to get the number of releases each artist has published in their career. Such a query is quite complex and takes more than 20 minutes in Neptune, so we need to materialize the result in order to reuse this new knowledge in some other query. So we add triplets with this information back to the graph by injecting the result of the 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
}Adding single triples to the graph takes a few milliseconds, while the execution time for inserting the result of a subquery depends on the execution time of the subquery itself.
While we haven't used this much, Neptune also allows you to remove triplets based on selections or explicit data that can be used to update information.
SPARQL queries
Introducing the previous subsample that returns the number of releases for each artist, we've already entered the first type of query we want to answer using Neptune. Building a query in Neptune is easy - send a POST request to the SPARQL endpoint, as shown below:
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/sparqlIn addition, we implemented a query that returns a profile of performers containing information about their name, age, or country of origin. Keep in mind that the performers can be individuals, groups, or orchestras. In addition, we complement this data with information on the number of releases released by artists during the year. For solo artists, we also add information about the groups in which these artists participated in each year.
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)
}Due to the complexity of such a query, we could only perform point queries for a specific artist, such as Elton John, but not for all artists. Neptune doesn't seem to optimize such a query by dropping filters into subselects. Therefore, each sample has to be filtered manually by artist name.
Neptune has both hourly and per I/O charges. For our testing, we used the most minimal Neptune instance, which costs $0,384/hour. In the case of the query above, which calculates a profile for a single performer, Amazon charges us several tens of thousands of IOPS, which implies a cost of $0.02.
Hack and predictor Aviator
First, Amazon Neptune keeps most of its promises. Being a managed service, it is a graph database that is extremely easy to install and can be started without a lot of configuration. Here are our five key takeaways:
- Bulk upload is simple but slow. But it can get complicated with error messages that aren't very helpful.
- Streaming supports everything we expected and was fast enough
- Queries are simple but not interactive enough to run analytic queries
- SPARQL queries must be manually optimized
- Amazon payments are difficult to estimate because it is difficult to estimate the amount of data scanned by a SPARQL query.
That's all. Sign up for .
Source: habr.com