Trino动态catalog实现

他说只是儿戏

已于 2023-11-23 13:56:15 修改

阅读量803

点赞数 2

文章标签：数据库数据库开发 java

于 2023-07-26 18:46:16 首次发布

本文链接：https://blog.csdn.net/qq_15695487/article/details/131945493

版权

本文基于trino423版本进行修改，实现从数据库中加载catalog配置，并设置刷新时间间隔动态更新trino中catalog配置

修改 CatalogStoreConfig类

io.trino.connector.CatalogStoreConfig文件，增加数据库枚举类型为 DATABASE

public class CatalogStoreConfig {
    public enum CatalogStoreKind {
        MEMORY, FILE, DATABASE
    }

    private CatalogStoreKind catalogStoreKind = CatalogStoreKind.FILE;

    @NotNull
    public CatalogStoreKind getCatalogStoreKind() {
        return catalogStoreKind;
    }

    @Config("catalog.store")
    public CatalogStoreConfig setCatalogStoreKind(CatalogStoreKind catalogStoreKind) {
        this.catalogStoreKind = catalogStoreKind;
        return this;
    }
}

DynamicCatalogManagerModule增加DATABASE实现

 switch (config.getCatalogStoreKind()) {
                case MEMORY -> binder.bind(CatalogStore.class).to(InMemoryCatalogStore.class).in(Scopes.SINGLETON);
                case FILE -> {
                    configBinder(binder).bindConfig(FileCatalogStoreConfig.class);
                    binder.bind(CatalogStore.class).to(FileCatalogStore.class).in(Scopes.SINGLETON);
                }
                case DATABASE -> {
                    configBinder(binder).bindConfig(DatabaseCatalogStoreConfig.class);
                    binder.bind(CatalogStore.class).to(DatabaseCatalogStore.class).in(Scopes.SINGLETON);
                }
            }

创建 DatabaseCatalogStoreConfig 对象

public class DatabaseCatalogStoreConfig {
    //数据库连接地址
    private String databaseUrl;
    //用户名
    private String userName;
    //密码
    private String password;
    //数据库类型 支持 mysql、dm、sqlserver、postgresql、oracle
    private String databaseType;
    //刷新时间间隔
    private int refreshInterval;
    //读取配置的数据表
    private String tableName;

    @Config("catalog.database-url")
    public DatabaseCatalogStoreConfig setDatabaseUrl(String databaseUrl) {
        this.databaseUrl = databaseUrl;
        return this;
    }

    @Config("catalog.database-userName")
    public DatabaseCatalogStoreConfig setUserName(String userName) {
        this.userName = userName;
        return this;
    }

    @Config("catalog.database-password")
    public DatabaseCatalogStoreConfig setPassword(String password) {
        this.password = password;
        return this;
    }

    @Config("catalog.database-type")
    public DatabaseCatalogStoreConfig setDatabaseType(String databaseType) {
        this.databaseType = databaseType;
        return this;
    }

    @Config("catalog.database-refreshInterval")
    public DatabaseCatalogStoreConfig setRefreshInterval(int refreshInterval) {
        this.refreshInterval = refreshInterval;
        return this;
    }

    @Config("catalog.database-table")
    public DatabaseCatalogStoreConfig setTableName(String tableName) {
        this.tableName = tableName;
        return this;
    }


    public String getDatabaseUrl() {
        return databaseUrl;
    }

    public String getUserName() {
        return userName;
    }

    public String getPassword() {
        return password;
    }

    public String getDatabaseType() {
        return databaseType;
    }

    public int getRefreshInterval() {
        return refreshInterval;
    }

    public String getTableName() {
        return tableName;
    }
}

创建DatabaseCatalogStore 对象

DatabaseCatalogStore对象主要目的如下：

1：从数据库中加载catalog信息

2：组装各类型连接器需要的配置信息

public class DatabaseCatalogStore implements CatalogStore {

    private static final Logger log = Logger.get(DatabaseCatalogStore.class);
    public final ConcurrentMap<String, DatabaseStoredCatalog> catalogs = new ConcurrentHashMap<>();
    private final DatabaseCatalogStoreConfig databaseCatalogStoreConfig;
    private final static String DS_ALIAS = "alias";

    @Inject
    public DatabaseCatalogStore(DatabaseCatalogStoreConfig config) throws Exception {
        this.databaseCatalogStoreConfig = config;
        checkConfig(databaseCatalogStoreConfig);
        loadDsCatalogs(catalogs);
    }

    @Override
    public Collection<StoredCatalog> getCatalogs() {
        return ImmutableList.copyOf(catalogs.values());
    }


    @Override
    public CatalogProperties createCatalogProperties(String catalogName, ConnectorName connectorName, Map<String, String> properties) {
        return new CatalogProperties(
                createRootCatalogHandle(catalogName, computeCatalogVersion(catalogName, connectorName, properties)),
                connectorName,
                ImmutableMap.copyOf(properties));
    }

    @Override
    public void addOrReplaceCatalog(CatalogProperties catalogProperties) {
        String catalogName = catalogProperties.getCatalogHandle().getCatalogName();
        Map<String, String> catalogPropertiesMap = new HashMap<>();
        catalogPropertiesMap.put("connector.name", catalogProperties.getConnectorName().toString());
        catalogPropertiesMap.putAll(catalogProperties.getProperties());
        catalogs.put(catalogName, new DatabaseStoredCatalog(catalogName, catalogPropertiesMap));
    }

    @Override
    public void removeCatalog(String catalogName) {
        catalogs.remove(catalogName);
    }

  
    public void loadDsCatalogs(ConcurrentMap<String, DatabaseStoredCatalog> catalogConcurrentMap) {
        try {
            List<Entity> dsConfigList = loadDsConfig();
            if (CollUtil.isNotEmpty(dsConfigList)) {
                for (Entity entity : dsConfigList) {
                    String alias = entity.getStr(DS_ALIAS);
                    String catalogName = getNameWithoutExtension(alias);
                    checkArgument(!catalogName.equals(GlobalSystemConnector.NAME), "Catalog name SYSTEM is reserved for internal usage");
                    catalogConcurrentMap.put(catalogName, new DatabaseStoredCatalog(catalogName, buildCatalogProperties(entity)));
                }
            }
        } catch (Exception e) {
            log.error("加载数据源配置异常，请检查 ！！！");
        }
    }


    public static class DatabaseStoredCatalog implements StoredCatalog {

        private String name;
        private Map<String, String> properties;

        public DatabaseStoredCatalog(String name, Map<String, String> properties) {
            this.name = requireNonNull(name, "name is null");
            this.properties = requireNonNull(properties, "catalogConfig is null");
        }

        @Override
        public String getName() {
            return name;
        }

        public Map<String, String> getProperties() {
            return properties;
        }

        @Override
        public CatalogProperties loadProperties() {
            String connectorNameValue = properties.remove("connector.name");
            checkState(connectorNameValue != null, "Catalog configuration %s does not contain 'connector.name'");
            if (connectorNameValue.indexOf('-') >= 0) {
                String deprecatedConnectorName = connectorNameValue;
                connectorNameValue = connectorNameValue.replace('-', '_');
                log.warn("Catalog '%s' is using the deprecated connector name '%s'. The correct connector name is '%s'", name, deprecatedConnectorName, connectorNameValue);
            }
            ConnectorName connectorName = new ConnectorName(connectorNameValue);
            CatalogHandle catalogHandle = createRootCatalogHandle(name, computeCatalogVersion(name, connectorName, properties));
            return new CatalogProperties(catalogHandle, connectorName, ImmutableMap.copyOf(properties));
        }
    }

    
    static CatalogHandle.CatalogVersion computeCatalogVersion(String catalogName, ConnectorName connectorName, Map<String, String> properties) {
        Hasher hasher = Hashing.sha256().newHasher();
        hasher.putUnencodedChars("catalog-hash");
        hashLengthPrefixedString(hasher, catalogName);
        hashLengthPrefixedString(hasher, connectorName.toString());
        hasher.putInt(properties.size());
        ImmutableSortedMap.copyOf(properties).forEach((key, value) -> {
            hashLengthPrefixedString(hasher, key);
            hashLengthPrefixedString(hasher, value);
        });
        return new CatalogHandle.CatalogVersion(hasher.hash().toString());
    }

    private static void hashLengthPrefixedString(Hasher hasher, String value) {
        hasher.putInt(value.length());
        hasher.putUnencodedChars(value);
    }

   
    private List<Entity> loadDsConfig() {
        Connection connection = null;
        try {
            connection = loadConnection(databaseCatalogStoreConfig);
            SqlConnRunner sqlConnRunner = SqlConnRunner.create(loadDialect(databaseCatalogStoreConfig));
            Entity where = new Entity();
            where.setTableName(databaseCatalogStoreConfig.getTableName());
            where.put("deleted", 0);
            return sqlConnRunner.findAll(connection, where);
        } catch (Exception e) {
            log.error("加载数据源配置异常，请检查 ！！！", e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                }
            }
        }
        return null;
    }

   
    private static void checkConfig(DatabaseCatalogStoreConfig config) {
        requireNonNull(config, "config is null");
        if (StrUtil.isBlank(config.getDatabaseUrl())) {
            requireNonNull(config, "动态数据源 数据库URL不能为空，请检查 ！！！");
        }
        if (StrUtil.isBlank(config.getDatabaseType())) {
            requireNonNull(config, "动态数据源 数据库类型不能为空，请检查 ！！！");
        }
        if (StrUtil.isBlank(config.getPassword())) {
            requireNonNull(config, "动态数据源 数据库连接密码不能为空，请检查 ！！！");
        }
        if (StrUtil.isBlank(config.getTableName())) {
            requireNonNull(config, "动态数据源 数据库数据源配置表不能为空，请检查 ！！！");
        }
        if (StrUtil.isBlank(config.getUserName())) {
            requireNonNull(config, "动态数据源 数据库用户名不能为空，请检查 ！！！");
        }

    }


    
    private static Connection loadConnection(DatabaseCatalogStoreConfig config) throws Exception {
        switch (config.getDatabaseType()) {
            case "MYSQL":
                return DbUtils.getMysqlConn(config.getDatabaseUrl(), config.getUserName(), config.getPassword());
            case "ORACLE":
                return DbUtils.getOracleConn(config.getDatabaseUrl(), config.getUserName(), config.getPassword());
            case "POSTGRESQL":
                return DbUtils.getPostGresqlConn(config.getDatabaseUrl(), config.getUserName(), config.getPassword());
            case "DM":
                return DbUtils.getDmSqlConn(config.getDatabaseUrl(), config.getUserName(), config.getPassword());
            case "SQLSERVER":
                return DbUtils.getSqlServerConn(config.getDatabaseUrl(), config.getUserName(), config.getPassword());
            default:
                throw new Exception("不支持的数据库类型，请配置 MYSQL、ORACLE、POSTGRESQL、DM、SQLSERVER 其中一种数据库，请检查 ！！！");
        }
    }

    
    private static Dialect loadDialect(DatabaseCatalogStoreConfig config) throws Exception {
        switch (config.getDatabaseType()) {
            case "MYSQL":
                return new MysqlDialect();
            case "ORACLE":
                return new OracleDialect();
            case "POSTGRESQL":
                return new PostgresqlDialect();
            case "DM":
                return new OracleDialect();
            case "SQLSERVER":
                return new SqlServer2012Dialect();
            default:
                throw new Exception("不支持的数据库类型，请配置 MYSQL、ORACLE、POSTGRESQL、DM、SQLSERVER 其中一种数据库，请检查 ！！！");
        }
    }


    
    public static Map<String, String> buildCatalogProperties(Entity entity) throws Exception {
        Map<String, String> catalogProperties = new HashMap<>();
        String dbType = entity.getStr("db_type");
        String userName = entity.getStr("username");
        String password = entity.getStr("password");
        requireNonNull(dbType, "数据库类型异常，请检查 ！！！");
        requireNonNull(userName, "用户名异常，请检查 ！！！");
        requireNonNull(password, "数据库密码异常，请检查 ！！！");
        DataBaseTypeEnum dataBaseTypeEnum = DataBaseTypeEnum.queryByKey(dbType);
        requireNonNull(dataBaseTypeEnum, "不支持的数据库类型，请检查 ！！！");
        password = RSAUtils.decrypt(password);
        switch (dataBaseTypeEnum) {
            case Mysql:
                catalogProperties.put("connector.name", "mysql");
                catalogProperties.put("connection-url", buildMysqlConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            case Postgresql:
                catalogProperties.put("connector.name", "postgresql");
                catalogProperties.put("connection-url", buildPostGreSqlConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            case Sqlserver:
                catalogProperties.put("connector.name", "sqlserver");
                catalogProperties.put("connection-url", buildSqlServerConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            case Oracle:
                catalogProperties.put("connector.name", "oracle");
                catalogProperties.put("connection-url", buildOracleConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            case Dm:
                catalogProperties.put("connector.name", "dm");
                catalogProperties.put("connection-url", buildDmConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            case MongoDB:
                catalogProperties.put("connector.name", "mongodb");
                catalogProperties.put("connection-url", buildMongodbConnUrl(userName, password, entity));
                catalogProperties.put("mongodb.case-insensitive-name-matching", "true");
                return catalogProperties;
            case Elasticsearch:
                catalogProperties.put("connector.name", "elasticsearch");
                catalogProperties.put("elasticsearch.host", entity.getStr("host"));
                catalogProperties.put("elasticsearch.port", entity.getStr("port"));
                catalogProperties.put("elasticsearch.ignore-publish-address", "true");
                catalogProperties.put("elasticsearch.default-schema-name", entity.getStr("db_name"));
                if (StrUtil.isNotBlank(userName) && StrUtil.isNotBlank(password)) {
                    catalogProperties.put("elasticsearch.security", "PASSWORD");
                    catalogProperties.put("elasticsearch.auth.user", userName);
                    catalogProperties.put("elasticsearch.auth.password", password);
                }
                return catalogProperties;
            case ClickHouse:
                catalogProperties.put("connector.name", "clickhouse");
                catalogProperties.put("connection-url", buildClickhouseConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            case Redshift:
                catalogProperties.put("connector.name", "redshift");
                catalogProperties.put("connection-url", buildRedshiftConnUrl(entity));
                catalogProperties.put("connection-user", userName);
                catalogProperties.put("connection-password", password);
                return catalogProperties;
            default:
                throw new Exception("不支持的数据库，请检查 ！！！");
        }
    }

   
    private static String buildMysqlConnUrl(Entity entity) {
        String suffix = entity.getStr("suffix");
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        if (StrUtil.isBlank(suffix)) {
            suffix = String.format("?%s", "useSSL=false&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai");
        } else {
            suffix = String.format("?%s", suffix);
            List<String> suffixList = StrUtil.split(suffix, "&");
            if (CollUtil.isNotEmpty(suffixList)) {
                if (!suffixList.contains("allowMultiQueries")) {
                    suffix = String.format("%s&%s", suffix, "allowMultiQueries=true");
                }
                if (!suffixList.contains("useSSL")) {
                    suffix = String.format("%s&%s", suffix, "useSSL=false");
                }
            }
        }
        return String.format("jdbc:mysql://%s:%s%s", host, port, suffix);
    }


    
    private static String buildPostGreSqlConnUrl(Entity entity) {
        String suffix = entity.getStr("suffix");
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        String dbName = entity.getStr("db_name");
        suffix = StrUtil.isBlankIfStr(suffix) ? "" : "?" + suffix;
        return String.format("jdbc:postgresql://%s:%s/%s%s", host, port, dbName, suffix);
    }

    
    private static String buildSqlServerConnUrl(Entity entity) {
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        String dbName = entity.getStr("db_name");
        return String.format("jdbc:sqlserver://%s:%s;DatabaseName=%s;encrypt=false", host, port, dbName);
    }

    
    private static String buildOracleConnUrl(Entity entity) {
        String suffix = entity.getStr("suffix");
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        String extendParam = entity.getStr("extend_param");
        suffix = StrUtil.isBlankIfStr(suffix) ? "" : "?" + suffix;
        return String.format("jdbc:oracle:thin:@%s:%s:%s%s", host, port, extendParam, suffix);
    }

  
    private static String buildDmConnUrl(Entity entity) {
        String suffix = entity.getStr("suffix");
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        String dbName = entity.getStr("db_name");
        suffix = StrUtil.isBlankIfStr(suffix) ? "" : "&" + suffix;
        return String.format("jdbc:dm://%s:%s?schema=%s%s", host, port, dbName, suffix);
    }

   
    private static String buildMongodbConnUrl(String userName, String password, Entity entity) {
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        return String.format("mongodb://%s:%s@%s:%s", userName, password, host, port);
    }

    
    private static String buildClickhouseConnUrl(Entity entity) {
        String suffix = entity.getStr("suffix");
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        suffix = StrUtil.isBlankIfStr(suffix) ? "" : "?" + suffix;
        return String.format("jdbc:clickhouse://%s:%s%s", host, port, suffix);
    }

   
    private static String buildRedshiftConnUrl(Entity entity) {
        String suffix = entity.getStr("suffix");
        String host = entity.getStr("host");
        String port = entity.getStr("port");
        suffix = StrUtil.isBlankIfStr(suffix) ? "" : "?" + suffix;
        return String.format("jdbc:redshift://%s:%s%s", host, port, suffix);
    }

修改CoordinatorDynamicCatalogManager文件实现定时调度加载更新catalog配置

引入调度执行器

    private final ScheduledExecutorService refreshExecutor = newSingleThreadScheduledExecutor(daemonThreadsNamed("DynamicCatalogRefreshEvent"));

实现刷新方法

private void refreshCatalogConfig() {
        try {
            //新的数据源列表
            DatabaseCatalogStore databaseCatalogStore = (DatabaseCatalogStore) catalogStore;
            ConcurrentMap<String, DatabaseCatalogStore.DatabaseStoredCatalog> newCatalogs = new ConcurrentHashMap<>();
            databaseCatalogStore.loadDsCatalogs(newCatalogs);
            //老的数据源列表
            ConcurrentMap<String, DatabaseCatalogStore.DatabaseStoredCatalog> oldCatalogs = databaseCatalogStore.catalogs;

            //需要新增的数据源
            ConcurrentMap<String, CatalogStore.StoredCatalog> addCatalogs = new ConcurrentHashMap<>();
            //需要删除的数据源
            ConcurrentMap<String, CatalogStore.StoredCatalog> deletedCatalogs = new ConcurrentHashMap<>();


            if (CollUtil.isNotEmpty(newCatalogs) && CollUtil.isNotEmpty(oldCatalogs)) {
                for (Entry<String, DatabaseCatalogStore.DatabaseStoredCatalog> newDsStoredCatalog : newCatalogs.entrySet()) {
                    DatabaseCatalogStore.DatabaseStoredCatalog oldDsStoredCatalog = oldCatalogs.get(newDsStoredCatalog.getKey());
                    if (oldDsStoredCatalog != null) {
                        if (!sameConfig(newDsStoredCatalog.getValue(), oldDsStoredCatalog)) {
                            addCatalogs.put(newDsStoredCatalog.getKey(), newDsStoredCatalog.getValue());
                            deletedCatalogs.put(oldDsStoredCatalog.getName(), oldDsStoredCatalog);
                        }
                    } else {
                        addCatalogs.put(newDsStoredCatalog.getKey(), newDsStoredCatalog.getValue());
                    }
                }
                for (Entry<String, DatabaseCatalogStore.DatabaseStoredCatalog> oldStoredCatalog : oldCatalogs.entrySet()) {
                    if (newCatalogs.get(oldStoredCatalog.getKey()) == null) {
                        deletedCatalogs.put(oldStoredCatalog.getKey(), oldStoredCatalog.getValue());
                    }
                }
            } else if (CollUtil.isNotEmpty(newCatalogs) && CollUtil.isEmpty(oldCatalogs)) {
                for (Entry<String, DatabaseCatalogStore.DatabaseStoredCatalog> newDsStoredCatalog : newCatalogs.entrySet()) {
                    addCatalogs.put(newDsStoredCatalog.getKey(), newDsStoredCatalog.getValue());
                }
            } else if (CollUtil.isEmpty(newCatalogs) && CollUtil.isNotEmpty(oldCatalogs)) {
                for (Entry<String, DatabaseCatalogStore.DatabaseStoredCatalog> oldStoredCatalog : oldCatalogs.entrySet()) {
                    deletedCatalogs.put(oldStoredCatalog.getKey(), oldStoredCatalog.getValue());
                }
            }
            if (CollUtil.isNotEmpty(deletedCatalogs)) {
                deletedCatalogs.forEach((key, value) -> dropCatalog(key, false));
            }

            if (CollUtil.isNotEmpty(addCatalogs)) {
                addCatalogs.forEach((key, value) -> {
                    CatalogProperties catalogProperties = value.loadProperties();
                    createCatalog(key, catalogProperties.getConnectorName(), catalogProperties.getProperties(), false);
                });
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("刷新数据源配置失败，请检查 ！！！", e);
        }


    }

    
    private boolean sameConfig(DatabaseCatalogStore.DatabaseStoredCatalog newDatabaseStoredCatalog, DatabaseCatalogStore.DatabaseStoredCatalog oldDatabaseStoredCatalog) {
        for (Entry<String, String> stringStringEntry : newDatabaseStoredCatalog.getProperties().entrySet()) {
            Map<String, String> properties = oldDatabaseStoredCatalog.getProperties();
            String oldValue = properties.get(stringStringEntry.getKey());
            if (StrUtil.isNotBlank(oldValue) && !oldValue.equals(stringStringEntry.getValue())) {
                return false;
            }
        }
        return true;
    }

调用刷新

ConnectorServicesProvider类增加接口方法

CoordinatorDynamicCatalogManager类实现刷新

@Override
    public void refreshCatalogs() {
        if (started.compareAndSet(false, true)) {
            refreshExecutor.scheduleWithFixedDelay(this::refreshCatalogConfig, 1, 1, TimeUnit.MINUTES);
        }
    }

主程序启动时调用刷新

配置文件修改catalog加载配置

catalog.management=DYNAMIC
catalog.store=DATABASE
catalog.database-type=MYSQL
catalog.database-url= 数据库url
catalog.database-userName=username
catalog.database-password=password
catalog.database-table=table
catalog.database-refreshInterval=1

注意 catalog的配置表可根据自己实际情况进行修改。只需要获取到catalog 关键的配置参数即可。