Product Documentation

Appearance

Connecting Tools and Applications ​

You can connect ybtools, such as ybsql and ybload, and third-party applications to your instances. Users can connect via JDBC and ODBC applications, such as DBeaver and Qlikview, using standard Yellowbrick and PostgreSQL drivers.

For more details, see Setting Up a Database Connection.

Tip: When you set up database roles and users, you can set a default cluster so that sessions from tools and applications always access that cluster.

The following procedure explains the steps for making a remote connection to an instance and running a query against the user's default cluster.

To connect to an instance:

  1. Create a database role in the Yellowbrick Manager Access Control tab.

For example:

This example creates a role with two member users, loader1 and ybsql10. The role grants them privileges to run bulk loads, create databases, and alter databases. (For more information about creating users and roles, see 2. Create a New Role and Some Users.)

You can drill down into the Databases tab (or use the Query Editor directly) to grant lower-level privileges on specific objects to users and roles as needed. For example:

Query the sys.user and sys.role system views for detailed information about database users and roles.

  1. Set the default cluster for specific users; you cannot set the default cluster at the role level. For example, in the Query Editor run the following command for each user:
alter user ybsql10 set default_cluster "small-default-cluster";

When this user logs in, all queries will be run on the specified cluster.

  1. Get the instance connection details from the Yellowbrick Manager Dashboard. Make sure you select the correct instance before copying the Host/Port connection string to the clipboard.

  1. Open the Connection Settings in your application and create a new connection. Specify the connection string you copied as the host (port 5432), the database you want to connect to, and the database username and password you created.

For example, in DBeaver:

  1. Test the connection and browse the database as the user loader1:

  1. Now use ybsql to connect to the same instance as user ybsql10. Use the same connection string that you copied earlier for the -h) value:
$ ybsql -h *************.************.com premdb ybsql10
Password for user ybsql10: 
Null display is "[NULL]".
Expanded display is used automatically.
ybsql (6.1.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=> \d
No relations found.
premdb=> set search_path to premdb;
SET
premdb=> \d
                      List of relations
 Schema |     Name      | Type  |           Owner            
--------+---------------+-------+----------------------------
 premdb | awayteam      | table | bob.atkins@tottenhamfc.com
 premdb | hometeam      | table | bob.atkins@tottenhamfc.com
 premdb | match         | table | bob.atkins@tottenhamfc.com
 premdb | newmatchstats | table | bob.atkins@tottenhamfc.com
 premdb | season        | table | bob.atkins@tottenhamfc.com
 premdb | team          | table | bob.atkins@tottenhamfc.com
(6 rows)
premdb=> select current_cluster();
    current_cluster           
-----------------------
 small-default-cluster
(1 row)

--cluster Option for ybtools Connections ​

You can use the --cluster option to direct a ybtools client connection to a specific cluster for the instance (where your instance is defined by the host name or -h option). Alternatively, you can specify the YBCLUSTER environment variable.

For example:

$ ybsql -h *************.yellowbrickcloud.com --cluster "small-default-cluster" premdb ybsql10
Password for user ybsql10: 
...

Important: Every new client connection will use the default cluster that is set for the instance unless the connection properties define the cluster to use or you have already set the default cluster for the user explicitly (see ALTER USER SET DEFAULT_CLUSTER).

This behavior applies to both initial ybsql connections and subsequent connections with \c. For example, changing to a different database with \c does not "keep" the cluster from the previous connection.

Parent topic:Getting Started with Yellowbrick