How to query with Curl in couchbase

SQL++
1

I am doing to test performance of MySQL and Couch base by using different queries. In MySQL I used SQL server profiler. like this

SET profiling = 1;

and then use query like

SELECT `Temp.Col.001` FROM kami_file1_dat WHERE ID > 1430870400 AND 
ID < 1430956800;

and then I saw time by using this command

SHOW PROFILES;

and it works completely fine. Now I am trying to use the same query and measure time in Couch base. I look over internet but I didn’t got any clue how to do this query in Couch base.

I tried something like this but it doesn’t work for me

curl -d 'q=SELECT `Temp.Col.001` FROM kami_file_dat WHERE
id > 1430870400 AND id < 1430956800.

I got this error

 {
    "requestID": "a893f17f-ae39-43fb-a8a2-276b1ebbb3a0",
    "errors": [
        {
            "code": 1065,
            "msg": "Unrecognized parameter in request: q"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "45.466µs",
        "executionTime": "45.233µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}

If someone guide or help me in this regard. I would be thankful.

2

you can run N1QL by cbq or Query Workbench or PREPARE statement FYI

3

@atom_yang Thanks for your kind reply. I am new to Couch-base. Can you give me a small example by using curl or any other method. I would be grateful.

4

I tried one query like this
curl -v http://localhost:8093/query/service -d ‘statement=PREPARE SELECT Temp.Col.001 FROM kami_file1_dat WHERE ID > 1430870400 AND ID < 1430956800’ .

but i got this error now.

{
“requestID”: “6011ac24-8ea3-4975-a206-3f7e86180d96”,
“errors”: [
{
“code”: 4000,
“msg”: “No index available on keyspace kami_file1_dat that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”
}
],
“status”: “fatal”,
“metrics”: {
“elapsedTime”: “745.176µs”,
“executionTime”: “698.197µs”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}

this kami_file1_dat is bucket name and ID are timestamps.

5

for example, you can use PREPARE statement like

# create PREPARE
curl -v http://127.0.0.1:8093/query/service --data-urlencode 'statement=PREPARE query001BYusec FROM SELECT `Temp.Col.001` FROM kami_file1_dat WHERE usec > $min_usec AND 
usec < $max_usec; '

# query by PREPARE
curl -v http://127.0.0.1:8093/query/service -d 'prepared="query001BYusec"&$min_usec= 1430870400&$max_usec= 1430956800'
6

@atom_yang Thanks a lot. I will give it a try and will come back soon. I hope it will work for me.

7

@atom_yang I got the same error as I mention above .

“code”: 4000,
“msg”: “No index available on keyspace kami_file1_dat that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”

Can you please guide me how to get rid of it. Thanks for your time and help.

8

you should create PRIMARY index first by cbq or Query Workbench with

CREATE PRIMARY INDEX `pidx` ON `kami_file1_dat` USING GSI;
9

my bucket name is kami_file1_dat and when i click on the Documents, it looks like this

ID Content
1430870400 {“Temp.Col.001: 17”, …“press.col.001: 13”…}
1430871000 {“Temp.Col.001: 19”, …“press.col.001: 42”…}
1430871120 {“Temp.Col.001: 33”, …“press.col.001: 53”…}
.
.
.
.
1430956800 {“Temp.Col.001: 25”, …“press.col.001: 43”…}

I have data every 10 minutes, so I store this data by using timestamp as ID. Now I want to extract temperature column data of 1 day and for that purpose I had to extract between two timestamp IDs. and should also receive the data. .
If I understand you correctly, I build primary index like this
CREATE PRIMARY INDEX ID ON kami_file1_dat USING GSI;
After that I run these commands

curl -v http://127.0.0.1:8093/query/service --data-urlencode ‘statement=PREPARE query001BYID FROM SELECT Temp.Col.001 FROM kami_file1_dat WHERE ID > $min_ID AND ID < $max_ID;’

and then this command

curl -v http://127.0.0.1:8093/query/service -d ‘prepared=“query001BYID”&$min_ID=1430870400$max_ID=1430956800’.

but there is no data displayed. Is I am making some mistake or I didn’t understand correctly the building primary index thats why i am getting wrong output.
Thanks for your help.

10

you should use META().id to get the document id,so, change your N1QL by

SELECT `Temp.Col.001` FROM kami_file1_dat WHERE META().id BETWEEN $min_ID AND  $max_ID;

notice: you should delete query001BYID first by cbq or Query Workbench with

DELETE FROM system:prepareds WHERE name == "query001BYID"

if you want to use the same PREPARE name.

11

I follow your guidelines and i got this

"requestID": "b37a9db5-aec0-4f3d-9573-16ce147eff27",
"signature": {
    "Temp.Col.001": "json"
},
"results": [
],
"status": "success",
"metrics": {
    "elapsedTime": "438.510721ms",
    "executionTime": "438.477717ms",
    "resultCount": 0,
    "resultSize": 0
}

}

Is this correct because I don’t see any values of this column “Temperature”?. I thought it will display all 143 values cox this columns has a value every 10 minutes.

I used these commands exactly

  1. DELETE FROM system:prepareds WHERE name == “query001BYID”

  2. CREATE PRIMARY INDEX ID ON kami_file1_001 USING GSI;

  3. curl -v http://127.0.0.1:8093/query/service --data-urlencode ‘statement=PREPARE query001BYID FROM SELECT Temp.Col.001 FROM kami_file1_dat WHERE META().id BETWEEN $min_ID AND
    ID < $max_ID;’

  4. curl -v http://127.0.0.1:8093/query/service -d ‘prepared=“query001BYID”&$min_ID= 1430870400&$max_ID= 1430956800’
    .

Sorry for asking too many questions. I am trying my best to understand. Cox my aim is to just measure the time taken by a query of 1 day. In MySQL it was very easy, but I am new to Couchbase so it is taking time.

12

try this:

curl -v http://127.0.0.1:8093/query/service -d ‘prepared=“query001BYID”&$min_ID= "1430870400"&$max_ID="1430956800"’

because primary key should be String.

13

Once again my apology. Now I got this message. .

{
“requestID”: “99b5cb7c-a8bb-4d40-89cd-f075926c25dd”,
“errors”: [
{
“code”: 1065,
“msg”: “Unrecognized parameter in request: ‘prepared”
}
],
“status”: “fatal”,
“metrics”: {
“elapsedTime”: “55.86µs”,
“executionTime”: “55.626µs”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}

  • Connection #0 to host 127.0.0.1 left intact
    =: command not found
    =1430956800’: command not found
    [1]- Done curl -v http://127.0.0.1:8093/query/service -d ‘prepared=“query001BYID”
    [2]+ Exit 127 $min_ID= “1430870400”
14 
curl -v http://127.0.0.1:8093/query/service -d 'prepared="query001BYID"&$min_ID="1430870400"&$max_ID="1430956800"'

there is a blank after min_ID=

15

Now it shows no error, but still I got no data values of the temperature column. it should display 143 values of the 1 day query. . :confused:

{
“requestID”: “343bba66-23d3-4bef-bfa8-38849ebbd45a”,
“signature”: {
“Temp.Col.001”: “json”
},
“results”: [
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “470.608341ms”,
“executionTime”: “470.572414ms”,
“resultCount”: 0,
“resultSize”: 0
}
}

  • Connection #0 to host 127.0.0.1 left intact
16

How about run N1QL by cbq with

SELECT `Temp.Col.001`,META().id FROM kami_file1_dat LIMIT 10;

maybe the Comparison Operation is not correct. you should try more.

17

when I run this command in couchbase as you mention with limit, it shows me data values. But I want to achieve or get these results by using curl.

18

does this N1QL

SELECT `Temp.Col.001` FROM kami_file1_dat WHERE META().id BETWEEN "1430956800" AND "1430870400";

return data that you want?

19

yes it return that data which I want or required to view.

20

ok,try this

curl -v http://127.0.0.1:8093/query/service -d 'prepared="query001BYID"&$max_ID="1430870400"&$min_ID="1430956800"'
1 Like