I did the same thing described in the previous article using ElasticSearch as target db. I’m using Nest as .NET driver. Here is the document model:

namespace SQL2Elastic.Models
{    
	[ElasticType]
	public class ESProduct
	{
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed, Type = FieldType.String)]
		public Guid Id { get; set; }
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed)]
		public string Code { get; set; }
		public string Description { get; set; }
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed)]
		public double Price { get; set; }
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed)]
		public long IdCategory { get; set; }
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed)]
		public IList<string> Synonims { get; set; }
		[ElasticProperty(Type = FieldType.Nested)]
		public IList<ProductAttribute> Attributes { get; set; }
	}

	[ElasticType]
	public class ProductAttribute
	{
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed)]
		public string Key { get; set; }
		[ElasticProperty(Index = FieldIndexOption.NotAnalyzed)]
		public string Value { get; set; }
	}
}

There are different ways to perform mapping with NEST: inference/dynamically generated, code based, attribute-based. I choosed Attributed-based, because I like the idea to link the document types to their mappings, it seems to me a natural way to see this thing. But, as you can see, mapping comes with analysing.
A step back: ElastiSearch was born to simplify text search in huge db. Full-text search is not particularly efficient in traditional RDBMS like MSSQL, while is very fast with ElasticSearch. Accordingly to that, ElasticSearch implicitly analyses all fields (because it wants to simplify the programmer’s job), and text fields with full-text analysis, unless you say to NOT to do that, as I did in most of my document type fields.

Indeed the catalog I’m working with does not need the full-text search feature and, for this reason, ElasticSearch could be not the right choice for this scenario. But let’s give ElasticSearch a chance.

The migration logic is inside its specific ElasticSearchClient. Again, I want to highlight three aspects about that:

  1. In the initialization logic there is the mapping logic taken from the document type’s attributes
  2. XML-to-JSON mapping is natural too 
    public void Save(SQLProduct dbProduct)
{
 Contract.Requires<ArgumentNullException>(dbProduct != null, "dbProduct");
 var product = new ESProduct
 {
     Id = Guid.NewGuid(),
     Code = dbProduct.Data.Code,
     Description = dbProduct.Data.Description,
     IdCategory = dbProduct.Data.IdCategory,
     Price = Math.Round(10 + rnd.NextDouble() * (1000 - 10), 2),
     Synonims = dbProduct.Synonims.ToStringList(),
     Attributes = dbProduct.Attributes.ToProductAttributes()
 };
 products.Add(product);
}
  3. No post-migration logic is necessary, because, by now, all the analysis settings put on initialization step are good enough. I’m waiting, at this point, to have migration time greater than with MongoDb, because when ElasticSearch indexes a document, it populates some other fields and index data…let’s see.

Here is the console log for SQl to ElasticSearch migration:


as expected, but pretty good.

Queries

Let’s see the queries and times with ElastiSearch for multi-attribute catalog:

Query for all product attributes (~175ms)

GET /catalog/products/_search?search_type=count
{
  "aggs": {
    "multi_properties": {
      "nested": {
        "path": "attributes"
      },
      "aggs": {
        "all_properties": {
          "terms": {
            "field": "key",
            "size": 0,
            "order": {
              "_term": "asc"
            }
          },
          "aggs": {
            "all_values_per_property": {
              "terms": {
                "field": "value",
                "size": 10,
                "order": {
                  "_term": "asc"
                }
              }
            }
          }
        }
      }
    }
  }
}

Query for product attributes filtered by some attribute values (~6ms)

GET /catalog/products/_search?search_type=count
{
  "aggs": {
    "multi_properties": {
      "nested": {
        "path": "attributes"
      },
      "aggs": {
        "all_properties": {
          "terms": {
            "field": "key",
            "size": 0
          },
          "aggs": {
            "all_values_per_property": {
              "terms": {
                "field": "value",
                "size": 10
              }
            }
          }
        }
      }
    }
  },  
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "attributes",
                "query": {
                  "bool": {
                    "must": [
                      {"term": {
                        "key": {
                          "value": "FORMATO"
                        }
                      }},
                      {"term": {
                        "value": {
                          "value": "0402 (1.0 x 0.5mm)"
                        }
                      }}
                    ]
                  }
                }
              }
            },
            {
              "nested": {
                "path": "attributes",
                "query": {
                  "bool": {
                    "must": [
                      {"term": {
                        "key": {
                          "value": "TOLLERANZA"
                        }
                      }},
                      {"terms": {
                        "value": ["± 0.01%","± 0.05%","± 0.1%"]
                      }
                       
                      }
                    ]
                  }
                }
              }
            },
            {
              "nested": {
                "path": "attributes",
                "query": {
                  "bool": {
                    "must": [
                      {"term": {
                        "key": {
                          "value": "TCR (ppm)"
                        }
                      }},
                      {"term": {
                        "value": {
                          "value": "5.0"
                        }
                      }}
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

Query for documents filtered by some attribute values

The same queries as before, but without the search_type=count querystring parameter. So you don’t need to perform two different queries to get all the result to display. Very powerful!!! Very Fast!!!

Pros

  • Fast...wow!
  • Again, the query results give the complete structured json object, with the nested couples of attribute's value/count and, better than MongoDb, with Elastic'aggregates you can set the size of you aggregation and this fits very well with "see all values" feature in multi-attributes catalog.
  • No need for separate find queries: the aggregate without search_type=count querystring parameter, returns the matched documents

Cons

  • IMHO, queries and aggregates are not so readable and maintainable as they are with MongoDb
  • Not so fast as MongoDb in the migration phase

Comments

You May Also Enjoy