Introduction
SingleStore (formerly MemSQL) is a proprietary, cloud-native database designed for data-intensive applications. It includes a distributed, relational, SQL database management system that features ANSI SQL support. This document describes the connectivity steps to integrate SingleStore RDMS as a Denodo data source.
SingleStore supports two kinds of table architectures: Rowstore and Columnstore. From the Denodo perspective they are the same since they are both seen as relational 2-dimensional tables.
SingleStore configuration and required information
SingleStore is a cloud database so it can be accessed SingleStore portal: https://portal.singlestore.com/
- If you do not have an existing workspace you must create one using the (+) button close to my_workspace, it will be automatically deployed. If you already have an existing workspace but it is not running click the “Resume” button.
- By selecting the workspace, in the Overview page you can create Databases and attach/detach them to the workspace. The Database you are going to connect to Denodo must be attached: click on the three dots under your workspace and click Detach/Attach Database, then select the database you want to attach and Save.
- For testing purposes, we will have one Rowstore and one Columnstore table. New tables can be created using the SQL Editor with the following syntax: CREATE TABLE. In SingleStore Cloud you can make use of the Sample Database (MarTech Application) that can be selected when creating a new Workspace.
- Set the admin password for the workspace group in my_workspace > Access, click the Reset button and set the new password. Save these credentials to enter when creating the data source in Denodo.
- You can obtain the host and port in the following SingleStore section:
- Click on Connect > CLI Client on the Overview page of your workspace.
- Select SingleStore Endpoints on the CLI Client tab to obtain the Endpoint URI string.
Create a JDBC data source in Denodo
- Download the SingleStore JDBC driver (singlestore-jdbc-client-x.y.z.jar) from SingleStore Docs or directly from the GitHub page.
- Import the driver in Virtual Data Port using the Web Design Studio or the Virtual DataPort Administration Tool. Go to File > Extension Management > Libraries > Import.
Select jdbc_other as Resource type and insert the name of the .jar file in the Custom version field to identify the new driver.
Click on the Add button and select the file you downloaded.
If you’re planning to connect with SSL you can download the server .pem certificate from the SingleStore Connect screen and that can be added to your Denodo truststore. This section of the user guide explains how to import the certificate: Importing the Certificates of Data Sources (SSL Connections).
- In the Web Design Studio or VDP Administration Tool:
- Create a new JDBC Data Source.
- Select Generic as Adapter.
- Using the host and port you’ve collected on SingleStore, configure the Database URL with the following format:
jdbc:singlestore://<host_string>:<port>/ - Insert and the password you set on SingleStore for the admin user.
- In the Advanced tab: select the Custom Version name you set in step 2 (usually appears in the bottom).
Click on Test Connection to validate the parameters. If there is an error, review the connection parameters.
Type “com.singlestore.jdbc.Driver” in the Driver class text box if it does not appear automatically.
- Click on the Create Base View button, expand the database tree and select the SingleStore table you want to virtualize in Denodo.
- Rename the Base Views you just created and execute them to test that they are working as expected.
References
Connect with Denodo · SingleStore Documentation
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.