https://www.growthbook.io/ logo
#announcements
Title
# announcements
b

brainy-addition-11488

05/24/2022, 8:36 AM
hi, i am using growthbook inside docker environment. i defined a test metric over trino db and when i ran the result experiment query, i got this error:
Copy code
line 59:5: Column 'dimension' cannot be resolved
and here is my query:
-- test_metric_trino (binomial)
WITH __rawExperiment as ( SELECT user_id as user_id, timestamp as timestamp, experiment_id as experiment_id, variation_id as variation_id FROM hive.experiments.viewed_experiment ), __experiment as ( -- Viewed Experiment SELECT e.user_id as user_id, cast(e.variation_id as varchar) as variation, e.timestamp as conversion_start, e.timestamp + INTERVAL '72' hour as conversion_end FROM __rawExperiment e WHERE e.experiment_id = 'test_trino2' AND e.timestamp >= from_iso8601_timestamp('2022-05-24T075900.000Z') ), __metric as ( -- Metric (test_metric_trino) SELECT user_id as user_id, 1 as value, m.timestamp as conversion_start, m.timestamp as conversion_end FROM ( SELECT user_id, occurrence_timestamp as timestamp FROM hive.khabarchin.action_event WHERE event_id = 74 ) m WHERE m.timestamp >= from_iso8601_timestamp('2022-05-24T075900.000Z') ), __distinctUsers as ( -- One row per user/dimension SELECT e.user_id, 'All' as dimension, ( CASE WHEN count(distinct e.variation) > 1 THEN '__multiple__' ELSE max(e.variation) END ) as variation, MIN(e.conversion_start) as conversion_start, MIN(e.conversion_end) as conversion_end FROM __experiment e GROUP BY dimension, e.user_id ), __userMetric as ( -- Add in the aggregate metric value for each user SELECT d.variation, d.dimension, 1 as value FROM __distinctUsers d JOIN __metric m ON (m.user_id = d.user_id) WHERE m.conversion_start >= d.conversion_start AND m.conversion_start <= d.conversion_end GROUP BY variation, dimension, d.user_id ), __overallUsers as ( -- Number of users in each variation SELECT variation, dimension, COUNT(*) as users FROM __distinctUsers GROUP BY variation, dimension ), __stats as ( -- Sum all user metrics together to get a total per variation/dimension SELECT variation, dimension, COUNT(*) as count, AVG(value) as mean, STDDEV(value) as stddev FROM __userMetric GROUP BY variation, dimension ) SELECT s.variation, s.dimension, s.count, s.mean, s.stddev, u.users FROM __stats s JOIN __overallUsers u ON ( s.variation = u.variation AND s.dimension = u.dimension )
f

future-teacher-7046

05/24/2022, 11:34 AM
Hmm. Trino must be having issues with aliases in group by.
Copy code
SELECT
  'All' as dimension
  ...
GROUP BY dimension
https://github.com/prestodb/presto/pull/5581 All of the other databases support this, but looks like presto/trino do not. We should be able to easily change the queries to fix this.
b

brainy-addition-11488

05/24/2022, 11:45 AM
can i edit query result of experiments directly?
f

future-teacher-7046

05/24/2022, 11:48 AM
No, it has to be fixed in our SQL generator
b

brainy-addition-11488

05/28/2022, 8:50 AM
i replaced format query getExperimentMetricQuery function by :
Copy code
`-- ${metric.name} (${metric.type})
WITH
  ${idJoinSQL}
  __rawExperiment as (
    ${replaceDateVars(
      exposureQuery.query,
      phase.dateStarted,
      phase.dateEnded
    )}
  ),
  __experiment as (${this.getExperimentCTE({
    experiment,
    phase,
    baseIdType,
    conversionWindowHours:
      (activationMetric
        ? activationMetric.conversionWindowHours
        : metric.conversionWindowHours) || 0,
    conversionDelayHours:
      (activationMetric
        ? activationMetric.conversionDelayHours
        : metric.conversionDelayHours) || 0,
    experimentDimension:
      dimension?.type === "experiment" ? dimension.id : null,
  })})
  , __metric as (${this.getMetricCTE({
    metric,
    baseIdType,
    idJoinMap,
    startDate: metricStart,
    endDate: metricEnd,
  })})
  ${
    segment
      ? `, __segment as (${this.getSegmentCTE(
          segment,
          baseIdType,
          idJoinMap
        )})`
      : ""
  }
  ${
    dimension?.type === "user"
      ? `, __dimension as (${this.getDimensionCTE(
          dimension.dimension,
          baseIdType,
          idJoinMap
        )})`
      : ""
  }
  ${
    activationMetric
      ? `, __activationMetric as (${this.getMetricCTE({
          metric: activationMetric,
          conversionWindowHours:
            metric.conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS,
          conversionDelayHours: metric.conversionDelayHours,
          baseIdType,
          idJoinMap,
          startDate: metricStart,
          endDate: metricEnd,
        })})
        , __activatedUsers as (${this.getActivatedUsersCTE(baseIdType)})`
      : ""
  }
  , __distinctUsers as (
    -- One row per user/dimension${
      removeMultipleExposures ? "" : "/variation"
    }
    SELECT
      e.${baseIdType},
      ${
        dimension?.type === "user"
          ? "d.value"
          : dimension?.type === "experiment"
          ? "e.dimension"
          : dimension?.type === "date"
          ? this.formatDate(this.dateTrunc("e.conversion_start"))
          : activationDimension
          ? this.ifElse(
              `a.${baseIdType} IS NULL`,
              "'Not Activated'",
              "'Activated'"
            )
          : "'All'"
      } as dimension,
      ${
        removeMultipleExposures
          ? this.ifElse(
              "count(distinct e.variation) > 1",
              "'__multiple__'",
              "max(e.variation)"
            )
          : "e.variation"
      } as variation,
      MIN(${this.ifNullFallback(
        activationMetric ? "a.conversion_start" : null,
        "e.conversion_start"
      )}) as conversion_start,
      MIN(${this.ifNullFallback(
        activationMetric ? "a.conversion_end" : null,
        "e.conversion_end"
      )}) as conversion_end
    FROM
      __experiment e
      ${
        segment
          ? `JOIN __segment s ON (s.${baseIdType} = e.${baseIdType})`
          : ""
      }
      ${
        dimension?.type === "user"
          ? `JOIN __dimension d ON (d.${baseIdType} = e.${baseIdType})`
          : ""
      }
      ${
        activationMetric
          ? `
      ${activationDimension ? "LEFT " : ""}JOIN __activatedUsers a ON (
        a.${baseIdType} = e.${baseIdType}
      )`
          : ""
      }
    ${segment ? `WHERE s.date <= e.conversion_start` : ""}
    GROUP BY
      1, 2${removeMultipleExposures ? "" : ", 3"}
  )
  , __userMetric as (
    -- Add in the aggregate metric value for each user
    SELECT
      d.variation,
      d.dimension,
      ${aggregate} as value
    FROM
      __distinctUsers d
      JOIN __metric m ON (
        m.${baseIdType} = d.${baseIdType}
      )
      WHERE
        m.conversion_start >= d.conversion_start
        AND m.conversion_start <= d.conversion_end
    GROUP BY
    1,2,3
  )
  , __overallUsers as (
    -- Number of users in each variation
    SELECT
      variation,
      dimension,
      COUNT(*) as users
    FROM
      __distinctUsers
    GROUP BY
      1,
      2
  )
  , __stats as (    
    -- Sum all user metrics together to get a total per variation/dimension
    SELECT
      variation,
      dimension,
      COUNT(*) as count,
      ${this.avg("value")} as mean,
      ${this.stddev("value")} as stddev
    FROM
      __userMetric
    GROUP BY
      1,
      2
  )
SELECT
  s.variation,
  s.dimension,
  s.count,
  s.mean,
  s.stddev,
  u.users
FROM
  __stats s
  JOIN __overallUsers u ON (
    s.variation = u.variation 
    AND s.dimension = u.dimension
  )
`
c

chilly-umbrella-90459

06/11/2022, 11:23 PM
@future-teacher-7046 I have the same problem with Athena
Athena
That effectively prevents us from using GrowthBook at the moment as we are using hosted version and cannot apply the fix. Can you help with that?
f

future-teacher-7046

06/12/2022, 12:34 AM
I'll see if we can get a fix for that live this weekend.
🙏 1
c

chilly-umbrella-90459

06/12/2022, 12:20 PM
Thank you, that would be super great!
I see the open PR, is there something I can do to help test it?
f

future-teacher-7046

06/13/2022, 12:57 PM
I just merged it after doing some final testing to make sure it doesn't break other data sources.
c

chilly-umbrella-90459

06/13/2022, 2:51 PM
Thank you!
22 Views