[GitHub] [phoenix] yanxinyi commented on a change in pull request #583: PHOENIX-5470 Tool to find view corruption

[GitBox <gi...@apache.org>]

yanxinyi commented on a change in pull request #583: PHOENIX-5470 Tool to find view corruption
URL: https://github.com/apache/phoenix/pull/583#discussion_r323388908
 
 

 ##########
 File path: phoenix-core/src/main/java/org/apache/phoenix/mapreduce/FindViewCorruptionTool.java
 ##########
 @@ -0,0 +1,328 @@
+/*
+ * 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 implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.mapreduce;
+
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.SYSTEM_CATALOG_NAME;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TABLE_NAME;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TABLE_SCHEM;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TABLE_TYPE;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.TENANT_ID;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_COUNT;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_NAME;
+
+import org.apache.commons.cli.*;
+import org.apache.commons.lang.exception.ExceptionUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.util.Tool;
+import org.apache.hadoop.util.ToolRunner;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.mapreduce.util.ConnectionUtil;
+import org.apache.phoenix.schema.PTableType;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.io.File;
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Properties;
+
+/**
+ * A tool to identify corrupted views.
+ */
+public class FindViewCorruptionTool extends Configured implements Tool {
+    private static final Logger LOGGER = LoggerFactory.getLogger(FindViewCorruptionTool.class);
+
+    private static final String VIEW_QUERY = "SELECT " +
+            TENANT_ID + ", " +
+            TABLE_SCHEM + "," +
+            TABLE_NAME + "," +
+            COLUMN_COUNT +
+            " FROM " + SYSTEM_CATALOG_NAME +
+            " WHERE "+ TABLE_TYPE + " = '" + PTableType.VIEW.getSerializedValue() + "'";
+
+    private static final String COLUMN_COUNT_QUERY = "SELECT COUNT(*) FROM " +
+            SYSTEM_CATALOG_NAME + " WHERE " +
+            TABLE_NAME + " = '%s' AND " +
+            COLUMN_NAME + " IS NOT NULL AND LINK_TYPE IS NULL";
+
+    // The set of views
+    List<View> viewSet = new ArrayList<>();
+    List<View> corruptedViewSet = new ArrayList<>();
+    String outputPath;
+    boolean getCorruptedViewCount = false;
+    String tenantId;
+    String schemName;
+    String tableName;
+    public static final String fileName = "FindViewCorruptionTool.txt";
+
+
+    private static final Option HELP_OPTION = new Option("h", "help", false, "Help");
+    private static final Option TENANT_ID_OPTION = new Option("id", "tenant id", true,
+            "Running tool for a specific tenant");
+    private static final Option TABLE_OPTION = new Option("t", "table name", true,
+            "Running tool for a specific table");
+    private static final Option SCHEMA_OPTION = new Option("s", "schema name", true,
+            "Running tool for a specific schema");
+    private static final Option OUTPUT_PATH_OPTION = new Option("op", "output-path", true,
+            "Output path where the files listing corrupted views are written");
+    private static final Option GET_CORRUPTED_VIEWS_COUNT_OPTION = new Option("c",
+            "get corrupted view count", false,
+            "If specified, cleans orphan views and links");
+
+    /**
+     * Go through all the views in the system catalog table and add them
+     * @param phoenixConnection
+     * @throws Exception
+     */
+    private void populateViewSetMetadata(PhoenixConnection phoenixConnection) throws Exception {
+        ResultSet viewRS = phoenixConnection.createStatement().executeQuery(getViewQuery());
+        while (viewRS.next()) {
+            String tenantId = viewRS.getString(1);
+            String schemaName = viewRS.getString(2);
+            String tableName = viewRS.getString(3);
+            String columnCount = viewRS.getString(4);
+            View view = new View(tenantId, schemaName, tableName, columnCount);
+            viewSet.add(view);
+        }
+    }
+
+    private void findCorruptedViews(PhoenixConnection phoenixConnection) throws Exception {
+        // get all views from syscat
+        populateViewSetMetadata(phoenixConnection);
+
+        for (View view : viewSet) {
+            ResultSet viewRS = phoenixConnection.createStatement()
+                    .executeQuery(getColumnCountQuery(view));
+            viewRS.next();
+            String columnCount = viewRS.getString(1);
+            if (view.getColumnCount() == null || !columnCount.equals(view.getColumnCount())) {
+                view.setSelectColumnCount(columnCount);
+                corruptedViewSet.add(view);
+            }
+        }
+    }
+
+    private String getViewQuery() {
+        String query = VIEW_QUERY;
+        if (this.schemName != null) {
+            query += " AND " + TABLE_SCHEM + "='" + this.schemName + "'";
+        }
+        if (this.tableName != null) {
+            query += " AND " + TABLE_NAME + "='" + this.tableName + "'";
+        }
+        if (this.tenantId != null) {
+            query += " AND " + TENANT_ID + "='" + this.tenantId + "'";
+        }
 
 Review comment:
   This is the flag to get all views belongs to a specific tenant. If we are adding NULL here, we will always get all non-tenant views.
   `SELECT * FROM SYSTEM.CATALOG WHERE TABLE_TYPE ='v' AND TENANT_ID ='some tenant' `
   V.S
   `SELECT * FROM SYSTEM.CATALOG WHERE TABLE_TYPE ='v' AND TENANT_ID IS NULL `

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services