You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "Vitor-Avila (via GitHub)" <gi...@apache.org> on 2024/01/22 17:12:48 UTC

[PR] fix(db2): Improving support for ibm db2 connections [superset]

Vitor-Avila opened a new pull request, #26744:
URL: https://github.com/apache/superset/pull/26744

   ### SUMMARY
   This PR fixes some issues with the IBM Db2 engine spec:
   
   #### Connection issues
   When trying to establish a connection to an IBM Db2 Cloud Instance, below error is displayed in case `ibm_db_sa==0.3.8` is installed:
   
   <img width="1117" alt="image" src="https://github.com/apache/superset/assets/96086495/cbd6beac-6f27-4058-8098-8cb4cd7016ab">
   
   To fix this, I updated the version limits in `setup.py`.
   
   #### Unable to create a physical dataset
   Trying to create a virtual dataset would return an error, because `get_table_comment()` returns a `Tuple()` for this engine:
   
   ![image](https://github.com/apache/superset/assets/96086495/00d47fc6-2f01-4c04-bbd6-3e003423d6db)
   
   stack trace:
   ```
   2024-01-22 14:04:37,371:ERROR:flask_appbuilder.api:Object of type LegacyRow is not JSON serializable
   Traceback (most recent call last):
     File "/Users/vitoravila/.pyenv/versions/3.10.12/envs/superset-mods/lib/python3.10/site-packages/flask_appbuilder/api/__init__.py", line 110, in wraps
       return f(self, *args, **kwargs)
     File "/Users/vitoravila/code/superset-mods/superset/superset/views/base_api.py", line 127, in wraps
       raise ex
     File "/Users/vitoravila/code/superset-mods/superset/superset/views/base_api.py", line 121, in wraps
       duration, response = time_function(f, self, *args, **kwargs)
     File "/Users/vitoravila/code/superset-mods/superset/superset/utils/core.py", line 1454, in time_function
       response = func(*args, **kwargs)
     File "/Users/vitoravila/code/superset-mods/superset/superset/utils/log.py", line 255, in wrapper
       value = f(*args, **kwargs)
     File "/Users/vitoravila/code/superset-mods/superset/superset/databases/api.py", line 749, in table_metadata
       return self.response(200, **table_info)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/envs/superset-mods/lib/python3.10/site-packages/flask_appbuilder/api/__init__.py", line 772, in response
       _ret_json = jsonify(kwargs)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/envs/superset-mods/lib/python3.10/site-packages/flask/json/__init__.py", line 342, in jsonify
       return current_app.json.response(*args, **kwargs)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/envs/superset-mods/lib/python3.10/site-packages/flask/json/provider.py", line 309, in response
       f"{self.dumps(obj, **dump_args)}\n", mimetype=mimetype
     File "/Users/vitoravila/.pyenv/versions/3.10.12/envs/superset-mods/lib/python3.10/site-packages/flask/json/provider.py", line 230, in dumps
       return json.dumps(obj, **kwargs)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/lib/python3.10/json/__init__.py", line 238, in dumps
       **kw).encode(obj)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/lib/python3.10/json/encoder.py", line 201, in encode
       chunks = list(chunks)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/lib/python3.10/json/encoder.py", line 431, in _iterencode
       yield from _iterencode_dict(o, _current_indent_level)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/lib/python3.10/json/encoder.py", line 405, in _iterencode_dict
       yield from chunks
     File "/Users/vitoravila/.pyenv/versions/3.10.12/lib/python3.10/json/encoder.py", line 438, in _iterencode
       o = _default(o)
     File "/Users/vitoravila/.pyenv/versions/3.10.12/envs/superset-mods/lib/python3.10/site-packages/flask/json/provider.py", line 122, in _default
       raise TypeError(f"Object of type {type(o).__name__} is not JSON serializable")
   ```
   
   To fix this, I created a db2-specific `get_table_comment()` method in the `Db2EngineSpec`.
   
   #### Unable to execute un-classified queries in SQL Lab
   It was not possible to run a query without specifying the schema in SQL Lab (even if the schema was selected in the dropdown):
   
   ![image](https://github.com/apache/superset/assets/96086495/41beed9c-9530-41a7-9d7f-43823b11efc1)
   
   To fix this, I created a db2-specific `get_prequeries()` method in the `Db2EngineSpec`, and also set `supports_dynamic_schema` to `True`.
   
   ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF
   Screenshots for **Before** added in Summary. After this PR:
   
   ![image](https://github.com/apache/superset/assets/96086495/0a88b1aa-b4d4-475c-bb13-8f4789297b7e)
   
   ![image](https://github.com/apache/superset/assets/96086495/c63de3cc-e9f7-46f4-9688-bb9a7cd3b6d0)
   
   ![image](https://github.com/apache/superset/assets/96086495/ad3a1d99-5614-4ecf-a311-c8b3df4bb9a3)
   
   ### TESTING INSTRUCTIONS
   Unit tests added. To manually test:
   1. Make sure you're able to connect to an IBM Db2 instance.
   2. Validate you're able to create physical datasets.
   3. Check you're able to run a SQL query without specifying the schema in the SQL statement as long as it's selected in the dropdown. 
   
   ### ADDITIONAL INFORMATION
   <!--- Check any relevant boxes with "x" -->
   <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue -->
   - [ ] Has associated issue:
   - [ ] Required feature flags:
   - [ ] Changes UI
   - [ ] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351))
     - [ ] Migration is atomic, supports rollback & is backwards-compatible
     - [ ] Confirm DB migration upgrade and downgrade tested
     - [ ] Runtime estimates and downtime expectations provided
   - [ ] Introduces new feature or API
   - [ ] Removes existing feature or API
   


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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on code in PR #26744:
URL: https://github.com/apache/superset/pull/26744#discussion_r1462477419


##########
superset/db_engine_specs/db2.py:
##########
@@ -52,3 +61,49 @@ class Db2EngineSpec(BaseEngineSpec):
     @classmethod
     def epoch_to_dttm(cls) -> str:
         return "(TIMESTAMP('1970-01-01', '00:00:00') + {col} SECONDS)"
+
+    @classmethod
+    def get_table_comment(
+        cls, inspector: Inspector, table_name: str, schema: Union[str, None]
+    ) -> Optional[str]:
+        """
+        Get comment of table from a given schema
+
+        Ibm Db2 return comments as tuples, so we need to get the first element
+
+        :param inspector: SqlAlchemy Inspector instance
+        :param table_name: Table name
+        :param schema: Schema name. If omitted, uses default schema for database
+        :return: comment of table
+        """
+        comment = None
+        try:
+            table_comment = inspector.get_table_comment(table_name, schema)
+            comment = table_comment.get("text")
+            return comment[0]

Review Comment:
   Ahhh, I see.
   
   In that case you could:
   
   ```python
   comment, = table_comment.get("text")
   return comment
   ```
   
   To indicate it's a one-tuple. But it's OK, it's just a nit. 😄 



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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "Vitor-Avila (via GitHub)" <gi...@apache.org>.
Vitor-Avila commented on code in PR #26744:
URL: https://github.com/apache/superset/pull/26744#discussion_r1462530895


##########
superset/db_engine_specs/db2.py:
##########
@@ -52,3 +61,49 @@ class Db2EngineSpec(BaseEngineSpec):
     @classmethod
     def epoch_to_dttm(cls) -> str:
         return "(TIMESTAMP('1970-01-01', '00:00:00') + {col} SECONDS)"
+
+    @classmethod
+    def get_table_comment(
+        cls, inspector: Inspector, table_name: str, schema: Union[str, None]
+    ) -> Optional[str]:
+        """
+        Get comment of table from a given schema
+
+        Ibm Db2 return comments as tuples, so we need to get the first element
+
+        :param inspector: SqlAlchemy Inspector instance
+        :param table_name: Table name
+        :param schema: Schema name. If omitted, uses default schema for database
+        :return: comment of table
+        """
+        comment = None
+        try:
+            table_comment = inspector.get_table_comment(table_name, schema)
+            comment = table_comment.get("text")
+            return comment[0]

Review Comment:
   that's really cool I didn't know that haha 😍 



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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "Vitor-Avila (via GitHub)" <gi...@apache.org>.
Vitor-Avila commented on code in PR #26744:
URL: https://github.com/apache/superset/pull/26744#discussion_r1462413291


##########
superset/db_engine_specs/db2.py:
##########
@@ -52,3 +61,49 @@ class Db2EngineSpec(BaseEngineSpec):
     @classmethod
     def epoch_to_dttm(cls) -> str:
         return "(TIMESTAMP('1970-01-01', '00:00:00') + {col} SECONDS)"
+
+    @classmethod
+    def get_table_comment(
+        cls, inspector: Inspector, table_name: str, schema: Union[str, None]
+    ) -> Optional[str]:
+        """
+        Get comment of table from a given schema
+
+        Ibm Db2 return comments as tuples, so we need to get the first element
+
+        :param inspector: SqlAlchemy Inspector instance
+        :param table_name: Table name
+        :param schema: Schema name. If omitted, uses default schema for database
+        :return: comment of table
+        """
+        comment = None
+        try:
+            table_comment = inspector.get_table_comment(table_name, schema)
+            comment = table_comment.get("text")
+            return comment[0]

Review Comment:
   @betodealmeida I think this would raise a `ValueError` because `text` is supposed to contain a tuple with a single element. Something like this:
   ``` python
   {
     "text": ("This is my table comment",)
   }
   ```
   
   I tested adding multiple comments to the same table, and it always get replaced (never appended). Also seems accurate with [their documentation](https://www.ibm.com/docs/en/db2oc?topic=statements-comment). 
   
   I just needed to override the `BaseEngineSpec` because it's [supposed to return a string and it doesn't account for the engine returning a tuple/array](https://github.com/apache/superset/blob/master/superset/db_engine_specs/base.py#L1309-L1319).
   
   



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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "codecov[bot] (via GitHub)" <gi...@apache.org>.
codecov[bot] commented on PR #26744:
URL: https://github.com/apache/superset/pull/26744#issuecomment-1904457825

   ## [Codecov](https://app.codecov.io/gh/apache/superset/pull/26744?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
   Attention: `1 lines` in your changes are missing coverage. Please review.
   > Comparison is base [(`e86d4d3`)](https://app.codecov.io/gh/apache/superset/commit/e86d4d3c92b91da0aa09bc215908c23f261b4f9a?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) 69.48% compared to head [(`407ea99`)](https://app.codecov.io/gh/apache/superset/pull/26744?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) 59.46%.
   > Report is 3 commits behind head on master.
   
   | [Files](https://app.codecov.io/gh/apache/superset/pull/26744?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Patch % | Lines |
   |---|---|---|
   | [superset/db\_engine\_specs/db2.py](https://app.codecov.io/gh/apache/superset/pull/26744?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-c3VwZXJzZXQvZGJfZW5naW5lX3NwZWNzL2RiMi5weQ==) | 95.23% | [1 Missing :warning: ](https://app.codecov.io/gh/apache/superset/pull/26744?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
   
   <details><summary>Additional details and impacted files</summary>
   
   
   ```diff
   @@             Coverage Diff             @@
   ##           master   #26744       +/-   ##
   ===========================================
   - Coverage   69.48%   59.46%   -10.02%     
   ===========================================
     Files        1894     1894               
     Lines       74138    74151       +13     
     Branches     8243     8243               
   ===========================================
   - Hits        51515    44096     -7419     
   - Misses      20554    27986     +7432     
     Partials     2069     2069               
   ```
   
   | [Flag](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Coverage Δ | |
   |---|---|---|
   | [hive](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [mysql](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [postgres](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [presto](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `53.77% <42.85%> (-0.03%)` | :arrow_down: |
   | [python](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `62.25% <95.23%> (-20.77%)` | :arrow_down: |
   | [sqlite](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [unit](https://app.codecov.io/gh/apache/superset/pull/26744/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `56.39% <95.23%> (+0.01%)` | :arrow_up: |
   
   Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#carryforward-flags-in-the-pull-request-comment) to find out more.
   
   
   </details>
   
   [:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/apache/superset/pull/26744?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).   
   :loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
   


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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on code in PR #26744:
URL: https://github.com/apache/superset/pull/26744#discussion_r1462282789


##########
superset/db_engine_specs/db2.py:
##########
@@ -52,3 +61,49 @@ class Db2EngineSpec(BaseEngineSpec):
     @classmethod
     def epoch_to_dttm(cls) -> str:
         return "(TIMESTAMP('1970-01-01', '00:00:00') + {col} SECONDS)"
+
+    @classmethod
+    def get_table_comment(
+        cls, inspector: Inspector, table_name: str, schema: Union[str, None]
+    ) -> Optional[str]:
+        """
+        Get comment of table from a given schema
+
+        Ibm Db2 return comments as tuples, so we need to get the first element
+
+        :param inspector: SqlAlchemy Inspector instance
+        :param table_name: Table name
+        :param schema: Schema name. If omitted, uses default schema for database
+        :return: comment of table
+        """
+        comment = None
+        try:
+            table_comment = inspector.get_table_comment(table_name, schema)
+            comment = table_comment.get("text")
+            return comment[0]

Review Comment:
   I'm a fan of doing:
   
   ```python
   comment, other_thing = table_comment.get("text")  # pylint: disable=unused-variable
   ```
   
   Even if you don't use `other_thing`, because it gives more information:
   
   1. How many elements `table_comment.get()` returns.
   2. A hint of what they are.



##########
superset/db_engine_specs/db2.py:
##########
@@ -52,3 +61,49 @@ class Db2EngineSpec(BaseEngineSpec):
     @classmethod
     def epoch_to_dttm(cls) -> str:
         return "(TIMESTAMP('1970-01-01', '00:00:00') + {col} SECONDS)"
+
+    @classmethod
+    def get_table_comment(
+        cls, inspector: Inspector, table_name: str, schema: Union[str, None]
+    ) -> Optional[str]:
+        """
+        Get comment of table from a given schema
+
+        Ibm Db2 return comments as tuples, so we need to get the first element
+
+        :param inspector: SqlAlchemy Inspector instance
+        :param table_name: Table name
+        :param schema: Schema name. If omitted, uses default schema for database
+        :return: comment of table
+        """
+        comment = None
+        try:
+            table_comment = inspector.get_table_comment(table_name, schema)
+            comment = table_comment.get("text")
+            return comment[0]
+        except IndexError:

Review Comment:
   Ohhh, so the behavior depends on the library version?



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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida merged PR #26744:
URL: https://github.com/apache/superset/pull/26744


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


Re: [PR] fix(db2): Improving support for ibm db2 connections [superset]

Posted by "Vitor-Avila (via GitHub)" <gi...@apache.org>.
Vitor-Avila commented on code in PR #26744:
URL: https://github.com/apache/superset/pull/26744#discussion_r1462293936


##########
superset/db_engine_specs/db2.py:
##########
@@ -52,3 +61,49 @@ class Db2EngineSpec(BaseEngineSpec):
     @classmethod
     def epoch_to_dttm(cls) -> str:
         return "(TIMESTAMP('1970-01-01', '00:00:00') + {col} SECONDS)"
+
+    @classmethod
+    def get_table_comment(
+        cls, inspector: Inspector, table_name: str, schema: Union[str, None]
+    ) -> Optional[str]:
+        """
+        Get comment of table from a given schema
+
+        Ibm Db2 return comments as tuples, so we need to get the first element
+
+        :param inspector: SqlAlchemy Inspector instance
+        :param table_name: Table name
+        :param schema: Schema name. If omitted, uses default schema for database
+        :return: comment of table
+        """
+        comment = None
+        try:
+            table_comment = inspector.get_table_comment(table_name, schema)
+            comment = table_comment.get("text")
+            return comment[0]
+        except IndexError:

Review Comment:
   In my tests it always returned a `Tuple()`, just added this as a precaution to avoid an issue in case we end up getting a `string`. I could remove this if we want to be more deterministic.  



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