Examples with CreateCube io.prestosql.sql.tree.CreateCube used on opensource projects

Example 1 with CreateCube

use of io.prestosql.sql.tree.CreateCube in project hetu-core by openlookeng.

the class TestSqlParser method testCreateCube.

@Test
public void testCreateCube() {
    assertStatement("CREATE CUBE foo ON bar WITH (AGGREGATIONS=(count(c)), GROUP = (a, b)) WHERE d1 > 10", new CreateCube(QualifiedName.of("foo"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c")))), false, ImmutableList.of(), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), null));
    assertStatement("CREATE CUBE foo ON bar WITH (AGGREGATIONS=(count(distinct c), sum(e)), GROUP = (a, b), FILTER = (f between 1 and 10)) WHERE d1 > 10", new CreateCube(QualifiedName.of("foo"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), true, ImmutableList.of(new Identifier("c"))), new FunctionCall(QualifiedName.of("sum"), ImmutableList.of(new Identifier("e")))), false, ImmutableList.of(), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), new BetweenPredicate(new Identifier("f"), new LongLiteral("1"), new LongLiteral("10"))));
    assertStatement("CREATE CUBE foo ON bar WITH (AGGREGATIONS=(count(c), sum(d), avg(e)), GROUP = (a, b)) WHERE d1 > 10", new CreateCube(QualifiedName.of("foo"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c"))), new FunctionCall(QualifiedName.of("sum"), ImmutableList.of(new Identifier("d"))), new FunctionCall(QualifiedName.of("avg"), ImmutableList.of(new Identifier("e"))), new FunctionCall(QualifiedName.of("sum"), ImmutableList.of(new Identifier("e"))), new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("e")))), false, ImmutableList.of(), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), null));
    assertStatement("CREATE CUBE avgtestcube ON bar WITH (AGGREGATIONS=(count(c), sum(d), avg(e), avg(f)), GROUP = (a, b)) WHERE d1 > 10", new CreateCube(QualifiedName.of("avgtestcube"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c"))), new FunctionCall(QualifiedName.of("sum"), ImmutableList.of(new Identifier("d"))), new FunctionCall(QualifiedName.of("avg"), ImmutableList.of(new Identifier("e"))), new FunctionCall(QualifiedName.of("sum"), ImmutableList.of(new Identifier("e"))), new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("e"))), new FunctionCall(QualifiedName.of("avg"), ImmutableList.of(new Identifier("f"))), new FunctionCall(QualifiedName.of("sum"), ImmutableList.of(new Identifier("f"))), new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("f")))), false, ImmutableList.of(), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), null));
    assertStatement("CREATE CUBE c1.s1.foo ON c2.s2.bar WITH (AGGREGATIONS=(count(c)), GROUP = (a, b)) WHERE d1 > 10", new CreateCube(QualifiedName.of("c1", "s1", "foo"), QualifiedName.of("c2", "s2", "bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c")))), false, ImmutableList.of(), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), null));
    assertStatement("CREATE CUBE IF NOT EXISTS foo ON bar WITH (AGGREGATIONS=(count(c)), GROUP = (a, b)) WHERE d1 > 10", new CreateCube(QualifiedName.of("foo"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c")))), true, ImmutableList.of(), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), null));
    assertStatement("CREATE CUBE IF NOT EXISTS foo ON bar WITH (AGGREGATIONS=(count(c)), GROUP = (a, b), format = 'ORC', partitioned_by = ARRAY[ 'd' ]) WHERE d1 > 10", new CreateCube(QualifiedName.of("foo"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c")))), true, ImmutableList.of(new Property(new Identifier("format"), new StringLiteral("ORC")), new Property(new Identifier("partitioned_by"), new ArrayConstructor(ImmutableList.of(new StringLiteral("d"))))), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), null));
    assertStatement("CREATE CUBE IF NOT EXISTS foo ON bar WITH (AGGREGATIONS=(count(c)), GROUP = (a, b), format = 'ORC', partitioned_by = ARRAY[ 'd' ], FILTER = (d2 > 20)) WHERE d1 > 10", new CreateCube(QualifiedName.of("foo"), QualifiedName.of("bar"), ImmutableList.of(new Identifier("a"), new Identifier("b")), ImmutableSet.of(new FunctionCall(QualifiedName.of("count"), ImmutableList.of(new Identifier("c")))), true, ImmutableList.of(new Property(new Identifier("format"), new StringLiteral("ORC")), new Property(new Identifier("partitioned_by"), new ArrayConstructor(ImmutableList.of(new StringLiteral("d"))))), Optional.of(new ComparisonExpression(GREATER_THAN, new Identifier("d1"), new LongLiteral("10"))), new ComparisonExpression(GREATER_THAN, new Identifier("d2"), new LongLiteral("20"))));
}

Example 2 with CreateCube

use of io.prestosql.sql.tree.CreateCube in project hetu-core by openlookeng.

the class CubeConsole method createCubeCommand.

/**
 * Process the Create Cube Query
 *
 * @param queryRunner queryRunner
 * @param outputFormat outputFormat
 * @param schemaChanged schemaChanged
 * @param usePager usePager
 * @param schemaChanged schemaChanged
 * @param showProgress showProgress
 * @param terminal terminal
 * @param out out
 * @param errorChannel errorChannel
 * @return boolean after processing the create cube query command.
 */
public boolean createCubeCommand(String query, QueryRunner queryRunner, ClientOptions.OutputFormat outputFormat, Runnable schemaChanged, boolean usePager, boolean showProgress, Terminal terminal, PrintStream out, PrintStream errorChannel) {
    boolean success = true;
    SqlParser parser = new SqlParser();
    QualifiedName cubeName = null;
    try {
        CreateCube createCube = (CreateCube) parser.createStatement(query, new ParsingOptions(ParsingOptions.DecimalLiteralTreatment.AS_DOUBLE));
        cubeName = createCube.getCubeName();
        QualifiedName sourceTableName = createCube.getSourceTableName();
        String whereClause = createCube.getWhere().get().toString();
        Set<FunctionCall> aggregations = createCube.getAggregations();
        List<Identifier> groupingSet = createCube.getGroupingSet();
        List<Property> properties = createCube.getProperties();
        boolean notExists = createCube.isNotExists();
        CreateCube modifiedCreateCube = new CreateCube(cubeName, sourceTableName, groupingSet, aggregations, notExists, properties, Optional.empty(), createCube.getSourceFilter().orElse(null));
        String queryCreateCube = SqlFormatter.formatSql(modifiedCreateCube, Optional.empty());
        if (!console.runQuery(queryRunner, queryCreateCube, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel)) {
            return false;
        }
        // we check whether the create cube expression can be processed
        if (isSupportedExpression(createCube, queryRunner, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel)) {
            if (createCube.getWhere().get() instanceof BetweenPredicate) {
                // we process the between predicate in the create cube query where clause
                success = processBetweenPredicate(createCube, queryRunner, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel, parser);
            }
            if (createCube.getWhere().get() instanceof ComparisonExpression) {
                // we process the comparison expression in the create cube query where clause
                success = processComparisonExpression(createCube, queryRunner, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel, parser);
            }
        } else {
            // if we donot support processing the create cube query with multiple inserts, then only a single insert is run internally.
            String queryInsert = String.format(INSERT_INTO_CUBE_STRING, cubeName, whereClause);
            success = console.runQuery(queryRunner, queryInsert, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel);
        }
        if (!success) {
            // roll back mechanism for unsuccessful create cube query
            String dropCubeQuery = String.format(DROP_CUBE_STRING, cubeName);
            console.runQuery(queryRunner, dropCubeQuery, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel);
        }
    } catch (ParsingException e) {
        if (cubeName != null) {
            // roll back mechanism for unsuccessful create cube query
            String dropCubeQuery = String.format(DROP_CUBE_STRING, cubeName);
            console.runQuery(queryRunner, dropCubeQuery, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel);
        }
        System.out.println(e.getMessage());
        Query.renderErrorLocation(query, new ErrorLocation(e.getLineNumber(), e.getColumnNumber()), errorChannel);
        success = false;
    } catch (Exception e) {
        if (cubeName != null) {
            // roll back mechanism for unsuccessful create cube query
            String dropCubeQuery = String.format(DROP_CUBE_STRING, cubeName);
            console.runQuery(queryRunner, dropCubeQuery, outputFormat, schemaChanged, usePager, showProgress, terminal, out, errorChannel);
        }
        // Add blank line after progress bar
        System.out.println();
        System.out.println(e.getMessage());
        success = false;
    }
    return success;
}

Example 3 with CreateCube

use of io.prestosql.sql.tree.CreateCube in project hetu-core by openlookeng.

the class CreateCubeTask method internalExecute.

@VisibleForTesting
public ListenableFuture<?> internalExecute(CreateCube statement, Metadata metadata, AccessControl accessControl, Session session, QueryStateMachine stateMachine, List<Expression> parameters) {
    Optional<CubeMetaStore> optionalCubeMetaStore = cubeManager.getMetaStore(STAR_TREE);
    if (!optionalCubeMetaStore.isPresent()) {
        throw new RuntimeException("HetuMetaStore is not initialized");
    }
    QualifiedObjectName cubeName = createQualifiedObjectName(session, statement, statement.getCubeName());
    QualifiedObjectName tableName = createQualifiedObjectName(session, statement, statement.getSourceTableName());
    Optional<TableHandle> cubeHandle = metadata.getTableHandle(session, cubeName);
    Optional<TableHandle> tableHandle = metadata.getTableHandle(session, tableName);
    if (optionalCubeMetaStore.get().getMetadataFromCubeName(cubeName.toString()).isPresent()) {
        if (!statement.isNotExists()) {
            throw new SemanticException(CUBE_ALREADY_EXISTS, statement, "Cube '%s' already exists", cubeName);
        }
        return immediateFuture(null);
    }
    if (cubeHandle.isPresent()) {
        if (!statement.isNotExists()) {
            throw new SemanticException(CUBE_OR_TABLE_ALREADY_EXISTS, statement, "Cube or Table '%s' already exists", cubeName);
        }
        return immediateFuture(null);
    }
    CatalogName catalogName = metadata.getCatalogHandle(session, cubeName.getCatalogName()).orElseThrow(() -> new PrestoException(NOT_FOUND, "Catalog not found: " + cubeName.getCatalogName()));
    if (!metadata.isPreAggregationSupported(session, catalogName)) {
        throw new PrestoException(StandardErrorCode.NOT_SUPPORTED, String.format("Cube cannot be created on catalog '%s'", catalogName.toString()));
    }
    if (!tableHandle.isPresent()) {
        throw new SemanticException(MISSING_TABLE, statement, "Table '%s' does not exist", tableName);
    }
    TableMetadata tableMetadata = metadata.getTableMetadata(session, tableHandle.get());
    List<String> groupingSet = statement.getGroupingSet().stream().map(s -> s.getValue().toLowerCase(ENGLISH)).collect(Collectors.toList());
    Map<String, ColumnMetadata> sourceTableColumns = tableMetadata.getColumns().stream().collect(Collectors.toMap(ColumnMetadata::getName, col -> col));
    List<ColumnMetadata> cubeColumns = new ArrayList<>();
    Map<String, AggregationSignature> aggregations = new HashMap<>();
    Analysis analysis = analyzeStatement(statement, session, metadata, accessControl, parameters, stateMachine.getWarningCollector());
    Map<String, Field> fields = analysis.getOutputDescriptor().getAllFields().stream().collect(Collectors.toMap(col -> col.getName().map(String::toLowerCase).get(), col -> col));
    for (FunctionCall aggFunction : statement.getAggregations()) {
        String aggFunctionName = aggFunction.getName().toString().toLowerCase(ENGLISH);
        String argument = aggFunction.getArguments().isEmpty() || aggFunction.getArguments().get(0) instanceof LongLiteral ? null : ((Identifier) aggFunction.getArguments().get(0)).getValue().toLowerCase(ENGLISH);
        boolean distinct = aggFunction.isDistinct();
        String cubeColumnName = aggFunctionName + "_" + (argument == null ? "all" : argument) + (aggFunction.isDistinct() ? "_distinct" : "");
        CubeAggregateFunction cubeAggregateFunction = CubeAggregateFunction.valueOf(aggFunctionName.toUpperCase(ENGLISH));
        switch(cubeAggregateFunction) {
            case SUM:
                aggregations.put(cubeColumnName, AggregationSignature.sum(argument, distinct));
                break;
            case COUNT:
                AggregationSignature aggregationSignature = argument == null ? AggregationSignature.count() : AggregationSignature.count(argument, distinct);
                aggregations.put(cubeColumnName, aggregationSignature);
                break;
            case AVG:
                aggregations.put(cubeColumnName, AggregationSignature.avg(argument, distinct));
                break;
            case MAX:
                aggregations.put(cubeColumnName, AggregationSignature.max(argument, distinct));
                break;
            case MIN:
                aggregations.put(cubeColumnName, AggregationSignature.min(argument, distinct));
                break;
            default:
                throw new PrestoException(NOT_SUPPORTED, format("Unsupported aggregation function : %s", aggFunctionName));
        }
        Field tableField = fields.get(cubeColumnName);
        ColumnMetadata cubeCol = new ColumnMetadata(cubeColumnName, tableField.getType(), true, null, null, false, Collections.emptyMap());
        cubeColumns.add(cubeCol);
    }
    accessControl.checkCanCreateTable(session.getRequiredTransactionId(), session.getIdentity(), tableName);
    Map<String, Expression> sqlProperties = mapFromProperties(statement.getProperties());
    Map<String, Object> properties = metadata.getTablePropertyManager().getProperties(catalogName, cubeName.getCatalogName(), sqlProperties, session, metadata, parameters);
    if (properties.containsKey("partitioned_by")) {
        List<String> partitionCols = new ArrayList<>(((List<String>) properties.get("partitioned_by")));
        // put all partition columns at the end of the list
        groupingSet.removeAll(partitionCols);
        groupingSet.addAll(partitionCols);
    }
    for (String dimension : groupingSet) {
        if (!sourceTableColumns.containsKey(dimension)) {
            throw new SemanticException(MISSING_COLUMN, statement, "Column %s does not exist", dimension);
        }
        ColumnMetadata tableCol = sourceTableColumns.get(dimension);
        ColumnMetadata cubeCol = new ColumnMetadata(dimension, tableCol.getType(), tableCol.isNullable(), null, null, false, tableCol.getProperties());
        cubeColumns.add(cubeCol);
    }
    ConnectorTableMetadata cubeTableMetadata = new ConnectorTableMetadata(cubeName.asSchemaTableName(), ImmutableList.copyOf(cubeColumns), properties);
    try {
        metadata.createTable(session, cubeName.getCatalogName(), cubeTableMetadata, statement.isNotExists());
    } catch (PrestoException e) {
        // connectors are not required to handle the ignoreExisting flag
        if (!e.getErrorCode().equals(ALREADY_EXISTS.toErrorCode()) || !statement.isNotExists()) {
            throw e;
        }
    }
    CubeMetadataBuilder builder = optionalCubeMetaStore.get().getBuilder(cubeName.toString(), tableName.toString());
    groupingSet.forEach(dimension -> builder.addDimensionColumn(dimension, dimension));
    aggregations.forEach((column, aggregationSignature) -> builder.addAggregationColumn(column, aggregationSignature.getFunction(), aggregationSignature.getDimension(), aggregationSignature.isDistinct()));
    builder.addGroup(new HashSet<>(groupingSet));
    // Status and Table modified time will be updated on the first insert into the cube
    builder.setCubeStatus(CubeStatus.INACTIVE);
    builder.setTableLastUpdatedTime(-1L);
    statement.getSourceFilter().ifPresent(sourceTablePredicate -> {
        sourceTablePredicate = Coercer.addCoercions(sourceTablePredicate, analysis);
        builder.withCubeFilter(new CubeFilter(ExpressionFormatter.formatExpression(sourceTablePredicate, Optional.empty())));
    });
    builder.setCubeLastUpdatedTime(System.currentTimeMillis());
    optionalCubeMetaStore.get().persist(builder.build());
    return immediateFuture(null);
}

Example 4 with CreateCube

use of io.prestosql.sql.tree.CreateCube in project hetu-core by openlookeng.

the class AstBuilder method visitCreateCube.

@Override
public Node visitCreateCube(SqlBaseParser.CreateCubeContext context) {
    if (context.cubeProperties() == null) {
        throw new IllegalArgumentException("Missing properties: AGGREGATIONS, GROUP");
    }
    QualifiedName cubeName = getQualifiedName(context.cubeName);
    QualifiedName sourceTableName = getQualifiedName(context.tableName);
    List<Identifier> groupingSet = ImmutableList.of();
    List<FunctionCall> aggregations = ImmutableList.of();
    List<Property> properties = new ArrayList<>();
    Optional<Expression> optionalExpression = visitIfPresent(context.expression(), Expression.class);
    boolean cubeGroupProvided = false;
    boolean aggregationsProvided = false;
    boolean sourceFilterProvided = false;
    Optional<Expression> sourceFilterPredicate = Optional.empty();
    for (SqlBaseParser.CubePropertyContext propertyContext : context.cubeProperties().cubeProperty()) {
        if (propertyContext.cubeGroup() != null) {
            if (cubeGroupProvided) {
                throw new IllegalArgumentException("Duplicate property: GROUP");
            }
            groupingSet = visit(propertyContext.cubeGroup().identifier(), Identifier.class);
            cubeGroupProvided = true;
        } else if (propertyContext.aggregations() != null) {
            if (aggregationsProvided) {
                throw new IllegalArgumentException("Duplicate property: AGGREGATIONS");
            }
            aggregations = visit(propertyContext.aggregations().expression(), FunctionCall.class);
            aggregationsProvided = true;
        } else if (propertyContext.sourceFilter() != null) {
            if (sourceFilterProvided) {
                throw new IllegalArgumentException("Duplicate Property: FILTER");
            }
            sourceFilterPredicate = visitIfPresent(propertyContext.sourceFilter().expression(), Expression.class);
            sourceFilterProvided = true;
        } else if (propertyContext.property() != null) {
            properties.add((Property) visitProperty(propertyContext.property()));
        }
    }
    if (!cubeGroupProvided) {
        throw new IllegalArgumentException("Missing property: GROUP");
    }
    if (!aggregationsProvided) {
        throw new IllegalArgumentException("Missing property: AGGREGATIONS");
    }
    List<Identifier> decomposedGroupingSet = new ArrayList<>();
    groupingSet.forEach(groupItem -> {
        decomposedGroupingSet.add(new Identifier(groupItem.getLocation().get(), groupItem.getValue().toLowerCase(Locale.ENGLISH), groupItem.isDelimited()));
    });
    Set<FunctionCall> decomposedAggregations = new LinkedHashSet<>();
    aggregations.forEach(aggItem -> {
        List<Expression> listArguments = aggItem.getArguments();
        List<Expression> newArguments = new ArrayList<>();
        for (Expression argument : listArguments) {
            if (argument instanceof Identifier) {
                newArguments.add(new Identifier(argument.getLocation().get(), ((Identifier) argument).getValue().toLowerCase(Locale.ENGLISH), ((Identifier) argument).isDelimited()));
            } else {
                newArguments.add(argument);
            }
        }
        if (!"avg".equals(aggItem.getName().toString())) {
            decomposedAggregations.add(new FunctionCall(aggItem.getLocation(), aggItem.getName(), aggItem.getWindow(), aggItem.getFilter(), aggItem.getOrderBy(), aggItem.isDistinct(), newArguments));
        } else {
            decomposedAggregations.add(aggItem);
            decomposedAggregations.add(new FunctionCall(aggItem.getLocation(), QualifiedName.of("sum"), aggItem.getWindow(), aggItem.getFilter(), aggItem.getOrderBy(), aggItem.isDistinct(), newArguments));
            decomposedAggregations.add(new FunctionCall(aggItem.getLocation(), QualifiedName.of("count"), aggItem.getWindow(), aggItem.getFilter(), aggItem.getOrderBy(), aggItem.isDistinct(), newArguments));
        }
    });
    return new CreateCube(getLocation(context), cubeName, sourceTableName, decomposedGroupingSet, decomposedAggregations, context.EXISTS() != null, properties, optionalExpression, sourceFilterPredicate.orElse(null));
}