DB

Tuesday, March 17, 2015

Query Expansion with Wordnet

Introduction

The Wordnet synonym database can be churned into a Lucene Index. This allows for rapid synonym lookup. User query terms can be expanded using these synonym sets as a method of boosting recall. Query expansion is a known technique for improving retrieval performance in Information Extraction systems.

The online Wordnet search:

This article covers the building and querying of a Lucene search index of Wordnet synonyms for the purpose of enabling computationally-efficient and thread-safe synonym lookups at runtime.

Building the Index

To build the index, you can either call Syns2Index directly from the command line or in the context of another Java class.

I prefer to use a simple wrapper:

1
2
3

public static void main(String... args) throws Throwable {
 Syns2Index.main(new String[] { "wn_s.pl", Constants.WORDNET_SYNONYMS_DIR.getAbsolutePath() });
}

The referenced "pl" file is the Wordnet Prolog Distribution (see references below).

Querying the Index

To query

package com.mycompany.wordnet.synonyms.svc;

public class QueryIndex {

 public static LogManager logger = new LogManager(QueryIndex.class);

 private FSDirectory  directory;

 private IndexSearcher  searcher;

 public QueryIndex() throws BusinessException {
  init();
 }

 public void close() throws BusinessException {
  try {

   if (null != searcher) searcher.close();
   if (null != directory) directory.close();

   this.searcher = null;
   this.directory = null;

  } catch (IOException e) {
   logger.error(e);
   throw new BusinessException("Unable to shutdown Lucene Index");
  }
 }

 private void init() throws BusinessException {
  try {

   directory = FSDirectory.open(Constants.WORDNET_SYNONYMS_DIR);
   searcher = new IndexSearcher(directory);

  } catch (Exception e) {
   logger.error(e);
   throw new BusinessException("Unable to open Lucene Directory (path = %s)", Constants.WORDNET_SYNONYMS_DIR.getAbsolutePath());
  }
 }

 public Collection<String> process(String term) throws BusinessException {
  try {

   if (null == directory || null == searcher) init();
   Query query = new TermQuery(new Term(Syns2Index.F_WORD, term));

   TotalHitCountCollector thcc = new TotalHitCountCollector();
   searcher.search(query, thcc);

   Set<String> results = new TreeSet<String>();
   ScoreDoc[] hits = searcher.search(query, 10).scoreDocs;

   for (ScoreDoc hit : hits) {
    Document doc = searcher.doc(hit.doc);

    String[] values = doc.getValues(Syns2Index.F_SYN);
    results.addAll(SetUtils.toSet(values));
   }

   if (0 == thcc.getTotalHits()) logger.debug("No Results Found (term = %s)", term);
   else logger.info("Synonyms Found (term = %s, total = %s, list = %s)", term, results.size(), SetUtils.toString(results, ", "));

   return results;

  } catch (IOException e) {
   logger.error(e);
   throw new BusinessException("Unable to Execute Query (term = %s)", term);
  }
 }
}

Note: Imports have been removed for increased readibility.

LUKE

LUKE is a handy development and diagnostic tool.

LUKE can be used to access pre-existing Lucene indexes and for the purpose of displaying and modify content.

I've connected LUKE to the Wordnet index, and can perform graphical queries:

Usage and Test Case

@Test
public void run() throws Throwable {

 QueryIndex queryIndex = new QueryIndex();
 assertNotNull(queryIndex);

 Collection<String> list = queryIndex.process("automobile");
 assertNotNull(list);
 assertFalse(list.isEmpty());
 assertEquals("auto, car, machine, motorcar", SetUtils.toString(list, ", "));

 queryIndex.close();
}

We note that the test case output is identical the screenshot captured from the online synonym lookup on the official Wordnet site.

Build Environment

I use Maven, and my POM looks something like this:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>com.mycompany.wordnet.synonyms</groupId>
 <artifactId>wordnet-synonyms</artifactId>
 <version>1.0.0</version>
 <packaging>jar</packaging>

 <url />
 <name>Wordnet Synonyms</name>
 <inceptionYear>2015</inceptionYear>
 <description>Wordnet Synonym Lookup via Lucene Index</description>

 <build>
  <plugins>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>3.2</version>
    <configuration>
     <source>1.7</source>
     <target>1.7</target>
    </configuration>
   </plugin>
  </plugins>
 </build>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.apache.lucene</groupId>
   <artifactId>lucene-wordnet</artifactId>
   <version>3.3.0</version>
  </dependency>
 </dependencies>

</project>

References

[MvnRepository] Lucene Wordnet 3.3.0

Also contains the JAR file if you do not plan to use Maven.
This dependency was last updated Jun 26, 2011.

[Princeton.edu] Wordnet Main Site

Prolog Distribution (deep link)

Unzip the tar file and extract the "wn_s.pl" prolog file, and make it available to the QueryBuilder.

Online Synonym Search

Used to derive the screenshot at the beginning of this article.

Query Extraction:

Wikipedia Entry, Stanford NLP

Use of a thesaurus can be combined with ideas of term weighting: for instance, one might weight added terms less than original query terms.

[Google Code] LUKE (Lucene Analyzer)

Monday, March 16, 2015

Querying the Lucene Index

Note: Imports removed for legibility.

package com.ibm.ted.ebear.lucene;

public final class QueryDemo1 {

 public static LogManager logger = new LogManager(QueryDemo1.class);

 private static BooleanQuery createBooleanQuery(Analyzer analyzer) throws ParseException {
  BooleanQuery booleanQuery = new BooleanQuery();

  Query query1 = new QueryParser("content", analyzer).parse(String.format("(%s) AND (%s)", "alpha", "beta"));
  query1.setBoost(0.9f);
  booleanQuery.add(query1, Occur.SHOULD);

  Query query2 = new QueryParser("title", analyzer).parse(String.format("(%s) AND (%s)", "alpha", "beta"));
  query2.setBoost(1.1f);
  booleanQuery.add(query2, Occur.SHOULD);

  return booleanQuery;
 }

 public static void main(String... args) throws Throwable {

  Directory directory = FSDirectory.open(new File("/home/astoruser/lucene/01/"));
  IndexReader reader = DirectoryReader.open(directory);

  Analyzer analyzer = new StandardAnalyzer();
  IndexSearcher searcher = new IndexSearcher(reader);

  TopScoreDocCollector collector = TopScoreDocCollector.create(10, true);
  BooleanQuery booleanQuery = createBooleanQuery(analyzer);
  searcher.search(booleanQuery, collector);

  ScoreDoc[] hits = collector.topDocs().scoreDocs;
  for (ScoreDoc hit : hits) {

   Document doc = searcher.doc(hit.doc);
   logger.debug("Result (score = %s, filename = %s)", hit.score, doc.get("filename"));
  }
 }
}

Building the Lucene Index

Introduction

Source documents can either be loaded into Lucene on a nearly as-is basis, or be passed through a custom parser. Such a parser may be responsible for various forms of pre-processing on the source document and/or removing information that should not be placed within the index.

The output of the parser is a collection of Lucene documents, that are then loaded into the Lucene Index, and made available to be queried.

Fig 1: Building the Lucene Index

At a minimum, a Lucene developer will need to create a component that returns one or more Lucene Document given one or more incoming source documents.

The component may leverage existing technology (such as Apache Tika) for transforming incoming document types (PDF, DOC, etc) into plain text representations. Or, the component may require custom logic for extracting information.

The developer will also need a strategy for reducing the incoming document in a set of Key/Value pairs. This could be as simple as treating the entire document as a single unit (e.g. key=all, value=<everything>). It's more likely that multiple Key/Value pairs will be used, including special treatment of document metadata, as a method for enhancing search and the display of search results.

Logical Architecture

The Lucene Document
Fields

String Fields
Text Fields
Custom Fields

The Lucene API is easy to use and easy to understand. Creating a basic search index up and running is not difficult. Complexity is found in loading and tuning the index and manipulation of the underlying content -- this is both a science and an art form.

Hierarchy:

A Lucene Index contains multiple documents.
A Lucene Document contains multiple fields.
Each field contains a Key/Value pair.

Fig 2: The Index Hierarchy

Hence, the API is a simple, structured hierarchy of Key/Value pairs extracted from incoming source documents.

For the sake of this tutorial, we'll assume that each source document will have a corresponding Lucene doucment. There might be times when this isn't true. A document could have a single Key/Value pair. This generally happens when structured data is being loaded into a Lucene index. The contents of a customer table could be represented using a single document for each customer name.

We'll make the assumption we're dealing with unstructured text, and that for each source document we will create a Lucene document that contains multiple fields, and each field contains a single Key/Value pair. We'll have to carefully choose the proper type of field to represent our Key/Value entry, depending on how we plan to use that data in our search queries.

Assuming the code to create the Lucene documents already exists, the latter half of the process depicted in Fig 1 looks like this:

Collection<Document> docs = ...
LuceneIndexer indexer = new LuceneIndexer("/home/user/lucene/");
indexer.add(docs);
indexer.close();

The only question that needs to be answered at this point is how to create Lucene Document instances from unstructured source data.

The Lucene Document

In Lucene, a Document is the unit of search and index. An index consists of one or more Documents. Indexing involves adding Documents to an IndexWriter, and searching involves retrieving Documents from an index via an IndexSearcher.

A Lucene Document doesn't necessarily have a 1..1 corespondence with an incoming text document, nor does it even imply the need to be something similar. If Lucene is being used to index structured text (e.g. a database table of users), then each user would be represented in the index as a Lucene Document.

A Document consists of one or more Fields. A Field is simply a name-value pair. For example, a Field commonly found in applications is title. In the case of a title Field, the field name is title and the value is the title of that content item. Indexing in Lucene thus involves creating Documents comprising of one or more Fields, and adding these Documents to an IndexWriter.

Given a hypothetical source document, these are some Key/Value pairs I would be interested in:

String id = getId(inputDocument.getName());
String title = getTitle(inputDocument.getName());
String page = String.valueOf(inputDocument.getPage());
String uri = getUri(inputDocument.getName());
String filename = inputDocument.getName();
String content = getContent(inputDocument);

The implementation of these methods is not important. This is a hypothetical source document, and therefore this data is hypothetical. There is no implication here that every incoming document will have any or all of these content items (id, title, page, url, etc).

Lucene Fields

Each of these Key/Value pairs will be contained within a Lucene Field. The type of Lucene Field I choose is important, and will impact how the data can be found during the execution of a Search Query.

This is a numeric identifier that can be used to uniquely identify the document.
It may be useful for correlating the incoming document to documents in other data sources.

Title

The title of the source document
This will be useful for displaying in the final search results back to the user

Page

Given a multi-page document, it is often useful to make each page a separate Lucene Document. In this case, we'll want to record the exact page number for traceability in the search results.

Given some uniform resource identifer, either a network path or URL.
This is particularly useful in the final search results if you want to give the user a way to examine the underlying search results.
This can also be useful for restricting a query to search or exclude a certain domain.

Filename

The underlying filename of the incoming document.
Useful in the search results, but may not be applicable in the case of a web page.

Content

The actual text for the document.
This could be very large, and that's fine.

To get a sense of other fields that could be added to a Lucene Document, I recommend looking at various Ontologies that have been created and used in the industry in the last few years. Dublin Core is a small set of standard vocabulary terms that can be used to describe web resources. The W3C PROV Ontology is an interoptable vocabulary for defining the influence on digital entities by agents, activities or other entities.

Proper use of known taxonomies and ontologies can provide a standards-based way of extending your Lucene search index and encourage lateral thinking in terms of what data is extracted from the incoming source documents.

Field Types

There are two basic Field types:

String Fields

Use for atomic values that should not be tokenized into a set of words for indexing.
Id, URI and Page are all examples of content items that should be placed within a String Field type.

Text Fields

Used for fields that should be tokenized into a set of words.

I'm going to implement the above fields like this:

doc.add(new VectorTextField("line", line, Field.Store.YES));
doc.add(new VectorTextField("speaker", speaker, Field.Store.YES));
doc.add(new VectorTextField("title", title, Field.Store.YES));
doc.add(new VectorTextField("filename", filename, Field.Store.YES));
doc.add(new StringField("id", id, Field.Store.YES));
doc.add(new StringField("uri", url, Field.Store.YES));
doc.add(new StringField("page", page, Field.Store.YES));

The VectorTextField is a custom type that logically extends the Text Field functionality. This field type stores additional information and permits retrieval of row vector information during the query result stage.

The attribution is given inline within the source code below:

package com.yourpackage;

import java.io.Reader;

import org.apache.lucene.analysis.TokenStream;
import org.apache.lucene.document.Field;
import org.apache.lucene.document.FieldType;

/* http://stackoverflow.com/questions/11945728/how-to-use-termvector-lucene-4-0 */
public class VectorTextField extends Field {

 /* Indexed, tokenized, not stored. */
 public static final FieldType TYPE_NOT_STORED = new FieldType();

 /* Indexed, tokenized, stored. */
 public static final FieldType TYPE_STORED  = new FieldType();

 static {
  TYPE_NOT_STORED.setIndexed(true);
  TYPE_NOT_STORED.setTokenized(true);
  TYPE_NOT_STORED.setStoreTermVectors(true);
  TYPE_NOT_STORED.setStoreTermVectorPositions(true);
  TYPE_NOT_STORED.freeze();

  TYPE_STORED.setIndexed(true);
  TYPE_STORED.setTokenized(true);
  TYPE_STORED.setStored(true);
  TYPE_STORED.setStoreTermVectors(true);
  TYPE_STORED.setStoreTermVectorPositions(true);
  TYPE_STORED.freeze();
 }

 /** Creates a new TextField with Reader value. */
 public VectorTextField(String name, Reader reader, Store store) {
  super(name, reader, store == Store.YES ? TYPE_STORED : TYPE_NOT_STORED);
 }

 /** Creates a new TextField with String value. */
 public VectorTextField(String name, String value, Store store) {
  super(name, value, store == Store.YES ? TYPE_STORED : TYPE_NOT_STORED);
 }

 /** Creates a new un-stored TextField with TokenStream value. */
 public VectorTextField(String name, TokenStream stream) {
  super(name, stream, TYPE_NOT_STORED);
 }
}

Environment Setup

I use Maven, and prefer to create a single POM for Lucene, then reference this POM in other projects.

Here's the Apache Lucene POM I've created:

<project 
 xmlns="http://maven.apache.org/POM/4.0.0" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>lucene-dependencies</groupId>
 <artifactId>lucene-dependencies</artifactId>
 <version>4.10.1</version>
 <packaging>pom</packaging>

 <properties>
  <lucene-core.version>4.10.1</lucene-core.version>
  <lucene-analyzers.version>4.10.1</lucene-analyzers.version>
  <lucene-queryparser.version>4.10.1</lucene-queryparser.version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.apache.lucene</groupId>
   <artifactId>lucene-core</artifactId>
   <version>${lucene-core.version}</version>
  </dependency>
  <dependency>
   <groupId>org.apache.lucene</groupId>
   <artifactId>lucene-analyzers-common</artifactId>
   <version>${lucene-analyzers.version}</version>
  </dependency>
  <dependency>
   <groupId>org.apache.lucene</groupId>
   <artifactId>lucene-queryparser</artifactId>
   <version>${lucene-queryparser.version}</version>
  </dependency>
 </dependencies>
 
</project>

Then in other projects, I simply reference this as:

<dependency>
 <groupId>lucene-dependencies</groupId>
 <artifactId>lucene-dependencies</artifactId>
 <version>4.10.1</version>
 <type>pom</type>
</dependency>

References

McCandless, Michael, et al. Lucene in Action, 2nd Ed. Manning Publications, 2010. Book.

The source code is a little dated 5 years on, given extensive API changes since this book's publication. The concepts however remain relatively unchanged, and this is a well-written text.

[LingPipe, 08-March-2014] Lucene 4 Essentials

Good overview of Lucene's inverted index structure.

Friday, January 30, 2015

MongoDB with Node.js

Find

this will:

 exports.connect = function() {  
      console.log("connecting to MongoDB ... ");  
      var mongoose = require('mongoose');  
      mongoose.connect('mongodb://host/db/');  
   
      var Gngram = mongoose.model('Schema', {   
           term: String,  
           noun: String,  
           verb: String }, 'collection');  
   
      Gngram.find(  
           {  
                $and:  
                [  
                     { "noun" : { $gte : "0.4" } },  
                     { "verb" : { $gte : "0.4" } }  
                ]  
           },  
           {  
                "term" : 1  
           },  
           function(err, gngrams) {  
                if (err) console.error(err);  
                console.log(gngrams);  
           }  
      );  
 }

In the Google n-Gram collection this query will look for terms that are used as both nouns and verbs with a distribution spread of 40% each. This code is highlighted in blue.

The query also uses projection to limit the returned data to terms sorted in ascending order.

Quick Reference

Specify in the sort parameter the fields to sort by and a value of 1 or -1 to specify an ascending or descending sort respectively^[1]

Tuesday, January 20, 2015

Changing the Data Path in MongoDB

Changing the dbPath

Changing the dbPath for MongoDB can be an exercise in frustration if permissions are not managed correctly.

A common error:

 craig@U14BASE01:~$ mongo  
 MongoDB shell version: 2.6.7  
 connecting to: test  
 2015-01-20T13:54:49.274-0800 warning: Failed to connect to 127.0.0.1:27017, reason: errno:111 Connection refused  
 2015-01-20T13:54:49.276-0800 Error: couldn't connect to server 127.0.0.1:27017 (127.0.0.1), connection attempt failed at src/mongo/shell/mongo.js:146  
 exception: connect failed

1. Stopping the Service

The first step is to stop the MongoDB service:

sudo service mongod stop

2. Editing the Configuration

Then edit the MongoDB configuration file:

sudo gedit /etc/mongod.conf

I recommend commenting out the dbPath variable and adding a new one.

3. Setting Permissions

We have to give the user "mongodb" permission to use this folder. To check the current permission set use:

ls -l <dir>

I would expect to see "mongodb nogroup" in the file permission set.

If these do not exist, add them using this command:

sudo chown -R mongodb /path/to/db

Likewise, make sure that the parent directories have the same permission.

For example:

sudo chown -R mongodb /path/to/

4. Starting and Connecting

Once complete, you should be able to start the service and connect to MongoDB using this command sequence:

sudo service mongod start mongo

Application

After stepping through these instructions to add a new hard drive to an Ubuntu Virtual Box instance, I followed the steps above to configure a path on that drive for MongoDB.

My directory permissions look like:

  craig@U14BASE01:~$ ls -l -R /media/craig/  
 /media/craig/:  
 total 4  
 drwxr-xr-x 4 mongodb root 4096 Jan 20 13:54 mongo  
 /media/craig/mongo:  
 total 20  
 drwxr-xr-x 4 mongodb root 4096 Jan 20 13:54 db  
 drwx------ 2 mongodb root 16384 Jan 20 13:38 lost+found  
 /media/craig/mongo/db:  
 total 81932  
 drwxr-xr-x 2 mongodb nogroup   4096 Jan 20 13:54 journal  
 -rw------- 1 mongodb nogroup 67108864 Jan 20 13:54 local.0  
 -rw------- 1 mongodb nogroup 16777216 Jan 20 13:54 local.ns  
 -rwxr-xr-x 1 mongodb nogroup    5 Jan 20 13:54 mongod.lock  
 drwxr-xr-x 2 mongodb nogroup   4096 Jan 20 13:54 _tmp  
 /media/craig/mongo/db/journal:  
 total 3145740  
 -rw------- 1 mongodb nogroup 1073741824 Jan 20 13:54 j._0  
 -rw------- 1 mongodb nogroup 1073741824 Jan 20 13:54 prealloc.1  
 -rw------- 1 mongodb nogroup 1073741824 Jan 20 13:54 prealloc.2  
 /media/craig/mongo/db/_tmp:  
 total 0  
 ls: cannot open directory /media/craig/mongo/lost+found: Permission denied

Note the presence of the "mongodb" username in the user permissions column.

It's important to keep an eye on the log file.

Generally, when I received a "connection refused" error it was due to incorrect permissions. However, once the permissions were set correctly, I had to type "mongo" on the terminal line twice. The first time was a failure due to the lack of journal files; on the second time the command succeeded, and I gained access to the shell.

In another instance, I cloned the VM that held MongoDB. When the clone started, I was unable to connect to the MongoDB shell with the same error (connection refused). By restarting the service, and typing "mongo" twice on the terminal, access was restored.

References

Setting Permissions

If I change the dbPath to another directory on the same drive, this solution works for me.

Changing Permissions on the Parent Directory

This is the post that put me over the top.
The parent directory that held the mongo directory did not have the correct permissions.

Changing the Default Path

This is a detailed post.
While the solution did nothing for me, it was helpful to walk through a detailed approach that worked for someone else.

Friday, January 16, 2015

An Introduction to the Aggregation Pipeline

Finding Names

This query will find all actors with a name like "stevens"

db.test.find({"actor.displayName":/stevens/i}).pretty()

and I get back 40 results, with the full tweet for each result:

 /* 0 */  
 {  
   "_id" : ObjectId("54b860e65756612564fbc584"),  
   "body" : "@TheRevAl Rev Cornell is the \"misguided YOUNG kid\" caught n jihadist social media. T. Rice 12, is \"20yr old\" killed b'cuz of toy pellet gun",  
   "retweetCount" : 0,  
   "generator" : {  
     "link" : "http://twitter.com/download/iphone",  
     "displayName" : "Twitter for iPhone"  
   },  
   "twitter_filter_level" : "low",  
   "gnip" : {  
     "klout_score" : 13,  
     "matching_rules" : [   
       {  
         "tag" : "SIDM_10205",  
         "value" : "\"mobile app\" OR \"mobile application\" OR \"Social Commerce\" OR \"Social Media\" OR \"Social Campaign\" OR \"Digital Optimization\" OR \"Digital Analytics\" OR \"Digital Software\" OR \"site optimization\" OR \"conversation optimization\" OR \"B2B Integration\" OR \"B2B enterprises\" OR \"B2b customer\" OR \"B2b purchase\" OR \"product information management\" OR \"web content management\" OR \"order management\" OR \"Campaign Management\" OR \"multichannel attribute\" OR \"multichannel optimization\" OR \"multi-channel attribute\" OR \"Customer Experience\""  
       }  
     ],  
     "language" : {  
       "value" : "en"  
     }  
   },  
   "favoritesCount" : 0,  
   "object" : {  
     "postedTime" : "2015-01-16T00:33:02.000Z",  
     "summary" : "@TheRevAl Rev Cornell is the \"misguided YOUNG kid\" caught n jihadist social media. T. Rice 12, is \"20yr old\" killed b'cuz of toy pellet gun",  
     "link" : "http://twitter.com/EDSDrum/statuses/555885403102662657",  
     "id" : "object:search.twitter.com,2005:555885403102662657",  
     "objectType" : "note"  
   },  
   "actor" : {  
     "preferredUsername" : "EDSDrum",  
     "displayName" : "Ernest Stevens",  
     "links" : [   
       {  
         "href" : null,  
         "rel" : "me"  
       }  
     ],  
     "twitterTimeZone" : null,  
     "image" : "https://pbs.twimg.com/profile_images/1474488395/ES_Photo__2005___2__normal.jpg",  
     "verified" : false,  
     "location" : {  
       "displayName" : "MD",  
       "objectType" : "place"  
     },  
     "statusesCount" : 350,  
     "summary" : "Financier, Educator, Artist, Business Consultant and political junkie.",  
     "languages" : [   
       "en"  
     ],  
     "utcOffset" : null,  
     "link" : "http://www.twitter.com/EDSDrum",  
     "followersCount" : 1,  
     "favoritesCount" : 1,  
     "friendsCount" : 57,  
     "listedCount" : 1,  
     "postedTime" : "2009-07-01T16:26:25.000Z",  
     "id" : "id:twitter.com:52771595",  
     "objectType" : "person"  
   },  
   "twitter_lang" : "en",  
   "twitter_entities" : {  
     "user_mentions" : [   
       {  
         "id" : 42389136,  
         "indices" : [   
           0,   
           9  
         ],  
         "id_str" : "42389136",  
         "screen_name" : "TheRevAl",  
         "name" : "Reverend Al Sharpton"  
       }  
     ],  
     "symbols" : [],  
     "trends" : [],  
     "hashtags" : [],  
     "urls" : []  
   },  
   "verb" : "post",  
   "link" : "http://twitter.com/EDSDrum/statuses/555885403102662657",  
   "provider" : {  
     "link" : "http://www.twitter.com",  
     "displayName" : "Twitter",  
     "objectType" : "service"  
   },  
   "postedTime" : "2015-01-16T00:33:02.000Z",  
   "id" : "tag:search.twitter.com,2005:555885403102662657",  
   "objectType" : "activity"  
 }  
 ... 40 results total ...

Projection

That last query returned more data than I needed. I can use projection to solve this.

 db.test.find({"actor.displayName":/Stevens/i}, {"actor.displayName":1, _id:0})

and now I only get back data of interest to me, rather than the entire tweet:

 /* 0 */  
 {  
   "actor" : {  
     "displayName" : "Ernest Stevens"  
   }  
 }  
   
 /* 1 */  
 {  
   "actor" : {  
     "displayName" : "Shimmel Stevenson"  
   }  
 }  
   
 /* 2 */  
 {  
   "actor" : {  
     "displayName" : "John Stevens"  
   }  
 }  
   
 /* 3 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 4 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 5 */  
 {  
   "actor" : {  
     "displayName" : "Edward Stevens"  
   }  
 }  
   
 /* 6 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 7 */  
 {  
   "actor" : {  
     "displayName" : "Gina Stevenson"  
   }  
 }  
   
 /* 8 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 9 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 10 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 11 */  
 {  
   "actor" : {  
     "displayName" : "Edward Stevens"  
   }  
 }  
   
 /* 12 */  
 {  
   "actor" : {  
     "displayName" : "Stewart Stevenson"  
   }  
 }  
   
 /* 13 */  
 {  
   "actor" : {  
     "displayName" : "Edward Stevens"  
   }  
 }  
   
 /* 14 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 15 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 16 */  
 {  
   "actor" : {  
     "displayName" : "Ezra Stevens"  
   }  
 }  
   
 /* 17 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 18 */  
 {  
   "actor" : {  
     "displayName" : "patti stevens"  
   }  
 }  
   
 /* 19 */  
 {  
   "actor" : {  
     "displayName" : "Edward Stevens"  
   }  
 }  
   
 /* 20 */  
 {  
   "actor" : {  
     "displayName" : "Manny Stevenson"  
   }  
 }  
   
 /* 21 */  
 {  
   "actor" : {  
     "displayName" : "Manny Stevenson"  
   }  
 }  
   
 /* 22 */  
 {  
   "actor" : {  
     "displayName" : "Dennis Stevens"  
   }  
 }  
   
 /* 23 */  
 {  
   "actor" : {  
     "displayName" : "Stewart Stevenson"  
   }  
 }  
   
 /* 24 */  
 {  
   "actor" : {  
     "displayName" : "Ruben Stevens"  
   }  
 }  
   
 /* 25 */  
 {  
   "actor" : {  
     "displayName" : "Simon Stevens"  
   }  
 }  
   
 /* 26 */  
 {  
   "actor" : {  
     "displayName" : "Neil Stevens"  
   }  
 }  
   
 /* 27 */  
 {  
   "actor" : {  
     "displayName" : "DavidBernard-Stevens"  
   }  
 }  
   
 /* 28 */  
 {  
   "actor" : {  
     "displayName" : "Ryan Stevens"  
   }  
 }  
   
 /* 29 */  
 {  
   "actor" : {  
     "displayName" : "julia stevens"  
   }  
 }  
   
 /* 30 */  
 {  
   "actor" : {  
     "displayName" : "julia stevens"  
   }  
 }  
   
 /* 31 */  
 {  
   "actor" : {  
     "displayName" : "Mike Stevens"  
   }  
 }  
   
 /* 32 */  
 {  
   "actor" : {  
     "displayName" : "Neil Stevens"  
   }  
 }  
   
 /* 33 */  
 {  
   "actor" : {  
     "displayName" : "Michael Stevens"  
   }  
 }  
   
 /* 34 */  
 {  
   "actor" : {  
     "displayName" : "Guido Stevens"  
   }  
 }  
   
 /* 35 */  
 {  
   "actor" : {  
     "displayName" : "Stewart Stevenson"  
   }  
 }  
   
 /* 36 */  
 {  
   "actor" : {  
     "displayName" : "Eric Stevens"  
   }  
 }  
   
 /* 37 */  
 {  
   "actor" : {  
     "displayName" : "Mike Stevens"  
   }  
 }  
   
 /* 38 */  
 {  
   "actor" : {  
     "displayName" : "Skeeve Stevens"  
   }  
 }  
   
 /* 39 */  
 {  
   "actor" : {  
     "displayName" : "Amber Stevens"  
   }  
 }

But now I have a lot of duplicates, and it would be nice to have an aggregation with a count for the number of times each name appears.

All Distinct Names

I can find all the distinct names

 db.test.distinct("actor.displayName")

and that gives me this output:

 /* 0 */  
 {  
   "0" : "abdul",  
   "1" : "Ayunda Dewi",  
   "2" : "CheckAContract",  
   "3" : "Lisa Peyton",  
   "4" : "NJT Advisory",  
   "5" : "E3M Filters",  
   "6" : "Ernest Stevens",  
   "7" : "Social Media Time",  
   "8" : "GoodmanHailey",  
   "9" : "ERP-VIEW.PL",  
   "10" : "12Stocks.com Tech",  
   "11" : "Technology News",  
   "12" : "resign .",  
   "13" : "Kenji Hiranabe",  
   "14" : "LittleBabyTinySteve",  
   "15" : "Bryan K. Robinson",  
   "16" : "christopherlortie",  
   "17" : "ErinFlannagan",  
   "18" : "Liz Barnett",  
   "19" : "Agile Retweets 2.2k",  
   "20" : "Ferry Irawan",  
   <snip ... 71,499 records in this dataset ...>

Distinct returns an array (above). Arrays are not compatible with the aggregation pipeline^[1].

By Name

This demonstrates a basic use of the aggregation pipeline.

This will count the number of tweets that contain the name "stevens" in some variation:

 db.test.aggregate(  
   [  
     { $match: { "actor.displayName": /stevens/i } },  
     { $group: { _id: null, count: { $sum: 1 } } }  
   ]  
 )

and gives me this result:

 /* 0 */  
 {  
   "result" : [   
     {  
       "_id" : null,  
       "count" : 40  
     }  
   ],  
   "ok" : 1  
 }

But what I'm really looking for is a count for each distinct variation of a name that contains ": stevens". I'm missing some key elements in my query above.

The Aggregation Pipeline

This query appears to do what I want:

 db.test.aggregate([  
   {$match: { "actor.displayName": /stevens/i } },  
   {$group: { _id: { actor: "$actor.displayName" }, numberOfTimes: { $sum: 1 }}},   
   {$sort:{numberOfTimes:-1}}  
 ])

and the output is 21 results:

 /* 0 */  
 {  
   "result" : [   
     {  
       "_id" : {  
         "actor" : "patti stevens"  
       },  
       "numberOfTimes" : 10  
     },   
     {  
       "_id" : {  
         "actor" : "Edward Stevens"  
       },  
       "numberOfTimes" : 4  
     },   
     {  
       "_id" : {  
         "actor" : "Stewart Stevenson"  
       },  
       "numberOfTimes" : 3  
     },   
     {  
       "_id" : {  
         "actor" : "julia stevens"  
       },  
       "numberOfTimes" : 2  
     },   
     {  
       "_id" : {  
         "actor" : "Neil Stevens"  
       },  
       "numberOfTimes" : 2  
     },   
     {  
       "_id" : {  
         "actor" : "Manny Stevenson"  
       },  
       "numberOfTimes" : 2  
     },   
     {  
       "_id" : {  
         "actor" : "Mike Stevens"  
       },  
       "numberOfTimes" : 2  
     },   
     {  
       "_id" : {  
         "actor" : "Skeeve Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Eric Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Michael Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Ryan Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "DavidBernard-Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Amber Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Simon Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Ezra Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Dennis Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Ruben Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Ernest Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Gina Stevenson"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Guido Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "John Stevens"  
       },  
       "numberOfTimes" : 1  
     },   
     {  
       "_id" : {  
         "actor" : "Shimmel Stevenson"  
       },  
       "numberOfTimes" : 1  
     }  
   ],  
   "ok" : 1  
 }

sorted in ascending order, with a count of the number of times it appears.

Matching all Names

And if I withdraw the match condition, and just use this query:

 db.test.aggregate([  
   {$group: { _id: { actor: "$actor.displayName" }, numberOfTimes: { $sum: 1 }}},   
   {$sort:{numberOfTimes:-1}}  
 ])

The result is a sorted list (like the above):

 /* 0 */  
 {  
   "result" : [   
     {  
       "_id" : {  
         "actor" : "BYOD News"  
       },  
       "numberOfTimes" : 529  
     },   
     {  
       "_id" : {  
         "actor" : "free movies"  
       },  
       "numberOfTimes" : 521  
     },   
     {  
       "_id" : {  
         "actor" : "mclovemckee"  
       },  
       "numberOfTimes" : 480  
     },   
     {  
       "_id" : {  
         "actor" : "Cell Phone Deals"  
       },  
       "numberOfTimes" : 417  
     },   
     {  
       "_id" : {  
         "actor" : "Jose Fornelino Muñiz"  
       },  
       "numberOfTimes" : 376  
     },   
     {  
       "_id" : {  
         "actor" : "mckeeliurvg"  
       },  
       "numberOfTimes" : 328  
     },   
     {  
       "_id" : {  
         "actor" : "NoSQL"  
       },  
       "numberOfTimes" : 325  
     },   
     {  
       "_id" : {  
         "actor" : "Capitalsecure"  
       },  
       "numberOfTimes" : 302  
     },   
     {  
       "_id" : {  
         "actor" : "Antonio Trento?"  
       },  
       "numberOfTimes" : 257  
     },   
     {  
       "_id" : {  
         "actor" : "Top Sinhala Blog"  
       },  
       "numberOfTimes" : 252  
     },   
     {  
       "_id" : {  
         "actor" : "?FollowerSale.com"  
       },  
       "numberOfTimes" : 221  
     },   
     {  
       "_id" : {  
         "actor" : "Jeff Ho"  
       },  
       "numberOfTimes" : 209  
     },   
     {  
       "_id" : {  
         "actor" : "Cloud Server Hosts"  
       },  
       "numberOfTimes" : 206  
     },   
     ... 71,449 results total ...

for every name; not just "stevens".

Thursday, January 15, 2015

MongoDB Administration: RoboMongo on Ubuntu

Environment

Ubuntu 14.04
MongoDB 2.6.7
RoboMongo 0.8.4

Installation and Configuration

Steps:

Installation:

Download the File
Install the File
Launch the File

Configuration:

Manage Connections
Viewing Data

1. Downloading the File

Download the .deb file from the homepage:

I downloaded the file to my

/home/craig/Downloads

directory.

2. Installing the .deb file

Double-click the .deb file and select install:

You will be required to authenticate as root to install the package. The installation time will vary based on the speed of network connection.

A successful installation will look like this:

3. Running RoboMongo

Use the search application in Ubuntu to find "RoboMongo":

Congratulations! You have successfully installed RoboMongo.

4. Creating a Connection

When RoboMongo is launched for the first time, you will need to create a connection to your database.

Click the "create" link on the dialog box as shown below: