superset使用说明-V 1.0.doc

Superset的使用示例
1.Superset的功能介绍
1、我们可以通过连接数据库，去对数据库中的单个表进行配置，展示出柱状图，折线图，饼图，气泡图，词汇云，数字，环状层次图，有向图，蛇形图，地图，平行坐标，热力图，箱线图，树状图，热力图，水平图等图，官网上是不可以操作多个表的，不过我们可以操作视图，也就是说在数据库建好视图，也可以在superset中给表新增一列进行展示。
2、配置好了我们想要的图表之后我们可以把它添加到仪盘表进行展示，还可以去配置缓存，来加速仪盘表的查询，不必要没次都去查询数据库。
3、我们可以查看进行查询表的sql，也可以把查询导出为json，csv文件。它有自己的sql编辑器，我们可以在里面来编写sql。
本地登录公司已经部署好的superset环境，登录地址：192.168.6.64:8989
2.superset连接数据库
Superset支持多种的数据库连接，如MySQL，Oracle，Hive等，其连接方式如下：
数据库类型
在服务器执行命令
URL填写方式
MySQL
pip install mysqlclient
mysql://用户名:密码@IP/数据库名
Postgres
pip install psycopg2
postgresql+psycopg2://
Presto
pip install pyhive
presto://
Oracle
pip install cx_Oracle
oracle://
sqlite

sqlite://
Redshift
pip install sqlalchemy-redshift
redshift+psycopg2://
MSSQL
pip install pymssql
mssql://
Impala
pip install impyla
impala://
Hive/SparkSQL
pip install pyhive
hive://
2.1superset连接MySQL
登录到部署superset主机
1. 安装MySQL客户端依赖，执行命令：pip install mysqlclient
2. 进入superset的Web界面，点击sources下拉选项的Databases，如下图：

3. 进入数据库界面，点击“+”按钮进入数据库连接界面，填写正确字段后保存，操作如下：

4. 查看已经连接好的数据库

2.2 superset连接Kylin数据库
Superset 官方支持清单里没有 Kylin。但是由于 Superset 是通过 SQLAlchemy 访问数据源的，所以原则上只要实现一套 Kylin 的 SQLAlchemy 方言（dialect）+ DBAPI 实现，即可以对其做集成。
2.2.1 下载PyKylin
Python 的官方库中没有找到 Kylin 的 SQLAlchemy + DBAPI 实现，但是在 GitHub 上有一个 pykylin 项目。
下载地址：
2.2.2 修改pykylin代码
解压下载的pykylin4superset-master.zip
cd pykylin4superset-master/pykylin
修改dialect.py代码：
修改def get_table_names函数为下面代码：
def get_table_names(self, engine, schema=None, **kw): 
connection = engine.contextual_connect() 
return connection.connection.list_tables() 
添加以下代码：
def get_schema_names(self, engine, schema=None, **kw):  
connection = engine.contextual_connect()  
return connection.connection.list_schemas() 
修改create_connect_args方法：
def create_connect_args(self, url):   
        opts = url.translate_connect_args() 
        api_prefix = 'kylin/api/' 
        args = { 
            'username': opts['username'], 
            'password': opts['password'], 
            'endpoint': 'http://%s:%s/%s' % (opts['host'], opts['port'], api_prefix) 
        } 
        args.update(url.query) 
        return [], args 

修改connection.py代码：
添加以下代码：
def create_connect_args(self, url):
opts = url.translate_connect_args()
api_prefix = 'kylin/api/'
args = {
'username': opts['username'],
'password': opts['password'],
'endpoint': 'http://%s:%s/%s' % (opts['host'], opts['port'], api_prefix)
}
args.update(url.query)
return [], args
修改cursor.py代码：
修改_type_mapped方法：
def _type_mapped(self, result):   
        meta = self.description 
        size = len(meta) 
        for i in range(0, size): 
            column = meta[i] 
            tpe = column[1] 
            val = result[i] 
            if val is None: 
                pass 
            elif tpe == 'DATE': 
                val = parser.parse(val) 
            elif tpe == 'BIGINT' or tpe == 'INT' or tpe == 'TINYINT': 
                val = int(val) 
            elif tpe == 'DOUBLE' or tpe == 'FLOAT': 
                val = float(val) 
            elif tpe == 'BOOLEAN': 
                val = (val == 'true') 
            result[i] = val 
        return result 
2.2.3 install
执行下面命令：
pip install -r ./requirements.txt
python setup.py install
重启superset：superset runserver –p 8989
2.2.4 superset 连接Kylin数据库

3.Superset操作数据库
经过上边的步骤就连接上了数据库，下边就可以进行数据的可视化操作了。首先点击SQL测试下拉菜单下的SQL编辑器按钮。如下图所示：

SQL语句的执行结果如下：

点击Visualize按钮进入数据可视化编辑窗口：

4.superset图形使用案例
4.1 Distribution – Bar Chart（分布-条形图）
案例需求：统计每个state的总人数，总女生人数，总男生人数。
SELECT state AS state,
sum(num) AS sum__num,
sum(sum_girls) AS sum__sum_girls,
sum(sum_boys) AS sum__sum_boys
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 18:21:31'
AND ds <= '2017-07-05 18:21:31'
GROUP BY state
ORDER BY sum__num DESC LIMIT 50000

进入可视化界面，可按需求显示图形：

在图形的右上方有对图形的保存等一些操作：

图表的保存：

查看保存的图表：

4.2 Table View – 表视图
需求1：根据name，gender分组，统计总人数。
SQL：
SELECT name AS name,
gender AS gender,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 18:24:31'
AND ds <= '2017-07-05 18:24:31'
GROUP BY name,
gender
ORDER BY sum__num DESC LIMIT 50000

4.3 Pivot Table – 数据透视表
数据透视表（Pivot Table）是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。
案例需求：按照name，gender分组，对每个state人数进行统计。
SQL：
SELECT gender AS gender,
state AS state,
name AS name,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 18:25:21'
AND ds <= '2017-07-05 18:25:21'
GROUP BY gender,
state,
name
ORDER BY sum__num DESC LIMIT 50000

4.4 Time Series – Line Chart – 时序线图
案例需求：查看每个state人数总数随时间的变化。
SQL：
SELECT state AS state,
ds AS __timestamp,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
INNER JOIN
(SELECT state AS state__,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 18:26:35'
AND ds <= '2017-07-05 18:26:35'
GROUP BY state
ORDER BY sum__num DESC LIMIT 50) AS anon_1 ON state = state__
WHERE ds >= '1917-07-05 18:26:35'
AND ds <= '2017-07-05 18:26:35'
GROUP BY state,
ds
ORDER BY sum__num DESC LIMIT 50000

4.5 Time Series – Stacked – 时序面积图
面积图强调数量随时间而变化的程度，也可用于引起人们对总值趋势的注意。例如，表示随时间而变化的产生的数据可以绘制在面积图中以强调总数据量。
案例需求：根据每个state每年的总人数的时序图-叠图。
SQL：
SELECT state AS state,
ds AS __timestamp,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
INNER JOIN
(SELECT state AS state__,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 18:27:06'
AND ds <= '2017-07-05 18:27:06'
GROUP BY state
ORDER BY sum__num DESC LIMIT 50) AS anon_1 ON state = state__
WHERE ds >= '1917-07-05 18:27:06'
AND ds <= '2017-07-05 18:27:06'
GROUP BY state,ds
ORDER BY sum__num DESC LIMIT 50000

4.6 Time Series – Bar Chart – 时序柱形图
案例需求：比较不同的年份每个state的人数差异的时序柱形图。
SQL:
SELECT state AS state,
ds AS __timestamp,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
INNER JOIN
(SELECT state AS state__,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE state NOT IN ('other')
AND ds >= '1917-07-05 18:28:57'
AND ds <= '2017-07-05 18:28:57'
GROUP BY state
ORDER BY sum__num DESC LIMIT 50) AS anon_1 ON state = state__
WHERE ds >= '1917-07-05 18:28:57'
AND ds <= '2017-07-05 18:28:57'
AND state NOT IN ('other')
GROUP BY state,
ds
ORDER BY sum__num DESC LIMIT 50000

4.7 Distribution – NVD3 - Pie Chart –饼图
案例：比较每个state的人数占总人数的比例。
SQL：
SELECT state AS state,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 18:29:51'
AND ds <= '2017-07-05 18:29:51'
AND state NOT IN ('other')
GROUP BY state
ORDER BY sum__num DESC LIMIT 50000

4.8 Bubble Chart – 气泡图
SQL语句：
SELECT country_name AS country_name,
region AS region,
SUM(`SP_POP_TOTL`) AS `sum__SP_POP_TOTL`,
SUM(`SP_RUR_TOTL_ZS`) AS `sum__SP_RUR_TOTL_ZS`,
SUM(`SP_DYN_LE00_IN`) AS `sum__SP_DYN_LE00_IN`
FROM wb_health_population
WHERE year >= STR_TO_DATE('2011-01-01 00:00:00', '%%Y-%%m-%%d %%H:%%i:%%s')
AND year <= STR_TO_DATE('2011-01-02 00:00:00', '%%Y-%%m-%%d %%H:%%i:%%s')
AND country_code NOT IN ('TCA', 'MNP', 'DMA', 'MHL', 'MCO', 'SXM', 'CYM', 'TUV', 'IMY', 'KNA', 'ASM', 'ADO', 'AMA', 'PLW')
GROUP BY country_name,
region
ORDER BY `sum__SP_POP_TOTL` DESC LIMIT 50000

4.9 MarKup – 标记图

4.10 Word Clould – 文字云
案例需求：显示所有的name，且看到使用这个名字的人数比重。
SQL语句：
SELECT name AS name,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 17:39:23'
AND ds <= '2017-07-05 17:39:23'
GROUP BY name
ORDER BY sum__num DESC LIMIT 50000

4.10 Sunburst – 旭日图
案例需求：第一层gender，第二层name，统计人数。
SQL：
SELECT gender AS gender,
name AS name,
sum(num) AS sum__num,
sum(num) AS sum__num
FROM
(select *
from birth_names) AS expr_qry
WHERE ds >= '1917-07-05 17:56:35'
AND ds <= '2017-07-05 17:56:35'
GROUP BY gender,
name
ORDER BY sum__num DESC LIMIT 50000

4.11 Parallel Coordinates –平行坐标图
平行坐标图为一种数据可视化的方式。以多个垂直平行的坐标轴表示多个维度，以维度上的刻度表示在该属性上对应值，以颜色区分类别。每个样本在各个维度上对应一个值，相连而得的一个折线表示该样本。
SQL：
SELECT country_name AS country_name,
SUM(`SP_POP_TOTL`) AS `sum__SP_POP_TOTL`,
SUM(`SP_RUR_TOTL_ZS`) AS `sum__SP_RUR_TOTL_ZS`,
SUM(`SH_DYN_AIDS`) AS `sum__SH_DYN_AIDS`,
AVG(`NY_GNP_PCAP_CD`) AS `avg__NY_GNP_PCAP_CD`
FROM wb_health_population
WHERE year >= STR_TO_DATE('2011-01-01 00:00:00', '%%Y-%%m-%%d %%H:%%i:%%s')
AND year <= STR_TO_DATE('2011-01-01 00:00:00', '%%Y-%%m-%%d %%H:%%i:%%s')
GROUP BY country_name
ORDER BY `sum__SP_POP_TOTL` DESC LIMIT 50000

4.12 Box plot – 盒图
盒图(boxplot)：摆弄数据离散度的一种图形。它对于显示数据的离散的分布情况效果不错。在软件工程中，Nassi和Shneiderman 提出了一种符合结构化程序设计原则的图形描述工具，叫做盒图，也被称为N-S图。
SQL：
SELECT region AS region,
year AS __timestamp,
SUM(`SP_POP_TOTL`) AS `sum__SP_POP_TOTL`
FROM wb_health_population
INNER JOIN
(SELECT region AS region__,
SUM(`SP_POP_TOTL`) AS `sum__SP_POP_TOTL`
FROM wb_health_population
WHERE year >= STR_TO_DATE('1960-01-01 00:00:00', '%%Y-%%m-%%d %%H:%%i:%%s')
AND year <= STR_TO_DATE('2017-07-11 09:46:33', '%%Y-%%m-%%d %%H:%%i:%%s')
GROUP BY region
ORDER BY `sum__SP_POP_TOTL` DESC LIMIT 25) AS anon_1 ON region = region__
WHERE year >= STR_TO_DATE('1960-01-01 00:00:00', '%%Y-%%m-%%d %%H:%%i:%%s')
AND year <= STR_TO_DATE('2017-07-11 09:46:33', '%%Y-%%m-%%d %%H:%%i:%%s')
GROUP BY region,
year
ORDER BY `sum__SP_POP_TOTL` DESC LIMIT 50000