Data types in my SQL query

SquaredUp Cloud
1

Hey folks. Just playing around with SQL analytics (very nice) and am wondering if typical data types can be used i…e if I express something as DATE() will the line graph automatically use it for the timeseries, or do I need to do something else/more?

2

Hey Jimmy!

Great question. SQL Analytics is brand new so apologies this hasn’t “just worked” yet… we can do what you want today with a small JSON edit though. We can jump on a call if you’re not comfortable with that.

Your thought to express types using SQL is spot on, we just don’t have the mechanism to automatically pick that up yet. Coming Soon :tm:

If you’re fine to make a JSON tweak, the missing piece here is “metadata”.

All of our plugins define exactly what type of data is in each column of the data stream i.e. a timestamp, a number, a currency… so we can pick up which visualizations will work and how to label things nicely. That isn’t in place for SQL Analytics though so we’ll need to give it some assistance.

If you can share your query I can give you the short JSON snippet to make it work.

3

Ah cool. I’ll give the JSON stuff a go.

My query is combining CI runs from Azure DevOps along with the same from CircleCI. I want to get a block view using red/green, and a line graph showing each pipeline as a line.

    SELECT
        pipelineName AS Pipeline,
        startTime AS Started,
        duration AS Duration,
        status AS Status 
    FROM
        CircleCIWorkflows 
    UNION
    SELECT
        pipelineName AS Pipeline,
        StartedDate AS Started,
        RunDurationSeconds AS Duration,
        runOutcomeState AS Status 
    FROM
        ADOBuilds
4

Great! Nice use case!

This should do it for you…

"metadata": [
        {
          "name": "Pipeline",
          "shape": "string",
          "role": "label"
        },
        {
          "name": "Started",
          "shape": "date",
          "role": "timestamp"
        },
        {
          "name": "Duration",
          "shape": "seconds",
          "role": "value"
        },
        {
          "name": "Status",
          "shape": "state"
        }
  ]

In summary, this tells the tile what the data looks like and what it can do.

  • Pipeline is a string, and can be the label on a block or line.
  • Started is a date, and can be the timestamp on a line graph.
  • Duration is in seconds, and can be the value on a line graph. This shape also lets us show larger numbers as minutes, hours etc.
  • Status is your health state, so blocks can show colour, tables can show bubbles etc

This goes at the same level in the tile JSON as the sql property.

  1. Open the tile editor
  2. Click code in the top right
  3. Paste the above below the sql query property
  4. Save and close
  5. Victory

image

1 Like
5

HO-LY-HECK! Nailed it. Is there somewhere I can find a reference for these shapes and roles? I can probably do pretty much anything with this now :muscle:

image
image754×318 34.5 KB

5 Likes
6

Looking good @JimmyTheFish!

Analytics uses the same shapes as Custom Data Streams, and those are all documented in our Knowledgebase here. We’ll look to get that article updated with the roles too.