hchart (Beta)¶
In [ ]:
%%hchart -c sql_command -f input_file -k 'auto' -o output_file
Draws responsive charts using the High Chart API: https://api.highcharts.com/highcharts/ The returned object can be customized using the API parameters and the 'set_dict_options' method.
⚠ Warning: This function uses the unsupported HighChart Python API. For more information, see python-hicharts repository.
Parameters¶
| Name | Type | Optional | Description |
|---|---|---|---|
-c / --command | str | ✓ | SQL Command to execute. |
-f / --file | str | ✓ | Input File. You can use this option if you want to execute the input file. |
-k / --kind | str | ✓ | Chart Type.
|
-o / --output | str | ✓ | Output File. You can use this option if you want to export the result of the query to the CSV or JSON format. |
In [33]:
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")
Otherwise, to use an existing connection:
In [34]:
vp.connect("VerticaDSN")
Load the extension:
In [1]:
%load_ext verticapy.hchart
To load a sample dataset. Once loaded, these datasets are stored in the 'public' schema. You can change the target schema with the 'schema' parameter:
In [4]:
from verticapy.datasets import load_titanic, load_amazon, load_iris
titanic = load_titanic()
amazon = load_amazon()
iris = load_iris()
Drawing Graphics¶
To draw responsive charts from SQL queries:
In [5]:
%hchart -k pie -c "SELECT pclass, AVG(age) AS av_avg FROM titanic GROUP BY 1;"
Out[5]:
In [3]:
%%hchart -k line
SELECT
date,
AVG(number) AS number
FROM amazon
GROUP BY 1;
Out[3]:
In [4]:
%%hchart -k heatmap
SELECT
CORR_MATRIX(PetalLengthCm,
PetalWidthCm,
SepalLengthCm) OVER ()
FROM iris;
Out[4]:
In [5]:
%%hchart --kind hist
SELECT
pclass,
SUM(survived)
FROM titanic GROUP BY 1;
Out[5]:
In [6]:
%%hchart --kind scatter
SELECT
PetalLengthCm,
PetalWidthCm,
Species
FROM iris;
Out[6]:
In [7]:
%%hchart -k spearman
SELECT * FROM titanic;
Out[7]:
In [2]:
%%hchart --kind boxplot
SELECT * FROM titanic;
Out[2]:
Exporting to HTML¶
To export a chart to HTML:
In [9]:
%%hchart -k spearman -o 'my_graphic'
SELECT * FROM titanic;
Out[9]:
In [10]:
file = open("my_graphic.html", "r")
print(file.read())
file.close()
Using Variables¶
You can use variables in hcharts with the ':' operator.
In [5]:
import verticapy.stats as st
class_fare = titanic.groupby("pclass",
[st.avg(titanic["fare"])._as("avg_fare")])
class_fare
Out[5]:
 | 123 pclassInteger | 123 avg_fareFloat |
| 1 | 1 | 88.3537128205128 |
| 2 | 2 | 21.4416984555985 |
| 3 | 3 | 13.2290019637462 |
Rows: 1-3 | Columns: 2
In [6]:
class_fare.__genSQL__()
Out[6]:
In [8]:
%%hchart -k bar
SELECT * FROM :class_fare;
Out[8]:
Using SQL files¶
To create charts from a SQL file:
In [2]:
file = open("query.sql", "w+")
file.write("SELECT PetalLengthCm, PetalWidthCm, Species FROM iris;")
file.close()
In [3]:
%hchart -f query.sql -k scatter
Out[3]: