This is an automated email from the ASF dual-hosted git repository.
ngangam pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 21dd8471f46 HIVE-25091: Implemented connector provider for MSSQL and
Oracle (#3167) (Saihemanth Gantasale via Naveen Gangam)
21dd8471f46 is described below
commit 21dd8471f4675ec1206b6043119c861ce3b38823
Author: Sai Hemanth Gantasala
<[email protected]>
AuthorDate: Wed Apr 27 20:43:43 2022 -0700
HIVE-25091: Implemented connector provider for MSSQL and Oracle (#3167)
(Saihemanth Gantasale via Naveen Gangam)
---
.../JDBCConnectorProviderFactory.java | 10 +++
.../jdbc/AbstractJDBCConnectorProvider.java | 12 ++-
.../jdbc/DerbySQLConnectorProvider.java | 8 ++
.../dataconnector/jdbc/MSSQLConnectorProvider.java | 96 +++++++++++++++++++++
.../dataconnector/jdbc/MySQLConnectorProvider.java | 8 ++
.../jdbc/OracleConnectorProvider.java | 99 ++++++++++++++++++++++
.../jdbc/PostgreSQLConnectorProvider.java | 8 ++
7 files changed, 238 insertions(+), 3 deletions(-)
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/JDBCConnectorProviderFactory.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/JDBCConnectorProviderFactory.java
index e59f1a6687d..60589e51844 100644
---
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/JDBCConnectorProviderFactory.java
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/JDBCConnectorProviderFactory.java
@@ -22,6 +22,8 @@ import org.apache.hadoop.hive.metastore.api.DataConnector;
import
org.apache.hadoop.hive.metastore.dataconnector.jdbc.DerbySQLConnectorProvider;
import
org.apache.hadoop.hive.metastore.dataconnector.jdbc.MySQLConnectorProvider;
import
org.apache.hadoop.hive.metastore.dataconnector.jdbc.PostgreSQLConnectorProvider;
+import
org.apache.hadoop.hive.metastore.dataconnector.jdbc.OracleConnectorProvider;
+import
org.apache.hadoop.hive.metastore.dataconnector.jdbc.MSSQLConnectorProvider;
import static
org.apache.hadoop.hive.metastore.dataconnector.IDataConnectorProvider.*;
@@ -41,6 +43,14 @@ public class JDBCConnectorProviderFactory {
provider = new DerbySQLConnectorProvider(dbName, connector);
break;
+ case ORACLE_TYPE:
+ provider = new OracleConnectorProvider(dbName, connector);
+ break;
+
+ case MSSQL_TYPE:
+ provider = new MSSQLConnectorProvider(dbName, connector);
+ break;
+
default:
throw new RuntimeException("Unsupported JDBC type");
}
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/AbstractJDBCConnectorProvider.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/AbstractJDBCConnectorProvider.java
index d60cea50c37..09d0784b929 100644
---
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/AbstractJDBCConnectorProvider.java
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/AbstractJDBCConnectorProvider.java
@@ -207,6 +207,10 @@ public abstract class AbstractJDBCConnectorProvider
extends AbstractDataConnecto
protected abstract ResultSet fetchTableNames() throws MetaException;
+ protected abstract String getCatalogName();
+
+ protected abstract String getDatabaseName();
+
/**
* Fetch a single table with the given name, returns a Hive Table object
from the remote database
* @return Table A Table object for the matching table, null otherwise.
@@ -220,6 +224,7 @@ public abstract class AbstractJDBCConnectorProvider extends
AbstractDataConnecto
rs = fetchColumnsViaDBMetaData(tableName);
List<FieldSchema> cols = new ArrayList<>();
while (rs.next()) {
+ String typename = rs.getString("TYPE_NAME");
FieldSchema fs = new FieldSchema();
fs.setName(rs.getString("COLUMN_NAME"));
fs.setType(getDataType(rs.getString("TYPE_NAME"),
rs.getInt("COLUMN_SIZE")));
@@ -236,7 +241,7 @@ public abstract class AbstractJDBCConnectorProvider extends
AbstractDataConnecto
//Setting the table properties.
table.getParameters().put(JDBC_DATABASE_TYPE, this.type);
table.getParameters().put(JDBC_DRIVER, this.driverClassName);
- table.getParameters().put(JDBC_TABLE, tableName);
+ table.getParameters().put(JDBC_TABLE, scoped_db+"."+tableName);
table.getParameters().put(JDBC_URL, this.jdbcUrl);
table.getParameters().put(hive_metastoreConstants.META_TABLE_STORAGE,
JDBC_HIVE_STORAGE_HANDLER_ID);
table.getParameters().put("EXTERNAL", "TRUE");
@@ -263,7 +268,7 @@ public abstract class AbstractJDBCConnectorProvider extends
AbstractDataConnecto
private ResultSet fetchTablesViaDBMetaData(String regex) throws SQLException
{
ResultSet rs = null;
try {
- rs = getConnection().getMetaData().getTables(scoped_db, null, regex, new
String[] { "TABLE" });
+ rs = getConnection().getMetaData().getTables(getCatalogName(),
getDatabaseName(), regex, new String[]{"TABLE"});
} catch (SQLException sqle) {
LOG.warn("Could not retrieve tables from JDBC table, cause:" +
sqle.getMessage());
throw sqle;
@@ -274,7 +279,7 @@ public abstract class AbstractJDBCConnectorProvider extends
AbstractDataConnecto
private ResultSet fetchColumnsViaDBMetaData(String tableName) throws
SQLException {
ResultSet rs = null;
try {
- rs = getConnection().getMetaData().getColumns(scoped_db, null,
tableName, null);
+ rs = getConnection().getMetaData().getColumns(getCatalogName(),
getDatabaseName(), tableName, null);
} catch (SQLException sqle) {
LOG.warn("Could not retrieve columns from JDBC table, cause:" +
sqle.getMessage());
throw sqle;
@@ -308,6 +313,7 @@ public abstract class AbstractJDBCConnectorProvider extends
AbstractDataConnecto
case "mediumblob":
case "longblob":
case "bytea":
+ case "binary":
return ColumnType.BINARY_TYPE_NAME;
case "tinyint":
return ColumnType.TINYINT_TYPE_NAME;
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/DerbySQLConnectorProvider.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/DerbySQLConnectorProvider.java
index f2ef01044e2..897f2b73b46 100644
---
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/DerbySQLConnectorProvider.java
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/DerbySQLConnectorProvider.java
@@ -65,6 +65,14 @@ public class DerbySQLConnectorProvider extends
AbstractJDBCConnectorProvider {
return null;
}
+ @Override protected String getCatalogName() {
+ return scoped_db;
+ }
+
+ @Override protected String getDatabaseName() {
+ return null;
+ }
+
protected String getDataType(String dbDataType, int size) {
String mappedType = super.getDataType(dbDataType, size);
if (!mappedType.equalsIgnoreCase(ColumnType.VOID_TYPE_NAME)) {
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MSSQLConnectorProvider.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MSSQLConnectorProvider.java
new file mode 100644
index 00000000000..d678cc0bf45
--- /dev/null
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MSSQLConnectorProvider.java
@@ -0,0 +1,96 @@
+/*
+ * 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.hadoop.hive.metastore.dataconnector.jdbc;
+
+import org.apache.hadoop.hive.metastore.ColumnType;
+import org.apache.hadoop.hive.metastore.api.DataConnector;
+import org.apache.hadoop.hive.metastore.api.MetaException;
+import org.apache.hadoop.hive.metastore.api.Table;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+public class MSSQLConnectorProvider extends AbstractJDBCConnectorProvider {
+ private static Logger LOG =
LoggerFactory.getLogger(MSSQLConnectorProvider.class);
+ private static final String DRIVER_CLASS =
"com.microsoft.sqlserver.jdbc.SQLServerDriver".intern();
+
+ public MSSQLConnectorProvider(String dbName, DataConnector dataConn) {
+ super(dbName, dataConn, DRIVER_CLASS);
+ driverClassName = DRIVER_CLASS;
+ }
+
+ @Override protected ResultSet fetchTableMetadata(String tableName) throws
MetaException {
+ ResultSet rs = null;
+ try {
+ rs = getConnection().getMetaData().getColumns(null, scoped_db,
tableName, null);
+ } catch (Exception ex) {
+ LOG.warn("Could not retrieve table names from remote datasource,
cause:" + ex.getMessage());
+ throw new MetaException("Could not retrieve table names from
remote datasource, cause:" + ex);
+ }
+ return rs;
+ }
+
+ @Override protected ResultSet fetchTableNames() throws MetaException {
+ ResultSet rs = null;
+ try {
+ rs = getConnection().getMetaData().getTables(null, scoped_db,
null, new String[] { "TABLE" });
+ } catch (SQLException sqle) {
+ LOG.warn("Could not retrieve table names from remote datasource,
cause:" + sqle.getMessage());
+ throw new MetaException("Could not retrieve table names from
remote datasource, cause:" + sqle);
+ }
+ return rs;
+ }
+
+ @Override protected String getCatalogName() {
+ return null;
+ }
+
+ @Override protected String getDatabaseName() {
+ return scoped_db;
+ }
+
+ protected String getDataType(String dbDataType, int size) {
+ String mappedType = super.getDataType(dbDataType, size);
+ if (!mappedType.equalsIgnoreCase(ColumnType.VOID_TYPE_NAME)) {
+ return mappedType;
+ }
+
+ // map any db specific types here.
+ switch (dbDataType.toLowerCase())
+ {
+ case "nvarchar":
+ case "nchar":
+ mappedType = ColumnType.VARCHAR_TYPE_NAME + wrapSize(size);
+ break;
+ case "bit":
+ mappedType = ColumnType.BOOLEAN_TYPE_NAME;
+ break;
+ case "number":
+ mappedType = ColumnType.INT_TYPE_NAME;
+ break;
+ default:
+ mappedType = ColumnType.VOID_TYPE_NAME;
+ }
+ return mappedType;
+ }
+}
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MySQLConnectorProvider.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MySQLConnectorProvider.java
index 6bb10b97b41..87430c29862 100644
---
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MySQLConnectorProvider.java
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/MySQLConnectorProvider.java
@@ -73,6 +73,14 @@ public class MySQLConnectorProvider extends
AbstractJDBCConnectorProvider {
}
}
+ @Override protected String getCatalogName() {
+ return scoped_db;
+ }
+
+ @Override protected String getDatabaseName() {
+ return null;
+ }
+
protected String getDataType(String dbDataType, int size) {
String mappedType = super.getDataType(dbDataType, size);
if (!mappedType.equalsIgnoreCase(ColumnType.VOID_TYPE_NAME)) {
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/OracleConnectorProvider.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/OracleConnectorProvider.java
new file mode 100644
index 00000000000..78d537cb65b
--- /dev/null
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/OracleConnectorProvider.java
@@ -0,0 +1,99 @@
+/*
+ * 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.hadoop.hive.metastore.dataconnector.jdbc;
+
+import org.apache.hadoop.hive.metastore.ColumnType;
+import org.apache.hadoop.hive.metastore.api.DataConnector;
+import org.apache.hadoop.hive.metastore.api.MetaException;
+import org.apache.hadoop.hive.metastore.api.Table;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.List;
+import java.util.ArrayList;
+
+public class OracleConnectorProvider extends AbstractJDBCConnectorProvider {
+ private static Logger LOG =
LoggerFactory.getLogger(OracleConnectorProvider.class);
+ private static final String DRIVER_CLASS =
"oracle.jdbc.driver.OracleDriver".intern();
+
+ public OracleConnectorProvider(String dbName, DataConnector dataConn) {
+ super(dbName, dataConn, DRIVER_CLASS);
+ driverClassName = DRIVER_CLASS;
+ }
+
+ @Override protected ResultSet fetchTableMetadata(String tableName) throws
MetaException {
+ ResultSet rs = null;
+ try {
+ rs = getConnection().getMetaData().getColumns(null, scoped_db,
tableName, null);
+ } catch (Exception ex) {
+ LOG.warn("Could not retrieve table names from remote datasource,
cause:" + ex.getMessage());
+ throw new MetaException("Could not retrieve table names from
remote datasource, cause:" + ex);
+ }
+ return rs;
+ }
+
+ @Override protected ResultSet fetchTableNames() throws MetaException {
+ ResultSet rs = null;
+ try {
+ rs = getConnection().getMetaData().getTables(null, scoped_db,
null, new String[] { "TABLE" });
+ } catch (SQLException sqle) {
+ LOG.warn("Could not retrieve table names from remote datasource,
cause:" + sqle.getMessage());
+ throw new MetaException("Could not retrieve table names from
remote datasource, cause:" + sqle);
+ }
+ return rs;
+ }
+
+ @Override protected String getCatalogName() {
+ return null;
+ }
+
+ @Override protected String getDatabaseName() {
+ return scoped_db;
+ }
+
+ protected String getDataType(String dbDataType, int size) {
+ String mappedType = super.getDataType(dbDataType, size);
+ if (!mappedType.equalsIgnoreCase(ColumnType.VOID_TYPE_NAME)) {
+ return mappedType;
+ }
+
+ // map any db specific types here.
+ //TODO: Large Objects (LOB), Interval data types of oracle needs to be
supported.
+ switch (dbDataType.toLowerCase())
+ {
+ case "varchar2":
+ case "nchar":
+ case "nvarchar2":
+ mappedType = ColumnType.VARCHAR_TYPE_NAME + wrapSize(size);
+ break;
+ case "raw":
+ case "long raw":
+ mappedType = ColumnType.STRING_TYPE_NAME;
+ break;
+ case "number":
+ mappedType = ColumnType.INT_TYPE_NAME;
+ break;
+ default:
+ mappedType = ColumnType.VOID_TYPE_NAME;
+ }
+ return mappedType;
+ }
+}
diff --git
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/PostgreSQLConnectorProvider.java
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/PostgreSQLConnectorProvider.java
index b9214f562bd..047932e111a 100644
---
a/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/PostgreSQLConnectorProvider.java
+++
b/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/dataconnector/jdbc/PostgreSQLConnectorProvider.java
@@ -50,6 +50,14 @@ public class PostgreSQLConnectorProvider extends
AbstractJDBCConnectorProvider {
return null;
}
+ @Override protected String getCatalogName() {
+ return scoped_db;
+ }
+
+ @Override protected String getDatabaseName() {
+ return null;
+ }
+
protected String getDataType(String dbDataType, int size) {
String mappedType = super.getDataType(dbDataType, size);
if (!mappedType.equalsIgnoreCase(ColumnType.VOID_TYPE_NAME)) {
