public static String checkTableColumn(String column) { return checkTableColumn(column, column, '"'); }
public static String checkTableColumn(String column, char escape) { return checkTableColumn(column, column, escape); }
@Override public String getTemplate(List<FunnelStep> steps, Optional<String> dimension, Optional<FunnelWindow> window) { return "select %s step, count(*) total from (\n" + "select %s funnel_step_time(array_agg(cast(step as tinyint)), array_agg(cast(to_unixtime(" + checkTableColumn(projectConfig.getTimeColumn()) + ") as integer))) as step from (select * from (%s) WHERE " + checkTableColumn(projectConfig.getTimeColumn()) + " between timestamp '%s' and timestamp '%s'\n" + ") t group by %s %s\n" + ") t group by 1 %s order by 1"; }
@Override public String getTemplate(List<FunnelStep> steps, Optional<String> dimension, Optional<FunnelWindow> window) { return "select %s get_funnel_step(steps) step, count(*) total from (\n" + "select %s array_agg(step order by " + checkTableColumn(projectConfig.getTimeColumn()) + ") as steps from (%s) t WHERE " + checkTableColumn(projectConfig.getTimeColumn()) + " between timestamp '%s' and timestamp '%s'\n" + "group by %s %s\n" + ") t group by 1 %s order by 1"; }
@Override protected String visitIdentifier(Identifier node, Boolean context) { String tableColumn = ValidationUtil .checkTableColumn(node.getValue(), "reference in filter", '"'); Optional<String> preComputedTable = columnNameMapper.apply(tableColumn); if (preComputedTable.isPresent()) { return preComputedTable.get(); } throw new UnsupportedOperationException(); }
@Override protected String visitIdentifier(Identifier node, Boolean context) { return "props.get(\"" + checkTableColumn(node.getValue(), "field reference is invalid", '"') + "\")"; }
public String getColumnValue(Map<TimestampTransformation, String> timestampMapping, Reference ref, boolean format) { switch (ref.type) { case COLUMN: return format ? checkTableColumn(ref.value) : ref.value; case REFERENCE: return format(timestampMapping.get(fromString(ref.value.replace(" ", "_"))), projectConfig.getTimeColumn()); default: throw new IllegalArgumentException("Unknown reference type: " + ref.value); } }
private String getQuery(String project, String collection, Schema schema) { StringBuilder query = new StringBuilder("INSERT INTO ") .append(checkProject(project, '"')) .append(".") .append(ValidationUtil.checkCollection(collection)); StringBuilder params = new StringBuilder(); List<Schema.Field> fields = schema.getFields(); Schema.Field firstField = fields.get(0); if (!sourceFields.contains(firstField.name())) { query.append(" (").append(checkTableColumn(firstField.name())); params.append('?'); } for (int i = 1; i < fields.size(); i++) { Schema.Field field = fields.get(i); if (!sourceFields.contains(field.name())) { query.append(", ").append(checkTableColumn(field.name())); params.append(", ?"); } } return query.append(") VALUES (").append(params.toString()).append(")").toString(); } }
protected String generateComputeQuery(Reference grouping, Reference segment, String collection) { StringBuilder selectBuilder = new StringBuilder(); if (grouping != null) { selectBuilder.append(getColumnValue(timestampMapping, grouping, true) + " as " + checkTableColumn(getColumnReference(grouping) + "_group")); if (segment != null) { selectBuilder.append(", "); } } if (segment != null) { selectBuilder.append((!segment.equals(DEFAULT_SEGMENT) ? getColumnValue(timestampMapping, segment, true) : "'" + checkLiteral(collection) + "'") + " as " + checkTableColumn(getColumnReference(segment) + "_segment")); } return selectBuilder.toString(); }
protected String getTableSubQuery(String project, String collection, String connectorField, Optional<String> dimension, String timePredicate, Optional<Expression> filter) { return format("select %s, %s %s from %s where %s %s %s", checkTableColumn(projectConfig.getTimeColumn()), dimension.isPresent() ? checkTableColumn(dimension.get(), "dimension", '"') + " as dimension, " : "", connectorField, checkProject(project, '"') + "." + checkCollection(collection), checkTableColumn(projectConfig.getTimeColumn()), timePredicate, filter.isPresent() ? "and " + formatExpression(filter.get(), reference -> { throw new UnsupportedOperationException(); }, '"') : ""); } }
@Override public void createSegment(RequestContext context, String name, String tableName, Expression filterExpression, List<EventFilter> eventFilter, Duration interval) { String query; if (filterExpression == null) { query = String.format("select distinct %s as id from (%s) t", checkTableColumn(projectConfig.getUserColumn()), eventFilter.stream().map(f -> String.format(getEventFilterQuery(context.project, f), f.collection)).collect(Collectors.joining(" UNION ALL "))); } else { throw new RakamException("User segment must have at least one event filter", BAD_REQUEST); } materializedViewService.create(context, new MaterializedView(tableName, "Users who did " + (tableName == null ? "at least one event" : tableName + " event"), query, interval, null, null, ImmutableMap.of())); }
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; } }
protected String getTableSubQuery( boolean mappingEnabled, String collection, String connectorField, Optional<Boolean> isText, String timeColumn, Optional<String> dimension, LocalDate startDate, LocalDate endDate, Optional<Expression> filter) { String userField = isText.map(text -> String.format("%s", checkTableColumn(connectorField))).orElse(connectorField); String timePredicate = String.format("between date '%s' and date '%s' + interval '1' day", startDate.format(ISO_LOCAL_DATE), endDate.format(ISO_LOCAL_DATE)); return format("select %s as date, %s %s from %s as data %s where data.%s %s %s", String.format(timeColumn, "data." + checkTableColumn(projectConfig.getTimeColumn())), dimension.isPresent() ? checkTableColumn(dimension.get(), "data.dimension", '"') + " as dimension, " : "", (userMappingEnabled && mappingEnabled) ? String.format("(case when data.%s is not null then data.%s else coalesce(mapping._user, data._device_id) end) as %s", userField, userField, userField) : ("data." + userField), checkCollection(collection), (userMappingEnabled && mappingEnabled) ? String.format("left join %s mapping on (data.%s is null and mapping.created_at >= date '%s' and mapping.merged_at <= date '%s' and mapping.id = data._user)", checkCollection(ANONYMOUS_ID_MAPPING), checkTableColumn(projectConfig.getUserColumn()), startDate.format(ISO_LOCAL_DATE), endDate.format(ISO_LOCAL_DATE)) : "", checkTableColumn(projectConfig.getTimeColumn()), timePredicate, filter.isPresent() ? "and " + formatExpression(filter.get(), reference -> { throw new UnsupportedOperationException(); }, '"') : ""); } }
table = String.format("SELECT cast(%s as date) as date, %s %s FROM _all", checkCollection(projectConfig.getTimeColumn()), Optional.ofNullable(query.dimension).map(v -> v + ",").orElse(""), checkTableColumn(projectConfig.getUserColumn())); dateColumn = "date"; } else {
public void createTable(String project) { String query = format("select date_trunc('minute', cast(%s as timestamp)) as _time, cast(_collection as varchar) as _collection, count(*) as total from _all group by 1, 2", checkTableColumn(projectConfig.getTimeColumn())); MaterializedView report = new MaterializedView(tableName(), format("Event explorer metrics"), query, Duration.ofHours(1), true, true, ImmutableMap.of()); materializedViewService.create(new RequestContext(project, null), report).join(); } }
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; }
.map(entry -> format("select '%s' as collection, row_to_json(coll) json, %s from %s.%s coll where _user = '%s' %s", entry.getKey(), checkTableColumn(projectConfig.getTimeColumn()), checkCollection(context.project), checkCollection(entry.getKey()), checkLiteral(user), beforeThisTime == null ? "" : format("and %s < timestamp '%s'", checkTableColumn(projectConfig.getTimeColumn()), beforeThisTime.toString()))) .collect(Collectors.joining(" union all ")); sqlQuery, checkTableColumn(projectConfig.getTimeColumn()), limit), ZoneOffset.UTC, ImmutableMap.of()).getResult(); return queryResult.thenApply(result -> { if (result.isFailed()) {
public void onCreateCollectionFields(String project, String collection, List<SchemaField> fields) { for (SchemaField field : fields) { try { // We cant't use CONCURRENTLY because it causes dead-lock with ALTER TABLE and it's slow. projectConfig.getTimeColumn(); executor.executeRawStatement(String.format("CREATE INDEX %s %s ON %s.%s USING %s(%s)", postgresql9_5 ? "IF NOT EXISTS" : "", checkCollection(String.format("%s_%s_%s_auto_index", project, collection, field.getName())), project, checkCollection(collection), (postgresql9_5 && field.getName().equals(projectConfig.getTimeColumn())) ? "BRIN" : "BTREE", checkTableColumn(field.getName()))); } catch (Exception e) { if (postgresql9_5) { throw e; } } } } }
public void incrementProperty(Connection conn, String project, Object userId, String property, double value) throws SQLException { Map<String, FieldType> columns = createMissingColumns(project, userId, ImmutableList.of(new SimpleImmutableEntry<>(property, new DoubleNode(value))), new CommitConnection(conn)); FieldType fieldType = columns.get(property); if (fieldType == null) { createColumn(project, userId, property, JsonHelper.numberNode(0)); } if (!fieldType.isNumeric()) { throw new RakamException(String.format("The property the is %s and it can't be incremented.", fieldType.name()), BAD_REQUEST); } String tableRef = checkTableColumn(stripName(property, "table column")); Statement statement = conn.createStatement(); ProjectCollection userTable = getUserTable(project, false); String table = checkProject(userTable.project, '"') + "." + checkCollection(userTable.collection); int execute = statement.executeUpdate("update " + table + " set " + tableRef + " = " + value + " + coalesce(" + tableRef + ", 0)"); if (execute == 0) { create(project, userId, JsonHelper.jsonObject().put(property, value)); } }
checkProject(userTable.project, '"'), checkCollection(userTable.collection), checkTableColumn(column), getPostgresqlType(value))); } catch (SQLException e) { Map<String, FieldType> fields = loadColumns(project);