How to query with Curl in couchbase

this works for me:

# insert test data
INSERT INTO kami_file1_dat VALUES("1430870400", {"Temp.Col.001": 17, "press.col.001": 13});
# Create PREPARE statement
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 $max_ID; '
# query by PREPARE
curl -v http://127.0.0.1:8093/query/service -d 'prepared="query001BYID"&$min_ID="1430870400"&$max_ID="1430956800"'

FYI

1 Like
curl -v http://localhost:8093/query/service -d 'statement=SELECT `Temp.Col.001` FROM kami_file_dat WHERE
META().id > "1430870400" AND META().id < "1430956800"'

Checkout Improved Profiling and Monitoring In 5.0.0 https://blog.couchbase.com/new-profiling-monitoring-couchbase-server-4-6/

1 Like

@atom_yang @vsr1 Sorry for late response. Since I am new to this forum so I came to know yesterday that I can reply only 21 times on the first day and then i had to wait for 24 hours for next reply. So i am replying now. .:slightly_smiling_face:. the curl statement is working now properly. I used @atom_yang command as well as @vsr1. But I had one question. (guide me if i post it as a new question or not?) . …In MySQL and Couchbase I read data of 1 day and 1 month by using same query.

For 1 day query the MySQL takes 40 milliseconds, but Couchbase took only 10 milliseconds. which shows that couch base is faster.:slightly_smiling_face:

And for 1 month data query MySQL still take 40 milliseconds, But in Couchbase it took 196- 225 milliseconds to display the same data of 1 month by using the same query . .“5) curl -v http://localhost:8093/query/service -d ‘statement=SELECT Temp.Col.001 FROM kami_file1_dat WHERE META().id > “1430438400” AND META().id < “1432944000”’” …

Why is Couchbase slow as compared to MySQL?. It should be faster.

Post the sample document, query and couchbase version

Try this.

CREATE INDEX ix_kami_col001 ON kami_file1_dat(META().id,`Temp.Col.001`);

If CB version 4.5.1 or higher 

curl -v http://localhost:8093/query/service -d 'statement=SELECT `Temp.Col.001` FROM kami_file_dat WHERE
META().id > "1430870400" AND META().id < "1430956800"&pretty=false'

Checkout Indexing and optimization sections IN https://dzone.com/articles/n1ql-a-practicle-guide

@vsr1 the version I am using is (4.6.0-3453) which I get from “/opt/couchbase/VERSION.TXT”. …and the query on that document is

SELECT EB1.HMP.T.AIR.001.AVG FROM EBM_DAR WHERE META().id BETWEEN “1467331200000000” AND “1469836800000000”;

and the sample document is attached as picture. . .Thanks a lot for your time and help in this regard. couchbase

Every timestamp had data which includes different variables, e.g temperature, pressure , humidity etc. So I store data as timestamp as ID to fetch it .
couchbase2
couchbase3

Try this.

CREATE INDEX ix1 ON EBM_DAR(META().id,`EB1.HMP.T.AIR.001.AVG`)
curl -v http://localhost:8093/query/service -d 'statement=SELECT `EB1.HMP.T.AIR.001.AVG` FROM EBM_DAR WHERE META().id BETWEEN "1467331200000000" AND "1469836800000000"&pretty=false'

Also As you projecting single field and not interested in the field name so that results size is smaller

curl -v http://localhost:8093/query/service -d 'statement=SELECT RAW `EB1.HMP.T.AIR.001.AVG` FROM EBM_DAR WHERE META().id BETWEEN "1467331200000000" AND "1469836800000000"&pretty=false'

Indexing and optimization sections IN https://dzone.com/articles/n1ql-a-practicle-guide gives useful information.

1 Like

@vsr1 First of all I am really really thankful to you for your kind help in this regard. I follow your 2 instructions above by creating index and using curl accordingly without the RAW, the query time comes down to 50 - 60 milliseconds with using curl command and with RAW it is 60-80 milliseconds.

I read this article “Performance Ingredients for NoSQL: Intersect Scans in N1QL” and “The Latest Performance Tricks in N1QL”. I try to implement something like this

CREATE INDEX def_type ON EBM_DAR(META().id,EB1.HMP.T.AIR.001.AVG);
and then I run my query. the time is still 50-60 milliseconds. I am trying to optimize it below 35 milliseconds because with MySQL I am getting 40 milliseconds. Is it possible to improve it further?. .

Thanks for your precious help

Web interface needs to format and display overhead. If you need to measure timings you should use CURL OR CBQ shell OR SDKs. You should read through Designing Index For Query In Couchbase N1QL , Understanding Index Scans. Hope you are using Memory Optimized Index.

1 Like

@vsr1 thanks a lot for the kind reply. Yes I am interested only in measuring timing performance. I will use curl and also I will read “Designing Index For Query In Couchbase N1QL” to understand Index Scans and will come back after giving it a try, because I had to optimize it so that it give me better results than MySQL query and time taken is less than 40 milliseconds…

I do not know about Memory Optimized Index. I am using the index proposed by you and it is this one
[CREATE INDEX ix1 ON EBM_DAR(META().id,EB1.HMP.T.AIR.001.AVG)
]…:slightly_smiling_face:

This is configuration during installation. You can check In Web console, Setting Tab, Index Settings.
N1QL works on JSON each document stores field name with it the document size will be bigger than MySQL. So there will be overhead.

Also Query Service of CE version limits number of CPU Cores to 4

1 Like

@vsr1 when I try to change the settings to “Memory-Optimized Global Secondary Indexes” instead of “”"“Standard Global Secondary Indexes”"""
I got this message

“Changing the optimization mode of global indexes is not supported when index service nodes are present in the cluster. Please remove all index service nodes to change this option.”

for the same query of 1 month data, MySQL takes 40 milliseconds and N1QL takes 60 miliseconds. . If I understand you correctly, you mean that N1QL query time will be slower than MySQL query time? . cox of document size overhead? .

You will not be able to change Index mode dynamically, @deepkaran.salooja. Follow the what message says.
I am not saying N1QL is slower, it depends on many factors system configuration etc. You can also use prepare statements. i.e prepare once and execute many times.

@vsr1 I had one small question here and I would be grateful to you. . this command works fine

"curl -v http://localhost:8093/query/service -d ‘statement=SELECT RAW EB1.HMP.T.AIR.001.AVG FROM EBM_DAR WHERE META().id BETWEEN “1467331200000000” AND “1469836800000000”&pretty=false’
"
But when I try to run it for 2 field , e.g

curl -v http://localhost:8093/query/service -d ‘statement=SELECT RAW EB1.HMP.T.AIR.001.AVG, EB1.HMP.M.RH.001.AVG FROM EBM_DAR WHERE META().id BETWEEN “1467331200000000” AND “1469836800000000”&pretty=false’

It gives this error.

“{
“requestID”: “a34b6e23-799c-44cc-abc2-0180e9df59af”,
“errors”: [{“code”:3000,“msg”:“syntax error - at ,”}],
“status”: “fatal”,
“metrics”: {“elapsedTime”: “279.951µs”,“executionTime”: “232.95µs”,“resultCount”: 0,“resultSize”: 0,“errorCount”: 1}
}

And Once again thanks a lot for your precious time and guidance. this forum and you people are really wonderful. .

RAW only works when projection contains single field. So remove RAW.
Also you need to adjust your index make it covered query otherwise it can take longer time.

1 Like

@vsr1 Thanks for your valuable suggestions and help. .Have a nice time . .:slightly_smiling_face:

@vsr1 I read about query indexing and other articles from the link you mention. thanks a lot because that book contains good articles about Couchbase. How can I make my query covered?. .If I understand correctly the covered query syntax, it looks like this

CREATE INDEX ix1 ON travel-sample (id, country, name) WHERE type = “airline” ; But How Can I convert my query cox it looks different from this use case.

My Query looks like this

CREATE INDEX ix1 ON EBM_DAR(META().id,EB1.HMP.T.AIR.001.AVG,EB1.HMP.M.RH.001.AVG);

I cannot include the WHERE clause because my data has no attribute like type etc. . I had timestamp as key and then it has key,values data inside as mention in my pictures. .Is it possible that I can convert my query to covered query to make it better?.
I guess my query is already covered query?. am I right?. .:slightly_smiling_face::slightly_smiling_face:

Have a nice time.

Index WHERE clause is optional. If you do EXPLAIN on query and you see cover, you are good.

1 Like

@vsr1 yes it is cover. thanks for your kind response. .:slightly_smiling_face::slightly_smiling_face:

How can i create an index using curl ?

@monisha_m_Singh You can create index by posting request to query service using below sample curl command:
curl -v http://localhost:8093/query/service -d ‘statement=create index indexName on bucketName(field1,field2)&creds=[{“user”:"username, “pass”:“password”}]’