N1QL Shell Linux Script and output results to a csv


#1

Hello,
I have 2 questions about N1QL on Couchbase server 4.0.

1- Is there a way to run N1QL in a Shell Linux Script. I want to setup an automatic job to extract data from couchbase using crontab ?

2- Can be the results of N1QL queries be directed to a csv file instead of the input in JSON format ?

thanks,
W


#2

Hello,

You can run N1QL cbq in a shell script as follows:

echo ‘select 1;’ | cbq

As for csv output, it is not yet supported, but you can write a small script to write out the results as csv.

Thanks,
Gerald


#3

hello @geraldss ,
I am not able to convert the JSON to a csv.
Do you have any script prepared in python, shell script ?
The JSON I have looks like this

cbq> select Col1 , Col2, Col3 , Col4, Col5 from test WHERE type=“type1” and CLOCK_STR(“2015-07-16”);
{
“requestID”: “d09be01d-2f11-47f2-90bc-fbed6c3a6704”,
“signature”: {
“Col1”: “json”,
“Col2”: “json”,
“Col3”: “json”,
“Col4”: “json”,
“Col5”: “json”
},
“results”: [
{
“Col1”: “Value1”,
“Col2”: Val A",
“Col3”: ValA-12",
“Col4”: 4,
“Col5”: “user1”
},
{
“Col1”: “Value1”,
“Col2”: Val E",
“Col3”: ValE-1",
“Col4”: 1,
“Col5”: “user20”
},
{
“Col1”: “Value1”,
“Col2”: Val C",
“Col3”: ValC-3",
“Col4”: 1,
“Col5”: “user1”
},
{
“Col1”: “Value1”,
“Col2”: Val F",
“Col3”: ValF-20",
“Col4”: 1,
“Col5”: “user20”
},
{
“Col1”: “Value1”,
“Col2”: Val B",
“Col3”: ValB-1",
“Col4”: 5,
“Col5”: “user1”
},
{
“Col1”: “Value1”,
“Col2”: Val D",
“Col3”: ValD-12",
“Col4”: 4,
“Col5”: “user20”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “317.825051ms”,
“executionTime”: “317.62925ms”,
“resultCount”: 6,
“resultSize”: 1158
}
}


#4

Hi @couchbwiss,

Here is a sampling of tools from google search: https://www.google.com/?gws_rd=ssl#q=script+to+convert+json+to+csv