[GitHub] [superset] betodealmeida commented on a diff in pull request #22812: feat: Add Ocient support

["betodealmeida (via GitHub)" <gi...@apache.org>]

betodealmeida commented on code in PR #22812:
URL: https://github.com/apache/superset/pull/22812#discussion_r1097816348


##########
docs/docs/databases/ocient.mdx:
##########
@@ -0,0 +1,32 @@
+---
+title: Ocient DB
+hide_title: true
+sidebar_position: 20
+version: 1
+---
+
+## Ocient DB
+
+The recommended connector library for Ocient is [sqlalchemy-ocient](https://pypi.org/project/sqlalchemy-ocient).
+
+## Install the Ocient Driver
+
+```
+pip install sqlalchemy_ocient
+```
+
+## Connecting to Ocient
+
+The connection string for Ocient looks like this:
+
+```shell
+ocient://{user}:{password}@{DNSname}:{port}/{database}
+```
+
+**NOTE**: You must enter the `user` and `password` credentials.  `host` defaults to localhost,
+port defaults to 4050, database defaults to `system` and `tls` defaults

Review Comment:
   How is `tls` passed to the URL?



##########
superset/db_engine_specs/ocient.py:
##########
@@ -0,0 +1,304 @@
+# 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.
+
+import re
+
+from sqlalchemy.engine.reflection import Inspector
+from sqlalchemy.orm import Session
+from superset.db_engine_specs.base import BaseEngineSpec
+from superset.errors import SupersetErrorType
+from flask_babel import gettext as __
+
+import pyocient
+from pyocient import _STPoint, _STLinestring, _STPolygon, TypeCodes
+from superset import app
+from superset.models.core import Database
+from typing import Any, Callable, Dict, List, NamedTuple, Tuple, Optional, Pattern
+import threading
+
+from superset.models.sql_lab import Query
+
+# Ensure pyocient inherits Superset's logging level
+superset_log_level = app.config["LOG_LEVEL"]
+pyocient.logger.setLevel(superset_log_level)
+
+
+# Regular expressions to catch custom errors
+
+CONNECTION_INVALID_USERNAME_REGEX = re.compile(
+    "The referenced user does not exist \(User '(?P<username>.*?)' not found\)"
+)
+CONNECTION_INVALID_PASSWORD_REGEX = re.compile(
+    "The userid/password combination was not valid \(Incorrect password for user\)"
+)
+CONNECTION_INVALID_HOSTNAME_REGEX = re.compile(
+    r"Unable to connect to (?P<host>.*?):(?P<port>.*?):"
+)
+CONNECTION_UNKNOWN_DATABASE_REGEX = re.compile(
+    "No database named '(?P<database>.*?)' exists"
+)
+CONNECTION_INVALID_PORT_ERROR = re.compile("Port out of range 0-65535")
+INVALID_CONNECTION_STRING_REGEX = re.compile(
+    "An invalid connection string attribute was specified \(failed to decrypt cipher text\)"
+)
+SYNTAX_ERROR_REGEX = re.compile(
+    r"There is a syntax error in your statement \((?P<qualifier>.*?) input '(?P<input>.*?)' expecting {.*}"
+)
+TABLE_DOES_NOT_EXIST_REGEX = re.compile(
+    "The referenced table or view '(?P<table>.*?)' does not exist"
+)
+COLUMN_DOES_NOT_EXIST_REGEX = re.compile(
+    "The reference to column '(?P<column>.*?)' is not valid"
+)
+
+
+# Custom datatype conversion functions
+
+
+def _to_hex(data: bytes) -> str:
+    """
+    Converts the bytes object into a string of hexadecimal digits.
+
+    :param data: the bytes object
+    :returns: string of hexadecimal digits representing the bytes
+    """
+    return data.hex()
+
+
+def _polygon_to_json(polygon: _STPolygon) -> str:
+    """
+    Converts the _STPolygon object into its JSON representation.
+
+    :param data: the polygon object
+    :returns: JSON representation of the polygon
+    """
+    json_value = f"{str([[p.long, p.lat] for p in polygon.exterior])}"
+    if polygon.holes:
+        for hole in polygon.holes:
+            json_value += f", {str([[p.long, p.lat] for p in hole])}"
+        json_value = f"[{json_value}]"
+    return json_value
+
+
+def _linestring_to_json(linestring: _STLinestring) -> str:
+    """
+    Converts the _STLinestring object into its JSON representation.
+
+    :param data: the linestring object
+    :returns: JSON representation of the linestring
+    """
+    return f"{str([[p.long, p.lat] for p in linestring.points])}"
+
+
+def _point_to_comma_delimited(point: _STPoint) -> str:
+    """
+    Returns the x and y coordinates as a comma delimited string.
+
+    :param data: the point object
+    :returns: the x and y coordinates as a comma delimited string
+    """
+    return f"{point.long}, {point.lat}"
+
+
+# Sanitization function for column values
+SanitizeFunc = Callable[[Any], Any]
+
+# Represents a pair of a column index and the sanitization function
+# to apply to its values.
+PlacedSanitizeFunc = NamedTuple(
+    "PlacedSanitizeFunc",
+    [
+        ("column_index", int),
+        ("sanitize_func", SanitizeFunc),
+    ],
+)
+
+# This map contains functions used to sanitize values for column types
+# that cannot be processed natively by Superset.
+#
+# Superset serializes temporal objects using a custom serializer
+# defined in superset/utils/core.py (#json_int_dttm_ser(...)). Other
+# are serialized by the default JSON encoder.
+_sanitized_ocient_type_codes: Dict[int, SanitizeFunc] = {
+    TypeCodes.BINARY: _to_hex,
+    TypeCodes.ST_POINT: _point_to_comma_delimited,
+    TypeCodes.IP: str,
+    TypeCodes.IPV4: str,
+    TypeCodes.ST_LINESTRING: _linestring_to_json,
+    TypeCodes.ST_POLYGON: _polygon_to_json,
+}
+
+
+def _find_columns_to_sanitize(cursor: Any) -> List[PlacedSanitizeFunc]:
+    """
+    Cleans the column value for consumption by Superset.
+
+    :param cursor: the result set cursor
+    :returns: the list of tuples consisting of the column index and sanitization function
+    """
+    return [
+        PlacedSanitizeFunc(i, _sanitized_ocient_type_codes[cursor.description[i][1]])
+        for i in range(len(cursor.description))
+        if cursor.description[i][1] in _sanitized_ocient_type_codes
+    ]
+
+
+class OcientEngineSpec(BaseEngineSpec):
+    engine = "ocient"
+    engine_name = "Ocient"
+    # limit_method = LimitMethod.WRAP_SQL
+    force_column_alias_quotes = True
+    max_column_name_length = 30
+
+    # Store mapping of superset Query id -> Ocient ID
+    # These are inserted into the cache when executing the query
+    # They are then removed, either upon cancellation or query completion
+    query_id_mapping: Dict[str, str] = dict()
+    query_id_mapping_lock = threading.Lock()

Review Comment:
   Did you also test this with async queries? (with the query being executed by Celery)



##########
superset/db_engine_specs/ocient.py:
##########
@@ -0,0 +1,304 @@
+# 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.
+
+import re
+
+from sqlalchemy.engine.reflection import Inspector
+from sqlalchemy.orm import Session
+from superset.db_engine_specs.base import BaseEngineSpec
+from superset.errors import SupersetErrorType
+from flask_babel import gettext as __
+
+import pyocient
+from pyocient import _STPoint, _STLinestring, _STPolygon, TypeCodes
+from superset import app
+from superset.models.core import Database
+from typing import Any, Callable, Dict, List, NamedTuple, Tuple, Optional, Pattern
+import threading
+
+from superset.models.sql_lab import Query
+
+# Ensure pyocient inherits Superset's logging level
+superset_log_level = app.config["LOG_LEVEL"]
+pyocient.logger.setLevel(superset_log_level)

Review Comment:
   Oh, this is clever! I'm going to steal this for some other DBs. 🙃



-- 
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.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org