Repository: mindsdb/mindsdb
Pull request: Ckan integration
Fixes #2312 

This PR will introduce new handler for [CKAN](https://github.com/ckan/ckan/) (data management) integration. 
  * CKAN has API endpoints where we could retrieve the data
  * Connect to `datastore_search_sql` to retreive data
  * Use the data for model training


Problem statement:
[New Integration]: Integration with CKAN
### Is there an existing integration?

- [X] I have searched the existing integrations.

### Use Case

CKAN is an open-source DMS (data management system) for powering data hubs and data portals. It not only stores the data, but makes it discoverable and presentable. Having integration with the ML for its DataStore would be additon for better data presentations, visualizations and makes it smarter 

### Motivation

Having this integration implemented will bring the ML capabilities into the CKAN ecosystem without thee needs of use to external tools or libraries. 

### Implementation

_No response_

### Anything else?

_No response_

Hints from issue discussion:
Working on this

Commit message: Fix test

Patch to review:
diff --git a/mindsdb/integrations/handlers/ckan_handler/__about__.py b/mindsdb/integrations/handlers/ckan_handler/__about__.py
new file mode 100644
index 00000000000..b3d77fbd9b7
--- /dev/null
+++ b/mindsdb/integrations/handlers/ckan_handler/__about__.py
@@ -0,0 +1,9 @@
+__title__ = 'MindsDB CKAN handler'
+__package_name__ = 'mindsdb_ckan_handler'
+__version__ = '0.0.1'
+__description__ = "MindsDB handler for CKAN"
+__author__ = 'Konstantin Sivakov'
+__github__ = 'https://github.com/mindsdb/mindsdb'
+__pypi__ = 'https://pypi.org/project/mindsdb/'
+__license__ = 'GPL-3.0'
+__copyright__ = 'Copyright 2022- mindsdb'
diff --git a/mindsdb/integrations/handlers/ckan_handler/__init__.py b/mindsdb/integrations/handlers/ckan_handler/__init__.py
new file mode 100644
index 00000000000..95a5d0c5d0b
--- /dev/null
+++ b/mindsdb/integrations/handlers/ckan_handler/__init__.py
@@ -0,0 +1,19 @@
+from mindsdb.integrations.libs.const import HANDLER_TYPE
+
+from mindsdb.integrations.handlers.ckan_handler.__about__ import __version__ as version, __description__ as description
+try:
+    from mindsdb.integrations.handlers.ckan_handler import CkanHandler as Handler
+    import_error = None
+except Exception as e:
+    Handler = None
+    import_error = e
+
+title = 'CKAN'
+name = 'ckan'
+type = HANDLER_TYPE.DATA
+icon_path = 'icon.svg'
+
+__all__ = [
+    'Handler', 'version', 'name', 'type', 'title',
+    'description', 'import_error', 'icon_path'
+]
diff --git a/mindsdb/integrations/handlers/ckan_handler/ckan_handler.py b/mindsdb/integrations/handlers/ckan_handler/ckan_handler.py
new file mode 100644
index 00000000000..3e288b3be9a
--- /dev/null
+++ b/mindsdb/integrations/handlers/ckan_handler/ckan_handler.py
@@ -0,0 +1,92 @@
+from ckanapi import RemoteCKAN as rc
+import pandas as pd
+
+from mindsdb.integrations.libs.base_handler import DatabaseHandler
+from mindsdb.integrations.libs.response import HandlerStatusResponse, HandlerResponse, RESPONSE_TYPE
+from mindsdb_sql.parser.ast.base import ASTNode
+from mindsdb_sql.render.sqlalchemy_render import SqlalchemyRender
+from mindsdb.utilities.log import log
+
+
+class CkanHandler(DatabaseHandler):
+    """
+    This handler handles connection and consumption of the CKAN API.
+    """
+    name = "ckan"
+
+    def __init__(self, name=None, **kwargs):
+        super().__init__(name)
+        self.type = 'ckan'
+        self.name = name
+        self.connection_args = kwargs.get('connection_data')
+        self.ckan = None
+        self.dialect = 'postgresql'
+        self.renderer = SqlalchemyRender('postgres')
+        self.connection = None
+        self.is_connected = False
+
+    def connect(self) -> HandlerStatusResponse:
+        """
+        Handles the connection to a CKAN remote portal instance.
+        """
+        url = self.connection_args.get('url')
+        try:
+            ckan = rc(url)
+            self.is_connected = True
+            self.ckan = ckan
+        except Exception as e:
+            return HandlerStatusResponse(False, f'Failed to connect to CKAN: {e}')
+        self.connection = ckan
+        return HandlerStatusResponse(True)
+
+    def disconnect(self):
+        self.is_connected = False
+
+    def check_connection(self) -> HandlerStatusResponse:
+        response = HandlerStatusResponse(False)
+        try:
+            self.connect()
+            result = self.ckan.action.status_show()
+            if 'datastore' not in result.get('extensions'):
+                """
+                If the CKAN instance does not have the datastore extension,
+                we can't use it.
+                """
+                response.message = 'CKAN datastore is not enabled'
+                response.status = False
+                self.is_connected = True
+
+        except Exception as e:
+            log.error(f'Error connecting to CKAN: {e}!')
+            self.is_connected = False
+            response.error_message = e
+
+        if response.success is False and self.is_connected is True:
+            self.is_connected = False
+
+        response.success = True
+        return response
+
+    def query(self, query: ASTNode) -> HandlerResponse:
+        if not self.ckan:
+            self.connect()
+        query_str = self.renderer.get_string(query, with_failback=True)
+        return self.native_query(query_str)
+
+    def native_query(self, query_str: str) -> HandlerResponse:
+        """
+        Execute a query on the CKAN instance.
+        :param query_str: The query to execute.
+        :return: The response of the query.
+        """
+        if not self.ckan:
+            self.connect()
+        result = self.ckan.action.datastore_search_sql(sql=query_str)
+        if len(result.get('records')) > 0:
+            df = pd.DataFrame(result['records'])
+            response = HandlerResponse(RESPONSE_TYPE.TABLE, df)
+        else:
+            response = HandlerResponse(RESPONSE_TYPE.TABLE, None)
+        return response
+
+
diff --git a/mindsdb/integrations/handlers/ckan_handler/requirements.txt b/mindsdb/integrations/handlers/ckan_handler/requirements.txt
new file mode 100644
index 00000000000..5a237d414fe
--- /dev/null
+++ b/mindsdb/integrations/handlers/ckan_handler/requirements.txt
@@ -0,0 +1 @@
+ckanapi
\ No newline at end of file
diff --git a/mindsdb/integrations/handlers/ckan_handler/tests/__init__.py b/mindsdb/integrations/handlers/ckan_handler/tests/__init__.py
new file mode 100644
index 00000000000..e69de29bb2d
diff --git a/mindsdb/integrations/handlers/ckan_handler/tests/test_ckan_handler.py b/mindsdb/integrations/handlers/ckan_handler/tests/test_ckan_handler.py
new file mode 100644
index 00000000000..a07f9f4a2c1
--- /dev/null
+++ b/mindsdb/integrations/handlers/ckan_handler/tests/test_ckan_handler.py
@@ -0,0 +1,21 @@
+import unittest
+
+from mindsdb.integrations.handlers.ckan_handler.ckan_handler import CkanHandler
+
+
+class CkanHandlerTest(unittest.TestCase):
+
+    @classmethod
+    def setUpClass(cls):
+        cls.handler = CkanHandler('test_ckan_handler')
+        cls.kwargs = {
+            "connection_data": {
+                "url": "http://demo.ckan.org/",
+                "api_key": "demo"}
+        }
+
+    def test_connect(self):
+        self.handler.connect()
+
+    def test_query(self):
+        self.handler.query("SELECT * from 'b53c9e72-6b59-4cda-8c0c-7d6a51dad12a'")


Code review comments:
diff_hunk: @@ -0,0 +1,92 @@
+from ckanapi import RemoteCKAN as rc
+import pandas as pd
+
+from mindsdb.integrations.libs.base_handler import DatabaseHandler
+from mindsdb.integrations.libs.response import HandlerStatusResponse, HandlerResponse, RESPONSE_TYPE
+from mindsdb_sql.parser.ast.base import ASTNode
+from mindsdb_sql.render.sqlalchemy_render import SqlalchemyRender
+from mindsdb.utilities.log import log
+
+
+class CkanHandler(DatabaseHandler):
+    """
+    This handler handles connection and consumption of the CKAN API.
+    """
+    name = "ckan"
+
+    def __init__(self, name=None, **kwargs):
+        super().__init__(name)
+        self.type = 'ckan'
+        self.name = name
+        self.connection_args = kwargs.get('connection_data')
+        self.ckan = None
+        self.dialect = 'postgresql'
+        self.renderer = SqlalchemyRender('postgres')
+        self.connection = None
+        self.is_connected = False
+
+    def connect(self) -> HandlerStatusResponse:
+        """
+        Handles the connection to a CKAN remote portal instance.
+        """
+        url = self.connection_args.get('url')
+        try:
+            ckan = rc(url)
+            self.is_connected = True
+            self.ckan = ckan
+        except Exception as e:
+            return HandlerStatusResponse(False, f'Failed to connect to CKAN: {e}')
+        self.connection = ckan
+        return HandlerStatusResponse(True)
+
+    def disconnect(self):
+        self.is_connected = False
+
+    def check_connection(self) -> HandlerStatusResponse:
+        response = HandlerStatusResponse(False)
+        try:
+            self.connect()
+            result = self.ckan.action.status_show()
+            if 'datastore' not in result.get('extensions'):
+                """
+                If the CKAN instance does not have the datastore extension,
+                we can't use it.
+                """
+                response.message = 'CKAN datastore is not enabled'
+                response.status = False
+                self.is_connected = True
path: mindsdb/integrations/handlers/ckan_handler/ckan_handler.py
text: @user1:
Isn't this missing an assignment to `response.success` if `datastore` is inside `result.extensions`?

@author:
i was considering `return response` on line 58