You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by GitBox <gi...@apache.org> on 2020/07/17 00:01:45 UTC
[GitHub] [phoenix] gjacoby126 commented on a change in pull request #806: PHOENIX-5946: Implement SchemaExtractionTool utility to get effective…
gjacoby126 commented on a change in pull request #806:
URL: https://github.com/apache/phoenix/pull/806#discussion_r456134042
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/SchemaExtractionToolIT.java
##########
@@ -0,0 +1,164 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.schema.SchemaExtractionTool;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.SchemaUtil;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.util.Collections;
+
+import java.util.Map;
+import java.util.Properties;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+
+public class SchemaExtractionToolIT extends BaseTest {
+
+ @BeforeClass
+ public static void setup() throws Exception {
+ Map<String, String> props = Collections.emptyMap();
+ setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Test
+ public void testCreateTableStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ conn.commit();
+ String [] args = {"-tb", tableName, "-s", schemaName};
+
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+ set.run(args);
+ String actualProperties = set.output.substring(set.output.lastIndexOf(")")+1).replace(" ","");
+ Assert.assertEquals(5, actualProperties.split(",").length);
+ }
+ }
+
+ @Test
+ public void testCreateIndexStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String indexName = generateUniqueName();
+ String indexName1 = generateUniqueName();
+ String indexName2 = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+
+ String createIndexStatement = "CREATE INDEX "+indexName + " ON "+pTableFullName+"(v1 DESC) INCLUDE (v2)";
+
+ String createIndexStatement1 = "CREATE INDEX "+indexName1 + " ON "+pTableFullName+"(v2 DESC) INCLUDE (v1)";
+
+ String createIndexStatement2 = "CREATE INDEX "+indexName2 + " ON "+pTableFullName+"(k)";
+
+ conn.createStatement().execute(createIndexStatement);
+ conn.createStatement().execute(createIndexStatement1);
+ conn.createStatement().execute(createIndexStatement2);
+ conn.commit();
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+
+ String [] args = {"-tb", indexName, "-s", schemaName};
+ set.run(args);
+ Assert.assertEquals(createIndexStatement.toUpperCase(), set.output.toUpperCase());
+
+ String [] args1 = {"-tb", indexName1, "-s", schemaName};
+ set.run(args1);
+ Assert.assertEquals(createIndexStatement1.toUpperCase(), set.output.toUpperCase());
+
+ String [] args2 = {"-tb", indexName2, "-s", schemaName};
+ set.run(args2);
+ Assert.assertEquals(createIndexStatement2.toUpperCase(), set.output.toUpperCase());
+ }
+ }
+
+ @Test
+ public void testCreateViewStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String viewName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k BIGINT NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ String viewFullName = SchemaUtil.getQualifiedTableName(schemaName, viewName);
+ String viewFullName1 = SchemaUtil.getQualifiedTableName(schemaName, viewName+"1");
+
+
+ String createView = "CREATE VIEW "+viewFullName + "(id1 BIGINT, id2 BIGINT NOT NULL, id3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (id2, id3 DESC)) AS SELECT * FROM "+pTableFullName;
+ String createView1 = "CREATE VIEW "+viewFullName1 + "(id1 BIGINT, id2 BIGINT NOT NULL, id3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (id2, id3 DESC)) AS SELECT * FROM "+pTableFullName;
Review comment:
ditto
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
+
+ private static final Logger LOGGER = Logger.getLogger(SchemaExtractionTool.class.getName());
+ private static final Option HELP_OPTION = new Option("h", "help",
+ false, "Help");
+ private static final Option TABLE_OPTION = new Option("tb", "table", true,
+ "[Required] Table name ex. table1");
+ private static final Option SCHEMA_OPTION = new Option("s", "schema", true,
+ "[Optional] Schema name ex. schema");
+
+ private String pTableName;
+ private String pSchemaName;
+
+ private static final String CREATE_TABLE = "CREATE TABLE %s";
+ private static final String CREATE_INDEX = "CREATE %sINDEX %s ON %s";
+ private static final String CREATE_VIEW = "CREATE VIEW %s%s AS SELECT * FROM %s%s";
+ public static Configuration conf;
+ Map<String, String> defaultProps = new HashMap<>();
+ Map<String, String> definedProps = new HashMap<>();
+ public String output;
+
+ @Override
+ public int run(String[] args) throws Exception {
+ populateToolAttributes(args);
+ conf = HBaseConfiguration.addHbaseResources(getConf());
+ PTable table = getPTable(pSchemaName, pTableName);
+ output = getDDL(table);
+ return 0;
+ }
+
+ private String getDDL(PTable table) throws Exception {
+ String ddl = null;
+ if(table.getType().equals(PTableType.TABLE)) {
+ ddl = extractCreateTableDDL(table);
+ } else if(table.getType().equals(PTableType.INDEX)) {
+ ddl = extractCreateIndexDDL(table);
+ } else if(table.getType().equals(PTableType.VIEW)) {
+ ddl = extractCreateViewDDL(table);
+ }
+ return ddl;
+ }
+
+ protected String extractCreateIndexDDL(PTable indexPTable)
+ throws SQLException {
+ String pSchemaName = indexPTable.getSchemaName().getString();
+ String pTableName = indexPTable.getTableName().getString();
+
+ String baseTableName = indexPTable.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(indexPTable.getSchemaName().getString(), baseTableName);
+ PTable dataPTable = getPTable(baseTableFullName);
+
+ String defaultCF = SchemaUtil.getEmptyColumnFamilyAsString(indexPTable);
Review comment:
(If you want to make supporting multiple column families a separate subtask JIRA and not block this PR that's fine, but I think it would be part of the requirement to merge the feature branch into a release branch.)
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/SchemaExtractionToolIT.java
##########
@@ -0,0 +1,164 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.schema.SchemaExtractionTool;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.SchemaUtil;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.util.Collections;
+
+import java.util.Map;
+import java.util.Properties;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+
+public class SchemaExtractionToolIT extends BaseTest {
+
+ @BeforeClass
+ public static void setup() throws Exception {
+ Map<String, String> props = Collections.emptyMap();
+ setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Test
+ public void testCreateTableStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ conn.commit();
+ String [] args = {"-tb", tableName, "-s", schemaName};
+
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+ set.run(args);
+ String actualProperties = set.output.substring(set.output.lastIndexOf(")")+1).replace(" ","");
+ Assert.assertEquals(5, actualProperties.split(",").length);
+ }
+ }
+
+ @Test
+ public void testCreateIndexStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String indexName = generateUniqueName();
+ String indexName1 = generateUniqueName();
+ String indexName2 = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+
+ String createIndexStatement = "CREATE INDEX "+indexName + " ON "+pTableFullName+"(v1 DESC) INCLUDE (v2)";
+
+ String createIndexStatement1 = "CREATE INDEX "+indexName1 + " ON "+pTableFullName+"(v2 DESC) INCLUDE (v1)";
+
+ String createIndexStatement2 = "CREATE INDEX "+indexName2 + " ON "+pTableFullName+"(k)";
+
+ conn.createStatement().execute(createIndexStatement);
+ conn.createStatement().execute(createIndexStatement1);
+ conn.createStatement().execute(createIndexStatement2);
+ conn.commit();
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+
+ String [] args = {"-tb", indexName, "-s", schemaName};
+ set.run(args);
+ Assert.assertEquals(createIndexStatement.toUpperCase(), set.output.toUpperCase());
+
+ String [] args1 = {"-tb", indexName1, "-s", schemaName};
+ set.run(args1);
+ Assert.assertEquals(createIndexStatement1.toUpperCase(), set.output.toUpperCase());
+
+ String [] args2 = {"-tb", indexName2, "-s", schemaName};
+ set.run(args2);
+ Assert.assertEquals(createIndexStatement2.toUpperCase(), set.output.toUpperCase());
+ }
+ }
+
+ @Test
+ public void testCreateViewStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String viewName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k BIGINT NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ String viewFullName = SchemaUtil.getQualifiedTableName(schemaName, viewName);
+ String viewFullName1 = SchemaUtil.getQualifiedTableName(schemaName, viewName+"1");
+
+
+ String createView = "CREATE VIEW "+viewFullName + "(id1 BIGINT, id2 BIGINT NOT NULL, id3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (id2, id3 DESC)) AS SELECT * FROM "+pTableFullName;
Review comment:
nit: long line
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/PTable.java
##########
@@ -800,6 +801,8 @@ private static int getReservedQualifier(byte[] bytes, int offset, int length) {
Boolean useStatsForParallelization();
boolean hasViewModifiedUpdateCacheFrequency();
boolean hasViewModifiedUseStatsForParallelization();
+ Map<String, String> getValues();
Review comment:
Maybe a more precise name here? If these are table properties, then getPropertyValues() and getDefaultPropertyValues()?
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
Review comment:
It's also just useful to have a class that takes in a PTable and spits out SQL.
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
+
+ private static final Logger LOGGER = Logger.getLogger(SchemaExtractionTool.class.getName());
+ private static final Option HELP_OPTION = new Option("h", "help",
+ false, "Help");
+ private static final Option TABLE_OPTION = new Option("tb", "table", true,
+ "[Required] Table name ex. table1");
+ private static final Option SCHEMA_OPTION = new Option("s", "schema", true,
+ "[Optional] Schema name ex. schema");
+
+ private String pTableName;
+ private String pSchemaName;
+
+ private static final String CREATE_TABLE = "CREATE TABLE %s";
+ private static final String CREATE_INDEX = "CREATE %sINDEX %s ON %s";
+ private static final String CREATE_VIEW = "CREATE VIEW %s%s AS SELECT * FROM %s%s";
+ public static Configuration conf;
+ Map<String, String> defaultProps = new HashMap<>();
+ Map<String, String> definedProps = new HashMap<>();
+ public String output;
+
+ @Override
+ public int run(String[] args) throws Exception {
+ populateToolAttributes(args);
+ conf = HBaseConfiguration.addHbaseResources(getConf());
+ PTable table = getPTable(pSchemaName, pTableName);
+ output = getDDL(table);
+ return 0;
+ }
+
+ private String getDDL(PTable table) throws Exception {
+ String ddl = null;
+ if(table.getType().equals(PTableType.TABLE)) {
+ ddl = extractCreateTableDDL(table);
+ } else if(table.getType().equals(PTableType.INDEX)) {
+ ddl = extractCreateIndexDDL(table);
+ } else if(table.getType().equals(PTableType.VIEW)) {
+ ddl = extractCreateViewDDL(table);
+ }
+ return ddl;
+ }
+
+ protected String extractCreateIndexDDL(PTable indexPTable)
+ throws SQLException {
+ String pSchemaName = indexPTable.getSchemaName().getString();
+ String pTableName = indexPTable.getTableName().getString();
+
+ String baseTableName = indexPTable.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(indexPTable.getSchemaName().getString(), baseTableName);
+ PTable dataPTable = getPTable(baseTableFullName);
+
+ String defaultCF = SchemaUtil.getEmptyColumnFamilyAsString(indexPTable);
+ String indexedColumnsString = getIndexedColumnsString(indexPTable, dataPTable, defaultCF);
+ String coveredColumnsString = getCoveredColumnsString(indexPTable, defaultCF);
+
+ return generateIndexDDLString(baseTableFullName, indexedColumnsString, coveredColumnsString,
+ indexPTable.getIndexType().equals(PTable.IndexType.LOCAL), pTableName);
+ }
+
+ //TODO: Indexed on an expression
+ // test with different default CF, key is a included column
+ private String getIndexedColumnsString(PTable indexPTable, PTable dataPTable, String defaultCF) {
+
+ List<PColumn> indexPK = indexPTable.getPKColumns();
+ List<PColumn> dataPK = dataPTable.getPKColumns();
+ Set<String> indexPkSet = new HashSet<>();
+ Set<String> dataPkSet = new HashSet<>();
+ Map<String, SortOrder> sortOrderMap = new HashMap<>();
+ StringBuilder indexedColumnsBuilder = new StringBuilder();
+ for (PColumn indexedColumn : indexPK) {
+ String indexColumn = extractIndexColumn(indexedColumn.getName().getString(), defaultCF);
+ if(indexColumn.equalsIgnoreCase(VIEW_INDEX_ID_COLUMN_NAME)) {
+ continue;
+ }
+ indexPkSet.add(indexColumn);
+ sortOrderMap.put(indexColumn, indexedColumn.getSortOrder());
+ }
+
+ for(PColumn pColumn : dataPK) {
+ dataPkSet.add(pColumn.getName().getString());
+ }
+
+ Set<String> effectivePK = Sets.symmetricDifference(indexPkSet, dataPkSet);
+ if (effectivePK.isEmpty()) {
+ effectivePK = indexPkSet;
+ }
+ for (String column : effectivePK) {
+ if(indexedColumnsBuilder.length()!=0) {
+ indexedColumnsBuilder.append(", ");
+ }
+ indexedColumnsBuilder.append(column);
+ if(sortOrderMap.get(column)!= SortOrder.getDefault()) {
+ indexedColumnsBuilder.append(" ");
+ indexedColumnsBuilder.append(sortOrderMap.get(column));
+ }
+ }
+ return indexedColumnsBuilder.toString();
+ }
+
+ private String extractIndexColumn(String columnName, String defaultCF) {
+ String [] columnNameSplit = columnName.split(":");
+ if(columnNameSplit[0].equals("") || columnNameSplit[0].equalsIgnoreCase(defaultCF)) {
+ return columnNameSplit[1];
+ } else {
+ return columnName.replace(":", ".");
+ }
+ }
+
+ private String getCoveredColumnsString(PTable indexPTable, String defaultCF) {
+ StringBuilder coveredColumnsBuilder = new StringBuilder();
+ List<PColumn> pkColumns = indexPTable.getColumns();
+ for (PColumn cc : pkColumns) {
+ if(coveredColumnsBuilder.length()!=0) {
+ coveredColumnsBuilder.append(", ");
+ }
+ if(cc.getFamilyName()!=null) {
+ String indexColumn = extractIndexColumn(cc.getName().getString(), defaultCF);
+ coveredColumnsBuilder.append(indexColumn);
+ }
+ }
+ return coveredColumnsBuilder.toString();
+ }
+
+ protected String generateIndexDDLString(String baseTableFullName, String indexedColumnString, String coveredColumnString, boolean local, String pTableName) {
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_INDEX, local ? "LOCAL " : "", pTableName, baseTableFullName));
+ outputBuilder.append("(");
+ outputBuilder.append(indexedColumnString);
+ outputBuilder.append(")");
+ if(!coveredColumnString.equals("")) {
+ outputBuilder.append(" INCLUDE (");
+ outputBuilder.append(coveredColumnString);
+ outputBuilder.append(")");
+ }
+ return outputBuilder.toString();
+ }
+
+ PTable getPTable(String pTableFullName) throws SQLException {
+ try (Connection conn = getConnection()) {
+ return PhoenixRuntime.getTable(conn, pTableFullName);
+ }
+ }
+
+ protected String extractCreateViewDDL(PTable table) throws SQLException {
+ String pSchemaName = table.getSchemaName().getString();
+ String pTableName = table.getTableName().getString();
+ String baseTableName = table.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, baseTableName);
+ PTable baseTable = getPTable(baseTableFullName);
+ String columnInfoString = getColumnInfoStringForView(table, baseTable);
+
+ String whereClause = table.getViewStatement();
+ if(whereClause != null) {
+ whereClause = whereClause.substring(whereClause.indexOf("WHERE"));
+ }
+ return generateCreateViewDDL(columnInfoString, baseTableFullName, whereClause == null ? "" : " "+whereClause, pSchemaName, pTableName);
+ }
+
+ private String generateCreateViewDDL(String columnInfoString, String baseTableFullName, String whereClause, String pSchemaName, String pTableName) {
+ String viewFullName = SchemaUtil.getQualifiedTableName(pSchemaName, pTableName);
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_VIEW, viewFullName, columnInfoString, baseTableFullName, whereClause));
+ return outputBuilder.toString();
+ }
+
+ public String extractCreateTableDDL(PTable table) throws IOException, SQLException {
+
+ String pSchemaName = table.getSchemaName().getString();
+ String pTableName = table.getTableName().getString();
+
+ ConnectionQueryServices cqsi = getCQSIObject();
+ HTableDescriptor htd = getHTableDescriptor(cqsi, table);
+ HColumnDescriptor hcd = htd.getFamily(SchemaUtil.getEmptyColumnFamily(table));
+
+ populateDefaultProperties(table);
+ setPTableProperties(table);
+ setHTableProperties(htd);
+ setHColumnFamilyProperties(hcd);
+
+ String columnInfoString = getColumnInfoStringForTable(table);
+ String propertiesString = convertPropertiesToString();
+
+ return generateTableDDLString(columnInfoString, propertiesString, pSchemaName, pTableName);
+ }
+ private String generateTableDDLString(String columnInfoString,String propertiesString,String pSchemaName,String pTableName) {
+ String pTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, pTableName);
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_TABLE, pTableFullName));
+ outputBuilder.append(columnInfoString).append(" ").append(propertiesString);
+ return outputBuilder.toString();
+ }
+
+ private void populateDefaultProperties(PTable table) {
+ Map<String, String> propsMap = HColumnDescriptor.getDefaultValues();
+ for (Map.Entry<String, String> entry : propsMap.entrySet()) {
+ String key = entry.getKey();
+ String value = entry.getValue();
+ defaultProps.put(key, value);
+ if(key.equalsIgnoreCase(BLOOMFILTER) || key.equalsIgnoreCase(COMPRESSION)) {
Review comment:
And what about if they're different for different major or minor HBase versions?
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
Review comment:
As this class evolves, and before merging it to a release branch, let's split out the business logic from the "tool" parts that handle command-line parsing. Having an object API will make unit testing (and future reuse) easier.
The example to avoid here is IndexTool, which mixes in command-line parsing, MapReduce configuration, and some business logic all together.
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
+
+ private static final Logger LOGGER = Logger.getLogger(SchemaExtractionTool.class.getName());
+ private static final Option HELP_OPTION = new Option("h", "help",
+ false, "Help");
+ private static final Option TABLE_OPTION = new Option("tb", "table", true,
+ "[Required] Table name ex. table1");
+ private static final Option SCHEMA_OPTION = new Option("s", "schema", true,
+ "[Optional] Schema name ex. schema");
+
+ private String pTableName;
+ private String pSchemaName;
+
+ private static final String CREATE_TABLE = "CREATE TABLE %s";
+ private static final String CREATE_INDEX = "CREATE %sINDEX %s ON %s";
+ private static final String CREATE_VIEW = "CREATE VIEW %s%s AS SELECT * FROM %s%s";
+ public static Configuration conf;
+ Map<String, String> defaultProps = new HashMap<>();
+ Map<String, String> definedProps = new HashMap<>();
+ public String output;
+
+ @Override
+ public int run(String[] args) throws Exception {
+ populateToolAttributes(args);
+ conf = HBaseConfiguration.addHbaseResources(getConf());
+ PTable table = getPTable(pSchemaName, pTableName);
+ output = getDDL(table);
+ return 0;
+ }
+
+ private String getDDL(PTable table) throws Exception {
+ String ddl = null;
+ if(table.getType().equals(PTableType.TABLE)) {
+ ddl = extractCreateTableDDL(table);
+ } else if(table.getType().equals(PTableType.INDEX)) {
+ ddl = extractCreateIndexDDL(table);
+ } else if(table.getType().equals(PTableType.VIEW)) {
+ ddl = extractCreateViewDDL(table);
+ }
+ return ddl;
+ }
+
+ protected String extractCreateIndexDDL(PTable indexPTable)
+ throws SQLException {
+ String pSchemaName = indexPTable.getSchemaName().getString();
+ String pTableName = indexPTable.getTableName().getString();
+
+ String baseTableName = indexPTable.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(indexPTable.getSchemaName().getString(), baseTableName);
+ PTable dataPTable = getPTable(baseTableFullName);
+
+ String defaultCF = SchemaUtil.getEmptyColumnFamilyAsString(indexPTable);
Review comment:
What if a table has more than one column family?
##########
File path: phoenix-core/src/it/java/org/apache/phoenix/end2end/SchemaExtractionToolIT.java
##########
@@ -0,0 +1,164 @@
+package org.apache.phoenix.end2end;
+
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.schema.SchemaExtractionTool;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.SchemaUtil;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.util.Collections;
+
+import java.util.Map;
+import java.util.Properties;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+
+public class SchemaExtractionToolIT extends BaseTest {
+
+ @BeforeClass
+ public static void setup() throws Exception {
+ Map<String, String> props = Collections.emptyMap();
+ setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Test
+ public void testCreateTableStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ conn.commit();
+ String [] args = {"-tb", tableName, "-s", schemaName};
+
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+ set.run(args);
+ String actualProperties = set.output.substring(set.output.lastIndexOf(")")+1).replace(" ","");
+ Assert.assertEquals(5, actualProperties.split(",").length);
+ }
+ }
+
+ @Test
+ public void testCreateIndexStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String indexName = generateUniqueName();
+ String indexName1 = generateUniqueName();
+ String indexName2 = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+
+ String createIndexStatement = "CREATE INDEX "+indexName + " ON "+pTableFullName+"(v1 DESC) INCLUDE (v2)";
+
+ String createIndexStatement1 = "CREATE INDEX "+indexName1 + " ON "+pTableFullName+"(v2 DESC) INCLUDE (v1)";
+
+ String createIndexStatement2 = "CREATE INDEX "+indexName2 + " ON "+pTableFullName+"(k)";
+
+ conn.createStatement().execute(createIndexStatement);
+ conn.createStatement().execute(createIndexStatement1);
+ conn.createStatement().execute(createIndexStatement2);
+ conn.commit();
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+
+ String [] args = {"-tb", indexName, "-s", schemaName};
+ set.run(args);
+ Assert.assertEquals(createIndexStatement.toUpperCase(), set.output.toUpperCase());
+
+ String [] args1 = {"-tb", indexName1, "-s", schemaName};
+ set.run(args1);
+ Assert.assertEquals(createIndexStatement1.toUpperCase(), set.output.toUpperCase());
+
+ String [] args2 = {"-tb", indexName2, "-s", schemaName};
+ set.run(args2);
+ Assert.assertEquals(createIndexStatement2.toUpperCase(), set.output.toUpperCase());
+ }
+ }
+
+ @Test
+ public void testCreateViewStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String viewName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k BIGINT NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ String viewFullName = SchemaUtil.getQualifiedTableName(schemaName, viewName);
+ String viewFullName1 = SchemaUtil.getQualifiedTableName(schemaName, viewName+"1");
+
+
+ String createView = "CREATE VIEW "+viewFullName + "(id1 BIGINT, id2 BIGINT NOT NULL, id3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (id2, id3 DESC)) AS SELECT * FROM "+pTableFullName;
+ String createView1 = "CREATE VIEW "+viewFullName1 + "(id1 BIGINT, id2 BIGINT NOT NULL, id3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (id2, id3 DESC)) AS SELECT * FROM "+pTableFullName;
+
+ conn.createStatement().execute(createView);
+ conn.createStatement().execute(createView1);
+ conn.commit();
+ String [] args = {"-tb", viewName, "-s", schemaName};
+
+ SchemaExtractionTool set = new SchemaExtractionTool();
+ set.setConf(conn.unwrap(PhoenixConnection.class).getQueryServices().getConfiguration());
+ set.run(args);
+ Assert.assertEquals(createView.toUpperCase(), set.output.toUpperCase());
+ }
+ }
+
+ @Test
+ public void testCreateViewIndexStatement() throws Exception {
+ String tableName = generateUniqueName();
+ String schemaName = generateUniqueName();
+ String viewName = generateUniqueName();
+ String childView = generateUniqueName();
+ String indexName = generateUniqueName();
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String properties = "TTL=2592000,IMMUTABLE_ROWS=true,DISABLE_MIGRATION=true,DISABLE_SOR=true,DISABLE_WAL=true";
+
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+
+ String pTableFullName = SchemaUtil.getQualifiedTableName(schemaName, tableName);
+ conn.createStatement().execute("CREATE TABLE "+pTableFullName + "(k BIGINT NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"
+ + properties);
+ String viewFullName = SchemaUtil.getQualifiedTableName(schemaName, viewName);
+ String childviewName = SchemaUtil.getQualifiedTableName(schemaName, childView);
+
+ String createView = "CREATE VIEW "+viewFullName + "(id1 BIGINT, id2 BIGINT NOT NULL, id3 VARCHAR NOT NULL CONSTRAINT PKVIEW PRIMARY KEY (id2, id3 DESC)) AS SELECT * FROM "+pTableFullName;
Review comment:
nit: long line
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
+
+ private static final Logger LOGGER = Logger.getLogger(SchemaExtractionTool.class.getName());
+ private static final Option HELP_OPTION = new Option("h", "help",
+ false, "Help");
+ private static final Option TABLE_OPTION = new Option("tb", "table", true,
+ "[Required] Table name ex. table1");
+ private static final Option SCHEMA_OPTION = new Option("s", "schema", true,
+ "[Optional] Schema name ex. schema");
+
+ private String pTableName;
+ private String pSchemaName;
+
+ private static final String CREATE_TABLE = "CREATE TABLE %s";
+ private static final String CREATE_INDEX = "CREATE %sINDEX %s ON %s";
+ private static final String CREATE_VIEW = "CREATE VIEW %s%s AS SELECT * FROM %s%s";
+ public static Configuration conf;
+ Map<String, String> defaultProps = new HashMap<>();
+ Map<String, String> definedProps = new HashMap<>();
+ public String output;
+
+ @Override
+ public int run(String[] args) throws Exception {
+ populateToolAttributes(args);
+ conf = HBaseConfiguration.addHbaseResources(getConf());
+ PTable table = getPTable(pSchemaName, pTableName);
+ output = getDDL(table);
+ return 0;
+ }
+
+ private String getDDL(PTable table) throws Exception {
+ String ddl = null;
+ if(table.getType().equals(PTableType.TABLE)) {
+ ddl = extractCreateTableDDL(table);
+ } else if(table.getType().equals(PTableType.INDEX)) {
+ ddl = extractCreateIndexDDL(table);
+ } else if(table.getType().equals(PTableType.VIEW)) {
+ ddl = extractCreateViewDDL(table);
+ }
+ return ddl;
+ }
+
+ protected String extractCreateIndexDDL(PTable indexPTable)
+ throws SQLException {
+ String pSchemaName = indexPTable.getSchemaName().getString();
+ String pTableName = indexPTable.getTableName().getString();
+
+ String baseTableName = indexPTable.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(indexPTable.getSchemaName().getString(), baseTableName);
+ PTable dataPTable = getPTable(baseTableFullName);
+
+ String defaultCF = SchemaUtil.getEmptyColumnFamilyAsString(indexPTable);
+ String indexedColumnsString = getIndexedColumnsString(indexPTable, dataPTable, defaultCF);
+ String coveredColumnsString = getCoveredColumnsString(indexPTable, defaultCF);
+
+ return generateIndexDDLString(baseTableFullName, indexedColumnsString, coveredColumnsString,
+ indexPTable.getIndexType().equals(PTable.IndexType.LOCAL), pTableName);
+ }
+
+ //TODO: Indexed on an expression
+ // test with different default CF, key is a included column
+ private String getIndexedColumnsString(PTable indexPTable, PTable dataPTable, String defaultCF) {
+
+ List<PColumn> indexPK = indexPTable.getPKColumns();
+ List<PColumn> dataPK = dataPTable.getPKColumns();
+ Set<String> indexPkSet = new HashSet<>();
+ Set<String> dataPkSet = new HashSet<>();
+ Map<String, SortOrder> sortOrderMap = new HashMap<>();
+ StringBuilder indexedColumnsBuilder = new StringBuilder();
+ for (PColumn indexedColumn : indexPK) {
+ String indexColumn = extractIndexColumn(indexedColumn.getName().getString(), defaultCF);
+ if(indexColumn.equalsIgnoreCase(VIEW_INDEX_ID_COLUMN_NAME)) {
+ continue;
+ }
+ indexPkSet.add(indexColumn);
+ sortOrderMap.put(indexColumn, indexedColumn.getSortOrder());
+ }
+
+ for(PColumn pColumn : dataPK) {
+ dataPkSet.add(pColumn.getName().getString());
+ }
+
+ Set<String> effectivePK = Sets.symmetricDifference(indexPkSet, dataPkSet);
+ if (effectivePK.isEmpty()) {
+ effectivePK = indexPkSet;
+ }
+ for (String column : effectivePK) {
+ if(indexedColumnsBuilder.length()!=0) {
+ indexedColumnsBuilder.append(", ");
+ }
+ indexedColumnsBuilder.append(column);
+ if(sortOrderMap.get(column)!= SortOrder.getDefault()) {
+ indexedColumnsBuilder.append(" ");
+ indexedColumnsBuilder.append(sortOrderMap.get(column));
+ }
+ }
+ return indexedColumnsBuilder.toString();
+ }
+
+ private String extractIndexColumn(String columnName, String defaultCF) {
+ String [] columnNameSplit = columnName.split(":");
+ if(columnNameSplit[0].equals("") || columnNameSplit[0].equalsIgnoreCase(defaultCF)) {
+ return columnNameSplit[1];
+ } else {
+ return columnName.replace(":", ".");
+ }
+ }
+
+ private String getCoveredColumnsString(PTable indexPTable, String defaultCF) {
+ StringBuilder coveredColumnsBuilder = new StringBuilder();
+ List<PColumn> pkColumns = indexPTable.getColumns();
+ for (PColumn cc : pkColumns) {
+ if(coveredColumnsBuilder.length()!=0) {
+ coveredColumnsBuilder.append(", ");
+ }
+ if(cc.getFamilyName()!=null) {
+ String indexColumn = extractIndexColumn(cc.getName().getString(), defaultCF);
+ coveredColumnsBuilder.append(indexColumn);
+ }
+ }
+ return coveredColumnsBuilder.toString();
+ }
+
+ protected String generateIndexDDLString(String baseTableFullName, String indexedColumnString, String coveredColumnString, boolean local, String pTableName) {
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_INDEX, local ? "LOCAL " : "", pTableName, baseTableFullName));
+ outputBuilder.append("(");
+ outputBuilder.append(indexedColumnString);
+ outputBuilder.append(")");
+ if(!coveredColumnString.equals("")) {
+ outputBuilder.append(" INCLUDE (");
+ outputBuilder.append(coveredColumnString);
+ outputBuilder.append(")");
+ }
+ return outputBuilder.toString();
+ }
+
+ PTable getPTable(String pTableFullName) throws SQLException {
+ try (Connection conn = getConnection()) {
+ return PhoenixRuntime.getTable(conn, pTableFullName);
+ }
+ }
+
+ protected String extractCreateViewDDL(PTable table) throws SQLException {
+ String pSchemaName = table.getSchemaName().getString();
+ String pTableName = table.getTableName().getString();
+ String baseTableName = table.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, baseTableName);
+ PTable baseTable = getPTable(baseTableFullName);
+ String columnInfoString = getColumnInfoStringForView(table, baseTable);
+
+ String whereClause = table.getViewStatement();
+ if(whereClause != null) {
+ whereClause = whereClause.substring(whereClause.indexOf("WHERE"));
+ }
+ return generateCreateViewDDL(columnInfoString, baseTableFullName, whereClause == null ? "" : " "+whereClause, pSchemaName, pTableName);
+ }
+
+ private String generateCreateViewDDL(String columnInfoString, String baseTableFullName, String whereClause, String pSchemaName, String pTableName) {
+ String viewFullName = SchemaUtil.getQualifiedTableName(pSchemaName, pTableName);
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_VIEW, viewFullName, columnInfoString, baseTableFullName, whereClause));
+ return outputBuilder.toString();
+ }
+
+ public String extractCreateTableDDL(PTable table) throws IOException, SQLException {
+
+ String pSchemaName = table.getSchemaName().getString();
+ String pTableName = table.getTableName().getString();
+
+ ConnectionQueryServices cqsi = getCQSIObject();
+ HTableDescriptor htd = getHTableDescriptor(cqsi, table);
+ HColumnDescriptor hcd = htd.getFamily(SchemaUtil.getEmptyColumnFamily(table));
+
+ populateDefaultProperties(table);
+ setPTableProperties(table);
+ setHTableProperties(htd);
+ setHColumnFamilyProperties(hcd);
+
+ String columnInfoString = getColumnInfoStringForTable(table);
+ String propertiesString = convertPropertiesToString();
+
+ return generateTableDDLString(columnInfoString, propertiesString, pSchemaName, pTableName);
+ }
+ private String generateTableDDLString(String columnInfoString,String propertiesString,String pSchemaName,String pTableName) {
+ String pTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, pTableName);
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_TABLE, pTableFullName));
+ outputBuilder.append(columnInfoString).append(" ").append(propertiesString);
+ return outputBuilder.toString();
+ }
+
+ private void populateDefaultProperties(PTable table) {
+ Map<String, String> propsMap = HColumnDescriptor.getDefaultValues();
+ for (Map.Entry<String, String> entry : propsMap.entrySet()) {
+ String key = entry.getKey();
+ String value = entry.getValue();
+ defaultProps.put(key, value);
+ if(key.equalsIgnoreCase(BLOOMFILTER) || key.equalsIgnoreCase(COMPRESSION)) {
+ defaultProps.put(key, "NONE");
+ }
+ if(key.equalsIgnoreCase(DATA_BLOCK_ENCODING)) {
+ defaultProps.put(key, String.valueOf(DEFAULT_DATA_BLOCK_ENCODING));
+ }
+ }
+ defaultProps.putAll(table.getDefaultValues());
+ }
+
+ private void setHTableProperties(HTableDescriptor htd) {
+ Map<ImmutableBytesWritable, ImmutableBytesWritable> propsMap = htd.getValues();
+ for (Map.Entry<ImmutableBytesWritable, ImmutableBytesWritable> entry : propsMap.entrySet()) {
+ ImmutableBytesWritable key = entry.getKey();
+ ImmutableBytesWritable value = entry.getValue();
+ if(Bytes.toString(key.get()).contains("coprocessor") || Bytes.toString(key.get()).contains(IS_META)) {
+ continue;
+ }
+ defaultProps.put(Bytes.toString(key.get()), "false");
+ definedProps.put(Bytes.toString(key.get()), Bytes.toString(value.get()));
+ }
+ }
+
+ private void setHColumnFamilyProperties(HColumnDescriptor columnDescriptor) {
+ Map<ImmutableBytesWritable, ImmutableBytesWritable> propsMap = columnDescriptor.getValues();
+ for (Map.Entry<ImmutableBytesWritable, ImmutableBytesWritable> entry : propsMap.entrySet()) {
+ ImmutableBytesWritable key = entry.getKey();
+ ImmutableBytesWritable value = entry.getValue();
+ definedProps.put(Bytes.toString(key.get()), Bytes.toString(value.get()));
+ }
+ }
+
+ private void setPTableProperties(PTable table) {
+ Map <String, String> map = table.getValues();
+ for(Map.Entry<String, String> entry : map.entrySet()) {
+ String key = entry.getKey();
+ String value = entry.getValue();
+ if(value != null) {
+ definedProps.put(key, value);
+ }
+ }
+ }
+
+ private HTableDescriptor getHTableDescriptor(ConnectionQueryServices cqsi, PTable table)
Review comment:
Easier for forward porting to call this getTableDescriptor and have it return a TableDescriptor, since HTableDescriptor is deprecated in 2.x
##########
File path: phoenix-core/src/main/java/org/apache/phoenix/schema/SchemaExtractionTool.java
##########
@@ -0,0 +1,488 @@
+package org.apache.phoenix.schema;
+
+import com.google.common.collect.Sets;
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.CommandLineParser;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.apache.commons.lang.StringUtils;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.conf.Configured;
+import org.apache.hadoop.hbase.HBaseConfiguration;
+import org.apache.hadoop.hbase.HColumnDescriptor;
+import org.apache.hadoop.hbase.HTableDescriptor;
+import org.apache.hadoop.hbase.TableName;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+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.query.ConnectionQueryServices;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.SchemaUtil;
+
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.logging.Logger;
+
+import static org.apache.hadoop.hbase.HColumnDescriptor.BLOOMFILTER;
+import static org.apache.hadoop.hbase.HColumnDescriptor.COMPRESSION;
+import static org.apache.hadoop.hbase.HColumnDescriptor.DATA_BLOCK_ENCODING;
+import static org.apache.hadoop.hbase.HTableDescriptor.IS_META;
+import static org.apache.phoenix.util.MetaDataUtil.VIEW_INDEX_ID_COLUMN_NAME;
+import static org.apache.phoenix.util.SchemaUtil.DEFAULT_DATA_BLOCK_ENCODING;
+
+public class SchemaExtractionTool extends Configured implements Tool {
+
+ private static final Logger LOGGER = Logger.getLogger(SchemaExtractionTool.class.getName());
+ private static final Option HELP_OPTION = new Option("h", "help",
+ false, "Help");
+ private static final Option TABLE_OPTION = new Option("tb", "table", true,
+ "[Required] Table name ex. table1");
+ private static final Option SCHEMA_OPTION = new Option("s", "schema", true,
+ "[Optional] Schema name ex. schema");
+
+ private String pTableName;
+ private String pSchemaName;
+
+ private static final String CREATE_TABLE = "CREATE TABLE %s";
+ private static final String CREATE_INDEX = "CREATE %sINDEX %s ON %s";
+ private static final String CREATE_VIEW = "CREATE VIEW %s%s AS SELECT * FROM %s%s";
+ public static Configuration conf;
+ Map<String, String> defaultProps = new HashMap<>();
+ Map<String, String> definedProps = new HashMap<>();
+ public String output;
+
+ @Override
+ public int run(String[] args) throws Exception {
+ populateToolAttributes(args);
+ conf = HBaseConfiguration.addHbaseResources(getConf());
+ PTable table = getPTable(pSchemaName, pTableName);
+ output = getDDL(table);
+ return 0;
+ }
+
+ private String getDDL(PTable table) throws Exception {
+ String ddl = null;
+ if(table.getType().equals(PTableType.TABLE)) {
+ ddl = extractCreateTableDDL(table);
+ } else if(table.getType().equals(PTableType.INDEX)) {
+ ddl = extractCreateIndexDDL(table);
+ } else if(table.getType().equals(PTableType.VIEW)) {
+ ddl = extractCreateViewDDL(table);
+ }
+ return ddl;
+ }
+
+ protected String extractCreateIndexDDL(PTable indexPTable)
+ throws SQLException {
+ String pSchemaName = indexPTable.getSchemaName().getString();
+ String pTableName = indexPTable.getTableName().getString();
+
+ String baseTableName = indexPTable.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(indexPTable.getSchemaName().getString(), baseTableName);
+ PTable dataPTable = getPTable(baseTableFullName);
+
+ String defaultCF = SchemaUtil.getEmptyColumnFamilyAsString(indexPTable);
+ String indexedColumnsString = getIndexedColumnsString(indexPTable, dataPTable, defaultCF);
+ String coveredColumnsString = getCoveredColumnsString(indexPTable, defaultCF);
+
+ return generateIndexDDLString(baseTableFullName, indexedColumnsString, coveredColumnsString,
+ indexPTable.getIndexType().equals(PTable.IndexType.LOCAL), pTableName);
+ }
+
+ //TODO: Indexed on an expression
+ // test with different default CF, key is a included column
+ private String getIndexedColumnsString(PTable indexPTable, PTable dataPTable, String defaultCF) {
+
+ List<PColumn> indexPK = indexPTable.getPKColumns();
+ List<PColumn> dataPK = dataPTable.getPKColumns();
+ Set<String> indexPkSet = new HashSet<>();
+ Set<String> dataPkSet = new HashSet<>();
+ Map<String, SortOrder> sortOrderMap = new HashMap<>();
+ StringBuilder indexedColumnsBuilder = new StringBuilder();
+ for (PColumn indexedColumn : indexPK) {
+ String indexColumn = extractIndexColumn(indexedColumn.getName().getString(), defaultCF);
+ if(indexColumn.equalsIgnoreCase(VIEW_INDEX_ID_COLUMN_NAME)) {
+ continue;
+ }
+ indexPkSet.add(indexColumn);
+ sortOrderMap.put(indexColumn, indexedColumn.getSortOrder());
+ }
+
+ for(PColumn pColumn : dataPK) {
+ dataPkSet.add(pColumn.getName().getString());
+ }
+
+ Set<String> effectivePK = Sets.symmetricDifference(indexPkSet, dataPkSet);
+ if (effectivePK.isEmpty()) {
+ effectivePK = indexPkSet;
+ }
+ for (String column : effectivePK) {
+ if(indexedColumnsBuilder.length()!=0) {
+ indexedColumnsBuilder.append(", ");
+ }
+ indexedColumnsBuilder.append(column);
+ if(sortOrderMap.get(column)!= SortOrder.getDefault()) {
+ indexedColumnsBuilder.append(" ");
+ indexedColumnsBuilder.append(sortOrderMap.get(column));
+ }
+ }
+ return indexedColumnsBuilder.toString();
+ }
+
+ private String extractIndexColumn(String columnName, String defaultCF) {
+ String [] columnNameSplit = columnName.split(":");
+ if(columnNameSplit[0].equals("") || columnNameSplit[0].equalsIgnoreCase(defaultCF)) {
+ return columnNameSplit[1];
+ } else {
+ return columnName.replace(":", ".");
+ }
+ }
+
+ private String getCoveredColumnsString(PTable indexPTable, String defaultCF) {
+ StringBuilder coveredColumnsBuilder = new StringBuilder();
+ List<PColumn> pkColumns = indexPTable.getColumns();
+ for (PColumn cc : pkColumns) {
+ if(coveredColumnsBuilder.length()!=0) {
+ coveredColumnsBuilder.append(", ");
+ }
+ if(cc.getFamilyName()!=null) {
+ String indexColumn = extractIndexColumn(cc.getName().getString(), defaultCF);
+ coveredColumnsBuilder.append(indexColumn);
+ }
+ }
+ return coveredColumnsBuilder.toString();
+ }
+
+ protected String generateIndexDDLString(String baseTableFullName, String indexedColumnString, String coveredColumnString, boolean local, String pTableName) {
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_INDEX, local ? "LOCAL " : "", pTableName, baseTableFullName));
+ outputBuilder.append("(");
+ outputBuilder.append(indexedColumnString);
+ outputBuilder.append(")");
+ if(!coveredColumnString.equals("")) {
+ outputBuilder.append(" INCLUDE (");
+ outputBuilder.append(coveredColumnString);
+ outputBuilder.append(")");
+ }
+ return outputBuilder.toString();
+ }
+
+ PTable getPTable(String pTableFullName) throws SQLException {
+ try (Connection conn = getConnection()) {
+ return PhoenixRuntime.getTable(conn, pTableFullName);
+ }
+ }
+
+ protected String extractCreateViewDDL(PTable table) throws SQLException {
+ String pSchemaName = table.getSchemaName().getString();
+ String pTableName = table.getTableName().getString();
+ String baseTableName = table.getParentTableName().getString();
+ String baseTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, baseTableName);
+ PTable baseTable = getPTable(baseTableFullName);
+ String columnInfoString = getColumnInfoStringForView(table, baseTable);
+
+ String whereClause = table.getViewStatement();
+ if(whereClause != null) {
+ whereClause = whereClause.substring(whereClause.indexOf("WHERE"));
+ }
+ return generateCreateViewDDL(columnInfoString, baseTableFullName, whereClause == null ? "" : " "+whereClause, pSchemaName, pTableName);
+ }
+
+ private String generateCreateViewDDL(String columnInfoString, String baseTableFullName, String whereClause, String pSchemaName, String pTableName) {
+ String viewFullName = SchemaUtil.getQualifiedTableName(pSchemaName, pTableName);
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_VIEW, viewFullName, columnInfoString, baseTableFullName, whereClause));
+ return outputBuilder.toString();
+ }
+
+ public String extractCreateTableDDL(PTable table) throws IOException, SQLException {
+
+ String pSchemaName = table.getSchemaName().getString();
+ String pTableName = table.getTableName().getString();
+
+ ConnectionQueryServices cqsi = getCQSIObject();
+ HTableDescriptor htd = getHTableDescriptor(cqsi, table);
+ HColumnDescriptor hcd = htd.getFamily(SchemaUtil.getEmptyColumnFamily(table));
+
+ populateDefaultProperties(table);
+ setPTableProperties(table);
+ setHTableProperties(htd);
+ setHColumnFamilyProperties(hcd);
+
+ String columnInfoString = getColumnInfoStringForTable(table);
+ String propertiesString = convertPropertiesToString();
+
+ return generateTableDDLString(columnInfoString, propertiesString, pSchemaName, pTableName);
+ }
+ private String generateTableDDLString(String columnInfoString,String propertiesString,String pSchemaName,String pTableName) {
+ String pTableFullName = SchemaUtil.getQualifiedTableName(pSchemaName, pTableName);
+ StringBuilder outputBuilder = new StringBuilder(String.format(CREATE_TABLE, pTableFullName));
+ outputBuilder.append(columnInfoString).append(" ").append(propertiesString);
+ return outputBuilder.toString();
+ }
+
+ private void populateDefaultProperties(PTable table) {
+ Map<String, String> propsMap = HColumnDescriptor.getDefaultValues();
+ for (Map.Entry<String, String> entry : propsMap.entrySet()) {
+ String key = entry.getKey();
+ String value = entry.getValue();
+ defaultProps.put(key, value);
+ if(key.equalsIgnoreCase(BLOOMFILTER) || key.equalsIgnoreCase(COMPRESSION)) {
Review comment:
How do we make sure these are kept up to date?
----------------------------------------------------------------
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