Cbq output file

Couchbase Server
1

Hi,

I am scheduling a job to run a query using cbq and output to a file. however the output not only includes the query result, but also has the query statement, and other states like:

“requestID”: “fbccc4a6-a66a-4d50-895b-720db6641884”,
“signature”: {xxx},
“status”: “success”,
“metrics”: {“elapsedTime”: “3.214837509s”,“executionTime”: “3.214784273s”,“resultCount”: 28,“resultSize”: 1002}.

This is my cbq command:

/opt/couchbase/bin/cbq -q -e https://127.0.0.1:18093 -u=xxx -p=xxx --no-ssl-verify -s=“select tenant,count(*) from default where _type = ‘Study’ group by tenant” -o=“study_per_tenant.json”

How can I only output the query result, not other info?

Thanks,
Matt

2

Remember cbq isn’t expected to be a “simple unload” tool as you’re trying to use it here and note the help for -o does indicate that both the statement and results are written to the output.

To some extent what you can do depends on the version you’re using. In all versions you can turn off the signature and metrics with request parameters, but you’ll always have the request ID and status except if on 7.1+.

On 7.1+ you can add the “-terse” command line option to greatly reduce the non-result output. (Combine with turning off metrics & pretty printing for minimal output.) If you redirect the output rather than use -o you need only eliminate the “resultCount” line to capture just the results:

cbq -q -terse -pretty=false -u xxx -p yyy -e http://localhost:8093 -s "select * from default limit 10"|grep -v "^," > results.json

NOTE: This is a comma-separated list of JSON objects, not a JSON array of objects. If you want the output as fully-formed JSON you’d have to add surrounding brackets.)

Generally a much easier, version independent way is to just use ‘jq’ to post-process the output and extract the “results” field, e.g.

cbq -q -u xxx -p yyy -e http://localhost:8093 -s "select * from default limit 10"|jq .results > result.json

You could also use the REST API:

curl -su uuu:ppp http://localhost:8093/query/service -d 'metrics=false&signature=false&statement=select * from default limit 10' |jq .results > result.json

in this manner, if you prefer.

Without examining the status field though, you won’t be able to monitor if your job failed. So perhaps a two phase approach is best? Something roughly like:

cbq -q -u xxx -p yyy -e http://localhost:8093 -s "select * from default limit 10" > cbq.results
STATUS=`jq .status cbq.results|sed 's/"//g'`
if [ $STATUS != "success" ]
then
   # report the error
fi
jq .results cbq.results > results.json

HTH.