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

本文是小编为大家收集整理的关于如何从SQL转换为NOSQL/MAPREDUCE?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个与关系数据库一起工作的背景,但最近开始在CouchDB中涉足,并且对某些非关系操作(在SQL中很简单)如何并不是CouchDB中的一流函数感到惊讶.

.

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

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一次发送所有更改.

另外,请咨询有关视图的文档有关如何发出查询的详细信息.这包括订购,分组等


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

地图

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

减少

_count

如果您需要使用任意金额,而不仅仅是20个,那么您需要在所有情况下发出价格,并使用startkey和endkey来缩小结果范围.


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

地图

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

减少

_sum

此地图功能本质上使用该类别作为键,价格为您的键/值对中的值.降低功能将增加每个不同键的价格.


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

地图

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

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

本文地址:https://www.itbaoku.cn/post/597607.html

问题描述

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.