Superset部署+连接MySQL8+省份地图可视化

您所在的位置：网站首页 › 海南省地图怎么画 › Superset部署+连接MySQL8+省份地图可视化

Superset部署+连接MySQL8+省份地图可视化

2024-03-15 01:05| 来源: 网络整理| 查看: 265

文章目录 Superset部署MySQL数据准备Superset对接MySQL数据源省份数据可视化解决Superset连不上MySQL8Superset是否具有动态可视化功能？权限管理报错：cannot import name 'TypedDict' from 'typing'2022-10-26补充全新安装步骤（一波流）

Superset部署 Apache Superset是一个轻量级的数据可视化平台 Superset的Web框架用的是Flask 官网：http://superset.apache.org/

1、创建具有sudo权限的用户，并切换到该用户，本文为miniconda

su - miniconda

2、安装MiniConda，然后创建虚拟环境，名为superset，Python版本3.7，选y

conda create --name superset python=3.7

3、进入虚拟环境

conda activate superset

4、安装SuperSet依赖

sudo yum install -y gcc gcc-c++ libffi-devel python-devel python-pip python-wheel python-setuptools openssl-devel cyrus-sasl-devel openldap-devel

5、安装（更新）setuptools和pip

pip install --upgrade setuptools pip -i https://pypi.douban.com/simple/

6、安装Superset（-i作用是指定镜像）

pip install apache-superset -i https://pypi.douban.com/simple/

7、初始化Superset数据库

export FLASK_APP=superset superset db upgrade

8、创建管理员用户，主要是填写Username和Password并记住，其它随意

superset fab create-admin

9、Superset初始化

superset init

10、安装 Gunicorn

pip install gunicorn -i https://pypi.douban.com/simple/

11、Superset启停

gunicorn --workers 6 --timeout 90 --bind hadoop107:8787 "superset.app:create_app()" --daemon 参数说明--workers进程个数--timeoutworker进程超时时间，超时会自动重启--bind绑定地址，即Superset访问地址，端口号建议8787--daemon后台运行 ps -ef | awk '/superset/ && !/awk/{print $2}' | xargs kill -9

12、superset启停脚本

touch superset.sh chmod 777 superset.sh vim superset.sh #!/bin/bash HOST=hadoop107 superset_status(){ result=`ps -ef | awk '/gunicorn/ && !/awk/{print $2}' | wc -l` if [[ $result -eq 0 ]]; then return 0 else return 1 fi } superset_start(){ source ~/.bashrc superset_status >/dev/null 2>&1 if [[ $? -eq 0 ]]; then conda activate superset;gunicorn --workers 6 --timeout 90 --bind $HOST:8787 --daemon 'superset.app:create_app()' else echo "superset正在运行" fi } superset_stop(){ superset_status >/dev/null 2>&1 if [[ $? -eq 0 ]]; then echo "superset未在运行" else ps -ef | awk '/gunicorn/ && !/awk/{print $2}' | xargs kill -9 fi } case $1 in start ) echo "启动Superset" superset_start ;; stop ) echo "停止Superset" superset_stop ;; restart ) echo "重启Superset" superset_stop superset_start ;; status ) superset_status >/dev/null 2>&1 if [[ $? -eq 0 ]]; then echo "superset未在运行" else echo "superset正在运行" fi esac

脚本使用

superset.sh start

13、浏览器访问hadoop107:8787，输入上面设定的用户名和密码

MySQL数据准备 CREATE DATABASE superset_test; USE superset_test; CREATE TABLE order_by_province( ymd date NOT NULL COMMENT '统计日期', province_name varchar(255) NOT NULL COMMENT '省份简称', province_id varchar(255) NOT NULL COMMENT '省份ID', iso_code varchar(255) NOT NULL COMMENT 'ISO 3316-2 国际地区编码', order_amount decimal(16,2) NOT NULL COMMENT '订单金额', PRIMARY KEY (ymd, province_id) USING BTREE ); INSERT INTO order_by_province VALUES ('2021-06-14','广东','44','CN-44',357141.20); INSERT INTO order_by_province VALUES ('2022-02-14','广西','45','CN-45',130352.03); INSERT INTO order_by_province VALUES ('2022-02-14','海南','46','CN-46',104162.35); INSERT INTO order_by_province VALUES ('2022-02-14','浙江','33','CN-33',2229.01); INSERT INTO order_by_province VALUES ('2022-02-15','广东','44','CN-44',357141.00); INSERT INTO order_by_province VALUES ('2022-02-15','广西','45','CN-45',352.00); INSERT INTO order_by_province VALUES ('2022-02-16','广东','44','CN-44',357141.00); INSERT INTO order_by_province VALUES ('2022-02-16','广西','45','CN-45',30352.04); INSERT INTO order_by_province VALUES ('2022-02-16','海南','46','CN-46',4162.35); INSERT INTO order_by_province VALUES ('2022-02-17','广东','44','CN-44',357141.00); INSERT INTO order_by_province VALUES ('2022-02-17','广西','45','CN-45',130352.05); INSERT INTO order_by_province VALUES ('2022-02-18','广东','44','CN-44',357141.00); INSERT INTO order_by_province VALUES ('2022-02-18','海南','46','CN-46',4162.35); Superset对接MySQL数据源

1、安装依赖

conda install mysqlclient

2、重启Superset

superset.sh restart

3、在界面创建数据源（Databases对应库，Datasets对应表）

4、填写MySQL连接参数，注意设置charset=UTF8，否则界面中文显示乱码

5、创建与表的连接

省份数据可视化

解决Superset连不上MySQL8

报错截图

报错信息

ERROR: (MySQLdb._exceptions.OperationalError) (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: /home/miniconda/miniconda3/envs/superset/lib/plugin/caching_sha2_password. so: cannot open shared object file: No such file or directory") (Background on this error at: http://sqlalche.me/e/13/e3q8)

其中关键：2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: 大概意思是：不能加载caching_sha2_password认证插件

解决办法修改MySQL8用户认证加密规则为mysql_native_password

ALTER USER '用户名'@'连接地址' IDENTIFIED WITH mysql_native_password BY '密码'; SELECT `user`,`host`,`plugin` FROM `mysql`.`user`;

修改后截图

Superset是否具有动态可视化功能？

暂时未发现动态可视化功能（自动刷新，类似心电图的效果），可手动刷新某个chart

权限管理术语译名说明User用户1个用户可以有1~n个角色Role角色1个角色可以有0~n个权限Permission权限大概分为：用户管理权限、数据源权限、仪表盘权限… 默认角色\权限用户管理数据源图表仪表盘Admin1111Alpha111Gamma1Public

需求：创建1个仪表盘，创建1个用户，该用户对该仪表盘具有只读权限

1、Settings=>List Roles 2、创建角色，名为库类型-主机名，例如mysql-hadoop107 3、为该角色赋权database access on ... 4、创建用户 5、给用户添加该角色以及 Gamma角色

报错：cannot import name ‘TypedDict’ from ‘typing’

如果报错ImportError: cannot import name 'TypedDict' from 'typing'，就删除Python3.7环境，装3.8

conda deactivate conda remove -n superset --all

以及删除数据库，以解决Error! User already exists

cd ~/.superset/ rm -f superset.db

1、创建虚拟环境，名为superset，Python版本3.8，选y

conda create --name superset python=3.8

2、进入虚拟环境

conda activate superset

3、安装SuperSet依赖

sudo yum install -y gcc gcc-c++ libffi-devel python-devel python-pip python-wheel python-setuptools openssl-devel cyrus-sasl-devel openldap-devel

4、安装（更新）setuptools和pip

pip install --upgrade setuptools pip -i https://pypi.douban.com/simple/

5、安装Superset（-i作用是指定镜像）

pip install apache-superset -i https://pypi.douban.com/simple/

6、安装markupsafe和pillow

python -m pip install markupsafe==2.0.1 conda install pillow

7、初始化Superset数据库

export FLASK_APP=superset superset db upgrade

8、创建管理员用户，主要是填写Username和Password并记住，其它随意

superset fab create-admin

9、Superset初始化

superset init

10、安装 Gunicorn

pip install gunicorn -i https://pypi.douban.com/simple/ 2022-10-26补充全新安装步骤（一波流）

创建隔离环境

conda create --name superset python=3.9

进入虚拟环境

conda activate superset

安装SuperSet依赖

sudo yum install -y gcc gcc-c++ libffi-devel python-devel python-pip python-wheel python-setuptools openssl-devel cyrus-sasl-devel openldap-devel

安装（更新）setuptools和pip

pip install --upgrade setuptools pip -i https://pypi.douban.com/simple/

安装SuperSet依赖包

pip install Werkzeug==2.0.2 pip install WTForms==2.3.3 pip install Flask==2.0.2

SQL Server

pip install pymssql==2.2.5

MySQL

conda install mysqlclient

ClickHouse

pip install sqlalchemy==1.3.16 pip install clickhouse-sqlalchemy==0.1.9

Impala

pip install impyla==0.18.0 conda install thriftpy2

安装Superset（-i作用是指定镜像）

pip install apache-superset -i https://pypi.douban.com/simple/

初始化Superset数据库

export FLASK_APP=superset superset db upgrade

创建管理员用户，主要是填写Username和Password并记住，其它随意

superset fab create-admin

Superset初始化

superset init

安装 Gunicorn

pip install gunicorn -i https://pypi.douban.com/simple/ 连接数据库 Impala impala://地址:端口/库名 ClickHouse clickhouse://用户名:密码@地址:端口/库名 SQL Server mssql+pymssql://用户名:密码@地址:端口/库名 MySQL mysql+pymysql://用户:密码@地址:端口/库名?charset=UTF8 DatabasePyPI packageConnection StringAmazon Athenapip install "PyAthenaJDBC>1.0.9pip install "PyAthena>1.2.0awsathena+rest://{aws_access_key_id}:{aws_secret_access_key}@athena.{region_name}.amazonaws.com/{Amazon Redshiftpip install sqlalchemy-redshiftredshift+psycopg2://:@:5439/Apache Drillpip install sqlalchemy-drilldrill+sadrill:// For JDBC drill+jdbc://Apache Druidpip install pydruiddruid://:@:/druid/v2/sqlApache Hivepip install pyhivehive://hive@{hostname}:{port}/{database}Apache Impalapip install impylaimpala://{hostname}:{port}/{database}Apache Kylinpip install kylinpykylin://:@:/?=&=Apache Pinotpip install pinotdbpinot://BROKER:5436/query?server=http://CONTROLLER:5983/Apache Solrpip install sqlalchemy-solrsolr://{username}:{password}@{hostname}:{port}/{server_path}/{collection}Apache Spark SQLpip install pyhivehive://hive@{hostname}:{port}/{database}Ascend.iopip install impylaascend://{username}:{password}@{hostname}:{port}/{database}?auth_mechanism=PLAIN;use_ssl=trueAzure MS SQLpip install pymssqlmssql+pymssql://UserName@presetSQL:[email protected]:1433/TestSchemaBig Querypip install pybigquerybigquery://{project_id}ClickHousepip install clickhouse-sqlalchemyclickhouse+native://{username}:{password}@{hostname}:{port}/{database}CockroachDBpip install cockroachdbcockroachdb://root@{hostname}:{port}/{database}?sslmode=disableDremiopip install sqlalchemy_dremiodremio://user:pwd@host:31010/Elasticsearchpip install elasticsearch-dbapielasticsearch+http://{user}:{password}@{host}:9200/Exasolpip install sqlalchemy-exasolexa+pyodbc://{username}:{password}@{hostname}:{port}/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBCGoogle Sheetspip install shillelagh[gsheetsapi]gsheets://Fireboltpip install firebolt-sqlalchemyfirebolt://{username}:{password}@{database}firebolt://{username}:{password}@{database}/{engine_name}Hologrespip install psycopg2postgresql+psycopg2://:@/IBM Db2pip install ibm_db_sadb2+ibm_db://IBM Netezza Performance Serverpip install nzalchemynetezza+nzpy://:@/MySQLpip install mysqlclientmysql://:@/Oraclepip install cx_Oracleoracle://PostgreSQLpip install psycopg2postgresql://:@/Trinopip install sqlalchemy-trinotrino://{username}:{password}@{hostname}:{port}/{catalog}Prestopip install pyhivepresto://SAP Hanapip install hdbcli sqlalchemy-hanapip install apache-superset[hana]hana://{username}:{password}@{host}:{port}Snowflakepip install snowflake-sqlalchemysnowflake://{user}:{password}@{account}.{region}/{database}?role={role}&warehouse={warehouse}SQLiteNo additional library neededsqlite://SQL Serverpip install pymssqlmssql://Teradatapip install teradatasqlalchemyteradata://{user}:{password}@{host}TimescaleDBpip install psycopg2postgresql://:@:/Verticapip install sqlalchemy-vertica-pythonvertica+vertica_python://:@/YugabyteDBpip install psycopg2postgresql://:@/

【本文地址】

Superset部署+连接MySQL8+省份地图可视化

Superset部署+连接MySQL8+省份地图可视化

今日新闻

推荐新闻