{{announcement.body}}
{{announcement.title}}

Simple DataViz With N1QL and Google Sheets

DZone 's Guide to

Simple DataViz With N1QL and Google Sheets

In this article, explore DataViz with N1QL and google Sheets.

· Database Zone ·
Free Resource
Laptop with code on it

Do whatever it takes to present the data to aid analysis and thinking.
— Edward Tufte

How do you create graphs like these if you don’t already have some ready-made cool DataViz tool?


You can run queries to wrangle the data to get the results. Often, you want to present the data visually in pie charts, bubble charts, histograms, and line graphs. If you have Tableau, Cognos, etc., it’s easier to visualize. Otherwise, you’ll have to copy the results into Google Sheets or Excel. Cut and paste of the results to Google Sheets is difficult due to formatting, header-row issues, etc., however, Couchbase Query Workbench makes it easy.

Next to the query results, we have a copy icon that copies the output into a tabular form that you can paste into spreadsheets such as Google Sheets, Excel, etc. This facility is available in both query workbench and analytics query workbench.

Let’s look at some sample data and charts.

  1. Calculate the types of documents and their count.
You might also want to read:  How to Make a Killer Data Dashboard With Google Sheets

Here’s the query on the travel-sample dataset shipped with Couchbase.

SQL
xxxxxxxxxx
1
12
 
1
SELECT type,
2
       COUNT(1) typecount
3
FROM `travel-sample`
4
GROUP BY type
5
ORDER BY typecount
6
 
          
7
type       typecount
8
"airline"       187
9
"hotel"         917
10
"airport"      1968
11
"landmark"     4495
12
"route"       24024

 

Here’s the piechart created for the data. This can be easily customized for color, data display, various labels, and legends.

 


See the GIF below to see how to get the results to Google Sheets to get the charts in a second.  


2. Task: Find the top 10 cities with the most number of hotels.

SQL
xxxxxxxxxx
1
21
 
1
SELECT country,
2
       city,
3
       COUNT(1) hotelcount
4
FROM `travel-sample`
5
WHERE type = 'hotel'
6
GROUP BY country,
7
         city
8
ORDER BY hotelcount DESC
9
LIMIT 10
10
 
11
city                country        hotelcount
12
"San Francisco" "United States"      132
13
"London"        "United Kingdom"      67
14
"Paris"         "France"              64
15
"San Diego"     "United States"       48
16
"Birmingham"    "United Kingdom"      36
17
"Los Angeles"   "United States"       35
18
                "United Kingdom"      23
19
"Santa Monica"  "United States"       14
20
"Malibu"        "United States"       12
21
"Edinburgh"     "United Kingdom"      10

 

The only customization to the histogram here is to add the data labels to show the actual number of hotels.


3. Task: Find the top 5 cities with the most number of hotels with “gardens” in the review.

SQL
xxxxxxxxxx
1
16
 
1
SELECT city,
2
       COUNT(1) hotelcount
3
FROM `travel-sample`
4
WHERE type = 'hotel'
5
    AND ANY r IN reviews SATISFIES search(r.content, "garden") END
6
GROUP BY city
7
ORDER BY hotelcount DESC
8
LIMIT 5;
9
 
          
10
 
          
11
city        hotelcount
12
"Paris"        10
13
                5
14
"San Francisco" 5
15
"San Diego"     4
16
"Edinburgh"     3

Simply choose the “Doughnut piechart” and add the labels.

 


6. Bubble charts require you to create the percentage value for each row. The window function, RATIO_TO_REPORT(), helps you to do that easily. Once you have your query, create a bubble.

SQL
xxxxxxxxxx
1
22
 
1
SELECT country, city, 
2
       SUM(ARRAY_SUM(reviews[*].ratings[*].Location)) / COUNT(1) city_avglocation,
3
       SUM(ARRAY_SUM(reviews[*].ratings[*].Service)) / COUNT(1) city_avgservice,
4
       RATIO_TO_REPORT(COUNT(1)) OVER(partition by country) * 100 ratio_val,
5
       (TO_STR(ROUND(RATIO_TO_REPORT(COUNT(1)) OVER(partition by country) * 100, 0)) || "%" ) AS ratio_percent 
6
FROM `travel-sample`
7
WHERE type = 'hotel'
8
group by country, city
9
order by ratio_val desc, city_avgservice desc, city_avgvalue desc
10
LIMIT 10
11
 
12
city           city_avglocation    city_avgservice country ratio_percent ratio_val
13
"Paris"           13.75 15.421875 "France" "46%" 45.714285714285715
14
"San Francisco" 15.583333333333334 16.71969696969697 "United States" "37%" 36.56509695290859
15
"London" 11.567164179104477 15.746268656716419 "United Kingdom" "16%" 16.105769230769234
16
"San Diego" 15.583333333333334 16.729166666666668 "United States" "13%" 13.29639889196676
17
"Los Angeles" 11.971428571428572 12.628571428571428 "United States" "10%" 9.695290858725762
18
"Birmingham" 14.666666666666666 18.083333333333332 "United Kingdom" "9%" 8.653846153846153
19
"Avignon" 10.375 13.375 "France" "6%" 5.714285714285714
20
 16.91304347826087 17.782608695652176 "United Kingdom" "6%" 5.528846153846153
21
"Chamonix-Mont-Blanc" 18 29.142857142857142 "France" "5%" 5
22
"Nice" 11.571428571428571 15 "France" "5%" 5



The bubble chart shows the average rating by location, etc. The size of the bubble shows the percentage of hotels represented within the respective country.

The GIF below shows how this bubble chart is created.


7. Creating geo charts is pretty easy in Google charts since it recognizes the countries and cities. You don’t need to deal with latlongs.

SQL
xxxxxxxxxx
1
 
1
Select country, count(1) num_hotels
2
from `travel-sample`
3
where type = 'airline'
4
 
          
5
country           num_hotels
6
"United States"     127
7
"United Kingdom"     39
8
"France"      21


With the data above, simply paste into a Google sheet and then create a geo chart.


8. You can also create a geo chart with markers (proportional bubbles), again, using the RATIO_TO_REPORT() window functions.

SQL
xxxxxxxxxx
1
13
 
1
SELECT country,
2
       COUNT(1) AS num_hotels,
3
       (TOSTR(ROUND(RATIO_TO_REPORT(COUNT(1)) OVER () * 100,0)) || "%") AS hotels_percent 
4
FROM `travel-sample`
5
WHERE type = 'hotel'
6
GROUP BY country;
7
 
          
8
 
          
9
 
          
10
country           hotels_percent      num_hotels
11
"United Kingdom"  "45%"                 416
12
"France"          "15%"                 140
13
"United States"   "39%"                 361


 


You can also customize the charts to the region, for example, the United States.


Finally, the query to generate the first graphic of this blog:

SQL
xxxxxxxxxx
1
12
 
1
SELECT country, city,
2
       SUM(ARRAY_SUM(reviews[*].ratings[*].Location)) / COUNT(1) city_avglocation,
3
       SUM(ARRAY_SUM(reviews[*].ratings[*].Service)) / COUNT(1) city_avgservice,
4
       SUM(ARRAY_SUM(reviews[*].ratings[*].Overall)) / COUNT(1) city_avgoverall,
5
       SUM(ARRAY_SUM(reviews[*].ratings[*].Rooms)) / COUNT(1) city_avgrooms,
6
       SUM(ARRAY_SUM(reviews[*].ratings[*].`Value`)) / COUNT(1) city_avgvalue
7
FROM `travel-sample`
8
WHERE type = 'hotel'
9
group by country, city
10
order by city_avgservice desc, city_avgvalue desc
11
LIMIT 50

You can also customize the charts to the region, for example, the United States.

 Finally, the query to generate the first graphic of this blog:

SQL
xxxxxxxxxx
1
11
 
1
SELECT country, city,
2
       SUM(ARRAY_SUM(reviews[*].ratings[*].Location)) / COUNT(1) city_avglocation,
3
       SUM(ARRAY_SUM(reviews[*].ratings[*].Service)) / COUNT(1) city_avgservice,
4
       SUM(ARRAY_SUM(reviews[*].ratings[*].Overall)) / COUNT(1) city_avgoverall,
5
       SUM(ARRAY_SUM(reviews[*].ratings[*].Rooms)) / COUNT(1) city_avgrooms,
6
       SUM(ARRAY_SUM(reviews[*].ratings[*].`Value`)) / COUNT(1) city_avgvalue
7
FROM `travel-sample`
8
WHERE type = 'hotel'
9
group by country, city
10
order by city_avgservice desc, city_avgvalue desc
11
LIMIT 50

 

Thanks for reading!

Further Reading

Visualize Google Sheets Data in Tableau

Data Visualization With Couchbase and Knowi

Topics:
nosql ,couchbase ,json ,query ,datavisualization ,google sheets ,database ,tutorial

Published at DZone with permission of Keshav Murthy , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}