N1QL - Getting Started

SQL++
1

I’ve got Couchbase 2.5.1 running on Mac OSX.

Using latest version of Couchnode, I am able to get and set objects no problem. However, I am not able to use N1QL query. every request comes back with “Not Found”.

For instance, I have a simple “default” bucket with an object as {_id: 1, username: myname, password:123}.

From Node, I run a query like:

select * from default ==> "not found"
select * from default where username = ‘myname’ ==> “not found”

I get a similar result when I run the same query from the command line.

I’m sure I am missing something simple and significant in the setup of Couchbase to use N1QL, but I’m unsure what it is, and any help would be appreciated.

Thanks.
Chris

2

Hi Chris,

Can you please be more specific, and are you using the download instructions?

First, you should verify your N1QL installation by running start_tutorial.sh and then opening your local tutorial in your browser.

After that, you would need several steps:

  • run Couchbase server
  • run cbq-engine and point it to Couchbase server
  • run cbq

Can you follow these steps (url’s and ports are specified in the instructions), and then post the results you are getting for each step?

Thanks.

3

Hi Gerald,

Thanks for the response, Gerald. You’re steps got me in the right direction and it’s working properly now. I wasn’t sure exactly what to make of the instructions that came with the Developer preview. It wasn’t clear for me if they were specific to the tutorial, or if they were the instructions on running the N1QL engine in general.

Where I was going wrong was running cbq-engine from the terminal, thinking it would be a long-running process, then in the same terminal window was running cbq. Also, I was attempting to run cbq against port 8091, not 8093. I now have everything working.

For those just starting with N1QL and NodeJS, here are the steps I took:

  1. Ensure Couchbase is installed and runs properly (obviously), and that you can connect through Couchnode properly.

  2. Download the Developer Preview 3 of N1QL, unpack it and put it in whatever directory you like

  3. Open Terminal or CMD window, CD into the directory with the developer preview, run cbq-engine with: ./cbq-engine -couchbase http://:8091/ **This terminal window must remain open, unless using a utility or command to keep it running

  4. Back in Node, ensure you add the following to your connection object when setting up the Couchbase Client:

queryhosts":“127.0.0.1:8093”

For example, the entire connection object might look like:

var defaultConnection = { "host": "127.0.0.1:8091" "bucket": "default", "operationTimeout":5000, "queryhosts":"127.0.0.1:8093" };

Now you can query your couchbase client like:

client.query(“select * from default where username = ‘myusername’”)

5

am getting below error when am trying to exicute below query

cbq> SELECT * FROM beer-sample WHERE city = 'San Francisco’
14:59:05.012828 ERROR: Post =http://ec2-54-163-253-172.compute-1.amazonaws.com:8093/query: unsupported protocol scheme “” – main.HandleInteractiveMode() at interactive.go:54

Request you to please let me know , Where am wrong.

Am using
couchbase 3.0 Enterprise , N1QL Developer Preview 3.
What are the steps to connect to bucket.?

6

Hi ashdatavail,

can you please show how you are starting cbq from the command-line? It should look something like:
./cbq --engine=“http://ec2-54-163-253-172.compute-1.amazonaws.com:8093”

Also please let me know which OS and shell you are running on? (E.g. ubuntu 12.04, bash shell).

Finally, can you please confirm which installation of N1QL Developer Preview 3 you are running? (E.g. Red Hat 64bit).

With this info, we’ll be able to diagnose the issue further.

Thanks,
Colm.

7

Thanks for your reply…

My issue got resolve.

I have one quick question if could help me with this.

how to query below data , If my profile is stored in plain array.

[
123, - > country Code
865, -> Telco
456789079 -> Mobilenumber
]

If i want to select Telco count by country wise , then how to use this array in query.

select doc[0] from ifa limit 1 ;

above query is not working , can you please let me know how to solve this.

or else i will have to update my all profiles with at list one key , eg
{
“u”: [
123,
865,
456789097
]
}

Then i can query like select u[0] from ifa limit 1 ;

SO , DO i Need to ADD “u” for all profiles which are already exist

Regards,
Ashwini Ahire

8

Hi Ashwini, can you send the output from the following queries:

SELECT ifa FROM ifa limit 1;
SELECT ifa[0] FROM ifa limit 1;

Just to note, the following is not legal JSON:
{ [ 123, 865, 456789097 ] }

Trying to parse that as JSON (using http://json.parser.online.fr/) will give errors.

This is legal (acceptable) JSON:
{ “U”: [ 123, 865, 456789097 ] }

So the answer to your question may be Yes, it is necessary to add a name for your profile arrays.

But please try running the above two n1ql queries to see what comes back.

Regards,
Colm

9

Do note however that this is legal JSON:
[123, 456, 789]
as is
123456
and even
null

In the long past, there were some parsers that would not consider it legal JSON unless it was in an object, but this has been clarified in updates on the standard. I think the (hilariously named) ECMA-404 is the right authority these days though it builds on the IETF RFCs and what is at json.org.

10

Continuing the discussion from N1QL - Getting Started:

Thanks for the note; I’ve verified that the query “SELECT ifa[0] FROM ifa” works when the document is of the format: [123, 456, 789]

Ashwini, if this is still an issue for you please send on the output of the query “SELECT ifa FROM ifa LIMIT 10”. This will show us what your data looks like and help diagnose the issue.

11

I understand we need to run cbq-engine and point it to Couchbase server, in order to get the N1QL working.

  1. Why is this cbq-engine not part of Couchbase server ?
  2. Also in production, do we need to run cbq-engine on all nodes in the cluster ?
12

I fail to understand where should I run cbq-engine and where to run cbq.
Let’s say I have a remote ubuntu couchbase server and osx local machine.
Is this what I should do to connect my local machine to run interactive queries (like the tutorial) in the remote server through my local machine?

  1. On the local machine (from the terminal) run

    ./cbq-engine -couchbase http://{server_ip}:8091

  2. On the local machine (from another terminal) run:

    ./cbq -engine=“http://localhost:8093/”

  3. run a select like: select * from bucket_name limit 2

Is this scenario should enable me to query my remote couchbase?
Should I create any view or index before I can run the query of bullet #3?

Thanks

13

In Beta and production, you will not need to run cbq-engine separately. It will be integrated with the server and the cluster.

14

@gluz,

You should be able to run queries without creating an index. The index is only to speed up performance.

15

Which version of Cocuhbase server has embeded/integrated CBQ Engine ? When will this be available ?

16

Next major release of Couchbase Server.