This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch dev-1.1.2
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/dev-1.1.2 by this push:
new 9516a12644 [improvement](odbc) support more ODBC Connection Parameters
for odbc external table (#9198)
9516a12644 is described below
commit 9516a1264436298258e1bcafd498805e765db106
Author: Zhengguo Yang <[email protected]>
AuthorDate: Thu May 5 20:45:13 2022 +0800
[improvement](odbc) support more ODBC Connection Parameters for odbc
external table (#9198)
user can add more supported ODBC Connection Parameters to resource
PROPERTIES or external table PROPERTIES
---
.../java/org/apache/doris/catalog/MysqlTable.java | 2 +-
.../apache/doris/catalog/OdbcCatalogResource.java | 34 +++++++-
.../java/org/apache/doris/catalog/OdbcTable.java | 95 ++++++++++++++++++----
.../java/org/apache/doris/catalog/Resource.java | 2 +
.../org/apache/doris/catalog/SparkResource.java | 5 ++
.../doris/catalog/OdbcCatalogResourceTest.java | 21 +++--
6 files changed, 132 insertions(+), 27 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/MysqlTable.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/MysqlTable.java
index 05617e878d..8f3fa4522f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/MysqlTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/MysqlTable.java
@@ -146,7 +146,7 @@ public class MysqlTable extends Table {
throw new RuntimeException("Resource does not exist. name: " +
odbcCatalogResourceName);
}
- String property = odbcCatalogResource.getProperties(propertyName);
+ String property = odbcCatalogResource.getProperty(propertyName);
if (property == null) {
throw new RuntimeException("The property:" + propertyName + " do
not set in resource " + odbcCatalogResourceName);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcCatalogResource.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcCatalogResource.java
index 9020cd8206..0b0384c5a4 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcCatalogResource.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcCatalogResource.java
@@ -90,10 +90,42 @@ public class OdbcCatalogResource extends Resource {
if (value == null) {
throw new DdlException("Missing " + propertiesKey + " in
properties");
}
+ }
+ @Override
+ public void modifyProperties(Map<String, String> properties) throws
DdlException {
+ // modify properties
+ replaceIfEffectiveValue(this.configs, HOST, properties.get(HOST));
+ replaceIfEffectiveValue(this.configs, PORT, properties.get(PORT));
+ replaceIfEffectiveValue(this.configs, USER, properties.get(USER));
+ replaceIfEffectiveValue(this.configs, PASSWORD,
properties.get(PASSWORD));
+ replaceIfEffectiveValue(this.configs, TYPE, properties.get(TYPE));
+ replaceIfEffectiveValue(this.configs, DRIVER, properties.get(DRIVER));
+ }
+
+ @Override
+ public void checkProperties(Map<String, String> properties) throws
AnalysisException {
+ Map<String, String> copiedProperties = Maps.newHashMap(properties);
+ // check properties
+ copiedProperties.remove(HOST);
+ copiedProperties.remove(PORT);
+ copiedProperties.remove(USER);
+ copiedProperties.remove(PASSWORD);
+ copiedProperties.remove(TYPE);
+ copiedProperties.remove(DRIVER);
+
+ if (!copiedProperties.isEmpty()) {
+ throw new AnalysisException("Unknown ODBC catalog resource
properties: " + copiedProperties);
+ }
+ }
+
+ @Override
+ public Map<String, String> getCopiedProperties() {
+ Map<String, String> copiedProperties = Maps.newHashMap(configs);
+ return copiedProperties;
}
- public String getProperties(String propertiesKey) {
+ public String getProperty(String propertiesKey) {
// check the properties key
String value = configs.get(propertiesKey);
return value;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
index 9283555901..b1efecdd52 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
@@ -43,6 +43,8 @@ import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import static java.util.stream.Collectors.joining;
+
public class OdbcTable extends Table {
private static final Logger LOG = LogManager.getLogger(OlapTable.class);
@@ -55,6 +57,9 @@ public class OdbcTable extends Table {
public static final String ODBC_TABLE = "table";
public static final String ODBC_DRIVER = "driver";
public static final String ODBC_TYPE = "odbc_type";
+ public static final String ODBC_CHARSET = "charset";
+ public static final String ODBC_EXTRA_PARAM = "extra_param";
+
// map now odbc external table Doris support now
private static Map<String, TOdbcTableType> TABLE_TYPE_MAP;
@@ -72,7 +77,7 @@ public class OdbcTable extends Table {
private static String mysqlProperName(String name) {
return "`" + name + "`";
}
-
+
public static String databaseProperName(TOdbcTableType tableType, String
name) {
switch (tableType) {
case MYSQL:
@@ -90,6 +95,9 @@ public class OdbcTable extends Table {
private String odbcTableName;
private String driver;
private String odbcTableTypeName;
+ private String charset;
+ private String extraParam;
+ private Map<String, String> resourceProperties;
public OdbcTable() {
super(TableType.ODBC);
@@ -107,7 +115,6 @@ public class OdbcTable extends Table {
+ "they are: odbc_catalog_resource or [host, port, user,
password, driver, odbc_type]" +
" and database and table");
}
-
if (properties.containsKey(ODBC_CATALOG_RESOURCE)) {
odbcCatalogResourceName = properties.get(ODBC_CATALOG_RESOURCE);
@@ -125,13 +132,26 @@ public class OdbcTable extends Table {
+ "'@'" + ConnectContext.get().getRemoteIP()
+ "' for resource '" + odbcCatalogResourceName + "'");
}
+ resourceProperties = new
HashMap<>(oriResource.getCopiedProperties());
+ resourceProperties.remove(ODBC_HOST);
+ resourceProperties.remove(ODBC_PORT);
+ resourceProperties.remove(ODBC_USER);
+ resourceProperties.remove(ODBC_PASSWORD);
+ resourceProperties.remove(ODBC_DRIVER);
+ resourceProperties.remove(ODBC_CHARSET);
+ resourceProperties.remove(ODBC_TYPE);
+ resourceProperties.remove("type");
+ resourceProperties.remove(ODBC_DATABASE);
} else {
+ Map<String, String> copiedProperties = new HashMap<>();
+ copiedProperties.putAll(properties);
// Set up
host = properties.get(ODBC_HOST);
if (Strings.isNullOrEmpty(host)) {
throw new DdlException("Host of Odbc table is null. "
+ "Please set proper resource or add
properties('host'='xxx.xxx.xxx.xxx') when create table");
}
+ copiedProperties.remove(ODBC_HOST);
port = properties.get(ODBC_PORT);
if (Strings.isNullOrEmpty(port)) {
@@ -147,25 +167,33 @@ public class OdbcTable extends Table {
}
}
+ copiedProperties.remove(ODBC_PORT);
userName = properties.get(ODBC_USER);
if (Strings.isNullOrEmpty(userName)) {
throw new DdlException("User of Odbc table is null. "
+ "Please set odbc_catalog_resource or add
properties('user'='root') when create table");
}
+ copiedProperties.remove(ODBC_USER);
passwd = properties.get(ODBC_PASSWORD);
if (passwd == null) {
throw new DdlException("Password of Odbc table is null. "
+ "Please set odbc_catalog_resource or add
properties('password'='xxxx') when create table");
}
+ copiedProperties.remove(ODBC_PASSWORD);
driver = properties.get(ODBC_DRIVER);
if (Strings.isNullOrEmpty(driver)) {
throw new DdlException("Driver of Odbc table is null. "
+ "Please set odbc_catalog_resource or add
properties('diver'='xxxx') when create table");
}
+ copiedProperties.remove(ODBC_DRIVER);
+
+ charset = properties.get(ODBC_CHARSET);
+ copiedProperties.remove(ODBC_CHARSET);
+
String tableType = properties.get(ODBC_TYPE);
if (Strings.isNullOrEmpty(tableType)) {
throw new DdlException("Type of Odbc table is null. "
@@ -177,6 +205,10 @@ public class OdbcTable extends Table {
+ " Now Odbc table type only support:" +
supportTableType());
}
}
+ copiedProperties.remove(ODBC_TYPE);
+ copiedProperties.remove(ODBC_DATABASE);
+ copiedProperties.remove(ODBC_TABLE);
+ extraParam = getExtraParameter(copiedProperties);
}
odbcDatabaseName = properties.get(ODBC_DATABASE);
@@ -187,7 +219,7 @@ public class OdbcTable extends Table {
odbcTableName = properties.get(ODBC_TABLE);
if (Strings.isNullOrEmpty(odbcTableName)) {
- throw new DdlException("Database of Odbc table is null. "
+ throw new DdlException("Table of Odbc table is null. "
+ "Please add properties('table'='xxxx') when create
table");
}
}
@@ -199,13 +231,28 @@ public class OdbcTable extends Table {
throw new RuntimeException("Resource does not exist. name: " +
odbcCatalogResourceName);
}
- String property = odbcCatalogResource.getProperties(propertyName);
+ String property = odbcCatalogResource.getProperty(propertyName);
if (property == null) {
throw new RuntimeException("The property:" + propertyName + " do
not set in resource " + odbcCatalogResourceName);
}
return property;
}
+ public String getExtraParameter(Map<String, String> extraMap) {
+ if (extraMap == null || extraMap.isEmpty()) {
+ return "";
+ }
+ return ";" + extraMap.entrySet()
+ .stream()
+ .map(e -> e.getKey() + "=" + e.getValue())
+ .collect(joining(";"));
+ }
+ public String getExtraParam() {
+ if (extraParam != null) {
+ return extraParam;
+ }
+ return getExtraParameter(resourceProperties);
+ }
public String getOdbcCatalogResourceName() {
return odbcCatalogResourceName;
}
@@ -253,6 +300,20 @@ public class OdbcTable extends Table {
return getPropertyFromResource(ODBC_DRIVER);
}
+ public String getCharset() {
+ if (charset != null) {
+ return charset;
+ }
+ String resourceCharset = "utf8";
+ try {
+ resourceCharset = getPropertyFromResource(ODBC_CHARSET);
+ } catch (Exception e) {
+ LOG.info(e.getMessage());
+ }
+
+ return resourceCharset;
+ }
+
public String getOdbcTableTypeName() {
if (odbcTableTypeName != null) {
return odbcTableTypeName;
@@ -273,7 +334,7 @@ public class OdbcTable extends Table {
getOdbcDatabaseName(),
getUserName(),
getPasswd(),
- "utf8");
+ getCharset());
break;
case POSTGRESQL:
connectString =
String.format("Driver=%s;Server=%s;Port=%s;DataBase=%s;Uid=%s;Pwd=%s;charset=%s;UseDeclareFetch=1;Fetch=4096",
@@ -283,7 +344,7 @@ public class OdbcTable extends Table {
getOdbcDatabaseName(),
getUserName(),
getPasswd(),
- "utf8");
+ getCharset());
break;
case MYSQL:
connectString =
String.format("Driver=%s;Server=%s;Port=%s;DataBase=%s;Uid=%s;Pwd=%s;charset=%s;forward_cursor=1;no_cache=1",
@@ -293,20 +354,20 @@ public class OdbcTable extends Table {
getOdbcDatabaseName(),
getUserName(),
getPasswd(),
- "utf8");
+ getCharset());
break;
case SQLSERVER:
connectString =
String.format("Driver=%s;Server=%s,%s;DataBase=%s;Uid=%s;Pwd=%s",
- getOdbcDriver(),
- getHost(),
- getPort(),
- getOdbcDatabaseName(),
- getUserName(),
- getPasswd());
+ getOdbcDriver(),
+ getHost(),
+ getPort(),
+ getOdbcDatabaseName(),
+ getUserName(),
+ getPasswd());
break;
default:
}
- return connectString;
+ return connectString + getExtraParam();
}
public TOdbcTableType getOdbcTableType() {
@@ -361,6 +422,8 @@ public class OdbcTable extends Table {
sb.append(passwd);
sb.append(driver);
sb.append(odbcTableTypeName);
+ sb.append(charset);
+ sb.append(extraParam);
}
String md5 = DigestUtils.md5Hex(sb.toString());
LOG.debug("get signature of odbc table {}: {}. signature string: {}",
name, md5, sb.toString());
@@ -382,6 +445,8 @@ public class OdbcTable extends Table {
serializeMap.put(ODBC_TABLE, odbcTableName);
serializeMap.put(ODBC_DRIVER, driver);
serializeMap.put(ODBC_TYPE, odbcTableTypeName);
+ serializeMap.put(ODBC_CHARSET, charset);
+ serializeMap.put(ODBC_EXTRA_PARAM, extraParam);
int size = (int) serializeMap.values().stream().filter(v -> {
return v != null;
@@ -417,6 +482,8 @@ public class OdbcTable extends Table {
odbcTableName = serializeMap.get(ODBC_TABLE);
driver = serializeMap.get(ODBC_DRIVER);
odbcTableTypeName = serializeMap.get(ODBC_TYPE);
+ charset = serializeMap.get(ODBC_CHARSET);
+ extraParam = serializeMap.get(ODBC_EXTRA_PARAM);
}
public static String supportTableType() {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/Resource.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/Resource.java
index 96a1ec6a6d..252c3c4d56 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/Resource.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/Resource.java
@@ -98,6 +98,8 @@ public abstract class Resource implements Writable {
*/
protected abstract void setProperties(Map<String, String> properties)
throws DdlException;
+
+ public abstract Map<String, String> getCopiedProperties();
/**
* Fill BaseProcResult with different properties in child resources
* ResourceMgr.RESOURCE_PROC_NODE_TITLE_NAMES format:
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/SparkResource.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/SparkResource.java
index 0bda48bf47..8940c30d46 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/SparkResource.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/SparkResource.java
@@ -157,6 +157,11 @@ public class SparkResource extends Resource {
return new SparkResource(name, Maps.newHashMap(sparkConfigs),
workingDir, broker, brokerProperties);
}
+ @Override
+ public Map<String, String> getCopiedProperties() {
+ Map<String, String> copiedProperties = Maps.newHashMap(sparkConfigs);
+ return copiedProperties;
+ }
// Each SparkResource has and only has one SparkRepository.
// This method get the remote archive which matches the dpp version from
remote repository
public synchronized SparkRepository.SparkArchive prepareArchive() throws
LoadException {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/catalog/OdbcCatalogResourceTest.java
b/fe/fe-core/src/test/java/org/apache/doris/catalog/OdbcCatalogResourceTest.java
index 91dbb40ebe..dbc5f666cf 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/catalog/OdbcCatalogResourceTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/catalog/OdbcCatalogResourceTest.java
@@ -27,7 +27,6 @@ import org.apache.doris.common.proc.BaseProcResult;
import org.apache.doris.meta.MetaContext;
import org.apache.doris.mysql.privilege.PaloAuth;
import org.apache.doris.mysql.privilege.PrivPredicate;
-import org.apache.doris.persist.DropInfo;
import org.apache.doris.qe.ConnectContext;
import com.google.common.collect.Maps;
@@ -92,10 +91,10 @@ public class OdbcCatalogResourceTest {
OdbcCatalogResource resource = (OdbcCatalogResource)
Resource.fromStmt(stmt);
Assert.assertEquals(name, resource.getName());
Assert.assertEquals(type, resource.getType().name().toLowerCase());
- Assert.assertEquals(host, resource.getProperties("host"));
- Assert.assertEquals(port, resource.getProperties("port"));
- Assert.assertEquals(user, resource.getProperties("user"));
- Assert.assertEquals(passwd, resource.getProperties("password"));
+ Assert.assertEquals(host, resource.getProperty("host"));
+ Assert.assertEquals(port, resource.getProperty("port"));
+ Assert.assertEquals(user, resource.getProperty("user"));
+ Assert.assertEquals(passwd, resource.getProperty("password"));
// with driver and odbc_type
properties.put("driver", "mysql");
@@ -103,8 +102,8 @@ public class OdbcCatalogResourceTest {
stmt = new CreateResourceStmt(true, name, properties);
stmt.analyze(analyzer);
resource = (OdbcCatalogResource) Resource.fromStmt(stmt);
- Assert.assertEquals("mysql", resource.getProperties("driver"));
- Assert.assertEquals("mysql", resource.getProperties("odbc_type"));
+ Assert.assertEquals("mysql", resource.getProperty("driver"));
+ Assert.assertEquals("mysql", resource.getProperty("odbc_type"));
// test getProcNodeData
BaseProcResult result = new BaseProcResult();
@@ -147,10 +146,10 @@ public class OdbcCatalogResourceTest {
Assert.assertEquals("odbc1", rOdbcCatalogResource1.getName());
Assert.assertEquals("odbc2", rOdbcCatalogResource2.getName());
- Assert.assertEquals(rOdbcCatalogResource2.getProperties("host"),
"host");
- Assert.assertEquals(rOdbcCatalogResource2.getProperties("port"),
"port");
- Assert.assertEquals(rOdbcCatalogResource2.getProperties("user"),
"user");
- Assert.assertEquals(rOdbcCatalogResource2.getProperties("password"),
"password");
+ Assert.assertEquals(rOdbcCatalogResource2.getProperty("host"), "host");
+ Assert.assertEquals(rOdbcCatalogResource2.getProperty("port"), "port");
+ Assert.assertEquals(rOdbcCatalogResource2.getProperty("user"), "user");
+ Assert.assertEquals(rOdbcCatalogResource2.getProperty("password"),
"password");
// 3. delete files
dis.close();
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
