IQL [1] - The Basics
IQL
IQL is a meta-language we (Iqmo) developed to provide a portable SQL interface and extensions for data sources like BQL.
We love SQL: in-memory OLAP engines have come a long way, allowing us to solve large analysis problems with clear, (mostly) portable query language. I’m sure some developers will hate me for calling SQL clear, but I find its concise declarative syntax far easier to work with for large-scale problems.
In this post, I’ll demonstrate the basics of IQL. More posts will follow on some advanced topics.
IQL defaults to a DuckDB backend, meaning that the SQL you write will be DuckDB-compatible SQL (forked from PostgreSQL’s syntax) with some IQL extensions.
BQuant Example
Prerequisite
To run this example, follow the steps in BQuant - Opening Blog Examples to create a Custom Environment that includes both nbappinator and iql and the notebook code below.
bqnt_iql_magics.ipynb
In [27]:
import os
%load_ext iql.jupyter_magics.iql_magic
The iql.jupyter_magics.iql_magic extension is already loaded. To reload it, use: %reload_ext iql.jupyter_magics.iql_magic
Cell and Line Magics¶
You can use %iql for a one-line command, or %%iql at the beginning of a cell for a long query.
This is the same as calling: iql.execute(...)
In [28]:
# %iql is called a "line magic" in Jupyter. %%iql is a cell magic
df = %iql select * from range(10)
display(df)
| range | |
|---|---|
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
Multiple Statements Separated by Semicolons¶
In [29]:
%%iql
create table abc as select * from range(10);
select * from abc where range > 5
Out[29]:
| range | |
|---|---|
| 0 | 6 |
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |
BQL can be executed on the fly¶
Use -o to store the output in a variable
In [30]:
%%iql -o my_bql_df
get(
px_last
) for(
['IBM US Equity']
) with(
dates=range(-29d, 0d),
fill=prev,
currency=USD
)
Out[30]:
| ID | DATE | name | value | |
|---|---|---|---|---|
| 0 | IBM US Equity | 2023-05-29 | px_last | 6.080166 |
| 1 | IBM US Equity | 2023-05-30 | px_last | 7.047133 |
| 2 | IBM US Equity | 2023-05-31 | px_last | 2.618075 |
| 3 | IBM US Equity | 2023-06-01 | px_last | 3.299787 |
| 4 | IBM US Equity | 2023-06-02 | px_last | 9.463090 |
| 5 | IBM US Equity | 2023-06-03 | px_last | 3.664822 |
| 6 | IBM US Equity | 2023-06-04 | px_last | 6.589516 |
| 7 | IBM US Equity | 2023-06-05 | px_last | 7.096633 |
| 8 | IBM US Equity | 2023-06-06 | px_last | 9.778978 |
| 9 | IBM US Equity | 2023-06-07 | px_last | 9.911424 |
| 10 | IBM US Equity | 2023-06-08 | px_last | 7.871942 |
| 11 | IBM US Equity | 2023-06-09 | px_last | 4.101196 |
| 12 | IBM US Equity | 2023-06-10 | px_last | 1.012025 |
| 13 | IBM US Equity | 2023-06-11 | px_last | 9.740720 |
| 14 | IBM US Equity | 2023-06-12 | px_last | 8.761841 |
| 15 | IBM US Equity | 2023-06-13 | px_last | 6.922619 |
| 16 | IBM US Equity | 2023-06-14 | px_last | 8.843994 |
| 17 | IBM US Equity | 2023-06-15 | px_last | 3.553850 |
| 18 | IBM US Equity | 2023-06-16 | px_last | 6.121285 |
| 19 | IBM US Equity | 2023-06-17 | px_last | 0.345041 |
| 20 | IBM US Equity | 2023-06-18 | px_last | 4.515255 |
| 21 | IBM US Equity | 2023-06-19 | px_last | 4.618648 |
| 22 | IBM US Equity | 2023-06-20 | px_last | 4.579350 |
| 23 | IBM US Equity | 2023-06-21 | px_last | 6.196254 |
| 24 | IBM US Equity | 2023-06-22 | px_last | 6.375983 |
| 25 | IBM US Equity | 2023-06-23 | px_last | 8.924309 |
| 26 | IBM US Equity | 2023-06-24 | px_last | 8.641288 |
| 27 | IBM US Equity | 2023-06-25 | px_last | 3.784876 |
| 28 | IBM US Equity | 2023-06-26 | px_last | 9.505375 |
| 29 | IBM US Equity | 2023-06-27 | px_last | 9.998839 |
%%capture to Suppress Display¶
In [31]:
%%capture
%%iql -o my_bql_df
get(
px_last
) for(
['IBM US Equity']
) with(
dates=range(-29d, 0d),
fill=prev,
currency=USD
)
Now for interesting stuff: SQL + BQL¶
Nothing too fancy, just ordering the results
In [32]:
%%iql -o my_bql_df
get(
px_last
) for(
['IBM US Equity']
) with(
dates=range(-29d, 0d),
fill=prev,
currency=USD
)
Out[32]:
| ID | DATE | name | value | |
|---|---|---|---|---|
| 0 | IBM US Equity | 2023-05-29 | px_last | 6.080166 |
| 1 | IBM US Equity | 2023-05-30 | px_last | 7.047133 |
| 2 | IBM US Equity | 2023-05-31 | px_last | 2.618075 |
| 3 | IBM US Equity | 2023-06-01 | px_last | 3.299787 |
| 4 | IBM US Equity | 2023-06-02 | px_last | 9.463090 |
| 5 | IBM US Equity | 2023-06-03 | px_last | 3.664822 |
| 6 | IBM US Equity | 2023-06-04 | px_last | 6.589516 |
| 7 | IBM US Equity | 2023-06-05 | px_last | 7.096633 |
| 8 | IBM US Equity | 2023-06-06 | px_last | 9.778978 |
| 9 | IBM US Equity | 2023-06-07 | px_last | 9.911424 |
| 10 | IBM US Equity | 2023-06-08 | px_last | 7.871942 |
| 11 | IBM US Equity | 2023-06-09 | px_last | 4.101196 |
| 12 | IBM US Equity | 2023-06-10 | px_last | 1.012025 |
| 13 | IBM US Equity | 2023-06-11 | px_last | 9.740720 |
| 14 | IBM US Equity | 2023-06-12 | px_last | 8.761841 |
| 15 | IBM US Equity | 2023-06-13 | px_last | 6.922619 |
| 16 | IBM US Equity | 2023-06-14 | px_last | 8.843994 |
| 17 | IBM US Equity | 2023-06-15 | px_last | 3.553850 |
| 18 | IBM US Equity | 2023-06-16 | px_last | 6.121285 |
| 19 | IBM US Equity | 2023-06-17 | px_last | 0.345041 |
| 20 | IBM US Equity | 2023-06-18 | px_last | 4.515255 |
| 21 | IBM US Equity | 2023-06-19 | px_last | 4.618648 |
| 22 | IBM US Equity | 2023-06-20 | px_last | 4.579350 |
| 23 | IBM US Equity | 2023-06-21 | px_last | 6.196254 |
| 24 | IBM US Equity | 2023-06-22 | px_last | 6.375983 |
| 25 | IBM US Equity | 2023-06-23 | px_last | 8.924309 |
| 26 | IBM US Equity | 2023-06-24 | px_last | 8.641288 |
| 27 | IBM US Equity | 2023-06-25 | px_last | 3.784876 |
| 28 | IBM US Equity | 2023-06-26 | px_last | 9.505375 |
| 29 | IBM US Equity | 2023-06-27 | px_last | 9.998839 |
Pivoting multiple fields for a time series result¶
In [33]:
%load_ext iql.jupyter_magics.iql_magic
The iql.jupyter_magics.iql_magic extension is already loaded. To reload it, use: %reload_ext iql.jupyter_magics.iql_magic
Cell and Line Magics¶
You can use %iql for a one-line command, or %%iql at the beginning of a cell for a long query.
This is the same as calling: iql.execute(...)
In [34]:
# %iql is called a "line magic" in Jupyter. %%iql is a cell magic
df = %iql select * from range(10)
display(df)
| range | |
|---|---|
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
Multiple Statements Separated by Semicolons¶
In [35]:
%%iql
create table abc as select * from range(10);
select * from abc where range > 5
Out[35]:
| range | |
|---|---|
| 0 | 6 |
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |
BQL can be executed on the fly¶
Use -o to store the output in a variable
In [36]:
%%iql -o my_bql_df
get(
px_last
) for(
['IBM US Equity']
) with(
dates=range(-29d, 0d),
fill=prev,
currency=USD
)
Out[36]:
| ID | DATE | name | value | |
|---|---|---|---|---|
| 0 | IBM US Equity | 2023-05-29 | px_last | 6.080166 |
| 1 | IBM US Equity | 2023-05-30 | px_last | 7.047133 |
| 2 | IBM US Equity | 2023-05-31 | px_last | 2.618075 |
| 3 | IBM US Equity | 2023-06-01 | px_last | 3.299787 |
| 4 | IBM US Equity | 2023-06-02 | px_last | 9.463090 |
| 5 | IBM US Equity | 2023-06-03 | px_last | 3.664822 |
| 6 | IBM US Equity | 2023-06-04 | px_last | 6.589516 |
| 7 | IBM US Equity | 2023-06-05 | px_last | 7.096633 |
| 8 | IBM US Equity | 2023-06-06 | px_last | 9.778978 |
| 9 | IBM US Equity | 2023-06-07 | px_last | 9.911424 |
| 10 | IBM US Equity | 2023-06-08 | px_last | 7.871942 |
| 11 | IBM US Equity | 2023-06-09 | px_last | 4.101196 |
| 12 | IBM US Equity | 2023-06-10 | px_last | 1.012025 |
| 13 | IBM US Equity | 2023-06-11 | px_last | 9.740720 |
| 14 | IBM US Equity | 2023-06-12 | px_last | 8.761841 |
| 15 | IBM US Equity | 2023-06-13 | px_last | 6.922619 |
| 16 | IBM US Equity | 2023-06-14 | px_last | 8.843994 |
| 17 | IBM US Equity | 2023-06-15 | px_last | 3.553850 |
| 18 | IBM US Equity | 2023-06-16 | px_last | 6.121285 |
| 19 | IBM US Equity | 2023-06-17 | px_last | 0.345041 |
| 20 | IBM US Equity | 2023-06-18 | px_last | 4.515255 |
| 21 | IBM US Equity | 2023-06-19 | px_last | 4.618648 |
| 22 | IBM US Equity | 2023-06-20 | px_last | 4.579350 |
| 23 | IBM US Equity | 2023-06-21 | px_last | 6.196254 |
| 24 | IBM US Equity | 2023-06-22 | px_last | 6.375983 |
| 25 | IBM US Equity | 2023-06-23 | px_last | 8.924309 |
| 26 | IBM US Equity | 2023-06-24 | px_last | 8.641288 |
| 27 | IBM US Equity | 2023-06-25 | px_last | 3.784876 |
| 28 | IBM US Equity | 2023-06-26 | px_last | 9.505375 |
| 29 | IBM US Equity | 2023-06-27 | px_last | 9.998839 |
Pivot Multi-Field Data¶
In [37]:
%%iql -o my_bql_df
select * from bql("
get(
px_last
) for(
['IBM US Equity']
) with(
dates=range(-29d, 0d),
fill=prev,
currency=USD
)
", pivot=((date, id), name, value))
order by date desc
limit 3
Out[37]:
| DATE | ID | px_last | |
|---|---|---|---|
| 0 | 2023-06-27 | IBM US Equity | 9.998839 |
| 1 | 2023-06-26 | IBM US Equity | 9.505375 |
| 2 | 2023-06-25 | IBM US Equity | 3.784876 |
Or, pivot to one column per security¶
In [38]:
%%iql -o my_bql_df
--- pivot=(rows, columns, value field)
select * from bql("
get(
px_last
) for(
['IBM US Equity']
) with(
dates=range(-29d, 0d),
fill=prev,
currency=USD
)
", pivot=(date, id, value))
limit 3
Out[38]:
| DATE | IBM_US_Equity | |
|---|---|---|
| 0 | 2023-05-29 | 6.080166 |
| 1 | 2023-05-30 | 7.047133 |
| 2 | 2023-05-31 | 2.618075 |
In [39]:
if "BQUANT_USERNAME" not in os.environ:
print("The remaining cells must be run in Bquant")
assert False
The remaining cells must be run in Bquant
--------------------------------------------------------------------------- AssertionError Traceback (most recent call last) Cell In[39], line 3 1 if "BQUANT_USERNAME" not in os.environ: 2 print("The remaining cells must be run in Bquant") ----> 3 assert False AssertionError:
Other Examples¶
In [ ]:
%%iql
---- Ranking
SELECT *, rank() over (partition by month(date) order by px_high) ranking
FROM bql("
get (px_open, px_high, px_low, px_last, px_volume/1M as #px_volume)
for ('IBM US Equity')
with (dates=range(-1Y, 0D), fill=prev)
", pivot=(auto)) as data
ORDER BY date
In [ ]:
%%iql
---- Skew and Kurtosis
SELECT id,
avg(px_last) avg_px_last,
kurtosis(px_last) kurt_px_last,
count(*) num_observations
FROM bql("
get(px_last(dates=range(-10D, 0D), fill=prev) as #px_last)
for (['TSLA US Equity','IBM US Equity'])
", pivot=auto)
GROUP BY id
In [ ]:
%%iql
---- Price change vs peers
SELECT c.id, c.country_iso, c.industry_subgroup, c.cur_mkt_cap, c.px_change, p.id, p.country_iso, p.industry_subgroup, p.cur_mkt_cap, p.px_change, c.px_change - p.px_change px_change_delta
FROM
(SELECT *, (px_last - px_last_1y) / px_last_1y as px_change
-- px_change could also be calculated in BQL via pct_diff()
FROM
bql("
get(name, COUNTRY_ISO, industry_subgroup, cur_mkt_cap/1M as #cur_mkt_cap, px_last, px_last(dates=-1Y) as #px_last_1y) for (peers('IBM US Equity')) with (currency=USD)
", pivot=(id,name), dropna=True)
) as p
JOIN
(SELECT *, (px_last - px_last_1y) / px_last_1y as px_change
FROM
bql("
get(name, COUNTRY_ISO, industry_subgroup, cur_mkt_cap/1M as #cur_mkt_cap, px_last, px_last(dates=-1Y) as #px_last_1y) for ('IBM US Equity') with (currency=USD)
", pivot=(id,name))
) as c
-- c always returns one row
ON true ORDER BY px_change_delta desc
In [ ]:
%%iql
---- With CTES
WITH c AS (select * from bql("get(px_last) for('IBM US Equity') with(fill=prev, currency=USD)")),
idx AS (select * from bql("get(px_last) for ('SPX Index') with(fill=prev, currency=USD)"))
SELECT c.id c, c.value as c_px, idx.id idx, idx.value as idx_px FROM c, idx
In [ ]:
%%iql
--- You can create tables in the in-memory database
--- These are deleted. You can also initialize the database with on-disk storage
CREATE TABLE temp_table_1 as
(SELECT id from bql("get(px_volume) for(translatesymbols(members('SPX Index'), targetidtype=FUNDAMENTALTICKER)) with(currency=USD)", pivot=(id, name))
ORDER BY px_volume desc limit 10)
;
SELECT id, name, px_last, px_volume/1000000 as px_volume FROM bql('get(name, px_last, px_volume) for("$SECURITY")', paramquery=('$SECURITY', 'select * from temp_table_1 limit 2'), pivot=(id, name))