Configuring DB connectors - Clickhouse

OLAP is used for multidimensional data analysis. OLAP enables users to obtain online answers to ad-hoc questions in a fairly rapid amount of time, except when the data are stored in very large databases. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting etc. ClickHouse is a fast open source OLAP DBMS that can come to use for such applications. So let’s see how we can integrate ClickhouseDB in DronaHQ.

Add a DB Connector

To add a third-party DB connectors, under Studio > Connectors, click (+) Connector.

image3

Configure Database Category

  • Enter the connector name.

  • Provide the database url to your database.
  • Authenticate the database connection by providing username and password.
  • You can also enable further action to Connect using SSL and Whitelisting IP.
  • After configuring all the details, test connection and ensure its success. You can now click Continue to Finish and Save your configuration.

Add a query to the Database connector

The DB connector is now added to the Connectors list.

image5

Click add a query to go to query editor

Here you can add your queries - simple as well as complex ones. To the right-hand side of the screen, you can see the list of tables from the database. You can expand the tables to view the fields from the table.

You can provide a name to the query using Enter a Service name. Add your query and click Run or press Ctrl + enter to run the query. The rows returned from the table are shown in the Response section.

You can also add dynamic values using the Variables. You can add the variable by simply adding the variables in double curly brackets. You can see the Variables listed out in the sidebar on the right corner.

You can view the queries that you saved under your respective connectors.

image4

Use connector to fetch and display data

Now lets see , how can we use our connector to fetch data and display it.

  • From controls place a tablegrid control on screen and go to the data option.
  • From the connectors option, select our clickhouse database connector.
  • Select the select query and after continue, select the columns you want to use.

image7

  • You can change the name of the connector for easy understanding, so let’s name it READ.

After this, you can reorder, toggle visibility of columns and filter and sort data fetched from the connector.

  • Click on Test and finish and preview the app.

Using the DB Connector is similar to using the sheets with your apps. You can make use of the Queries to fetch data as well bind data such that you can undertake the insert, update, delete actions as well.

You can refer to this link here to understand how to use the API services.