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

问题描述

我有使用关系数据库的背景，但最近开始涉足 CouchDB，并且对一些在 SQL 中很简单的非关系操作在 CouchDB 中不是一流的函数感到惊讶.

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.

感谢您花点时间将下面的每个 SQL 语句映射到其 MapReduce 等效项.

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();

推荐答案

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

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;

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

减少

_count

如果您需要使用任意数量，而不仅仅是 20，那么您需要在所有情况下发出价格，并使用 startkey 和 endkey缩小结果集.

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;

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

减少

_sum

此地图函数本质上使用类别作为键，价格作为键/值对中的值.reduce 函数会将每个不同键的价格相加.

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.

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

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

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

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();

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

根据您存储日期时间值的方式，您可能需要调整地图功能以及对视图的查询.使用时间戳(JS 使用毫秒而不是秒)可能是完成此操作的最快方法.设置查询后，您将为每个文档添加一个新字段._deleted: true.一旦将此列表发送回数据库(再次使用 _bulk_docs)，所有指定的文档都将被删除.

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屋！