ChEMBL Resources


Wednesday, 25 April 2018

Schema changes coming in ChEMBL_24

Since ChEMBL was first released in 2009, the diversity of data sources and data types in the database has increased significantly. Increasingly, we are dealing with more complex assays such as measurement of drug pharmacokinetic parameters or toxicology data sets such as clinical biochemistry and tissue histopathology data. There are a number of problems handling these kinds of assays with the current data model/database schema. For example, since parameters such as compound doses or time points could not be recorded against individual activity measurements (only the whole assay) such experiments were typically split so that a separate assay was created for each compound or time point measured. This is obviously far from ideal. Another issue is that such experiments frequently measure or derive multiple endpoints from a particular assay (e.g., AUC, Cmax, tmax, t1/2 for a pharmacokinetic study) or produce large amounts of raw data that may need to be associated with summary-level information (e.g., toxicity measurements for individual animals).

For these reasons, we have introduced additional tables to the ChEMBL schema, as well as making some minor adjustments to existing ones (see diagram above for more details).

A full copy of the ChEMBL_24 schema is available here.

Changes To Existing Tables

PUBLISHED_TYPE, PUBLISHED_RELATION, PUBLISHED_VALUE, PUBLISHED_UNITS column have been renamed to TYPE, RELATION, VALUE, UNITS. This is to reflect the changing nature of the ChEMBL data (as we receive increasing numbers of deposited data sets in addition to extracting 'published' data) and also to keep consistency with other new tables. However, the existing columns will also be retained for one release and then removed in ChEMBL_25. Therefore ChEMBL_24 will contain both PUBLISHED_TYPE and TYPE columns (for example), which will be populated with identical data. The STANDARD_TYPE/RELATION/VALUE/UNITS columns are not affected.
Several other new columns have been added to ACTIVITIES:

  • TEXT_VALUE (used for non-numeric/qualitative measurements, which were previously stored in ACTIVITY_COMMENT)
  • STANDARD_TEXT_VALUE (standardised version of TEXT_VALUE)
  • UPPER_VALUE (used with VALUE column to capture measurements that are ranges)
  • STANDARD_UPPER_VALUE (standardised version of UPPER_VALUE)
  • TOID (Test Occasion Identifier, used to group together related activity measurements)
  • SRC_ID (Indicates the source of the bioactivity data, link to SOURCE table)

The format of the ASSAY_PARAMETERS table has been changed to make it consistent with the ACTIVITIES table and allow standardisation of assay parameter data (e.g., conversion of units). The previous columns of PARAMETER_TYPE and PARAMETER_VALUE have been replaced with:

  • TYPE (equivalent to the previous PARAMETER_TYPE column)
  • VALUE (which will store numeric parameter values)
  • TEXT_VALUE (which will store non-numeric/qualitative parameter values)

There will also be an equivalent set of STANDARD columns, storing standardised parameter values (e.g., TIME in hr rather than minutes, concentrations in nM rather than M) and these should generally be used for querying.
This change means that parameters that were previously split across multiple rows will now be grouped together. For example, previously 'DOSE' and 'DOSE_UNITS' would have been recorded as two separate parameters with no explicit link. Now, since the UNITS column has been added, these will be merged to just a single parameter called 'DOSE'.

Table has been removed. Parameter types that have standardisation rules will be recorded in the ACTIVITY_STDS_LOOKUP table, as for activity types.

New Tables

As mentioned previously, it was not possible in the old ChEMBL schema to record experimental parameters associated with an individual activity measurement rather than the whole assay. We have therefore created an ACTIVITY_PROPERTIES table to store this information. The format of this table is very similar to the ACTIVITIES and ASSAY_PARAMETERS tables and it can be joined to the ACTIVITIES table using the ACTIVITY_ID. There is an additional column called RESULT_FLAG that allows users to distinguish between true parameters/independent variables (RESULT_FLAG = 0) and measured properties/dependent variables e.g., 'HILL_SLOPE' or 'MAX_EFFECT' (RESULT_FLAG = 1), the latter also being included in this table where they are important in interpreting activity values.

The ACTIVITY_SUPP table is provided to store supplementary activity data that may not be appropriate to include in the main ACTIVITIES table. For example, in cases where IC50 data are included in the ACTIVITIES table, the supplementary table may be used to store the associated % inhibition values. Similarly, where summary-level toxicology results are stored in the ACTIVITIES table, results for individual animals may be included in ACTIVITY_SUPP. This does not mean that all raw data should be captured in ChEMBL in future, but in cases where this is deemed important to users, there is now a facility to do this.
The format of the ACTIVITY_SUPP table is again similar to ACTIVITIES, ASSAY_PARAMETERS and ACTIVITY_PROPERTIES. The table also contains an RGID (Record Grouping ID) column, which is used to group together multiple related supplementary measurements, and an SMID column which is used to link to the ACTIVITIES table (via the new ACTIVITY_SMID and ACTIVITY_SUPP_MAP tables).

Data Deposition

In order to facilitate data deposition, changes have been made to the ChEMBL loading process and the data deposition format. More details of the required deposition format will be made available soon (and we also hope to provide a submission portal in future), but in the meantime if you wish to submit data to us, please contact us and we'll be happy to talk you through the process.
The improvements we've made allow us to update existing data sets (for example, where a depositor has additional activity measurements from a previously deposited assay and wants to add these to the database). They also allow a depositor to reference an existing set of compounds/assays and deposit data against them (for example, a different institution may deposit a set of assays/activities for compounds in the MMV Pathogen Box dataset).  There are a few schema changes associated with these improvements (and there are also a few business rules/assumptions that will change).  Firstly, key tables in ChEMBL now include a depositor defined ID:
  • RIDX - Reference identifier stored in the DOCS table
  • CIDX - Compound identifier stored in the COMPOUND_RECORDS table
  • AIDX - Assay identifier stored in the ASSAYS table
These IDs are used by data depositors to refer to their references/compounds/assays and should be stable between depositions e.g., if a CIDX such as 'CPD0001' appears in two datasets from the same depositor, it must refer to the same compound. In practice, these fields are mainly used for loading/maintaining data but may be used in queries if a user or depositor wants to retrieve data based on an externally assigned identifier such as a compound research code.
  • SRC_ID field has also been added to the DOCS and ACTIVITIES tables
This means that the DOCS table could become more redundant - if the same reference was provided by two different data sources, two separate documents would be created. In practice, this is not likely to happen very frequently.

An additional and important consequence of these changes is that is no longer necessarily the case that a given activity measurement, the assay it was measured in, and the compound that the activity was measured for will link to the same SRC_ID or DOC_ID. Queries should therefore take into account the SRC_ID and DOC_ID on all three tables (COMPOUND_RECORDS, ACTIVITIES and ASSAYS) when retrieving data. Please notes that in ChEMBL_24 there are no examples of this situation, but it is likely to occur in future releases.

Other Changes

A number of other minor schema changes have been made:

  • COMPOUND_PROPERTIES.NUM_ALERTS removed (for full set of structural alerts see COMPOUND_STRUCTURAL_ALERTS table)
  • MOLECULE_DICTIONARY.WITHDRAWN_CLASS added (high-level categories for WITHDRAWN_REASON e.g., Cardiotoxicity, Hepatotoxicity)

Data Changes

In addition to the schema changes outlined above, we have performed some software and data set updates that will affect some of the records in ChEMBL.

InChI version upgraded:
We have upgraded the version of Standard InChI used in ChEMBL to v1.05. This means the STANDARD_INCHI and STANDARD_INCHI_KEY in the COMPOUND_RECORDS table have changed for a very small number of compounds (16). Details of affected compounds will be provided with the release.

Compound properties now calculated with RDKit:
Fields in the COMPOUND_PROPERTIES table (with the exception of ACD_MOST_APKA, ACD_MOST_BPKA, ACD_LOGP and ACD_LOGD) are now calculated with RDKit, rather than BIOVIA Pipeline Pilot. Therefore some values may be slightly different from those in previous releases.

Reformatting of data sets:
Some previous data sets (Open TG-GATEs, DrugMatrix and Curated Drug Pharmacokinetic Data) have been re-formatted to take advantage of the new ACTIVITY_PROPERTIES and ACTIVITY_SUPP tables. This results in a smaller number of assays than before and as a result, assay and document CHEMBL_IDs for these data sets have changed. Please note that we may need to re-format some other legacy data in future releases, for example to re-group assays that have previously been split to accommodate different doses/time points, or to migrate results captured in the ACTIVITY_COMMENT field to the STANDARD_TEXT_VALUE field.

While we've described a lot of improvements here, most of these changes will not break existing code/queries (unless you're using the NUM_ALERTS property or querying the ASSAY_PARAMETERS table) for this release. However, it's worth taking note of the changes in the underlying data model (particularly if you are using the tox data sets or querying for particular sources) and checking that any assumptions still hold true.

If you need any more information about any of these changes please feel free to contact us and we'll do our best to help!

Tuesday, 17 April 2018

Join the ChEMBL Team!

We are looking for talented individuals to help us maintain and develop the ChEMBL and SureChEMBL resources and currently have a number of open positions within the team. If you are looking for an exciting new role and would like to work with us on the beautiful Wellcome Genome Campus, here are details of the positions:

Data Integration Scientist

We are looking for a Scientist with a passion for data integration to manage the incorporation of drug discovery data into the ChEMBL database.

Responsibilities will include:
  • Responsibility for the handling, processing and integration of data into the ChEMBL database.
  • Facilitating the deposition of datasets directly into ChEMBL through working with external collaborators.
  • Applying text- & data-mining techniques for the development of effective large-scale curation strategies.
  • Developing methods for the application and maintenance of ontologies in ChEMBL.
  • Working with other teams to facilitate the integration of data between different EBI resources.

Essential requirements include:
  • A BSc (or equivalent) in a life-science subject (e.g. biological or biomedical sciences).
  • 3+ years of postgraduate experience in scientific application development, database development or text- & data-mining, with a demonstrable track record of achievement.
  • Proficient in at least one programming/scripting language (Python knowledge is highly desirable).
  • Good knowledge of relational databases, data modelling, SQL and PL/SQL, and RESTful web-services.
  • Experience in integrating diverse data sets.

For full details and to apply for the position, please visit the EMBL website:

Software Engineer - Dev Ops

We are seeking a talented Software Engineer/Dev Ops Developer to work on SureChEMBL, one of the largest live resources of chemistry extracted from patent data.

Responsibilities will include:
  • Maintaining and improving the SureChEMBL system;
  • Building new monitoring tools and dashboards;
  • Developing new functionalities in collaboration with colleagues and collaborators;
  • Profiling and scaling the cloud-based IaaS patent processing pipeline;
  • Optimizing the application stack for maximum speed and scalability;

Essential requirements include:
  • A minimum of 3 years of professional development experience;
  • Strong core Java Enterprise Edition development skills and understanding of Java design principles;
  • Experience of defining and creating Continuous Integration and Development environments using technologies such as Jenkins, Maven, Artifactory;
  • A solid understanding of the Open Stack platform;
  • Experience with distributed queue messaging (e.g. Amazon SQS, RabbitMQ)
  • Experience with relational databases (mySQL, PostgreSQL);
  • A solid foundation in computer science, with strong competencies in concurrency, shell scripting, and software design;

For full details and to apply for the position, please visit the EMBL website:

Software Engineer - Web Developer

We require a passionate Web Developer who can design and develop robust solutions that deliver ChEMBL data to our extensive user community.

Responsibilities will include:
  • Developing web-based solutions to better deliver ChEMBL resources to users
  • Maintaining and further developing the infrastructure that supports interfaces on chemogenomics data
  • Working with other members of the team, collaborators and users to develop and deliver new and innovative ways to analyse and visualise ChEMBL data
  • Integrating chemogenomics data with that from other relevant resources at the EBI and beyond
  • Keeping up-to-date with relevant developments in the field of web development

Essential requirements include:
  • A BSc (or equivalent) in a technical subject (e.g. life science, computing or mathematics)
  • 3+ years postgraduate experience in front-end software development with a demonstrable track record of delivery
  • Sound programming skills, including experience of Unix and Python
  • Experience in building and using web services and good knowledge of current web technologies;
  • Knowledge of search technologies e.g. Solr/Elastic
  • Knowledge of relational databases, SQL PL/SQL and NoSQL approaches
  • Evidence of good practice in software engineering to deliver clean, extensible and robust code through rapid development cycles with documentation and version control

For full details and to apply for the position, please visit the EMBL website: