Data set upload mechanism

[gubuntu]

We need a UI for uploading data sets to GCP cloud storage backend

Install vanilla CKAN

• decide on GCP managed PostgreSQL or kartoza/docker-postgis or internal PostgreSQL for CKAN admin db backend
• integrate GCP Cloud Storage as a back-end and direct file uploads to be stored there, ideally in the structure already created there by the WRO team

[Mohab25]

investigating options:
There are a number of parameters that can be taken into account comparing CloduSQL (managed PostgreSQL instance), PostgreSQL running inside a VM instance, and docker-PostGIS), few i can think of are:

• Simplicity (as per interaction,integration, management,)
• Security.
• Extensibility (as per supporting PostGIS Extension).
• Features.

Note: as the number of users isn’t expected to scale to the level of data, thus scalability isn’t taken as a comparison parameter.

Simplicity:
The interaction model with managed cloud sql is through console, api’s, and cli tools, for non-technical staff of WRO, this is the simplest option as the console makes it easier to interact with the database and provision resources, from a development side CloudSQL is easier to integrate with other parts of the WRO system that relies on other GCP services(GKE, BigQuery), cloudSQL is more simpler than other two.

Security:
For privacy, this is a matter of service-level agreement, for cloud sql google insures data encryption at rest and transit, as we are hosting CKAN within the same region of cloud sql, the connection can be done through Private IP connection which adds a security layer, though from a connection point, having PostGres installed within the same VM as CKAN can be more secure, for docker-PostGIS, it relates to how are we deploying it, there is no need to scale for admin db thus kubernetes is not an option here, which means we are connecting a docker container with CKAN in the same VM, from other side having the database with application in the same VM presents a single point of failure.

Extensibility:
All three options can be extended with PostGIS, but both docker-PostGIS and a Postgres running in a VM are more customizable than the managed service, this the strongest case for these solutions.

Features:
CloudSQL comes with a lot of features out of the box, most notably failover(automatic backups), autoscaling, patches and updates are automatically applied and others, the same can be achieved with the other two solutions with a bit of configuration.

Thus I think we should go with CloudSQL (managed service).

[Mohab25]

Investigating Cloud storage with CKAN:
CKAN stopped supporting file uploads to Cloud Storage providers since version 2.2 (Feb 2014), and uses CKAN “FileStore” local storage instead, files can be uploaded to the local storage through actions resource_create() and/or resource_update(), for cloud storage we can either extend CKAN or use a plugin (e.g. ckanext-cloudstorage), the suggested method is to begin with the second option and loop back to the first.
ckanext-cloudstorage supports all the cloud providers supported by the open source libcloud python package (https://libcloud.apache.org/) which includes GCP cloud storage, thus working on testing this after going through issue#3 (#3) == creating the metadata Form.

[gubuntu]

based on meeting feedback today, please do attempt to implement storage into GCS folders.

A possible scenario:
In the metadata form, fetch list of available folders for user to choose. This then gets stored in the metadata but is also used during the upload to direct the file storage location

[Mohab25]

the current implementation takes WRO themes into account for dynamic directory creation, as a user inputs a Dataset Topic Category (Agriculture, Biodiversity, ..etc.) , that category gets created as a GCS directory, and resources are stored within those directories,
note: GCS doesn't hold directories in the technical sense, rather these structures and paths found within GCS are visualizations of the objects stored (see https://cloud.google.com/storage/docs/folders).

[gubuntu]

@Mohab25 please document behaviour in all practical situations of uploading files so that we have clear procedures about file naming conventions, how to avoid overwriting or inadvertently deleting files, differentiating between apparent duplicates (same 'folder' and file name but different object ID) etc.

[Mohab25]

@gubuntu for uploading / deletion situation, as ckanext-cloudstorage has a custom behavior and also uses underlying apache-libcloud (https://libcloud.apache.org/) to control cloud objects (including other vendors beside google), and as ckan uses certain flow to upload / delete resources, i had to re-write the logic in a custom extension for uploading / deletion in-order to have a stable flow, now we are using GCS python client lib to upload/delete objects from bucket (https://cloud.google.com/storage/docs/uploads-downloads).

for name resolution, instead of generating a random string, we now use the same resource id in the database which is guaranteed to be unique, the mapping of the name is as [resourceName_id_resourceName.ext , for example shapefile1_id_3dsfsdfss.shp], this way we can keep a connection between the cloud object with the ckan database, and separate objects with the same name.

[mikev3003]

@Mohab25 the above sounds good. Is there now a platform available that we can test the data upload facility? http://130.211.222.159/ is not linking to the ckan interface.

[Mohab25]

@mikev3003 hi Michael, i still don't have a permission to create a private google cloud sql instance (see the highlight in the screenshot). i want to be able to create a private connection, also i want to be able to create a service account (https://cloud.google.com/iam/docs/service-accounts) with GCP to connect ckan to your cloud storage instance.

[mikev3003]

@Mohab25 I have changed your role to 'owner' could you please check if you are able to do it now? Otherwise I suggest we jump on a call together and go through the permission options to get you what you need.

[Mohab25]

@mikev3003 Hello professor, OK, i've scheduled a meeting see if that a good timing for you? also sent you an email with some credentials for ckan site admin, give http://130.211.222.159/ a check.

[Mohab25]

hello professor, it seems you weren't able to attend the call, to summarize, i need a new cloudsql (postgres) instance, with the following configs:

• zonal availability (single zone) - don't see why it should be multiple zones, if you have other preferences change accordingly.
• database version postgres 12 - we tested on this, no need for new features of postgres 14.
• go to Customise your instance
  • choose a machine type of your preferences (the default 4 cores and 26GB ram is good).
  • choose a storage of your preference (the default is 100GB which is good and can be increased in the future)
  • ⚠️ here is the part that i don't have permissions to, for connection we want a private connection as we don't want the database to be exposed publicly to the internet, choose a private connection.
  • you can leave the rest as defaults or customize your preferences.
    drop the existing database (named ckanwrosql) so no costs to be added.

after that we can try to connect ckan with the new one.

[mikev3003]

@Mohab25 I've given @gubuntu owner rights - hopefully he can assist with the needed permissions