Realtime SQL query for MySQL

[shakerrod]

Hi,
I looked at the implementation of the realtime chart for MongoDB, but I can't figure out how to query MySQL database the same way.

Here is how I tried to recreate the schema for my page views

cube(`Pageview`, {
  sql: `SELECT * FROM mydb.pageview`,
  
  joins: {
    Session: {
      sql: `${CUBE}.session_id = ${Session}.session_id`,
      relationship: `belongsTo`
    },
    
    Website: {
      sql: `${CUBE}.website_id = ${Website}.website_id`,
      relationship: `belongsTo`
    }
  },
  
  measures: {
    count: {
      type: `count`,
      drillMembers: [createdAt]
    }
  },
  
  dimensions: {
    viewId: {
      sql: `view_id`,
      type: `number`,
      primaryKey: true
    },

    referrer: {
      sql: `referrer`,
      type: `string`
    },
    
    url: {
      sql: `url`,
      type: `string`
    },
    
    createdAt: {
      sql: `created_at`,
      type: `time`
    }
  },
  
  dataSource: `default`
});


const derivedTables = (where) => (
  `
      with generator as (
        select 0 as d union all
        select 1 union all select 2 union all select 3 union all
        select 4 union all select 5 union all select 6 union all
        select 7 union all select 8 union all select 9
    ),
    seq as (
      SELECT ( hii.d * 100 + hi.d * 10 + lo.d ) AS num
        FROM generator lo
          , generator hi,
          generator hii
          order by num
          limit 250
    ),
    series as (
    SELECT
      DATE_SUB(now(), INTERVAL seq.num SECOND) AS created_at
    from seq
    ),
    unioned as (
    select
      1 as pageview,
      pageview.created_at
    from mydb.pageview
    WHERE ${where}
    union all
    select
      0,
      series.created_at
    from series
    )
  `
)

const filterSuffix = (from, to) => `mydb.pageview.created_at >= TIMESTAMP(${from}) AND mydb.pageview.created_at <= TIMESTAMP(${to})`

cube(`ViewsBucketed`, {
  sql:
  `
    ${derivedTables(FILTER_PARAMS.ViewsBucketed.time.filter(filterSuffix))}
    select * from unioned
  `,

  refreshKey: {
    sql: `select (FLOOR(UNIX_TIMESTAMP(now())/15))*15`
  },

  measures: {
    pageview: {
      type: `sum`,
      sql: `pageview`
    }
  },

  dimensions: {
    time: {
      sql: `created_at`,
      type: `time`
    },

    quarter: {
      sql:`
        STR_TO_DATE(CONCAT(
          DATE_FORMAT(created_at, "%H:%i"), ":",
          LPAD(CAST((FLOOR(SECOND(created_at)/15))*15 as CHAR), 2, 0)
        ), "%H:%i:%s")`,
      type: `time`
    }
  }
})

So the query looks like this:

 SELECT

        STR_TO_DATE(CONCAT(
          DATE_FORMAT(created_at, "%H:%i"), ":",
          LPAD(CAST((FLOOR(SECOND(created_at)/15))*15 as CHAR), 2, 0)
        ), "%H:%i:%s") `views_bucketed__quarter`, sum(`views_bucketed`.pageview) `views_bucketed__pageview`
    FROM
      (

      with generator as (
        select 0 as d union all
        select 1 union all select 2 union all select 3 union all
        select 4 union all select 5 union all select 6 union all
        select 7 union all select 8 union all select 9
    ),
    seq as (
      SELECT ( hii.d * 100 + hi.d * 10 + lo.d ) AS num
        FROM generator lo
          , generator hi,
          generator hii
          order by num
          limit 250
    ),
    series as (
    SELECT
      DATE_SUB(now(), INTERVAL seq.num SECOND) AS created_at
    from seq
    ),
    unioned as (
    select
      1 as pageview,
      pageview.created_at
    from mydb.pageview
    WHERE mydb.pageview.created_at >= TIMESTAMP('2021-04-21T09:47:06.000') AND mydb.pageview.created_at <= TIMESTAMP('2021-04-21T09:49:35.999')
    union all
    select
      0,
      series.created_at
    from series
    )

    select * from unioned
  ) AS `views_bucketed`  WHERE (`views_bucketed`.created_at >= TIMESTAMP(convert_tz('2021-04-21T09:47:06.000', '+00:00', @@session.time_zone)) AND `views_bucketed`.created_at <= TIMESTAMP(convert_tz('2021-04-21T09:49:35.999', '+00:00', @@session.time_zone))) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000

And here is what it returns

And this is how my pageview table looks like

Could you please suggest what I'm doing wrong? Thanks.