taherk77 commented on a change in pull request #917: [HUDI-251] JDBC
incremental load to HUDI with DeltaStreamer
URL: https://github.com/apache/incubator-hudi/pull/917#discussion_r329908038
##########
File path:
hudi-utilities/src/main/java/org/apache/hudi/utilities/sources/JDBCSource.java
##########
@@ -0,0 +1,233 @@
+package org.apache.hudi.utilities.sources;
+
+import java.util.Arrays;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.fs.FSDataInputStream;
+import org.apache.hadoop.fs.FileSystem;
+import org.apache.hadoop.fs.Path;
+import org.apache.hadoop.io.IOUtils;
+import org.apache.hudi.DataSourceUtils;
+import org.apache.hudi.common.util.Option;
+import org.apache.hudi.common.util.StringUtils;
+import org.apache.hudi.common.util.TypedProperties;
+import org.apache.hudi.common.util.collection.Pair;
+import org.apache.hudi.exception.HoodieException;
+import org.apache.hudi.utilities.schema.SchemaProvider;
+import org.apache.spark.api.java.JavaSparkContext;
+import org.apache.spark.sql.Column;
+import org.apache.spark.sql.DataFrameReader;
+import org.apache.spark.sql.Dataset;
+import org.apache.spark.sql.Row;
+import org.apache.spark.sql.SparkSession;
+import org.apache.spark.sql.functions;
+import org.apache.spark.sql.types.DataTypes;
+import org.jetbrains.annotations.NotNull;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+public class JDBCSource extends RowSource {
+
+ private static Logger LOG = LoggerFactory.getLogger(JDBCSource.class);
+
+ private final String ppdQuery = "(select * from %s where %s >= \" %s \")
rdbms_table";
+
+
+ public JDBCSource(TypedProperties props, JavaSparkContext sparkContext,
SparkSession sparkSession,
+ SchemaProvider schemaProvider) {
+ super(props, sparkContext, sparkSession, schemaProvider);
+ }
+
+ private static DataFrameReader validatePropsAndGetDataFrameReader(final
SparkSession session,
+ final TypedProperties properties)
+ throws HoodieException {
+ FSDataInputStream passwordFileStream = null;
+ try {
+ DataFrameReader dataFrameReader = session.read().format("jdbc");
+ dataFrameReader = dataFrameReader.option(Config.URL_PROP,
properties.getString(Config.URL));
+ dataFrameReader = dataFrameReader.option(Config.USER_PROP,
properties.getString(Config.USER));
+ dataFrameReader = dataFrameReader.option(Config.DRIVER_PROP,
properties.getString(Config.DRIVER_CLASS));
+ dataFrameReader = dataFrameReader
+ .option(Config.RDBMS_TABLE_PROP,
properties.getString(Config.RDBMS_TABLE_NAME));
+
+ if (!properties.containsKey(Config.PASSWORD)) {
+ if (properties.containsKey(Config.PASSWORD_FILE)) {
+ if
(!StringUtils.isNullOrEmpty(properties.getString(Config.PASSWORD_FILE))) {
+ LOG.info("Reading password for password file {}",
properties.getString(Config.PASSWORD_FILE));
+ FileSystem fileSystem = FileSystem.get(new Configuration());
+ passwordFileStream = fileSystem.open(new
Path(properties.getString(Config.PASSWORD_FILE)));
+ byte[] bytes = new byte[passwordFileStream.available()];
+ passwordFileStream.read(bytes);
+ dataFrameReader = dataFrameReader.option(Config.PASSWORD_PROP, new
String(bytes));
+ } else {
+ throw new IllegalArgumentException(
+ String.format("%s property cannot be null or empty",
Config.PASSWORD_FILE));
+ }
+ } else {
+ throw new IllegalArgumentException(String.format("JDBCSource needs
either a %s or %s to connect to RDBMS "
+ + "datasource", Config.PASSWORD_FILE, Config.PASSWORD));
+ }
+ } else if
(!StringUtils.isNullOrEmpty(properties.getString(Config.PASSWORD))) {
+ dataFrameReader = dataFrameReader.option(Config.PASSWORD_PROP,
properties.getString(Config.PASSWORD));
+ } else {
+ throw new IllegalArgumentException(String.format("%s cannot be null or
empty. ", Config.PASSWORD));
+ }
+ if (properties.containsKey(Config.EXTRA_OPTIONS)) {
+ if
(!StringUtils.isNullOrEmpty(properties.getString(Config.EXTRA_OPTIONS))) {
+ LOG.info("Setting {}", Config.EXTRA_OPTIONS);
+ String[] options =
properties.getString(Config.EXTRA_OPTIONS).split(",");
+ for (String option : options) {
+ if (!StringUtils.isNullOrEmpty(option)) {
+ String[] kv = option.split("=");
+ dataFrameReader = dataFrameReader.option(kv[0], kv[1]);
+ LOG.info("{} = {} has been set for JDBC pull ", kv[0], kv[1]);
+ }
+ }
+ }
+ }
+ if (properties.getBoolean(Config.IS_INCREMENTAL)) {
+ DataSourceUtils.checkRequiredProperties(properties,
Arrays.asList(Config.INCREMENTAL_COLUMN));
+ }
+ return dataFrameReader;
+ } catch (Exception e) {
+ throw new HoodieException(e);
+ } finally {
+ IOUtils.closeStream(passwordFileStream);
+ }
+ }
+
+ @Override
+ protected Pair<Option<Dataset<Row>>, String> fetchNextBatch(Option<String>
lastCkptStr, long sourceLimit) {
+ try {
+ DataSourceUtils.checkRequiredProperties(props, Arrays.asList(Config.URL,
Config.DRIVER_CLASS, Config.USER,
+ Config.RDBMS_TABLE_NAME, Config.IS_INCREMENTAL));
+
+ Option<String> beginInstant =
+ lastCkptStr.isPresent() ? lastCkptStr.get().isEmpty() ?
Option.empty() : lastCkptStr : Option.empty();
+ boolean isIncremental = props.getBoolean(Config.IS_INCREMENTAL);
+ if (beginInstant.equals(Option.empty())) {
+ LOG.info("No previous checkpoints found.. ");
+ Dataset<Row> rowDataset =
validatePropsAndGetDataFrameReader(sparkSession, props).load();
+ return sendDfAndCheckpoint(rowDataset, isIncremental);
+ } else {
+ if (StringUtils.isNullOrEmpty(beginInstant.get())) {
+ LOG.warn("Previous checkpoint entry was null or empty. Falling back
to full jdbc pull.");
+ Dataset<Row> rowDataset =
validatePropsAndGetDataFrameReader(sparkSession, props).load();
+ return sendDfAndCheckpoint(rowDataset, isIncremental);
+ } else {
+ String query = String
+ .format(ppdQuery, props.getString(Config.RDBMS_TABLE_NAME),
props.getString(Config.INCREMENTAL_COLUMN),
+ beginInstant.get());
+ LOG.info("Referenced last checkpoint and prepared new predicate
pushdown query for jdbc pull {}", query);
+ Dataset<Row> rowDataset =
validatePropsAndGetDataFrameReader(sparkSession, props)
+ .option(props.getString(Config.RDBMS_TABLE_PROP), query).load();
+ return sendDfAndCheckpoint(rowDataset, isIncremental);
+
+ }
+ }
+ } catch (Exception e) {
+ LOG.error("Exception while running JDBCSource ", e);
+ return Pair.of(Option.empty(), null);
+ }
+ }
+
+ @NotNull
+ private Pair<Option<Dataset<Row>>, String> sendDfAndCheckpoint(Dataset<Row>
rowDataset, boolean isIncremental) {
+ if (isIncremental) {
+ Column incrementalColumn =
rowDataset.col(props.getString(Config.INCREMENTAL_COLUMN));
+ final String max =
rowDataset.agg(functions.max(incrementalColumn).cast(DataTypes.StringType)).first()
+ .getString(0);
+ LOG.info("Sending {} with checkpoint val {} ", incrementalColumn, max);
+ return Pair.of(Option.of(rowDataset), max);
+ } else {
Review comment:
Let me put it this way for you.
case 1: A JDBC job can be continuous but not incremental
case 2: A JDBC job can be continuous and incremental
case 3: A JDBC job is not continuous but is incremental
case 4: A JDBC job is not continuous and not incremental
In case 1 and case 4 we never reference anything to/from the checkpoint and
we pull the entire table always.
In case 2 and 3 we reference the checkpoint to see the last checkpointed
value. If the value is present, we build an incremental query or else we do the
whole table pull and write the max of incremental column and keep doing this
same thing again and again. This is what I think will be best.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
