Examples with CreateTableAsSelect - io.prestosql.sql.tree.CreateTableAsSelect

Example 1 with CreateTableAsSelect

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

the class TestSqlParser method testCreateTableAsWith.

@Test
public void testCreateTableAsWith() {
    String queryParenthesizedWith = "CREATE TABLE foo " + "AS " + "( WITH t(x) AS (VALUES 1) " + "TABLE t ) " + "WITH NO DATA";
    String queryUnparenthesizedWith = "CREATE TABLE foo " + "AS " + "WITH t(x) AS (VALUES 1) " + "TABLE t " + "WITH NO DATA";
    String queryParenthesizedWithHasAlias = "CREATE TABLE foo(a) " + "AS " + "( WITH t(x) AS (VALUES 1) " + "TABLE t ) " + "WITH NO DATA";
    String queryUnparenthesizedWithHasAlias = "CREATE TABLE foo(a) " + "AS " + "WITH t(x) AS (VALUES 1) " + "TABLE t " + "WITH NO DATA";
    QualifiedName table = QualifiedName.of("foo");
    Query query = new Query(Optional.of(new With(false, ImmutableList.of(new WithQuery(identifier("t"), query(new Values(ImmutableList.of(new LongLiteral("1")))), Optional.of(ImmutableList.of(identifier("x"))))))), new Table(QualifiedName.of("t")), Optional.empty(), Optional.empty(), Optional.empty());
    assertStatement(queryParenthesizedWith, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.empty(), Optional.empty()));
    assertStatement(queryUnparenthesizedWith, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.empty(), Optional.empty()));
    assertStatement(queryParenthesizedWithHasAlias, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.of(ImmutableList.of(new Identifier("a"))), Optional.empty()));
    assertStatement(queryUnparenthesizedWithHasAlias, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.of(ImmutableList.of(new Identifier("a"))), Optional.empty()));
}

Also used : Table(io.prestosql.sql.tree.Table) CreateTable(io.prestosql.sql.tree.CreateTable) VacuumTable(io.prestosql.sql.tree.VacuumTable) DropTable(io.prestosql.sql.tree.DropTable) RenameTable(io.prestosql.sql.tree.RenameTable) Identifier(io.prestosql.sql.tree.Identifier) QueryUtil.quotedIdentifier(io.prestosql.sql.QueryUtil.quotedIdentifier) QueryUtil.simpleQuery(io.prestosql.sql.QueryUtil.simpleQuery) Query(io.prestosql.sql.tree.Query) WithQuery(io.prestosql.sql.tree.WithQuery) LongLiteral(io.prestosql.sql.tree.LongLiteral) WithQuery(io.prestosql.sql.tree.WithQuery) QualifiedName(io.prestosql.sql.tree.QualifiedName) CreateTableAsSelect(io.prestosql.sql.tree.CreateTableAsSelect) Values(io.prestosql.sql.tree.Values) With(io.prestosql.sql.tree.With) Test(org.testng.annotations.Test)

Example 2 with CreateTableAsSelect

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

the class TestSqlParser method testCreateTableAsSelect.

@Test
public void testCreateTableAsSelect() {
    Query query = simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("t")));
    Query querySelectColumn = simpleQuery(selectList(new Identifier("a")), table(QualifiedName.of("t")));
    Query querySelectColumns = simpleQuery(selectList(new Identifier("a"), new Identifier("b")), table(QualifiedName.of("t")));
    QualifiedName table = QualifiedName.of("foo");
    assertStatement("CREATE TABLE foo AS SELECT * FROM t", new CreateTableAsSelect(table, query, false, ImmutableList.of(), true, Optional.empty(), Optional.empty()));
    assertStatement("CREATE TABLE foo(x) AS SELECT a FROM t", new CreateTableAsSelect(table, querySelectColumn, false, ImmutableList.of(), true, Optional.of(ImmutableList.of(new Identifier("x"))), Optional.empty()));
    assertStatement("CREATE TABLE foo(x,y) AS SELECT a,b FROM t", new CreateTableAsSelect(table, querySelectColumns, false, ImmutableList.of(), true, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.empty()));
    assertStatement("CREATE TABLE IF NOT EXISTS foo AS SELECT * FROM t", new CreateTableAsSelect(table, query, true, ImmutableList.of(), true, Optional.empty(), Optional.empty()));
    assertStatement("CREATE TABLE IF NOT EXISTS foo(x) AS SELECT a FROM t", new CreateTableAsSelect(table, querySelectColumn, true, ImmutableList.of(), true, Optional.of(ImmutableList.of(new Identifier("x"))), Optional.empty()));
    assertStatement("CREATE TABLE IF NOT EXISTS foo(x,y) AS SELECT a,b FROM t", new CreateTableAsSelect(table, querySelectColumns, true, ImmutableList.of(), true, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.empty()));
    assertStatement("CREATE TABLE foo AS SELECT * FROM t WITH NO DATA", new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.empty(), Optional.empty()));
    assertStatement("CREATE TABLE foo(x) AS SELECT a FROM t WITH NO DATA", new CreateTableAsSelect(table, querySelectColumn, false, ImmutableList.of(), false, Optional.of(ImmutableList.of(new Identifier("x"))), Optional.empty()));
    assertStatement("CREATE TABLE foo(x,y) AS SELECT a,b FROM t WITH NO DATA", new CreateTableAsSelect(table, querySelectColumns, false, ImmutableList.of(), false, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.empty()));
    List<Property> properties = ImmutableList.of(new Property(new Identifier("string"), new StringLiteral("bar")), new Property(new Identifier("long"), new LongLiteral("42")), new Property(new Identifier("computed"), new FunctionCall(QualifiedName.of("concat"), ImmutableList.of(new StringLiteral("ban"), new StringLiteral("ana")))), new Property(new Identifier("a"), new ArrayConstructor(ImmutableList.of(new StringLiteral("v1"), new StringLiteral("v2")))));
    assertStatement("CREATE TABLE foo " + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT * FROM t", new CreateTableAsSelect(table, query, false, properties, true, Optional.empty(), Optional.empty()));
    assertStatement("CREATE TABLE foo(x) " + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a FROM t", new CreateTableAsSelect(table, querySelectColumn, false, properties, true, Optional.of(ImmutableList.of(new Identifier("x"))), Optional.empty()));
    assertStatement("CREATE TABLE foo(x,y) " + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a,b FROM t", new CreateTableAsSelect(table, querySelectColumns, false, properties, true, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.empty()));
    assertStatement("CREATE TABLE foo " + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT * FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, query, false, properties, false, Optional.empty(), Optional.empty()));
    assertStatement("CREATE TABLE foo(x) " + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, querySelectColumn, false, properties, false, Optional.of(ImmutableList.of(new Identifier("x"))), Optional.empty()));
    assertStatement("CREATE TABLE foo(x,y) " + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a,b FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, querySelectColumns, false, properties, false, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.empty()));
    assertStatement("CREATE TABLE foo COMMENT 'test'" + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT * FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, query, false, properties, false, Optional.empty(), Optional.of("test")));
    assertStatement("CREATE TABLE foo(x) COMMENT 'test'" + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, querySelectColumn, false, properties, false, Optional.of(ImmutableList.of(new Identifier("x"))), Optional.of("test")));
    assertStatement("CREATE TABLE foo(x,y) COMMENT 'test'" + "WITH ( string = 'bar', long = 42, computed = 'ban' || 'ana', a  = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a,b FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, querySelectColumns, false, properties, false, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.of("test")));
    assertStatement("CREATE TABLE foo(x,y) COMMENT 'test'" + "WITH ( \"string\" = 'bar', \"long\" = 42, computed = 'ban' || 'ana', a = ARRAY[ 'v1', 'v2' ] ) " + "AS " + "SELECT a,b FROM t " + "WITH NO DATA", new CreateTableAsSelect(table, querySelectColumns, false, properties, false, Optional.of(ImmutableList.of(new Identifier("x"), new Identifier("y"))), Optional.of("test")));
}

Also used : Identifier(io.prestosql.sql.tree.Identifier) QueryUtil.quotedIdentifier(io.prestosql.sql.QueryUtil.quotedIdentifier) QueryUtil.simpleQuery(io.prestosql.sql.QueryUtil.simpleQuery) Query(io.prestosql.sql.tree.Query) WithQuery(io.prestosql.sql.tree.WithQuery) StringLiteral(io.prestosql.sql.tree.StringLiteral) LongLiteral(io.prestosql.sql.tree.LongLiteral) QualifiedName(io.prestosql.sql.tree.QualifiedName) CreateTableAsSelect(io.prestosql.sql.tree.CreateTableAsSelect) ArrayConstructor(io.prestosql.sql.tree.ArrayConstructor) AllColumns(io.prestosql.sql.tree.AllColumns) FunctionCall(io.prestosql.sql.tree.FunctionCall) Property(io.prestosql.sql.tree.Property) Test(org.testng.annotations.Test)

Example 3 with CreateTableAsSelect

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

the class QueryRewriter method rewriteCreateTableAsSelect.

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());
}

Also used : Statement(io.prestosql.sql.tree.Statement) QualifiedName(io.prestosql.sql.tree.QualifiedName) CreateTableAsSelect(io.prestosql.sql.tree.CreateTableAsSelect)

Example 4 with CreateTableAsSelect

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

the class TestShadowing method testCreateTableAsSelectDifferentCatalog.

@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"));
}

Also used : CreateTableAsSelect(io.prestosql.sql.tree.CreateTableAsSelect) SqlParser(io.prestosql.sql.parser.SqlParser) Duration(io.airlift.units.Duration) Test(org.testng.annotations.Test)

Example 5 with CreateTableAsSelect

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

the class TestShadowing method testCreateTableAsSelect.

@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(), Optional.empty());
    assertEquals(parser.createStatement(rewrittenQuery.getQuery()), new io.prestosql.sql.tree.Query(Optional.empty(), querySpecification, Optional.empty(), Optional.empty(), Optional.empty()));
    assertEquals(parser.createStatement(rewrittenQuery.getPostQueries().get(0)), new DropTable(createTableAs.getName(), true));
}

Also used : Table(io.prestosql.sql.tree.Table) DropTable(io.prestosql.sql.tree.DropTable) CreateTable(io.prestosql.sql.tree.CreateTable) LongLiteral(io.prestosql.sql.tree.LongLiteral) SqlParser(io.prestosql.sql.parser.SqlParser) Duration(io.airlift.units.Duration) SingleColumn(io.prestosql.sql.tree.SingleColumn) DropTable(io.prestosql.sql.tree.DropTable) QuerySpecification(io.prestosql.sql.tree.QuerySpecification) Identifier(io.prestosql.sql.tree.Identifier) CreateTableAsSelect(io.prestosql.sql.tree.CreateTableAsSelect) Select(io.prestosql.sql.tree.Select) CreateTableAsSelect(io.prestosql.sql.tree.CreateTableAsSelect) FunctionCall(io.prestosql.sql.tree.FunctionCall) Test(org.testng.annotations.Test)

Aggregations

CreateTableAsSelect (io.prestosql.sql.tree.CreateTableAsSelect)9 Identifier (io.prestosql.sql.tree.Identifier)5 Test (org.testng.annotations.Test)4 CreateTable (io.prestosql.sql.tree.CreateTable)3 LongLiteral (io.prestosql.sql.tree.LongLiteral)3 Property (io.prestosql.sql.tree.Property)3 QualifiedName (io.prestosql.sql.tree.QualifiedName)3 StringLiteral (io.prestosql.sql.tree.StringLiteral)3 ArrayList (java.util.ArrayList)3 Duration (io.airlift.units.Duration)2 QueryUtil.quotedIdentifier (io.prestosql.sql.QueryUtil.quotedIdentifier)2 QueryUtil.simpleQuery (io.prestosql.sql.QueryUtil.simpleQuery)2 SqlParser (io.prestosql.sql.parser.SqlParser)2 ArithmeticBinaryExpression (io.prestosql.sql.tree.ArithmeticBinaryExpression)2 ArithmeticUnaryExpression (io.prestosql.sql.tree.ArithmeticUnaryExpression)2 ArrayConstructor (io.prestosql.sql.tree.ArrayConstructor)2 CoalesceExpression (io.prestosql.sql.tree.CoalesceExpression)2 ComparisonExpression (io.prestosql.sql.tree.ComparisonExpression)2 DereferenceExpression (io.prestosql.sql.tree.DereferenceExpression)2 DropTable (io.prestosql.sql.tree.DropTable)2