NoSQL Elasticsearch

1. Index design

Elasticsearch is a document-oriented database that uses an inverted index. An inverted index is an annotated data structure that stores a mapping from content, such as words, numbers, or phrases, to its locations in a document or a collection. Inverted indexing stores a list of documents per token and retrieve all documents containing the query token in a fast time. There are two common indexing strategies: BSBI, which is faster and adds terms directly to the postings list, and SPIMI, which is slower and collects term-document ID pairs, sorts them, and then creates postings list. Elasticsearch is also a non-relational database, which is unlike the SQL database. It stores data without functional dependencies or any unique constraints such as primary key and foreign key, but each document will have an _id that identifies the document. The _id will be generated automatically for each document by Elasticsearch

In Elasticsearch, an index is similar to a database in a relational database where we should load the data and a type is like a table in a relational database, which contains fields (column in a relational database) and documents (row in a relational database). According to the question, we could create a ‘companies’ index and the fields are divided into several parts: ‘company’, ‘city’, ‘state’, ‘country’, ‘description’, ’employees’, ‘founded’, ‘annual revenue’, etc. The fields are key-value pairs, where the value can be any basic data type (e.g., integer, string) or a nested data structure (e.g., array, dictionary, object, etc.). The ‘company’, ‘city’, ‘state’, ‘country, and ‘description’ fields are strings, the ’employee’, ‘founded’, and ‘annual revenue’ fields are numbers. The documents contain eight company JSON objects. Mapping is like a ‘Schema’ in a relational database, which defines each type within the index. Each document is stored in a primary shard and by default, an index has 5 primary shards. Each primary shard can have zero or more replica shards, which are copies of the primary shard. The mapping of the index we created could be generated automatically when our loaded documents are indexed.

3. Elasticsearch Queries

1. Which company is the oldest?

We search the companies index and in order to find the oldest company, we use the ‘founded’ field to find the minimum value. We use the aggregation command ‘.aggs.metric’ and ‘min’ to find the minimum value in the ‘founded’ field, and then we use a query with ‘match’ to find the company which has the minimum founded year. Finally, the code prints only the company names and the founded year meeting this criteria

from elasticsearch_dsl import Search, Q

s = Search(using = client, index = "companies")
s.aggs.metric("earliest_found", "min", field = 'founded')
response = s.execute()
s = Search(using = client, index = "companies").query("match", founded = response.aggregations.earliest_found.value)

response = s.execute() # by default you get 10 hits
for hit in response:
    print("The oldest company is: " + str(hit.company) + ". The founded year for this company is: " + str(hit.founded))

2. Which company’s product is a search engine?

We search the companies index and use the query method ‘match’ to find companies whose description field contains the word “search engine”. The code will return all results and only print the company names meeting this criteria

from elasticsearch_dsl import Search

s = Search(using = client, index = "companies").query("match", description = "search engine")  
response = s.execute() # by default you get 10 hits

for hit in response:
    print("The company whose products contain a search engine is:", hit.company)

We find the description field contains the word “search engine” and the company meeting this criteria is Google.

from elasticsearch_dsl import Search

s = Search(using = client, index = "companies").query("match", country = "USA")  
response = s.execute() # by default you get 10 hits

for hit in response:
    print(hit.company, "is headquatered in the USA")

TIME_FROM = 1990
TIME_TO   = 1999
s = Search(using = client, index = "companies") \
        .filter("range", founded ={'gte': TIME_FROM, 'lte': TIME_TO})
#By default we get 10 records as a response
response = s.execute()
for hit in response:
    print("The compnay's name is: " + str(hit.company) + ". The founded year is: "+ str(hit.founded))

from elasticsearch_dsl import Search, Q

total_revenue = 0
s = Search(using=client, index="companies")
s.query = Q("match", country = 'China') | Q("match", country = 'Japan') | Q("match", country = 'South Korea')
s.aggs.metric("total_revenue", "sum", field = "annual_revenue")
response = s.execute() # by default you get 10 hits

total_revenue = response.aggregations.total_revenue.value
print("The total annual revenue of the three companies in Asia is", total_revenue, "billion dollars")

The total annual revenue of the three companies in Asia is 358.0 billion dollars

client.indices.delete(index='companies', ignore=[400, 404])
print("Companies index is dropped!")

Companies index is dropped!