This is an automated email from the ASF dual-hosted git repository.
villebro pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git
The following commit(s) were added to refs/heads/master by this push:
new ec5bbaa678 feat: add connector for CouchbaseDB (#29225)
ec5bbaa678 is described below
commit ec5bbaa6787829909227dc969d03c3f4b58c9aa7
Author: Ayush Tripathi <[email protected]>
AuthorDate: Wed Jul 10 14:24:36 2024 +0530
feat: add connector for CouchbaseDB (#29225)
Co-authored-by: ayush-couchbase <[email protected]>
---
docs/docs/configuration/databases.mdx | 17 ++
docs/src/resources/data.js | 5 +
docs/static/img/databases/couchbase.svg | 19 ++
superset-frontend/src/assets/images/couchbase.svg | 19 ++
superset/db_engine_specs/couchbasedb.py | 257 +++++++++++++++++++++
superset/sql_parse.py | 1 +
tests/unit_tests/db_engine_specs/test_couchbase.py | 93 ++++++++
7 files changed, 411 insertions(+)
diff --git a/docs/docs/configuration/databases.mdx
b/docs/docs/configuration/databases.mdx
index 5681acc14d..67734643b9 100644
--- a/docs/docs/configuration/databases.mdx
+++ b/docs/docs/configuration/databases.mdx
@@ -54,6 +54,7 @@ are compatible with Superset.
| [Azure MS SQL](/docs/configuration/databases#sql-server) |
`pip install pymssql`
|
`mssql+pymssql://UserName@presetSQL:[email protected]:1433/TestSchema`
|
| [ClickHouse](/docs/configuration/databases#clickhouse) |
`pip install clickhouse-connect`
| `clickhousedb://{username}:{password}@{hostname}:{port}/{database}`
|
| [CockroachDB](/docs/configuration/databases#cockroachdb) |
`pip install cockroachdb`
| `cockroachdb://root@{hostname}:{port}/{database}?sslmode=disable`
|
+| [CouchbaseDB](/docs/configuration/databases#couchbaseDB) |
`pip install couchbase-sqlalchemy`
|
`couchbasedb://{username}:{password}@{hostname}:{port}?truststorepath={ssl
certificate path}`
|
| [Dremio](/docs/configuration/databases#dremio) |
`pip install sqlalchemy_dremio`
| `dremio://user:pwd@host:31010/`
|
| [Elasticsearch](/docs/configuration/databases#elasticsearch) |
`pip install elasticsearch-dbapi`
| `elasticsearch+http://{user}:{password}@{host}:9200/`
|
| [Exasol](/docs/configuration/databases#exasol) |
`pip install sqlalchemy-exasol`
|
`exa+pyodbc://{username}:{password}@{hostname}:{port}/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC`
|
@@ -373,6 +374,22 @@
cockroachdb://root@{hostname}:{port}/{database}?sslmode=disable
```
+
+#### CouchbaseDB
+
+The recommended connector library for CouchbaseDB is
+[couchbase-sqlalchemy](https://github.com/couchbase/couchbase-sqlalchemy).
+```
+pip install couchbase-sqlalchemy
+```
+
+The expected connection string is formatted as follows:
+
+```
+couchbasedb://{username}:{password}@{hostname}:{port}?truststorepath={certificate
path}?ssl={true/false}
+```
+
+
#### CrateDB
The recommended connector library for CrateDB is
diff --git a/docs/src/resources/data.js b/docs/src/resources/data.js
index 766e32c5dd..ec19e92400 100644
--- a/docs/src/resources/data.js
+++ b/docs/src/resources/data.js
@@ -127,4 +127,9 @@ export const Databases = [
href: 'https://www.oceanbase.com/',
imgName: 'oceanbase.svg',
},
+ {
+ title: 'Couchbase',
+ href: 'https://www.couchbase.com/',
+ imgName: 'couchbase.svg',
+ },
];
diff --git a/docs/static/img/databases/couchbase.svg
b/docs/static/img/databases/couchbase.svg
new file mode 100644
index 0000000000..732a592444
--- /dev/null
+++ b/docs/static/img/databases/couchbase.svg
@@ -0,0 +1,19 @@
+<!--
+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.
+-->
+<svg xmlns="http://www.w3.org/2000/svg"; width="2500" height="575" viewBox="0.6
0.1 575.509 132.4"><title>logo</title><path d="M199.3 96.9c-20.3
0-30.5-14.601-30.5-30.8 0-16.1 10.6-30.6 30.7-30.6 7.7 0 13.2 1.7 17.9 4.7l-5.8
9.5c-3.3-2.1-6.9-3.4-12.3-3.4-10.9 0-16.7 8.7-16.7 19.5 0 11.101 5.6 20.3 16.9
20.3 6.4 0 10.2-2.199 13.3-4.5l5.3 9.101c-3 2.699-10.1 6.199-18.8
6.199zm43.1-36.4c-6.5 0-8.6 5.5-8.6 13.5s2.6 13.7 9.1 13.7c6.6 0 8.8-5.4
8.8-13.4-.1-8-2.7-13.8-9.3-13.8zm.2 36.4c-15.2 0-2 [...]
diff --git a/superset-frontend/src/assets/images/couchbase.svg
b/superset-frontend/src/assets/images/couchbase.svg
new file mode 100644
index 0000000000..732a592444
--- /dev/null
+++ b/superset-frontend/src/assets/images/couchbase.svg
@@ -0,0 +1,19 @@
+<!--
+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.
+-->
+<svg xmlns="http://www.w3.org/2000/svg"; width="2500" height="575" viewBox="0.6
0.1 575.509 132.4"><title>logo</title><path d="M199.3 96.9c-20.3
0-30.5-14.601-30.5-30.8 0-16.1 10.6-30.6 30.7-30.6 7.7 0 13.2 1.7 17.9 4.7l-5.8
9.5c-3.3-2.1-6.9-3.4-12.3-3.4-10.9 0-16.7 8.7-16.7 19.5 0 11.101 5.6 20.3 16.9
20.3 6.4 0 10.2-2.199 13.3-4.5l5.3 9.101c-3 2.699-10.1 6.199-18.8
6.199zm43.1-36.4c-6.5 0-8.6 5.5-8.6 13.5s2.6 13.7 9.1 13.7c6.6 0 8.8-5.4
8.8-13.4-.1-8-2.7-13.8-9.3-13.8zm.2 36.4c-15.2 0-2 [...]
diff --git a/superset/db_engine_specs/couchbasedb.py
b/superset/db_engine_specs/couchbasedb.py
new file mode 100644
index 0000000000..b9cebdba32
--- /dev/null
+++ b/superset/db_engine_specs/couchbasedb.py
@@ -0,0 +1,257 @@
+# 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.
+# pylint: disable=too-many-lines
+
+from __future__ import annotations
+
+from datetime import datetime
+from typing import Any, Optional, TypedDict
+from urllib import parse
+
+from flask_babel import gettext as __
+from marshmallow import fields, Schema
+from sqlalchemy.engine.url import URL
+
+from superset.constants import TimeGrain
+from superset.databases.utils import make_url_safe
+from superset.db_engine_specs.base import (
+ BaseEngineSpec,
+ BasicParametersMixin,
+ BasicParametersType as BaseBasicParametersType,
+ BasicPropertiesType as BaseBasicPropertiesType,
+)
+from superset.errors import ErrorLevel, SupersetError, SupersetErrorType
+from superset.utils.network import is_hostname_valid, is_port_open
+
+
+class BasicParametersType(TypedDict, total=False):
+ username: Optional[str]
+ password: Optional[str]
+ host: str
+ database: str
+ port: Optional[int]
+ query: dict[str, Any]
+ encryption: bool
+
+
+class BasicPropertiesType(TypedDict):
+ parameters: BasicParametersType
+
+
+class CouchbaseParametersSchema(Schema):
+ username = fields.String(allow_none=True, metadata={"description":
__("Username")})
+ password = fields.String(allow_none=True, metadata={"description":
__("Password")})
+ host = fields.String(
+ required=True, metadata={"description": __("Hostname or IP address")}
+ )
+ database = fields.String(
+ allow_none=True, metadata={"description": __("Database name")}
+ )
+ port = fields.Integer(
+ allow_none=True, metadata={"description": __("Database port")}
+ )
+ encryption = fields.Boolean(
+ dump_default=False,
+ metadata={"description": __("Use an encrypted connection to the
database")},
+ )
+ query = fields.Dict(
+ keys=fields.Str(),
+ values=fields.Raw(),
+ metadata={"description": __("Additional parameters")},
+ )
+
+
+class CouchbaseDbEngineSpec(BasicParametersMixin, BaseEngineSpec):
+ engine = "couchbasedb"
+ engine_name = "Couchbase"
+ default_driver = "couchbasedb"
+ allows_joins = False
+ allows_subqueries = False
+ sqlalchemy_uri_placeholder = (
+
"couchbasedb://user:password@host[:port]?truststorepath=value?ssl=value"
+ )
+ parameters_schema = CouchbaseParametersSchema()
+
+ _time_grain_expressions = {
+ None: "{col}",
+ TimeGrain.SECOND: "DATE_TRUNC_STR(TOSTRING({col}),'second')",
+ TimeGrain.MINUTE: "DATE_TRUNC_STR(TOSTRING({col}),'minute')",
+ TimeGrain.HOUR: "DATE_TRUNC_STR(TOSTRING({col}),'hour')",
+ TimeGrain.DAY: "DATE_TRUNC_STR(TOSTRING({col}),'day')",
+ TimeGrain.MONTH: "DATE_TRUNC_STR(TOSTRING({col}),'month')",
+ TimeGrain.YEAR: "DATE_TRUNC_STR(TOSTRING({col}),'year')",
+ TimeGrain.QUARTER: "DATE_TRUNC_STR(TOSTRING({col}),'quarter')",
+ }
+
+ @classmethod
+ def epoch_to_dttm(cls) -> str:
+ return "MILLIS_TO_STR({col} * 1000)"
+
+ @classmethod
+ def epoch_ms_to_dttm(cls) -> str:
+ return "MILLIS_TO_STR({col})"
+
+ @classmethod
+ def convert_dttm(
+ cls, target_type: str, dttm: datetime, db_extra: Optional[dict[str,
Any]] = None
+ ) -> Optional[str]:
+ if target_type.lower() == "date":
+ formatted_date = dttm.date().isoformat()
+ else:
+ formatted_date = dttm.replace(microsecond=0).isoformat()
+ return f"DATETIME(DATE_FORMAT_STR(STR_TO_UTC('{formatted_date}'),
'iso8601'))"
+
+ @classmethod
+ def build_sqlalchemy_uri(
+ cls,
+ parameters: BaseBasicParametersType,
+ encrypted_extra: Optional[dict[str, Any]] = None,
+ ) -> str:
+ query_params = parameters.get("query", {}).copy()
+ if parameters.get("encryption"):
+ query_params["ssl"] = "true"
+ else:
+ query_params["ssl"] = "false"
+
+ if parameters.get("port") is None:
+ uri = URL.create(
+ "couchbasedb",
+ username=parameters.get("username"),
+ password=parameters.get("password"),
+ host=parameters["host"],
+ port=None,
+ query=query_params,
+ )
+ else:
+ uri = URL.create(
+ "couchbasedb",
+ username=parameters.get("username"),
+ password=parameters.get("password"),
+ host=parameters["host"],
+ port=parameters.get("port"),
+ query=query_params,
+ )
+ print(uri)
+ return str(uri)
+
+ @classmethod
+ def get_parameters_from_uri(
+ cls, uri: str, encrypted_extra: Optional[dict[str, Any]] = None
+ ) -> BaseBasicParametersType:
+ print("get_parameters is called : ", uri)
+ url = make_url_safe(uri)
+ query = {
+ key: value
+ for key, value in url.query.items()
+ if (key, value) not in cls.encryption_parameters.items()
+ }
+ ssl_value = url.query.get("ssl", "false").lower()
+ encryption = ssl_value == "true"
+ return BaseBasicParametersType(
+ username=url.username,
+ password=url.password,
+ host=url.host,
+ port=url.port,
+ database=url.database,
+ query=query,
+ encryption=encryption,
+ )
+
+ @classmethod
+ def validate_parameters(
+ cls, properties: BaseBasicPropertiesType
+ ) -> list[SupersetError]:
+ """
+ Couchbase local server needs hostname and port but on cloud we need
only connection String along with credentials to connect.
+ """
+ errors: list[SupersetError] = []
+
+ required = {"host", "username", "password", "database"}
+ parameters = properties.get("parameters", {})
+ present = {key for key in parameters if parameters.get(key, ())}
+
+ if missing := sorted(required - present):
+ errors.append(
+ SupersetError(
+ message=f'One or more parameters are missing: {",
".join(missing)}',
+
error_type=SupersetErrorType.CONNECTION_MISSING_PARAMETERS_ERROR,
+ level=ErrorLevel.WARNING,
+ extra={"missing": missing},
+ ),
+ )
+
+ host = parameters.get("host", None)
+ if not host:
+ return errors
+ # host can be a connection string in case of couchbase cloud. So
Connection Check is not required in that case.
+ if not is_hostname_valid(host):
+ errors.append(
+ SupersetError(
+ message="The hostname provided can't be resolved.",
+
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
+ level=ErrorLevel.ERROR,
+ extra={"invalid": ["host"]},
+ ),
+ )
+ return errors
+
+ if port := parameters.get("port", None):
+ try:
+ port = int(port)
+ except (ValueError, TypeError):
+ errors.append(
+ SupersetError(
+ message="Port must be a valid integer.",
+
error_type=SupersetErrorType.CONNECTION_INVALID_PORT_ERROR,
+ level=ErrorLevel.ERROR,
+ extra={"invalid": ["port"]},
+ ),
+ )
+ if not (isinstance(port, int) and 0 <= port < 2**16):
+ errors.append(
+ SupersetError(
+ message=(
+ "The port must be an integer between 0 and 65535 "
+ "(inclusive)."
+ ),
+
error_type=SupersetErrorType.CONNECTION_INVALID_PORT_ERROR,
+ level=ErrorLevel.ERROR,
+ extra={"invalid": ["port"]},
+ ),
+ )
+ elif not is_port_open(host, port):
+ errors.append(
+ SupersetError(
+ message="The port is closed.",
+
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
+ level=ErrorLevel.ERROR,
+ extra={"invalid": ["port"]},
+ ),
+ )
+
+ return errors
+
+ @classmethod
+ def get_schema_from_engine_params(
+ cls,
+ sqlalchemy_uri: URL,
+ connect_args: dict[str, Any],
+ ) -> Optional[str]:
+ """
+ Return the configured schema.
+ """
+ return parse.unquote(sqlalchemy_uri.database)
diff --git a/superset/sql_parse.py b/superset/sql_parse.py
index 8046e8f74f..05bf9b19bb 100644
--- a/superset/sql_parse.py
+++ b/superset/sql_parse.py
@@ -102,6 +102,7 @@ SQLGLOT_DIALECTS = {
"clickhouse": Dialects.CLICKHOUSE,
"clickhousedb": Dialects.CLICKHOUSE,
"cockroachdb": Dialects.POSTGRES,
+ "couchbasedb": Dialects.MYSQL,
# "crate": ???
# "databend": ???
"databricks": Dialects.DATABRICKS,
diff --git a/tests/unit_tests/db_engine_specs/test_couchbase.py
b/tests/unit_tests/db_engine_specs/test_couchbase.py
new file mode 100644
index 0000000000..140df28732
--- /dev/null
+++ b/tests/unit_tests/db_engine_specs/test_couchbase.py
@@ -0,0 +1,93 @@
+# 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.
+
+from datetime import datetime
+from typing import Any, Optional
+
+import pytest
+from sqlalchemy import types
+
+from superset.utils.core import GenericDataType
+from tests.unit_tests.db_engine_specs.utils import (
+ assert_column_spec,
+ assert_convert_dttm,
+)
+from tests.unit_tests.fixtures.common import dttm # noqa: F401
+
+
+def test_epoch_to_dttm() -> None:
+ """
+ DB Eng Specs (couchbase): Test epoch to dttm
+ """
+ from superset.db_engine_specs.couchbasedb import CouchbaseDbEngineSpec
+
+ assert CouchbaseDbEngineSpec.epoch_to_dttm() == "MILLIS_TO_STR({col} *
1000)"
+
+
+def test_epoch_ms_to_dttm() -> None:
+ """
+ DB Eng Specs (couchbase): Test epoch ms to dttm
+ """
+ from superset.db_engine_specs.couchbasedb import CouchbaseDbEngineSpec
+
+ assert CouchbaseDbEngineSpec.epoch_ms_to_dttm() == "MILLIS_TO_STR({col})"
+
+
[email protected](
+ "target_type,expected_result",
+ [
+ ("Date", "DATETIME(DATE_FORMAT_STR(STR_TO_UTC('2019-01-02'),
'iso8601'))"),
+ (
+ "DateTime",
+ "DATETIME(DATE_FORMAT_STR(STR_TO_UTC('2019-01-02T03:04:05'),
'iso8601'))",
+ ),
+ ],
+)
+def test_convert_dttm(
+ target_type: str,
+ expected_result: Optional[str],
+ dttm: datetime, # noqa: F811
+) -> None:
+ from superset.db_engine_specs.couchbasedb import CouchbaseDbEngineSpec as
spec
+
+ assert_convert_dttm(spec, target_type, expected_result, dttm)
+
+
[email protected](
+ "native_type,sqla_type,attrs,generic_type,is_dttm",
+ [
+ ("SMALLINT", types.SmallInteger, None, GenericDataType.NUMERIC, False),
+ ("INTEGER", types.Integer, None, GenericDataType.NUMERIC, False),
+ ("BIGINT", types.BigInteger, None, GenericDataType.NUMERIC, False),
+ ("DECIMAL", types.Numeric, None, GenericDataType.NUMERIC, False),
+ ("NUMERIC", types.Numeric, None, GenericDataType.NUMERIC, False),
+ ("CHAR", types.String, None, GenericDataType.STRING, False),
+ ("VARCHAR", types.String, None, GenericDataType.STRING, False),
+ ("TEXT", types.String, None, GenericDataType.STRING, False),
+ ("BOOLEAN", types.Boolean, None, GenericDataType.BOOLEAN, False),
+ ],
+)
+def test_get_column_spec(
+ native_type: str,
+ sqla_type: type[types.TypeEngine],
+ attrs: dict[str, Any] | None,
+ generic_type: GenericDataType,
+ is_dttm: bool,
+) -> None:
+ from superset.db_engine_specs.couchbasedb import CouchbaseDbEngineSpec as
spec
+
+ assert_column_spec(spec, native_type, sqla_type, attrs, generic_type,
is_dttm)
