Configuration and Usage¶

Normally IPython only displays the output of the last statement. However it can be handy to run multiple sql magics in a single cell and see the output of each execution. Setting ast_node_interactivity to all will enable that.

In [1]:

# Display all cell outputs in notebook
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = 'all'

# Display all cell outputs in notebook from IPython.core.interactiveshell import InteractiveShell InteractiveShell.ast_node_interactivity = 'all'

In [2]:

%load_ext jupyterlab_sql_editor.ipython_magic.trino

%load_ext jupyterlab_sql_editor.ipython_magic.trino

In [3]:

%config Trino.host='localhost'
%config Trino.port=8080
%config Trino.httpScheme='http'
%config Trino.auth=None
%config Trino.user='the-user'

%config Trino.cacheTTL=3600
%config Trino.outputFile="/tmp/trinodb.schema.json"

# comma seperated list of schema to cache in the schema file
%config Trino.catalogs="system,tpch"

%config Trino.host='localhost' %config Trino.port=8080 %config Trino.httpScheme='http' %config Trino.auth=None %config Trino.user='the-user' %config Trino.cacheTTL=3600 %config Trino.outputFile="/tmp/trinodb.schema.json" # comma seperated list of schema to cache in the schema file %config Trino.catalogs="system,tpch"

In production environment you will want to pass in an authentiction

import trino
    %config Trino.auth=trino.auth.BasicAuthentication("principal id", "password")
    %config Trino.user=None

See https://github.com/trinodb/trino-python-client/blob/master/trino/auth.py for more details

In [4]:

%trino --refresh all

%trino --refresh all

Exporting functions: [########################################] 100.0%
Schema file updated: /tmp/trinodb.schema.json

In [5]:

%trino SELECT 'hello'

%trino SELECT 'hello'

_col0

hello

In [6]:

#%trino SHOW CATALOGS

#%trino SHOW CATALOGS

In [7]:

%%trino --limit 2 --output sql
SELECT *
FROM
    tpch.tiny.orders AS ord

%%trino --limit 2 --output sql SELECT * FROM tpch.tiny.orders AS ord

Out[7]:

SELECT *
FROM
    tpch.tiny.orders AS ord

In [8]:

%%trino --limit 2 --dataframe x --output grid
SELECT
    ord.orderkey,
    ord.custkey,
    ord.orderstatus,
    ord.totalprice,
    ord.orderdate,
    ord.orderpriority,
    ord.clerk,
    ord.shippriority,
    ord.comment
FROM
    tpch.tiny.orders AS ord

%%trino --limit 2 --dataframe x --output grid SELECT ord.orderkey, ord.custkey, ord.orderstatus, ord.totalprice, ord.orderdate, ord.orderpriority, ord.clerk, ord.shippriority, ord.comment FROM tpch.tiny.orders AS ord

Saved results to pandas dataframe named `x`

DataGrid(auto_fit_params={'area': 'all', 'padding': 30, 'numCols': None}, corner_renderer=None, default_render…

Only showing top 2 row(s)

In [9]:

%%trino --catalog tpch --schema sf1000

SELECT * FROM lineitem

%%trino --catalog tpch --schema sf1000 SELECT * FROM lineitem

tpch
sf1000

orderkey	partkey	suppkey	linenumber	quantity	extendedprice	discount	tax	returnflag	linestatus	shipdate	commitdate	receiptdate	shipinstruct	shipmode	comment
3750000001	75605453	3105475	1	39.0	52832.13	0.07	0.07	A	F	1994-03-30	1994-05-23	1994-04-04	TAKE BACK RETURN	AIR	dolites above the even, b
3750000001	100831811	5831832	2	15.0	26066.55	0.09	0.05	A	F	1994-03-19	1994-04-16	1994-03-25	COLLECT COD	RAIL	regular dependencies. entici
3750000002	128850112	8850113	1	49.0	51727.83	0.02	0.07	A	F	1995-03-08	1995-01-26	1995-03-09	TAKE BACK RETURN	RAIL	ly alongside of the re
3750000002	183830552	8830589	2	5.0	7366.8	0.04	0.08	R	F	1994-12-18	1995-01-02	1995-01-09	NONE	MAIL	usly regular accoun
3750000002	40082464	82465	3	44.0	63556.24	0.02	0.04	R	F	1994-12-20	1995-01-21	1994-12-26	COLLECT COD	RAIL	t have to wake blithely r
3750000002	65957019	957032	4	4.0	4290.88	0.05	0.07	R	F	1995-02-03	1995-01-13	1995-02-24	TAKE BACK RETURN	SHIP	ges cajole furiously across the sl
3750000002	139611232	2111246	5	13.0	14771.25	0.06	0.08	A	F	1994-12-13	1995-02-26	1995-01-02	COLLECT COD	REG AIR	uests sleep furiously slyly special excus
3750000002	45969513	3469526	6	44.0	69529.68	0.01	0.06	R	F	1995-01-20	1995-01-30	1995-02-08	NONE	AIR	ironic braids across t
3750000003	148470915	8470916	1	38.0	71382.62	0.07	0.07	A	F	1993-10-02	1993-09-19	1993-10-20	TAKE BACK RETURN	AIR	ke blithely. furiously bold accounts dete
3750000003	150663572	3163588	2	47.0	71817.88	0.08	0.06	A	F	1993-09-06	1993-11-05	1993-09-19	COLLECT COD	AIR	nding orbits ought to nag evenly express s
3750000003	2641545	2641546	3	7.0	10404.87	0.01	0.08	R	F	1993-11-28	1993-09-20	1993-12-07	COLLECT COD	SHIP	nding warhorses wake slyly instr
3750000003	196444543	1444582	4	24.0	35465.28	0.05	0.03	R	F	1993-09-18	1993-09-26	1993-10-18	COLLECT COD	AIR	eans: carefully express a
3750000004	68826735	6326754	1	9.0	14924.61	0.1	0.07	A	F	1993-07-27	1993-09-20	1993-07-29	TAKE BACK RETURN	MAIL	ar foxes.
3750000004	170928189	928190	2	6.0	7251.84	0.04	0.04	A	F	1993-10-30	1993-08-25	1993-11-02	DELIVER IN PERSON	MAIL	iously up the final notornis. depe
3750000005	156770761	1770792	1	26.0	47422.18	0.04	0.02	R	F	1993-12-10	1993-11-01	1993-12-26	COLLECT COD	REG AIR	furiously final deposits. fluffily
3750000005	46178756	1178765	2	5.0	9162.25	0.02	0.05	A	F	1993-10-12	1993-11-05	1993-10-14	DELIVER IN PERSON	MAIL	side the always special accounts
3750000005	126343102	6343103	3	30.0	34163.7	0.05	0.07	A	F	1993-11-25	1993-11-12	1993-11-30	DELIVER IN PERSON	FOB	ly. slyly regular dolphins cajole blithel
3750000005	48499222	999227	4	8.0	9750.4	0.04	0.02	A	F	1993-12-01	1993-11-02	1993-12-21	TAKE BACK RETURN	TRUCK	fluffily even dependencies. reg
3750000006	146596633	9096648	1	46.0	79226.26	0.0	0.01	R	F	1994-03-12	1994-02-16	1994-04-05	DELIVER IN PERSON	FOB	pending requests.
3750000006	49610262	7110275	2	38.0	44451.64	0.0	0.03	R	F	1994-01-23	1994-01-03	1994-02-03	DELIVER IN PERSON	REG AIR	ong the regular, express packages

Only showing top 20 row(s)

In [10]:

%%trino
SELECT
    lin.orderkey,
    lin.partkey,
    lin.suppkey,
    lin.linenumber,
    lin.quantity,
    lin.extendedprice,
    lin.discount,
    lin.tax,
    lin.returnflag,
    lin.linestatus,
    lin.shipdate,
    lin.commitdate,
    lin.receiptdate,
    lin.shipinstruct,
    lin.shipmode,
    lin.comment,
    ord.orderpriority
FROM
    tpch.sf1000.lineitem AS lin
    INNER JOIN tpch.sf1.orders AS ord ON ord.orderkey = lin.orderkey

%%trino SELECT lin.orderkey, lin.partkey, lin.suppkey, lin.linenumber, lin.quantity, lin.extendedprice, lin.discount, lin.tax, lin.returnflag, lin.linestatus, lin.shipdate, lin.commitdate, lin.receiptdate, lin.shipinstruct, lin.shipmode, lin.comment, ord.orderpriority FROM tpch.sf1000.lineitem AS lin INNER JOIN tpch.sf1.orders AS ord ON ord.orderkey = lin.orderkey

orderkey	partkey	suppkey	linenumber	quantity	extendedprice	discount	tax	returnflag	linestatus	shipdate	commitdate	receiptdate	shipinstruct	shipmode	comment	orderpriority
1	155189345	7689361	1	17.0	24252.03	0.04	0.02	N	O	1996-03-13	1996-02-12	1996-03-22	DELIVER IN PERSON	TRUCK	egular courts above the	5-LOW
1	67309080	7309081	2	36.0	39085.92	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	ly final dependencies: slyly bold	5-LOW
1	63699776	3699777	3	8.0	14180.72	0.1	0.02	N	O	1996-01-29	1996-03-05	1996-01-31	TAKE BACK RETURN	REG AIR	riously. regular, express dep	5-LOW
1	2131495	4631496	4	28.0	42738.92	0.09	0.06	N	O	1996-04-21	1996-03-30	1996-05-16	NONE	AIR	lites. fluffily even de	5-LOW
1	24026634	1526641	5	24.0	37426.32	0.1	0.04	N	O	1996-03-30	1996-03-14	1996-04-01	NONE	FOB	pending foxes. slyly re	5-LOW
1	15634450	634453	6	32.0	44277.44	0.07	0.02	N	O	1996-01-30	1996-02-07	1996-02-03	DELIVER IN PERSON	MAIL	arefully slyly ex	5-LOW
2	106169722	1169743	1	38.0	67883.96	0.0	0.05	N	O	1997-01-28	1997-01-14	1997-02-02	TAKE BACK RETURN	RAIL	ven requests. deposits breach a	1-URGENT
3	4296962	1796963	1	45.0	88143.75	0.06	0.0	R	F	1994-02-02	1994-01-04	1994-02-23	NONE	AIR	ongside of the furiously brave acco	5-LOW
3	19035429	6535433	2	49.0	66810.03	0.1	0.0	R	F	1993-11-09	1993-12-20	1993-11-24	TAKE BACK RETURN	RAIL	unusual accounts. eve	5-LOW
3	128448229	3448254	3	27.0	31611.6	0.06	0.07	A	F	1994-01-16	1993-11-22	1994-01-23	DELIVER IN PERSON	SHIP	nal foxes wake.	5-LOW
3	29379610	1879613	4	2.0	3376.3	0.01	0.06	A	F	1993-12-04	1994-01-07	1994-01-01	NONE	TRUCK	y. fluffily pending d	5-LOW
3	183094077	594132	5	28.0	29733.76	0.04	0.0	R	F	1993-12-14	1994-01-10	1994-01-01	TAKE BACK RETURN	FOB	ages nag slyly pending	5-LOW
3	62142591	9642610	6	26.0	42392.74	0.1	0.02	A	F	1993-10-29	1993-12-18	1993-11-04	TAKE BACK RETURN	RAIL	ges sleep after the caref	5-LOW
4	88034684	5534709	1	30.0	48428.4	0.03	0.08	N	O	1996-01-10	1995-12-14	1996-01-18	DELIVER IN PERSON	REG AIR	- quickly regular packages sleep. idly	5-LOW
5	108569283	8569284	1	15.0	20202.9	0.02	0.04	R	F	1994-10-31	1994-08-31	1994-11-20	NONE	AIR	ts wake furiously	5-LOW
5	123926789	3926790	2	26.0	47049.34	0.07	0.08	R	F	1994-10-16	1994-09-25	1994-10-19	NONE	FOB	sts use slyly quickly special instruc	5-LOW
5	37530180	30184	3	50.0	60415.5	0.08	0.03	A	F	1994-08-08	1994-10-13	1994-08-26	DELIVER IN PERSON	AIR	eodolites. fluffily unusual	5-LOW
6	139635455	2135469	1	37.0	51188.39	0.08	0.03	A	F	1992-04-27	1992-05-15	1992-05-02	TAKE BACK RETURN	TRUCK	p furiously special foxes	4-NOT SPECIFIED
7	182051839	9551894	1	12.0	21380.76	0.07	0.03	N	O	1996-05-07	1996-03-13	1996-06-03	TAKE BACK RETURN	FOB	ss pinto beans wake against th	2-HIGH
7	145242743	7742758	2	9.0	15106.32	0.08	0.08	N	O	1996-02-01	1996-03-02	1996-02-19	TAKE BACK RETURN	SHIP	es. instructions	2-HIGH

Only showing top 20 row(s)

In [11]:

%%trino --output json
SELECT
    1 AS a,
    'abc' as b,
    1.2 as c,
    ARRAY[1,2] as d,
    ARRAY[1, null, 4] as e,
    ARRAY[ARRAY[1,2],ARRAY[5,4]] as f,
    CAST(ROW(1,23,456) as ROW(k1 INT, k2 INT, k3 INT)) as g,
    CAST(ROW(1,'abc',true,null) as ROW(k1 INT, k2 VARCHAR, k3 BOOLEAN, k4 VARCHAR)) as h

%%trino --output json SELECT 1 AS a, 'abc' as b, 1.2 as c, ARRAY[1,2] as d, ARRAY[1, null, 4] as e, ARRAY[ARRAY[1,2],ARRAY[5,4]] as f, CAST(ROW(1,23,456) as ROW(k1 INT, k2 INT, k3 INT)) as g, CAST(ROW(1,'abc',true,null) as ROW(k1 INT, k2 VARCHAR, k3 BOOLEAN, k4 VARCHAR)) as h

<IPython.core.display.JSON object>

In [12]:

%%trino?

%%trino?

Docstring:
::

  %trino [-l max_rows] [-r all|local|none] [-d name]
             [-o sql|json|html|grid|text|skip|none] [-s] [-x] [-c catalogname]
             [-m schemaname] [-j]
             [sql [sql ...]]

Magic that works both as %trino and as %%trino

positional arguments:
  sql                   SQL statement to execute

optional arguments:
  -l max_rows, --limit max_rows
                        The maximum number of rows to display. A value of zero
                        is equivalent to `--output skip`
  -r <all|local|none>, --refresh <all|local|none>
                        Force the regeneration of the schema cache file. The
                        `local` option will only update tables/views created
                        in the local Spark context.
  -d name, --dataframe name
                        Capture results in pandas dataframe
  -o <sql|json|html|grid|text|skip|none>, --output <sql|json|html|grid|text|skip|none>
                        Output format. Defaults to html. The `sql` option
                        prints the SQL statement that will be executed (useful
                        to test jinja templated statements)
  -s, --show-nonprinting
                        Replace none printable characters with their ascii
                        codes (LF -> )
  -x, --raw             Run statement as is. Do not wrap statement with a
                        limit. Use this option to run statement which can't be
                        wrapped in a SELECT/LIMIT statement. For example
                        EXPLAIN, SHOW TABLE, SHOW CATALOGS.
  -c catalogname, --catalog catalogname
                        Trino catalog to use
  -m schemaname, --schema schemaname
                        Trino schema to use
  -j, --jinja           Enable Jinja templating support
File:      /data/dev/jupyterlab-sql-editor/ipython_magic/trino/trino.py