You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by GitBox <gi...@apache.org> on 2021/05/31 20:22:35 UTC
[GitHub] [drill] cgivre opened a new pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
cgivre opened a new pull request #2241:
URL: https://github.com/apache/drill/pull/2241
# [DRILL-7938](https://issues.apache.org/jira/browse/DRILL-7938): Convert JDBC Storage Plugin to EVF
## Description
This PR converts the JDBC Storage plugin to use the Enhanced Vector Framework (EVF). Additionally, it updates HikariCP to version 4.0.3.
## Documentation
No user facing changes.
## Testing
Existing unit tests all pass.
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on pull request #2241:
URL: https://github.com/apache/drill/pull/2241#issuecomment-855077451
@vvysotskyi @luocooong
Thanks for the review comments. I believe I've addressed everything. Could you please take a look?
Have a great weekend!
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r645894400
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -53,6 +54,10 @@
@BeforeClass
public static void init() throws Exception {
startCluster(ClusterFixture.builder(dirTestWatcher));
+ // Force timezone to UTC for these tests.
Review comment:
Fixed.
--
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:
users@infra.apache.org
[GitHub] [drill] luocooong commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
luocooong commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643936655
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -53,6 +54,10 @@
@BeforeClass
public static void init() throws Exception {
startCluster(ClusterFixture.builder(dirTestWatcher));
+ // Force timezone to UTC for these tests.
Review comment:
I recommend that :
```java
private static TimeZone defaultTimeZone;
@BeforeClass
public static void setUp() {
defaultTimeZone = TimeZone.getDefault();
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}
@AfterClass
public static void cleanUp() {
TimeZone.setDefault(defaultTimeZone);
}
```
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643606960
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcScanBatchCreator.java
##########
@@ -0,0 +1,95 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.exceptions.ExecutionSetupException;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.common.types.Types;
+import org.apache.drill.exec.ops.ExecutorFragmentContext;
+import org.apache.drill.exec.physical.impl.BatchCreator;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework.ReaderFactory;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework.ScanFrameworkBuilder;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.record.CloseableRecordBatch;
+import org.apache.drill.exec.record.RecordBatch;
+import org.apache.drill.exec.server.options.OptionManager;
+import org.apache.drill.shaded.guava.com.google.common.base.Preconditions;
+
+import java.util.List;
+
+public class JdbcScanBatchCreator implements BatchCreator<JdbcSubScan> {
+
+ @Override
+ public CloseableRecordBatch getBatch(ExecutorFragmentContext context,
+ JdbcSubScan subScan, List<RecordBatch> children) throws ExecutionSetupException {
+ Preconditions.checkArgument(children.isEmpty());
+
+ try {
+ ScanFrameworkBuilder builder = createBuilder(context.getOptions(), subScan);
+ return builder.buildScanOperator(context, subScan);
+ } catch (UserException e) {
+ // Rethrow user exceptions directly
+ throw e;
+ } catch (Throwable e) {
+ // Wrap all others
+ throw new ExecutionSetupException(e);
+ }
+ }
+
+ private ScanFrameworkBuilder createBuilder(OptionManager options, JdbcSubScan subScan) {
+ JdbcStorageConfig config = subScan.getConfig();
+ ScanFrameworkBuilder builder = new ScanFrameworkBuilder();
+ builder.projection(subScan.getColumns());
+ builder.setUserName(subScan.getUserName());
+
+ // Reader
+ ReaderFactory readerFactory = new JdbcReaderFactory(config, subScan);
+ builder.setReaderFactory(readerFactory);
+ builder.nullType(Types.optional(MinorType.VARCHAR));
+ return builder;
+ }
+
+ private static class JdbcReaderFactory implements ReaderFactory {
Review comment:
Fixed
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643364804
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
Review comment:
Fixed
--
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:
users@infra.apache.org
[GitHub] [drill] vvysotskyi commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
vvysotskyi commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r644063373
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
+ private RowSetLoader rowWriter;
+ private CustomErrorContext errorContext;
+ private List<JdbcColumnWriter> columnWriters;
+ private List<JdbcColumn> jdbcColumns;
+
+
+ public JdbcBatchReader(DataSource source, String sql, List<SchemaPath> columns) {
+ this.source = source;
+ this.sql = sql;
+ this.columns = columns;
+ }
+
+ static {
+ JDBC_TYPE_MAPPINGS = ImmutableMap.<Integer, MinorType>builder()
+ .put(java.sql.Types.DOUBLE, MinorType.FLOAT8)
+ .put(java.sql.Types.FLOAT, MinorType.FLOAT4)
+ .put(java.sql.Types.TINYINT, MinorType.INT)
+ .put(java.sql.Types.SMALLINT, MinorType.INT)
+ .put(java.sql.Types.INTEGER, MinorType.INT)
+ .put(java.sql.Types.BIGINT, MinorType.BIGINT)
+
+ .put(java.sql.Types.CHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.VARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.CLOB, MinorType.VARCHAR)
+
+ .put(java.sql.Types.NCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.NVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGNVARCHAR, MinorType.VARCHAR)
+
+ .put(java.sql.Types.VARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.LONGVARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.BLOB, MinorType.VARBINARY)
+
+ .put(java.sql.Types.NUMERIC, MinorType.FLOAT8)
+ .put(java.sql.Types.DECIMAL, MinorType.VARDECIMAL)
+ .put(java.sql.Types.REAL, MinorType.FLOAT8)
+
+ .put(java.sql.Types.DATE, MinorType.DATE)
+ .put(java.sql.Types.TIME, MinorType.TIME)
+ .put(java.sql.Types.TIMESTAMP, MinorType.TIMESTAMP)
+
+ .put(java.sql.Types.BOOLEAN, MinorType.BIT)
+ .put(java.sql.Types.BIT, MinorType.BIT)
+
+ .build();
+ }
+
+ @Override
+ public boolean open(SchemaNegotiator negotiator) {
+
+ this.errorContext = negotiator.parentErrorContext();
+ try {
+ connection = source.getConnection();
+ statement = connection.prepareStatement(sql);
+ resultSet = statement.executeQuery();
+ builder = new SchemaBuilder();
+
+ TupleMetadata drillSchema = buildSchema();
+ negotiator.tableSchema(drillSchema, true);
+ ResultSetLoader resultSetLoader = negotiator.build();
+
+ // Create ScalarWriters
+ rowWriter = resultSetLoader.writer();
+ populateWriterArray();
+
+ } catch (SQLException e) {
+ throw UserException.dataReadError(e)
+ .message("The JDBC storage plugin failed while trying setup the SQL query. ")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public boolean next() {
+ while (!rowWriter.isFull()) {
+ if (!processRow()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean processRow() {
+ try {
+ if (!resultSet.next()) {
+ return false;
+ }
+ rowWriter.start();
+ // Process results
+ for (JdbcColumnWriter writer : columnWriters) {
+ writer.load(resultSet);
+ }
+ rowWriter.save();
+ } catch (SQLException e) {
+ throw UserException
+ .dataReadError(e)
+ .message("Failure while attempting to read from database.")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public void close() {
+ AutoCloseables.closeSilently(resultSet, statement, connection);
+ }
+
+ private TupleMetadata buildSchema() throws SQLException {
+ ResultSetMetaData meta = resultSet.getMetaData();
+ jdbcColumns = new ArrayList<>();
+
+ int columnsCount = meta.getColumnCount();
+
+ if (columns.size() != columnsCount) {
+ throw UserException
+ .validationError()
+ .message(
+ "Expected columns count differs from the returned one.\n" +
+ "Expected columns: %s\n" +
+ "Returned columns count: %s",
+ columns, columnsCount)
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ for (int i = 1; i <= columnsCount; i++) {
+ String name = columns.get(i - 1).getRootSegmentPath();
+ // column index in ResultSetMetaData starts from 1
+ int jdbcType = meta.getColumnType(i);
+ int width = Math.min(meta.getPrecision(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericPrecision());
+ int scale = Math.min(meta.getScale(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericScale());
+
+ MinorType minorType = JDBC_TYPE_MAPPINGS.get(jdbcType);
+ if (minorType == null) {
+ logger.warn("Ignoring column that is unsupported.", UserException
+ .unsupportedError()
+ .message(
+ "A column you queried has a data type that is not currently supported by the JDBC storage plugin. "
+ + "The column's name was %s and its JDBC data type was %s. ",
+ name,
+ nameFromType(jdbcType))
+ .addContext("Sql", sql)
+ .addContext("Column Name", name)
+ .addContext(errorContext)
+ .build(logger));
+ continue;
+ }
+
+ jdbcColumns.add(new JdbcColumn(name, minorType));
+ if (minorType == MinorType.VARDECIMAL) {
Review comment:
Precision shows the max possible size for varchar of binary, so Drill will be able to allocate less memory compared to the default one. Also, this precision may be exposed to the end-user, and he might use it.
Initially, it was set for all types but looks like this change breaks it.
--
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:
users@infra.apache.org
[GitHub] [drill] vvysotskyi commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
vvysotskyi commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643313005
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcScanBatchCreator.java
##########
@@ -0,0 +1,95 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.exceptions.ExecutionSetupException;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.common.types.Types;
+import org.apache.drill.exec.ops.ExecutorFragmentContext;
+import org.apache.drill.exec.physical.impl.BatchCreator;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework.ReaderFactory;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework.ScanFrameworkBuilder;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.record.CloseableRecordBatch;
+import org.apache.drill.exec.record.RecordBatch;
+import org.apache.drill.exec.server.options.OptionManager;
+import org.apache.drill.shaded.guava.com.google.common.base.Preconditions;
+
+import java.util.List;
+
+public class JdbcScanBatchCreator implements BatchCreator<JdbcSubScan> {
+
+ @Override
+ public CloseableRecordBatch getBatch(ExecutorFragmentContext context,
+ JdbcSubScan subScan, List<RecordBatch> children) throws ExecutionSetupException {
+ Preconditions.checkArgument(children.isEmpty());
+
+ try {
+ ScanFrameworkBuilder builder = createBuilder(context.getOptions(), subScan);
+ return builder.buildScanOperator(context, subScan);
+ } catch (UserException e) {
+ // Rethrow user exceptions directly
+ throw e;
+ } catch (Throwable e) {
+ // Wrap all others
+ throw new ExecutionSetupException(e);
+ }
+ }
+
+ private ScanFrameworkBuilder createBuilder(OptionManager options, JdbcSubScan subScan) {
+ JdbcStorageConfig config = subScan.getConfig();
+ ScanFrameworkBuilder builder = new ScanFrameworkBuilder();
+ builder.projection(subScan.getColumns());
+ builder.setUserName(subScan.getUserName());
+
+ // Reader
+ ReaderFactory readerFactory = new JdbcReaderFactory(config, subScan);
+ builder.setReaderFactory(readerFactory);
+ builder.nullType(Types.optional(MinorType.VARCHAR));
+ return builder;
+ }
+
+ private static class JdbcReaderFactory implements ReaderFactory {
Review comment:
No need to create one more implementation. Please use `BasicScanFactory`.
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
+ private RowSetLoader rowWriter;
+ private CustomErrorContext errorContext;
+ private List<JdbcColumnWriter> columnWriters;
+ private List<JdbcColumn> jdbcColumns;
+
+
+ public JdbcBatchReader(DataSource source, String sql, List<SchemaPath> columns) {
+ this.source = source;
+ this.sql = sql;
+ this.columns = columns;
+ }
+
+ static {
+ JDBC_TYPE_MAPPINGS = ImmutableMap.<Integer, MinorType>builder()
+ .put(java.sql.Types.DOUBLE, MinorType.FLOAT8)
+ .put(java.sql.Types.FLOAT, MinorType.FLOAT4)
+ .put(java.sql.Types.TINYINT, MinorType.INT)
+ .put(java.sql.Types.SMALLINT, MinorType.INT)
+ .put(java.sql.Types.INTEGER, MinorType.INT)
+ .put(java.sql.Types.BIGINT, MinorType.BIGINT)
+
+ .put(java.sql.Types.CHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.VARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.CLOB, MinorType.VARCHAR)
+
+ .put(java.sql.Types.NCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.NVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGNVARCHAR, MinorType.VARCHAR)
+
+ .put(java.sql.Types.VARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.LONGVARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.BLOB, MinorType.VARBINARY)
+
+ .put(java.sql.Types.NUMERIC, MinorType.FLOAT8)
+ .put(java.sql.Types.DECIMAL, MinorType.VARDECIMAL)
+ .put(java.sql.Types.REAL, MinorType.FLOAT8)
+
+ .put(java.sql.Types.DATE, MinorType.DATE)
+ .put(java.sql.Types.TIME, MinorType.TIME)
+ .put(java.sql.Types.TIMESTAMP, MinorType.TIMESTAMP)
+
+ .put(java.sql.Types.BOOLEAN, MinorType.BIT)
+ .put(java.sql.Types.BIT, MinorType.BIT)
+
+ .build();
+ }
+
+ @Override
+ public boolean open(SchemaNegotiator negotiator) {
+
+ this.errorContext = negotiator.parentErrorContext();
+ try {
+ connection = source.getConnection();
+ statement = connection.prepareStatement(sql);
+ resultSet = statement.executeQuery();
+ builder = new SchemaBuilder();
+
+ TupleMetadata drillSchema = buildSchema();
+ negotiator.tableSchema(drillSchema, true);
+ ResultSetLoader resultSetLoader = negotiator.build();
+
+ // Create ScalarWriters
+ rowWriter = resultSetLoader.writer();
+ populateWriterArray();
+
+ } catch (SQLException e) {
+ throw UserException.dataReadError(e)
+ .message("The JDBC storage plugin failed while trying setup the SQL query. ")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public boolean next() {
+ while (!rowWriter.isFull()) {
+ if (!processRow()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean processRow() {
+ try {
+ if (!resultSet.next()) {
+ return false;
+ }
+ rowWriter.start();
+ // Process results
+ for (JdbcColumnWriter writer : columnWriters) {
+ writer.load(resultSet);
+ }
+ rowWriter.save();
+ } catch (SQLException e) {
+ throw UserException
+ .dataReadError(e)
+ .message("Failure while attempting to read from database.")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public void close() {
+ AutoCloseables.closeSilently(resultSet, statement, connection);
+ }
+
+ private TupleMetadata buildSchema() throws SQLException {
+ ResultSetMetaData meta = resultSet.getMetaData();
+ jdbcColumns = new ArrayList<>();
+
+ int columnsCount = meta.getColumnCount();
+
+ if (columns.size() != columnsCount) {
+ throw UserException
+ .validationError()
+ .message(
+ "Expected columns count differs from the returned one.\n" +
+ "Expected columns: %s\n" +
+ "Returned columns count: %s",
+ columns, columnsCount)
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ for (int i = 1; i <= columnsCount; i++) {
+ String name = columns.get(i - 1).getRootSegmentPath();
+ // column index in ResultSetMetaData starts from 1
+ int jdbcType = meta.getColumnType(i);
+ int width = Math.min(meta.getPrecision(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericPrecision());
+ int scale = Math.min(meta.getScale(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericScale());
+
+ MinorType minorType = JDBC_TYPE_MAPPINGS.get(jdbcType);
+ if (minorType == null) {
+ logger.warn("Ignoring column that is unsupported.", UserException
+ .unsupportedError()
+ .message(
+ "A column you queried has a data type that is not currently supported by the JDBC storage plugin. "
+ + "The column's name was %s and its JDBC data type was %s. ",
+ name,
+ nameFromType(jdbcType))
+ .addContext("Sql", sql)
+ .addContext("Column Name", name)
+ .addContext(errorContext)
+ .build(logger));
+ continue;
+ }
+
+ jdbcColumns.add(new JdbcColumn(name, minorType));
+ if (minorType == MinorType.VARDECIMAL) {
+ builder.addNullable(name, minorType, width, scale);
+ } else {
+ builder.addNullable(name, minorType);
+ }
+ }
+
+ return builder.buildSchema();
+ }
+
+ private void populateWriterArray() {
+ columnWriters = new ArrayList<>();
+ int colPosition = 1;
+
+ for (JdbcColumn col : jdbcColumns) {
+ switch (col.type) {
+ case VARCHAR:
+ columnWriters.add(new JdbcVarcharWriter(col.colName, rowWriter, colPosition));
+ break;
+ case FLOAT4:
+ columnWriters.add(new JdbcFloatWriter(col.colName, rowWriter, colPosition));
+ break;
+ case FLOAT8:
+ columnWriters.add(new JdbcDoubleWriter(col.colName, rowWriter, colPosition));
+ break;
+ case INT:
+ columnWriters.add(new JdbcIntWriter(col.colName, rowWriter, colPosition));
+ break;
+ case BIGINT:
+ columnWriters.add(new JdbcBigintWriter(col.colName, rowWriter, colPosition));
+ break;
+ case DATE:
+ columnWriters.add(new JdbcDateWriter(col.colName, rowWriter, colPosition));
+ break;
+ case TIME:
+ columnWriters.add(new JdbcTimeWriter(col.colName, rowWriter, colPosition));
+ break;
+ case TIMESTAMP:
+ columnWriters.add(new JdbcTimestampWriter(col.colName, rowWriter, colPosition));
+ break;
+ case VARBINARY:
+ columnWriters.add(new JdbcVarbinaryWriter(col.colName, rowWriter, colPosition));
+ break;
+ case BIT:
+ columnWriters.add(new JdbcBitWriter(col.colName, rowWriter, colPosition));
+ break;
+ case VARDECIMAL:
+ columnWriters.add(new JdbcVardecimalWriter(col.colName, rowWriter, colPosition));
+ break;
+ default:
+ logger.warn("Unsupported data type {} found at column {}", col.type.getDescriptorForType(), col.colName);
Review comment:
For the case when we will reach this branch, it will break values for all columns after this one, since we will have invalid `colPosition` values. Please store the info about column index to `JdbcColumn` and use it in this method.
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -83,6 +84,7 @@ public void testCrossSourceMultiFragmentJoin() throws Exception {
}
@Test
+ @Ignore("Ignore this test since h2 mangles dates and times due to improper timezone support.")
Review comment:
Is it possible to use something like `ExecTest.mockUsDateFormatSymbols()` instead of just ignoring the test?
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
Review comment:
No need to declare it as a field. Schema is built only when calling the `buildSchema()` method, so make it just to return the resulting schema and remove this field.
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
+ private RowSetLoader rowWriter;
+ private CustomErrorContext errorContext;
+ private List<JdbcColumnWriter> columnWriters;
+ private List<JdbcColumn> jdbcColumns;
+
+
+ public JdbcBatchReader(DataSource source, String sql, List<SchemaPath> columns) {
+ this.source = source;
+ this.sql = sql;
+ this.columns = columns;
+ }
+
+ static {
+ JDBC_TYPE_MAPPINGS = ImmutableMap.<Integer, MinorType>builder()
+ .put(java.sql.Types.DOUBLE, MinorType.FLOAT8)
+ .put(java.sql.Types.FLOAT, MinorType.FLOAT4)
+ .put(java.sql.Types.TINYINT, MinorType.INT)
+ .put(java.sql.Types.SMALLINT, MinorType.INT)
+ .put(java.sql.Types.INTEGER, MinorType.INT)
+ .put(java.sql.Types.BIGINT, MinorType.BIGINT)
+
+ .put(java.sql.Types.CHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.VARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.CLOB, MinorType.VARCHAR)
+
+ .put(java.sql.Types.NCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.NVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGNVARCHAR, MinorType.VARCHAR)
+
+ .put(java.sql.Types.VARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.LONGVARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.BLOB, MinorType.VARBINARY)
+
+ .put(java.sql.Types.NUMERIC, MinorType.FLOAT8)
+ .put(java.sql.Types.DECIMAL, MinorType.VARDECIMAL)
+ .put(java.sql.Types.REAL, MinorType.FLOAT8)
+
+ .put(java.sql.Types.DATE, MinorType.DATE)
+ .put(java.sql.Types.TIME, MinorType.TIME)
+ .put(java.sql.Types.TIMESTAMP, MinorType.TIMESTAMP)
+
+ .put(java.sql.Types.BOOLEAN, MinorType.BIT)
+ .put(java.sql.Types.BIT, MinorType.BIT)
+
+ .build();
+ }
+
+ @Override
+ public boolean open(SchemaNegotiator negotiator) {
+
+ this.errorContext = negotiator.parentErrorContext();
+ try {
+ connection = source.getConnection();
+ statement = connection.prepareStatement(sql);
+ resultSet = statement.executeQuery();
+ builder = new SchemaBuilder();
+
+ TupleMetadata drillSchema = buildSchema();
+ negotiator.tableSchema(drillSchema, true);
+ ResultSetLoader resultSetLoader = negotiator.build();
+
+ // Create ScalarWriters
+ rowWriter = resultSetLoader.writer();
+ populateWriterArray();
+
+ } catch (SQLException e) {
+ throw UserException.dataReadError(e)
+ .message("The JDBC storage plugin failed while trying setup the SQL query. ")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public boolean next() {
+ while (!rowWriter.isFull()) {
+ if (!processRow()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean processRow() {
+ try {
+ if (!resultSet.next()) {
+ return false;
+ }
+ rowWriter.start();
+ // Process results
+ for (JdbcColumnWriter writer : columnWriters) {
+ writer.load(resultSet);
+ }
+ rowWriter.save();
+ } catch (SQLException e) {
+ throw UserException
+ .dataReadError(e)
+ .message("Failure while attempting to read from database.")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public void close() {
+ AutoCloseables.closeSilently(resultSet, statement, connection);
+ }
+
+ private TupleMetadata buildSchema() throws SQLException {
+ ResultSetMetaData meta = resultSet.getMetaData();
+ jdbcColumns = new ArrayList<>();
+
+ int columnsCount = meta.getColumnCount();
+
+ if (columns.size() != columnsCount) {
+ throw UserException
+ .validationError()
+ .message(
+ "Expected columns count differs from the returned one.\n" +
+ "Expected columns: %s\n" +
+ "Returned columns count: %s",
+ columns, columnsCount)
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ for (int i = 1; i <= columnsCount; i++) {
+ String name = columns.get(i - 1).getRootSegmentPath();
+ // column index in ResultSetMetaData starts from 1
+ int jdbcType = meta.getColumnType(i);
+ int width = Math.min(meta.getPrecision(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericPrecision());
+ int scale = Math.min(meta.getScale(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericScale());
+
+ MinorType minorType = JDBC_TYPE_MAPPINGS.get(jdbcType);
+ if (minorType == null) {
+ logger.warn("Ignoring column that is unsupported.", UserException
+ .unsupportedError()
+ .message(
+ "A column you queried has a data type that is not currently supported by the JDBC storage plugin. "
+ + "The column's name was %s and its JDBC data type was %s. ",
+ name,
+ nameFromType(jdbcType))
+ .addContext("Sql", sql)
+ .addContext("Column Name", name)
+ .addContext(errorContext)
+ .build(logger));
+ continue;
+ }
+
+ jdbcColumns.add(new JdbcColumn(name, minorType));
+ if (minorType == MinorType.VARDECIMAL) {
Review comment:
Not only decimal type supports scale and/or precision. It might be also varchar, timestamp, binary, and some other types...
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643988575
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -53,6 +54,10 @@
@BeforeClass
public static void init() throws Exception {
startCluster(ClusterFixture.builder(dirTestWatcher));
+ // Force timezone to UTC for these tests.
Review comment:
Good call. I wonder if it might actually make sense to include that in the actual test architecture as there have been a lot of issues with unit tests and timezones. What do you think?
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre merged pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre merged pull request #2241:
URL: https://github.com/apache/drill/pull/2241
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r645893169
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
+ private RowSetLoader rowWriter;
+ private CustomErrorContext errorContext;
+ private List<JdbcColumnWriter> columnWriters;
+ private List<JdbcColumn> jdbcColumns;
+
+
+ public JdbcBatchReader(DataSource source, String sql, List<SchemaPath> columns) {
+ this.source = source;
+ this.sql = sql;
+ this.columns = columns;
+ }
+
+ static {
+ JDBC_TYPE_MAPPINGS = ImmutableMap.<Integer, MinorType>builder()
+ .put(java.sql.Types.DOUBLE, MinorType.FLOAT8)
+ .put(java.sql.Types.FLOAT, MinorType.FLOAT4)
+ .put(java.sql.Types.TINYINT, MinorType.INT)
+ .put(java.sql.Types.SMALLINT, MinorType.INT)
+ .put(java.sql.Types.INTEGER, MinorType.INT)
+ .put(java.sql.Types.BIGINT, MinorType.BIGINT)
+
+ .put(java.sql.Types.CHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.VARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.CLOB, MinorType.VARCHAR)
+
+ .put(java.sql.Types.NCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.NVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGNVARCHAR, MinorType.VARCHAR)
+
+ .put(java.sql.Types.VARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.LONGVARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.BLOB, MinorType.VARBINARY)
+
+ .put(java.sql.Types.NUMERIC, MinorType.FLOAT8)
+ .put(java.sql.Types.DECIMAL, MinorType.VARDECIMAL)
+ .put(java.sql.Types.REAL, MinorType.FLOAT8)
+
+ .put(java.sql.Types.DATE, MinorType.DATE)
+ .put(java.sql.Types.TIME, MinorType.TIME)
+ .put(java.sql.Types.TIMESTAMP, MinorType.TIMESTAMP)
+
+ .put(java.sql.Types.BOOLEAN, MinorType.BIT)
+ .put(java.sql.Types.BIT, MinorType.BIT)
+
+ .build();
+ }
+
+ @Override
+ public boolean open(SchemaNegotiator negotiator) {
+
+ this.errorContext = negotiator.parentErrorContext();
+ try {
+ connection = source.getConnection();
+ statement = connection.prepareStatement(sql);
+ resultSet = statement.executeQuery();
+ builder = new SchemaBuilder();
+
+ TupleMetadata drillSchema = buildSchema();
+ negotiator.tableSchema(drillSchema, true);
+ ResultSetLoader resultSetLoader = negotiator.build();
+
+ // Create ScalarWriters
+ rowWriter = resultSetLoader.writer();
+ populateWriterArray();
+
+ } catch (SQLException e) {
+ throw UserException.dataReadError(e)
+ .message("The JDBC storage plugin failed while trying setup the SQL query. ")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public boolean next() {
+ while (!rowWriter.isFull()) {
+ if (!processRow()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean processRow() {
+ try {
+ if (!resultSet.next()) {
+ return false;
+ }
+ rowWriter.start();
+ // Process results
+ for (JdbcColumnWriter writer : columnWriters) {
+ writer.load(resultSet);
+ }
+ rowWriter.save();
+ } catch (SQLException e) {
+ throw UserException
+ .dataReadError(e)
+ .message("Failure while attempting to read from database.")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public void close() {
+ AutoCloseables.closeSilently(resultSet, statement, connection);
+ }
+
+ private TupleMetadata buildSchema() throws SQLException {
+ ResultSetMetaData meta = resultSet.getMetaData();
+ jdbcColumns = new ArrayList<>();
+
+ int columnsCount = meta.getColumnCount();
+
+ if (columns.size() != columnsCount) {
+ throw UserException
+ .validationError()
+ .message(
+ "Expected columns count differs from the returned one.\n" +
+ "Expected columns: %s\n" +
+ "Returned columns count: %s",
+ columns, columnsCount)
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ for (int i = 1; i <= columnsCount; i++) {
+ String name = columns.get(i - 1).getRootSegmentPath();
+ // column index in ResultSetMetaData starts from 1
+ int jdbcType = meta.getColumnType(i);
+ int width = Math.min(meta.getPrecision(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericPrecision());
+ int scale = Math.min(meta.getScale(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericScale());
+
+ MinorType minorType = JDBC_TYPE_MAPPINGS.get(jdbcType);
+ if (minorType == null) {
+ logger.warn("Ignoring column that is unsupported.", UserException
+ .unsupportedError()
+ .message(
+ "A column you queried has a data type that is not currently supported by the JDBC storage plugin. "
+ + "The column's name was %s and its JDBC data type was %s. ",
+ name,
+ nameFromType(jdbcType))
+ .addContext("Sql", sql)
+ .addContext("Column Name", name)
+ .addContext(errorContext)
+ .build(logger));
+ continue;
+ }
+
+ jdbcColumns.add(new JdbcColumn(name, minorType));
+ if (minorType == MinorType.VARDECIMAL) {
Review comment:
@vvysotskyi
I changed this so that precision and scale are passed to the builder in all cases. It appears that they are ignored when not needed, but more importantly they are present when needed.
--
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:
users@infra.apache.org
[GitHub] [drill] luocooong commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
luocooong commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r644055051
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -53,6 +54,10 @@
@BeforeClass
public static void init() throws Exception {
startCluster(ClusterFixture.builder(dirTestWatcher));
+ // Force timezone to UTC for these tests.
Review comment:
Actually, I really don't recommend setup the timezone in UT. However, I changed my mind when refactor the image plugin. Because the library of image not supported to set timezone by API (Get the timezone of host automatically), the unit tests required to compare the value and only provide the constant value by `testBuilder().jsonBaselineFile()`. So, I have to set the timezone in the unit tests. I think there's no problem if we have to pass the tests.
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643602408
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -83,6 +84,7 @@ public void testCrossSourceMultiFragmentJoin() throws Exception {
}
@Test
+ @Ignore("Ignore this test since h2 mangles dates and times due to improper timezone support.")
Review comment:
I fixed this by adding explicitly setting the timezone to UTC before these tests.
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r643602408
##########
File path: contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithH2IT.java
##########
@@ -83,6 +84,7 @@ public void testCrossSourceMultiFragmentJoin() throws Exception {
}
@Test
+ @Ignore("Ignore this test since h2 mangles dates and times due to improper timezone support.")
Review comment:
I fixed this by adding explicitly setting the timezone to UFT before these tests.
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
+ private RowSetLoader rowWriter;
+ private CustomErrorContext errorContext;
+ private List<JdbcColumnWriter> columnWriters;
+ private List<JdbcColumn> jdbcColumns;
+
+
+ public JdbcBatchReader(DataSource source, String sql, List<SchemaPath> columns) {
+ this.source = source;
+ this.sql = sql;
+ this.columns = columns;
+ }
+
+ static {
+ JDBC_TYPE_MAPPINGS = ImmutableMap.<Integer, MinorType>builder()
+ .put(java.sql.Types.DOUBLE, MinorType.FLOAT8)
+ .put(java.sql.Types.FLOAT, MinorType.FLOAT4)
+ .put(java.sql.Types.TINYINT, MinorType.INT)
+ .put(java.sql.Types.SMALLINT, MinorType.INT)
+ .put(java.sql.Types.INTEGER, MinorType.INT)
+ .put(java.sql.Types.BIGINT, MinorType.BIGINT)
+
+ .put(java.sql.Types.CHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.VARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.CLOB, MinorType.VARCHAR)
+
+ .put(java.sql.Types.NCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.NVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGNVARCHAR, MinorType.VARCHAR)
+
+ .put(java.sql.Types.VARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.LONGVARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.BLOB, MinorType.VARBINARY)
+
+ .put(java.sql.Types.NUMERIC, MinorType.FLOAT8)
+ .put(java.sql.Types.DECIMAL, MinorType.VARDECIMAL)
+ .put(java.sql.Types.REAL, MinorType.FLOAT8)
+
+ .put(java.sql.Types.DATE, MinorType.DATE)
+ .put(java.sql.Types.TIME, MinorType.TIME)
+ .put(java.sql.Types.TIMESTAMP, MinorType.TIMESTAMP)
+
+ .put(java.sql.Types.BOOLEAN, MinorType.BIT)
+ .put(java.sql.Types.BIT, MinorType.BIT)
+
+ .build();
+ }
+
+ @Override
+ public boolean open(SchemaNegotiator negotiator) {
+
+ this.errorContext = negotiator.parentErrorContext();
+ try {
+ connection = source.getConnection();
+ statement = connection.prepareStatement(sql);
+ resultSet = statement.executeQuery();
+ builder = new SchemaBuilder();
+
+ TupleMetadata drillSchema = buildSchema();
+ negotiator.tableSchema(drillSchema, true);
+ ResultSetLoader resultSetLoader = negotiator.build();
+
+ // Create ScalarWriters
+ rowWriter = resultSetLoader.writer();
+ populateWriterArray();
+
+ } catch (SQLException e) {
+ throw UserException.dataReadError(e)
+ .message("The JDBC storage plugin failed while trying setup the SQL query. ")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public boolean next() {
+ while (!rowWriter.isFull()) {
+ if (!processRow()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean processRow() {
+ try {
+ if (!resultSet.next()) {
+ return false;
+ }
+ rowWriter.start();
+ // Process results
+ for (JdbcColumnWriter writer : columnWriters) {
+ writer.load(resultSet);
+ }
+ rowWriter.save();
+ } catch (SQLException e) {
+ throw UserException
+ .dataReadError(e)
+ .message("Failure while attempting to read from database.")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public void close() {
+ AutoCloseables.closeSilently(resultSet, statement, connection);
+ }
+
+ private TupleMetadata buildSchema() throws SQLException {
+ ResultSetMetaData meta = resultSet.getMetaData();
+ jdbcColumns = new ArrayList<>();
+
+ int columnsCount = meta.getColumnCount();
+
+ if (columns.size() != columnsCount) {
+ throw UserException
+ .validationError()
+ .message(
+ "Expected columns count differs from the returned one.\n" +
+ "Expected columns: %s\n" +
+ "Returned columns count: %s",
+ columns, columnsCount)
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ for (int i = 1; i <= columnsCount; i++) {
+ String name = columns.get(i - 1).getRootSegmentPath();
+ // column index in ResultSetMetaData starts from 1
+ int jdbcType = meta.getColumnType(i);
+ int width = Math.min(meta.getPrecision(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericPrecision());
+ int scale = Math.min(meta.getScale(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericScale());
+
+ MinorType minorType = JDBC_TYPE_MAPPINGS.get(jdbcType);
+ if (minorType == null) {
+ logger.warn("Ignoring column that is unsupported.", UserException
+ .unsupportedError()
+ .message(
+ "A column you queried has a data type that is not currently supported by the JDBC storage plugin. "
+ + "The column's name was %s and its JDBC data type was %s. ",
+ name,
+ nameFromType(jdbcType))
+ .addContext("Sql", sql)
+ .addContext("Column Name", name)
+ .addContext(errorContext)
+ .build(logger));
+ continue;
+ }
+
+ jdbcColumns.add(new JdbcColumn(name, minorType));
+ if (minorType == MinorType.VARDECIMAL) {
+ builder.addNullable(name, minorType, width, scale);
+ } else {
+ builder.addNullable(name, minorType);
+ }
+ }
+
+ return builder.buildSchema();
+ }
+
+ private void populateWriterArray() {
+ columnWriters = new ArrayList<>();
+ int colPosition = 1;
+
+ for (JdbcColumn col : jdbcColumns) {
+ switch (col.type) {
+ case VARCHAR:
+ columnWriters.add(new JdbcVarcharWriter(col.colName, rowWriter, colPosition));
+ break;
+ case FLOAT4:
+ columnWriters.add(new JdbcFloatWriter(col.colName, rowWriter, colPosition));
+ break;
+ case FLOAT8:
+ columnWriters.add(new JdbcDoubleWriter(col.colName, rowWriter, colPosition));
+ break;
+ case INT:
+ columnWriters.add(new JdbcIntWriter(col.colName, rowWriter, colPosition));
+ break;
+ case BIGINT:
+ columnWriters.add(new JdbcBigintWriter(col.colName, rowWriter, colPosition));
+ break;
+ case DATE:
+ columnWriters.add(new JdbcDateWriter(col.colName, rowWriter, colPosition));
+ break;
+ case TIME:
+ columnWriters.add(new JdbcTimeWriter(col.colName, rowWriter, colPosition));
+ break;
+ case TIMESTAMP:
+ columnWriters.add(new JdbcTimestampWriter(col.colName, rowWriter, colPosition));
+ break;
+ case VARBINARY:
+ columnWriters.add(new JdbcVarbinaryWriter(col.colName, rowWriter, colPosition));
+ break;
+ case BIT:
+ columnWriters.add(new JdbcBitWriter(col.colName, rowWriter, colPosition));
+ break;
+ case VARDECIMAL:
+ columnWriters.add(new JdbcVardecimalWriter(col.colName, rowWriter, colPosition));
+ break;
+ default:
+ logger.warn("Unsupported data type {} found at column {}", col.type.getDescriptorForType(), col.colName);
Review comment:
Fixed
--
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:
users@infra.apache.org
[GitHub] [drill] cgivre commented on a change in pull request #2241: DRILL-7938: Convert JDBC Storage Plugin to EVF
Posted by GitBox <gi...@apache.org>.
cgivre commented on a change in pull request #2241:
URL: https://github.com/apache/drill/pull/2241#discussion_r644000634
##########
File path: contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcBatchReader.java
##########
@@ -0,0 +1,311 @@
+/*
+ * 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.drill.exec.store.jdbc;
+
+import org.apache.drill.common.AutoCloseables;
+import org.apache.drill.common.exceptions.CustomErrorContext;
+import org.apache.drill.common.exceptions.UserException;
+import org.apache.drill.common.expression.SchemaPath;
+import org.apache.drill.common.types.TypeProtos.MinorType;
+import org.apache.drill.exec.physical.impl.scan.framework.ManagedReader;
+import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
+import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.SchemaBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBigintWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcBitWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcColumnWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDateWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcDoubleWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcFloatWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcIntWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimeWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcTimestampWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarbinaryWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVarcharWriter;
+import org.apache.drill.exec.store.jdbc.writers.JdbcVardecimalWriter;
+import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableMap;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.lang.reflect.Field;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import static org.apache.drill.exec.planner.types.DrillRelDataTypeSystem.DRILL_REL_DATATYPE_SYSTEM;
+
+public class JdbcBatchReader implements ManagedReader<SchemaNegotiator> {
+
+ private static final Logger logger = LoggerFactory.getLogger(JdbcBatchReader.class);
+ private static final ImmutableMap<Integer, MinorType> JDBC_TYPE_MAPPINGS;
+ private final DataSource source;
+ private final String sql;
+ private final List<SchemaPath> columns;
+ private Connection connection;
+ private PreparedStatement statement;
+ private ResultSet resultSet;
+ private SchemaBuilder builder;
+ private RowSetLoader rowWriter;
+ private CustomErrorContext errorContext;
+ private List<JdbcColumnWriter> columnWriters;
+ private List<JdbcColumn> jdbcColumns;
+
+
+ public JdbcBatchReader(DataSource source, String sql, List<SchemaPath> columns) {
+ this.source = source;
+ this.sql = sql;
+ this.columns = columns;
+ }
+
+ static {
+ JDBC_TYPE_MAPPINGS = ImmutableMap.<Integer, MinorType>builder()
+ .put(java.sql.Types.DOUBLE, MinorType.FLOAT8)
+ .put(java.sql.Types.FLOAT, MinorType.FLOAT4)
+ .put(java.sql.Types.TINYINT, MinorType.INT)
+ .put(java.sql.Types.SMALLINT, MinorType.INT)
+ .put(java.sql.Types.INTEGER, MinorType.INT)
+ .put(java.sql.Types.BIGINT, MinorType.BIGINT)
+
+ .put(java.sql.Types.CHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.VARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.CLOB, MinorType.VARCHAR)
+
+ .put(java.sql.Types.NCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.NVARCHAR, MinorType.VARCHAR)
+ .put(java.sql.Types.LONGNVARCHAR, MinorType.VARCHAR)
+
+ .put(java.sql.Types.VARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.LONGVARBINARY, MinorType.VARBINARY)
+ .put(java.sql.Types.BLOB, MinorType.VARBINARY)
+
+ .put(java.sql.Types.NUMERIC, MinorType.FLOAT8)
+ .put(java.sql.Types.DECIMAL, MinorType.VARDECIMAL)
+ .put(java.sql.Types.REAL, MinorType.FLOAT8)
+
+ .put(java.sql.Types.DATE, MinorType.DATE)
+ .put(java.sql.Types.TIME, MinorType.TIME)
+ .put(java.sql.Types.TIMESTAMP, MinorType.TIMESTAMP)
+
+ .put(java.sql.Types.BOOLEAN, MinorType.BIT)
+ .put(java.sql.Types.BIT, MinorType.BIT)
+
+ .build();
+ }
+
+ @Override
+ public boolean open(SchemaNegotiator negotiator) {
+
+ this.errorContext = negotiator.parentErrorContext();
+ try {
+ connection = source.getConnection();
+ statement = connection.prepareStatement(sql);
+ resultSet = statement.executeQuery();
+ builder = new SchemaBuilder();
+
+ TupleMetadata drillSchema = buildSchema();
+ negotiator.tableSchema(drillSchema, true);
+ ResultSetLoader resultSetLoader = negotiator.build();
+
+ // Create ScalarWriters
+ rowWriter = resultSetLoader.writer();
+ populateWriterArray();
+
+ } catch (SQLException e) {
+ throw UserException.dataReadError(e)
+ .message("The JDBC storage plugin failed while trying setup the SQL query. ")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public boolean next() {
+ while (!rowWriter.isFull()) {
+ if (!processRow()) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean processRow() {
+ try {
+ if (!resultSet.next()) {
+ return false;
+ }
+ rowWriter.start();
+ // Process results
+ for (JdbcColumnWriter writer : columnWriters) {
+ writer.load(resultSet);
+ }
+ rowWriter.save();
+ } catch (SQLException e) {
+ throw UserException
+ .dataReadError(e)
+ .message("Failure while attempting to read from database.")
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ return true;
+ }
+
+ @Override
+ public void close() {
+ AutoCloseables.closeSilently(resultSet, statement, connection);
+ }
+
+ private TupleMetadata buildSchema() throws SQLException {
+ ResultSetMetaData meta = resultSet.getMetaData();
+ jdbcColumns = new ArrayList<>();
+
+ int columnsCount = meta.getColumnCount();
+
+ if (columns.size() != columnsCount) {
+ throw UserException
+ .validationError()
+ .message(
+ "Expected columns count differs from the returned one.\n" +
+ "Expected columns: %s\n" +
+ "Returned columns count: %s",
+ columns, columnsCount)
+ .addContext("Sql", sql)
+ .addContext(errorContext)
+ .build(logger);
+ }
+
+ for (int i = 1; i <= columnsCount; i++) {
+ String name = columns.get(i - 1).getRootSegmentPath();
+ // column index in ResultSetMetaData starts from 1
+ int jdbcType = meta.getColumnType(i);
+ int width = Math.min(meta.getPrecision(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericPrecision());
+ int scale = Math.min(meta.getScale(i), DRILL_REL_DATATYPE_SYSTEM.getMaxNumericScale());
+
+ MinorType minorType = JDBC_TYPE_MAPPINGS.get(jdbcType);
+ if (minorType == null) {
+ logger.warn("Ignoring column that is unsupported.", UserException
+ .unsupportedError()
+ .message(
+ "A column you queried has a data type that is not currently supported by the JDBC storage plugin. "
+ + "The column's name was %s and its JDBC data type was %s. ",
+ name,
+ nameFromType(jdbcType))
+ .addContext("Sql", sql)
+ .addContext("Column Name", name)
+ .addContext(errorContext)
+ .build(logger));
+ continue;
+ }
+
+ jdbcColumns.add(new JdbcColumn(name, minorType));
+ if (minorType == MinorType.VARDECIMAL) {
Review comment:
@vvysotskyi I see the importance with the `DECIMAL` types, but does it matter with the other types you mentioned? The binary type is determined by the length of the byte array it receives from the source system, so I'm not sure what difference it would make there. With `VARCHAR` would it affect the results in any way?
Consider if you have a MySQL column `foo` which is `VARCHAR(16)` When you query that via Drill, you'll get the 16 characters in the `VARCHAR` vector.
I know that It DOES make a difference in the `VARDECIMAL` because if you don't specify the precision and scale, you'll get different results.
--
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:
users@infra.apache.org