Example: Building a dataset from an Excel file

The Python package ckanapi_harvesters.builder implements functions to manage a CKAN dataset (previously known as a package) with the help of an Excel workbook. This Excel file specifies the dataset metadata and information to upload/download the resources of the dataset. An illustration of these tasks is given in this notebook.

Initialisation

The Python package and its extra dependencies can be installed with the following command:

> pip install ckanapi-harvesters[extras]

The following cell refers to the code present in the Git directory with the option use_git_package.

# initial checks
import sys
print(f"Python version: {sys.version} in {sys.executable}")

# optionally, use the ckanapi_harvesters package present in the Git directory
use_git_package = False
if use_git_package:
    import os
    cwd = os.getcwd()
    if not os.path.isdir(os.path.join(cwd, "ckanapi_harvesters")):
        # we assume we are in the examples directory
        cwd = os.path.join(cwd, r"../../src")  # aim for src directory
        assert(os.path.isdir(os.path.join(cwd, "ckanapi_harvesters")))
        os.chdir(cwd)
        print("CWD changed to: " + os.path.abspath(""))

import os
from ckanapi_harvesters import CkanApi, BuilderPackage, CkanCallbackLevel
from ckanapi_harvesters.builder.example import example_package_xls

from ckanapi_harvesters import __version__ as ckanapi_harvesters_version
from ckanapi_harvesters import package_dir as ckanapi_package_dir
print(f"ckanapi-harvesters version: {ckanapi_harvesters_version} in {ckanapi_package_dir}")

Script configuration

• CKAN URL

• Owner organisation

• Excel file

• API key file

• Proxies

ckan_url = "https://demo.ckan.org/"
ckan_url = None  # use this line if the CKAN URL is specified in the Excel workbook / user input

# Owner organization for the new dataset
owner_org = "test"
owner_org = None  # use this line if the owner organisation is specified in the Excel workbook / user input

xls_file = example_package_xls  # Excel file
print("Excel file: " + xls_file)
if not os.path.exists(xls_file):
    print("Excel file not found !!!")

apikey_file = os.path.expanduser(os.path.join("~", ".config", "__CKAN_API_KEY__.txt"))  # default location: ~/.config/__CKAN_API_KEY__.txt
apikey_file = None  # if not specified, the package will look in the different locations and environment variables
print("API key file: " + str(apikey_file))
if apikey_file is not None and not os.path.exists(apikey_file):
    print("API key file not found !!!")

# proxy configuration
proxies = {"http": "http://myproxy", "https": "http://myproxy"}  # example
proxies = {"http": "", "https": ""}  # no proxies
proxies = None  # use the system configuration, defined in your environment variables
print("proxies = " + str(proxies))

Loading dataset metadata from Excel file

The Excel workbook given in the example refers to an external code module for DataFrame upload/download functions. To activate this feature, a call to unlock_external_code_execution must be done.

Warning: Only enable this feature for code which comes from trusted sources as this executes the module referred in the Excel workbook (Auxiliary functions file field).

BuilderPackage.unlock_external_code_execution()
mdl = BuilderPackage.from_excel(xls_file)
print(f"Excel file loaded for dataset '{mdl.package_attributes.title}'")
print(f"The URL will be: {mdl.get_package_page_url(ckan)}")

Connecting to CKAN

ckan = CkanApi(ckan_url, proxies=proxies, apikey_file=apikey_file, owner_org=owner_org)
# you can specify the CKAN URL, owner organization, API key here or in the Excel workbook
ckan = mdl.init_ckan(ckan)
ckan.input_missing_info(input_args_if_necessary=True, input_owner_org=True, error_not_found=False)  # request user input to configure CKAN
ckan.set_limits_per_request(10000)  # reduce if server hangs up
ckan.set_requests_delay(0.1)  # increase if server errors 502
ckan.set_verbosity(True)  # this displays all the steps performed by the script
ckan.test_ckan_login(raise_error=True, verbose=True)  # test if you are correctly logged in

Displaying the dataset model

df_dict = mdl.get_all_df()
for tab, df in df_dict.items():
    display(f"Tab {tab}:")
    display(df)

Initiating the dataset

This call creates the dataset if no other dataset with the same name exists. If the dataset already exists, it is updated with information from the Excel workbook. The resources are initialized. Optionally, the resources data can be fully reuploaded (even if the resources already exist) to ensure the server side of the dataset represents the information specified in the Excel workbook. However, if there are large datasets, this resets them.

reupload = True  # True: reuploads all documents and resets large datasets to the first document (not recommended if there is a large dataset)
mdl.patch_request_full(ckan, reupload=reupload)

Uploading large datasets

Large datasets are defined locally by a directory containing multiple CSV files. The first file found (in alphabetic order) is used to initialize the dataset. This function automates the concatenation of other files using the API datastore_upsert in a multi-threaded implementation. It can be executed multiple times without affecting the final result, as long as all the data has been transferred. The number of threads should be adjusted if there are too many request errors.

threads = 3  # > 1: multi-threading mode - reduce if HTTP 502 errors
mdl.upload_large_datasets(ckan, threads=threads, only_missing=True)

Final requests

mdl.patch_request_final(ckan)  # sets dataset state, reorders resources, updates dataset policy fields - also called at the end of upload_large_datasets

Updating metadata policy scores

mdl.remote_policy_check(ckan, verbose=True)

Database queries

Querying the dataset as a database table. Check the format of the first few lines with this method.

users_id = mdl.get_or_query_resource_id(ckan, "users.csv")
traces_id = mdl.get_or_query_resource_id(ckan, "traces.csv")

Simple requests

Using API datastore_search.

cursor = ckan.datastore_search_cursor(users_id, total_limit=1)
document = next(cursor)
user_id = document["user_id"]

cursor = ckan.datastore_search_cursor(traces_id, filters={"user_id": int(user_id)}, total_limit=10)
for document in cursor:
    print(document)

SQL queries

Example of an SQL query joining two tables using API datastore_search_sql.

query = f"""
SELECT t.*, u.* FROM "{traces_id}" t
JOIN "{users_id}" u ON t.user_id = u.user_id
WHERE t.user_id = {user_id}
LIMIT 10
"""

cursor = ckan.datastore_search_sql_cursor(query)
for document in cursor:
    print(document)

Restoring original files

This function downloads all the resources of a dataset to CSV files. The multi-threaded implementation is reserved to download large datasets.

# define the destination directory
example_package_download_dir = os.path.abspath("package_download")
print("Dataset will be downloaded in: " + example_package_download_dir)

threads = 3  # > 1: number of threads to download large datasets
mdl.download_request_full(ckan, example_package_download_dir, full_download=True, threads=threads, skip_existing=False)