Product Documentation

Appearance

4. Query with the Large Cluster ​

In the context of this tutorial, you can assume that queries are being run by many users (high concurrency). Therefore, the large cluster is suitable for this workload. When no queries are coming in, or a much lighter workload is running, the large cluster can be suspended and the small cluster can be used instead.

  1. In the Query Editor, run as the initial login user and grant table privileges to the analyst role so that members of that role can run queries. The analyst role requires SELECT privilege on all tables in the premdb schema for the premdb database.

For example:

  1. Log into your running instance via ybsql as user ybtools10 and check that the current cluster is the large-default-cluster (as defined in 2. Create a New Role and Some Users). Replace the host name in the following ybsql command with the correct host name for your instance:
% ybsql -h ********************** -U ybtools10 -W -d premdb
Password for user ybtools10: 
ybsql (6.0.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type:  \h for help with SQL commands
       \? for help with ybsql commands
       \g or terminate with semicolon to execute query
       \q to quit
premdb=> select current_cluster();
    current_cluster    
-----------------------
 large-default-cluster
(1 row)
premdb=> select default_cluster();
    default_cluster    
-----------------------
 large-default-cluster
(1 row)
  1. Set the search path if it is not already set:
premdb=> set search_path to public, premdb;
SET
  1. As the ybtools10 user, run any query on the premdb tables. For example:
premdb=> select t1.season_name, t1.winners, homegoals+awaygoals as total 
from 
(select season_name, winners, sum(substr(ftscore,1,1)::int) homegoals 
from season, match
where season.seasonid=match.seasonid 
group by season_name, winners) t1, 
(select season_name, winners, sum(substr(ftscore,3,1)::int) awaygoals 
from season, match
where season.seasonid=match.seasonid 
group by season_name, winners) t2 
where t1.season_name=t2.season_name 
order by 1,2;
 season_name |      winners      | total 
-------------+-------------------+-------
 1992-1993   | Manchester United |  1222
 1993-1994   | Manchester United |  1195
 1994-1995   | Blackburn Rovers  |  1195
 1995-1996   | Manchester United |   988
 ...
(22 rows)
  1. As user ybapps10, set up a connection to the same database from a third-party tool, such as DBeaver. Download and upgrade DBeaver to the latest version, then create a new database connection (Database > New Database Connection > Yellowbrick).

For example:

  1. View the database in DBeaver:

Now you can run queries on these tables as user ybapps10.

  1. Verify that both users were connected to their designated default cluster when they ran queries. For example:
premdb=# select distinct username, cluster_name from sys.log_query where username like 'yb%10';
 username  |     cluster_name       
-----------+-----------------------
 ybtools10 | large-default-cluster
 ybapps10  | large-default-cluster
(2 rows)

Parent topic:Tutorial: Creating and Using Clusters