Performance N1ql on cluster

SQL++
21

Hello,
Because the query has a aggregate operation on a importat number of rows is candidate to use MAP and Reduce Views.

I’ve créate a view that return a main indicator and a sum of a column. And the performance is very good because, the views créate a index with the ouput of the aggregation and is materialized on disk.

The view is accesible via rest API.

http://127.0.0.1:8092/HR/_design/suma/_view/suma?connection_timeout=60000&group=true&inclusive_end=true&limit=6&reduce=true&skip=0&stale=false
{“rows”:[
{“key”:10,“value”:143360000},
{“key”:20,“value”:178176000},
{“key”:30,“value”:154009600}
]
}
Now , I’ll ask if is posible to Access a view output from N1QL.

Regards
Arturo

1 Like
22

For the following query could you help me to translate it into a map-reduce?

SELECT SUM (operation), user
BucketTime FROM USE INDEX (indexTest USING GSI)
WHERE environment = "/ root"
AND startTime> 1356998400000
AND endTime <1483228799999
GROUP BY user
ORDER BY DESC INDEX

Thank you very much

23

Hello,
The steps are:

  1. From the GUI go to Buckets and Views option click on Views:
    2.Click on Create Development View (First the view is created on dev , and after publish to Prod env)
    3.Write the name of the view at document level and View level in my case suma
  2. In the view code panel, remove the example code and put the View code:

function (doc, meta) {
if (doc.type == environment = “/ root” && startTime> 1356998400000 && endTime <1483228799999) {
emit(doc.user, doc.operation);
}
}

5.On the righ panel (REDUCE) PUT:
_sum
6. click on Filter Results:
Choose GROUP (click) and Reduce (click) and close.
7.You can click on buttom show results and generate the view with local data. You have the option of click on Full Cluster Data Set to generate the view with all cluster Data.

At this moment you view the query results to see if is correct. In my case somethink like:

{“rows”:[
{“key”:10,“value”:143360000},
{“key”:20,“value”:178176000},
{“key”:30,“value”:154009600}
]
}

  1. Click on SAVE buttom.
  2. Back to the main VIEWS MENU and click the PUBLISH option to deploy the view at all cluster nodes.

You can Access to the view output with a URL in the VIEW menú:
In my case:

http://127.0.0.1:8092/HR/_design/dev_suma/_view/suma?connection_timeout=60000&group=true&inclusive_end=true&limit=6&skip=0&stale=false
And show the results.
{“rows”:[
{“key”:10,“value”:173100},
{“key”:20,“value”:177625},
{“key”:30,“value”:140600}
]
}

With this way you query need improve.
However, the view can be accced from any SDK.

Regards
Arturo

1 Like
24

Again thanks for the support
My problem is that the fields (environment, start time and endtime) are variable and can not fix them in views.
There is possibility to make this field parametric, for example using startKey and endKey?

25

Hello,
My experience with Views aré límited to créate from the GUI.

You need to use any SDK to use views.

Take a look at Couchbase doc

http://developer.couchbase.com/documentation/server/4.5/sdk/java/view-queries-with-sdk.html

Regards
Arturo

26

hi,
but the view create from the gui have a parameter external ?
you know how I can convert my query in a map-reduce?
The parameters of the where are variable I can not enter directly in view

Thanks for your useful tips