Java Code Examples for org.apache.kylin.metadata.model.TableDesc#getColumns()

private void generateCreateTableDDL(Set<TableDesc> tables, PrintWriter out) {
    for (TableDesc t : tables) {
        if (t.isView())
            continue;

        out.print("DROP TABLE IF EXISTS " + normHiveIdentifier(t.getIdentity()) + ";\n");

        out.print("CREATE TABLE " + normHiveIdentifier(t.getIdentity()) + "(" + "\n");

        for (int i = 0; i < t.getColumns().length; i++) {
            ColumnDesc col = t.getColumns()[i];
            out.print("    ");
            if (i > 0) {
                out.print(",");
            }
            out.print(normHiveIdentifier(col.getName()) + " " + hiveType(col.getType()) + "\n");
        }

        out.print(")" + "\n");
        out.print("ROW FORMAT DELIMITED FIELDS TERMINATED BY ','" + "\n");
        out.print("STORED AS TEXTFILE" + ";\n");
        out.print("\n");
    }
}
 

private List<String> generateCreateH2IndexSql(TableDesc tableDesc) {
    List<String> result = Lists.newArrayList();
    int x = 0;
    for (ColumnDesc col : tableDesc.getColumns()) {
        if ("T".equalsIgnoreCase(col.getIndex())) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("CREATE INDEX IDX_" + tableDesc.getName() + "_" + x + " ON " + tableDesc.getIdentity() + "("
                    + col.getName() + ")");
            ddl.append("\n");
            result.add(ddl.toString());
            x++;
        }
    }

    return result;
}
 

private String generateCreateH2TableSql(TableDesc tableDesc, String csvFilePath, String nullString) {
    StringBuilder ddl = new StringBuilder();
    StringBuilder csvColumns = new StringBuilder();

    ddl.append("CREATE TABLE " + tableDesc.getIdentity() + "\n");
    ddl.append("(" + "\n");

    for (int i = 0; i < tableDesc.getColumns().length; i++) {
        ColumnDesc col = tableDesc.getColumns()[i];
        if (i > 0) {
            ddl.append(",");
            csvColumns.append(",");
        }
        ddl.append(col.getName() + " " + getH2DataType((col.getDatatype())) + "\n");
        csvColumns.append(col.getName());
    }
    ddl.append(")" + "\n");
    ddl.append("AS SELECT * FROM CSVREAD('" + csvFilePath + "', '" + csvColumns + "', 'null=" + nullString
        + " charset=UTF-8 fieldSeparator=,');");

    return ddl.toString();
}
 

/**
 * check whether all columns used in `cube` has compatible schema in current hive schema denoted by `fieldsMap`.
 * @param cube cube to check, must use `table` in its model
 * @param origTable kylin's table metadata
 * @param newTable current hive schema of `table`
 * @return columns in origTable that can't be found in newTable
 */
private List<String> checkAllColumnsInCube(CubeInstance cube, TableDesc origTable, TableDesc newTable) {
    Set<ColumnDesc> usedColumns = Sets.newHashSet();
    for (TblColRef col : cube.getAllColumns()) {
        usedColumns.add(col.getColumnDesc());
    }

    List<String> violateColumns = Lists.newArrayList();
    for (ColumnDesc column : origTable.getColumns()) {
        if (!column.isComputedColumn() && usedColumns.contains(column)) {
            ColumnDesc newCol = newTable.findColumnByName(column.getName());
            if (newCol == null || !isColumnCompatible(column, newCol)) {
                violateColumns.add(column.getName());
            }
        }
    }
    return violateColumns;
}
 

private static String[] generateCreateTableHql(TableDesc tableDesc) {

        String dropsql = "DROP TABLE IF EXISTS " + tableDesc.getIdentity();
        StringBuilder ddl = new StringBuilder();

        ddl.append("CREATE TABLE " + tableDesc.getIdentity() + "\n");
        ddl.append("(" + "\n");

        for (int i = 0; i < tableDesc.getColumns().length; i++) {
            ColumnDesc col = tableDesc.getColumns()[i];
            if (i > 0) {
                ddl.append(",");
            }
            ddl.append(col.getName() + " " + SqlHiveDataTypeMapping.getHiveDataType((col.getDatatype())) + "\n");
        }

        ddl.append(")" + "\n");
        ddl.append("ROW FORMAT DELIMITED FIELDS TERMINATED BY ','" + "\n");
        ddl.append("STORED AS TEXTFILE");

        return new String[] { dropsql, ddl.toString() };
    }
 

/**
 * Used to quote identifiers for JDBC ext job when quoting cc expr
 * @param tableDesc
 * @param sqlExpr
 * @return
 */
public static String quoteIdentifierInSqlExpr(TableDesc tableDesc, String sqlExpr, SqlDialect sqlDialect) {
    String table = tableDesc.getName();
    boolean tableMatched = false;
    List<String> tabPatterns = getTableNameOrAliasPatterns(table);
    if (isIdentifierNeedToQuote(sqlExpr, table, tabPatterns)) {
        sqlExpr = quoteIdentifier(sqlExpr, table, tabPatterns, sqlDialect);
        tableMatched = true;
    }

    if (tableMatched) {
        for (ColumnDesc columnDesc : tableDesc.getColumns()) {
            String column = columnDesc.getName();
            List<String> colPatterns = getColumnNameOrAliasPatterns(column);
            if (isIdentifierNeedToQuote(sqlExpr, column, colPatterns)) {
                sqlExpr = quoteIdentifier(sqlExpr, column, colPatterns, sqlDialect);
            }
        }
    }

    return sqlExpr;
}
 

@Test
public void testLoadTableMetadata() throws Exception {
    Pair<TableDesc, TableExtDesc> pair = explorer.loadTableMetadata("DEFAULT", "TEST_KYLIN_FACT", "DEFAULT");
    Assert.assertNotNull(pair.getFirst());
    Assert.assertNotNull(pair.getSecond());

    TableDesc tblDesc = pair.getFirst();
    TableExtDesc tblExtDesc = pair.getSecond();
    Assert.assertEquals("TEST_KYLIN_FACT", tblDesc.getName());
    Assert.assertEquals("TABLE", tblDesc.getTableType());
    Assert.assertEquals("DEFAULT.TEST_KYLIN_FACT", tblDesc.getIdentity());
    Assert.assertEquals("DEFAULT", tblDesc.getDatabase());
    Assert.assertEquals("DEFAULT", tblDesc.getProject());
    Assert.assertEquals(tblDesc.getIdentity(), tblExtDesc.getIdentity());
    Assert.assertEquals(tblDesc.getProject(), tblExtDesc.getProject());

    ColumnDesc[] columnDescs = tblDesc.getColumns();
    Assert.assertEquals(tblDesc.getColumnCount(), columnDescs.length);
    Assert.assertNotNull(columnDescs[0].getName());
    Assert.assertNotNull(columnDescs[0].getDatatype());
    Assert.assertNotNull(columnDescs[0].getType());
    Assert.assertNotNull(columnDescs[0].getId());
}
 

public AbstractLookupRowEncoder(TableDesc tableDesc, String[] keyColumns) {
    this.columnsNum = tableDesc.getColumns().length;
    this.keyIndexes = new int[keyColumns.length];
    this.valueIndexes = new int[columnsNum - keyColumns.length];
    int keyIdx = 0;
    int valIdx = 0;
    for (int i = 0; i < columnsNum; i++) {
        boolean isKeyColumn = false;
        for (String keyColumn : keyColumns) {
            if (keyColumn.equals(tableDesc.getColumns()[i].getName())) {
                isKeyColumn = true;
                break;
            }
        }
        if (isKeyColumn) {
            keyIndexes[keyIdx] = i;
            keyIdx++;
        } else {
            valueIndexes[valIdx] = i;
            valIdx++;
        }
    }
}
 

private List<String> generateCreateH2IndexSql(TableDesc tableDesc) {
    List<String> result = Lists.newArrayList();
    int x = 0;
    for (ColumnDesc col : tableDesc.getColumns()) {
        if ("T".equalsIgnoreCase(col.getIndex())) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("CREATE INDEX IDX_" + tableDesc.getName() + "_" + x + " ON " + tableDesc.getIdentity() + "("
                    + col.getName() + ")");
            ddl.append("\n");
            result.add(ddl.toString());
            x++;
        }
    }

    return result;
}
 

private String generateCreateH2TableSql(TableDesc tableDesc, String csvFilePath) {
    StringBuilder ddl = new StringBuilder();
    StringBuilder csvColumns = new StringBuilder();

    ddl.append("CREATE TABLE " + tableDesc.getIdentity() + "\n");
    ddl.append("(" + "\n");

    for (int i = 0; i < tableDesc.getColumns().length; i++) {
        ColumnDesc col = tableDesc.getColumns()[i];
        if (col.isComputedColumn()) {
            continue;
        }
        if (i > 0) {
            ddl.append(",");
            csvColumns.append(",");
        }
        ddl.append(col.getName() + " " + getH2DataType((col.getDatatype())) + "\n");
        csvColumns.append(col.getName());
    }
    ddl.append(")" + "\n");
    ddl.append("AS SELECT * FROM CSVREAD('" + csvFilePath + "', '" + csvColumns
            + "', 'charset=UTF-8 fieldSeparator=,');");

    return ddl.toString();
}
 

private void generateCreateTableDDL(Set<TableDesc> tables, PrintWriter out) {
    for (TableDesc t : tables) {
        if (t.isView())
            continue;

        out.print("DROP TABLE IF EXISTS " + normHiveIdentifier(t.getIdentity()) + ";\n");

        out.print("CREATE TABLE " + normHiveIdentifier(t.getIdentity()) + "(" + "\n");

        for (int i = 0; i < t.getColumns().length; i++) {
            ColumnDesc col = t.getColumns()[i];
            out.print("    ");
            if (i > 0) {
                out.print(",");
            }
            out.print(normHiveIdentifier(col.getName()) + " " + hiveType(col.getType()) + "\n");
        }

        out.print(")" + "\n");
        out.print("ROW FORMAT DELIMITED FIELDS TERMINATED BY ','" + "\n");
        out.print("STORED AS TEXTFILE" + ";\n");
        out.print("\n");
    }
}
 

private String[] generateCreateTableSql(TableDesc tableDesc) {

        String dropsql = "DROP TABLE IF EXISTS " + tableDesc.getIdentity();
        String dropsql2 = "DROP VIEW IF EXISTS " + tableDesc.getIdentity();

        StringBuilder ddl = new StringBuilder();
        ddl.append("CREATE TABLE " + tableDesc.getIdentity() + "\n");
        ddl.append("(" + "\n");

        for (int i = 0; i < tableDesc.getColumns().length; i++) {
            ColumnDesc col = tableDesc.getColumns()[i];
            if (i > 0) {
                ddl.append(",");
            }
            ddl.append(col.getName() + " " + getHiveDataType((col.getDatatype())) + "\n");
        }

        ddl.append(")" + "\n");
        ddl.append("ROW FORMAT DELIMITED FIELDS TERMINATED BY ','" + "\n");
        ddl.append("STORED AS TEXTFILE");

        return new String[] { dropsql, dropsql2, ddl.toString() };
    }
 

private String convertNull(String content, TableDesc tableDesc, String nullValue) {
    StringBuffer sb = new StringBuffer();

    String[] nullStrings = new String[tableDesc.getColumns().length];
    for (int i = 0; i < tableDesc.getColumns().length; ++i) {
        ColumnDesc columnDesc = tableDesc.getColumns()[i];
        if (ColumnGenConfig.isNullable(columnDesc)) {
            nullStrings[i] = ColumnGenConfig.getNullStr(columnDesc);
        } else {
            nullStrings[i] = null;
        }
    }

    String[] lines = content.split("\\n");
    for (int j = 0; j < lines.length; ++j) {
        String line = lines[j];
        String[] columnValues = line.split(",", -1);
        for (int i = 0; i < columnValues.length; ++i) {
            String columnValue = columnValues[i];
            if (nullStrings[i] != null && nullStrings[i].equals(columnValue)) {
                columnValue = nullValue;
            }
            sb.append(columnValue);
            if (i < columnValues.length - 1) {
                sb.append(",");
            }
        }
        if (j < lines.length - 1) {
            sb.append("\n");
        }
    }

    return sb.toString();
}
 

@Test
public void testLoadTableMetadata() throws SQLException {
    String tableName = "tb1";
    String databaseName = "testdb";
    ResultSet rs1 = mock(ResultSet.class);
    when(rs1.next()).thenReturn(true).thenReturn(false);
    when(rs1.getString("TABLE_TYPE")).thenReturn("TABLE");

    ResultSet rs2 = mock(ResultSet.class);
    when(rs2.next()).thenReturn(true).thenReturn(true).thenReturn(true).thenReturn(false);
    when(rs2.getString("COLUMN_NAME")).thenReturn("COL1").thenReturn("COL2").thenReturn("COL3");
    when(rs2.getInt("DATA_TYPE")).thenReturn(Types.VARCHAR).thenReturn(Types.INTEGER).thenReturn(Types.DECIMAL);
    when(rs2.getInt("COLUMN_SIZE")).thenReturn(128).thenReturn(10).thenReturn(19);
    when(rs2.getInt("DECIMAL_DIGITS")).thenReturn(0).thenReturn(0).thenReturn(4);
    when(rs2.getInt("ORDINAL_POSITION")).thenReturn(1).thenReturn(3).thenReturn(2);
    when(rs2.getString("REMARKS")).thenReturn("comment1").thenReturn("comment2").thenReturn("comment3");

    when(jdbcMetadata.getTable(dbmd, databaseName, tableName)).thenReturn(rs1);
    when(jdbcMetadata.listColumns(dbmd, databaseName, tableName)).thenReturn(rs2);

    Pair<TableDesc, TableExtDesc> result = jdbcExplorer.loadTableMetadata(databaseName, tableName, "proj");
    TableDesc tableDesc = result.getFirst();
    ColumnDesc columnDesc = tableDesc.getColumns()[1];

    Assert.assertEquals(databaseName.toUpperCase(Locale.ROOT), tableDesc.getDatabase());
    Assert.assertEquals(3, tableDesc.getColumnCount());
    Assert.assertEquals("TABLE", tableDesc.getTableType());
    Assert.assertEquals("COL2", columnDesc.getName());
    Assert.assertEquals("integer", columnDesc.getTypeName());
    Assert.assertEquals("comment2", columnDesc.getComment());
    Assert.assertEquals(databaseName.toUpperCase(Locale.ROOT) + "." + tableName.toUpperCase(Locale.ROOT),
            result.getSecond().getIdentity());
}
 

/**
 * @param context
 * @param cube
 * @param value
 */
private void validateColumnParameter(ValidateContext context, CubeDesc cube, String value) {
    String factTable = cube.getFactTable();
    if (StringUtils.isEmpty(factTable)) {
        context.addResult(ResultLevel.ERROR, "Fact table can not be null.");
        return;
    }
    TableDesc table = MetadataManager.getInstance(cube.getConfig()).getTableDesc(factTable);
    if (table == null) {
        context.addResult(ResultLevel.ERROR, "Fact table can not be found: " + cube);
        return;
    }
    // Prepare column set
    Set<String> set = new HashSet<String>();
    ColumnDesc[] cdesc = table.getColumns();
    for (int i = 0; i < cdesc.length; i++) {
        ColumnDesc columnDesc = cdesc[i];
        set.add(columnDesc.getName());
    }

    String[] items = value.split(",");
    for (int i = 0; i < items.length; i++) {
        String item = items[i].trim();
        if (StringUtils.isEmpty(item)) {
            continue;
        }
        if (!set.contains(item)) {
            context.addResult(ResultLevel.ERROR, "Column [" + item + "] does not exist in factable table" + factTable);
        }
    }

}
 

private List<String> generateCreateH2IndexSql(TableDesc tableDesc) {
    List<String> result = Lists.newArrayList();
    int x = 0;
    for (ColumnDesc col : tableDesc.getColumns()) {
        if ("T".equalsIgnoreCase(col.getIndex())) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("CREATE INDEX IDX_" + tableDesc.getName() + "_" + x + " ON " + tableDesc.getIdentity() + "(" + col.getName() + ")");
            ddl.append("\n");
            result.add(ddl.toString());
            x++;
        }
    }

    return result;
}
 

@Test
public void testLoadTableMetadata() throws SQLException {
    String tableName = "tb1";
    String databaseName = "testdb";
    ResultSet rs1 = mock(ResultSet.class);
    when(rs1.next()).thenReturn(true).thenReturn(false);
    when(rs1.getString("TABLE_TYPE")).thenReturn("TABLE");

    ResultSet rs2 = mock(ResultSet.class);
    when(rs2.next()).thenReturn(true).thenReturn(true).thenReturn(true).thenReturn(false);
    when(rs2.getString("COLUMN_NAME")).thenReturn("COL1").thenReturn("COL2").thenReturn("COL3");
    when(rs2.getInt("DATA_TYPE")).thenReturn(Types.VARCHAR).thenReturn(Types.INTEGER).thenReturn(Types.DECIMAL);
    when(rs2.getInt("COLUMN_SIZE")).thenReturn(128).thenReturn(10).thenReturn(19);
    when(rs2.getInt("DECIMAL_DIGITS")).thenReturn(0).thenReturn(0).thenReturn(4);
    when(rs2.getInt("ORDINAL_POSITION")).thenReturn(1).thenReturn(3).thenReturn(2);
    when(rs2.getString("REMARKS")).thenReturn("comment1").thenReturn("comment2").thenReturn("comment3");

    when(jdbcMetadata.getTable(dbmd, databaseName, tableName)).thenReturn(rs1);
    when(jdbcMetadata.listColumns(dbmd, databaseName, tableName)).thenReturn(rs2);

    Pair<TableDesc, TableExtDesc> result = jdbcExplorer.loadTableMetadata(databaseName, tableName, "proj");
    TableDesc tableDesc = result.getFirst();
    ColumnDesc columnDesc = tableDesc.getColumns()[1];

    Assert.assertEquals(databaseName.toUpperCase(Locale.ROOT), tableDesc.getDatabase());
    Assert.assertEquals(3, tableDesc.getColumnCount());
    Assert.assertEquals("TABLE", tableDesc.getTableType());
    Assert.assertEquals("COL2", columnDesc.getName());
    Assert.assertEquals("integer", columnDesc.getTypeName());
    Assert.assertEquals("comment2", columnDesc.getComment());
    Assert.assertEquals(databaseName.toUpperCase(Locale.ROOT) + "." + tableName.toUpperCase(Locale.ROOT),
            result.getSecond().getIdentity());
}
 

private void checkAllColumnsInHiveTableDesc(TableDesc hiveTable, TableDesc newTable, List<String> issues) {
    ColumnDesc[] hiveTableCols = hiveTable.getColumns();
    ColumnDesc[] newTableCols = newTable.getColumns();

    if (hiveTableCols.length < newTableCols.length) {
        Set<String> colNamesNew = Lists.newArrayList(newTableCols).stream().map(input -> input.getName())
                .collect(Collectors.toSet());
        Set<String> colNamesHive = Lists.newArrayList(hiveTableCols).stream().map(input -> input.getName())
                .collect(Collectors.toSet());
        colNamesNew.removeAll(colNamesHive);
        issues.add(format(Locale.ROOT, "columns %s are not existing in hive table", colNamesNew));
        return;
    }

    Map<String, ColumnDesc> hiveColMap = Lists.newArrayList(hiveTableCols).stream()
            .collect(Collectors.toMap(input -> input.getName().toUpperCase(Locale.ROOT), input -> input));
    Map<String, ColumnDesc> newColMap = Lists.newArrayList(newTableCols).stream()
            .collect(Collectors.toMap(input -> input.getName().toUpperCase(Locale.ROOT), input -> input));

    List<String> violateColumns = Lists.newArrayList();
    for (String colName : newColMap.keySet()) {
        ColumnDesc hiveCol = hiveColMap.get(colName);
        if (hiveCol == null) {
            issues.add(format(Locale.ROOT, "column %s is not existing in hive table", colName));
            continue;
        }
        if (!isColumnCompatible(hiveCol, newColMap.get(colName))) {
            violateColumns.add(colName);
        }
    }
    if (!violateColumns.isEmpty()) {
        issues.add(format(Locale.ROOT, "Columns %s are incompatible " + "in hive", violateColumns));
    }
}
 

@Override
public void createSampleTable(TableDesc table) throws Exception {
    LinkedHashMap<String, String> columnInfo = Maps.newLinkedHashMap();
    for (ColumnDesc columnDesc : table.getColumns()) {
        columnInfo.put(columnDesc.getName(), columnDesc.getTypeName());
    }
    String[] sqls = dataSource.buildSqlToCreateTable(table.getIdentity(), columnInfo);
    dataSource.executeUpdate(sqls);
}
 

private List<String> generateCreateH2IndexSql(TableDesc tableDesc) {
    List<String> result = Lists.newArrayList();
    int x = 0;
    for (ColumnDesc col : tableDesc.getColumns()) {
        if ("T".equalsIgnoreCase(col.getIndex())) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("CREATE INDEX IDX_" + tableDesc.getName() + "_" + x + " ON " + tableDesc.getIdentity() + "(" + col.getName() + ")");
            ddl.append("\n");
            result.add(ddl.toString());
            x++;
        }
    }

    return result;
}