CockroachDB and Google Data Studio

Ever wanted to connect CockroachDB to Google Data Studio to visualize those data points and KPI's you have been storing in your database then this blog post is for you. We will walkthrough step by step connecting a CockroachDB  cluster to Google Data Studio,

Create a CockroachDB Cluster

1. Visit https://cockroachlabs.cloud/ and sign up for a free serverless cluster
2. After the cluster has been created you will need to create a SQL user

3. Note the username and password as they will be used in the connection string for Gogle Data Studio
4. The last thing to do here is download the CA cert for TLS encryption. This can be done by clicking on the connect button and expanding the CA cert section

Creating a Data Source in Google Studio

1. Go to https://datastudio.google.com/ and click on Data Source tab
2. Click the Create Button and then select new Data Source.You will need to search for the postgres connector

3. You will be presented with the connection screen as below

4. Lets discuss each of the paremeters and where you can get details for each. At this point its usefull to go back to your cockroachDB cluster and click on the connect button. I like then to select parameters only in the language option to get the details you will need next

5. Lets discuss each of the settings

• Host Name or IP - This is the hostname or IP of your cockroachDB serverless cluster in my example this is free-tier.gcp-us-central1.cockroachlabs.cloud
• Port - this is the port that the connection will be made on the default port for CockroachDB is 26257
• Database - This is the Database you want to connect to in my case the Database is defaultdb however as this is a serverless cluster you also need to include the Fully Qualified DB name which is clustername.dbname in my example this would be hugest-donkey-909.defaultdb
• Username - The Username of the user you in my example is dan
• Password - The Password for the user you have selected

6. The last thing to do here is click on enable SSL and select the CA cert that you downloaded in the previous steps.
7. After you have clicked on Authenticate you will be presented with a list of tables you can select to use in your Data source or there is an option for you to write a custom SQL query for more advanced analysis.

You now have a fully configured connection in to a CockroachDB cluster from Google Data Studo. You can use this Data source via the Explorer tab for exploritory analysis or create polished and proffesional reports from the reports tab.

Stay tuned for a follow up on how to create reports in Google Data Studio