How To Connect ClickHouse To Looker

Although Looker can not natively connect with ClickHouse, HTTPs connection is a way to integrate it seamlessly without any additional setup effort.

1. What is Looker?

Looker, now part of Google Cloud, is a cloud-based business intelligence (BI) platform designed to explore, share, and analyze data. The solution helps businesses to capture and analyze data from multiple sources and make data-driven decisions. It features extensive dashboard capabilities that can handle most companies' data demands. It also requires a modeling layer to store and apply all your business logic to the raw data. This process requires an upfront definition using their own language, LookML, which will take considerable time to master. We have written an article introducing Looker.

2. Connect ClickHouse to Looker

Looker supports ClickHouse version 0.2 and higher.

1. Encrypting network traffic

Looker strongly recommends encrypting network traffic between the Looker application and your database.

To enable SSL encryption on the server side, see the ClickHouse documentation.

2. Users and security

First, configure your Looker user on the ClickHouse server. ClickHouse database users are not created with the traditional CREATE USER command. Follow the ClickHouse Access Rights document to configure the user's section in the users.xml file. Here is a basic example:

<!-- Users and ACL. -->
<users>
    <looker>
        <password>CHANGEIT</password>
        <networks incl="networks" />
        <profile>default</profile>
        <quota>default</quota>
    </looker>

    <web>
        <password></password>
        <networks incl="networks" />
        <profile>web</profile>
        <quota>default</quota>
        <allow_databases>
           <database>test</database>
        </allow_databases>
        <allow_dictionaries>
           <dictionary>test</dictionary>
        </allow_dictionaries>
    </web>
</users>

Also within this file, configure the appropriate database access:

<allow_databases>
    <database>database_1</database>
    <database>database_2</database>
    <database>database_3</database>
</allow_databases>

3. Adding the connection

In the Admin section of Looker, select Connections, and then click Add Connection.

Fill out the connection details :

• Dialect: ClickHouse.

• Host: Reachable hostname.

• Port: Port on which the ClickHouse service is reachable over HTTP(S).

  • By default, HTTP connections will use 8123, and HTTPS will use 8443.

  • Port 9000 and 9440 are by default used by the ClickHouse command line client, but these ports cannot be used by Looker to connect to ClickHouse.

  • Your ClickHouse administrator may have chosen alternate ports with the http_port/https_port settings in the ClickHouse configuration. Ask your ClickHouse admin for the settings appropriate to your local configuration.

• Database: Database name (must be one of the databases allowed in the users.xml file).

• Username: Database username.

• Password: Database password.

• Additional Params: (Optional) Additional JDBC string parameters.

• PDT and Datagroup Maintenance Schedule: ClickHouse does not support PDTs, so this can be ignored.

• SSL: Check to connect to ClickHouse over SSL.

• Verify SSL: (Optional) Check to enforce strict hostname verification on the ClickHouse server. Check this only if you are using an SSL certificate that is signed by a generally trusted Certificate Authority. If you are using a self-signed SSL certificate, leave it unchecked.

3. Looker as a BI tool for ClickHouse

• Looker can combine many data sources and offer a comprehensive perspective of an organization's data, processes, target market, and operations.

• Its interface makes it easy to use its features for each role across the entire organization, from business user to developer.

• Looker offers a data-driven workflow: Team members access real-time data from consolidated sources, ensuring that everyone uses the same data and works on the same page.

• Looker's centrally managed modeling layer encourages the reuse of dimensions and measures while offering distinct degrees of data governance.

• Automated reporting - Looker allows you to schedule emails for daily/weekly/monthly reports or send alerts if data anomalies occur.

Some disadvantages

• A lot of learning is needed to become proficient with the tool and adopt LookML and the model-view methodology for end users.

• Difficult to transition to another BI platform. Moving from Looker to another visualization tool will require additional work to migrate everything that has been defined by LookML.

• Business needs a sizable staff to maximize their potential. Looker is not an all-in-one solution. Users have to model data in dbt before completing the task with Looker.

• It takes longer than other tools to complete various tasks. Since there are no functions for certain tasks, including performing calculations, you will need to develop your own code. You might require technical knowledge to utilize Looker.

4. Build a visualization with Looker

1. Create and run your query.

2. Click the Visualization tab.

3. Select the type of visualization that best displays your data. For more options, click … to the right of the displayed visualization options.

4. Click Edit to configure the visualization option settings, such as naming and arranging chart axes, choosing the position and type of each data series, or modifying the chart color palette.

You can further customize your visualization by specifying which dimensions and measures to include in the visualization. If your data is missing key values, you can tell Looker to fill in those values on the appropriate part of your visualization.

__________________________________________________________________________________

Rocket.BI: A Better Alternative BI For ClickHouse

All this is to say that Looker is great, but not for everyone. There are plenty of other great data visualization tools out there, such as Rocket.BI. Rocket.BI is a powerful business intelligence tool for data discovery, exploration, and visualization. Since is natively integrated with ClickHouse, now you can easily perform BI for ClickHouse within some simple steps. Discover more here

Turn ClickHouse data into beautiful charts and dashboards

• Build dashboards & reports of ClickHouse that auto-update with the latest data

• Get fast ClickHouse reports performance with auto-scheduled refreshes

• No scripting or programming knowledge needed

• Wide selection of graphs and charts from 36 types of visualization to choose from

Share insights with colleagues and external partners

• Share & Collaboration: Share with the rest of your organization by granting access or providing links to them.

• Powerful User Access Control limits a user's access to certain data, defines filters for each Attribute Value, and restricts data access to query and view at the row level.
  

Did you know? Data Insider Rocket.BI is providing free trials with unlimited features, integrations, and visualisation capabilities. Visit https://www.datainsider.co/register to sign up for a free trial and experience full accessibility.