@Override protected Void visitCreateTableAsSelect(CreateTableAsSelect node, Integer indent) { builder.append("CREATE TABLE "); if (node.isNotExists()) { builder.append("IF NOT EXISTS "); } builder.append(formatName(node.getName())); if (node.getColumnAliases().isPresent()) { String columnList = node.getColumnAliases().get().stream().map(element -> formatExpression(element, parameters)).collect(joining(", ")); builder.append(format("( %s )", columnList)); } if (node.getComment().isPresent()) { builder.append("\nCOMMENT " + formatStringLiteral(node.getComment().get())); } builder.append(formatPropertiesMultiLine(node.getProperties())); builder.append(" AS "); process(node.getQuery(), indent); if (!node.isWithData()) { builder.append(" WITH NO DATA"); } return null; }
@Test public void testCreateTableAsSelectDifferentCatalog() throws Exception { handle.execute("CREATE TABLE \"my_test_table2\" (column1 BIGINT, column2 DOUBLE)"); SqlParser parser = new SqlParser(); Query query = new Query(CATALOG, SCHEMA, ImmutableList.of(), "CREATE TABLE public.my_test_table2 AS SELECT 1 column1, 2E0 column2", ImmutableList.of(), null, null, ImmutableMap.of()); QueryRewriter rewriter = new QueryRewriter(parser, URL, QualifiedName.of("other_catalog", "other_schema", "tmp_"), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), 1, new Duration(10, SECONDS)); Query rewrittenQuery = rewriter.shadowQuery(query); assertEquals(rewrittenQuery.getPreQueries().size(), 1); CreateTableAsSelect createTableAs = (CreateTableAsSelect) parser.createStatement(rewrittenQuery.getPreQueries().get(0)); assertEquals(createTableAs.getName().getParts().size(), 3); assertEquals(createTableAs.getName().getPrefix().get(), QualifiedName.of("other_catalog", "other_schema")); assertTrue(createTableAs.getName().getSuffix().startsWith("tmp_")); assertFalse(createTableAs.getName().getSuffix().contains("my_test_table")); }
private Query rewriteCreateTableAsSelect(Connection connection, Query query, CreateTableAsSelect statement) throws SQLException, QueryRewriteException { QualifiedName temporaryTableName = generateTemporaryTableName(statement.getName()); Statement rewritten = new CreateTableAsSelect(temporaryTableName, statement.getQuery(), statement.isNotExists(), statement.getProperties(), statement.isWithData(), statement.getColumnAliases(), Optional.empty()); String createTableAsSql = formatSql(rewritten, Optional.empty()); String checksumSql = checksumSql(getColumns(connection, statement), temporaryTableName); String dropTableSql = dropTableSql(temporaryTableName); return new Query(query.getCatalog(), query.getSchema(), ImmutableList.of(createTableAsSql), checksumSql, ImmutableList.of(dropTableSql), query.getUsername(), query.getPassword(), query.getSessionProperties()); }
@Test public void testCreateTableAsSelect() throws Exception { handle.execute("CREATE TABLE \"my_test_table\" (column1 BIGINT, column2 DOUBLE)"); SqlParser parser = new SqlParser(); Query query = new Query(CATALOG, SCHEMA, ImmutableList.of(), "CREATE TABLE my_test_table AS SELECT 1 column1, CAST('2.0' AS DOUBLE) column2 LIMIT 1", ImmutableList.of(), null, null, ImmutableMap.of()); QueryRewriter rewriter = new QueryRewriter(parser, URL, QualifiedName.of("tmp_"), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), 1, new Duration(10, SECONDS)); Query rewrittenQuery = rewriter.shadowQuery(query); assertEquals(rewrittenQuery.getPreQueries().size(), 1); assertEquals(rewrittenQuery.getPostQueries().size(), 1); CreateTableAsSelect createTableAs = (CreateTableAsSelect) parser.createStatement(rewrittenQuery.getPreQueries().get(0)); assertEquals(createTableAs.getName().getParts().size(), 1); assertTrue(createTableAs.getName().getSuffix().startsWith("tmp_")); assertFalse(createTableAs.getName().getSuffix().contains("my_test_table")); assertEquals(statementToQueryType(parser, rewrittenQuery.getQuery()), READ); Table table = new Table(createTableAs.getName()); SingleColumn column1 = new SingleColumn(new FunctionCall(QualifiedName.of("checksum"), ImmutableList.of(new Identifier("COLUMN1")))); SingleColumn column2 = new SingleColumn(new FunctionCall(QualifiedName.of("checksum"), ImmutableList.of(new FunctionCall(QualifiedName.of("round"), ImmutableList.of(new Identifier("COLUMN2"), new LongLiteral("1")))))); Select select = new Select(false, ImmutableList.of(column1, column2)); QuerySpecification querySpecification = new QuerySpecification(select, Optional.of(table), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty()); assertEquals(parser.createStatement(rewrittenQuery.getQuery()), new com.facebook.presto.sql.tree.Query(Optional.empty(), querySpecification, Optional.empty(), Optional.empty())); assertEquals(parser.createStatement(rewrittenQuery.getPostQueries().get(0)), new DropTable(createTableAs.getName(), true)); }
QualifiedObjectName targetTable = createQualifiedObjectName(session, node, node.getName()); analysis.setCreateTableDestination(targetTable);
@Override protected Void visitCreateTableAsSelect(CreateTableAsSelect node, Integer indent) { builder.append("CREATE TABLE "); if (node.isNotExists()) { builder.append("IF NOT EXISTS "); } builder.append(formatName(node.getName())); if (node.getColumnAliases().isPresent()) { String columnList = node.getColumnAliases().get().stream().map(element -> formatExpression(element, tableNameMapper, columnNameMapper, queryWithTables, escapeIdentifier)).collect(joining(", ")); builder.append(format("( %s )", columnList)); } if (node.getComment().isPresent()) { builder.append("\nCOMMENT " + formatStringLiteral(node.getComment().get())); } builder.append(formatProperties(node.getProperties())); builder.append(" AS "); process(node.getQuery(), indent); if (!node.isWithData()) { builder.append(" WITH NO DATA"); } return null; }
@Override protected Void visitCreateTableAsSelect(CreateTableAsSelect node, Integer indent) { builder.append("CREATE TABLE ") .append(node.getName()); if (!node.getProperties().isEmpty()) { builder.append(" WITH ("); Joiner.on(", ").appendTo(builder, transform(node.getProperties().entrySet(), entry -> entry.getKey() + " = " + formatExpression(entry.getValue()))); builder.append(")"); } builder.append(" AS "); process(node.getQuery(), indent); if (!node.isWithData()) { builder.append(" WITH NO DATA"); } return null; }
@Override protected Void visitCreateTableAsSelect(CreateTableAsSelect node, Integer indent) { builder.append("CREATE TABLE "); if (node.isNotExists()) { builder.append("IF NOT EXISTS "); } builder.append(node.getName()); if (!node.getProperties().isEmpty()) { builder.append(" WITH ("); Joiner.on(", ").appendTo(builder, transform(node.getProperties().entrySet(), entry -> entry.getKey() + " = " + formatExpression(entry.getValue()))); builder.append(")"); } builder.append(" AS "); process(node.getQuery(), indent); if (!node.isWithData()) { builder.append(" WITH NO DATA"); } return null; }
@Override protected Void visitCreateTableAsSelect(CreateTableAsSelect node, Integer indent) { builder.append("CREATE TABLE "); if (node.isNotExists()) { builder.append("IF NOT EXISTS "); } builder.append(formatName(node.getName())); if (node.getColumnAliases().isPresent()) { String columnList = node.getColumnAliases().get().stream().map(element -> formatExpression(element, parameters)).collect(joining(", ")); builder.append(format("( %s )", columnList)); } if (node.getComment().isPresent()) { builder.append("\nCOMMENT " + formatStringLiteral(node.getComment().get())); } builder.append(formatPropertiesMultiLine(node.getProperties())); builder.append(" AS "); process(node.getQuery(), indent); if (!node.isWithData()) { builder.append(" WITH NO DATA"); } return null; }
@Override protected RelationType visitCreateTableAsSelect(CreateTableAsSelect node, AnalysisContext context) { analysis.setUpdateType("CREATE TABLE"); // turn this into a query that has a new table writer node on top. QualifiedObjectName targetTable = createQualifiedObjectName(session, node, node.getName()); analysis.setCreateTableDestination(targetTable); Optional<TableHandle> targetTableHandle = metadata.getTableHandle(session, targetTable); if (targetTableHandle.isPresent()) { if (node.isNotExists()) { analysis.setCreateTableAsSelectNoOp(true); analysis.setStatement(node); return new RelationType(Field.newUnqualified("rows", BIGINT)); } throw new SemanticException(TABLE_ALREADY_EXISTS, node, "Destination table '%s' already exists", targetTable); } for (Expression expression : node.getProperties().values()) { // analyze table property value expressions which must be constant createConstantAnalyzer(metadata, session) .analyze(expression, new RelationType(), context); } analysis.setCreateTableProperties(node.getProperties()); accessControl.checkCanCreateTable(session.getRequiredTransactionId(), session.getIdentity(), targetTable); analysis.setCreateTableAsSelectWithData(node.isWithData()); // analyze the query that creates the table RelationType descriptor = process(node.getQuery(), context); analysis.setStatement(node); validateColumns(node, descriptor); return new RelationType(Field.newUnqualified("rows", BIGINT)); }