@Test public void testDropTableIfExists() { assertFalse(getQueryRunner().tableExists(getSession(), "test_drop_if_exists")); assertUpdate("DROP TABLE IF EXISTS test_drop_if_exists"); assertFalse(getQueryRunner().tableExists(getSession(), "test_drop_if_exists")); }
@Test public void testCreateTable() { assertUpdate("CREATE TABLE test_create (a bigint, b double, c varchar)"); assertTrue(getQueryRunner().tableExists(getSession(), "test_create")); assertTableColumnNames("test_create", "a", "b", "c"); assertUpdate("DROP TABLE test_create"); assertFalse(getQueryRunner().tableExists(getSession(), "test_create")); assertQueryFails("CREATE TABLE test_create (a bad_type)", ".* Unknown type 'bad_type' for column 'a'"); assertFalse(getQueryRunner().tableExists(getSession(), "test_create")); assertUpdate("CREATE TABLE test_create_table_if_not_exists (a bigint, b varchar, c double)"); assertTrue(getQueryRunner().tableExists(getSession(), "test_create_table_if_not_exists")); assertTableColumnNames("test_create_table_if_not_exists", "a", "b", "c"); assertUpdate("CREATE TABLE IF NOT EXISTS test_create_table_if_not_exists (d bigint, e varchar)"); assertTrue(getQueryRunner().tableExists(getSession(), "test_create_table_if_not_exists")); assertTableColumnNames("test_create_table_if_not_exists", "a", "b", "c"); assertUpdate("DROP TABLE test_create_table_if_not_exists"); assertFalse(getQueryRunner().tableExists(getSession(), "test_create_table_if_not_exists")); // Test CREATE TABLE LIKE assertUpdate("CREATE TABLE test_create_original (a bigint, b double, c varchar)"); assertTrue(getQueryRunner().tableExists(getSession(), "test_create_original")); assertTableColumnNames("test_create_original", "a", "b", "c"); assertUpdate("CREATE TABLE test_create_like (LIKE test_create_original, d boolean, e varchar)"); assertTrue(getQueryRunner().tableExists(getSession(), "test_create_like")); assertTableColumnNames("test_create_like", "a", "b", "c", "d", "e"); assertUpdate("DROP TABLE test_create_original"); assertFalse(getQueryRunner().tableExists(getSession(), "test_create_original")); assertUpdate("DROP TABLE test_create_like"); assertFalse(getQueryRunner().tableExists(getSession(), "test_create_like")); }
@Test public void testRenameTable() { assertUpdate("CREATE TABLE test_rename AS SELECT 123 x", 1); assertUpdate("ALTER TABLE test_rename RENAME TO test_rename_new"); MaterializedResult materializedRows = computeActual("SELECT x FROM test_rename_new"); assertEquals(getOnlyElement(materializedRows.getMaterializedRows()).getField(0), 123); // provide new table name in uppercase assertUpdate("ALTER TABLE test_rename_new RENAME TO TEST_RENAME"); materializedRows = computeActual("SELECT x FROM test_rename"); assertEquals(getOnlyElement(materializedRows.getMaterializedRows()).getField(0), 123); assertUpdate("DROP TABLE test_rename"); assertFalse(getQueryRunner().tableExists(getSession(), "test_rename")); assertFalse(getQueryRunner().tableExists(getSession(), "test_rename_new")); }
protected void assertCreateTableAsSelect(Session session, String table, @Language("SQL") String query, @Language("SQL") String expectedQuery, @Language("SQL") String rowCountQuery) { assertUpdate(session, "CREATE TABLE " + table + " AS " + query, rowCountQuery); assertQuery(session, "SELECT * FROM " + table, expectedQuery); assertUpdate(session, "DROP TABLE " + table); assertFalse(getQueryRunner().tableExists(session, table)); }
@Test public void testDropTable() { assertUpdate("CREATE TABLE test_drop AS SELECT 123 x", 1); assertTrue(getQueryRunner().tableExists(getSession(), "test_drop")); assertUpdate("DROP TABLE test_drop"); assertFalse(getQueryRunner().tableExists(getSession(), "test_drop")); }
@Test public void testDropTable() { assertUpdate("CREATE TABLE test_drop AS SELECT 123 x", 1); assertTrue(getQueryRunner().tableExists(getSession(), "test_drop")); assertUpdate("DROP TABLE test_drop"); assertFalse(getQueryRunner().tableExists(getSession(), "test_drop")); }
@Test public void testRenameColumn() { assertUpdate("CREATE TABLE test_rename_column AS SELECT 123 x", 1); assertUpdate("ALTER TABLE test_rename_column RENAME COLUMN x TO y"); MaterializedResult materializedRows = computeActual("SELECT y FROM test_rename_column"); assertEquals(getOnlyElement(materializedRows.getMaterializedRows()).getField(0), 123); assertUpdate("ALTER TABLE test_rename_column RENAME COLUMN y TO Z"); materializedRows = computeActual("SELECT z FROM test_rename_column"); assertEquals(getOnlyElement(materializedRows.getMaterializedRows()).getField(0), 123); assertUpdate("DROP TABLE test_rename_column"); assertFalse(getQueryRunner().tableExists(getSession(), "test_rename_column")); }
@Test public void testNameEscaping() { Session session = testSessionBuilder() .setCatalog("mysql") .setSchema("test_database") .build(); assertFalse(getQueryRunner().tableExists(session, "test_table")); assertUpdate(session, "CREATE TABLE test_table AS SELECT 123 x", 1); assertTrue(getQueryRunner().tableExists(session, "test_table")); assertQuery(session, "SELECT * FROM test_table", "SELECT 123"); assertUpdate(session, "DROP TABLE test_table"); assertFalse(getQueryRunner().tableExists(session, "test_table")); }
@Test public void testViews() throws Exception { execute("CREATE OR REPLACE VIEW tpch.test_view AS SELECT * FROM tpch.orders"); assertTrue(getQueryRunner().tableExists(getSession(), "test_view")); assertQuery("SELECT orderkey FROM test_view", "SELECT orderkey FROM orders"); execute("DROP VIEW IF EXISTS tpch.test_view"); }
public void testCreateEmptyBucketedPartition(HiveStorageFormat storageFormat) { String tableName = "test_insert_empty_partitioned_bucketed_table"; createPartitionedBucketedTable(tableName, storageFormat); List<String> orderStatusList = ImmutableList.of("F", "O", "P"); for (int i = 0; i < orderStatusList.size(); i++) { String sql = format("CALL system.create_empty_partition('%s', '%s', ARRAY['orderstatus'], ARRAY['%s'])", TPCH_SCHEMA, tableName, orderStatusList.get(i)); assertUpdate(sql); assertQuery( format("SELECT count(*) FROM \"%s$partitions\"", tableName), "SELECT " + (i + 1)); assertQueryFails(sql, "Partition already exists.*"); } assertUpdate("DROP TABLE " + tableName); assertFalse(getQueryRunner().tableExists(getSession(), tableName)); }
@Test public void testMaterializedView() throws Exception { execute("CREATE MATERIALIZED VIEW tpch.test_mv as SELECT * FROM tpch.orders"); assertTrue(getQueryRunner().tableExists(getSession(), "test_mv")); assertQuery("SELECT orderkey FROM test_mv", "SELECT orderkey FROM orders"); execute("DROP MATERIALIZED VIEW tpch.test_mv"); }
private void testInsertPartitionedBucketedTable(HiveStorageFormat storageFormat) { String tableName = "test_insert_partitioned_bucketed_table"; createPartitionedBucketedTable(tableName, storageFormat); List<String> orderStatusList = ImmutableList.of("F", "O", "P"); for (int i = 0; i < orderStatusList.size(); i++) { String orderStatus = orderStatusList.get(i); assertUpdate( // make sure that we will get one file per bucket regardless of writer count configured getParallelWriteSession(), format( "INSERT INTO " + tableName + " " + "SELECT custkey, custkey AS custkey2, comment, orderstatus " + "FROM tpch.tiny.orders " + "WHERE orderstatus = '%s'", orderStatus), format("SELECT count(*) from orders where orderstatus = '%s'", orderStatus)); } verifyPartitionedBucketedTable(storageFormat, tableName); assertUpdate("DROP TABLE " + tableName); assertFalse(getQueryRunner().tableExists(getSession(), tableName)); }
@Test public void testForeignTable() throws Exception { execute("CREATE SERVER devnull FOREIGN DATA WRAPPER file_fdw"); execute("CREATE FOREIGN TABLE tpch.test_ft (x bigint) SERVER devnull OPTIONS (filename '/dev/null')"); assertTrue(getQueryRunner().tableExists(getSession(), "test_ft")); computeActual("SELECT * FROM test_ft"); execute("DROP FOREIGN TABLE tpch.test_ft"); execute("DROP SERVER devnull"); }
@Test public void testDeleteFromUnpartitionedTable() { assertUpdate("CREATE TABLE test_delete_unpartitioned AS SELECT orderstatus FROM tpch.tiny.orders", "SELECT count(*) from orders"); assertUpdate("DELETE FROM test_delete_unpartitioned"); MaterializedResult result = computeActual("SELECT * from test_delete_unpartitioned"); assertEquals(result.getRowCount(), 0); assertUpdate("DROP TABLE test_delete_unpartitioned"); assertFalse(getQueryRunner().tableExists(getSession(), "test_delete_unpartitioned")); }
@Override public void testCreateTableAsSelect() { // This test is overridden due to Function "UUID" not found errors // Some test cases from the base class are removed // TODO some test cases from overridden method succeed to create table, but with wrong number or rows. assertUpdate("CREATE TABLE test_create_table_as_if_not_exists (a bigint, b double)"); assertTrue(getQueryRunner().tableExists(getSession(), "test_create_table_as_if_not_exists")); assertTableColumnNames("test_create_table_as_if_not_exists", "a", "b"); assertUpdate("CREATE TABLE IF NOT EXISTS test_create_table_as_if_not_exists AS SELECT UUID() AS uuid, orderkey, discount FROM lineitem", 0); assertTrue(getQueryRunner().tableExists(getSession(), "test_create_table_as_if_not_exists")); assertTableColumnNames("test_create_table_as_if_not_exists", "a", "b"); assertUpdate("DROP TABLE test_create_table_as_if_not_exists"); assertFalse(getQueryRunner().tableExists(getSession(), "test_create_table_as_if_not_exists")); this.assertCreateTableAsSelect( "test_group", "SELECT orderstatus, sum(totalprice) x FROM orders GROUP BY orderstatus", "SELECT count(DISTINCT orderstatus) FROM orders"); this.assertCreateTableAsSelect( "test_with_data", "SELECT * FROM orders WITH DATA", "SELECT * FROM orders", "SELECT count(*) FROM orders"); this.assertCreateTableAsSelect( "test_with_no_data", "SELECT * FROM orders WITH NO DATA", "SELECT * FROM orders LIMIT 0", "SELECT 0"); }
private void testCreatePartitionedTableAs(Session session, HiveStorageFormat storageFormat) { @Language("SQL") String createTable = "" + "CREATE TABLE test_create_partitioned_table_as " + "WITH (" + "format = '" + storageFormat + "', " + "partitioned_by = ARRAY[ 'SHIP_PRIORITY', 'ORDER_STATUS' ]" + ") " + "AS " + "SELECT orderkey AS order_key, shippriority AS ship_priority, orderstatus AS order_status " + "FROM tpch.tiny.orders"; assertUpdate(session, createTable, "SELECT count(*) from orders"); TableMetadata tableMetadata = getTableMetadata(catalog, TPCH_SCHEMA, "test_create_partitioned_table_as"); assertEquals(tableMetadata.getMetadata().getProperties().get(STORAGE_FORMAT_PROPERTY), storageFormat); assertEquals(tableMetadata.getMetadata().getProperties().get(PARTITIONED_BY_PROPERTY), ImmutableList.of("ship_priority", "order_status")); List<?> partitions = getPartitions("test_create_partitioned_table_as"); assertEquals(partitions.size(), 3); assertQuery(session, "SELECT * from test_create_partitioned_table_as", "SELECT orderkey, shippriority, orderstatus FROM orders"); assertUpdate(session, "DROP TABLE test_create_partitioned_table_as"); assertFalse(getQueryRunner().tableExists(session, "test_create_partitioned_table_as")); }
assertFalse(getQueryRunner().tableExists(session, tableName));
private void testCreatePartitionedBucketedTableAs(HiveStorageFormat storageFormat) { String tableName = "test_create_partitioned_bucketed_table_as"; @Language("SQL") String createTable = "" + "CREATE TABLE " + tableName + " " + "WITH (" + "format = '" + storageFormat + "', " + "partitioned_by = ARRAY[ 'orderstatus' ], " + "bucketed_by = ARRAY[ 'custkey', 'custkey2' ], " + "bucket_count = 11 " + ") " + "AS " + "SELECT custkey, custkey AS custkey2, comment, orderstatus " + "FROM tpch.tiny.orders"; assertUpdate( // make sure that we will get one file per bucket regardless of writer count configured getParallelWriteSession(), createTable, "SELECT count(*) from orders"); verifyPartitionedBucketedTable(storageFormat, tableName); assertUpdate("DROP TABLE " + tableName); assertFalse(getQueryRunner().tableExists(getSession(), tableName)); }
private void testInsertPartitionedBucketedTableFewRows(Session session, HiveStorageFormat storageFormat) { String tableName = "test_insert_partitioned_bucketed_table_few_rows"; assertUpdate(session, "" + "CREATE TABLE " + tableName + " (" + " bucket_key varchar," + " col varchar," + " partition_key varchar)" + "WITH (" + "format = '" + storageFormat + "', " + "partitioned_by = ARRAY[ 'partition_key' ], " + "bucketed_by = ARRAY[ 'bucket_key' ], " + "bucket_count = 11)"); assertUpdate( // make sure that we will get one file per bucket regardless of writer count configured getParallelWriteSession(), "INSERT INTO " + tableName + " " + "VALUES " + " (VARCHAR 'a', VARCHAR 'b', VARCHAR 'c'), " + " ('aa', 'bb', 'cc'), " + " ('aaa', 'bbb', 'ccc')", 3); verifyPartitionedBucketedTableAsFewRows(storageFormat, tableName); assertThatThrownBy(() -> assertUpdate(session, "INSERT INTO test_insert_partitioned_bucketed_table_few_rows VALUES ('a0', 'b0', 'c')", 1)) .hasMessage(getExpectedErrorMessageForInsertExistingBucketedTable( getInsertExistingPartitionsBehavior(getConnectorSession(session)), "partition_key=c")); assertUpdate(session, "DROP TABLE test_insert_partitioned_bucketed_table_few_rows"); assertFalse(getQueryRunner().tableExists(session, tableName)); }
private void testCreatePartitionedBucketedTableAsWithUnionAll(HiveStorageFormat storageFormat) { String tableName = "test_create_partitioned_bucketed_table_as_with_union_all"; @Language("SQL") String createTable = "" + "CREATE TABLE " + tableName + " " + "WITH (" + "format = '" + storageFormat + "', " + "partitioned_by = ARRAY[ 'orderstatus' ], " + "bucketed_by = ARRAY[ 'custkey', 'custkey2' ], " + "bucket_count = 11 " + ") " + "AS " + "SELECT custkey, custkey AS custkey2, comment, orderstatus " + "FROM tpch.tiny.orders " + "WHERE length(comment) % 2 = 0 " + "UNION ALL " + "SELECT custkey, custkey AS custkey2, comment, orderstatus " + "FROM tpch.tiny.orders " + "WHERE length(comment) % 2 = 1"; assertUpdate( // make sure that we will get one file per bucket regardless of writer count configured getParallelWriteSession(), createTable, "SELECT count(*) from orders"); verifyPartitionedBucketedTable(storageFormat, tableName); assertUpdate("DROP TABLE " + tableName); assertFalse(getQueryRunner().tableExists(getSession(), tableName)); }