This is an automated email from the ASF dual-hosted git repository. vogievetsky pushed a commit to branch sod in repository https://gitbox.apache.org/repos/asf/druid.git
commit d6c2eabed99037af3ba062705454fd68aea7ebee Author: Vadim Ogievetsky <[email protected]> AuthorDate: Thu May 2 08:49:00 2024 -0700 progress --- .../explore-view/modules/table-react-module.tsx | 137 ++++++++++++++++----- .../views/explore-view/modules/utils/utils.spec.ts | 32 ++++- .../src/views/explore-view/modules/utils/utils.ts | 37 +++++- 3 files changed, 168 insertions(+), 38 deletions(-) diff --git a/web-console/src/views/explore-view/modules/table-react-module.tsx b/web-console/src/views/explore-view/modules/table-react-module.tsx index d2ad302fcd2..2b2e9ee30f3 100644 --- a/web-console/src/views/explore-view/modules/table-react-module.tsx +++ b/web-console/src/views/explore-view/modules/table-react-module.tsx @@ -17,7 +17,7 @@ */ import { Button } from '@blueprintjs/core'; -import type { SqlOrderByExpression } from '@druid-toolkit/query'; +import type { SqlColumn, SqlOrderByExpression } from '@druid-toolkit/query'; import { C, F, @@ -45,6 +45,8 @@ import './table-react-module.scss'; type MultipleValueMode = 'null' | 'empty' | 'latest' | 'latestNonNull' | 'count'; +type CompareType = 'value' | 'delta' | 'percent'; + const KNOWN_AGGREGATIONS = [ 'COUNT', 'SUM', @@ -74,6 +76,10 @@ const KNOWN_AGGREGATIONS = [ 'ANY_VALUE', ]; +function coalesce0(ex: SqlExpression) { + return F('COALESCE', ex, 0); +} + function toGroupByExpression( splitColumn: ExpressionMeta, timeBucket: string, @@ -222,6 +228,12 @@ export default typedVisualModule({ visible: ({ params }) => Boolean((params.compares || []).length), }, }, + showPercent: { + type: 'boolean', + control: { + visible: ({ params }) => Boolean((params.compares || []).length), + }, + }, maxRows: { type: 'number', default: 200, @@ -298,6 +310,7 @@ function TableModule(props: TableModuleProps) { const metrics: ExpressionMeta[] = parameterValues.metrics; const compares: string[] = parameterValues.compares || []; const showDelta: boolean = parameterValues.showDelta; + const showPercent: boolean = parameterValues.showPercent; const maxRows: number = parameterValues.maxRows; const pivotValues = pivotColumn ? pivotValueState.data : undefined; @@ -308,14 +321,29 @@ function TableModule(props: TableModuleProps) { const hasCompare = Boolean(compares.length); + const orderByColumnName = (effectiveOrderBy.expression as SqlColumn).getName(); + let orderByCompareMeasure: string | undefined; + let orderByCompareDuration: string | undefined; + let orderByCompareType: CompareType | undefined; + if (hasCompare) { + const m = orderByColumnName.match(/^(.+):cmp:([^:]+):(value|delta|percent)$/); + if (m) { + orderByCompareMeasure = m[1]; + orderByCompareDuration = m[2]; + orderByCompareType = m[3] as CompareType; + } + } + const mainQuery = getInitQuery(table, where) .applyForEach(splitColumns, (q, splitColumn) => q.addSelect(toGroupByExpression(splitColumn, timeBucket), { addToGroupBy: 'end', }), ) - .applyForEach(showColumns, (q, showColumn) => - q.addSelect(toShowColumnExpression(showColumn, multipleValueMode)), + .applyIf(!orderByCompareDuration, q => + q.applyForEach(showColumns, (q, showColumn) => + q.addSelect(toShowColumnExpression(showColumn, multipleValueMode)), + ), ) .applyForEach(pivotValues || [''], (q, pivotValue, i) => q.applyForEach(metrics, (q, metric) => @@ -333,10 +361,13 @@ function TableModule(props: TableModuleProps) { ), ), ) - .applyIf(metrics.length > 0 || splitColumns.length > 0, q => - q.changeOrderByExpression(effectiveOrderBy), - ) - .changeLimitValue(maxRows); + .applyIf(!orderByCompareDuration, q => + q + .applyIf(metrics.length > 0 || splitColumns.length > 0, q => + q.changeOrderByExpression(effectiveOrderBy), + ) + .changeLimitValue(maxRows), + ); if (!hasCompare) { return { @@ -352,22 +383,38 @@ function TableModule(props: TableModuleProps) { }; } + const needsOuterOrderBy = orderByCompareType === 'delta' || orderByCompareType === 'percent'; const main = T('main'); + const leader = T(orderByCompareDuration ? `compare_${orderByCompareDuration}` : 'main'); return { - query: SqlQuery.from(main) + query: SqlQuery.from(leader) .changeWithParts( [SqlWithPart.simple('main', mainQuery)].concat( - compares.map((comparePeriod, i) => + compares.map(compare => SqlWithPart.simple( - `compare${i}`, - getInitQuery(table, shiftTimeInWhere(where, comparePeriod)) + `compare_${compare}`, + getInitQuery(table, shiftTimeInWhere(where, compare)) .applyForEach(splitColumns, (q, splitColumn) => - q.addSelect(toGroupByExpression(splitColumn, timeBucket, comparePeriod), { + q.addSelect(toGroupByExpression(splitColumn, timeBucket, compare), { addToGroupBy: 'end', }), ) + .applyIf(orderByCompareDuration === compare, q => + q.applyForEach(showColumns, (q, showColumn) => + q.addSelect(toShowColumnExpression(showColumn, multipleValueMode)), + ), + ) .applyForEach(metrics, (q, metric) => q.addSelect(metric.expression.as(metric.name)), + ) + .applyIf( + compare === orderByCompareDuration && orderByCompareType === 'value', + q => + q + .changeOrderByExpression( + effectiveOrderBy.changeExpression(C(orderByCompareMeasure!)), + ) + .changeLimitValue(maxRows), ), ), ), @@ -377,24 +424,34 @@ function TableModule(props: TableModuleProps) { splitColumns .map(splitColumn => main.column(splitColumn.name).as(splitColumn.name)) .concat( - showColumns.map(showColumn => main.column(showColumn.name).as(showColumn.name)), - metrics.map(metric => main.column(metric.name).as(metric.name)), - compares.flatMap((compare, i) => + showColumns.map(showColumn => leader.column(showColumn.name).as(showColumn.name)), + metrics.map(metric => + main.column(metric.name).applyIf(orderByCompareDuration, coalesce0).as(metric.name), + ), + compares.flatMap(compare => metrics.flatMap(metric => { - const c = T(`compare${i}`).column(metric.name); + const c = T(`compare_${compare}`) + .column(metric.name) + .applyIf(compare !== orderByCompareDuration, coalesce0); - const ret = [ - SqlFunction.simple('COALESCE', [c, 0]).as( - `cmp:${compare}:value:${metric.name}`, - ), - ]; + const ret = [c.as(`${metric.name}:cmp:${compare}:value`)]; + const mainMetric = main + .column(metric.name) + .applyIf(orderByCompareDuration, ex => F('COALESCE', ex, 0)); if (showDelta) { ret.push( - SqlFunction.simple('SAFE_DIVIDE', [ - SqlExpression.parse(`(${main.column(metric.name)} - ${c}) * 1.0`), - c, - ]).as(`cmp:${compare}:percent:${metric.name}`), + SqlExpression.parse(`${mainMetric} - ${c}`).as( + `${metric.name}:cmp:${compare}:delta`, + ), + ); + } + + if (showPercent) { + ret.push( + F('SAFE_DIVIDE', SqlExpression.parse(`(${mainMetric} - ${c}) * 100.0`), c).as( + `${metric.name}:cmp:${compare}:percent`, + ), ); } @@ -403,27 +460,45 @@ function TableModule(props: TableModuleProps) { ), ), ) - .applyForEach(compares, (q, _comparePeriod, i) => + .applyIf(orderByCompareDuration, q => q.addLeftJoin( - T(`compare${i}`), + main, SqlExpression.and( ...splitColumns.map(splitColumn => main .column(splitColumn.name) - .isNotDistinctFrom(T(`compare${i}`).column(splitColumn.name)), + .isNotDistinctFrom( + T(`compare_${orderByCompareDuration}`).column(splitColumn.name), + ), ), ), ), - ), + ) + .applyForEach( + compares.filter(c => c !== orderByCompareDuration), + (q, compare) => + q.addLeftJoin( + T(`compare_${compare}`), + SqlExpression.and( + ...splitColumns.map(splitColumn => + main + .column(splitColumn.name) + .isNotDistinctFrom(T(`compare_${compare}`).column(splitColumn.name)), + ), + ), + ), + ) + .addOrderBy(effectiveOrderBy) + .changeLimitValue(maxRows), groupHints: splitColumns .map(() => 'Current') .concat( showColumns.map(() => 'Current'), metrics.map(() => 'Current'), - compares.flatMap(comparePeriod => + compares.flatMap(compare => metrics .flatMap(() => (showDelta ? ['', ''] : [''])) - .map(() => `Comparison to ${comparePeriod}`), + .map(() => `Comparison to ${compare}`), ), ), }; diff --git a/web-console/src/views/explore-view/modules/utils/utils.spec.ts b/web-console/src/views/explore-view/modules/utils/utils.spec.ts index 9db276e6a0b..00cf1f198e4 100644 --- a/web-console/src/views/explore-view/modules/utils/utils.spec.ts +++ b/web-console/src/views/explore-view/modules/utils/utils.spec.ts @@ -21,13 +21,41 @@ import { SqlExpression } from '@druid-toolkit/query'; import { shiftTimeInWhere } from './utils'; describe('shiftTimeInWhere', () => { - it('works with TIME_IN_INTERVAL', () => { + it('works with TIME_IN_INTERVAL (date)', () => { expect( shiftTimeInWhere( SqlExpression.parse(`TIME_IN_INTERVAL("__time", '2016-06-27/2016-06-28')`), 'P1D', ).toString(), - ).toEqual(`TIME_IN_INTERVAL(TIME_SHIFT("__time", 'P1D', 1), '2016-06-27/2016-06-28')`); + ).toEqual( + `TIME_SHIFT(TIMESTAMP '2016-06-27', 'P1D', -1) <= "__time" AND "__time" < TIME_SHIFT(TIMESTAMP '2016-06-28', 'P1D', -1)`, + ); + }); + + it('works with TIME_IN_INTERVAL (date and time)', () => { + expect( + shiftTimeInWhere( + SqlExpression.parse( + `TIME_IN_INTERVAL("__time", '2016-06-27T12:34:56/2016-06-28T12:34:56')`, + ), + 'P1D', + ).toString(), + ).toEqual( + `TIME_SHIFT(TIMESTAMP '2016-06-27 12:34:56', 'P1D', -1) <= "__time" AND "__time" < TIME_SHIFT(TIMESTAMP '2016-06-28 12:34:56', 'P1D', -1)`, + ); + }); + + it('works with TIME_IN_INTERVAL (date and time, zulu)', () => { + expect( + shiftTimeInWhere( + SqlExpression.parse( + `TIME_IN_INTERVAL("__time", '2016-06-27T12:34:56Z/2016-06-28T12:34:56Z')`, + ), + 'P1D', + ).toString(), + ).toEqual( + `TIME_SHIFT(TIME_PARSE('2016-06-27 12:34:56', NULL, 'Etc/UTC'), 'P1D', -1) <= "__time" AND "__time" < TIME_SHIFT(TIME_PARSE('2016-06-28 12:34:56', NULL, 'Etc/UTC'), 'P1D', -1)`, + ); }); it('works with relative time', () => { diff --git a/web-console/src/views/explore-view/modules/utils/utils.ts b/web-console/src/views/explore-view/modules/utils/utils.ts index 7320ca52404..030797f4d53 100644 --- a/web-console/src/views/explore-view/modules/utils/utils.ts +++ b/web-console/src/views/explore-view/modules/utils/utils.ts @@ -16,28 +16,55 @@ * limitations under the License. */ -import type { SqlExpression } from '@druid-toolkit/query'; -import { F, SqlFunction, SqlLiteral } from '@druid-toolkit/query'; +import { F, SqlExpression, SqlFunction, SqlLiteral } from '@druid-toolkit/query'; + +const IS_DATE_LIKE = /^[+-]?\d\d\d\d[^']+$/; + +function isoStringToTimestampLiteral(iso: string): SqlExpression { + const zulu = iso.endsWith('Z'); + const cleanIso = iso.replace('T', ' ').replace('Z', ''); + let sql: string; + if (zulu) { + sql = `TIME_PARSE('${cleanIso}', NULL, 'Etc/UTC')`; + } else { + sql = `TIMESTAMP '${cleanIso}'`; + } + return SqlExpression.parse(sql); +} export function shiftTimeInWhere(where: SqlExpression, period: string): SqlExpression { return where.walk(ex => { if (ex instanceof SqlLiteral) { // Works with: __time < TIMESTAMP '2022-01-02 03:04:05' if (ex.isDate()) { - return F('TIME_SHIFT', ex, period, -1); + return F.timeShift(ex, period, -1); } } else if (ex instanceof SqlFunction) { const effectiveFunctionName = ex.getEffectiveFunctionName(); // Works with: TIME_IN_INTERVAL(__time, '<interval>') if (effectiveFunctionName === 'TIME_IN_INTERVAL') { - return ex.changeArgs(ex.args!.change(0, F('TIME_SHIFT', ex.getArg(0), period, 1))); + // Ideally we could rewrite it to TIME_IN_INTERVAL(TIME_SHIFT(__time, period, 1), '<interval>') but that would be slow in the current Druid + // return ex.changeArgs(ex.args!.change(0, F('TIME_SHIFT', ex.getArg(0), period, 1))); + + const interval = ex.getArgAsString(1); + if (!interval) return ex; + + const [start, end] = interval.split('/'); + if (!IS_DATE_LIKE.test(start) || !IS_DATE_LIKE.test(end)) return ex; + + const t = ex.getArg(0); + if (!t) return ex; + + return F.timeShift(isoStringToTimestampLiteral(start), period, -1) + .lessThanOrEqual(t) + .and(t.lessThan(F.timeShift(isoStringToTimestampLiteral(end), period, -1))); } // Works with: TIME_SHIFT(...) <= __time // and: __time < MAX_DATA_TIME() if (effectiveFunctionName === 'TIME_SHIFT' || effectiveFunctionName === 'MAX_DATA_TIME') { - return F('TIME_SHIFT', ex, period, -1); + return F.timeShift(ex, period, -1); } } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
