Announcing the Couchbase Query Workbench DP1 - now DP1.0.1

SQL++
1

I am thrilled to announce the availability of the Couchbase Query Workbench (developer preview) with the launch of Couchbase 4.0 GA. Back in June, when we launched Couchbase 4.0 Beta at Connect 2015 in Levi’s Stadium, the most frequent request from customers visiting the N1QL demonstration was for a GUI to create and run N1QL queries. Now that request is answered, and you can download and work with it.

Here is the screenshot of the Query Workbench which exposes three main areas: Metadata, N1QL editor and Query results.

Screen Shot 2015-10-06 at 10.22.09 AM.png1113×968 216 KB

Couchbase 4.0 includes the cbq command-line query tool. While I love command-line tools, graphical user interfaces can provide several advantages, such as easier editing of long, multiline queries, syntax coloring and auto-completion, and flexible, interactive output presentation, including raw JSON, tabular, and tree-based formats, and saving JSON results to files.

The Couchbase Query Workbench also includes an advanced feature not yet available in cbq: the describe command. Describe samples the documents in a bucket to create a description of common document structure(s), i.e., a schema or set of schemas for the documents in a bucket. This can give people a better understanding of the various fields, to help them write better queries, and also highlight differences in document structure that might indicate data cleanliness issues. This describe command may be issued as a query (e.g., “describe beer-sample limit 500”), and it is used behind the scenes to support the metadata explorer panel on the left side of the GUI.

The Query Workbench is a separately downloadable standalone tool that serves up the GUI in your web browser, and gets results from your existing query nodes on your Couchbase installation. It works with Couchbase 4.0 GA or later (but not 4.0 Beta).

Please give the Query Workbench Developer Preview a try it out and give me your feedback!

Version 1.0.2 is now available from the downloads page. Just click on the “Tools” tab.

Downloads Page

Please let me know if you have questions or comments!

Eben M. Haber
Senior Software Engineer & User Advocate

3 Likes
3

Cool bean Eben, Its working for me.

4

Fantastic! And support for SASL buckets, too - bonus points.

5

Works fine for queries, but describe doesn’t work:

DESCRIBE SELECT COUNT(*) from bucket where dataItem > 0;

returns

[
{
“code”: 3000,
“msg”: “syntax error - at DESCRIBE”
}
]

6

Hi unhuman,

Right now “describe” only works for buckets, and the only option is the sample size. I.e.,

describe bucket name [limit sample_size]

We would like to be able to describe queries as well, but that will take a bit longer. :smile:

Thanks for giving it a try!

-Eben

7

One of my coworkers said “explain” and that seems to work:

EXPLAIN SELECT COUNT(*) from bucket where dataItem > 0;

Kicks back what might be useful output.

I’ve always known explain and describe to be synonyms… Not so here.

8

“Explain” is useful to see how a query would be executed, e.g., what indexes will be used. “Describe” is used to understand the structure of data in a bucket, which is necessary because Couchbase doesn’t have formal schemas.

9

I like Query Workbench bravo !!!
4 points I like to see

  1. alignment of the columns in table view and should be easy
  2. add option for full flatted table
  3. remove the row limit we can limit in the SQL
  4. add 2 drop downs have user input for host ip and bucket

Very good that CB is making investment in a quality N1QL tool is great Bravo

George Leon from Karpathos Greece

10

Hi George, thank you for your comments!

Can you explain more what you mean in points #1 and #2?

One issue with creating a table view is that when you have a query like,

select * from beer-sample;

The JSON that is produced is an array of nested objects, where each outer object has the name of the source bucket. E.g.:

{
“beer-sample”: {
“abv”: 9.8,
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep, golden, rich malt flavor huge citrus, fruity grassy, ethanol sweetness aroma with a profound bitterness, yet balanced malt back bone with grapefruit, mellow citric overtones. Dry hopped three times in the fermenter. Brewed with over 65 lbs of hops for 300 gallons of beer. The beer to bring world peace and end the war. Bronze Medal - 2006 Imperial IPA Festival at the Bistro in Hayward, California.”,
“ibu”: 0,
“name”: “Double Trouble IPA”,
“srm”: 0,
“style”: “Imperial or Double India Pale Ale”,
“type”: “beer”,
“upc”: 0,
“updated”: “2010-07-22 20:00:20”
}
}

So in the array of results, each object has only one field, called “beer-sample”, whose value is a nested object with the actual results. This doesn’t produce a nice table.

On the other hand, if you write your query as:

select beer-sample.* from beer-sample;

Then the result JSON does not have nested objects, which makes it possible to create a nice even table.

Does that point relate to your comments #1 and #2?

-Eben

11

@eben

Yes that works for me I didn’t know that so yes you support 1 and 2 it is great tool
One thing might be good to have if a query takes long time to allow user to cancel it .
Keep up the good work .

Cheers
G.

12

Version DP 1.0.1 is out with a number of bug fixes, as well as much better performance on multi-node clusters. I updated the links at the beginning of the thread to point to the new version.

2 Likes
13

Hi @eben

I find that when i write a large multi line NQL1 statement the area i write the statement in is small .
Could you allow it to expand in next version ?

Best Regards
G.

14

Hello George, I will work on that.

Can you tell me how big a large query is? How many lines and columns would a typical large query use?

Regards,

-Eben

15

Hi @eben,

For me 15-20 lines but each user will be different .
Thanks
G.

16

Hi eben,

is query workbench supports syntax error. i just quired select * from default whe. but i got results

I would like to see below points: 1.i think you can include minimize for all query panel. it includes @GeorgeLeon point.
2. you can include keys in results

regards,
Arihant

17

Hello Arihant,

The query workbench should show error messages when syntax errors occur.

Also, to include keys in results, you can use the meta syntax. For example, to get all documents from beer-sample along with their keys, you could use the following query:

select meta(beer-sample).id,* from beer-sample;

Regards,

-Eben

18

Hi Eben, My pc connection doesn’t work with a couchbase installed on Linux, thank to tell my why please
set GUI_PORT=:8092
set COUCHBASE_URL=http://10.144.8.35:8092
set USER=admin
set PASS=xxxxxx
Error:
Error with connection to http://10.144.8.35:8092: HTTP error 404 Object Not Found getting “http://10.144.8.35:8092/pools”: {“error”:“not_found”,“reason”:“missing”}

Unable to find a N1QL query service on: http://10.144.8.35:8092

19

The standard port for Couchbase is usually 8091, but in the configuration you showed it looks like you have 8092. Try changing:

http://10.144.8.35:8092

to

http://10.144.8.35:8091

-Eben

20

P.s. Version 1.0.2 is now available on the downloads page:

http://www.couchbase.com/nosql-databases/downloads