如何从SQL转换为NoSQL / MapReduce？ [英] How to translate from SQL to NoSQL/MapReduce?

问题描述

我有一个背景与关系数据库工作，但最近开始在CouchDB中挖掘，并惊讶于一些非关系操作，这将是简单的SQL，在CouchDB中不是一流的函数。

我想请您花一点时间将下面的每个SQL语句映射到其MapReduce等效项。

  SELECT COUNT（*）FROM products WHERE price< 20.00; 
 SELECT类别，SUM（价格）FROM产品GROUP BY类别; 
更新产品SET price = 19.99 WHERE price = 20.00; 
 DELETE FROM products WHERE expires_at< = NOW（）; 
  

解决方案

SELECT 命令很容易。批量写入有点复杂。通常，您将使用某些视图来检索需要更改的文档，然后您将使用 _bulk_docs API可一次发送所有更改

另请参阅有关视图的文档。这包括排序，分组等。

---

SELECT COUNT（*）FROM products WHERE price< ; 20.00;

映射

  （doc）{
 if（doc.price< 20）{
 emit（doc.price）; 
} 
} 
  

减少

  _count 
  

有一个任意量，不只是20，那么你需要在所有情况下发出价格，并使用 startkey 和 endkey 缩小结果集。

---

SELECT类别，SUM GROUP BY类别;

地图

  function（doc）{
 emit（doc.category，doc.price）; 
} 
  

减少

  _sum 
  

此映射函数基本上使用类别作为键，将价格作为您的键/值对中的值。 reduce函数将为每个不同的键添加价格。

---

更新产品SET price = 19.99 WHERE price = 20.00;

地图

  function（doc）{
 if（doc.price == 20）{
 emit（doc.price）; 
} 
} 
  

一旦你的应用程序拉下这个视图的内容，您将在应用程序代码中执行所有操作，然后通过 _bulk_docs API将结果发送回数据库。

---

DELETE FROM products WHERE expires_at <= NOW（）;

$ b b

地图

  function（doc）{
 emit（doc.expires_at）; 
} 
  

根据您的日期/时间值的存储方式，您可能需要调整地图功能以及您对查看的查询。使用时间戳（JS使用毫秒而不是秒）可能是实现这一点的最快的方法。设置查询后，您需要为每个文档添加一个新字段。 _deleted：true 。一旦您将此列表发送回数据库（再次使用 _bulk_docs ），所有指定的文档将被删除。

I have a background working with relational databases but recently started to dabble in CouchDB and was surprised by how some non-relational operations, which would be simple in SQL, were not first-class functions in CouchDB.

I would appreciate you taking a moment to map each SQL statement below to its MapReduce equivalent.

SELECT COUNT(*) FROM products WHERE price < 20.00;
SELECT category, SUM(price) FROM products GROUP BY category;
UPDATE products SET price = 19.99 WHERE price = 20.00;
DELETE FROM products WHERE expires_at <= NOW();

解决方案

The SELECT commands are pretty easy. Bulk writes are a bit more complicated. Generally, you'll use some view to retrieve the documents that need to be changed, then you'll use the _bulk_docs API to send all the changes at once.

Also, consult the documentation regarding views for details for how to issue queries. This includes ordering, grouping, etc.

---

SELECT COUNT(*) FROM products WHERE price < 20.00;

Map

function (doc) {
  if (doc.price < 20) {
    emit(doc.price);
  }
}

Reduce

_count

If you need this to work with an arbitrary amount, not just 20, then you'll need to emit the price in all cases, and use startkey and endkey to narrow down your resultset.

---

SELECT category, SUM(price) FROM products GROUP BY category;

Map

function (doc) {
  emit(doc.category, doc.price);
}

Reduce

_sum

This map function essentially uses the category as the key, with the price as the value in your key/value pair. The reduce function will add up the prices for each different key.

---

UPDATE products SET price = 19.99 WHERE price = 20.00;

Map

function (doc) {
  if (doc.price == 20) {
    emit(doc.price);
  }
}

Once your application pulls down the contents of this view, you'll perform all the manipulations in your application code, then send back the results into the database via the _bulk_docs API.

---

DELETE FROM products WHERE expires_at <= NOW();

Map

function (doc) {
  emit(doc.expires_at);
}

Depending on how your store your date-time values, you may need to adjust the map function as well as your query to the view. Using a timestamp (JS uses milliseconds instead of seconds) is probably the fastest way to accomplish this. Once you've set up your query, you'll add a new field to each of these documents. _deleted: true. Once you send this list back into the database (again with _bulk_docs) all the specified documents will be deleted.

这篇关于如何从SQL转换为NoSQL / MapReduce？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！