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.
The following procedure explains the steps for making a remote connection to an instance and running a query against the user's default cluster.
- Create a database role in the Yellowbrick Manager Access
Control tab.
For example:
This example creates a role with two member users,
loader1
andybsql10
. 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
andsys.role
system views for detailed information about database users and roles. - 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.
- 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.
- 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:
- Test the connection and browse the database as the user
loader1
:
- Now use
ybsql
to connect to the same instance as userybsql10
. 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.
$ ybsql -h *************.yellowbrickcloud.com --cluster "small-default-cluster" premdb ybsql10
Password for user ybsql10:
...
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.