org.apache.flink.table.client.utils.SqlJobUtil Maven / Gradle / Ivy
/*
* 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.flink.table.client.utils;
import org.apache.flink.sql.parser.ddl.SqlAnalyzeTable;
import org.apache.flink.sql.parser.ddl.SqlColumnType;
import org.apache.flink.sql.parser.ddl.SqlCreateDatabase;
import org.apache.flink.sql.parser.ddl.SqlCreateFunction;
import org.apache.flink.sql.parser.ddl.SqlCreateTable;
import org.apache.flink.sql.parser.ddl.SqlCreateView;
import org.apache.flink.sql.parser.ddl.SqlNodeInfo;
import org.apache.flink.sql.parser.ddl.SqlParseArrayType;
import org.apache.flink.sql.parser.ddl.SqlParseMapType;
import org.apache.flink.sql.parser.ddl.SqlParseRowType;
import org.apache.flink.sql.parser.ddl.SqlTableColumn;
import org.apache.flink.sql.parser.plan.SqlParseException;
import org.apache.flink.sql.parser.util.SqlInfo;
import org.apache.flink.sql.parser.util.SqlLists;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.BatchTableEnvironment;
import org.apache.flink.table.api.Column;
import org.apache.flink.table.api.RichTableSchema;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.TableEnvironment;
import org.apache.flink.table.api.TableException;
import org.apache.flink.table.api.TableSchema;
import org.apache.flink.table.api.functions.UserDefinedFunction;
import org.apache.flink.table.calcite.FlinkPlannerImpl;
import org.apache.flink.table.catalog.CatalogDatabase;
import org.apache.flink.table.catalog.CatalogTable;
import org.apache.flink.table.catalog.CatalogView;
import org.apache.flink.table.catalog.config.CatalogDatabaseConfig;
import org.apache.flink.table.catalog.config.CatalogTableConfig;
import org.apache.flink.table.client.gateway.SqlExecutionException;
import org.apache.flink.table.dataformat.BaseRow;
import org.apache.flink.table.errorcode.TableErrors;
import org.apache.flink.table.plan.stats.AnalyzeStatistic;
import org.apache.flink.table.plan.stats.TableStats;
import org.apache.flink.table.runtime.functions.python.PythonUDFUtil;
import org.apache.flink.table.sources.BatchTableSource;
import org.apache.flink.table.sources.StreamTableSource;
import org.apache.flink.table.types.ArrayType;
import org.apache.flink.table.types.DataType;
import org.apache.flink.table.types.DataTypes;
import org.apache.flink.table.types.DecimalType;
import org.apache.flink.table.types.GenericType;
import org.apache.flink.table.types.InternalType;
import org.apache.flink.table.types.MapType;
import org.apache.flink.table.types.RowType;
import org.apache.flink.table.types.TimestampType;
import org.apache.flink.util.Preconditions;
import org.apache.flink.util.StringUtils;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOrderBy;
import org.apache.calcite.sql.SqlProperty;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.util.Pair;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* Util to transform a sql context (a sequence of sql statements) into a flink job.
*/
public class SqlJobUtil {
private static final Logger LOG = LoggerFactory.getLogger(SqlJobUtil.class);
/**
* Register an external database to current catalog.
*
* @param tableEnv table environment.
* @param sqlNodeInfo external database defined in ddl.
*/
public static void registerExternalDatabase(TableEnvironment tableEnv, SqlNodeInfo sqlNodeInfo) {
final SqlCreateDatabase sqlCreateDatabase = (SqlCreateDatabase) sqlNodeInfo.getSqlNode();
// set with properties
SqlNodeList propertyList = sqlCreateDatabase.getPropertyList();
Map properties = new HashMap<>();
if (propertyList != null) {
for (SqlNode sqlNode : propertyList) {
SqlProperty sqlProperty = (SqlProperty) sqlNode;
properties.put(sqlProperty.getKeyString(), sqlProperty.getValueString());
}
}
// set comment
String comment = "";
if (sqlCreateDatabase.getComment() != null) {
comment = sqlCreateDatabase.getComment().getNlsString().getValue();
}
properties.put(CatalogDatabaseConfig.DATABASE_COMMENT, comment);
CatalogDatabase catalogDatabase = new CatalogDatabase(properties);
tableEnv.registerDatabase(sqlCreateDatabase.fullDatabaseName(), catalogDatabase,
sqlCreateDatabase.isIfNotExists());
}
/**
* Register an external table to current catalog.
* @param tableEnv table environment
* @param sqlNodeInfo external table defined in ddl.
*/
public static void registerExternalTable(TableEnvironment tableEnv, SqlNodeInfo sqlNodeInfo) {
final SqlCreateTable sqlCreateTable = (SqlCreateTable) sqlNodeInfo.getSqlNode();
// set with properties
SqlNodeList propertyList = sqlCreateTable.getPropertyList();
Map properties = new HashMap<>();
if (propertyList != null) {
for (SqlNode sqlNode : propertyList) {
SqlProperty sqlProperty = (SqlProperty) sqlNode;
properties.put(
sqlProperty.getKeyString(), sqlProperty.getValueString());
}
}
String tableType = properties.get("type");
if (StringUtils.isNullOrWhitespaceOnly(tableType)) {
throw new SqlExecutionException("Please specify table type in table options in CREATE TABLE.");
}
RichTableSchema richTableSchema = createBlinkTableSchema(sqlCreateTable);
TableSchema tableSchema = SqlJobUtil.getTableSchema(tableEnv, sqlCreateTable, richTableSchema);
String computedColumnsSql = SqlJobUtil.getComputedColumnsSql(sqlCreateTable);
String rowtimeField = null;
long offset = -1;
if (sqlCreateTable.getWatermark() != null) {
try {
rowtimeField = sqlCreateTable.getWatermark().getColumnName().toString();
offset = sqlCreateTable.getWatermark().getWatermarkOffset();
} catch (SqlParseException e) {
throw new SqlExecutionException(e.getMessage(), e);
}
}
boolean isStreaming = true;
if (tableEnv instanceof BatchTableEnvironment) {
isStreaming = false;
}
String tableComment = "";
if (sqlCreateTable.getComment() != null) {
tableComment = sqlCreateTable.getComment().getNlsString().getValue();
}
properties.put(CatalogTableConfig.IS_STREAMING, String.valueOf(isStreaming));
LinkedHashSet partitionColumns = null;
if (richTableSchema.getPartitionColumns() != null
&& richTableSchema.getPartitionColumns().size() > 0) {
partitionColumns = new LinkedHashSet<>(richTableSchema.getPartitionColumns());
}
long now = System.currentTimeMillis();
CatalogTable catalogTable = new CatalogTable(
tableType,
tableSchema,
properties,
richTableSchema,
TableStats.UNKNOWN_STATS,
tableComment,
partitionColumns,
partitionColumns != null && partitionColumns.size() > 0,
computedColumnsSql,
rowtimeField,
offset,
now,
now);
tableEnv.registerTable(sqlCreateTable.fullTableName(), catalogTable);
}
/**
* Register an external view to current catalog.
* @param tableEnv
* @param sqlNodeInfo
*/
public static void registerExternalView(TableEnvironment tableEnv, SqlNodeInfo sqlNodeInfo) {
final SqlCreateView sqlCreateView = (SqlCreateView) sqlNodeInfo.getSqlNode();
final String viewName = sqlCreateView.getName();
String subQuerySql = sqlCreateView.getSubQuerySql();
final List aliasNames = sqlCreateView.getFieldNames();
SqlNodeList propertyList = sqlCreateView.getPropertyList();
Table view = tableEnv.sqlQuery(subQuerySql);
if (!aliasNames.isEmpty()) {
// if we have alias column names
// packing the original query with a SELECT clause
String viewSql = "SELECT %s FROM (" + subQuerySql.trim() + ")";
StringBuilder aliasClause = new StringBuilder();
List inputFields = view.getRelNode().getRowType().getFieldNames();
assert aliasNames.size() == inputFields.size();
if (aliasNames.size() != inputFields.size()) {
throw new RuntimeException("View definition and input fields not match: \nDef Fields: "
+ aliasNames.toString() + "\nInput Fields: " + inputFields.toString());
}
for (int idx = 0; idx < aliasNames.size(); ++idx) {
aliasClause.append("`" + inputFields.get(idx) + "` as `" + aliasNames.get(idx) + "`");
if (idx < aliasNames.size() - 1) {
aliasClause.append(", ");
}
}
subQuerySql = String.format(viewSql, aliasClause.toString());
view = tableEnv.sqlQuery(subQuerySql);
}
String expandedSubQuerySql = getValidatedSqlQuery(tableEnv, subQuerySql);
long now = System.currentTimeMillis();
Map properties = new HashMap<>();
populateProperties(propertyList, properties);
CatalogView catalogView = new CatalogView(
null,
view.getSchema(),
properties,
null,
TableStats.UNKNOWN_STATS,
null,
new LinkedHashSet<>(),
false,
null,
null,
-1L,
now,
now,
subQuerySql,
expandedSubQuerySql
);
tableEnv.registerView(viewName, catalogView);
}
/**
* Parses a sql context as a list of {@link SqlNodeInfo}.
*
* @throws SqlParseException if there is any syntactic error
*/
public static List parseSqlContext(TableEnvironment tableEnv, String sqlContext) {
// use planner in TableEnvironment
FlinkPlannerImpl planner = tableEnv.getFlinkPlanner();
// simple parse the sql content, which separated by semicolon
List sqlList = SqlLists.getSQLList(sqlContext);
for (SqlInfo sqlInfo : sqlList) {
int startLine = sqlInfo.getLine();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < startLine - 1; i++) {
sqlBuilder.append('\n');
}
String sql = sqlBuilder.append(sqlInfo.getSqlContent()).toString();
sqlInfo.setSqlContent(sql);
}
List sqlNodeList = new ArrayList<>();
sqlList
.stream()
.filter((sqlInfo) -> !StringUtils.isNullOrWhitespaceOnly(sqlInfo.getSqlContent()))
.forEach((sqlInfo) -> {
SqlNodeInfo sqlNodeInfo = new SqlNodeInfo();
SqlNode node = planner.parse(sqlInfo.getSqlContent());
if (node instanceof SqlCreateView) {
((SqlCreateView) node).setSubQuerySql(getOriginalQueryForView(sqlInfo.getSqlContent(),
(SqlCreateView) node));
}
sqlNodeInfo.setSqlNode(node);
sqlNodeInfo.setOriginSql(sqlInfo.getSqlContent());
sqlNodeList.add(sqlNodeInfo);
});
return sqlNodeList;
}
// extracts the original query text for SqlCreateView
private static String getOriginalQueryForView(String ddl, SqlCreateView sqlCreateView) {
SqlParserPos selPos;
if (sqlCreateView.getQuery() instanceof SqlOrderBy) {
selPos = ((SqlOrderBy) sqlCreateView.getQuery()).query.getParserPosition();
} else {
selPos = sqlCreateView.getQuery().getParserPosition();
}
final char lineSep = '\n';
int lineNum = 1;
int index = 0;
while (index < ddl.length() && lineNum < selPos.getLineNum()) {
if (ddl.charAt(index++) == lineSep) {
lineNum++;
}
}
int beginIdx = index + selPos.getColumnNum() - 1;
Preconditions.checkState(beginIdx < ddl.length(), "Cannot extract original query text for view in DDL: " + ddl);
return ddl.substring(beginIdx);
}
/**
* extracts the create table ddl from catalogTable.
* @param catalogTable
* @return
*/
public static String getCreatTableFromCatalogTable(CatalogTable catalogTable, String[] tablePath) {
StringBuilder sb = new StringBuilder();
List tablePaths = Arrays.asList(tablePath).stream().map(s -> "`" + s + "`").collect(Collectors.toList());
sb.append("CREATE TABLE ").append(org.apache.commons.lang3.StringUtils.join(tablePaths, ".")).append("(\n");
TableSchema tableSchema = catalogTable.getTableSchema();
RichTableSchema richTableSchema = catalogTable.getRichTableSchema();
Column[] columns = tableSchema.getColumns();
String[] columnsDefine = new String[columns.length];
String[] computedColumnsSql = null;
if (catalogTable.getComputedColumnsSql() != null && !catalogTable.getComputedColumnsSql().isEmpty()) {
computedColumnsSql = catalogTable.getComputedColumnsSql().split(",");
}
for (int i = 0; i < columns.length; i++) {
if (null != computedColumnsSql && computedColumnsSql[i].contains(" AS ")) {
String[] operands = computedColumnsSql[i].split("\\s+AS\\s+");
if (operands.length != 2) {
throw new TableException("Can not get correct definition from " + computedColumnsSql[i]);
}
columnsDefine[i] = operands[1] + " AS " + operands[0];
} else {
columnsDefine[i] = "`" + columns[i].name() + "`\t" + getSqlType(columns[i].internalType());
}
}
sb.append(org.apache.commons.lang3.StringUtils.join(columnsDefine, ",\n"));
if (catalogTable.getWatermarkOffset() > -1) {
sb.append(",\n");
sb.append("WATERMARK FOR `" + catalogTable.getRowTimeField() + "` AS withOffset(`" +
catalogTable.getRowTimeField() + "`," + catalogTable.getWatermarkOffset() + ")");
}
if (tableSchema.getPrimaryKeys() != null && tableSchema.getPrimaryKeys().length != 0) {
sb.append(",\n");
List pks = Arrays.asList(tableSchema.getPrimaryKeys())
.stream().map(s -> "`" + s + "`").collect(Collectors.toList());
sb.append("PRIMARY KEY ("
+ org.apache.commons.lang3.StringUtils.join(pks, ",")
+ ")");
}
if (richTableSchema.getIndexes() != null && richTableSchema.getIndexes().size() != 0) {
for (RichTableSchema.Index index : richTableSchema.getIndexes()) {
sb.append(",\n");
if (index.unique) {
sb.append("unique index");
} else {
sb.append("index");
}
List indexKeys = index.keyList.stream().map(s -> "`" + s + "`").collect(Collectors.toList());
sb.append("(" + org.apache.commons.lang3.StringUtils.join(indexKeys, ",") + ")");
}
}
sb.append("\n)");
if (catalogTable.getComment() != null) {
sb.append("COMMENT '" + catalogTable.getComment() + "' \n");
}
if (catalogTable.isPartitioned()) {
sb.append("PARTITIONED BY(" +
org.apache.commons.lang3.StringUtils.join(
catalogTable.getPartitionColumnNames()
.stream().map(s -> "`" + s + "`")
.collect(Collectors.toList()), ",") + ")\n");
}
if (catalogTable.getProperties() != null) {
sb.append("WITH (\n");
List kvItems = new ArrayList<>();
for (Map.Entry entry : catalogTable.getProperties().entrySet()) {
kvItems.add(entry.getKey() + " = '" + entry.getValue() + "'");
}
sb.append(org.apache.commons.lang3.StringUtils.join(kvItems, ",\n"));
sb.append("\n)\n");
}
return sb.toString();
}
/**
* Registers functions to the tableEnvironment.
*
* @param tableEnv the {@link TableEnvironment} of the sql job
* @param sqlNodeInfoList the parsed result of a sql context
* @return true or false
*/
public static boolean registerFunctions(
TableEnvironment tableEnv,
List sqlNodeInfoList,
String userPyLibs) {
Map pyUdfNameClass = new HashMap<>();
for (SqlNodeInfo sqlNodeInfo : sqlNodeInfoList) {
if (sqlNodeInfo.getSqlNode() instanceof SqlCreateFunction) {
SqlCreateFunction sqlCreateFunction = (SqlCreateFunction) sqlNodeInfo.getSqlNode();
String functionName = sqlCreateFunction.getFunctionName().toString();
boolean isPyUdf = sqlCreateFunction.getClassName().startsWith(PythonUDFUtil.PYUDF_PREFIX);
if (isPyUdf) {
String className = sqlCreateFunction.getClassName()
.substring(PythonUDFUtil.PYUDF_PREFIX.length());
pyUdfNameClass.put(functionName, className);
continue;
}
// Register in catalog
// TODO: [BLINK-18570607] re-enable register external functions in SqlJobUtil
// tableEnv.registerExternalFunction(
// null, functionName, sqlCreateFunction.getClassName(), false);
throw new UnsupportedOperationException(
"catalogs haven't support registering functions yet");
}
}
if (pyUdfNameClass.size() > 0) {
ArrayList pyFiles = new ArrayList<>(Arrays.asList(userPyLibs.split(",")));
Map pyUDFs =
PythonUDFUtil.registerPyUdfsToTableEnvironment(tableEnv, pyFiles, pyUdfNameClass);
// TODO How to handle the python function?
}
return true;
}
/** Caution that this returns schema fields come from the original table, that means, the
* computed columns info are ignored. */
public static RichTableSchema createBlinkTableSchema(SqlCreateTable sqlCreateTable) {
//set columnList
SqlNodeList columnList = sqlCreateTable.getColumnList();
int columnCount = columnList.size();
String[] columnNames = new String[columnCount];
boolean[] nullables = new boolean[columnCount];
InternalType[] columnTypes = new InternalType[columnCount];
List headerFields = new ArrayList<>();
RichTableSchema schema;
if (!sqlCreateTable.containsComputedColumn()) {
// all column is SqlTableColumn
for (int i = 0; i < columnCount; i++) {
SqlTableColumn columnNode = (SqlTableColumn) columnList.get(i);
String columnName = columnNode.getName().getSimple();
columnNames[i] = columnName;
try {
columnTypes[i] = getInternalType(columnNode.getType());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(
TableErrors.INST.sqlUnSupportedColumnType(
sqlCreateTable.getTableName().toString(),
columnName,
columnNode.getType().getTypeName().getSimple()));
}
nullables[i] = columnNode.getType().getNullable() == null ? true : columnNode.getType().getNullable();
if (columnNode.isHeader()) {
headerFields.add(columnName);
}
}
schema = new RichTableSchema(columnNames, columnTypes, nullables);
} else {
// some columns are computed column
List originNameList = new ArrayList<>();
List originTypeList = new ArrayList<>();
List originNullableList = new ArrayList<>();
for (int i = 0; i < columnCount; i++) {
SqlNode node = columnList.get(i);
if (node instanceof SqlTableColumn) {
SqlTableColumn columnNode = (SqlTableColumn) columnList.get(i);
String columnName = columnNode.getName().getSimple();
try {
InternalType columnType = getInternalType(columnNode.getType());
originTypeList.add(columnType);
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(
TableErrors.INST.sqlUnSupportedColumnType(
sqlCreateTable.getTableName().toString(),
columnName,
columnNode.getType().getTypeName().getSimple()));
}
originNameList.add(columnName);
originNullableList.add(columnNode.getType().getNullable() == null ? true : columnNode.getType().getNullable());
if (columnNode.isHeader()) {
headerFields.add(columnName);
}
}
}
String[] originColumnNames = originNameList.toArray(new String[originNameList.size()]);
InternalType[] originColumnTypes = originTypeList.toArray(new InternalType[originTypeList.size()]);
boolean[] originNullables = new boolean[originNullableList.size()];
for (int i = 0; i < originNullables.length; i++) {
originNullables[i] = originNullableList.get(i);
}
schema = new RichTableSchema(originColumnNames, originColumnTypes, originNullables);
}
schema.setHeaderFields(headerFields);
//set primary key
if (sqlCreateTable.getPrimaryKeyList() != null) {
String[] primaryKeys = new String[sqlCreateTable.getPrimaryKeyList().size()];
for (int i = 0; i < primaryKeys.length; i++) {
primaryKeys[i] = sqlCreateTable.getPrimaryKeyList().get(i).toString();
}
schema.setPrimaryKey(primaryKeys);
}
//set unique key
List uniqueKeyList = sqlCreateTable.getUniqueKeysList();
if (uniqueKeyList != null) {
List> ukList = new ArrayList<>();
for (SqlNodeList uniqueKeys: uniqueKeyList) {
List uk = new ArrayList<>();
for (int i = 0; i < uniqueKeys.size(); i++) {
uk.add(uniqueKeys.get(i).toString());
}
ukList.add(uk);
}
schema.setUniqueKeys(ukList);
}
//set index
List indexKeyList = sqlCreateTable.getIndexKeysList();
if (indexKeyList != null) {
List indexes = new ArrayList<>();
for (SqlCreateTable.IndexWrapper idx : indexKeyList) {
List keyList = new ArrayList<>();
for (int i = 0; i < idx.indexKeys.size(); i++) {
keyList.add(idx.indexKeys.get(i).toString());
}
indexes.add(new RichTableSchema.Index(idx.unique, keyList));
}
schema.setIndexes(indexes);
}
// set partition key
SqlNodeList partitionKey = sqlCreateTable.getPartitionKeysList();
if (partitionKey != null) {
schema.setPartitionColumns(partitionKey
.getList()
.stream()
.map(p -> ((SqlIdentifier) p).getSimple())
.toArray(String[]::new));
}
return schema;
}
/**
* Analyze table statistics.
*
* @param tableEnv the {@link TableEnvironment} of the sql job
* @param sqlNodeInfo the parsed result of a sql statement
*/
public static void analyzeTableStats(TableEnvironment tableEnv, SqlNodeInfo sqlNodeInfo) {
final SqlAnalyzeTable sqlAnalyzeTable = (SqlAnalyzeTable) sqlNodeInfo.getSqlNode();
String[] tablePath = sqlAnalyzeTable.getTableName().names.toArray(new String[]{});
String[] columnNames = getColumnsToAnalyze(sqlAnalyzeTable);
TableStats newStats = AnalyzeStatistic.generateTableStats(tableEnv, tablePath, columnNames);
TableStats oldStats = tableEnv.getTableStats(tablePath);
TableStats.Builder builder = TableStats.builder();
if (oldStats != null && !oldStats.equals(TableStats.UNKNOWN_STATS)) {
builder.tableStats(oldStats);
}
builder.rowCount(newStats.rowCount).colStats(newStats.colStats);
tableEnv.alterTableStats(tablePath, builder.build());
}
private static String[] getColumnsToAnalyze(SqlAnalyzeTable analyzeTable) {
if (!analyzeTable.isWithColumns()) {
return new String[] {};
}
SqlNodeList columnList = analyzeTable.getColumnList();
int columnCount = columnList.size();
// analyze all columns or specified columns.
if (columnCount == 0) {
return new String[] {"*"};
}
String[] columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
SqlIdentifier column = (SqlIdentifier) columnList.get(i);
columnNames[i] = column.getSimple();
}
return columnNames;
}
/**
* Maps a sql column type to a flink {@link InternalType}.
*
* @param type the sql column type
* @return the corresponding flink type
*/
public static InternalType getInternalType(SqlDataTypeSpec type) {
switch (SqlColumnType.getType(type.getTypeName().getSimple())) {
case BOOLEAN:
return DataTypes.BOOLEAN;
case TINYINT:
return DataTypes.BYTE;
case SMALLINT:
return DataTypes.SHORT;
case INT:
return DataTypes.INT;
case BIGINT:
return DataTypes.LONG;
case FLOAT:
return DataTypes.FLOAT;
case DECIMAL:
if (type.getPrecision() >= 0) {
return DecimalType.of(type.getPrecision(), type.getScale());
}
return DecimalType.USER_DEFAULT;
case DOUBLE:
return DataTypes.DOUBLE;
case DATE:
return DataTypes.DATE;
case TIME:
return DataTypes.TIME;
case TIMESTAMP:
return DataTypes.TIMESTAMP;
case VARCHAR:
return DataTypes.STRING;
case VARBINARY:
return DataTypes.BYTE_ARRAY;
case ANY:
return new GenericType<>(Object.class);
case ARRAY:
SqlParseArrayType arrayType = (SqlParseArrayType) type.getTypeName();
SqlDataTypeSpec elementType = arrayType.getElementType();
return DataTypes.createArrayType(getInternalType(elementType));
case MAP:
SqlParseMapType mapType = (SqlParseMapType) type.getTypeName();
return DataTypes.createMapType(getInternalType(mapType.getKeyType()),
getInternalType(mapType.getValType()));
case ROW:
SqlParseRowType parseRowType = (SqlParseRowType) type.getTypeName();
String[] names = new String[parseRowType.getArity()];
InternalType[] types = new InternalType[parseRowType.getArity()];
for (int i = 0; i < parseRowType.getArity(); i++) {
names[i] = parseRowType.getFieldName(i).getSimple();
types[i] = getInternalType(parseRowType.getFieldType(i));
}
return DataTypes.createRowTypeV2(types, names);
default:
LOG.warn("Unsupported sql column type: {}", type);
throw new IllegalArgumentException("Unsupported sql column type " + type + " !");
}
}
/**
* Maps a flink {@link InternalType} to a sql column type.
*
* @param type the sql column flink's internalType
* @return the corresponding flink type
*/
public static String getSqlType(InternalType type) {
if (type.equals(DataTypes.BOOLEAN)) {
return "boolean";
} else if (type.equals(DataTypes.BYTE)) {
return "tinyInt";
} else if (type.equals(DataTypes.SHORT)) {
return "smallInt";
} else if (type.equals(DataTypes.INT)) {
return "int";
} else if (type.equals(DataTypes.LONG)) {
return "bigint";
} else if (type.equals(DataTypes.FLOAT)) {
return "float";
} else if (type.equals(DataTypes.DOUBLE)) {
return "double";
} else if (type instanceof DecimalType) {
if (((DecimalType) type).precision() >= 0) {
return "decimal(" + ((DecimalType) type).precision() + ","
+ ((DecimalType) type).scale() + ")";
}
return "decimal";
} else if (type.equals(DataTypes.DATE)) {
return "date";
} else if (type.equals(DataTypes.TIME)) {
return "time";
} else if (type.equals(DataTypes.TIMESTAMP)) {
return "timestamp";
} else if (type.equals(DataTypes.STRING)) {
return "varchar";
} else if (type.equals(DataTypes.BYTE_ARRAY)) {
return "varbinary";
} else if (type instanceof GenericType) {
return "any";
} else if (type instanceof ArrayType) {
return "array<" + getSqlType(((ArrayType) type).getElementInternalType()) + ">";
} else if (type instanceof MapType) {
MapType mapType = (MapType) type;
return "map<" + getSqlType(mapType.getKeyInternalType()) + ", "
+ getSqlType(mapType.getValueInternalType()) + ">";
} else if (type instanceof RowType) {
RowType rowType = (RowType) type;
List innerColumns = new ArrayList<>();
for (int i = 0; i < rowType.getArity(); i++) {
innerColumns.add(rowType.getFieldNames()[i] + ":" + getSqlType(rowType.getInternalTypeAt(i)));
}
return "row<" + org.apache.commons.lang3.StringUtils.join(innerColumns, " ,") + ">";
} else if (type.equals(TimestampType.ROWTIME_INDICATOR)){
return "timestamp";
} else {
LOG.warn("Unsupported sql column type: {}", type);
throw new IllegalArgumentException("Unsupported inner type " + type + " !");
}
}
private static String getComputedColumnsSql(SqlCreateTable sqlCreateTable) {
if (!sqlCreateTable.containsComputedColumn()) {
return null;
}
return sqlCreateTable.getColumnSqlString();
}
/** Get table schema, this table schema contains fields info of computed columns. **/
public static TableSchema getTableSchema(TableEnvironment tEnv,
SqlCreateTable sqlCreateTable,
RichTableSchema richTableSchema) {
String rowtimeField = null;
if (sqlCreateTable.getWatermark() != null) {
rowtimeField = sqlCreateTable.getWatermark().getColumnName().toString();
}
TableSchema.Builder builder = TableSchema.builder();
if (sqlCreateTable.containsComputedColumn()) {
TableSchema originalSchema = new TableSchema(
richTableSchema.getColumnNames(),
richTableSchema.getColumnTypes(),
richTableSchema.getNullables());
String name = tEnv.createUniqueTableName();
tEnv.registerTableSource(
name, new MockTableSource(name, originalSchema));
String viewSql = "select " + sqlCreateTable.getColumnSqlString() + " from " + name;
Table viewTable = tEnv.sqlQuery(viewSql);
TableSchema schemaWithComputedColumn = viewTable.getSchema();
for (int i = 0; i < schemaWithComputedColumn.getColumns().length; i++) {
Column col = schemaWithComputedColumn.getColumn(i);
if (col.name().equals(rowtimeField)) {
builder.field(
rowtimeField,
DataTypes.ROWTIME_INDICATOR,
col.isNullable());
} else {
builder.field(
col.name(),
col.internalType(),
col.isNullable());
}
}
List> computedColumnPairs = new ArrayList<>();
sqlCreateTable.getComputedColumnsStringsPair(computedColumnPairs);
assert computedColumnPairs.size() > 0 : "Create Table DDL does not contain computed columns !";
for (Pair p : computedColumnPairs) {
builder.computedColumn(p.left, p.right);
}
} else {
for (int i = 0; i < richTableSchema.getColumnNames().length; i++) {
if (richTableSchema.getColumnNames()[i].equals(rowtimeField)) {
builder.field(rowtimeField, DataTypes.ROWTIME_INDICATOR,
richTableSchema.getNullables()[i]);
} else {
builder.field(
richTableSchema.getColumnNames()[i],
richTableSchema.getColumnTypes()[i],
richTableSchema.getNullables()[i]);
}
}
}
builder.primaryKey(richTableSchema.getPrimaryKeys().stream().toArray(String[]::new));
for (List uniqueKey: richTableSchema.getUniqueKeys()) {
builder.uniqueIndex(uniqueKey.stream().toArray(String[]::new));
}
for (RichTableSchema.Index index : richTableSchema.getIndexes()) {
if (!index.unique) {
builder.normalIndex(index.keyList.stream().toArray(String[]::new));
} else {
builder.uniqueIndex(index.keyList.stream().toArray(String[]::new));
}
}
return builder.build();
}
/**
* Returns validated SQL string for a given subquery.
*/
public static String getValidatedSqlQuery(TableEnvironment tEnv, String originalSubQuery) {
// parse the sql query
SqlNode parsed = tEnv.getFlinkPlanner().parse(originalSubQuery);
if (null != parsed && parsed.getKind().belongsTo(SqlKind.QUERY)) {
// validate the sql query
SqlNode validated = tEnv.getFlinkPlanner().validate(parsed);
return validated.toSqlString(null, false).getSql();
} else {
throw new TableException(
"Unsupported SQL query! getValidatedSqlQuery() only accepts SQL queries of type " +
"SELECT, UNION, INTERSECT, EXCEPT, VALUES, and ORDER_BY.");
}
}
private static class MockTableSource
implements BatchTableSource, StreamTableSource {
private String name;
private TableSchema schema;
public MockTableSource(String name, TableSchema tableSchema) {
this.name = name;
this.schema = tableSchema;
}
@Override
public DataStream getBoundedStream(StreamExecutionEnvironment streamEnv) {
return null;
}
@Override
public DataType getReturnType() {
return DataTypes.createRowTypeV2(schema.getFieldTypes(), schema.getFieldNames());
}
@Override
public TableSchema getTableSchema() {
return schema;
}
@Override
public String explainSource() {
return name;
}
@Override
public TableStats getTableStats() {
return null;
}
@Override
public DataStream getDataStream(StreamExecutionEnvironment execEnv) {
return null;
}
}
public static void populateProperties(SqlNodeList propertyList, Map properties) {
if (propertyList != null) {
for (SqlNode sqlNode : propertyList) {
SqlProperty sqlProperty = (SqlProperty) sqlNode;
properties.put(sqlProperty.getKeyString(), sqlProperty.getValueString());
}
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy