From issues-return-3091-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Sat Dec 1 20:54:06 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id D3E79180789 for ; Sat, 1 Dec 2018 20:54:05 +0100 (CET) Received: (qmail 75017 invoked by uid 500); 1 Dec 2018 19:54:05 -0000 Mailing-List: contact issues-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list issues@phoenix.apache.org Received: (qmail 75008 invoked by uid 99); 1 Dec 2018 19:54:05 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 01 Dec 2018 19:54:04 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 9B143CD6B2 for ; Sat, 1 Dec 2018 19:54:04 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -110.301 X-Spam-Level: X-Spam-Status: No, score=-110.301 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, RCVD_IN_DNSWL_MED=-2.3, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id Xw1iMk4tNR2n for ; Sat, 1 Dec 2018 19:54:02 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTP id A10D8610DF for ; Sat, 1 Dec 2018 19:54:01 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id D21B7E0360 for ; Sat, 1 Dec 2018 19:54:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 8E5F424DD5 for ; Sat, 1 Dec 2018 19:54:00 +0000 (UTC) Date: Sat, 1 Dec 2018 19:54:00 +0000 (UTC) From: "ASF GitHub Bot (JIRA)" To: issues@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (PHOENIX-5025) Tool to clean up orphan views MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/PHOENIX-5025?page=3Dcom.atlassi= an.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D16= 705974#comment-16705974 ]=20 ASF GitHub Bot commented on PHOENIX-5025: ----------------------------------------- Github user kadirozde commented on a diff in the pull request: https://github.com/apache/phoenix/pull/404#discussion_r238074583 =20 --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/OrphanVie= wToolIT.java --- @@ -0,0 +1,456 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or imp= lied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.phoenix.end2end; + +import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.LINK_TYP= E; +import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.SYSTEM_C= ATALOG_NAME; +import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.SYSTEM_C= HILD_LINK_NAME; +import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TABLE_NA= ME; +import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TABLE_SC= HEM; +import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TABLE_TY= PE; +import static org.apache.phoenix.util.PhoenixRuntime.TENANT_ID_ATTRIB; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +import java.io.IOException; +import java.nio.file.Files; +import java.nio.file.Paths; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Arrays; +import java.util.Collection; +import java.util.List; + +import com.google.common.collect.Lists; +import org.apache.hadoop.conf.Configuration; +import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData; +import org.apache.phoenix.mapreduce.OrphanViewTool; +import org.apache.phoenix.schema.PTable; +import org.apache.phoenix.schema.PTableType; +import org.apache.phoenix.util.SchemaUtil; +import org.junit.Test; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; +import org.junit.runners.Parameterized.Parameters; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +@RunWith(Parameterized.class) +public class OrphanViewToolIT extends ParallelStatsDisabledIT { + private static final Logger LOG =3D LoggerFactory.getLogger(Orphan= ViewToolIT.class); + + private final boolean isMultiTenant; + private final boolean columnEncoded; + + private static final int fanout =3D 2; + + private static final String filePath =3D "/tmp/"; + private static final String fileName =3D "/tmp/OrphanView.txt"; + + protected static String SCHEMA1 =3D "SCHEMA1"; + protected static String SCHEMA2 =3D "SCHEMA2"; + protected static String SCHEMA3 =3D "SCHEMA3"; + protected static String SCHEMA4 =3D "SCHEMA4"; + + private final String TENANT_SPECIFIC_URL =3D getUrl() + ';' + TENA= NT_ID_ATTRIB + "=3Dtenant"; + + public OrphanViewToolIT(boolean isMultiTenant, boolean columnEncod= ed) { + this.isMultiTenant =3D isMultiTenant; + this.columnEncoded =3D columnEncoded; + } + + @Parameters(name=3D"OrphanViewToolIT_multiTenant=3D{0}, columnEnco= ded=3D{1}") // name is used by failsafe as file name in reports + public static Collection data() { + return Arrays.asList(new Boolean[][] { + { false, false }, { false, true }, + { true, false }, { true, true } }); + } + + private String generateDDL(String format) { + return generateDDL("", format); + } + + private String generateDDL(String options, String format) { + StringBuilder optionsBuilder =3D new StringBuilder(options); + if (!columnEncoded) { + if (optionsBuilder.length()!=3D0) + optionsBuilder.append(","); + optionsBuilder.append("COLUMN_ENCODED_BYTES=3D0"); + } + if (isMultiTenant) { + if (optionsBuilder.length()!=3D0) + optionsBuilder.append(","); + optionsBuilder.append("MULTI_TENANT=3Dtrue"); + } + return String.format(format, isMultiTenant ? "TENANT_ID VARCHA= R NOT NULL, " : "", + isMultiTenant ? "TENANT_ID, " : "", optionsBuilder.toS= tring()); + } + + private void deleteRows(Connection connection, String systemTableN= ame, String schemaName) throws SQLException { + String delete =3D "DELETE FROM " + systemTableName + + " WHERE " + TABLE_SCHEM + " =3D '" + schemaName + "'"; + connection.createStatement().execute(delete); + } + + private void deleteAllRows(Connection connection, String baseTable= Schema, + String childViewSchemaName, + String grandchildViewSchemaName, String gr= andGrandChildViewSchemaName) throws SQLException { + deleteRows(connection, SYSTEM_CATALOG_NAME, baseTableSchema); + deleteRows(connection, SYSTEM_CATALOG_NAME, childViewSchemaNam= e); + deleteRows(connection, SYSTEM_CATALOG_NAME, grandchildViewSche= maName); + deleteRows(connection, SYSTEM_CATALOG_NAME, grandGrandChildVie= wSchemaName); + deleteRows(connection, SYSTEM_CHILD_LINK_NAME, baseTableSchema= ); + deleteRows(connection, SYSTEM_CHILD_LINK_NAME, childViewSchema= Name); + deleteRows(connection, SYSTEM_CHILD_LINK_NAME, grandchildViewS= chemaName); + deleteRows(connection, SYSTEM_CHILD_LINK_NAME, grandGrandChild= ViewSchemaName); + + connection.commit(); + } + private void createBaseTableAndViews(Connection baseTableConnectio= n, String baseTableFullName, + Connection viewConnection, St= ring childViewSchemaName, + String grandchildViewSchemaNa= me, String grandGrandChildViewSchemaName) + throws SQLException { + String ddlFormat =3D + "CREATE TABLE IF NOT EXISTS " + baseTableFullName + " = (" + + " %s PK2 VARCHAR NOT NULL, V1 VARCHAR, V2 VA= RCHAR " + + " CONSTRAINT NAME_PK PRIMARY KEY (%s PK2)" += " ) %s"; + baseTableConnection.createStatement().execute(generateDDL(ddlF= ormat)); + // Create a view tree (i.e., tree of views) with depth of 3 + for (int i =3D 0; i < fanout; i++) { + String childView =3D SchemaUtil.getTableName(childViewSche= maName, generateUniqueName()); + String childViewDDL =3D "CREATE VIEW " + childView + " AS = SELECT * FROM " + baseTableFullName; + viewConnection.createStatement().execute(childViewDDL); + for (int j =3D 0; j < fanout; j++) { + String grandchildView =3D SchemaUtil.getTableName(gran= dchildViewSchemaName, generateUniqueName()); + String grandchildViewDDL =3D "CREATE VIEW " + grandchi= ldView + " AS SELECT * FROM " + childView; + viewConnection.createStatement().execute(grandchildVie= wDDL); + for (int k =3D 0; k < fanout; k++) { + viewConnection.createStatement().execute("CREATE V= IEW " + + SchemaUtil.getTableName(grandGrandChildVie= wSchemaName, generateUniqueName()) + + " AS SELECT * FROM " + grandchildView); + } + } + } + } + + private long getLineCount() throws IOException { + return Files.lines(Paths.get(fileName)).count(); + } + + @Test + public void testCreateTableAndViews() throws Exception { + String baseTableName =3D generateUniqueName(); + String baseTableFullName =3D SchemaUtil.getTableName(SCHEMA1, = baseTableName); + try (Connection connection =3D DriverManager.getConnection(get= Url()); + Connection viewConnection =3D + isMultiTenant ? DriverManager.getConnection(TENAN= T_SPECIFIC_URL) : connection) { + createBaseTableAndViews(connection, baseTableFullName, vie= wConnection, SCHEMA2, SCHEMA3, SCHEMA4); + // Run the orphan view tool to drop orphan views but no vi= ew should be dropped + runOrphanViewTool(true, false, true, false); + assertTrue(getLineCount() =3D=3D 0); + // Verify that the views we have created are still in the = system catalog table + String viewQuery =3D "SELECT COUNT(*) FROM " + + SYSTEM_CATALOG_NAME + + " WHERE " + TABLE_TYPE + " =3D '" + PTableType.VIE= W.getSerializedValue() + "'"; + ResultSet rs =3D connection.createStatement().executeQuery= (viewQuery); + assertTrue(rs.next()); + assertTrue(rs.getLong(1) =3D=3D fanout + fanout * fanout += fanout * fanout * fanout); + deleteAllRows(connection, SCHEMA1, SCHEMA2, SCHEMA3, SCHEM= A4); + } + } + + private void verifyNoChildLink(Connection connection, String viewS= chemaName) throws Exception { + // Verify that there there is no link in the system child link= table + String childLinkQuery =3D "SELECT COUNT(*) FROM " + SYSTEM_CHI= LD_LINK_NAME + + " WHERE " + TABLE_SCHEM + " =3D '" + viewSchemaName + = "' AND " + + LINK_TYPE + " =3D " + PTable.LinkType.CHILD_TABLE.getS= erializedValue(); + ResultSet rs =3D connection.createStatement().executeQuery(chi= ldLinkQuery); + assertTrue(rs.next()); + assertTrue(rs.getLong(1) =3D=3D 0); + } + + private void verifyNoViewNoLink(Connection connection, String view= SchemaName) throws Exception { + // Verify that the views and links have been removed from the = system catalog table + String catalogQuery =3D "SELECT COUNT(*) FROM " + SYSTEM_CATAL= OG_NAME + + " WHERE " + TABLE_SCHEM + " =3D '" + viewSchemaName + = "'"; + ResultSet rs =3D connection.createStatement().executeQuery(cat= alogQuery); + assertTrue(rs.next()); + long count =3D rs.getLong(1); + assertTrue(rs.getLong(1) =3D=3D 0); + } + + @Test + public void testDeleteBaseTableRows() throws Exception { + String baseTableName =3D generateUniqueName(); + String baseTableFullName =3D SchemaUtil.getTableName(SCHEMA1, = baseTableName); + try (Connection connection =3D DriverManager.getConnection(get= Url()); + Connection viewConnection =3D + isMultiTenant ? DriverManager.getConnection(TENAN= T_SPECIFIC_URL) : connection) { + createBaseTableAndViews(connection, baseTableFullName, vie= wConnection, SCHEMA2, SCHEMA2, SCHEMA2); + // Delete the base table rows from the system catalog + String deleteTableRows =3D "DELETE FROM " + SYSTEM_CATALOG= _NAME + + " WHERE " + TABLE_SCHEM + " =3D '" + SCHEMA1 + "' = AND " + + TABLE_NAME + " =3D '" + baseTableName + "' AND " + + TABLE_TYPE + " =3D '" + PTableType.TABLE.getSerial= izedValue() + "'"; + connection.createStatement().execute(deleteTableRows); + connection.commit(); + // Verify that the views we have created are still in the = system catalog table + String viewQuery =3D "SELECT COUNT(*) FROM " + SYSTEM_CATA= LOG_NAME + + " WHERE " + TABLE_TYPE + " =3D '" + PTableType.VIE= W.getSerializedValue() + "'"; + ResultSet rs =3D connection.createStatement().executeQuery= (viewQuery); + assertTrue(rs.next()); + long count =3D rs.getLong(1); --- End diff -- =20 I will remove it > Tool to clean up orphan views > ----------------------------- > > Key: PHOENIX-5025 > URL: https://issues.apache.org/jira/browse/PHOENIX-5025 > Project: Phoenix > Issue Type: New Feature > Reporter: Kadir OZDEMIR > Assignee: Kadir OZDEMIR > Priority: Major > > A view without its base table is an orphan view. Since views are virtual = tables and their data is stored in their base tables, they are useless when= they become orphan. A base table can have child views, grandchild views an= d so on. Due to some reasons/bugs, when a base table was dropped, its views= were not not properly cleaned up in the past. For example, the drop table = code did not support cleaning up grandchild views. This has been recently f= ixed by PHOENIX-4764.=C2=A0Although=C2=A0PHOENIX-4764 prevents new orphan v= iews=C2=A0due to table drop operations, it does not clean up existing orpha= n views. It is also believed that when the system catalog table was split d= ue to a bug in the past, it also contributed to creating orphan views as Ph= oenix did not support splittable system catalog. Therefore,=C2=A0Phoenix ne= eds a tool to clean up orphan views. -- This message was sent by Atlassian JIRA (v7.6.3#76005)