Ingesting the ChEMBL database into Snowflake with Snowpark Container Services

Photo by Terry Vlisidis on Unsplash

Snowpark Container Services (SPCS) represents a major advancement in Snowflake’s vision of the modern Data Cloud. This capability enables organizations to deploy and run containerized applications directly within their Snowflake environment — bringing compute to the data, rather than moving data to compute.

Whether you’re executing complex data processing pipelines, running machine learning models, or integrating with third-party services, SPCS provide a secure, scalable, and fully managed environment to support your most demanding workloads. By leveraging Docker containers within Snowflake, teams can extend the platform’s functionality while maintaining the governance, performance, and simplicity that Snowflake is known for.

In this article, we will explore how you can use SPCS to implement a data pipeline which will ingest the ChEMBL database straight into Snowflake without the need for additional tools or runtime.

The ChEMBL database is a curated repository of bioactive drug-like small molecules, providing information on their chemical properties, bioactivities, and associated targets to support drug discovery research.

Assumptions

The intention of this blog is to demonstrate how you can use SPCS to ingest the ChEMBL database, it’s not meant as an introduction to SPCS.

Therefore it is assumed you have basic knowledge of Snowflake and SPCS and that you have already defined a stage, an image repository and a compute pool. If not, please go through this introductory blog or this Snowflake quickstart first.

Architecture and components

In this blog we will use, and refer to, the following architecture and components:

1. The FTP site where the ChEMBL database is hosted
2. An External Access Integration (EAI) object which enables access to the FTP site for the container.
3. Container, a container runs software in a portable, isolated environment.
4. A stage specifies where the ChEMBL data will be stored. We will use chembl_spcs_st as ‘named’ stage.
5. The Service Specification references an image (#8), a compute pool (#7), the EAI object (#2) and a stage (#4) for execution of the pipeline
6. The image repository, image repositories store and serve container images. (see #8)
7. Containers can be executed with a variety of different compute options. These compute options are defined in ‘Compute Pools’. We will use SPCS_COMPUTE_POOL which is of type CPU_X64_XS
8. An image is an immutable snapshot of a filesystem and its dependencies.

Please note : The data flows from 1 to 4 via the orange arrows.

We will now double-click on some of the components mentioned above: the image (#8), the external access integration (#2) and the service specification (#5). The other elements are assumed to be in place.

The image (#8)

We will start off with creating the image. An image is a lightweight, standalone package that contains everything needed to run a piece of software, including the code, runtime, libraries, and system tools. It’s used to create containers — isolated environments that ensure applications run consistently across different systems.

This image can be developed outside of Snowflake and is therefore our starting point. Docker images are defined with a Dockerfile:

FROM alpine:latest
# Install SQLite
RUN apk --no-cache add sqlite
# Install bash
RUN apk --no-cache add bash
# Install tar
RUN apk --no-cache add tar

# Create a directory to store the chembl database
WORKDIR /db

COPY sqlite_script /db/
COPY run.sh /db/
RUN chmod +x run.sh

# Command to run when the container starts
ENTRYPOINT ["/db/run.sh"]

In this Dockerfile we use alpine, a Linux distribution designed to be small, simple and lightweight. We then install three packages: sqlite, bash and tar, which are used in the run.sh script. The WORKDIR command creates a working directory /db in the image. Two scripts, sqlite_script and run.sh, are then copied from the local file system into the image. The run.sh script is made executable via the chmod +x command and will be executed once the container is started.

The contents of the run.sh script are below :

#!/bin/bash
#download the chembl sqlite database
wget https://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/latest/chembl_35_sqlite.tar.gz
#unpack 
tar -zxvf chembl_35_sqlite.tar.gz
#run the chembl database via sqlite and execute the sqlite_script file
sqlite3 chembl_35/chembl_35_sqlite/chembl_35.db < sqlite_script
#gzip all extracted csv files
gzip *.csv
#copy the gzipped csv files into directory '/hostvol'
cp /db/*.csv.gz /hostvol

On container start the following steps will happen:

1. The ChEMBL database will be downloaded from FTP
2. Download is a tarball and needs to be unpacked
3. Start the ChEMBL database
4. Then we have another script ‘sqlite_script‘ executed. It will output all the tables as CSV file.
5. The next step is to gzip all the CSV files
6. After gzipping all CSV files they will be copied into a target directory called ‘hostvol’

This sqlite_script looks like this :

.headers on
.mode csv
.output action_type.csv
SELECT * FROM action_type;
.output assay_type.csv
SELECT * FROM assay_type;
.output chembl_id_lookup.csv
SELECT * FROM chembl_id_lookup;
.output confidence_score_lookup.csv
SELECT * FROM confidence_score_lookup;
.output curation_lookup.csv
SELECT * FROM curation_lookup;
.output chembl_release.csv
SELECT * FROM chembl_release;
.output source.csv
SELECT * FROM source;
.output relationship_type.csv
SELECT * FROM relationship_type;
.output target_type.csv
SELECT * FROM target_type;
.output variant_sequences.csv
SELECT * FROM variant_sequences;
.output bioassay_ontology.csv
SELECT * FROM bioassay_ontology;
.output data_validity_lookup.csv
SELECT * FROM data_validity_lookup;
.output activity_smid.csv
SELECT * FROM activity_smid;
.output activity_stds_lookup.csv
SELECT * FROM activity_stds_lookup;
.output assay_classification.csv
SELECT * FROM assay_classification;
.output atc_classification.csv
SELECT * FROM atc_classification;
.output bio_component_sequences.csv
SELECT * FROM bio_component_sequences;
.output component_sequences.csv
SELECT * FROM component_sequences;
.output protein_classification.csv
SELECT * FROM protein_classification;
.output domains.csv
SELECT * FROM domains;
.output go_classification.csv
SELECT * FROM go_classification;
.output structural_alert_sets.csv
SELECT * FROM structural_alert_sets;
.output products.csv
SELECT * FROM products;
.output frac_classification.csv
SELECT * FROM frac_classification;
.output hrac_classification.csv
SELECT * FROM hrac_classification;
.output irac_classification.csv
SELECT * FROM irac_classification;
.output research_stem.csv
SELECT * FROM research_stem;
.output organism_class.csv
SELECT * FROM organism_class;
.output patent_use_codes.csv
SELECT * FROM patent_use_codes;
.output usan_stems.csv
SELECT * FROM usan_stems;
.output version.csv
SELECT * FROM version;
.output cell_dictionary.csv
SELECT * FROM cell_dictionary;
.output docs.csv
SELECT * FROM docs;
.output target_dictionary.csv
SELECT * FROM target_dictionary;
.output tissue_dictionary.csv
SELECT * FROM tissue_dictionary;
.output molecule_dictionary.csv
SELECT * FROM molecule_dictionary;
.output activity_supp.csv
SELECT * FROM activity_supp;
.output component_class.csv
SELECT * FROM component_class;
.output component_domains.csv
SELECT * FROM component_domains;
.output component_go.csv
SELECT * FROM component_go;
.output component_synonyms.csv
SELECT * FROM component_synonyms;
.output structural_alerts.csv
SELECT * FROM structural_alerts;
.output defined_daily_dose.csv
SELECT * FROM defined_daily_dose;
.output product_patents.csv
SELECT * FROM product_patents;
.output protein_class_synonyms.csv
SELECT * FROM protein_class_synonyms;
.output research_companies.csv
SELECT * FROM research_companies;
.output assays.csv
SELECT * FROM assays;
.output compound_records.csv
SELECT * FROM compound_records;
.output binding_sites.csv
SELECT * FROM binding_sites;
.output biotherapeutics.csv
SELECT * FROM biotherapeutics;
.output compound_properties.csv
SELECT * FROM compound_properties;
.output compound_structural_alerts.csv
SELECT * FROM compound_structural_alerts;
.output compound_structures.csv
SELECT * FROM compound_structures;
.output molecule_atc_classification.csv
SELECT * FROM molecule_atc_classification;
.output molecule_frac_classification.csv
SELECT * FROM molecule_frac_classification;
.output molecule_hierarchy.csv
SELECT * FROM molecule_hierarchy;
.output molecule_hrac_classification.csv
SELECT * FROM molecule_hrac_classification;
.output molecule_irac_classification.csv
SELECT * FROM molecule_irac_classification;
.output molecule_synonyms.csv
SELECT * FROM molecule_synonyms;
.output target_components.csv
SELECT * FROM target_components;
.output target_relations.csv
SELECT * FROM target_relations;
.output activities.csv
SELECT * FROM activities;
.output assay_class_map.csv
SELECT * FROM assay_class_map;
.output assay_parameters.csv
SELECT * FROM assay_parameters;
.output biotherapeutic_components.csv
SELECT * FROM biotherapeutic_components;
.output drug_indication.csv
SELECT * FROM drug_indication;
.output drug_mechanism.csv
SELECT * FROM drug_mechanism;
.output drug_warning.csv
SELECT * FROM drug_warning;
.output formulations.csv
SELECT * FROM formulations;
.output metabolism.csv
SELECT * FROM metabolism;
.output site_components.csv
SELECT * FROM site_components;
.output activity_properties.csv
SELECT * FROM activity_properties;
.output activity_supp_map.csv
SELECT * FROM activity_supp_map;
.output indication_refs.csv
SELECT * FROM indication_refs;
.output ligand_eff.csv
SELECT * FROM ligand_eff;
.output mechanism_refs.csv
SELECT * FROM mechanism_refs;
.output metabolism_refs.csv
SELECT * FROM metabolism_refs;
.output predicted_binding_domains.csv
SELECT * FROM predicted_binding_domains;
.output warning_refs.csv
SELECT * FROM warning_refs;
.output sqlite_stat1.csv
SELECT * FROM sqlite_stat1;
.quit

The script above was generated with a few lines of Python and the output of this sqlite script:

.headers off
.mode csv
.output tables.csv
SELECT name FROM sqlite_master WHERE type='table';
.quit

Testing the container locally

Before we upload the image file into Snowflake, we need to build and run locally to test if everything works as intended:

docker build --no-cache -t chembl:latest .

After the build is succesful, we run a local test:

docker run --rm -ti -v <local_path>/hostvol:/hostvol chembl:latest

Running the container should result in a few dozen gzipped csv files stored in the local hostvol directory. Please note that this might take a while, since the ChEMBL sqlite distribution file is 4.6G in size and needs to be downloaded.

Uploading the image into Snowflake

The next step is to push the image into Snowflake. To be able to run the container in SPCS we need a slightly modified Docker build:

docker build --rm --no-cache --platform linux/amd64 -t <repository_url>/chembl35:latest .

Retrieve the <repository_url> from Snowflake using SQL:

SHOW IMAGE REPOSITORIES;

Next, upload the newly built image to the image repository:

docker login <registry_hostname> -u <username>
docker push <repository_url>/chembl35:latest

(The host name in the repository URL is the registry host name)

Configuring External Access (#2)

By default Snowflake blocks all outbound network access, however, you can specify exceptions. To give access to the ChEMBL FTP site from the container we require two objects: a network rule and an external access integration:

CREATE OR REPLACE NETWORK RULE chembl_nw_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('ftp.ebi.ac.uk');

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION chembl_access_integration
  ALLOWED_NETWORK_RULES = (chembl_nw_rule)
  ENABLED = true;

Good to go

We now have the image stored in the image repository, an access integration object and a stage defined, so we’re ready to start ingesting the ChEMBL database directly into Snowflake without requiring an additional runtime.

For that we will use a job service. This is an SPCS concept which automatically stops a container when it’s finished. In a way similar to a SQL stored procedure.

EXECUTE JOB SERVICE
    IN COMPUTE POOL SPCS_COMPUTE_POOL 
    NAME=ChEMBL_SVC
    EXTERNAL_ACCESS_INTEGRATIONS=(chembl_access_integration) 
    COMMENT = 'ChEMBL SERVICE' 
    FROM SPECIFICATION $$
spec:
  containers:
  - name: chembl-spcs
    image: /ste/public/chembl_repo/chembl35:latest
    volumeMounts:
    - name: chembl-st
      mountPath: /hostvol
  volumes:
  - name: chembl-st
    source: "@chembl_spcs_st"
$$
;

Please note that the service specification (#5) is defined ‘inline’ between the $$ signs, but could also be managed and stored in a separate yaml file.

If you want to monitor the output of the services while running you can execute:

SELECT SYSTEM$GET_SERVICE_LOGS('ChEMBL_SVC', 0, 'chembl-spcs');

On completion you will be able to find the ChEMBL tables in the stage chembl_spcs_st. Verify the results with:

LS @CHEMBL_SPCS_ST;

Done !

Populating Snowflake tables

Ingesting the data from staged files into Snowflake tables is not within the scope of this blog, but for reference here is way to do it for the ‘component_sequences’ table:

CREATE FILE FORMAT chembl_fmt
  TYPE = csv
  FIELD_OPTIONALLY_ENCLOSED_BY = '"' 
  PARSE_HEADER = True;

CREATE OR REPLACE TABLE component_sequences
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@chembl_spcs_st/component_sequences.csv.gz',
          FILE_FORMAT=>'chembl_fmt'
        )
      ));

COPY INTO component_sequences
FROM @chembl_spcs_st/component_sequences.csv.gz
file_format = (field_optionally_enclosed_by = '"', skip_header = 1); 

Next steps

Having the ChEMBL database available in Snowflake allows for a variety of use cases. For instance, this excellent blog by Namitha shows how to do machine learning, in Snowflake, on top the ChEMBL database:

[embed]5 Steps to Leverage Snowpark ML in Drug Discovery Research Train, deploy, and apply an ML model to predict the activity of novel chemical compounds.medium.com