A case study on ETL and research data management.
OpenAlex is a public bibliometrics database that contains many millions of records related to academic journal articles, authors, citations, and other research outputs and indicators related to scholarly publishing. This data is useful for doing research about research, or at least about the communication of research findings and how they interrelate. If you want to know how many papers are published in a field each year, which paper has the largest number of contributors, or who the most cited author is at a given institution, this is the kind of dataset that will answer those questions.
SQL is expressive; it’s hard to do this kind of thing using an API or a web interface. The interfaces available for OpenAlex and other bibliometric databases make certain types of questions easy to answer, like the ones above. Other questions may require additional processing of the data to answer. In my case, I wanted to compute the h-index (an indicator of an author’s impact on their field through their research) and compare it to their collaboration diversity (how many different researchers did they work with). Calculating h-index and collaboration diversity both require computationally-expensive operations, and the APIs available don’t often provide the level of granularity and breadth necessary to answer these more complex questions. Structured Query Language (SQL) provides a well-defined, expressive syntax for performing these kinds of calculations and analyses.
OpenAlex does not provide an interface for using SQL. Luckily, they do provide a way to download their entire 1.7tb (at time of writing) dataset. That data can be loaded into a database engine like PostgreSQL that does support SQL. So the first step in the process is to download all of the data, which took me several days. I am grateful to OpenAlex for making their entire dataset available this way. Most bibliometric databases require paid memberships to access, and even then only provide a limited API or search interface to access the data contained within them.
A note about data types. Computers are really good at computing and comparing numbers. Dealing with text is significantly slower because it requires more computation. The OpenAlex dataset as they provide it includes unique identifiers for authors, works, institutions and so on. However, those identifiers are provided in the form of URLs like https://openalex.org/A5035982093, a text format clocking in at 32 bytes, rather than a numeric format like 5035982093 that can be stored in 8 bytes. If left in this state, the queries I wanted to run would take several orders of magnitude longer to process.
Luckily, we have an opportunity. Importing the data into PostgreSQL is most efficient when the data is already in tabular format, eg with CSV data. The OpenAlex data snapshot has semi-structured JSON. Converting from one form to the other is relatively straightforward, though time consuming. This provides an excellent opportunity for data transformation. Since the data format conversion is already processing the data, we can do things like filter out rows we know we won’t need (less data to import) and change data types. Replacing the textual IDs in the original data with numeric versions is easy to do at this stage and will save a lot of time later.
The next step of the process is to take the processed CSV files and import them into the database using bulk COPY commands. It’s better to create indices (to improve query performance) after all of the data has been loaded into the tables. Be sure you only add indices that will benefit the performance of your queries; otherwise you’re just wasting processing power and disk space. If you need to find a subset of rows, for example a set of works published within a certain date range or by a specific author, indices will have much better performance because they avoid looking at every row of the table.
Now we can run complex queries. To calculate h-index and number of collaborators, for example, one can write a query like this:
with x as (
select author_aid,
rank() over ( partition by author_aid order by cited_by_count desc) as rank,
cited_by_count >= rank() over ( partition by author_aid order by cited_by_count desc) as fger
from openalex.works_authorships wa
join openalex.works w
on wa.work_wid = w.wid
), h as (
select author_aid, max(rank) as h_index from x where fger group by 1
)
select a.author_aid,
h.h_index,
count(distinct b.author_aid) as d_distinct,
count(b.author_aid) as d_nondistinct
from openalex.works_authorships a
join openalex.works_authorships b on a.work_wid=b.work_wid and a.author_aid <> b.author_aid
join h on a.author_aid=h.author_aid
group by 1,2;I doubt you would be able to do an analysis this complex using the APIs provided by OpenAlex or other bibliometric database providers. It takes a lot of computing power to process all of the data, both for loading it into a SQL database and to run the queries. But if you have a problem, if no API can help, and if you can hack it, maybe you can hire the SQL-Team.