vastorbit.jupyter.extensions.sql_magic.sql_magic¶

vastorbit.jupyter.extensions.sql_magic.sql_magic(line: str, cell: str | None = None, local_ns: dict | None = None) → VastFrame¶

Executes SQL queries in the Jupyter cell.

Warning

In the case of profiling (using PROFILE keywords), the query will be executed twice: once for profiling and another time to build the VastFrame.

Parameters:

--command (-c /) – SQL Command to execute.
--file (-f /) – Input File. You can use this option if you want to execute the input file.
-ncols (int, optional) – Maximum number of columns to display.
-nrows (int, optional) – Maximum number of rows to display.
--output (-o /) – Output File. You can use this option if you want to export the result of the query to the CSV or JSON format.
-stdin (bool, optional) – If set to False and you’re trying to perform a local copy, the parser will not replace the file name with STDIN, simplifying the ingestion.

Returns:

Result of the query

Return type:

VastFrame

Examples

The following examples demonstrate:

Setting up the environment
Using SQL Magic
Getting the VastFrame of a query
Using variables inside a query
Limiting the number of rows and columns
Exporting a query to JSON or CSV
Executing SQL files

Setting up the environment¶

If you don’t already have a connection, create one:

import vastorbit as vo

# Save a new connection
vo.new_connection(
    {
        "host": "10.211.55.14",
        "port": "5433",
        "database": "testdb",
        "password": "XxX",
        "user": "dbadmin",
    },
    name = "VASTDSN",
)

If you already have a connection in a connection file, you can use it by running the following command:

# Connect using the VASTDSN connection
vo.connect("VASTDSN")

Load the extension:

%load_ext vastorbit.sql

Load a sample dataset. These sample datasets are loaded into the public schema by default. You can specify a target schema with the name and schema parameters:

from vastorbit.datasets import load_titanic, load_iris

titanic = load_titanic()
iris = load_iris()

SQL Magic¶

Use %%sql to run a query on the dataset:

%%sql
SELECT
    survived,
    AVG(fare) AS avg_fare,
    AVG(age) AS avg_age
FROM titanic
GROUP BY 1;

Execution: 0.006s

	123 survived Integer	123 avg_fare Double	123 avg_age Double
1	0	23.353830569306915	30.545363489499195
2	1	49.36118360000001	28.91824355971897

Rows: 1-2 | Columns: 3

You can also run queries with %sql and the -c option:

%sql -c 'SELECT DISTINCT Species FROM iris;'

Execution: 0.006s

	Abc Species Varchar(30)
1	Iris-setosa
2	Iris-virginica
3	Iris-versicolor

Rows: 1-3 | Column: Species | Type: varchar(30)

You can use a single cell for multiple queries:

Warning

Don’t forget to include a semicolon at the end of each query.

%%sql
DROP TABLE IF EXISTS test;
CREATE TABLE test AS SELECT 'Badr Ouali' AS name;
SELECT * FROM test;

Execution: 0.05s

	Abc name Varchar(10)
1	Badr Ouali

Rows: 1-1 | Column: name | Type: varchar(10)

To add comments to a query, use one of the following comment syntaxes:

Warning

VAST uses ‘/’ and ‘/’ for both comments and query hints. Whenever possible, use ‘–’ to avoid conflicts.

%%sql
-- Comment Test
/* My VAST Version */
SELECT version() AS version; -- Select my current version

Execution: 0.005s

	Abc version Varchar
1	479

Rows: 1-1 | Column: version | Type: varchar

Get the VastFrame of a query¶

Results of a SQL Magic query are stored in a VastFrame, which is assigned to a temporary variable called ‘_’. You can assign this temporary variable to a new variable to save your results.

%%sql
SELECT
    age,
    fare,
    pclass
FROM titanic
WHERE age IS NOT NULL AND fare IS NOT NULL;

Execution: 0.007s

	123 age Double	123 fare Double	123 pclass Integer
1	15.0	8.0292	3
2	35.0	7.75	3
3	24.0	16.1	3
4	19.0	16.1	3
5	55.5	8.05	3
6	21.0	7.775	3
7	24.0	7.8958	3
8	21.0	7.8958	3
9	28.0	7.8958	3
10	25.0	7.65	3
11	6.0	12.475	3
12	27.0	12.475	3
13	34.0	8.05	3
14	24.0	7.75	3
15	18.0	7.75	3
16	22.0	7.8958	3
17	15.0	7.225	3
18	1.0	15.7417	3
19	20.0	15.7417	3
20	19.0	15.7417	3
21	33.0	8.05	3
22	12.0	11.2417	3
23	14.0	11.2417	3
24	29.0	7.925	3
25	28.0	8.05	3
26	18.0	7.775	3
27	26.0	7.8542	3
28	21.0	7.8542	3
29	41.0	7.125	3
30	39.0	7.925	3
31	21.0	7.8	3
32	28.5	7.2292	3
33	22.0	7.75	3
34	61.0	6.2375	3
35	23.0	9.225	3
36	22.0	7.775	3
37	9.0	3.1708	3
38	28.0	22.525	3
39	42.0	8.4042	3
40	31.0	7.8542	3
41	28.0	7.8542	3
42	32.0	7.775	3
43	20.0	9.225	3
44	23.0	8.6625	3
45	20.0	8.6625	3
46	20.0	8.6625	3
47	16.0	9.2167	3
48	31.0	8.6833	3
49	2.0	21.075	3
50	6.0	21.075	3
51	3.0	21.075	3
52	8.0	21.075	3
53	29.0	21.075	3
54	1.0	39.6875	3
55	7.0	39.6875	3
56	2.0	39.6875	3
57	16.0	39.6875	3
58	14.0	39.6875	3
59	41.0	39.6875	3
60	21.0	8.6625	3
61	19.0	14.5	3
62	32.0	7.8958	3
63	0.75	13.775	3
64	3.0	13.775	3
65	26.0	13.775	3
66	21.0	7.925	3
67	25.0	7.925	3
68	22.0	7.25	3
69	25.0	7.775	3
70	24.0	8.05	3
71	28.0	7.8958	3
72	19.0	7.8958	3
73	25.0	7.775	3
74	18.0	7.775	3
75	32.0	8.05	3
76	17.0	8.6625	3
77	24.0	8.6625	3
78	38.0	7.8958	3
79	21.0	8.05	3
80	10.0	29.125	3
81	4.0	29.125	3
82	7.0	29.125	3
83	2.0	29.125	3
84	8.0	29.125	3
85	39.0	29.125	3
86	22.0	39.6875	3
87	35.0	7.125	3
88	50.0	14.5	3
89	47.0	14.5	3
90	2.0	20.2125	3
91	18.0	20.2125	3
92	41.0	20.2125	3
93	50.0	8.05	3
94	16.0	8.05	3
95	25.0	7.225	3
96	38.5	7.25	3
97	14.5	69.55	3
98	24.0	9.325	3
99	21.0	7.65	3
100	39.0	7.925	3

Rows: 1-100 | Columns: 3

Assign the results to a new variable:

titanic_clean = _
display(titanic_clean)

	123 age Double	123 fare Double	123 pclass Integer
1	15.0	8.0292	3
2	35.0	7.75	3
3	24.0	16.1	3
4	19.0	16.1	3
5	55.5	8.05	3
6	21.0	7.775	3
7	24.0	7.8958	3
8	21.0	7.8958	3
9	28.0	7.8958	3
10	25.0	7.65	3
11	6.0	12.475	3
12	27.0	12.475	3
13	34.0	8.05	3
14	24.0	7.75	3
15	18.0	7.75	3
16	22.0	7.8958	3
17	15.0	7.225	3
18	1.0	15.7417	3
19	20.0	15.7417	3
20	19.0	15.7417	3
21	33.0	8.05	3
22	12.0	11.2417	3
23	14.0	11.2417	3
24	29.0	7.925	3
25	28.0	8.05	3
26	18.0	7.775	3
27	26.0	7.8542	3
28	21.0	7.8542	3
29	41.0	7.125	3
30	39.0	7.925	3
31	21.0	7.8	3
32	28.5	7.2292	3
33	22.0	7.75	3
34	61.0	6.2375	3
35	23.0	9.225	3
36	22.0	7.775	3
37	9.0	3.1708	3
38	28.0	22.525	3
39	42.0	8.4042	3
40	31.0	7.8542	3
41	28.0	7.8542	3
42	32.0	7.775	3
43	20.0	9.225	3
44	23.0	8.6625	3
45	20.0	8.6625	3
46	20.0	8.6625	3
47	16.0	9.2167	3
48	31.0	8.6833	3
49	2.0	21.075	3
50	6.0	21.075	3
51	3.0	21.075	3
52	8.0	21.075	3
53	29.0	21.075	3
54	1.0	39.6875	3
55	7.0	39.6875	3
56	2.0	39.6875	3
57	16.0	39.6875	3
58	14.0	39.6875	3
59	41.0	39.6875	3
60	21.0	8.6625	3
61	19.0	14.5	3
62	32.0	7.8958	3
63	0.75	13.775	3
64	3.0	13.775	3
65	26.0	13.775	3
66	21.0	7.925	3
67	25.0	7.925	3
68	22.0	7.25	3
69	25.0	7.775	3
70	24.0	8.05	3
71	28.0	7.8958	3
72	19.0	7.8958	3
73	25.0	7.775	3
74	18.0	7.775	3
75	32.0	8.05	3
76	17.0	8.6625	3
77	24.0	8.6625	3
78	38.0	7.8958	3
79	21.0	8.05	3
80	10.0	29.125	3
81	4.0	29.125	3
82	7.0	29.125	3
83	2.0	29.125	3
84	8.0	29.125	3
85	39.0	29.125	3
86	22.0	39.6875	3
87	35.0	7.125	3
88	50.0	14.5	3
89	47.0	14.5	3
90	2.0	20.2125	3
91	18.0	20.2125	3
92	41.0	20.2125	3
93	50.0	8.05	3
94	16.0	8.05	3
95	25.0	7.225	3
96	38.5	7.25	3
97	14.5	69.55	3
98	24.0	9.325	3
99	21.0	7.65	3
100	39.0	7.925	3

Rows: 1-100 | Columns: 3

Temporary results are stored in a VastFrame, allowing you to call VastFrame methods:

titanic_clean["age"].max()

Using variables inside a query¶

You can use variables in a SQL query with the ‘:’ operator. This variable can be a VastFrame, a TableSample, a pandas.DataFrame, or any standard Python type.

import vastorbit.sql.functions as vof

class_fare = titanic_clean.groupby(
    "pclass",
    [vof.avg(titanic_clean["fare"])._as("avg_fare")],
)
class_fare

	123 pclass Integer	123 avg_fare Double
1	3	12.879299000000014
2	2	21.85504444444445
3	1	92.22935845070427

Rows: 1-3 | Columns: 2

Use the ‘class_fare’ variable in a SQL query:

%%sql
SELECT
    x.*,
    y.avg_fare
FROM titanic AS x LEFT JOIN (SELECT * FROM :class_fare) AS y
ON x.pclass = y.pclass;

Execution: 0.011s

	123 pclass Integer	123 survived Integer	Abc name Varchar(164)	Abc sex Varchar(20)	123 age Double	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Double	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)	123 avg_fare Double
1	3	1	McCormack, Mr. Thomas Joseph	male	[null]	0	0	367228	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
2	3	1	McCoy, Miss. Agnes	female	[null]	2	0	367226	23.25	[null]	Q	16	[null]	[null]	12.879299000000014
3	3	1	McCoy, Miss. Alicia	female	[null]	2	0	367226	23.25	[null]	Q	16	[null]	[null]	12.879299000000014
4	3	1	McCoy, Mr. Bernard	male	[null]	2	0	367226	23.25	[null]	Q	16	[null]	[null]	12.879299000000014
5	3	1	McDermott, Miss. Brigdet Delia	female	[null]	0	0	330932	7.7875	[null]	Q	13	[null]	[null]	12.879299000000014
6	3	0	McEvoy, Mr. Michael	male	[null]	0	0	36568	15.5	[null]	Q	[null]	[null]	[null]	12.879299000000014
7	3	1	McGovern, Miss. Mary	female	[null]	0	0	330931	7.8792	[null]	Q	13	[null]	[null]	12.879299000000014
8	3	1	McGowan, Miss. Anna "Annie"	female	15.0	0	0	330923	8.0292	[null]	Q	[null]	[null]	[null]	12.879299000000014
9	3	0	McGowan, Miss. Katherine	female	35.0	0	0	9232	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
10	3	0	McMahon, Mr. Martin	male	[null]	0	0	370372	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
11	3	0	McNamee, Mr. Neal	male	24.0	1	0	376566	16.1	[null]	S	[null]	[null]	[null]	12.879299000000014
12	3	0	McNamee, Mrs. Neal (Eileen O'Leary)	female	19.0	1	0	376566	16.1	[null]	S	[null]	53	[null]	12.879299000000014
13	3	0	McNeill, Miss. Bridget	female	[null]	0	0	370368	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
14	3	0	Meanwell, Miss. (Marion Ogden)	female	[null]	0	0	SOTON/O.Q. 392087	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
15	3	0	Meek, Mrs. Thomas (Annie Louise Rowley)	female	[null]	0	0	343095	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
16	3	0	Meo, Mr. Alfonzo	male	55.5	0	0	A.5. 11206	8.05	[null]	S	[null]	201	[null]	12.879299000000014
17	3	0	Mernagh, Mr. Robert	male	[null]	0	0	368703	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
18	3	1	Midtsjo, Mr. Karl Albert	male	21.0	0	0	345501	7.775	[null]	S	15	[null]	[null]	12.879299000000014
19	3	0	Miles, Mr. Frank	male	[null]	0	0	359306	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
20	3	0	Mineff, Mr. Ivan	male	24.0	0	0	349233	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
21	3	0	Minkoff, Mr. Lazar	male	21.0	0	0	349211	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
22	3	0	Mionoff, Mr. Stoytcho	male	28.0	0	0	349207	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
23	3	0	Mitkoff, Mr. Mito	male	[null]	0	0	349221	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
24	3	1	Mockler, Miss. Helen Mary "Ellie"	female	[null]	0	0	330980	7.8792	[null]	Q	16	[null]	[null]	12.879299000000014
25	3	0	Moen, Mr. Sigurd Hansen	male	25.0	0	0	348123	7.65	F G73	S	[null]	309	[null]	12.879299000000014
26	3	1	Moor, Master. Meier	male	6.0	0	1	392096	12.475	E121	S	14	[null]	[null]	12.879299000000014
27	3	1	Moor, Mrs. (Beila)	female	27.0	0	1	392096	12.475	E121	S	14	[null]	[null]	12.879299000000014
28	3	0	Moore, Mr. Leonard Charles	male	[null]	0	0	A4. 54510	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
29	3	1	Moran, Miss. Bertha	female	[null]	1	0	371110	24.15	[null]	Q	16	[null]	[null]	12.879299000000014
30	3	0	Moran, Mr. Daniel J	male	[null]	1	0	371110	24.15	[null]	Q	[null]	[null]	[null]	12.879299000000014
31	3	0	Moran, Mr. James	male	[null]	0	0	330877	8.4583	[null]	Q	[null]	[null]	[null]	12.879299000000014
32	3	0	Morley, Mr. William	male	34.0	0	0	364506	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
33	3	0	Morrow, Mr. Thomas Rowan	male	[null]	0	0	372622	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
34	3	1	Moss, Mr. Albert Johan	male	[null]	0	0	312991	7.775	[null]	S	B	[null]	[null]	12.879299000000014
35	3	1	Moubarek, Master. Gerios	male	[null]	1	1	2661	15.2458	[null]	C	C	[null]	[null]	12.879299000000014
36	3	1	Moubarek, Master. Halim Gonios ("William George")	male	[null]	1	1	2661	15.2458	[null]	C	C	[null]	[null]	12.879299000000014
37	3	1	Moubarek, Mrs. George (Omine "Amenia" Alexander)	female	[null]	0	2	2661	15.2458	[null]	C	C	[null]	[null]	12.879299000000014
38	3	1	Moussa, Mrs. (Mantoura Boulos)	female	[null]	0	0	2626	7.2292	[null]	C	[null]	[null]	[null]	12.879299000000014
39	3	0	Moutal, Mr. Rahamin Haim	male	[null]	0	0	374746	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
40	3	1	Mullens, Miss. Katherine "Katie"	female	[null]	0	0	35852	7.7333	[null]	Q	16	[null]	[null]	12.879299000000014
41	3	1	Mulvihill, Miss. Bertha E	female	24.0	0	0	382653	7.75	[null]	Q	15	[null]	[null]	12.879299000000014
42	3	0	Murdlin, Mr. Joseph	male	[null]	0	0	A./5. 3235	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
43	3	1	Murphy, Miss. Katherine "Kate"	female	[null]	1	0	367230	15.5	[null]	Q	16	[null]	[null]	12.879299000000014
44	3	1	Murphy, Miss. Margaret Jane	female	[null]	1	0	367230	15.5	[null]	Q	16	[null]	[null]	12.879299000000014
45	3	1	Murphy, Miss. Nora	female	[null]	0	0	36568	15.5	[null]	Q	16	[null]	[null]	12.879299000000014
46	3	0	Myhrman, Mr. Pehr Fabian Oliver Malkolm	male	18.0	0	0	347078	7.75	[null]	S	[null]	[null]	[null]	12.879299000000014
47	3	0	Naidenoff, Mr. Penko	male	22.0	0	0	349206	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
48	3	1	Najib, Miss. Adele Kiamie "Jane"	female	15.0	0	0	2667	7.225	[null]	C	C	[null]	[null]	12.879299000000014
49	3	1	Nakid, Miss. Maria ("Mary")	female	1.0	0	2	2653	15.7417	[null]	C	C	[null]	[null]	12.879299000000014
50	3	1	Nakid, Mr. Sahid	male	20.0	1	1	2653	15.7417	[null]	C	C	[null]	[null]	12.879299000000014
51	3	1	Nakid, Mrs. Said (Waika "Mary" Mowad)	female	19.0	1	1	2653	15.7417	[null]	C	C	[null]	[null]	12.879299000000014
52	3	0	Nancarrow, Mr. William Henry	male	33.0	0	0	A./5. 3338	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
53	3	0	Nankoff, Mr. Minko	male	[null]	0	0	349218	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
54	3	0	Nasr, Mr. Mustafa	male	[null]	0	0	2652	7.2292	[null]	C	[null]	[null]	[null]	12.879299000000014
55	3	0	Naughton, Miss. Hannah	female	[null]	0	0	365237	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
56	3	0	Nenkoff, Mr. Christo	male	[null]	0	0	349234	7.8958	[null]	S	[null]	[null]	[null]	12.879299000000014
57	3	1	Nicola-Yarred, Master. Elias	male	12.0	1	0	2651	11.2417	[null]	C	C	[null]	[null]	12.879299000000014
58	3	1	Nicola-Yarred, Miss. Jamila	female	14.0	1	0	2651	11.2417	[null]	C	C	[null]	[null]	12.879299000000014
59	3	0	Nieminen, Miss. Manta Josefina	female	29.0	0	0	3101297	7.925	[null]	S	[null]	[null]	[null]	12.879299000000014
60	3	0	Niklasson, Mr. Samuel	male	28.0	0	0	363611	8.05	[null]	S	[null]	[null]	[null]	12.879299000000014
61	3	1	Nilsson, Miss. Berta Olivia	female	18.0	0	0	347066	7.775	[null]	S	D	[null]	[null]	12.879299000000014
62	3	1	Nilsson, Miss. Helmina Josefina	female	26.0	0	0	347470	7.8542	[null]	S	13	[null]	[null]	12.879299000000014
63	3	0	Nilsson, Mr. August Ferdinand	male	21.0	0	0	350410	7.8542	[null]	S	[null]	[null]	[null]	12.879299000000014
64	3	0	Nirva, Mr. Iisakki Antino Aijo	male	41.0	0	0	SOTON/O2 3101272	7.125	[null]	S	[null]	[null]	Finland Sudbury, ON	12.879299000000014
65	3	1	Niskanen, Mr. Juha	male	39.0	0	0	STON/O 2. 3101289	7.925	[null]	S	9	[null]	[null]	12.879299000000014
66	3	0	Nosworthy, Mr. Richard Cater	male	21.0	0	0	A/4. 39886	7.8	[null]	S	[null]	[null]	[null]	12.879299000000014
67	3	0	Novel, Mr. Mansouer	male	28.5	0	0	2697	7.2292	[null]	C	[null]	181	[null]	12.879299000000014
68	3	1	Nysten, Miss. Anna Sofia	female	22.0	0	0	347081	7.75	[null]	S	13	[null]	[null]	12.879299000000014
69	3	0	Nysveen, Mr. Johan Hansen	male	61.0	0	0	345364	6.2375	[null]	S	[null]	[null]	[null]	12.879299000000014
70	3	0	O'Brien, Mr. Thomas	male	[null]	1	0	370365	15.5	[null]	Q	[null]	[null]	[null]	12.879299000000014
71	3	0	O'Brien, Mr. Timothy	male	[null]	0	0	330979	7.8292	[null]	Q	[null]	[null]	[null]	12.879299000000014
72	3	1	O'Brien, Mrs. Thomas (Johanna "Hannah" Godfrey)	female	[null]	1	0	370365	15.5	[null]	Q	[null]	[null]	[null]	12.879299000000014
73	3	0	O'Connell, Mr. Patrick D	male	[null]	0	0	334912	7.7333	[null]	Q	[null]	[null]	[null]	12.879299000000014
74	3	0	O'Connor, Mr. Maurice	male	[null]	0	0	371060	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
75	3	0	O'Connor, Mr. Patrick	male	[null]	0	0	366713	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
76	3	0	Odahl, Mr. Nils Martin	male	23.0	0	0	7267	9.225	[null]	S	[null]	[null]	[null]	12.879299000000014
77	3	0	O'Donoghue, Ms. Bridget	female	[null]	0	0	364856	7.75	[null]	Q	[null]	[null]	[null]	12.879299000000014
78	3	1	O'Driscoll, Miss. Bridget	female	[null]	0	0	14311	7.75	[null]	Q	D	[null]	[null]	12.879299000000014
79	3	1	O'Dwyer, Miss. Ellen "Nellie"	female	[null]	0	0	330959	7.8792	[null]	Q	[null]	[null]	[null]	12.879299000000014
80	3	1	Ohman, Miss. Velin	female	22.0	0	0	347085	7.775	[null]	S	C	[null]	[null]	12.879299000000014
81	3	1	O'Keefe, Mr. Patrick	male	[null]	0	0	368402	7.75	[null]	Q	B	[null]	[null]	12.879299000000014
82	3	1	O'Leary, Miss. Hanora "Norah"	female	[null]	0	0	330919	7.8292	[null]	Q	13	[null]	[null]	12.879299000000014
83	3	1	Olsen, Master. Artur Karl	male	9.0	0	1	C 17368	3.1708	[null]	S	13	[null]	[null]	12.879299000000014
84	3	0	Olsen, Mr. Henry Margido	male	28.0	0	0	C 4001	22.525	[null]	S	[null]	173	[null]	12.879299000000014
85	3	0	Olsen, Mr. Karl Siegwart Andreas	male	42.0	0	1	4579	8.4042	[null]	S	[null]	[null]	[null]	12.879299000000014
86	3	0	Olsen, Mr. Ole Martin	male	[null]	0	0	Fa 265302	7.3125	[null]	S	[null]	[null]	[null]	12.879299000000014
87	3	0	Olsson, Miss. Elina	female	31.0	0	0	350407	7.8542	[null]	S	[null]	[null]	[null]	12.879299000000014
88	3	0	Olsson, Mr. Nils Johan Goransson	male	28.0	0	0	347464	7.8542	[null]	S	[null]	[null]	[null]	12.879299000000014
89	3	1	Olsson, Mr. Oscar Wilhelm	male	32.0	0	0	347079	7.775	[null]	S	A	[null]	[null]	12.879299000000014
90	3	0	Olsvigen, Mr. Thor Anderson	male	20.0	0	0	6563	9.225	[null]	S	[null]	89	Oslo, Norway Cameron, WI	12.879299000000014
91	3	0	Oreskovic, Miss. Jelka	female	23.0	0	0	315085	8.6625	[null]	S	[null]	[null]	[null]	12.879299000000014
92	3	0	Oreskovic, Miss. Marija	female	20.0	0	0	315096	8.6625	[null]	S	[null]	[null]	[null]	12.879299000000014
93	3	0	Oreskovic, Mr. Luka	male	20.0	0	0	315094	8.6625	[null]	S	[null]	[null]	[null]	12.879299000000014
94	3	0	Osen, Mr. Olaf Elon	male	16.0	0	0	7534	9.2167	[null]	S	[null]	[null]	[null]	12.879299000000014
95	3	1	Osman, Mrs. Mara	female	31.0	0	0	349244	8.6833	[null]	S	[null]	[null]	[null]	12.879299000000014
96	3	0	O'Sullivan, Miss. Bridget Mary	female	[null]	0	0	330909	7.6292	[null]	Q	[null]	[null]	[null]	12.879299000000014
97	3	0	Palsson, Master. Gosta Leonard	male	2.0	3	1	349909	21.075	[null]	S	[null]	4	[null]	12.879299000000014
98	3	0	Palsson, Master. Paul Folke	male	6.0	3	1	349909	21.075	[null]	S	[null]	[null]	[null]	12.879299000000014
99	3	0	Palsson, Miss. Stina Viola	female	3.0	3	1	349909	21.075	[null]	S	[null]	[null]	[null]	12.879299000000014
100	3	0	Palsson, Miss. Torborg Danira	female	8.0	3	1	349909	21.075	[null]	S	[null]	[null]	[null]	12.879299000000014

Rows: 1-100 | Columns: 15

You can do the same with a TableSample:

tb = {
    "name": ["Badr", "Arash"],
    "specialty": ["Python", "C++"],
}
tb = vo.TableSample(tb)

%%sql
SELECT * FROM :tb;

Execution: 0.014s

	Abc name Varchar(5)	Abc specialty Varchar(6)
1	Arash	C++
2	Badr	Python

Rows: 1-2 | Columns: 2

And with a pandas.DataFrame:

titanic_pandas = titanic.to_pandas()
titanic_pandas

%%sql
SELECT * FROM :titanic_pandas;

	123 pclass Integer	123 survived Integer	Abc name Varchar(132)	Abc sex Varchar(50)	123 age Double	123 sibsp Integer	123 parch Integer	123 ticket Integer	123 fare Double	Abc cabin Varchar(50)	Abc embarked Varchar(50)	Abc boat Varchar(50)	123 body Integer	Abc home_dest Varchar(50)
1	3	1	McCormack, Mr. Thomas Joseph	male	[null]	0	0	367228	7.75	[null]	Q	[null]	[null]	[null]
2	3	1	McCoy, Miss. Agnes	female	[null]	2	0	367226	23.25	[null]	Q	16	[null]	[null]
3	3	1	McCoy, Miss. Alicia	female	[null]	2	0	367226	23.25	[null]	Q	16	[null]	[null]
4	3	1	McCoy, Mr. Bernard	male	[null]	2	0	367226	23.25	[null]	Q	16	[null]	[null]
5	3	1	McDermott, Miss. Brigdet Delia	female	[null]	0	0	330932	7.7875	[null]	Q	13	[null]	[null]
6	3	0	McEvoy, Mr. Michael	male	[null]	0	0	36568	15.5	[null]	Q	[null]	[null]	[null]
7	3	1	McGovern, Miss. Mary	female	[null]	0	0	330931	7.8792	[null]	Q	13	[null]	[null]
8	3	1	McGowan, Miss. Anna "Annie"	female	15.0	0	0	330923	8.0292	[null]	Q	[null]	[null]	[null]
9	3	0	McGowan, Miss. Katherine	female	35.0	0	0	9232	7.75	[null]	Q	[null]	[null]	[null]
10	3	0	McMahon, Mr. Martin	male	[null]	0	0	370372	7.75	[null]	Q	[null]	[null]	[null]
11	3	0	McNamee, Mr. Neal	male	24.0	1	0	376566	16.1	[null]	S	[null]	[null]	[null]
12	3	0	McNamee, Mrs. Neal (Eileen O'Leary)	female	19.0	1	0	376566	16.1	[null]	S	[null]	53	[null]
13	3	0	McNeill, Miss. Bridget	female	[null]	0	0	370368	7.75	[null]	Q	[null]	[null]	[null]
14	3	0	Meanwell, Miss. (Marion Ogden)	female	[null]	0	0	[null]	8.05	[null]	S	[null]	[null]	[null]
15	3	0	Meek, Mrs. Thomas (Annie Louise Rowley)	female	[null]	0	0	343095	8.05	[null]	S	[null]	[null]	[null]
16	3	0	Meo, Mr. Alfonzo	male	55.5	0	0	[null]	8.05	[null]	S	[null]	201	[null]
17	3	0	Mernagh, Mr. Robert	male	[null]	0	0	368703	7.75	[null]	Q	[null]	[null]	[null]
18	3	1	Midtsjo, Mr. Karl Albert	male	21.0	0	0	345501	7.775	[null]	S	15	[null]	[null]
19	3	0	Miles, Mr. Frank	male	[null]	0	0	359306	8.05	[null]	S	[null]	[null]	[null]
20	3	0	Mineff, Mr. Ivan	male	24.0	0	0	349233	7.8958	[null]	S	[null]	[null]	[null]
21	3	0	Minkoff, Mr. Lazar	male	21.0	0	0	349211	7.8958	[null]	S	[null]	[null]	[null]
22	3	0	Mionoff, Mr. Stoytcho	male	28.0	0	0	349207	7.8958	[null]	S	[null]	[null]	[null]
23	3	0	Mitkoff, Mr. Mito	male	[null]	0	0	349221	7.8958	[null]	S	[null]	[null]	[null]
24	3	1	Mockler, Miss. Helen Mary "Ellie"	female	[null]	0	0	330980	7.8792	[null]	Q	16	[null]	[null]
25	3	0	Moen, Mr. Sigurd Hansen	male	25.0	0	0	348123	7.65	F G73	S	[null]	309	[null]
26	3	1	Moor, Master. Meier	male	6.0	0	1	392096	12.475	E121	S	14	[null]	[null]
27	3	1	Moor, Mrs. (Beila)	female	27.0	0	1	392096	12.475	E121	S	14	[null]	[null]
28	3	0	Moore, Mr. Leonard Charles	male	[null]	0	0	[null]	8.05	[null]	S	[null]	[null]	[null]
29	3	1	Moran, Miss. Bertha	female	[null]	1	0	371110	24.15	[null]	Q	16	[null]	[null]
30	3	0	Moran, Mr. Daniel J	male	[null]	1	0	371110	24.15	[null]	Q	[null]	[null]	[null]
31	3	0	Moran, Mr. James	male	[null]	0	0	330877	8.4583	[null]	Q	[null]	[null]	[null]
32	3	0	Morley, Mr. William	male	34.0	0	0	364506	8.05	[null]	S	[null]	[null]	[null]
33	3	0	Morrow, Mr. Thomas Rowan	male	[null]	0	0	372622	7.75	[null]	Q	[null]	[null]	[null]
34	3	1	Moss, Mr. Albert Johan	male	[null]	0	0	312991	7.775	[null]	S	B	[null]	[null]
35	3	1	Moubarek, Master. Gerios	male	[null]	1	1	2661	15.2458	[null]	C	C	[null]	[null]
36	3	1	Moubarek, Master. Halim Gonios ("William George")	male	[null]	1	1	2661	15.2458	[null]	C	C	[null]	[null]
37	3	1	Moubarek, Mrs. George (Omine "Amenia" Alexander)	female	[null]	0	2	2661	15.2458	[null]	C	C	[null]	[null]
38	3	1	Moussa, Mrs. (Mantoura Boulos)	female	[null]	0	0	2626	7.2292	[null]	C	[null]	[null]	[null]
39	3	0	Moutal, Mr. Rahamin Haim	male	[null]	0	0	374746	8.05	[null]	S	[null]	[null]	[null]
40	3	1	Mullens, Miss. Katherine "Katie"	female	[null]	0	0	35852	7.7333	[null]	Q	16	[null]	[null]
41	3	1	Mulvihill, Miss. Bertha E	female	24.0	0	0	382653	7.75	[null]	Q	15	[null]	[null]
42	3	0	Murdlin, Mr. Joseph	male	[null]	0	0	[null]	8.05	[null]	S	[null]	[null]	[null]
43	3	1	Murphy, Miss. Katherine "Kate"	female	[null]	1	0	367230	15.5	[null]	Q	16	[null]	[null]
44	3	1	Murphy, Miss. Margaret Jane	female	[null]	1	0	367230	15.5	[null]	Q	16	[null]	[null]
45	3	1	Murphy, Miss. Nora	female	[null]	0	0	36568	15.5	[null]	Q	16	[null]	[null]
46	3	0	Myhrman, Mr. Pehr Fabian Oliver Malkolm	male	18.0	0	0	347078	7.75	[null]	S	[null]	[null]	[null]
47	3	0	Naidenoff, Mr. Penko	male	22.0	0	0	349206	7.8958	[null]	S	[null]	[null]	[null]
48	3	1	Najib, Miss. Adele Kiamie "Jane"	female	15.0	0	0	2667	7.225	[null]	C	C	[null]	[null]
49	3	1	Nakid, Miss. Maria ("Mary")	female	1.0	0	2	2653	15.7417	[null]	C	C	[null]	[null]
50	3	1	Nakid, Mr. Sahid	male	20.0	1	1	2653	15.7417	[null]	C	C	[null]	[null]
51	3	1	Nakid, Mrs. Said (Waika "Mary" Mowad)	female	19.0	1	1	2653	15.7417	[null]	C	C	[null]	[null]
52	3	0	Nancarrow, Mr. William Henry	male	33.0	0	0	[null]	8.05	[null]	S	[null]	[null]	[null]
53	3	0	Nankoff, Mr. Minko	male	[null]	0	0	349218	7.8958	[null]	S	[null]	[null]	[null]
54	3	0	Nasr, Mr. Mustafa	male	[null]	0	0	2652	7.2292	[null]	C	[null]	[null]	[null]
55	3	0	Naughton, Miss. Hannah	female	[null]	0	0	365237	7.75	[null]	Q	[null]	[null]	[null]
56	3	0	Nenkoff, Mr. Christo	male	[null]	0	0	349234	7.8958	[null]	S	[null]	[null]	[null]
57	3	1	Nicola-Yarred, Master. Elias	male	12.0	1	0	2651	11.2417	[null]	C	C	[null]	[null]
58	3	1	Nicola-Yarred, Miss. Jamila	female	14.0	1	0	2651	11.2417	[null]	C	C	[null]	[null]
59	3	0	Nieminen, Miss. Manta Josefina	female	29.0	0	0	3101297	7.925	[null]	S	[null]	[null]	[null]
60	3	0	Niklasson, Mr. Samuel	male	28.0	0	0	363611	8.05	[null]	S	[null]	[null]	[null]
61	3	1	Nilsson, Miss. Berta Olivia	female	18.0	0	0	347066	7.775	[null]	S	D	[null]	[null]
62	3	1	Nilsson, Miss. Helmina Josefina	female	26.0	0	0	347470	7.8542	[null]	S	13	[null]	[null]
63	3	0	Nilsson, Mr. August Ferdinand	male	21.0	0	0	350410	7.8542	[null]	S	[null]	[null]	[null]
64	3	0	Nirva, Mr. Iisakki Antino Aijo	male	41.0	0	0	[null]	7.125	[null]	S	[null]	[null]	Finland Sudbury, ON
65	3	1	Niskanen, Mr. Juha	male	39.0	0	0	[null]	7.925	[null]	S	9	[null]	[null]
66	3	0	Nosworthy, Mr. Richard Cater	male	21.0	0	0	[null]	7.8	[null]	S	[null]	[null]	[null]
67	3	0	Novel, Mr. Mansouer	male	28.5	0	0	2697	7.2292	[null]	C	[null]	181	[null]
68	3	1	Nysten, Miss. Anna Sofia	female	22.0	0	0	347081	7.75	[null]	S	13	[null]	[null]
69	3	0	Nysveen, Mr. Johan Hansen	male	61.0	0	0	345364	6.2375	[null]	S	[null]	[null]	[null]
70	3	0	O'Brien, Mr. Thomas	male	[null]	1	0	370365	15.5	[null]	Q	[null]	[null]	[null]
71	3	0	O'Brien, Mr. Timothy	male	[null]	0	0	330979	7.8292	[null]	Q	[null]	[null]	[null]
72	3	1	O'Brien, Mrs. Thomas (Johanna "Hannah" Godfrey)	female	[null]	1	0	370365	15.5	[null]	Q	[null]	[null]	[null]
73	3	0	O'Connell, Mr. Patrick D	male	[null]	0	0	334912	7.7333	[null]	Q	[null]	[null]	[null]
74	3	0	O'Connor, Mr. Maurice	male	[null]	0	0	371060	7.75	[null]	Q	[null]	[null]	[null]
75	3	0	O'Connor, Mr. Patrick	male	[null]	0	0	366713	7.75	[null]	Q	[null]	[null]	[null]
76	3	0	Odahl, Mr. Nils Martin	male	23.0	0	0	7267	9.225	[null]	S	[null]	[null]	[null]
77	3	0	O'Donoghue, Ms. Bridget	female	[null]	0	0	364856	7.75	[null]	Q	[null]	[null]	[null]
78	3	1	O'Driscoll, Miss. Bridget	female	[null]	0	0	14311	7.75	[null]	Q	D	[null]	[null]
79	3	1	O'Dwyer, Miss. Ellen "Nellie"	female	[null]	0	0	330959	7.8792	[null]	Q	[null]	[null]	[null]
80	3	1	Ohman, Miss. Velin	female	22.0	0	0	347085	7.775	[null]	S	C	[null]	[null]
81	3	1	O'Keefe, Mr. Patrick	male	[null]	0	0	368402	7.75	[null]	Q	B	[null]	[null]
82	3	1	O'Leary, Miss. Hanora "Norah"	female	[null]	0	0	330919	7.8292	[null]	Q	13	[null]	[null]
83	3	1	Olsen, Master. Artur Karl	male	9.0	0	1	[null]	3.1708	[null]	S	13	[null]	[null]
84	3	0	Olsen, Mr. Henry Margido	male	28.0	0	0	[null]	22.525	[null]	S	[null]	173	[null]
85	3	0	Olsen, Mr. Karl Siegwart Andreas	male	42.0	0	1	4579	8.4042	[null]	S	[null]	[null]	[null]
86	3	0	Olsen, Mr. Ole Martin	male	[null]	0	0	[null]	7.3125	[null]	S	[null]	[null]	[null]
87	3	0	Olsson, Miss. Elina	female	31.0	0	0	350407	7.8542	[null]	S	[null]	[null]	[null]
88	3	0	Olsson, Mr. Nils Johan Goransson	male	28.0	0	0	347464	7.8542	[null]	S	[null]	[null]	[null]
89	3	1	Olsson, Mr. Oscar Wilhelm	male	32.0	0	0	347079	7.775	[null]	S	A	[null]	[null]
90	3	0	Olsvigen, Mr. Thor Anderson	male	20.0	0	0	6563	9.225	[null]	S	[null]	89	Oslo, Norway Cameron, WI
91	3	0	Oreskovic, Miss. Jelka	female	23.0	0	0	315085	8.6625	[null]	S	[null]	[null]	[null]
92	3	0	Oreskovic, Miss. Marija	female	20.0	0	0	315096	8.6625	[null]	S	[null]	[null]	[null]
93	3	0	Oreskovic, Mr. Luka	male	20.0	0	0	315094	8.6625	[null]	S	[null]	[null]	[null]
94	3	0	Osen, Mr. Olaf Elon	male	16.0	0	0	7534	9.2167	[null]	S	[null]	[null]	[null]
95	3	1	Osman, Mrs. Mara	female	31.0	0	0	349244	8.6833	[null]	S	[null]	[null]	[null]
96	3	0	O'Sullivan, Miss. Bridget Mary	female	[null]	0	0	330909	7.6292	[null]	Q	[null]	[null]	[null]
97	3	0	Palsson, Master. Gosta Leonard	male	2.0	3	1	349909	21.075	[null]	S	[null]	4	[null]
98	3	0	Palsson, Master. Paul Folke	male	6.0	3	1	349909	21.075	[null]	S	[null]	[null]	[null]
99	3	0	Palsson, Miss. Stina Viola	female	3.0	3	1	349909	21.075	[null]	S	[null]	[null]	[null]
100	3	0	Palsson, Miss. Torborg Danira	female	8.0	3	1	349909	21.075	[null]	S	[null]	[null]	[null]

Rows: 1-100 | Columns: 14

You can also use a sample loop with a variable:

Note

vastorbit will store the object in a temporary local table before executing the overall query, which facilitates integration with in-memory objects.

%sql -c 'DROP TABLE IF EXISTS test;'
%sql -c 'CREATE TABLE test (id INT);'
for i in range(4):
    %sql -c 'INSERT INTO test(id) SELECT :i;'

DROP

Execution: 0.014s

CREATE

Execution: 0.008s

INSERT

Execution: 0.05s

INSERT

Execution: 0.015s

INSERT

Execution: 0.016s

INSERT

Execution: 0.013s

%sql -c 'DROP TABLE IF EXISTS test;'
%sql -c 'CREATE TABLE test (id INT);'
for i in range(4):
    %sql -c 'INSERT INTO test(id) SELECT :i;'

%%sql
SELECT * FROM test;

Execution: 0.005s

	123 id Integer
1	0
2	1
3	2
4	3

Rows: 1-4 | Column: id | Type: integer

Change the maximum number of rows/columns to display¶

Use the -nrows and -ncols option to limit the number of rows and columns displayed:

%%sql -nrows 5 -ncols 2
SELECT * FROM default.titanic;

Execution: 0.008s

	123 pclass Integer	...	Abc home.dest Varchar(100)
1	3	...	[null]
2	3	...	[null]
3	3	...	[null]
4	3	...	[null]
5	3	...	[null]

Rows: 1-5 | Columns: 14

Export results to a JSON or CSV file¶

To export the results of a query to a CSV file:

%%sql -o titanic_age_clean.csv
SELECT
    *
FROM default.titanic
WHERE age IS NOT NULL LIMIT 5;

Execution: 0.008s

	123 pclass Integer	123 survived Integer	Abc name Varchar(164)	Abc sex Varchar(20)	123 age Double	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Double	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)
1	1	1	Allen, Miss. Elisabeth Walton	female	29.0	0	0	24160	211.3375	B5	S	2	[null]	St Louis, MO
2	1	1	Allison, Master. Hudson Trevor	male	0.92	1	2	113781	151.55	C22 C26	S	11	[null]	Montreal, PQ / Chesterville, ON
3	1	0	Allison, Miss. Helen Loraine	female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
4	1	0	Allison, Mr. Hudson Joshua Creighton	male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON
5	1	0	Allison, Mrs. Hudson J C (Bessie Waldo Daniels)	female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON

Rows: 5 | Columns: 14

file = open("titanic_age_clean.csv", "r")
print(file.read())
file.close()

To export the results of a query to a JSON file:

%%sql -o titanic_age_clean.json
SELECT
    *
FROM default.titanic
WHERE age IS NOT NULL LIMIT 5;

Execution: 0.008s

	123 pclass Integer	123 survived Integer	Abc name Varchar(164)	Abc sex Varchar(20)	123 age Double	123 sibsp Integer	Abc ticket Varchar(36)	123 fare Double	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)
1	3	1	McGowan, Miss. Anna "Annie"	female	15.0	0	330923	8.0292	[null]	Q	[null]	[null]	[null]
2	3	0	McGowan, Miss. Katherine	female	35.0	0	9232	7.75	[null]	Q	[null]	[null]	[null]
3	3	0	McNamee, Mr. Neal	male	24.0	1	376566	16.1	[null]	S	[null]	[null]	[null]
4	3	0	McNamee, Mrs. Neal (Eileen O'Leary)	female	19.0	1	376566	16.1	[null]	S	[null]	53	[null]
5	3	0	Meo, Mr. Alfonzo	male	55.5	0	A.5. 11206	8.05	[null]	S	[null]	201	[null]

Rows: 5 | Columns: 14

file = open("titanic_age_clean.json", "r")
print(file.read())
file.close()

Execute SQL files¶

To execute commands from a SQL file, use the following syntax:

file = open("query.sql", "w+")
file.write("SELECT version() AS version")
file.close()

Using the -f option, we can easily read SQL files:

%sql -f query.sql

Execution: 0.006s

	Abc version Varchar
1	479

Rows: 1-1 | Column: version | Type: varchar