I'm sharing from personal experience what was useful, where, and when. This is an overview and outline, so it's clear what to explore further — but this is purely my subjective personal experience, yours may be completely different.
Why is it important to know and be able to work with query languages? In Data Science, there are several crucial stages of work, and the first and most important one (without which nothing will work!) is acquiring or extracting data. Most often, data sits somewhere in some form, and it needs to be 'pulled out' from there.
Query languages are exactly what allows us to extract that data! Today, I will discuss the query languages that have been useful to me and show where and how exactly — why it's necessary for study.
There will be three main blocks of types of data queries that we will cover in this article:
- ‘Standard’ query languages — what is typically understood when talking about query languages, such as relational algebra or SQL.
- Scripting query languages: for example, Python tools like pandas, numpy, or shell scripting.
- Query languages for knowledge graphs and graph databases.
Everything written here is simply personal experience of what was useful, describing situations and 'why it was needed' — everyone can find out how similar situations might arise for them and try to prepare for them in advance by understanding these languages before having to apply them urgently on a project or actually encountering a project where they are needed.
‘Standard’ query languages
Standard query languages in the sense that these are typically what we think of when we talk about queries.
Relational algebra
Why is relational algebra needed today? To have a good understanding of why query languages are structured in a certain way and to use them consciously, one needs to understand the core that underlies them.
What is relational algebra?
The formal definition is: relational algebra — a closed system of operations on relations in the relational data model. In more human terms, it is a system of operations on tables such that the result is always a table.
See all relational operations in the article from Habr — here we describe why it's important to know and where it comes in handy.
Why?
You start to understand what query languages are made of and what operations lie behind the expressions of specific query languages — this often provides a deeper understanding of what works and how in query languages.
Taken from articles. An example of an operation: join, which combines tables.
Study materials:
. In general, there is a wealth of materials on relational algebra and theory — platforms like Coursera, Udacity. There are also a plethora of online materials, including some excellent . My personal advice: you need to understand relational algebra very well — it’s the foundation.
SQL
Taken from articles.
SQL is essentially an implementation of relational algebra — with an important caveat, SQL is declarative! That is, by writing a query in relational algebra, you are effectively saying how to calculate — but with SQL, you specify what you want to retrieve, and then the DBMS generates (efficient) expressions in relational algebra (their equivalence is known to us as ).
Taken from articles.
Why?
Relational DBMS: Oracle, Postgres, SQL Server, etc. — are still essentially everywhere and there is a huge chance that you will have to interact with them, which means you will either need to read SQL (which is very likely) or write it (also not unlikely).
What to read and study
From the same links above (on relational algebra), there is an incredible amount of material, for example, .
By the way, what is NoSQL?
It should be emphasized again that the term "NoSQL" has absolutely spontaneous origins and has no widely recognized definition or scientific institution behind it. The corresponding on Habr.
In essence, people have realized that a complete relational model is not needed to solve many problems, especially those where, for example, performance is critical and certain simple aggregation queries dominate — it’s crucial to compute metrics quickly and write them to the database, and most relational features turned out to be not only unnecessary but also detrimental — why normalize something if it will spoil the most important thing for us (for a specific task) — performance?
Flexible schemes instead of fixed mathematical schemas from the classical relational model are often necessary — and this simplifies application development immensely, especially when it’s critical to deploy a system and start processing results quickly — or when the schema and types of stored data are not particularly important.
For example, we are creating an expert system and want to store information for a specific domain along with some metadata — we might not know all the fields and can simply store JSON for each record — this gives us a very flexible environment for extending the data model and rapid iteration — so in this case, NoSQL may even be preferable and more readable. Here’s an example of a record (from one of my projects where NoSQL was exactly what was needed).
{"en_wikipedia_url":"https://en.wikipedia.org/wiki/Johnny_Cash",
"ru_wikipedia_url":"https://ru.wikipedia.org/wiki/?curid=301643",
"ru_wiki_pagecount":149616,
"entity":[42775,"Johnny Cash","ru"],
"en_wiki_pagecount":2338861}
You can read more about NoSQL.
What to study?
Here it’s more important to carefully analyze your task, what its properties are and which NoSQL systems might fit this description — and then focus on studying the chosen system.
Scripting query languages
At first, it seems strange why Python is relevant at all — it’s a programming language, not about queries at all.
- Pandas is like the Swiss Army knife of Data Science, with a huge amount of data transformations, aggregations, and so on occurring within it.
- Numpy handles vector calculations, matrices, and linear algebra.
- Scipy has a lot of mathematics, especially statistics, in this package.
- Jupyter lab fits exploratory data analysis really well in notebooks — it's useful to know how to use it.
- Requests are for network operations.
- Pyspark is very popular among data engineers; you will likely have to interact with it or Spark simply due to its popularity.
- *Selenium is very useful for web scraping and data collection; sometimes, it’s the only way to obtain the data.
My main advice: learn Python!
Pandas
Let’s take the following code as an example:
import pandas as pd
df = pd.read_csv("data/dataset.csv")
# Calculate and rename aggregations
all_together = (df[df['trip_type'] == "return"]
.groupby(['start_station_name','end_station_name'])
.agg({'trip_duration_seconds': [np.size, np.mean, np.min, np.max]})
.rename(columns={'size': 'num_trips',
'mean': 'avg_duration_seconds',
'amin': 'min_duration_seconds',
'amax': 'max_duration_seconds'}))Essentially, we see that the code fits into a classic SQL pattern.
SELECT start_station_name, end_station_name, count(trip_duration_seconds) as size, …..
FROM dataset
WHERE trip_type = 'return'
GROUP BY start_station_name, end_station_nameBut an important part is that this code is part of a script and pipeline; in fact, we are embedding queries into a Python pipeline. In this situation, the query language comes to us from libraries such as Pandas or pySpark.
Overall, in pySpark we see a similar type of data transformation through the query language in the spirit of:
df.filter(df.trip_type = 'return')
.groupby('day')
.agg({duration: 'mean'})
.sort()Where to read and what to read
About Python itself finding materials for study. There are a huge number of tutorials online about , and courses on (as well as on ). In general, the materials here are excellently searchable, and if I had to choose one package to focus on, it would be pandas, of course. There are also .
Shell as a query language
Many projects I worked on for data processing and analysis are essentially shell scripts that invoke code in Python, Java, and the shell commands themselves. Therefore, pipelines in bash/zsh/etc. can be considered a high-level query (you can, of course, include loops there, but this is atypical for DS code in shell languages). Here's a simple example — I needed to make a mapping of QID from Wikidata and full links to the Russian and English wikis; for this, I wrote a simple bash command request and for output wrote a simple script in Python, which I combined like this:
pv 'data/latest-all.json.gz' |
unpigz -c |
jq --stream $JQ_QUERY |
python3 scripts/post_process.py 'output.csv'
where
JQ_QUERY = 'select((.[0][1] == "sitelinks" and (.[0][2]=="enwiki" or .[0][2] =="ruwiki") and .[0][3] =="title") or .[0][1] == "id")' This was, in essence, the entire pipeline that created the required mapping; as we see, everything worked in streaming mode:
- pv filepath — gives a progress bar based on file size and forwards its contents
- unpigz -c reads part of the archive and passes it to jq
- jq with the stream key immediately outputs the result and passes it to the post-processor (just like with the very first example) in Python
- inside the post-processor — it's a simple state machine that formatted the output
In summary, a complex pipeline operating in stream mode on large data (0.5TB), without significant resources, is made from a simple pipeline and a couple of tools.
Another important tip: be able to work well and efficiently in the terminal and write in bash/zsh/etc.
Where will it be useful? Almost everywhere—there are A LOT of materials to study online. In particular, here is my previous article.
R scripting
Once again, the reader may exclaim—well, this is a whole programming language! And of course, they will be right. However, I have usually encountered R in contexts where it was very similar to a query language.
R is a statistical computing environment and a language for statistical computing and visualization (according to ).
Taken . By the way, I recommend it, it's a decent resource.
Why should a data scientist know R? At least because there is a large group of non-IT people who analyze data in R. I have encountered it in the following fields:
- The pharmaceutical sector.
- Biologists.
- The financial sector.
- People with purely mathematical education who are engaged in statistics.
- Specialized statistical models and machine learning models (which can often only be found in custom versions as R packages).
Why is this essentially a query language? In the form where it is commonly found—it is essentially a request to create a model, including reading data and specifying the parameters of the request (model), as well as visualizing data in packages like ggplot2—this is also a form of writing queries.
Example queries for visualization
ggplot(data = beav,
aes(x = id, y = temp,
group = activ, color = activ)) +
geom_line() +
geom_point() +
scale_color_manual(values = c("red", "blue"))In general, many ideas from R have migrated to Python packages such as pandas, numpy, or scipy, like dataframes and data vectorization—therefore, many things in R will seem familiar and convenient to you.
There are many sources to study from, for example, .
Knowledge graphs
Here I have somewhat unusual experience because I often have to work with knowledge graphs and query languages for graphs. Therefore, we will briefly go over the basics, as this part is somewhat more exotic.
In classic relational databases, we have a fixed schema — here, the schema is flexible; each predicate is essentially a 'column' and even more.
Imagine you are modeling a person and want to describe key aspects, let’s use the specific person Douglas Adams as an example.
If we were using a relational database, we would have to create a huge table or tables with a vast number of columns, most of which would be NULL or filled with some default False value; for example, it’s unlikely many of us have a record in the national Korean library — of course, we could separate them into distinct tables, but that would ultimately be an attempt to simulate a flexible logical schema with predicates using a fixed relational model.
So, imagine that all data is stored in the form of a graph or as binary and unary logical expressions.
Where can you encounter something like this? First, when working with , and with any graph databases or linked data.
Next are the main query languages I have had to apply and work with.
SPARQL
Wiki:
SPARQL ( from SPARQL Protocol and RDF Query Language) — , presented in the model of , as well as for transmitting these queries and responses. SPARQL is a recommendation by the and one of the technologies of the .
Essentially, it is a query language for logical unary and binary predicates. You just conditionally specify what is fixed in the logical expression and what is not (very simplistically).
The RDF database (Resource Description Framework), on which SPARQL queries are executed, consists of triples object, predicate, subject — and the query selects the required triples based on specified constraints like: find such X that p_55(X, q_33) holds true — where, of course, p_55 is some relation with id 55, and q_33 is the object with id 33 (that’s the whole point, again skipping various details).
Example of data representation:
Images and an example with countries here .
Example of a basic query
Essentially, we want to find the value of variable ?country such that for the predicate
member_of, it holds that member_of(?country,q458), while q458 is the ID of the European Union.
Example of a real SPARQL query within the Python engine:
Generally, I needed to read SPARQL rather than write it — in such situations, it is likely useful to understand the language at least at a basic level to grasp how data is extracted.
There are many online resources for learning: for example, here is one. and I usually Google specific constructs and examples, and that’s been enough for me so far.
Logical Query Languages
You can read more on the topic in my article. . Here, we will briefly discuss why logical languages are well-suited for writing queries. Essentially, RDF is just a set of logical statements of the form p(X) and h(X,Y), while a logical query looks like this:
output(X) :- country(X), member_of(X,"EU").
Here we define a new predicate output/1 (/1 means unary) under the condition that for X, it holds that country(X) — i.e., X is a country and also member_of(X,"EU").
Thus, both the data and the rules are represented in the same way, allowing for easy and effective modeling of tasks.
Where it has appeared in the industry: a large project with a company that writes queries in such a language, as well as in the current project at the core of the system — seemingly a rather exotic thing, yet it does sometimes come up.
Example of a code snippet in a logical language processing wikidata:
Materials: I'll provide a couple of links to the modern logical programming language Answer Set Programming — I recommend studying this particular language:
Source: habr.com
