[ https://issues.apache.org/jira/browse/BEAM-5151?focusedWorklogId=145824&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-145824 ]
ASF GitHub Bot logged work on BEAM-5151: ---------------------------------------- Author: ASF GitHub Bot Created on: 19/Sep/18 21:40 Start Date: 19/Sep/18 21:40 Worklog Time Spent: 10m Work Description: akedin closed pull request #6252: [BEAM-5151][SQL] create external table URL: https://github.com/apache/beam/pull/6252 This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineIT.java b/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineIT.java index 7d83a6de0da..0169fe1c418 100644 --- a/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineIT.java +++ b/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineIT.java @@ -70,7 +70,7 @@ public static void setUpClass() { setProject = String.format("SET project = '%s';", project); createPubsubTableStatement = - "CREATE TABLE taxi_rides (\n" + "CREATE EXTERNAL TABLE taxi_rides (\n" + " event_timestamp TIMESTAMP,\n" + " attributes MAP<VARCHAR, VARCHAR>,\n" + " payload ROW<\n" diff --git a/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineTest.java b/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineTest.java index d14bc863efa..34bae8dc0f0 100644 --- a/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineTest.java +++ b/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/BeamSqlLineTest.java @@ -63,7 +63,7 @@ public void testSqlLine_parse() throws Exception { public void testSqlLine_ddl() throws Exception { BeamSqlLine.main( new String[] { - "-e", "CREATE TABLE test (id INTEGER) TYPE 'text';", "-e", "DROP TABLE test;" + "-e", "CREATE EXTERNAL TABLE test (id INTEGER) TYPE 'text';", "-e", "DROP TABLE test;" }); } @@ -74,7 +74,7 @@ public void classLoader_readFile() throws Exception { BeamSqlLine.main( new String[] { "-e", - "CREATE TABLE test (id INTEGER) TYPE 'text' LOCATION '" + "CREATE EXTERNAL TABLE test (id INTEGER) TYPE 'text' LOCATION '" + simpleTable.getAbsolutePath() + "';", "-e", @@ -102,7 +102,7 @@ public void testSqlLine_selectFromTable() throws Exception { ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); String[] args = buildArgs( - "CREATE TABLE table_test (col_a VARCHAR, col_b VARCHAR, " + "CREATE EXTERNAL TABLE table_test (col_a VARCHAR, col_b VARCHAR, " + "col_c VARCHAR, col_x TINYINT, col_y INT, col_z BIGINT) TYPE 'test';", "INSERT INTO table_test VALUES ('a', 'b', 'c', 1, 2, 3);", "SELECT * FROM table_test;"); @@ -122,7 +122,7 @@ public void testSqlLine_insertSelect() throws Exception { ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); String[] args = buildArgs( - "CREATE TABLE table_test (col_a VARCHAR, col_b VARCHAR) TYPE 'test';", + "CREATE EXTERNAL TABLE table_test (col_a VARCHAR, col_b VARCHAR) TYPE 'test';", "INSERT INTO table_test SELECT '3', 'hello';", "SELECT * FROM table_test;"); @@ -138,7 +138,7 @@ public void testSqlLine_GroupBy() throws Exception { ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); String[] args = buildArgs( - "CREATE TABLE table_test (col_a VARCHAR, col_b VARCHAR) TYPE 'test';", + "CREATE EXTERNAL TABLE table_test (col_a VARCHAR, col_b VARCHAR) TYPE 'test';", "INSERT INTO table_test SELECT '3', 'foo';", "INSERT INTO table_test SELECT '3', 'bar';", "INSERT INTO table_test SELECT '4', 'foo';", @@ -157,7 +157,7 @@ public void testSqlLine_fixedWindow() throws Exception { ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); String[] args = buildArgs( - "CREATE TABLE table_test (col_a VARCHAR, col_b TIMESTAMP) TYPE 'test';", + "CREATE EXTERNAL TABLE table_test (col_a VARCHAR, col_b TIMESTAMP) TYPE 'test';", "INSERT INTO table_test SELECT '3', TIMESTAMP '2018-07-01 21:26:06';", "INSERT INTO table_test SELECT '3', TIMESTAMP '2018-07-01 21:26:07';", "SELECT TUMBLE_START(col_b, INTERVAL '1' SECOND), count(*) FROM table_test " @@ -177,7 +177,7 @@ public void testSqlLine_slidingWindow() throws Exception { ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); String[] args = buildArgs( - "CREATE TABLE table_test (col_a VARCHAR, col_b TIMESTAMP) TYPE 'test';", + "CREATE EXTERNAL TABLE table_test (col_a VARCHAR, col_b TIMESTAMP) TYPE 'test';", "INSERT INTO table_test SELECT '3', TIMESTAMP '2018-07-01 21:26:06';", "INSERT INTO table_test SELECT '4', TIMESTAMP '2018-07-01 21:26:07';", "INSERT INTO table_test SELECT '6', TIMESTAMP '2018-07-01 21:26:08';", diff --git a/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/JdbcJarTest.java b/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/JdbcJarTest.java index dd9fb21cc2a..dfd757dd5fa 100644 --- a/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/JdbcJarTest.java +++ b/sdks/java/extensions/sql/jdbc/src/test/java/org/apache/beam/sdk/extensions/sql/jdbc/JdbcJarTest.java @@ -71,7 +71,7 @@ public void classLoader_parse() throws Exception { public void classLoader_ddl() throws Exception { Connection connection = getConnection(); Statement statement = connection.createStatement(); - assertEquals(0, statement.executeUpdate("CREATE TABLE test (id INTEGER) TYPE 'text'")); + assertEquals(0, statement.executeUpdate("CREATE EXTERNAL TABLE test (id INTEGER) TYPE 'text'")); assertEquals(0, statement.executeUpdate("DROP TABLE test")); } @@ -84,7 +84,7 @@ public void classLoader_readFile() throws Exception { assertEquals( 0, statement.executeUpdate( - "CREATE TABLE test (id INTEGER) TYPE 'text' LOCATION '" + "CREATE EXTERNAL TABLE test (id INTEGER) TYPE 'text' LOCATION '" + simpleTable.getAbsolutePath() + "'")); assertTrue(statement.execute("SELECT * FROM test")); diff --git a/sdks/java/extensions/sql/src/main/codegen/config.fmpp b/sdks/java/extensions/sql/src/main/codegen/config.fmpp index f9fb117ce80..2758740af3c 100644 --- a/sdks/java/extensions/sql/src/main/codegen/config.fmpp +++ b/sdks/java/extensions/sql/src/main/codegen/config.fmpp @@ -24,7 +24,7 @@ data: { "org.apache.calcite.schema.ColumnStrategy" "org.apache.calcite.sql.SqlCreate" "org.apache.calcite.sql.SqlDrop" - "org.apache.beam.sdk.extensions.sql.impl.parser.SqlCreateTable" + "org.apache.beam.sdk.extensions.sql.impl.parser.SqlCreateExternalTable" "org.apache.beam.sdk.extensions.sql.impl.parser.SqlDdlNodes" "org.apache.beam.sdk.extensions.sql.impl.parser.SqlSetOptionBeam" "org.apache.beam.sdk.schemas.Schema" @@ -56,6 +56,7 @@ data: { # List of methods for parsing custom SQL statements. statementParserMethods: [ "SqlSetOptionBeam(Span.of(), null)" + "SqlCreateExternalTable()" ] # List of methods for parsing custom literals. @@ -76,7 +77,7 @@ data: { # List of methods for parsing extensions to "CREATE [OR REPLACE]" calls. # Each must accept arguments "(SqlParserPos pos, boolean replace)". createStatementParserMethods: [ - "SqlCreateTable" + SqlCreateTableNotSupportedMessage ] # List of methods for parsing extensions to "DROP" calls. diff --git a/sdks/java/extensions/sql/src/main/codegen/includes/parserImpls.ftl b/sdks/java/extensions/sql/src/main/codegen/includes/parserImpls.ftl index 66ac4964066..0f9b2d1db5e 100644 --- a/sdks/java/extensions/sql/src/main/codegen/includes/parserImpls.ftl +++ b/sdks/java/extensions/sql/src/main/codegen/includes/parserImpls.ftl @@ -146,8 +146,10 @@ Schema.Field Field() : * ( LOCATION location_string )? * ( TBLPROPERTIES tbl_properties )? */ -SqlCreate SqlCreateTable(Span s, boolean replace) : +SqlCreate SqlCreateExternalTable() : { + final Span s = Span.of(); + final boolean replace = false; final boolean ifNotExists; final SqlIdentifier id; List<Schema.Field> fieldList = null; @@ -157,7 +159,12 @@ SqlCreate SqlCreateTable(Span s, boolean replace) : SqlNode tblProperties = null; } { - <TABLE> ifNotExists = IfNotExistsOpt() + + <CREATE> <EXTERNAL> <TABLE> { + s.add(this); + } + + ifNotExists = IfNotExistsOpt() id = CompoundIdentifier() fieldList = FieldListParens() <TYPE> @@ -171,7 +178,7 @@ SqlCreate SqlCreateTable(Span s, boolean replace) : [ <TBLPROPERTIES> tblProperties = StringLiteral() ] { return - new SqlCreateTable( + new SqlCreateExternalTable( s.end(this), replace, ifNotExists, @@ -184,6 +191,17 @@ SqlCreate SqlCreateTable(Span s, boolean replace) : } } +SqlCreate SqlCreateTableNotSupportedMessage(Span s, boolean replace) : +{ +} +{ + <TABLE> + { + throw new ParseException("'CREATE TABLE' is not supported in BeamSQL. You can use " + + "'CREATE EXTERNAL TABLE' to register an external data source to BeamSQL"); + } +} + SqlDrop SqlDropTable(Span s, boolean replace) : { final boolean ifExists; diff --git a/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/parser/SqlCreateTable.java b/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/parser/SqlCreateExternalTable.java similarity index 93% rename from sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/parser/SqlCreateTable.java rename to sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/parser/SqlCreateExternalTable.java index a41579a2065..4c6149b280f 100644 --- a/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/parser/SqlCreateTable.java +++ b/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/parser/SqlCreateExternalTable.java @@ -41,8 +41,8 @@ import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.util.Pair; -/** Parse tree for {@code CREATE TABLE} statement. */ -public class SqlCreateTable extends SqlCreate implements SqlExecutableStatement { +/** Parse tree for {@code CREATE EXTERNAL TABLE} statement. */ +public class SqlCreateExternalTable extends SqlCreate implements SqlExecutableStatement { private final SqlIdentifier name; private final List<Schema.Field> columnList; private final SqlNode type; @@ -51,10 +51,10 @@ private final SqlNode tblProperties; private static final SqlOperator OPERATOR = - new SqlSpecialOperator("CREATE TABLE", SqlKind.CREATE_TABLE); + new SqlSpecialOperator("CREATE EXTERNAL TABLE", SqlKind.OTHER_DDL); - /** Creates a SqlCreateTable. */ - public SqlCreateTable( + /** Creates a SqlCreateExternalTable. */ + public SqlCreateExternalTable( SqlParserPos pos, boolean replace, boolean ifNotExists, @@ -82,6 +82,7 @@ public SqlCreateTable( @Override public void unparse(SqlWriter writer, int leftPrec, int rightPrec) { writer.keyword("CREATE"); + writer.keyword("EXTERNAL"); writer.keyword("TABLE"); if (ifNotExists) { writer.keyword("IF NOT EXISTS"); @@ -161,4 +162,4 @@ private Table toTable() { } } -// End SqlCreateTable.java +// End SqlCreateExternalTable.java diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlCliTest.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlCliTest.java index 54f5f4b42ac..0bde1eae7ed 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlCliTest.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlCliTest.java @@ -45,7 +45,7 @@ public void testExecute_createTextTable() throws Exception { BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age') \n" @@ -69,7 +69,7 @@ public void testExecute_createTableWithPrefixArrayField() throws Exception { BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age', \n" @@ -99,7 +99,7 @@ public void testExecute_createTableWithPrefixMapField() throws Exception { BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age', \n" @@ -131,7 +131,7 @@ public void testExecute_createTableWithRowField() throws Exception { BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age', \n" @@ -182,7 +182,7 @@ public void testExecute_dropTable() throws Exception { BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age') \n" @@ -203,7 +203,7 @@ public void testExecute_dropTable_assertTableRemovedFromPlanner() throws Excepti BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age') \n" @@ -221,7 +221,7 @@ public void testExplainQuery() throws Exception { BeamSqlCli cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age') \n" diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlExplainTest.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlExplainTest.java index cf5b9b37f23..2a476ce9740 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlExplainTest.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlExplainTest.java @@ -39,7 +39,7 @@ public void setUp() throws SqlParseException, RelConversionException, Validation cli = new BeamSqlCli().metaStore(metaStore); cli.execute( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name', \n" + "age int COMMENT 'age') \n" @@ -47,14 +47,14 @@ public void setUp() throws SqlParseException, RelConversionException, Validation + "COMMENT '' "); cli.execute( - "create table A (\n" + "CREATE EXTERNAL TABLE A (\n" + "c1 int COMMENT 'c1',\n" + "c2 int COMMENT 'c2')\n" + "TYPE 'text'\n" + "COMMENT '' "); cli.execute( - "create table B (\n" + "CREATE EXTERNAL TABLE B (\n" + "c1 int COMMENT 'c1',\n" + "c2 int COMMENT 'c2')\n" + "TYPE 'text'\n" diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/PubsubToBigqueryIT.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/PubsubToBigqueryIT.java index 0d7d6d1d845..8ced72c0556 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/PubsubToBigqueryIT.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/PubsubToBigqueryIT.java @@ -59,7 +59,7 @@ public void testSimpleInsert() throws Exception { BeamSqlEnv.inMemory(new PubsubJsonTableProvider(), new BigQueryTableProvider()); String createTableString = - "CREATE TABLE pubsub_topic (\n" + "CREATE EXTERNAL TABLE pubsub_topic (\n" + "event_timestamp TIMESTAMP, \n" + "attributes MAP<VARCHAR, VARCHAR>, \n" + "payload ROW< \n" @@ -75,7 +75,7 @@ public void testSimpleInsert() throws Exception { sqlEnv.executeDdl(createTableString); String createTableStatement = - "CREATE TABLE bq_table( \n" + "CREATE EXTERNAL TABLE bq_table( \n" + " id BIGINT, \n" + " name VARCHAR \n " + ") \n" diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/JdbcDriverTest.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/JdbcDriverTest.java index fcf529c3d58..8eec1b6bfef 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/JdbcDriverTest.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/JdbcDriverTest.java @@ -164,9 +164,9 @@ public void testDriverManager_ddl() throws Exception { ResultSet resultSet = metadata.getTables(null, null, null, new String[] {"TABLE"}); assertFalse(resultSet.next()); - // Create tables + // create external tables Statement statement = connection.createStatement(); - assertEquals(0, statement.executeUpdate("CREATE TABLE test (id INTEGER) TYPE 'text'")); + assertEquals(0, statement.executeUpdate("CREATE EXTERNAL TABLE test (id INTEGER) TYPE 'text'")); // Ensure table test resultSet = metadata.getTables(null, null, null, new String[] {"TABLE"}); @@ -174,7 +174,6 @@ public void testDriverManager_ddl() throws Exception { assertEquals("test", resultSet.getString("TABLE_NAME")); assertFalse(resultSet.next()); - // Create tables assertEquals(0, statement.executeUpdate("DROP TABLE test")); // Ensure no tables @@ -189,7 +188,7 @@ public void testSelectsFromExistingTable() throws Exception { connection .createStatement() - .executeUpdate("CREATE TABLE person (id BIGINT, name VARCHAR) TYPE 'test'"); + .executeUpdate("CREATE EXTERNAL TABLE person (id BIGINT, name VARCHAR) TYPE 'test'"); tableProvider.addRows("person", row(1L, "aaa"), row(2L, "bbb")); @@ -212,7 +211,9 @@ public void testTimestampWithDefaultTimezone() throws Exception { // A table with one TIMESTAMP column Schema schema = Schema.builder().addDateTimeField("ts").build(); - connection.createStatement().executeUpdate("CREATE TABLE test (ts TIMESTAMP) TYPE 'test'"); + connection + .createStatement() + .executeUpdate("CREATE EXTERNAL TABLE test (ts TIMESTAMP) TYPE 'test'"); ReadableInstant july1 = ISODateTimeFormat.dateTimeParser().parseDateTime("2018-07-01T01:02:03Z"); @@ -241,7 +242,9 @@ public void testTimestampWithNonzeroTimezone() throws Exception { // A table with one TIMESTAMP column Schema schema = Schema.builder().addDateTimeField("ts").build(); - connection.createStatement().executeUpdate("CREATE TABLE test (ts TIMESTAMP) TYPE 'test'"); + connection + .createStatement() + .executeUpdate("CREATE EXTERNAL TABLE test (ts TIMESTAMP) TYPE 'test'"); ReadableInstant july1 = ISODateTimeFormat.dateTimeParser().parseDateTime("2018-07-01T01:02:03Z"); @@ -269,7 +272,9 @@ public void testTimestampWithZeroTimezone() throws Exception { // A table with one TIMESTAMP column Schema schema = Schema.builder().addDateTimeField("ts").build(); - connection.createStatement().executeUpdate("CREATE TABLE test (ts TIMESTAMP) TYPE 'test'"); + connection + .createStatement() + .executeUpdate("CREATE EXTERNAL TABLE test (ts TIMESTAMP) TYPE 'test'"); ReadableInstant july1 = ISODateTimeFormat.dateTimeParser().parseDateTime("2018-07-01T01:02:03Z"); @@ -297,7 +302,7 @@ public void testSelectsFromExistingComplexTable() throws Exception { connection .createStatement() .executeUpdate( - "CREATE TABLE person ( \n" + "CREATE EXTERNAL TABLE person ( \n" + "description VARCHAR, \n" + "nestedRow ROW< \n" + " id BIGINT, \n" @@ -331,11 +336,11 @@ public void testInsertIntoCreatedTable() throws Exception { connection .createStatement() - .executeUpdate("CREATE TABLE person (id BIGINT, name VARCHAR) TYPE 'test'"); + .executeUpdate("CREATE EXTERNAL TABLE person (id BIGINT, name VARCHAR) TYPE 'test'"); connection .createStatement() - .executeUpdate("CREATE TABLE person_src (id BIGINT, name VARCHAR) TYPE 'test'"); + .executeUpdate("CREATE EXTERNAL TABLE person_src (id BIGINT, name VARCHAR) TYPE 'test'"); tableProvider.addRows("person_src", row(1L, "aaa"), row(2L, "bbb")); connection.createStatement().execute("INSERT INTO person SELECT id, name FROM person_src"); diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLNestedTypesTest.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLNestedTypesTest.java index 134223eda42..acc7633fa4f 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLNestedTypesTest.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLNestedTypesTest.java @@ -71,7 +71,7 @@ public void supportsPrimitiveTypes(@From(PrimitiveTypes.class) FieldType fieldTy private Table executeCreateTableWith(String fieldType) throws SqlParseException { String createTable = - "create table tablename ( " + "CREATE EXTERNAL TABLE tablename ( " + "fieldName " + fieldType + " ) " diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLTest.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLTest.java index c3b83449112..bf77c14dd58 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLTest.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/impl/parser/BeamDDLTest.java @@ -38,7 +38,7 @@ public class BeamDDLTest { @Test - public void testParseCreateTable_full() throws Exception { + public void testParseCreateExternalTable_full() throws Exception { TestTableProvider tableProvider = new TestTableProvider(); BeamSqlEnv env = BeamSqlEnv.withTableProvider(tableProvider); @@ -49,7 +49,7 @@ public void testParseCreateTable_full() throws Exception { properties.put("hello", hello); env.executeDdl( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name') \n" + "TYPE 'text' \n" @@ -63,10 +63,10 @@ public void testParseCreateTable_full() throws Exception { } @Test(expected = ParseException.class) - public void testParseCreateTable_withoutType() throws Exception { + public void testParseCreateExternalTable_withoutType() throws Exception { BeamSqlEnv env = BeamSqlEnv.withTableProvider(new TestTableProvider()); env.executeDdl( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name') \n" + "COMMENT 'person table' \n" @@ -74,8 +74,21 @@ public void testParseCreateTable_withoutType() throws Exception { + "TBLPROPERTIES '{\"hello\": [\"james\", \"bond\"]}'"); } + @Test(expected = ParseException.class) + public void testParseCreateTable() throws Exception { + BeamSqlEnv env = BeamSqlEnv.withTableProvider(new TestTableProvider()); + env.executeDdl( + "CREATE TABLE person (\n" + + "id int COMMENT 'id', \n" + + "name varchar COMMENT 'name') \n" + + "TYPE 'text' \n" + + "COMMENT 'person table' \n" + + "LOCATION '/home/admin/person'\n" + + "TBLPROPERTIES '{\"hello\": [\"james\", \"bond\"]}'"); + } + @Test - public void testParseCreateTable_withoutTableComment() throws Exception { + public void testParseCreateExternalTable_withoutTableComment() throws Exception { TestTableProvider tableProvider = new TestTableProvider(); BeamSqlEnv env = BeamSqlEnv.withTableProvider(tableProvider); @@ -86,7 +99,7 @@ public void testParseCreateTable_withoutTableComment() throws Exception { properties.put("hello", hello); env.executeDdl( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name') \n" + "TYPE 'text' \n" @@ -97,12 +110,12 @@ public void testParseCreateTable_withoutTableComment() throws Exception { } @Test - public void testParseCreateTable_withoutTblProperties() throws Exception { + public void testParseCreateExternalTable_withoutTblProperties() throws Exception { TestTableProvider tableProvider = new TestTableProvider(); BeamSqlEnv env = BeamSqlEnv.withTableProvider(tableProvider); env.executeDdl( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name') \n" + "TYPE 'text' \n" @@ -114,12 +127,12 @@ public void testParseCreateTable_withoutTblProperties() throws Exception { } @Test - public void testParseCreateTable_withoutLocation() throws Exception { + public void testParseCreateExternalTable_withoutLocation() throws Exception { TestTableProvider tableProvider = new TestTableProvider(); BeamSqlEnv env = BeamSqlEnv.withTableProvider(tableProvider); env.executeDdl( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name') \n" + "TYPE 'text' \n" @@ -131,11 +144,11 @@ public void testParseCreateTable_withoutLocation() throws Exception { } @Test - public void testParseCreateTable_minimal() throws Exception { + public void testParseCreateExternalTable_minimal() throws Exception { TestTableProvider tableProvider = new TestTableProvider(); BeamSqlEnv env = BeamSqlEnv.withTableProvider(tableProvider); - env.executeDdl("CREATE TABLE person (id INT) TYPE text"); + env.executeDdl("CREATE EXTERNAL TABLE person (id INT) TYPE text"); assertEquals( Table.builder() @@ -156,7 +169,7 @@ public void testParseDropTable() throws Exception { assertNull(tableProvider.getTables().get("person")); env.executeDdl( - "create table person (\n" + "CREATE EXTERNAL TABLE person (\n" + "id int COMMENT 'id', \n" + "name varchar COMMENT 'name') \n" + "TYPE 'text' \n" diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/integrationtest/BeamSqlBuiltinFunctionsIntegrationTestBase.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/integrationtest/BeamSqlBuiltinFunctionsIntegrationTestBase.java index 28b5c630b01..ccd1f210601 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/integrationtest/BeamSqlBuiltinFunctionsIntegrationTestBase.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/integrationtest/BeamSqlBuiltinFunctionsIntegrationTestBase.java @@ -362,7 +362,9 @@ private void checkJdbc() throws Exception { // Here we create a Beam table just to force the calling convention. TestTableProvider tableProvider = new TestTableProvider(); Connection connection = JdbcDriver.connect(tableProvider); - connection.createStatement().executeUpdate("CREATE TABLE dummy (dummy BOOLEAN) TYPE 'test'"); + connection + .createStatement() + .executeUpdate("CREATE EXTERNAL TABLE dummy (dummy BOOLEAN) TYPE 'test'"); tableProvider.addRows("dummy", DUMMY_ROW); for (String expr : exprs) { diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/bigquery/BigQueryReadWriteIT.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/bigquery/BigQueryReadWriteIT.java index e863a5acba0..98225693e7e 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/bigquery/BigQueryReadWriteIT.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/bigquery/BigQueryReadWriteIT.java @@ -93,7 +93,7 @@ public void testSQLRead() { BeamSqlEnv sqlEnv = BeamSqlEnv.inMemory(new BigQueryTableProvider()); String createTableStatement = - "CREATE TABLE TEST( \n" + "CREATE EXTERNAL TABLE TEST( \n" + " c_bigint BIGINT, \n" + " c_tinyint TINYINT, \n" + " c_smallint SMALLINT, \n" @@ -159,7 +159,7 @@ public void testSQLTypes() { BeamSqlEnv sqlEnv = BeamSqlEnv.inMemory(new BigQueryTableProvider()); String createTableStatement = - "CREATE TABLE TEST( \n" + "CREATE EXTERNAL TABLE TEST( \n" + " c_bigint BIGINT, \n" + " c_tinyint TINYINT, \n" + " c_smallint SMALLINT, \n" @@ -227,7 +227,7 @@ public void testInsertSelect() throws Exception { new BigQueryTableProvider()); String createTableStatement = - "CREATE TABLE ORDERS_BQ( \n" + "CREATE EXTERNAL TABLE ORDERS_BQ( \n" + " id BIGINT, \n" + " name VARCHAR, \n " + " arr ARRAY<VARCHAR> \n" diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/pubsub/PubsubJsonIT.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/pubsub/PubsubJsonIT.java index 6e8a803be6a..351abe9d4d1 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/pubsub/PubsubJsonIT.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/pubsub/PubsubJsonIT.java @@ -105,7 +105,7 @@ @Test public void testSelectsPayloadContent() throws Exception { String createTableString = - "CREATE TABLE message (\n" + "CREATE EXTERNAL TABLE message (\n" + "event_timestamp TIMESTAMP, \n" + "attributes MAP<VARCHAR, VARCHAR>, \n" + "payload ROW< \n" @@ -166,7 +166,7 @@ public void testSelectsPayloadContent() throws Exception { @Test public void testUsesDlq() throws Exception { String createTableString = - "CREATE TABLE message (\n" + "CREATE EXTERNAL TABLE message (\n" + "event_timestamp TIMESTAMP, \n" + "attributes MAP<VARCHAR, VARCHAR>, \n" + "payload ROW< \n" @@ -256,7 +256,7 @@ public void testUsesDlq() throws Exception { @Test public void testSQLLimit() throws Exception { String createTableString = - "CREATE TABLE message (\n" + "CREATE EXTERNAL TABLE message (\n" + "event_timestamp TIMESTAMP, \n" + "attributes MAP<VARCHAR, VARCHAR>, \n" + "payload ROW< \n" diff --git a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/text/TextTableProviderTest.java b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/text/TextTableProviderTest.java index 1c1c27922e5..205718787fb 100644 --- a/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/text/TextTableProviderTest.java +++ b/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/meta/provider/text/TextTableProviderTest.java @@ -68,7 +68,7 @@ protected void after() {} private static final String SINGLE_STRING_SQL_SCHEMA = "(f_string VARCHAR)"; /** - * Tests {@code CREATE TABLE TYPE text} with no format reads a default CSV. + * Tests {@code CREATE EXTERNAL TABLE TYPE text} with no format reads a default CSV. * * <p>The default format ignores empty lines, so that is an important part of this test. */ @@ -81,7 +81,7 @@ public void testLegacyDefaultCsv() throws Exception { BeamSqlEnv env = BeamSqlEnv.inMemory(new TextTableProvider()); env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s/*'", + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s/*'", SQL_CSV_SCHEMA, tempFolder.getRoot())); PCollection<Row> rows = @@ -95,8 +95,8 @@ public void testLegacyDefaultCsv() throws Exception { } /** - * Tests {@code CREATE TABLE TYPE text} with a format other than "csv" or "lines" results in a CSV - * read of that format. + * Tests {@code CREATE EXTERNAL TABLE TYPE text} with a format other than "csv" or "lines" results + * in a CSV read of that format. */ @Test public void testLegacyTdfCsv() throws Exception { @@ -107,7 +107,7 @@ public void testLegacyTdfCsv() throws Exception { BeamSqlEnv env = BeamSqlEnv.inMemory(new TextTableProvider()); env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s/*' TBLPROPERTIES '{\"format\":\"TDF\"}'", + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s/*' TBLPROPERTIES '{\"format\":\"TDF\"}'", SQL_CSV_SCHEMA, tempFolder.getRoot())); PCollection<Row> rows = @@ -128,7 +128,10 @@ public void testLegacyTdfCsv() throws Exception { pipeline.run(); } - /** Tests {@code CREATE TABLE TYPE text TBLPROPERTIES '{"format":"csv"}'} works as expected. */ + /** + * Tests {@code CREATE EXTERNAL TABLE TYPE text TBLPROPERTIES '{"format":"csv"}'} works as + * expected. + */ @Test public void testExplicitCsv() throws Exception { Files.write( @@ -138,7 +141,7 @@ public void testExplicitCsv() throws Exception { BeamSqlEnv env = BeamSqlEnv.inMemory(new TextTableProvider()); env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s/*' TBLPROPERTIES '{\"format\":\"csv\"}'", + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s/*' TBLPROPERTIES '{\"format\":\"csv\"}'", SQL_CSV_SCHEMA, tempFolder.getRoot())); PCollection<Row> rows = @@ -152,8 +155,8 @@ public void testExplicitCsv() throws Exception { } /** - * Tests {@code CREATE TABLE TYPE text TBLPROPERTIES '{"format":"csv", "csvFormat": "Excel"}'} - * works as expected. + * Tests {@code CREATE EXTERNAL TABLE TYPE text TBLPROPERTIES '{"format":"csv", "csvFormat": + * "Excel"}'} works as expected. * * <p>Not that the different with "Excel" format is that blank lines are not ignored but have a * single string field. @@ -166,7 +169,7 @@ public void testExplicitCsvExcel() throws Exception { BeamSqlEnv env = BeamSqlEnv.inMemory(new TextTableProvider()); env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s/*' " + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s/*' " + "TBLPROPERTIES '{\"format\":\"csv\", \"csvFormat\":\"Excel\"}'", SINGLE_STRING_SQL_SCHEMA, tempFolder.getRoot())); @@ -180,7 +183,10 @@ public void testExplicitCsvExcel() throws Exception { pipeline.run(); } - /** Tests {@code CREATE TABLE TYPE text TBLPROPERTIES '{"format":"lines"}'} works as expected. */ + /** + * Tests {@code CREATE EXTERNAL TABLE TYPE text TBLPROPERTIES '{"format":"lines"}'} works as + * expected. + */ @Test public void testLines() throws Exception { // Data that looks like CSV but isn't parsed as it @@ -190,7 +196,7 @@ public void testLines() throws Exception { BeamSqlEnv env = BeamSqlEnv.inMemory(new TextTableProvider()); env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s/*' TBLPROPERTIES '{\"format\":\"lines\"}'", + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s/*' TBLPROPERTIES '{\"format\":\"lines\"}'", SQL_LINES_SCHEMA, tempFolder.getRoot())); PCollection<Row> rows = @@ -209,7 +215,7 @@ public void testWriteLines() throws Exception { BeamSqlEnv env = BeamSqlEnv.inMemory(new TextTableProvider()); env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s' TBLPROPERTIES '{\"format\":\"lines\"}'", + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s' TBLPROPERTIES '{\"format\":\"lines\"}'", SQL_LINES_SCHEMA, destinationFile.getAbsolutePath())); BeamSqlRelUtils.toPCollection( @@ -230,7 +236,7 @@ public void testWriteCsv() throws Exception { // NumberedShardedFile env.executeDdl( String.format( - "CREATE TABLE test %s TYPE text LOCATION '%s' TBLPROPERTIES '{\"format\":\"csv\"}'", + "CREATE EXTERNAL TABLE test %s TYPE text LOCATION '%s' TBLPROPERTIES '{\"format\":\"csv\"}'", SQL_CSV_SCHEMA, destinationFile.getAbsolutePath())); BeamSqlRelUtils.toPCollection( ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking ------------------- Worklog Id: (was: 145824) Time Spent: 4h (was: 3h 50m) > Add EXTERNAL to CREATE TABLE statement > -------------------------------------- > > Key: BEAM-5151 > URL: https://issues.apache.org/jira/browse/BEAM-5151 > Project: Beam > Issue Type: Improvement > Components: dsl-sql > Reporter: Rui Wang > Assignee: Rui Wang > Priority: Major > Time Spent: 4h > Remaining Estimate: 0h > > BeamSQL allows [CREATE > TABLE|https://beam.apache.org/documentation/dsls/sql/create-table/] > statements to register virtual tables from external storage systems (e.g. > BigQuery). > > BeamSQL is not a storage system, so any table registered by "CREATE TABLE" > statement is essentially equivalent to be registered by "CREATE EXTERNAL > TABLE", which requires the user to provide a LOCATION and BeamSQL will > register the table outside of current execution environment based on LOCATION. > > So I propose to add EXTERNAL keyword to "CREATE TABLE" in BeamSQL to help > users understand they are registering tables, and BeamSQL does not create non > existing tables by running CREATE TABLE (at least on some storage systems, if > not all). > > We can make the EXTERNAL keyword either required or optional. > > If we make the EXTERNAL keyword required: > > Pros: > a. We can get rid of the registering table semantic on CREATE TABLE. > b, We keep the room that we could add CREATE TABLE back in the future if we > want CREATE TABLE to create, rather than not only register tables in BeamSQL. > > Cons: > 1. CREATE TABLE syntax will not be supported so existing BeamSQL pipelines > which has CREATE TABLE require changes. > 2. It's required to type tedious EXTERNAL keyword every time, especially in > SQL Shell. > > If we make the EXTERNAL keyword optional, we will have reversed pros and cons > above. -- This message was sent by Atlassian JIRA (v7.6.3#76005)