Connecting to your datasource

This article provides you with tips to set up a Bouquet connection to your datasource.

From the Bouquet main screen you can access the project selection menu and edit or create a new one. The connection information are defined directly in the Project edit window.

You need 3 pieces of information to setup a connection:

  • A JDBC URL connection string: since Bouquet uses JDBC framework to interact with your datasource, you will need to figure out the correct URL. You will find some help depending on the database you are using in the section below.

  • A Username
  • A Password

If you don't have access to this information, you should ask the database administrator.

Once you have provided all the info, select  to establish a connection with the recently configured database. If the information you have entered is valid, Bouquet will validate the connection and automatically detect the database type you are using and thus use relevant optimizations.

 

Security matters

Once the connection password is defined, there is no way to retrieve it from Bouquet API.

The DB connection will be made from the Bouquet Server, not the Bouquet UI. Depending on the way you are running Bouquet, this can be your local computer (using Docker for example), or an AWS EC2 instance, or some private hosting service.

If your database is accessible through a public address (with Heroku for example), you'd better use encryption. To do this, add the following options at the end of the JDBC URL:

ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

 

Supported databases and drivers

Bouquet has been designed to support a wide variety of SQL databases. However, in order to enable access you must install the corresponding plugin in the DRIVERS folder (see the Installation guide).

 

A JDBC URL is made of the following pieces:

jdbc:[driver_name]://[host]:[port]/{[database]}{options}

 

Below you will find an overview of the JDBC URL syntax for each type of database:

 

DatabaseJDBC URL
MySQL jdbc:mysql://host:3306/database
Postgresql jdbc:postgresql://host:5432/database
AWS Redshift

jdbc:redshift://endpoint:port/database

Pivotal Greenplum

jdbc:postgresql://host:5432/database

Oracle

jdbc:oracle:thin:@//host:1521/database

Apache DRILL

jdbc:drill:zk=zookeeperlist:2181/drill/drillbits1;schema=hive

SparkSQL  jdbc:hive2://thriftserverip:10000/default
Pivotal HAWQ

jdbc:postgresql://host:5432/database

 

About Apache Drill support

The Drill Driver needs to contact your Zookeeper server to interact with Hive metastore and Drill engine.

For further configuration information refers to the Drill documentation.

 

About AWS Redshift support

AWS recommends using their specific JDBC driver for Redshift. Bouquet has a license right to redistribute it so it is already configured and available. We found that it provides better reliability compared to using a stock PostgreSQL driver.

Also note that network access to Redshift database is usually restricted.

You can find additional information on how to connect to your Redshift database from the AWS support site.

 

About Oracle support

Bouquet does support Oracle database. But due to distribution constraints with the Oracle JDBC driver you will need to perform additional setup. Please contact us by creating a ticket, and we'll be happy to help you directly.

 

Troubleshooting the connection

If the Bouquet Server cannot establish a connection, you will get one of these JDBC error messages listed in the table below.

For each error message, we have included some explanation and possible ways to troubleshoot the connection.

ErrorExplanation
Unable to get driver instance for jdbcUrl=XXXX
The driver_name is not recognized by Bouquet. Either it is incorrect, or not supported by your Bouquet Server. Check the list of supported databases listed above and corresponding drivers. Note that the available drivers depends on your Bouquet installation (Classic, HD or custom)
 
Driver org.postgresql.Driver claims to not accept JDBC URL jdbc:postgresql://127.0.0.1:5432
The trailing / is required for some driver. The correct URL is jdbc:postgresql://127.0.0.1:5432/
unable to connect to jdbc:postgresql://127.0.0.1:5432/
Connection to 127.0.0.1:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
The database is not accessible for the following reasons:
  • you may be using the wrong ip/port
  • the database may be unavailable
  • the Bouquet server may be prohibited from access to the network address by a firewall
unable to connect to jdbc:postgresql://127.0.0.1:5432/
Error setting/closing connection: SocketTimeoutException
The Bouquet server may be prohibited from access to the network address by a firewall.
unable to connect to jdbc:postgresql://127.0.0.1:5432/:
FATAL: password authentication failed for user "user"
Double check your credentials. The database user must at least have access to the database metadata catalog, and to some tables.

If you don't find your error message above,you can easily google the error or ask your database support team to help... no need to be a Bouquet expert.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.