SQL Magic¶

In [ ]:

%sql -c sql_command -f input_file -ncols nb_of_cols -nrows nb_of_rows -o output_file

Executes SQL queries in the Jupyter cell.

Parameters¶

Name	Type	Optional	Description
-c / --command	str	✓	SQL Command to execute.
-f / --file	str	✓	Input file to execute.
-ncols	int	✓	Maximum number of columns to display.
-nrows	int	✓	Maximum number of rows to display.
-o / --output	str	✓	Output file. Use this option to export the result of the query to the CSV or JSON format.

Returns¶

vDataFrame : Result of the query.

Examples¶

These examples demonstrate the following:

Setting up the environment.
SQL Magic.
Getting the vDataFrame 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 one, create a new connection:

In [11]:

import verticapy as vp

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

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

In [12]:

# Connection using the VerticaDSN connection
vp.connect("VerticaDSN")

Load the extension:

In [13]:

%load_ext verticapy.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:

In [14]:

from verticapy.datasets import load_titanic, load_iris
titanic = load_titanic()
iris = load_iris()

SQL Magic¶

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

In [15]:

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

Execution: 0.006s

Out[15]:

	123 survived Integer	123 avg_fare Float	123 avg_age Float
1	0	23.4255950191571	30.6420462046205
2	1	52.3002593333333	29.3936572890026

Rows: 1-2 | Columns: 3

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

In [17]:

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

Execution: 0.006s

Out[17]:

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

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

You can use a single cell for multiple queries:

In [18]:

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

DROP
CREATE

Execution: 0.05s

Out[18]:

	Abc name Varchar(10)
1	Badr Ouali

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

To add comments to query:

In [19]:

%%sql
-- Comment Test
/* My Vertica Version */
SELECT version(); -- Looking at the current Vertica version.

Execution: 0.005s

Out[19]:

	Abc Varchar(128)
1

Rows: 1-1 | Column: version | Type: varchar(128)

Getting the vDataFrame of a query¶

Results are stored in a vDataFrame, which is assigned to a temporary variable called '_'. As shown in the following procedure, you can assign this to a variable to save your results.

In [20]:

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

Execution: 0.007s

Out[20]:

	123 age Numeric(6,3)	123 fare Numeric(10,5)	123 pclass Integer
1	2.0	151.55	1
2	30.0	151.55	1
3	25.0	151.55	1
4	39.0	0.0	1
5	71.0	49.5042	1
6	47.0	227.525	1
7	24.0	247.5208	1
8	36.0	75.2417	1
9	25.0	26.0	1
10	45.0	35.5	1
11	42.0	26.55	1
12	41.0	30.5	1
13	48.0	50.4958	1
14	45.0	26.55	1
15	33.0	5.0	1
16	28.0	47.1	1
17	17.0	47.1	1
18	49.0	26.0	1
19	36.0	78.85	1
20	46.0	61.175	1
21	27.0	136.7792	1
22	47.0	25.5875	1
23	37.0	83.1583	1
24	70.0	71.0	1
25	39.0	71.2833	1
26	31.0	52.0	1
27	50.0	106.425	1
28	39.0	29.7	1
29	36.0	31.6792	1
30	30.0	27.75	1
31	19.0	263.0	1
32	64.0	263.0	1
33	37.0	53.1	1
34	47.0	38.5	1
35	24.0	79.2	1
36	71.0	34.6542	1
37	38.0	153.4625	1
38	46.0	79.2	1
39	45.0	83.475	1
40	40.0	0.0	1
41	55.0	93.5	1
42	42.0	42.5	1
43	55.0	50.0	1
44	42.0	52.0	1
45	50.0	28.7125	1
46	46.0	26.0	1
47	50.0	26.0	1
48	32.5	211.5	1
49	58.0	29.7	1
50	41.0	51.8625	1
51	29.0	30.0	1
52	30.0	45.5	1
53	30.0	26.0	1
54	19.0	53.1	1
55	46.0	75.2417	1
56	54.0	51.8625	1
57	28.0	82.1708	1
58	65.0	26.55	1
59	44.0	90.0	1
60	55.0	30.5	1
61	47.0	42.4	1
62	37.0	29.7	1
63	58.0	113.275	1
64	64.0	26.0	1
65	65.0	61.9792	1
66	28.5	27.7208	1
67	45.5	28.5	1
68	23.0	93.5	1
69	29.0	66.6	1
70	18.0	108.9	1
71	47.0	52.0	1
72	38.0	0.0	1
73	22.0	135.6333	1
74	31.0	50.4958	1
75	36.0	40.125	1
76	55.0	59.4	1
77	33.0	26.55	1
78	61.0	262.375	1
79	50.0	55.9	1
80	56.0	26.55	1
81	56.0	30.6958	1
82	24.0	60.0	1
83	57.0	146.5208	1
84	62.0	26.55	1
85	67.0	221.7792	1
86	63.0	221.7792	1
87	61.0	32.3208	1
88	52.0	79.65	1
89	49.0	110.8833	1
90	40.0	27.7208	1
91	61.0	33.5	1
92	47.0	34.0208	1
93	64.0	75.25	1
94	60.0	26.55	1
95	54.0	77.2875	1
96	21.0	77.2875	1
97	57.0	164.8667	1
98	50.0	211.5	1
99	27.0	211.5	1
100	51.0	61.3792	1

Rows: 1-100 | Columns: 3

Assign the results to a new variable:

In [21]:

titanic_clean = _
display(titanic_clean)

	123 age Numeric(6,3)	123 fare Numeric(10,5)	123 pclass Integer
1	2.0	151.55	1
2	30.0	151.55	1
3	25.0	151.55	1
4	39.0	0.0	1
5	71.0	49.5042	1
6	47.0	227.525	1
7	24.0	247.5208	1
8	36.0	75.2417	1
9	25.0	26.0	1
10	45.0	35.5	1
11	42.0	26.55	1
12	41.0	30.5	1
13	48.0	50.4958	1
14	45.0	26.55	1
15	33.0	5.0	1
16	28.0	47.1	1
17	17.0	47.1	1
18	49.0	26.0	1
19	36.0	78.85	1
20	46.0	61.175	1
21	27.0	136.7792	1
22	47.0	25.5875	1
23	37.0	83.1583	1
24	70.0	71.0	1
25	39.0	71.2833	1
26	31.0	52.0	1
27	50.0	106.425	1
28	39.0	29.7	1
29	36.0	31.6792	1
30	30.0	27.75	1
31	19.0	263.0	1
32	64.0	263.0	1
33	37.0	53.1	1
34	47.0	38.5	1
35	24.0	79.2	1
36	71.0	34.6542	1
37	38.0	153.4625	1
38	46.0	79.2	1
39	45.0	83.475	1
40	40.0	0.0	1
41	55.0	93.5	1
42	42.0	42.5	1
43	55.0	50.0	1
44	42.0	52.0	1
45	50.0	28.7125	1
46	46.0	26.0	1
47	50.0	26.0	1
48	32.5	211.5	1
49	58.0	29.7	1
50	41.0	51.8625	1
51	29.0	30.0	1
52	30.0	45.5	1
53	30.0	26.0	1
54	19.0	53.1	1
55	46.0	75.2417	1
56	54.0	51.8625	1
57	28.0	82.1708	1
58	65.0	26.55	1
59	44.0	90.0	1
60	55.0	30.5	1
61	47.0	42.4	1
62	37.0	29.7	1
63	58.0	113.275	1
64	64.0	26.0	1
65	65.0	61.9792	1
66	28.5	27.7208	1
67	45.5	28.5	1
68	23.0	93.5	1
69	29.0	66.6	1
70	18.0	108.9	1
71	47.0	52.0	1
72	38.0	0.0	1
73	22.0	135.6333	1
74	31.0	50.4958	1
75	36.0	40.125	1
76	55.0	59.4	1
77	33.0	26.55	1
78	61.0	262.375	1
79	50.0	55.9	1
80	56.0	26.55	1
81	56.0	30.6958	1
82	24.0	60.0	1
83	57.0	146.5208	1
84	62.0	26.55	1
85	67.0	221.7792	1
86	63.0	221.7792	1
87	61.0	32.3208	1
88	52.0	79.65	1
89	49.0	110.8833	1
90	40.0	27.7208	1
91	61.0	33.5	1
92	47.0	34.0208	1
93	64.0	75.25	1
94	60.0	26.55	1
95	54.0	77.2875	1
96	21.0	77.2875	1
97	57.0	164.8667	1
98	50.0	211.5	1
99	27.0	211.5	1
100	51.0	61.3792	1

Rows: 1-100 | Columns: 3

Temporary results are stored in a vDataFrame, so you can call vDataFrame methods:

In [22]:

titanic_clean.corr()

Out[22]:

	"age"	"fare"	"pclass"
"age"	1.0	0.178575164117464	-0.403900427391808
"fare"	0.178575164117464	1.0	-0.568258663041552
"pclass"	-0.403900427391808	-0.568258663041552	1.0

Rows: 1-3 | Columns: 4

Using variables inside a query¶

You can use variables in a SQL query with the ':' operator. This variable can be a vDataFrame, a tablesample, a pandas.DataFrame, or any standard Python type.

In [23]:

import verticapy.stats as st

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

Out[23]:

	123 pclass Integer	123 avg_fare Float
1	1	93.1410288321168
2	2	21.9666833333333
3	3	12.8090323529412

Rows: 1-3 | Columns: 2

Use the 'class_fare' variable in a SQL query:

In [17]:

%%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

Out[17]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)	123 avg_fare Float
1	1	0		female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON	93.1410288321168
2	1	0		male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON	93.1410288321168
3	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON	93.1410288321168
4	1	0		male	39.0	0	0	112050	0.0	A36	S	[null]	[null]	Belfast, NI	93.1410288321168
5	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay	93.1410288321168
6	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124	New York, NY	93.1410288321168
7	1	0		male	[null]	0	0	PC 17318	25.925	[null]	S	[null]	[null]	New York, NY	93.1410288321168
8	1	0		male	24.0	0	1	PC 17558	247.5208	B58 B60	C	[null]	[null]	Montreal, PQ	93.1410288321168
9	1	0		male	36.0	0	0	13050	75.2417	C6	C	A	[null]	Winnipeg, MN	93.1410288321168
10	1	0		male	25.0	0	0	13905	26.0	[null]	C	[null]	148	San Francisco, CA	93.1410288321168
11	1	0		male	45.0	0	0	113784	35.5	T	S	[null]	[null]	Trenton, NJ	93.1410288321168
12	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]	London / Winnipeg, MB	93.1410288321168
13	1	0		male	41.0	0	0	113054	30.5	A21	S	[null]	[null]	Pomeroy, WA	93.1410288321168
14	1	0		male	48.0	0	0	PC 17591	50.4958	B10	C	[null]	208	Omaha, NE	93.1410288321168
15	1	0		male	[null]	0	0	112379	39.6	[null]	C	[null]	[null]	Philadelphia, PA	93.1410288321168
16	1	0		male	45.0	0	0	113050	26.55	B38	S	[null]	[null]	Washington, DC	93.1410288321168
17	1	0		male	[null]	0	0	113798	31.0	[null]	S	[null]	[null]	[null]	93.1410288321168
18	1	0		male	33.0	0	0	695	5.0	B51 B53 B55	S	[null]	[null]	New York, NY	93.1410288321168
19	1	0		male	28.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay	93.1410288321168
20	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay	93.1410288321168
21	1	0		male	49.0	0	0	19924	26.0	[null]	S	[null]	[null]	Ascot, Berkshire / Rochester, NY	93.1410288321168
22	1	0		male	36.0	1	0	19877	78.85	C46	S	[null]	172	Little Onn Hall, Staffs	93.1410288321168
23	1	0		male	46.0	1	0	W.E.P. 5734	61.175	E31	S	[null]	[null]	Amenia, ND	93.1410288321168
24	1	0		male	[null]	0	0	112051	0.0	[null]	S	[null]	[null]	Liverpool, England / Belfast	93.1410288321168
25	1	0		male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]	Los Angeles, CA	93.1410288321168
26	1	0		male	[null]	0	0	110465	52.0	A14	S	[null]	[null]	Stoughton, MA	93.1410288321168
27	1	0		male	47.0	0	0	5727	25.5875	E58	S	[null]	[null]	Victoria, BC	93.1410288321168
28	1	0		male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]	Lakewood, NJ	93.1410288321168
29	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]	Roachdale, IN	93.1410288321168
30	1	0		male	70.0	1	1	WE/P 5735	71.0	B22	S	[null]	269	Milwaukee, WI	93.1410288321168
31	1	0		male	39.0	1	0	PC 17599	71.2833	C85	C	[null]	[null]	New York, NY	93.1410288321168
32	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]	Montreal, PQ	93.1410288321168
33	1	0		male	50.0	1	0	PC 17761	106.425	C86	C	[null]	62	Deephaven, MN / Cedar Rapids, IA	93.1410288321168
34	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133	Philadelphia, PA	93.1410288321168
35	1	0		female	36.0	0	0	PC 17531	31.6792	A29	C	[null]	[null]	New York, NY	93.1410288321168
36	1	0		male	[null]	0	0	PC 17483	221.7792	C95	S	[null]	[null]	[null]	93.1410288321168
37	1	0		male	30.0	0	0	113051	27.75	C111	C	[null]	[null]	New York, NY	93.1410288321168
38	1	0		male	19.0	3	2	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB	93.1410288321168
39	1	0		male	64.0	1	4	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB	93.1410288321168
40	1	0		male	[null]	0	0