提取SQL中的表名称与列名称

最新推荐文章于 2023-05-14 11:12:01 发布

小二木成林

最新推荐文章于 2023-05-14 11:12:01 发布

阅读量1.5k

点赞数 2

文章标签： Hive SQL解析 Druid 表统计 Java

本文链接：https://blog.csdn.net/m760741876/article/details/124751979

版权

1、增加pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid.version}</version>
</dependency>

<druid.version>1.2.9</druid.version>

import com.alibaba.druid.DbType;
        import com.alibaba.druid.sql.SQLUtils;
        import com.alibaba.druid.sql.ast.SQLStatement;
        import com.alibaba.druid.sql.ast.statement.SQLAlterTableStatement;
        import com.alibaba.druid.sql.ast.statement.SQLDeleteStatement;
        import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
        import com.alibaba.druid.sql.dialect.hive.ast.HiveInsertStatement;
        import com.alibaba.druid.sql.dialect.hive.visitor.HiveSchemaStatVisitor;
        import com.alibaba.druid.stat.TableStat;
        import lombok.extern.slf4j.Slf4j;

        import java.util.*;

/**
 * @Description SQL工具类
 * @Author 木
 * @Date 2022/5/6 18:38
 */
@Slf4j
public class SqlUtils {

    public static List<String> getTableNameList(String sql) {
        List<String> res = new ArrayList<>();
        try {
            List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sql, DbType.hive);
            for (SQLStatement sqlStatement : sqlStatements) {
                HiveSchemaStatVisitor sqlastVisitor = new HiveSchemaStatVisitor();
                if (sqlStatement instanceof SQLSelectStatement) {
                    SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) sqlStatement;
                    sqlSelectStatement.accept(sqlastVisitor);
                } else if (sqlStatement instanceof HiveInsertStatement) {
                    HiveInsertStatement sqlInsertStatement = (HiveInsertStatement) sqlStatement;
                    sqlInsertStatement.accept(sqlastVisitor);
                } else if (sqlStatement instanceof SQLDeleteStatement) {
                    SQLDeleteStatement sqlDeleteStatement = (SQLDeleteStatement) sqlStatement;
                    sqlDeleteStatement.accept(sqlastVisitor);
                }else if (sqlStatement instanceof SQLAlterTableStatement) {
                    SQLAlterTableStatement sqlAlterTableStatement = (SQLAlterTableStatement) sqlStatement;
                    sqlAlterTableStatement.accept(sqlastVisitor);
                }
                //获取表列表
                Map<TableStat.Name, TableStat> tables = sqlastVisitor.getTables();
                for (Map.Entry<TableStat.Name, TableStat> nameTableStatEntry : tables.entrySet()) {
                    res.add(nameTableStatEntry.getKey().getName());
                }
                //获取列列表
                Collection<TableStat.Column> columnCollection =  sqlastVisitor.getColumns();
                columnCollection.forEach(column->{
                    log.info("tableName:{},columnName:{},iswhere:{}",column.getTable(),column.getName(),column.isWhere());
                });
            }
        } catch (Exception e) {
            log.error("sql解析错误sql:{},错误原因：{}", sql, e.getMessage());
            if (e.getMessage().contains("token IDENTIFIER serdeproperties")) {
                String tempSql = sql.replaceAll(" ","").toLowerCase(Locale.ROOT);
                int start = tempSql.indexOf("altertable") + 10;
                int end = tempSql.indexOf("setserdeproperties(");
                res.add(tempSql.substring(start, end));
            }
        }
        return res;
    }

    public static void main(String[] args) {
        String sql = "select * from dba.test where AVAILABLE = '0'";
        System.out.println(getTableNameList(sql));
    }
}