How to publish SQL queries to Atlassian Cloud

Description

Atlassian Cloud (formerly OnDemand) restricts the ability to install most apps. So how can you still implement concepts like how to use Confluence for business intelligence and reporting? Or, perhaps you are not allowed to install apps on your own corporate Confluence installation and you would still like to publish SQL or other external data (like Excel data). Now, there is a solution for both of these use cases.

This article discusses this in terms of Cloud, but it applies equally to other restricted environments. While not as dynamic as using the apps directly, it only takes a few more steps to implement and skills and knowledge gained can also be applied in the other context. 

This is how the Products page is produced. Bamboo automation publishes the page once a day.

Steps

Some of the elements here are optional depending on what you want to do and how automated you want to be. This section outlines the most automatic and rich example.

You need:

1. An instance of Confluence (download edition) installed and running. You need administrator rights.  A $10 starter license is sufficient and an instance can be setup in minutes.
2. If not already installed, install the following apps:
   
   • SQL for Confluence - version 4.5 or above
   • Run Self-Service Reports for Confluence (optional) - provides a parameterized page for testing
   • Run CLI actions in Confluence - makes it easy to store the page on the Cloud instance. After installation, configure a profile to get to Cloud
3. Create a page to test your query page just like you would normally.

4. Create a publish page using the Run, CLI and SQL Query macros. The key is that the SQL Query produces wiki markup because of output=unrenderedWiki (this is what gets published to Cloud).

   Wiki markup for publish page

   {run:titleRun=Publish
   |autoRun=true
   |requestPrefix
   |replace=profile:confluence:Profile:select::confluence-local::confluence-cloud}
   
   {cli:profile=$profile|macros=true}
   
   -a storePage --title "Project Summary" --space info --file - --minor --comment "Updated from database"
   
   @DATA:
   \{\{toc:type=flat|separator=pipe|minLevel=3|maxLevel=3}
   h3. Confluence Apps
   {sql-query:id=CONF|datasource=myDS|output=unrenderedWiki|columnTypes=S,S,S,S,S,S,S,S,C}
   select ...
   {sql-query}
   
   {cli}
   
   {run}

5. Copy in your SQL from your test page.
6. Verify your local published page matches your expectation.
7. Once satisfied, select the Cloud instance profile and press Publish. Refer to How to automate page rendering in Confluence.