flink 13.5 自定义connect之clickhouse

最新推荐文章于 2024-08-28 09:12:28 发布
最新推荐文章于 2024-08-28 09:12:28 发布
阅读量1.3k 收藏 3
点赞数 1
分类专栏： Flink 文章标签： flink clickhouse java
wudl
本文链接：https://blog.csdn.net/wudonglianga/article/details/126748539
版权

1.项目结构

在这里插入图片描述

2. pom 文件

<!--
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.
-->
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com-wudl-ck</groupId>
    <artifactId>flink-connector-clickhouse</artifactId>
    <version>13.5</version>
    <packaging>jar</packaging>

    <name>Flink Connector ClickHouse</name>

    <properties>
        <java.version>1.8</java.version>
        <flink.version>1.13.5</flink.version>
        <log4j.version>2.12.1</log4j.version>
        <scala.binary.version>2.12</scala.binary.version>
        <clickhouse-jdbc.version>0.3.2</clickhouse-jdbc.version>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <repositories>
        <repository>
            <id>apache.snapshots</id>
            <name>Apache Development Snapshot Repository</name>
            <url>https://repository.apache.org/content/repositories/snapshots/</url>
            <releases>
                <enabled>false</enabled>
            </releases>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>

    <dependencies>

        <!-- Apache Flink dependencies -->
        <!-- These dependencies are provided, because they should not be packaged into the JAR file. -->
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-java</artifactId>
            <version>${flink.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-table-api-java-bridge_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-jdbc_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>${clickhouse-jdbc.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>${log4j.version}</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>${log4j.version}</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>${log4j.version}</version>
            <scope>runtime</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>

            <!-- Java Compiler -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                </configuration>
            </plugin>

            <!-- We use the maven-shade plugin to create a fat jar that contains all necessary dependencies. -->
            <!-- Change the value of <mainClass>...</mainClass> if your program entry point changes. -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.1.1</version>
                <executions>
                    <!-- Run shade goal on package phase -->
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <artifactSet>
                                <excludes>
                                    <exclude>org.apache.flink:force-shading</exclude>
                                    <exclude>com.google.code.findbugs:jsr305</exclude>
                                    <exclude>org.slf4j:*</exclude>
                                    <exclude>org.apache.logging.log4j:*</exclude>
                                </excludes>
                            </artifactSet>
                            <filters>
                                <filter>
                                    <!-- Do not copy the signatures in the META-INF folder.
                                    Otherwise, this might cause SecurityExceptions when using the JAR. -->
                                    <artifact>*:*</artifact>
                                    <excludes>
                                        <exclude>META-INF/*.SF</exclude>
                                        <exclude>META-INF/*.DSA</exclude>
                                        <exclude>META-INF/*.RSA</exclude>
                                    </excludes>
                                </filter>
                            </filters>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>

        <pluginManagement>
            <plugins>

                <!-- This improves the out-of-the-box experience in Eclipse by resolving some warnings. -->
                <plugin>
                    <groupId>org.eclipse.m2e</groupId>
                    <artifactId>lifecycle-mapping</artifactId>
                    <version>1.0.0</version>
                    <configuration>
                        <lifecycleMappingMetadata>
                            <pluginExecutions>
                                <pluginExecution>
                                    <pluginExecutionFilter>
                                        <groupId>org.apache.maven.plugins</groupId>
                                        <artifactId>maven-shade-plugin</artifactId>
                                        <versionRange>[3.1.1,)</versionRange>
                                        <goals>
                                            <goal>shade</goal>
                                        </goals>
                                    </pluginExecutionFilter>
                                    <action>
                                        <ignore/>
                                    </action>
                                </pluginExecution>
                                <pluginExecution>
                                    <pluginExecutionFilter>
                                        <groupId>org.apache.maven.plugins</groupId>
                                        <artifactId>maven-compiler-plugin</artifactId>
                                        <versionRange>[3.1,)</versionRange>
                                        <goals>
                                            <goal>testCompile</goal>
                                            <goal>compile</goal>
                                        </goals>
                                    </pluginExecutionFilter>
                                    <action>
                                        <ignore/>
                                    </action>
                                </pluginExecution>
                            </pluginExecutions>
                        </lifecycleMappingMetadata>
                    </configuration>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>
</project>

3.ClickHouseRowConverter.文件

package com.wudl.ck.converter;


import org.apache.flink.connector.jdbc.internal.converter.JdbcRowConverter;
import org.apache.flink.connector.jdbc.statement.FieldNamedPreparedStatement;
import org.apache.flink.table.data.*;
import org.apache.flink.table.types.logical.DecimalType;
import org.apache.flink.table.types.logical.LogicalType;
import org.apache.flink.table.types.logical.RowType;
import org.apache.flink.table.types.logical.TimestampType;
import org.apache.flink.util.Preconditions;
import ru.yandex.clickhouse.ClickHousePreparedStatement;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalTime;

public class ClickHouseRowConverter implements JdbcRowConverter {
    private static final long serialVersionUID = 1L;

    private RowType rowType;

    private LogicalType[] fieldTypes;

    private DeserializationConverter[] toInternalConverters;

    private SerializationConverter[] toClickHouseConverters;

    private JDBCSerializationConverter[] toExternalConverters;

    public ClickHouseRowConverter(RowType rowType) {
        this.rowType = (RowType) Preconditions.checkNotNull(rowType);
        this.fieldTypes = (LogicalType[]) rowType.getFields().stream().map(RowType.RowField::getType).toArray(x -> new LogicalType[x]);
        this.toInternalConverters = new DeserializationConverter[rowType.getFieldCount()];
        this.toClickHouseConverters = new SerializationConverter[rowType.getFieldCount()];
        this.toExternalConverters = new JDBCSerializationConverter[rowType.getFieldCount()];
        for (int i = 0; i < rowType.getFieldCount(); i++) {
            this.toInternalConverters[i] = createToFlinkConverter(rowType.getTypeAt(i));
            this.toClickHouseConverters[i] = createToClickHouseConverter(this.fieldTypes[i]);
            this.toExternalConverters[i] = createToExternalConverter(this.fieldTypes[i]);
        }
    }

    public ClickHousePreparedStatement toClickHouse(RowData rowData, ClickHousePreparedStatement statement) throws SQLException {
        //getArity Returns the number of fields in this row.
        for(int idx = 0; idx < rowData.getArity(); idx++) {
            //isNullAt Returns true if the field is null at the given position
            if(rowData == null || rowData.isNullAt(idx)) {
                statement.setObject(idx + 1, null);
            } else {
                //通过函数式接口将rowdata转换为ck statement
                this.toClickHouseConverters[idx].serialize(rowData, idx, statement);
            }
        }
        return statement;
    }

    /**
     * toInternal与toExternal为继承jdbc，查询使用
     * @return
     * @throws SQLException
     */
    @Override
    public RowData toInternal(ResultSet resultSet) throws SQLException {
        GenericRowData genericRowData = new GenericRowData(this.rowType.getFieldCount());
        for(int idx = 0; idx < genericRowData.getArity(); idx++) {
            Object field = resultSet.getObject(idx + 1);
            genericRowData.setField(idx, this.toInternalConverters[idx].deserialize(field));
        }
        return (RowData) genericRowData;
    }

    @Override
    public FieldNamedPreparedStatement toExternal(RowData rowData, FieldNamedPreparedStatement statement) throws SQLException {
        //getArity Returns the number of fields in this row.
        for(int idx = 0; idx < rowData.getArity(); idx++) {
            //isNullAt Returns true if the field is null at the given position
            if(rowData == null || rowData.isNullAt(idx)) {
                statement.setObject(idx + 1, null);
            } else {
                //通过函数式接口将rowdata转换为ck statement
                this.toExternalConverters[idx].serialize(rowData, idx, statement);
            }
        }
        return statement;
    }

    protected SerializationConverter createToClickHouseConverter(LogicalType type) {
        int timestampPrecision;
        int decimalPrecision;
        int decimalScale;
        switch(type.getTypeRoot()) {
            case BOOLEAN:
                return (val, index, statement) -> statement.setBoolean(index + 1, val.getBoolean(index));
            case TINYINT:
                return (val, index, statement) -> statement.setByte(index + 1, val.getByte(index));
            case SMALLINT:
                return (val, index, statement) -> statement.setShort(index + 1, val.getShort(index));
            case INTERVAL_YEAR_MONTH:
            case INTEGER:
                return (val, index, statement) -> statement.setInt(index + 1, val.getInt(index));
            case INTERVAL_DAY_TIME:
            case BIGINT:
                return (val, index, statement) -> statement.setLong(index + 1, val.getLong(index));
            case FLOAT:
                return (val, index, statement) -> statement.setFloat(index + 1, val.getFloat(index));
            case DOUBLE:
                return (val, index, statement) -> statement.setDouble(index + 1, val.getDouble(index));
            case CHAR:
            case VARCHAR:
                return (val, index, statement) -> statement.setString(index + 1, val.getString(index).toString());
            case VARBINARY:
                return (val, index, statement) -> statement.setBytes(index + 1, val.getBinary(index));
            case DATE:
                return (val, index, statement) -> statement.setDate(index + 1, Date.valueOf(LocalDate.ofEpochDay(val.getInt(index))));
            case TIME_WITHOUT_TIME_ZONE:
                return (val, index, statement) -> statement.setTime(index + 1, Time.valueOf(LocalTime.ofNanoOfDay(val.getInt(index) * 1000000L)));
            case TIMESTAMP_WITH_TIME_ZONE:
            case TIMESTAMP_WITHOUT_TIME_ZONE:
                timestampPrecision = ((TimestampType)type).getPrecision();
                return (val, index, statement) -> statement.setTimestamp(index + 1, val.getTimestamp(index, timestampPrecision).toTimestamp());
            case DECIMAL:
                decimalPrecision = ((DecimalType)type).getPrecision();
                decimalScale = ((DecimalType)type).getScale();
                return (val, index, statement) -> statement.setBigDecimal(index + 1, val.getDecimal(index, decimalPrecision, decimalScale).toBigDecimal());
        }
        throw new UnsupportedOperationException("Unsupported type:" + type);
    }

    protected JDBCSerializationConverter createToExternalConverter(LogicalType type) {
        int timestampPrecision;
        int decimalPrecision;
        int decimalScale;
        switch(type.getTypeRoot()) {
            case BOOLEAN:
                return (val, index, statement) -> statement.setBoolean(index + 1, val.getBoolean(index));
            case TINYINT:
                return (val, index, statement) -> statement.setByte(index + 1, val.getByte(index));
            case SMALLINT:
                return (val, index, statement) -> statement.setShort(index + 1, val.getShort(index));
            case INTERVAL_YEAR_MONTH:
            case INTEGER:
                return (val, index, statement) -> statement.setInt(index + 1, val.getInt(index));
            case INTERVAL_DAY_TIME:
            case BIGINT:
                return (val, index, statement) -> statement.setLong(index + 1, val.getLong(index));
            case FLOAT:
                return (val, index, statement) -> statement.setFloat(index + 1, val.getFloat(index));
            case DOUBLE:
                return (val, index, statement) -> statement.setDouble(index + 1, val.getDouble(index));
            case CHAR:
            case VARCHAR:
                return (val, index, statement) -> statement.setString(index + 1, val.getString(index).toString());
            case VARBINARY:
                return (val, index, statement) -> statement.setBytes(index + 1, val.getBinary(index));
            case DATE:
                return (val, index, statement) -> statement.setDate(index + 1, Date.valueOf(LocalDate.ofEpochDay(val.getInt(index))));
            case TIME_WITHOUT_TIME_ZONE:
                return (val, index, statement) -> statement.setTime(index + 1, Time.valueOf(LocalTime.ofNanoOfDay(val.getInt(index) * 1000000L)));
            case TIMESTAMP_WITH_TIME_ZONE:
            case TIMESTAMP_WITHOUT_TIME_ZONE:
                timestampPrecision = ((TimestampType)type).getPrecision();
                return (val, index, statement) -> statement.setTimestamp(index + 1, val.getTimestamp(index, timestampPrecision).toTimestamp());
            case DECIMAL:
                decimalPrecision = ((DecimalType)type).getPrecision();
                decimalScale = ((DecimalType)type).getScale();
                return (val, index, statement) -> statement.setBigDecimal(index + 1, val.getDecimal(index, decimalPrecision, decimalScale).toBigDecimal());
        }
        throw new UnsupportedOperationException("Unsupported type:" + type);
    }

    protected DeserializationConverter createToFlinkConverter(LogicalType type) {
        int precision;
        int scale;
        switch (type.getTypeRoot()) {
            case NULL:
                return val -> null;
            case BOOLEAN:
            case FLOAT:
            case DOUBLE:
            case INTERVAL_YEAR_MONTH:
            case INTERVAL_DAY_TIME:
                return val -> val;
            case TINYINT:
                return val -> Byte.valueOf(((Integer)val).byteValue());
            case SMALLINT:
                return val -> (val instanceof Integer) ? Short.valueOf(((Integer)val).shortValue()):val;
            case INTEGER:
                return val -> val;
            case BIGINT:
                return val -> (val instanceof BigInteger) ? ((BigInteger) val).longValue() :val;
            case DECIMAL:
                precision = ((DecimalType)type).getPrecision();
                scale = ((DecimalType)type).getScale();
                return val -> (val instanceof BigInteger) ? DecimalData.fromBigDecimal(new BigDecimal((BigInteger)val, 0), precision, scale) : DecimalData.fromBigDecimal((BigDecimal)val, precision, scale);
            case DATE:
                return val -> Integer.valueOf((int)((Date)val).toLocalDate().toEpochDay());
            case TIME_WITHOUT_TIME_ZONE:
                return val -> Integer.valueOf((int)(((Time)val).toLocalTime().toNanoOfDay() / 1000000L));
            case TIMESTAMP_WITH_TIME_ZONE:
            case TIMESTAMP_WITHOUT_TIME_ZONE:
                return val -> TimestampData.fromTimestamp((Timestamp) val);
            case CHAR:
            case VARCHAR:
                return val -> StringData.fromString(String.valueOf(val));
            case BINARY:
            case VARBINARY:
                return val -> (byte[])val;
        }
        throw new UnsupportedOperationException("Unsupported type:" + type);
    }

    @FunctionalInterface
    static interface SerializationConverter extends Serializable{
        void serialize(RowData param1RowData, int param1Int, PreparedStatement param1PreparedStatement) throws SQLException;
    }

    @FunctionalInterface
    static interface JDBCSerializationConverter extends Serializable{
        void serialize(RowData param1RowData, int param1Int, FieldNamedPreparedStatement param1PreparedStatement) throws SQLException;
    }

    @FunctionalInterface
    static interface DeserializationConverter extends Serializable {
        Object deserialize(Object param1Object) throws SQLException;
    }
}

4. ClickHouseDialect.java 类

package com.wudl.ck.dialect;

import org.apache.flink.connector.jdbc.dialect.JdbcDialect;
import org.apache.flink.connector.jdbc.internal.converter.JdbcRowConverter;
import org.apache.flink.table.types.logical.RowType;
import com.wudl.ck.converter.ClickHouseRowConverter;

import java.util.Optional;


public class ClickHouseDialect implements JdbcDialect {

    private static final long serialVersionUID = 1L;

    @Override
    public String dialectName() {
        return "ClickHouse";
    }

    @Override
    public boolean canHandle(String url) {
        return url.startsWith("jdbc:clickhouse:");
    }

    @Override
    public JdbcRowConverter getRowConverter(RowType rowType) {
        return new ClickHouseRowConverter(rowType);
    }

    @Override
    public String getLimitClause(long limit) {
        return "LIMIT " + limit;
    }

    @Override
    public Optional<String> defaultDriverName() {
        return Optional.of("ru.yandex.clickhouse.ClickHouseDriver");
    }

    @Override
    public String quoteIdentifier(String identifier) {
        return "`" + identifier + "`";
    }

    @Override
    public Optional<String> getUpsertStatement(String tableName, String[] fieldNames, String[] uniqueKeyFields) {
        return Optional.empty();
    }

}

5.ClickHouseDynamicTableFactory.java 工厂类

package com.wudl.ck.table;

import org.apache.flink.api.common.serialization.SerializationSchema;
import org.apache.flink.configuration.ConfigOption;
import org.apache.flink.configuration.ConfigOptions;
import org.apache.flink.configuration.ReadableConfig;
//import org.apache.flink.connector.jdbc.internal.options.JdbcConnectorOptions;
import org.apache.flink.connector.jdbc.internal.options.JdbcOptions;
import org.apache.flink.table.api.TableSchema;
import org.apache.flink.table.connector.format.EncodingFormat;
import org.apache.flink.table.connector.sink.DynamicTableSink;
import org.apache.flink.table.connector.source.DynamicTableSource;
import org.apache.flink.table.data.RowData;
import org.apache.flink.table.factories.DynamicTableSinkFactory;
import org.apache.flink.table.factories.DynamicTableSourceFactory;
import org.apache.flink.table.factories.FactoryUtil;
import org.apache.flink.table.factories.SerializationFormatFactory;
import org.apache.flink.table.types.DataType;
import org.apache.flink.table.utils.TableSchemaUtils;
import com.wudl.ck.dialect.ClickHouseDialect;

import java.util.HashSet;
import java.util.Set;


public class ClickHouseDynamicTableFactory implements DynamicTableSourceFactory, DynamicTableSinkFactory {

    public static final String IDENTIFIER = "clickhouse";

    private static final String DRIVER_NAME = "ru.yandex.clickhouse.ClickHouseDriver";

    public static final ConfigOption<String> URL = ConfigOptions
            .key("url")
            .stringType()
            .noDefaultValue()
            .withDescription("the jdbc database url.");

    public static final ConfigOption<String> TABLE_NAME = ConfigOptions
            .key("table-name")
            .stringType()
            .noDefaultValue()
            .withDescription("the jdbc table name.");

    public static final ConfigOption<String> USERNAME = ConfigOptions
            .key("username")
            .stringType()
            .noDefaultValue()
            .withDescription("the jdbc user name.");

    public static final ConfigOption<String> PASSWORD = ConfigOptions
            .key("password")
            .stringType()
            .noDefaultValue()
            .withDescription("the jdbc password.");

    public static final ConfigOption<String> FORMAT = ConfigOptions
            .key("format")
            .stringType()
            .noDefaultValue()
            .withDescription("the format.");

    @Override
    public String factoryIdentifier() {
        return IDENTIFIER;
    }

    @Override
    public Set<ConfigOption<?>> requiredOptions() {
        Set<ConfigOption<?>> requiredOptions = new HashSet<>();
        requiredOptions.add(URL);
        requiredOptions.add(TABLE_NAME);
        requiredOptions.add(USERNAME);
        requiredOptions.add(PASSWORD);
        requiredOptions.add(FORMAT);
        return requiredOptions;
    }

    @Override
    public Set<ConfigOption<?>> optionalOptions() {
        return new HashSet<>();
    }

    @Override
    public DynamicTableSource createDynamicTableSource(Context context) {

        // either implement your custom validation logic here ...
        final FactoryUtil.TableFactoryHelper helper = FactoryUtil.createTableFactoryHelper(this, context);

        final ReadableConfig config = helper.getOptions();

        // validate all options
        helper.validate();

//        JdbcConnectorOptions jdbcOptions = getJdbcOptions(config);
        JdbcOptions  jdbcOptions = getJdbcOptions(config);

        // get table schema
        TableSchema physicalSchema = TableSchemaUtils.getPhysicalSchema(context.getCatalogTable().getSchema());

        // table source
        return new ClickHouseDynamicTableSource(jdbcOptions, physicalSchema);

    }

    @Override
    public DynamicTableSink createDynamicTableSink(Context context) {

        // either implement your custom validation logic here ...
        final FactoryUtil.TableFactoryHelper helper = FactoryUtil.createTableFactoryHelper(this, context);

        // discover a suitable decoding format
        final EncodingFormat<SerializationSchema<RowData>> encodingFormat = helper.discoverEncodingFormat(
                SerializationFormatFactory.class,
                FactoryUtil.FORMAT);

        final ReadableConfig config = helper.getOptions();

        // validate all options
        helper.validate();

        // get the validated options
        JdbcOptions  jdbcOptions = getJdbcOptions(config);

        // derive the produced data type (excluding computed columns) from the catalog table
        final DataType dataType = context.getCatalogTable().getSchema().toPhysicalRowDataType();

        // table sink
        return new ClickHouseDynamicTableSink(jdbcOptions, encodingFormat, dataType);
    }

    private JdbcOptions getJdbcOptions(ReadableConfig readableConfig) {
        final String url = readableConfig.get(URL);
        final JdbcOptions .Builder builder = JdbcOptions .builder()
                .setDriverName(DRIVER_NAME)
                .setDBUrl(url)
                .setTableName(readableConfig.get(TABLE_NAME))
                .setDialect(new ClickHouseDialect());

        readableConfig.getOptional(USERNAME).ifPresent(builder::setUsername);
        readableConfig.getOptional(PASSWORD).ifPresent(builder::setPassword);
        return builder.build();
    }

}

5. ClickHouseDynamicTableSink.java 动态表输出

package com.wudl.ck.table;

import org.apache.flink.api.common.serialization.SerializationSchema;
//import org.apache.flink.connector.jdbc.internal.options.JdbcConnectorOptions;
import org.apache.flink.connector.jdbc.internal.options.JdbcOptions;
import org.apache.flink.table.connector.ChangelogMode;
import org.apache.flink.table.connector.format.EncodingFormat;
import org.apache.flink.table.connector.sink.DynamicTableSink;
import org.apache.flink.table.connector.sink.SinkFunctionProvider;
import org.apache.flink.table.data.RowData;
import org.apache.flink.table.types.DataType;


public class ClickHouseDynamicTableSink implements DynamicTableSink {

//    private final JdbcConnectorOptions jdbcOptions;
    private final JdbcOptions jdbcOptions;
    private final EncodingFormat<SerializationSchema<RowData>> encodingFormat;
    private final DataType dataType;

    public ClickHouseDynamicTableSink(JdbcOptions  jdbcOptions, EncodingFormat<SerializationSchema<RowData>> encodingFormat, DataType dataType) {
        this.jdbcOptions = jdbcOptions;
        this.encodingFormat = encodingFormat;
        this.dataType = dataType;
    }

    @Override
    public ChangelogMode getChangelogMode(ChangelogMode requestedMode) {
        return requestedMode;
    }

    @Override
    public SinkRuntimeProvider getSinkRuntimeProvider(Context context) {
        SerializationSchema<RowData> serializationSchema = encodingFormat.createRuntimeEncoder(context, dataType);
        ClickHouseSinkFunction clickHouseSinkFunction = new ClickHouseSinkFunction(jdbcOptions, serializationSchema);
        return SinkFunctionProvider.of(clickHouseSinkFunction);
    }

    @Override
    public DynamicTableSink copy() {
        return new ClickHouseDynamicTableSink(jdbcOptions, encodingFormat, dataType);
    }

    @Override表

6. ClickHouseDynamicTableSource.java 动态源

package com.wudl.ck.table;

import org.apache.flink.connector.jdbc.dialect.JdbcDialect;
//import org.apache.flink.connector.jdbc.internal.options.JdbcConnectorOptions;
import org.apache.flink.connector.jdbc.internal.options.JdbcOptions;
import org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat;
import org.apache.flink.table.api.TableSchema;
import org.apache.flink.table.connector.ChangelogMode;
import org.apache.flink.table.connector.source.DynamicTableSource;
import org.apache.flink.table.connector.source.InputFormatProvider;
import org.apache.flink.table.connector.source.ScanTableSource;
import org.apache.flink.table.types.logical.RowType;


public class ClickHouseDynamicTableSource implements ScanTableSource {

//    private final JdbcConnectorOptions options;
    private final JdbcOptions options;
    private final TableSchema tableSchema;

    public ClickHouseDynamicTableSource(JdbcOptions  options, TableSchema tableSchema) {
        this.options = options;
        this.tableSchema = tableSchema;
    }

    @Override
    public ChangelogMode getChangelogMode() {
        return ChangelogMode.insertOnly();
    }

    @Override
    @SuppressWarnings("unchecked")
    public ScanRuntimeProvider getScanRuntimeProvider(ScanContext runtimeProviderContext) {

        final JdbcDialect dialect = options.getDialect();

        String query = dialect.getSelectFromStatement(
                options.getTableName(), tableSchema.getFieldNames(), new String[0]);

        final RowType rowType = (RowType) tableSchema.toRowDataType().getLogicalType();

        final JdbcRowDataInputFormat.Builder builder = JdbcRowDataInputFormat.builder()
                .setDrivername(options.getDriverName())
                .setDBUrl(options.getDbURL())
                .setUsername(options.getUsername().orElse(null))
                .setPassword(options.getPassword().orElse(null))
                .setQuery(query)
                .setRowConverter(dialect.getRowConverter(rowType))
                .setRowDataTypeInfo(runtimeProviderContext.createTypeInformation(tableSchema.toRowDataType()));;

        return InputFormatProvider.of(builder.build());

    }

    @Override
    public DynamicTableSource copy() {
        return new ClickHouseDynamicTableSource(options, tableSchema);
    }

    @Override
    public String asSummaryString() {
        return "ClickHouse Table Source";
    }

}

7.ClickHouseSinkFunction.java 的函数

package com.wudl.ck.table;

import org.apache.flink.api.common.serialization.SerializationSchema;
import org.apache.flink.configuration.Configuration;
//import org.apache.flink.connector.jdbc.internal.options.JdbcConnectorOptions;
import org.apache.flink.connector.jdbc.internal.options.JdbcOptions;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.apache.flink.table.data.RowData;
import ru.yandex.clickhouse.BalancedClickhouseDataSource;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseStatement;
import ru.yandex.clickhouse.domain.ClickHouseFormat;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
import ru.yandex.clickhouse.settings.ClickHouseQueryParam;
import java.io.ByteArrayInputStream;
import java.sql.SQLException;


public class ClickHouseSinkFunction extends RichSinkFunction<RowData> {

    private static final long serialVersionUID = 1L;

    private static final String MAX_PARALLEL_REPLICAS_VALUE = "2";

    private final JdbcOptions jdbcOptions;
    private final SerializationSchema<RowData> serializationSchema;

    private ClickHouseConnection conn;
    private ClickHouseStatement stmt;


    public ClickHouseSinkFunction(JdbcOptions  jdbcOptions, SerializationSchema<RowData> serializationSchema) {
        this.jdbcOptions = jdbcOptions;
        this.serializationSchema = serializationSchema;
    }

    @Override
    public void open(Configuration parameters) {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(jdbcOptions.getUsername().orElse(null));
        properties.setPassword(jdbcOptions.getPassword().orElse(null));
        BalancedClickhouseDataSource dataSource;
        try {
            if (null == conn) {
                dataSource = new BalancedClickhouseDataSource(jdbcOptions.getDbURL(), properties);
                conn = dataSource.getConnection();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void invoke(RowData value, Context context) throws Exception {
        byte[] serialize = serializationSchema.serialize(value);
        stmt = conn.createStatement();
        stmt.write().table(jdbcOptions.getTableName()).data(new ByteArrayInputStream(serialize), ClickHouseFormat.JSONEachRow)
                .addDbParam(ClickHouseQueryParam.MAX_PARALLEL_REPLICAS, MAX_PARALLEL_REPLICAS_VALUE).send();
    }

    @Override
    public void close() throws Exception {
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

}

8.配置类

在resources下面新建 META-INF/services 然后新建一个 org.apache.flink.table.factories.Factory 文件里面指定到com.wudl.ck.table.ClickHouseDynamicTableFactory 动态表工厂。

9. 测试

从clickhouse 数据同步到 mysql



Flink SQL> CREATE TABLE source_mysql4 (
>    id BIGINT PRIMARY KEY NOT ENFORCED,
>    name STRING,
>    age BIGINT,
>    birthday TIMESTAMP(3),
>    ts TIMESTAMP(3)
>  ) WITH (
>  'connector' = 'jdbc',
>  'url' = 'jdbc:mysql://192.168.1.162:3306/wudldb?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8',
> 'table-name' = 'Flink_cdc_2022',
>  'username' = 'root',
>  'password' = '123456'
>  );
[INFO] Execute statement succeed.

Flink SQL> drop table source_mysql4;
[INFO] Execute statement succeed.

Flink SQL> CREATE TABLE source_mysql4 (
>    id BIGINT PRIMARY KEY NOT ENFORCED,
>    name STRING,
>    age BIGINT,
>    birthday TIMESTAMP,
>    ts TIMESTAMP
>  ) WITH (
>  'connector' = 'jdbc',
>  'url' = 'jdbc:mysql://192.168.1.162:3306/wudldb?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8',
> 'table-name' = 'Flink_cdc_2022',
>  'username' = 'root',
>  'password' = '123456'
>  );
[INFO] Execute statement succeed.

Flink SQL> select * from  source_mysql4;
[INFO] Result retrieval cancelled.

Flink SQL> insert into source_mysql4 select id,name,age,birthday,LOCALTIMESTAMP  ts  from wutable_ck;
[INFO] Submitting SQL update statement to the cluster...
[INFO] SQL update statement has been successfully submitted to the cluster:
Job ID: 4d9cc4cf21d528779fa9c97c5cd933dc

类型转化
cast(属性 as 类型) ，进行类型转换


Flink SQL> CREATE TABLE if not exists wutable_ck (
>   id BIGINT,
>    name STRING,
>    age BIGINT,
>    birthday  TIMESTAMP
> ) WITH (
>     'connector' = 'clickhouse',
>     'url' = 'jdbc:clickhouse://192.168.1.161:8123/wudldb',
>     'username' = 'default',  
>     'password' = '',   
>     'table-name' = 'clickhosuetable', 
>     'format' = 'json'
> );
> 
[INFO] Execute statement succeed.

Flink SQL> CREATE TABLE source_mysql4 (
>    id BIGINT PRIMARY KEY NOT ENFORCED,
>    name STRING,
>    age int,
>    birthday TIMESTAMP,
>    ts TIMESTAMP
>  ) WITH (
>  'connector' = 'jdbc',
>  'url' = 'jdbc:mysql://192.168.1.162:3306/wudldb?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8',
> 'table-name' = 'Flink_cdc_2022',
>  'username' = 'root',
>  'password' = '123456'
>  );
[INFO] Execute statement succeed.

Flink SQL> select * from  source_mysql4;
[INFO] Result retrieval cancelled.

Flink SQL> select  id ,name , CAST(age AS BIGINT) age, birthday   from source_mysql4;
[INFO] Result retrieval cancelled.

Flink SQL> insert into wutable_ck  select  id ,name , CAST(age AS BIGINT) age, birthday   from source_mysql4;
[INFO] Submitting SQL update statement to the cluster...
[INFO] SQL update statement has been successfully submitted to the cluster:
Job ID: a29a4655930b10c0464e906f21bcef97
wudl5566
关注
专栏目录
flink-connector-clickhouse-1.12.0.jar
02-15
flink 以connector形式连接clickhouse，资源稀缺，阿里云已经找不到了 id Int, age Int, sex Int, create_date Date ) WITH ( 'connector' = 'clickhouse', 'url' = 'jdbc:clickhouse://10.1.3.176:8123/test', 'table-name' = 'tb_user', 'username' = '', 'password' = '', 'format' = 'json' )
flink-connector-clickhouse
AinUser的博客
09-26 6915
# 情景介绍 目前要对flink进行多数据源适配工作，需要支持的有pclickhouse,elasticsearch # 版本介绍 flink：1.13.1 elasticsearch:7.6.2 clickhouse:21.9.3.30 # 参考文献 # github flink全量案例demo：https://github.com/zhisheng17/flink-learning flink-connector连接器：https://github.com/DTStack/flinkx fli.
flink-connector-clickhouse.jar
10-19
flinkSQL clickhouse连接器
flink-connector-clickhouse_2.12-1.13.6
09-21
flink-connector-clickhouse_2.12-1.13.6 flink版本1.13.6 ClickHouse：20.9.2.20 scala:2.12
Flink 使用JDBC connector 写入 Clickhouse
Light_Dream的博客
09-07 1681
写在前面 Flink 1.11.0 对JDBC connector 进行了一次较大的重构 重构之前（1.10.3 以及之前版本）， 包名为 flink-jdbc. 重构之后（1.11.0 以及之后版本）， 包名为 flink-connector-jdbc. Flink 1.11.0 以及之后版本需要采用flink-connector-jdbc + DataStream的方式写入数据到ClickHouse。 Flink 1.13.2+ flink-connector-jdbc 依赖： ... .
Flink sql 实现 -connection-clickhouse的 source和 sink
wudonglianga的专栏
08-28 3228
flink13.1 操作clickhouse 所需要的jar 包 以及自定义flink 连接clickhouse 的驱动包
Flink自定义source读取clickhouse
qq_43662627的博客
09-17 3338
昨晚想从clickhouse读取数据，愣是没找到clickhouse的flink的读取方法，一百度都是slink的，最后是找到一个clickhouse官方提供的jdbc方法，但还是用游标，好吧凑合着用 官方链接 package redo.source import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment import org.apache.flink.api.scala._ import org.apache.fl
flink13.1 scala 11 操作clickhouse 的代码实现 包括source sink
08-29
flink13.1 中操作clickhouse 所需要的jar 包 以及自定义flink 连接clickhouse 的驱动包 直接写运行就可以了， 请查看https://b flink13.1 中操作clickhouse 所需要的jar 包 以及自定义flink 连接clickhouse 的驱动包...
flink-clickhouse-sink:Flink水槽的Clickhouse
05-17
Flink-ClickHouse-Sink 描述 用于数据库的器。 由。 用于将数据加载到ClickHouse的高性能库。 它有两个触发器来加载数据：超时和缓冲区大小。 版本图 闪烁 flink-clickhouse-sink 1.3。* 1.0.0 1.9。* 1.3.1 ...
flink jdbc connector支持clickhouse
u012573226的专栏
12-09 4144
flink jdbc connector支持实时数据写入clickhouse
flink13.1 操作clickhouse 的代码实现 包括自定义 source 自定义 sink
08-28
flink13 操作clickhouse 的代码实现 包括source sink 的代码实现直接mavne clean install 放入到 flink lib 目录下 然后直接运行就可以 用flink sql 往clickhouse 中 的 clickhouse inck source 和 sink sql 直接执行就可以 详细请参考专栏博客https://blog.csdn.net/wudonglianga/category_10572030.html?spm=1001.2014.3001.5482
【Flink】使用JDBC connector写入ClickHouse
九师兄
01-29 1814
1.概述 转载并且添加 使用JDBC connector写入ClickHouse 2.背景信息 Flink 1.10.1 + flink-jdbc Flink 1.11.0 + flink-connector-jdbc 本节主要介绍如何利用 ClickHouse JDBC connector，使用不同版本的 Flink 写入数据到 ClickHouse 中。 2.1 背景信息 Flink 在 1.11.0 版本对其 JDBC connector 进行了一次较大的重构： 重构之前（1.10.1 及之前版本.
Flink实战（123）：connector（二十）clickhouse写入(三)
微信搜：import_bigdata，大数据领域硬核原创作者
01-31 835
package com.caimou.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.HashMap; import java.util.HashSet; import java.util.Map; import java.util.Set; /** * Created by .
flink实战--flinksql连接器(jdbc-connector)添加对clickhouse的支持
阿华田的博客
02-04 3500
简介 在目前的所有的flink版本中，flinksql原生还不支持直接ddl的当时创建clickhouse表，进行业务开发。需要我们自己实现clickhouse的connector。本文将详细介绍通过改造原生flink-connector-jdbc添加对clickhouse的支持。 步骤 第一步：模仿MySQLDialect编写自己的ClickHouseDialect，代码如下： public class ClickHouseDialect extends.........
Flink ClickHouse Connector：高效数据处理的利器
最新发布
gitblog_00560的博客
08-28 305
Flink ClickHouse Connector：高效数据处理的利器 flink-connector-clickhouseFlink SQL connector for ClickHouse. Support ClickHouseCatalog and read/write primary data, maps, arrays to clickhouse.项目地址:https://gitco...
Flink 连接器 ClickHouse 教程
gitblog_00276的博客
08-24 1099
Flink 连接器 ClickHouse 教程 flink-connector-clickhouseFlink SQL connector for ClickHouse. Support ClickHouseCatalog and read/write primary data, maps, arrays to clickhouse.项目地址:https://gitcode.com/gh_mir...
flink-connector-jdbc.jar加入clickhouse驱动支持，并重新编译
qq_27474277的博客
07-09 2098
1.在dialect package里面新加一个ClickhouseDialect，这个类可以根据MySQLDialect改 /* * 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 * rega
Flink实例（六十一）: connectors（十二）clickhouse 写 入 (一)
微信搜：import_bigdata，大数据领域硬核原创作者
01-23 559
声明：本系列博客是根据SGG的视频整理而成，非常适合大家入门学习。 《2021年最新版大数据面试题全面开启更新》 pom.xml <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaL.
使用flink-connector-jdbc实现flink写入clickhouse、mysql等
weixin_43695091的博客
05-01 3167
使用flink-connector-jdbc实现flink写入clickhouse、mysql等 适用范围：flink1.11.0及之后版本，包名为flink-connector-jdbc 编辑pom.xml中的<dependencies />小节添加依赖。 <dependency> <groupId>org.apache.flink</groupId> <artifactId>fli
写个flink 流关联sink到clickhouse的代码
04-20
好的，请稍等，我正在为您编写代码。 ``` import org.apache.flink.api.common.serialization.SimpleStringSchema; import org.apache.flink.streaming.api.datastream.DataStream; import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment; import org.apache.flink.streaming.connectors.kafka.FlinkKafkaConsumer; import org.apache.flink.streaming.connectors.kafka.KafkaDeserializationSchema; import org.apache.flink.streaming.connectors.kafka.KafkaSerializationSchema; import org.apache.flink.streaming.connectors.kafka.FlinkKafkaProducer; import org.apache.flink.streaming.util.serialization.KeyedSerializationSchema; import org.apache.flink.streaming.util.serialization.KeyedDeserializationSchema; import org.apache.flink.api.common.functions.MapFunction; import org.apache.flink.api.common.typeinfo.TypeInformation; import org.apache.flink.api.java.tuple.Tuple2; import org.apache.flink.api.java.typeutils.TypeExtractor; import org.apache.flink.streaming.api.functions.sink.SinkFunction; import org.apache.flink.streaming.api.functions.source.SourceFunction; import org.apache.flink.types.Row; import java.util.Properties; import ru.yandex.clickhouse.ClickHouseDataSource; public class FlinkClickhouseSink { public static void main(String[] args) throws Exception { final StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); // Kafka consumer properties Properties consumerProperties = new Properties(); consumerProperties.setProperty("bootstrap.servers", "localhost:9092"); consumerProperties.setProperty("group.id", "test-group"); // Kafka producer properties Properties producerProperties = new Properties(); producerProperties.setProperty("bootstrap.servers", "localhost:9092"); // ClickHouse properties Properties clickHouseProperties = new Properties(); clickHouseProperties.setProperty("user", "default"); clickHouseProperties.setProperty("password", ""); clickHouseProperties.setProperty("database", "test"); ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/test", clickHouseProperties); // Kafka consumer DataStream<String> stream = env .addSource(new FlinkKafkaConsumer<>("test-topic", new SimpleStringSchema(), consumerProperties)); // Flink transformation, e.g. filter or map DataStream<Tuple2<Integer, String>> mappedStream = stream .map(new MapFunction<String, Tuple2<Integer, String>>() { @Override public Tuple2<Integer, String> map(String value) throws Exception { Integer key = Integer.parseInt(value.split(",")[0]); String message = value.split(",")[1]; return new Tuple2<>(key, message); } }); // Kafka producer serialization schema KafkaSerializationSchema<Tuple2<Integer, String>> kafkaSerializationSchema = new KafkaSerializationSchema<Tuple2<Integer, String>>() { @Override public void open(SerializationSchema.InitializationContext context) throws Exception { } @Override public ProducerRecord<byte[], byte[]> serialize(Tuple2<Integer, String> element, Long timestamp) { return new ProducerRecord<>("test-topic", (element.f0 + "," + element.f1).getBytes()); } }; // Kafka producer FlinkKafkaProducer<Tuple2<Integer, String>> kafkaProducer = new FlinkKafkaProducer<>("test-topic", kafkaSerializationSchema, producerProperties); // ClickHouse sink function SinkFunction<Tuple2<Integer, String>> clickHouseSinkFunction = new SinkFunction<Tuple2<Integer, String>>() { @Override public void invoke(Tuple2<Integer, String> value, Context context) throws Exception { String sql = "INSERT INTO test (id, message) values (?, ?)"; try (ClickHouseConnection connection = clickHouseDataSource.getConnection(); ClickHousePreparedStatement stmt = (ClickHousePreparedStatement) connection.prepareStatement(sql)) { stmt.setInt(1, value.f0); stmt.setString(2, value.f1); stmt.execute(); } } }; // ClickHouse sink mappedStream.addSink(clickHouseSinkFunction); // Kafka producer sink mappedStream.addSink(kafkaProducer); env.execute(); } } ``` 这是一个简单的 Flink 流程序，将从 Kafka 中读取数据，经过 Flink 的转换后将数据写入 Kafka 和 ClickHouse 中。请注意，这个程序只是示例代码，如果您在实际环境中使用，请根据实际情况进行修改。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值