YDB FQ: support Oracle as an external data source (#6723)

9 files changed, 63 insertions, 6 deletions

diff --git a/ydb/core/external_sources/external_data_source.cpp b/ydb/core/external_sources/external_data_source.cpp
index d44c8ca6db..9de3908fc7 100644
--- a/ydb/core/external_sources/external_data_source.cpp
+++ b/ydb/core/external_sources/external_data_source.cpp
@@ -37,7 +37,7 @@ struct TExternalDataSource : public IExternalSource {
     }
 
     bool IsRDBMSDataSource(const TProtoStringType& sourceType) const {
-        return IsIn({"Greenplum", "PostgreSQL", "MySQL", "MsSQLServer", "Clickhouse"}, sourceType);
+        return IsIn({"Greenplum", "PostgreSQL", "MySQL", "MsSQLServer", "ClickHouse", "Oracle"}, sourceType);
     }
 
     virtual void ValidateExternalDataSource(const TString& externalDataSourceDescription) const override {
@@ -53,10 +53,15 @@ struct TExternalDataSource : public IExternalSource {
             ythrow TExternalSourceException() << "Unsupported property: " << key;
         }
 
-        if (IsRDBMSDataSource(proto.GetSourceType()) && !proto.GetProperties().GetProperties().contains("database_name")){
+        if (IsRDBMSDataSource(proto.GetSourceType()) && !proto.GetProperties().GetProperties().contains("database_name")) {
             ythrow TExternalSourceException() << proto.GetSourceType() << " source must provide database_name";
         }
 
+        // oracle must have property service_name
+        if (proto.GetSourceType() == "Oracle" && !proto.GetProperties().GetProperties().contains("service_name")) {
+            ythrow TExternalSourceException() << proto.GetSourceType() << " source must provide service_name";
+        }
+
         ValidateHostname(HostnamePatterns, proto.GetLocation());
     }
 
diff --git a/ydb/core/external_sources/external_source_factory.cpp b/ydb/core/external_sources/external_source_factory.cpp
index c0be11d62e..8c1e1c9bc9 100644
--- a/ydb/core/external_sources/external_source_factory.cpp
+++ b/ydb/core/external_sources/external_source_factory.cpp
@@ -70,6 +70,10 @@ IExternalSourceFactory::TPtr CreateExternalSourceFactory(const std::vector<TStri
         {
             ToString(NYql::EDatabaseType::MsSQLServer),
             CreateExternalDataSource(TString{NYql::GenericProviderName}, {"BASIC"}, {"database_name", "use_tls"}, hostnamePatternsRegEx)
+        },
+        {
+            ToString(NYql::EDatabaseType::Oracle),
+            CreateExternalDataSource(TString{NYql::GenericProviderName}, {"BASIC"}, {"database_name", "use_tls", "service_name"}, hostnamePatternsRegEx)
         }}); 
 }
 
diff --git a/ydb/core/kqp/gateway/behaviour/external_data_source/manager.cpp b/ydb/core/kqp/gateway/behaviour/external_data_source/manager.cpp
index 077db86859..d5aa96eaa2 100644
--- a/ydb/core/kqp/gateway/behaviour/external_data_source/manager.cpp
+++ b/ydb/core/kqp/gateway/behaviour/external_data_source/manager.cpp
@@ -76,6 +76,7 @@ void FillCreateExternalDataSourceDesc(NKikimrSchemeOp::TExternalDataSourceDescri
         "database_id", // managed YDB
         "use_tls",
         "schema", // managed PG
+        "service_name", // oracle
     };
 
     for (const auto& property: properties) {
diff --git a/ydb/library/yql/providers/common/db_id_async_resolver/db_async_resolver.h b/ydb/library/yql/providers/common/db_id_async_resolver/db_async_resolver.h
index fab14c1dda..129c05ee21 100644
--- a/ydb/library/yql/providers/common/db_id_async_resolver/db_async_resolver.h
+++ b/ydb/library/yql/providers/common/db_id_async_resolver/db_async_resolver.h
@@ -17,7 +17,8 @@ enum class EDatabaseType {
     YT,
     MySQL,
     Greenplum,
-    MsSQLServer
+    MsSQLServer,
+    Oracle
 };
 
 inline EDatabaseType DatabaseTypeFromDataSourceKind(NConnector::NApi::EDataSourceKind dataSourceKind) {
@@ -34,6 +35,8 @@ inline EDatabaseType DatabaseTypeFromDataSourceKind(NConnector::NApi::EDataSourc
             return EDatabaseType::Greenplum;
         case NConnector::NApi::EDataSourceKind::MS_SQL_SERVER:
           return EDatabaseType::MsSQLServer;
+        case NConnector::NApi::EDataSourceKind::ORACLE:
+          return EDatabaseType::Oracle;
         default:
             ythrow yexception() << "Unknown data source kind: " << NConnector::NApi::EDataSourceKind_Name(dataSourceKind);
     }
@@ -53,6 +56,8 @@ inline NConnector::NApi::EDataSourceKind DatabaseTypeToDataSourceKind(EDatabaseT
             return  NConnector::NApi::EDataSourceKind::GREENPLUM;
         case EDatabaseType::MsSQLServer:
             return NConnector::NApi::EDataSourceKind::MS_SQL_SERVER;
+        case EDatabaseType::Oracle:
+            return NConnector::NApi::EDataSourceKind::ORACLE;
         default:
             ythrow yexception() << "Unknown database type: " << ToString(databaseType);
     }
diff --git a/ydb/library/yql/providers/generic/actors/yql_generic_provider_factories.cpp b/ydb/library/yql/providers/generic/actors/yql_generic_provider_factories.cpp
index e9b2b8bf8b..e0a1caa9f7 100644
--- a/ydb/library/yql/providers/generic/actors/yql_generic_provider_factories.cpp
+++ b/ydb/library/yql/providers/generic/actors/yql_generic_provider_factories.cpp
@@ -32,7 +32,7 @@ namespace NYql::NDq {
                 args.MaxKeysInRequest);
         };
 
-        for (auto& name : {"ClickHouseGeneric", "PostgreSqlGeneric", "YdbGeneric", "MySqlGeneric", "GreenplumGeneric", "MsSQLServerGeneric"}) {
+        for (auto& name : {"ClickHouseGeneric", "PostgreSqlGeneric", "YdbGeneric", "MySqlGeneric", "GreenplumGeneric", "MsSQLServerGeneric", "OracleGeneric"}) {
             factory.RegisterSource<Generic::TSource>(name, readActorFactory);
             factory.RegisterLookupSource<Generic::TLookupSource>(name, lookupActorFactory);
         }
diff --git a/ydb/library/yql/providers/generic/connector/api/common/data_source.proto b/ydb/library/yql/providers/generic/connector/api/common/data_source.proto
index 7be6bd8d7b..692a26f491 100644
--- a/ydb/library/yql/providers/generic/connector/api/common/data_source.proto
+++ b/ydb/library/yql/providers/generic/connector/api/common/data_source.proto
@@ -99,6 +99,6 @@ message TDataSourceInstance {
         TClickhouseDataSourceOptions ch_options = 8;
         TS3DataSourceOptions s3_options = 9;
         TGreenplumDataSourceOptions gp_options = 10;
-        TOracleDataSourceOptions ora_options = 11;
+        TOracleDataSourceOptions oracle_options = 11;
     }
 }
diff --git a/ydb/library/yql/providers/generic/provider/yql_generic_cluster_config.cpp b/ydb/library/yql/providers/generic/provider/yql_generic_cluster_config.cpp
index 0c0e769184..dc74479e4d 100644
--- a/ydb/library/yql/providers/generic/provider/yql_generic_cluster_config.cpp
+++ b/ydb/library/yql/providers/generic/provider/yql_generic_cluster_config.cpp
@@ -139,6 +139,20 @@ namespace NYql {
         clusterConfig.mutable_datasourceoptions()->insert({TString("schema"), TString(it->second)});
     }
 
+    void ParseServiceName(const THashMap<TString, TString>& properties,
+                          NYql::TGenericClusterConfig& clusterConfig) {
+        auto it = properties.find("service_name");
+        if (it == properties.cend()) {
+            return;
+        }
+
+        if (!it->second) {
+            return;
+        }
+
+        clusterConfig.mutable_datasourceoptions()->insert({TString("service_name"), TString(it->second)});
+    }
+
     void ParseMdbClusterId(const THashMap<TString, TString>& properties,
                            NYql::TGenericClusterConfig& clusterConfig) {
         auto it = properties.find("mdb_cluster_id");
@@ -192,7 +206,7 @@ namespace NYql {
                        NYql::TGenericClusterConfig& clusterConfig) {
         using namespace NConnector::NApi;
 
-        if (IsIn({EDataSourceKind::GREENPLUM, EDataSourceKind::YDB, EDataSourceKind::MYSQL, EDataSourceKind::MS_SQL_SERVER}, clusterConfig.GetKind())) {
+        if (IsIn({EDataSourceKind::GREENPLUM, EDataSourceKind::YDB, EDataSourceKind::MYSQL, EDataSourceKind::MS_SQL_SERVER, EDataSourceKind::ORACLE}, clusterConfig.GetKind())) {
             clusterConfig.SetProtocol(EProtocol::NATIVE);
             return;
         }
@@ -268,6 +282,7 @@ namespace NYql {
         ParseUseTLS(properties, clusterConfig);
         ParseDatabaseName(properties, clusterConfig);
         ParseSchema(properties, clusterConfig);
+        ParseServiceName(properties, clusterConfig);
         ParseMdbClusterId(properties, clusterConfig);
         ParseDatabaseId(properties, clusterConfig);
         ParseSourceType(properties, clusterConfig);
@@ -396,6 +411,17 @@ namespace NYql {
             }
         }
 
+        // Oracle:
+        // * always set service_name for oracle;
+        if (clusterConfig.GetKind() == NConnector::NApi::ORACLE) {
+            if (!clusterConfig.GetDataSourceOptions().contains("service_name")) {
+                return ValidationError(
+                    clusterConfig,
+                    context,
+                    "For Oracle databases you must set service, but you have not set it");
+            }
+        }
+
         // check required fields
         if (!clusterConfig.GetName()) {
             return ValidationError(clusterConfig, context, "empty field 'Name'");
diff --git a/ydb/library/yql/providers/generic/provider/yql_generic_dq_integration.cpp b/ydb/library/yql/providers/generic/provider/yql_generic_dq_integration.cpp
index 19d8a5694d..8954937a78 100644
--- a/ydb/library/yql/providers/generic/provider/yql_generic_dq_integration.cpp
+++ b/ydb/library/yql/providers/generic/provider/yql_generic_dq_integration.cpp
@@ -35,6 +35,8 @@ namespace NYql {
                     return "GreenplumGeneric";
                 case NYql::NConnector::NApi::MS_SQL_SERVER:
                     return "MsSQLServerGeneric";
+                case NYql::NConnector::NApi::ORACLE:
+                    return "OracleGeneric";
                 default:
                     ythrow yexception() << "Data source kind is unknown or not specified";
             }
@@ -214,6 +216,9 @@ namespace NYql {
                         case NConnector::NApi::MS_SQL_SERVER:
                             properties["SourceType"] = "MsSQLServer";
                             break;
+                        case NConnector::NApi::ORACLE:
+                            properties["SourceType"] = "Oracle";
+                            break;
                         case NConnector::NApi::DATA_SOURCE_KIND_UNSPECIFIED:
                             break;
                         default:
diff --git a/ydb/library/yql/providers/generic/provider/yql_generic_load_meta.cpp b/ydb/library/yql/providers/generic/provider/yql_generic_load_meta.cpp
index a5becd6add..341568e82b 100644
--- a/ydb/library/yql/providers/generic/provider/yql_generic_load_meta.cpp
+++ b/ydb/library/yql/providers/generic/provider/yql_generic_load_meta.cpp
@@ -327,6 +327,13 @@ namespace NYql {
             request.set_schema(schema);
         }
 
+        void GetServiceName(NYql::NConnector::NApi::TOracleDataSourceOptions& request, const TGenericClusterConfig& clusterConfig) {
+            const auto it = clusterConfig.GetDataSourceOptions().find("service_name");
+            if (it != clusterConfig.GetDataSourceOptions().end()) {
+                request.set_service_name(it->second);
+            }
+        }
+
         void FillDataSourceOptions(NConnector::NApi::TDescribeTableRequest& request, const TGenericClusterConfig& clusterConfig) {
             const auto dataSourceKind = clusterConfig.GetKind();
             switch (dataSourceKind) {
@@ -346,6 +353,10 @@ namespace NYql {
                     auto* options = request.mutable_data_source_instance()->mutable_pg_options();
                     SetSchema(*options, clusterConfig);
                 } break;
+                case NYql::NConnector::NApi::ORACLE: {
+                    auto* options = request.mutable_data_source_instance()->mutable_oracle_options();
+                    GetServiceName(*options, clusterConfig);
+                } break;
 
                 default:
                     ythrow yexception() << "Unexpected data source kind: '" << NYql::NConnector::NApi::EDataSourceKind_Name(dataSourceKind)