SchemaField column = metastore.getCollection(context.project, steps.get(0).getCollection()).stream() .filter(c -> c.getName().equals(dimension.get())) .findAny().orElseThrow(() -> new RakamException("Dimension is not exist.", BAD_REQUEST)); Optional<String> filterExp = steps.get(i).getExpression().map(value -> RakamSqlFormatter.formatExpression(value, name -> name.getParts().stream().map(e -> formatIdentifier(e, '"')).collect(Collectors.joining(".")), ValidationUtil::checkTableColumn, '"')); connectorString, segment.isPresent() ? "" : ", " + checkTableColumn(projectConfig.getTimeColumn()), checkCollection(steps.get(i).getCollection()), checkTableColumn(projectConfig.getTimeColumn()), startDate,
String collection = funnelStep.getCollection(); startDate.format(ISO_DATE), endDate.plusDays(1).format(ISO_DATE), funnelStep.getExpression().map(exp -> "AND " + ClickhouseExpressionFormatter.formatExpression(exp, name -> name.getParts().stream().map(e -> formatIdentifier(e, '`')).collect(Collectors.joining(".")), name -> checkCollection(funnelStep.getCollection()) + "." + name.getParts().stream() .map(e -> formatIdentifier(e, '`')).collect(Collectors.joining(".")), '`')).orElse("")); }).collect(Collectors.joining("\n UNION ALL\n "));
dimension.map(ValidationUtil::checkTableColumn).map(v -> segment.isPresent() ? applySegment(v, segment) : v).orElse(""), checkTableColumn(projectConfig.getUserColumn()), step, checkCollection(steps.get(step).getCollection()), checkTableColumn(projectConfig.getTimeColumn()), startDateStr, endDateStr, String queries = steps.stream().map(step -> String.format("(select approx_set(%s) from %s where %s between timestamp '%s' and timestamp '%s' and %s )", checkTableColumn(projectConfig.getUserColumn()), checkCollection(step.getCollection()), checkTableColumn(projectConfig.getTimeColumn()), startDateStr, endDateStr, getFilterExp(step)))
public String convertFunnel(String project, String connectorField, int idx, FunnelStep funnelStep, Optional<String> dimension, Optional<String> segment, LocalDate startDate, LocalDate endDate) { String table = checkProject(project, '"') + "." + ValidationUtil.checkCollection(funnelStep.getCollection()); Optional<String> filterExp = funnelStep.getExpression().map(value -> RakamSqlFormatter.formatExpression(value, name -> name.getParts().stream().map(e -> formatIdentifier(e, '"')).collect(Collectors.joining(".")), name -> formatIdentifier("step" + idx, '"') + "." + name, '"')); String format = format("SELECT %s %s %s, %d as step, %s from %s %s %s", segment.isPresent() ? "" : dimension.map(ValidationUtil::checkTableColumn).map(v -> v + ",").orElse(""), segment.isPresent() ? format(timeStampMapping.get(FunnelTimestampSegments.valueOf(segment.get().replace(" ", "_").toUpperCase())), dimension.get()) + " as " + checkTableColumn(dimension.get() + "_segment") + "," : "", format(connectorField, "step" + idx), idx + 1, checkTableColumn(projectConfig.getTimeColumn()), table, "step" + idx, filterExp.map(v -> "where " + v).orElse("")); return format; } }
@Test(dataProvider = "types") public void testDimension(FunnelQueryExecutor.FunnelType funnelType) throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null), new FunnelStep("test1", null)), Optional.of("teststr"), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.of(new FunnelWindow(30, DAY)), UTC, Optional.empty(), funnelType).getResult().join(); assertFalse(query.isFailed()); assertEquals(ImmutableSet.copyOf(query.getResult()), ImmutableSet.of( of("Step 1", "test0", 3L), of("Step 1", "test1", 3L), of("Step 2", "test0", 3L), of("Step 2", "test1", 3L))); }
public String convertFunnel(String project, String connectorField, int idx, FunnelStep funnelStep, Optional<String> dimension, Optional<String> segment, LocalDate startDate, LocalDate endDate) { Optional<String> filterExp = funnelStep.getExpression().map(value -> RakamSqlFormatter.formatExpression(value, name -> name.getParts().stream().map(e -> formatIdentifier(e, '"')).collect(Collectors.joining(".")), name -> formatIdentifier("step" + idx, '"') + "." + name, '"')); String format = format("SELECT %s %s, %d as step, %s.%s from %s.%s.%s %s %s %s", dimension.map(ValidationUtil::checkTableColumn).map(v -> "step" + idx + "." + v).map(v -> segment.isPresent() ? applySegment(v, segment) + " as \"" + dimension.orElse("") + "_segment\"" + "," : v + ",").orElse(""), userMappingEnabled ? format("coalesce(mapping._user, %s._user, %s) as _user", "step" + idx, format(connectorField, "step" + idx)) : format(connectorField, "step" + idx), idx + 1, "step" + idx, checkTableColumn(projectConfig.getTimeColumn()), prestoConfig.getColdStorageConnector(), checkProject(project, '"'), checkCollection(funnelStep.getCollection()), "step" + idx, userMappingEnabled ? format("left join %s.%s mapping on (%s.%s is null and mapping.created_at >= date '%s' and mapping.merged_at <= date '%s' and mapping.id = %s.%s)", project, checkCollection(ANONYMOUS_ID_MAPPING), "step" + idx, checkTableColumn(projectConfig.getUserColumn()), startDate.format(ISO_LOCAL_DATE), endDate.format(ISO_LOCAL_DATE), "step" + idx, checkTableColumn(projectConfig.getUserColumn())) : "", filterExp.map(v -> "where " + v).orElse("")); return format; }
@Test(dataProvider = "types") public void testMultipleStepsGrouping(FunnelQueryExecutor.FunnelType funnelType) throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null), new FunnelStep("test1", null), new FunnelStep("test2", null)), Optional.of("teststr"), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.of(new FunnelWindow(30, DAY)), UTC, Optional.empty(), funnelType).getResult().join(); assertFalse(query.isFailed()); assertEquals(ImmutableSet.copyOf(query.getResult()), ImmutableSet.of( of("Step 1", "test0", 3L), of("Step 1", "test1", 3L), of("Step 2", "test0", 3L), of("Step 2", "test1", 3L), of("Step 3", "test0", 3L), of("Step 3", "test1", 3L))); }
@Test public void testApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", Optional.of("teststr = 'test1'")), new FunnelStep("test1", Optional.of("teststr = 'test1'"))), Optional.empty(), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), APPROXIMATE).getResult().join(); assertFalse(query.isFailed()); assertEquals(query.getResult(), of(of("Step 1", 3L), of("Step 2", 3L))); }
@Test public void testSegmentApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", Optional.of("teststr = 'test1'")), new FunnelStep("test1", Optional.of("teststr = 'test1'"))), Optional.of("_time"), Optional.of(FunnelQueryExecutor.FunnelTimestampSegments.DAY_OF_MONTH.value()), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), APPROXIMATE).getResult().join(); assertFalse(query.isFailed()); assertEquals(query.getResult(), of(of("Step 1", "1th day", 3L), of("Step 2", "1th day", 3L))); }
@Test public void testSegmentOrdered() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", Optional.of("teststr = 'test1'")), new FunnelStep("test1", Optional.of("teststr = 'test1'"))), Optional.of("_time"), Optional.of(FunnelQueryExecutor.FunnelTimestampSegments.DAY_OF_MONTH.value()), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), ORDERED).getResult().join(); assertFalse(query.isFailed()); assertEquals(query.getResult(), of(of("Step 1", "1th day", 3L), of("Step 2", "1th day", 3L))); }
@Test public void testSegment() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", Optional.of("teststr = 'test1'")), new FunnelStep("test1", Optional.of("teststr = 'test1'"))), Optional.of("_time"), Optional.of(FunnelQueryExecutor.FunnelTimestampSegments.DAY_OF_MONTH.value()), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), NORMAL).getResult().join(); assertFalse(query.isFailed()); assertEquals(query.getResult(), of(of("Step 1", "1th day", 3L), of("Step 2", "1th day", 3L))); }
@Test public void testMultipleStepsGroupingApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null), new FunnelStep("test1", null), new FunnelStep("test2", null)), Optional.of("teststr"), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), APPROXIMATE).getResult().join(); assertFalse(query.isFailed()); }
@Test public void testMultipleStepsApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null), new FunnelStep("test1", null), new FunnelStep("test2", null)), Optional.empty(), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), APPROXIMATE).getResult().join(); assertFalse(query.isFailed()); }
@Test(dataProvider = "types") public void testSingleStep(FunnelQueryExecutor.FunnelType funnelType) throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null)), Optional.empty(), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.of(new FunnelWindow(30, DAY)), UTC, Optional.empty(), funnelType).getResult().join(); assertFalse(query.isFailed()); assertEquals(query.getResult(), of(of("Step 1", 3L))); }
@Test public void testFilterApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", Optional.of("teststr = 'test1'")), new FunnelStep("test1", Optional.of("teststr = 'test1'"))), Optional.of("teststr"), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), APPROXIMATE).getResult().join(); assertFalse(query.isFailed()); }
private String getFilterExp(FunnelStep step) { return step.getExpression().map(value -> RakamSqlFormatter.formatExpression(value, name -> name.getParts().stream().map(e -> formatIdentifier(e, '"')).collect(Collectors.joining(".")), ValidationUtil::checkTableColumn, '"')).orElse("true"); }
@Test public void testSingleStepApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null)), Optional.empty(), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.empty(), UTC, Optional.empty(), FunnelQueryExecutor.FunnelType.APPROXIMATE).getResult().join(); assertFalse(query.isFailed()); }
@Test public void testDimensionApproximate() throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null), new FunnelStep("test1", null)), Optional.of("teststr"), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.of(new FunnelWindow(30, DAY)), UTC, Optional.empty(), NORMAL).getResult().join(); assertFalse(query.isFailed()); }
@Test(dataProvider = "types") public void testMultipleSteps(FunnelQueryExecutor.FunnelType funnelType) throws Exception { QueryResult query = getFunnelQueryExecutor().query(new RequestContext(PROJECT_NAME, null), of(new FunnelStep("test0", null), new FunnelStep("test1", null), new FunnelStep("test2", null)), Optional.empty(), Optional.empty(), LocalDate.ofEpochDay(0), LocalDate.ofEpochDay(SCALE_FACTOR), Optional.of(new FunnelWindow(30, DAY)), UTC, Optional.empty(), funnelType).getResult().join(); assertFalse(query.isFailed()); assertEquals(query.getResult(), of(of("Step 1", 3L), of("Step 2", 3L), of("Step 3", 3L))); }
protected boolean testDeviceIdExists(FunnelStep firstAction, Map<String, List<SchemaField>> collections) { List<SchemaField> schemaFields = collections.get(firstAction.getCollection()); if (schemaFields == null) { throw new RakamException("The collection in first action does not exist.", HttpResponseStatus.BAD_REQUEST); } return schemaFields.stream().anyMatch(e -> e.getName().equals("_device_id")); } }