Log in Get started

Overview

Colrows provides a JDBC 4.1 compliant driver that enables developers to connect to Colrows and execute SQL queries on any datasource configured within Colrows, including both SQL and NoSQL sources. The driver allows to run SQL queries from Java applications as well as developer tools like Squirrel and DBeaver.

Key benefits of the driver include:

  • Colrows Data Security: All queries executed through the Colrows JDBC driver are protected by the same data security measures.
  • Run SQL Everywhere: The driver allows for SQL query execution across all datasources configured in Colrows, whether they are SQL, NoSQL, or files.
  • Connection Management: Colrows centrally manages connection pooling for each datasource.
  • Auditing: All data access operations are logged for auditing purposes.
  • Collaboration: Queries executed via the driver are cached in Colrows Query Cache, enabling developers to search and reuse previous queries.

Data Flow

There are two distinct ways in which data flows from datastore to the driver: one in which data flows through Colrows and another in which data flows directly from data store to the driver.

Driver <--> Colrows <--> Datastore

This flow must be used if the underlying datastore does not support SQL. In this case, Colrows acts as SQL query engine. There will be a small latency added due to an additional network hop.

Driver <--> Datastore

This is the classic flow in which most of the database provided JDBC driver work. This is optimal from latency standpoint. While Colrows is not part of the data flow, the driver still enforce the security wherever applicable by using the Colrows Permission API.

Programming Guide

The driver can be used by adding the colrows-jdbc jar to the classpath. The jar can be downloaded from maven central.

Additionally it can also be added as POM dependency as below-


  <dependency>
    <groupid> </groupid>
    <artifactid> </artifactid>
    <version> </version>
  </dependency>

The driver class name is com.colrows.client.jdbc.ColrowsDriver and the connection URL will be- jdbc:colrows:url=http://$HOST:$PORT/api/jdbc;datasource_id=$DATASOURCE_ID;default_schema=$SCHEMA;colrows_user=$USER_NAME;colrows_password=$PASSWORD

As you must have noticed, the URL accepts a group of properties and some of them are mandatory.

  1. datasource_id: every datasource has an alphanumeric unique id in Colrows. It identifies the datasource in Colrows.
  2. default_schema: Default schema to connect to. This is an optional attribute.
  3. colrows_user: Colrows User (not datastore) to be used. As Colrows supports multiple user realms, these user could be an active directly user if the Colrows has been configured with access to active directory. who has logged in to Colrows.
  4. colrows_password: the user password.
  5. fetch_size: an optional property to control record fetch size at datastore level. The default value is 100.

SSL Properties (Optional)

  1. truststore: jks file path
  2. truststore_password: password for jks
  3. keystore_type: format of the truststore file specified by truststore. This needs to be specified if non JKS format keystores are used (i.e. BCFKS). The default value is null.

Client side load balancing

In production-grade environments, it is common to deploy multiple Colrows nodes to ensure high availability. Applications connecting to a Colrows cluster via the Colrows driver can benefit from this setup by enabling load balancing. This feature allows the driver to distribute requests across any of the Colrows nodes. To enable load balancing, configure the 'load-balancing' feature in the driver.

The default load-balancing strategy is org.apache.calcite.avatica.ha.ShuffledRoundRobinLBStrategy

Here are the client-side properties related to load balancing:

  • use_client_side_lb: Default is false
  • lb_urls: List of URLs in comma-separated format (e.g., "URL1,URL2,URL3,...")
  • lb_connection_failover_retries: Default is 3
  • lb_connection_failover_sleep_time : Default is 1000 milliseconds
  • lb_strategy : Specify the fully qualified class name. Options include:
    • org.apache.calcite.avatica.ha.RoundRobinLBStrategy
    • org.apache.calcite.avatica.ha.RandomSelectLBStrategy
    • org.apache.calcite.avatica.ha.ShuffledRoundRobinLBStrategy

Limitations

Colrows is not a full-fledged database and hence the drive does not support everything in the JDBC 4.1 specification. Below is the list of features which are not supported by Colrows JDBC driver-

  • DDL: Colrows is a data consumption system and hence the driver does not support execution of DDL statements on underlying datastores.
  • BLOB: The current implementation is unable to handle BLOB data types; however this will be supported in future releases.
  • CLOB: The current implementation does not support CLOB data types. Upcoming release will have the support for COLB though.