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(); }, '"') : ""); } }
public static String checkCollection(String collection, char character) { checkCollectionValid(collection); return character + collection.replaceAll("\"", "") + character; }
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(); }
@Override public void dropProjectIfExists(String project) { ProjectCollection userTable = getUserTable(project, false); String table = checkProject(userTable.project, '"') + "." + checkCollection(userTable.collection); QueryResult result = queryExecutor.executeRawStatement(new RequestContext(project, null), format("DROP TABLE IF EXISTS %s", table)).getResult().join(); propertyCache.invalidateAll(); userTypeCache.invalidateAll(); if (result.isFailed()) { throw new IllegalStateException(result.toString()); } }
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)); } }
@Override public CompletableFuture<List<CollectionEvent>> getEvents(RequestContext context, String user, Optional<List<String>> properties, int limit, Instant beforeThisTime) { checkProject(context.project); checkNotNull(user); checkArgument(limit <= 1000, "Maximum 1000 events can be fetched at once."); .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()) {
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(); }, '"') : ""); } }
"\tselect cast(%s as date) as date, '%s' as collection, '%s' as attribute, %s as value from %s group by 1, 2, 3, 4 order by count(*) limit 10000\n" + ") \"unique_values_%s_%s\"", projectConfig.getTimeColumn(), checkLiteral(collection), checkLiteral(field.getName()), checkTableColumn(field.getName()), checkCollection(collection), checkCollectionValid(collection), stripName(field.getName(), "attribute")); firstIteration = false;
public static String checkCollection(String collection) { return checkCollection(collection, '"'); }
checkCollection(attribute), checkProject(project), checkProject(collection), samplePercentage); if (filter.isPresent() && !filter.get().isEmpty()) { String value = "%" + filter.get().replaceAll("%", "\\%").replaceAll("_", "\\_") + "%"; queryPrep += format(" AND %s like '%s' escape '\\' and %s is not null", checkCollection(attribute), checkLiteral(value), checkCollection(attribute));
public static String checkProject(String project, char character) { return character + checkProject(project).replaceAll("\"", "") + character; }
public static String checkTableColumn(String column) { return checkTableColumn(column, column, '"'); }
measure.aggregation != null ? measure.aggregation : COUNT); String measureColumn = measure != null && measure.column != null ? checkTableColumn(measure.column, '`') : ""; select.isEmpty() ? select : select + ",", format(measureAgg, measureColumn), project, checkCollection(collections.get(0), '`'), where, groupBy); String selectPart = (grouping == null ? "" : checkTableColumn(getColumnReference(grouping) + "_group", '`')) + (grouping == null ? "" : ", ") + checkTableColumn(getColumnReference(segment) + "_segment", '`'); checkLiteral(collection), measureColumn.isEmpty() ? select : measureColumn + ",", measureColumn, project, checkCollection(collection, '`'), where); return format; }) "%s\n" + ") ORDER BY %s DESC LIMIT 500", grouping == null ? "" : format(grouping.type == COLUMN ? checkTableColumn("%s_group", '`') : checkTableColumn("%s_group", '`'), getColumnReference(grouping)), segment == null ? "" : ((grouping == null ? "" : ",") + format(segment.type == COLUMN ? checkTableColumn("%s_segment", '`') : checkTableColumn("%s_segment", '`'), getColumnReference(segment))), grouping != null || segment != null ? "," : "", computeQuery, segment != null && grouping != null ? 3 : 2);
params.add(format("temporal_column = '%s'", checkLiteral(projectConfig.getTimeColumn()))); bucketCount = distribution.getBucketCount(); params.add(format("bucketed_on = array['%s']", checkLiteral(projectConfig.getUserColumn()))); params.add(format("bucket_count = %d", bucketCount)); params.add(format("distribution_name = '%s'", project)); prestoConfig.getColdStorageConnector(), project, checkCollection(collection), queryEnd, properties);
@Override public CompletableFuture<QueryResult> searchUsers(RequestContext context, List<String> selectColumns, Expression filterExpression, List<EventFilter> eventFilter, Sorting sortColumn, long limit, String offset) { checkProject(context.project); List<SchemaField> metadata = getMetadata(context); String columns = Joiner.on(", ").join(projectColumns.map(col -> checkTableColumn(col.getName())) .toArray());
public String sourceTable(Optional<Set<String>> collections) { String predicate = collections.map(e -> "WHERE _collection IN (" + e.stream().map(n -> "'" + checkLiteral(n) + "'").collect(Collectors.joining(", ")) + ")").orElse(""); return format("select _time, total, _collection as collection from materialized.%s %s", EventExplorerListener.tableName(), predicate); }
@Override public synchronized List<SchemaField> getOrCreateCollectionFields(String project, String collection, Set<SchemaField> fields) throws NotExistsException { ValidationUtil.checkCollectionValid(collection); return currentFields; query = format("CREATE TABLE %s.\"%s\".\"%s\" (%s) WITH (temporal_column = '%s') ", config.getColdStorageConnector(), project, collection, queryEnd, checkLiteral(projectConfig.getTimeColumn())); connection.createStatement().execute(query);
public static <T> T checkNotNull(T value, String name) { checkArgument(value != null, name + " is null"); return value; }
Optional<String> dimension, Optional<Integer> period, LocalDate startDate, LocalDate endDate, ZoneId zoneId, boolean approximate) { period.ifPresent(e -> checkArgument(e >= 0, "Period must be 0 or a positive value")); if (approximate) { testDeviceIdExists(firstAction, collections) ? format("coalesce(cast(%s as varchar), _device_id) as %s", projectConfig.getUserColumn(), checkTableColumn(projectConfig.getUserColumn())) : projectConfig.getUserColumn(), dimension, startDate, endDate); String returningActionQuery = generateQuery( collections, context.project, returningAction, testDeviceIdExists(firstAction, collections) ? format("coalesce(cast(%s as varchar), _device_id) as %s", projectConfig.getUserColumn(), checkTableColumn(projectConfig.getUserColumn())) : projectConfig.getUserColumn(), dimension, startDate, endDate);
public static String checkTableColumn(String column, String type, char escape) { if (column == null) { throw new IllegalArgumentException(type + " is null"); } return escape + stripName(column, "field name") + escape; }