よしたく blog

ほぼ週刊で記事を書いています

#oss_gate のもくもく会で、Apache supersetのデモ環境を触ってみた

はじめに

yoshitaku_jpです。

OSS Gate東京もくもく会2018-04-24 にて触った、Apache supersetについて書きます。Apache supersetは、Airbnb社が開発していたBI(ビジネスインテリジェンス)ツールです。2017年5月2日にAPACHE INCUBATORになりました。他にOSSのBIツールと言えば、redashが有名でしょうか?他にOSSのBIツールはないかなと探していた中でたまたま見つけたApache supersetもくもく会で触ってみたメモです。

デモ環境を構築する

さっそくデモ環境を構築してみます。公式サイトのインストールの手順は下記です。 https://superset.incubator.apache.org/installation.html しかし、公式的にはDockerがないようで、有志の方々がDockerファイルを作っていただけているようです。今回はこちらを利用させてもらいます。 https://github.com/amancevice/superset

まずはクローンをします。

git clone https://github.com/amancevice/superset.git

フォルダ構成は下です。2018/5/1現在。

.
├── Dockerfile
├── LICENSE
├── README.md
├── docker-compose.yml
├── examples
│   ├── README.md
│   ├── celery
│   │   ├── docker-compose.yml
│   │   └── superset
│   │       └── superset_config.py
│   ├── demo.sh
│   ├── mysql
│   │   ├── docker-compose.yml
│   │   └── superset_config.py
│   ├── postgres
│   │   ├── docker-compose.yml
│   │   └── superset_config.py
│   └── sqlite
│       ├── docker-compose.yml
│       └── superset_config.py
└── superset
    ├── superset-demo
    └── superset-init

7 directories, 16 files

今回はデモ環境を使わせてもらうので、superset/exampleまで移動し、demo.shを使います

cd superset/examples/

bash demo.shと打つと、

bash demo.sh
Usage: bash demo.sh mysql|postgres|sqlite|celer

Usage: bash demo.sh mysql|postgres|sqlite|celeryと使いたいデータベースを選べと怒られますので、

bash demo.sh postgres

と打ちましょう。

mysqlなら下です。

bash demo.sh mysql

そうすると、demo.shに記述してあるdocker-composeが動き始めますので、カップラーメンを作るかコーヒーでも飲んで少し待ちます。この間に環境の構築とデモのデータを用意してくれます。途中で管理者ユーザの作成や、一番最後にPress RETURN to bring down demoの罠が仕込んであるので、EnterKeyの連打はしないようにしましょう。

自分はやりました。 流れている処理を全文載せておきます。長いです。

bash demo.sh postgres
Starting redis & postgres services...
WARNING: The MAPBOX_API_KEY variable is not set. Defaulting to a blank string.
Creating network "postgres_default" with the default driver
Creating volume "postgres_postgres" with default driver
Creating volume "postgres_redis" with default driver
Pulling postgres (postgres:)...
latest: Pulling from library/postgres
f2aa67a397c4: Already exists
8218dd41bf94: Pull complete
e9b7fa2e6bd8: Pull complete
7288a45ee17f: Pull complete
0d0f8a67376c: Pull complete
670a6cc35896: Pull complete
813e44621756: Pull complete
220a4cd75207: Pull complete
2f20067ce75a: Pull complete
4adafe999f39: Pull complete
a4e661925544: Pull complete
db5c3dc2b534: Pull complete
9f501bef0c46: Pull complete
Digest: sha256:1c2cc88d0573332ff1584f72f0cf066b1db764166786d85f5541b3fc1e362aee
Status: Downloaded newer image for postgres:latest
Creating postgres_postgres_1 ... done
Creating postgres_redis_1    ... done
Sleeping for 30s
Starting Superset...
WARNING: The MAPBOX_API_KEY variable is not set. Defaulting to a blank string.
postgres_redis_1 is up-to-date
postgres_postgres_1 is up-to-date
Creating postgres_superset_1 ... done
Sleeping for 30s
WARNING: The MAPBOX_API_KEY variable is not set. Defaulting to a blank string.
Username [admin]: admin
User first name [admin]: admin
User last name [user]: user
Email [admin@fab.org]: 
Password: 
Repeat for confirmation: 
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
/usr/local/lib/python3.5/dist-packages/flask_cache/jinja2ext.py:33: ExtDeprecationWarning: Importing flask.ext.cache is deprecated, use flask_cache instead.
  from flask.ext.cache import make_template_fragment_key
Recognized Database Authentications.
Admin User admin created.
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 4e6a06bad7a8, Init
INFO  [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -> 5a7bad26f2a7, empty message
INFO  [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -> 1e2841a4128, empty message
INFO  [alembic.runtime.migration] Running upgrade 1e2841a4128 -> 2929af7925ed, TZ offsets in data sources
INFO  [alembic.runtime.migration] Running upgrade 2929af7925ed -> 289ce07647b, Add encrypted password field
INFO  [alembic.runtime.migration] Running upgrade 289ce07647b -> 1a48a5411020, adding slug to dash
INFO  [alembic.runtime.migration] Running upgrade 1a48a5411020 -> 315b3f4da9b0, adding log model
INFO  [alembic.runtime.migration] Running upgrade 315b3f4da9b0 -> 55179c7f25c7, sqla_descr
INFO  [alembic.runtime.migration] Running upgrade 55179c7f25c7 -> 12d55656cbca, is_featured
INFO  [alembic.runtime.migration] Running upgrade 12d55656cbca -> 2591d77e9831, user_id
INFO  [alembic.runtime.migration] Running upgrade 2591d77e9831 -> 8e80a26a31db, empty message
INFO  [alembic.runtime.migration] Running upgrade 8e80a26a31db -> 7dbf98566af7, empty message
INFO  [alembic.runtime.migration] Running upgrade 7dbf98566af7 -> 43df8de3a5f4, empty message
INFO  [alembic.runtime.migration] Running upgrade 43df8de3a5f4 -> d827694c7555, css templates
INFO  [alembic.runtime.migration] Running upgrade d827694c7555 -> 430039611635, log more
INFO  [alembic.runtime.migration] Running upgrade 430039611635 -> 18e88e1cc004, making audit nullable
INFO  [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> 836c0bf75904, cache_timeouts
INFO  [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> a2d606a761d9, adding favstar model
INFO  [alembic.runtime.migration] Running upgrade a2d606a761d9, 836c0bf75904 -> d2424a248d63, empty message
INFO  [alembic.runtime.migration] Running upgrade d2424a248d63 -> 763d4b211ec9, fixing audit fk
INFO  [alembic.runtime.migration] Running upgrade d2424a248d63 -> 1d2ddd543133, log dt
INFO  [alembic.runtime.migration] Running upgrade 1d2ddd543133, 763d4b211ec9 -> fee7b758c130, empty message
INFO  [alembic.runtime.migration] Running upgrade fee7b758c130 -> 867bf4f117f9, Adding extra field to Database model
INFO  [alembic.runtime.migration] Running upgrade 867bf4f117f9 -> bb51420eaf83, add schema to table model
INFO  [alembic.runtime.migration] Running upgrade bb51420eaf83 -> b4456560d4f3, change_table_unique_constraint
INFO  [alembic.runtime.migration] Running upgrade b4456560d4f3 -> 4fa88fe24e94, owners_many_to_many
INFO  [alembic.runtime.migration] Running upgrade 4fa88fe24e94 -> c3a8f8611885, Materializing permission
INFO  [alembic.runtime.migration] Running upgrade c3a8f8611885 -> f0fbf6129e13, Adding verbose_name to tablecolumn
INFO  [alembic.runtime.migration] Running upgrade f0fbf6129e13 -> 956a063c52b3, adjusting key length
INFO  [alembic.runtime.migration] Running upgrade 956a063c52b3 -> 1226819ee0e3, Fix wrong constraint on table columns
WARNI [root] Could not find or drop constraint on `columns`
INFO  [alembic.runtime.migration] Running upgrade 1226819ee0e3 -> d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric
INFO  [alembic.runtime.migration] Running upgrade d8bc074f7aad -> 27ae655e4247, Make creator owners
INFO  [alembic.runtime.migration] Running upgrade 27ae655e4247 -> 960c69cb1f5b, add dttm_format related fields in table_columns
INFO  [alembic.runtime.migration] Running upgrade 960c69cb1f5b -> f162a1dea4c4, d3format_by_metric
INFO  [alembic.runtime.migration] Running upgrade f162a1dea4c4 -> ad82a75afd82, Update models to support storing the queries.
INFO  [alembic.runtime.migration] Running upgrade ad82a75afd82 -> 3c3ffe173e4f, add_sql_string_to_table
INFO  [alembic.runtime.migration] Running upgrade 3c3ffe173e4f -> 41f6a59a61f2, database options for sql lab
INFO  [alembic.runtime.migration] Running upgrade 41f6a59a61f2 -> 4500485bde7d, allow_run_sync_async
INFO  [alembic.runtime.migration] Running upgrade 4500485bde7d -> 65903709c321, allow_dml
INFO  [alembic.runtime.migration] Running upgrade 41f6a59a61f2 -> 33d996bcc382
INFO  [alembic.runtime.migration] Running upgrade 33d996bcc382, 65903709c321 -> b347b202819b, empty message
INFO  [alembic.runtime.migration] Running upgrade b347b202819b -> 5e4a03ef0bf0, Add access_request table to manage requests to access datastores.
INFO  [alembic.runtime.migration] Running upgrade 5e4a03ef0bf0 -> eca4694defa7, sqllab_setting_defaults
INFO  [alembic.runtime.migration] Running upgrade eca4694defa7 -> ab3d66c4246e, add_cache_timeout_to_druid_cluster
INFO  [alembic.runtime.migration] Running upgrade eca4694defa7 -> 3b626e2a6783, Sync DB with the models.py.
WARNI [root] slices
WARNI [root] tables
INFO  [alembic.runtime.migration] Running upgrade 3b626e2a6783, ab3d66c4246e -> ef8843b41dac, empty message
INFO  [alembic.runtime.migration] Running upgrade ef8843b41dac -> b46fa1b0b39e, Add json_metadata to the tables table.
INFO  [alembic.runtime.migration] Running upgrade b46fa1b0b39e -> 7e3ddad2a00b, results_key to query
INFO  [alembic.runtime.migration] Running upgrade 7e3ddad2a00b -> ad4d656d92bc, Add avg() to default metrics
INFO  [alembic.runtime.migration] Running upgrade ad4d656d92bc -> c611f2b591b8, dim_spec
INFO  [alembic.runtime.migration] Running upgrade c611f2b591b8 -> e46f2d27a08e, materialize perms
INFO  [alembic.runtime.migration] Running upgrade e46f2d27a08e -> f1f2d4af5b90, Enable Filter Select
INFO  [alembic.runtime.migration] Running upgrade e46f2d27a08e -> 525c854f0005, log_this_plus
INFO  [alembic.runtime.migration] Running upgrade 525c854f0005, f1f2d4af5b90 -> 6414e83d82b7, empty message
INFO  [alembic.runtime.migration] Running upgrade 6414e83d82b7 -> 1296d28ec131, Adds params to the datasource (druid) table
INFO  [alembic.runtime.migration] Running upgrade 1296d28ec131 -> f18570e03440, Add index on the result key to the query table.
INFO  [alembic.runtime.migration] Running upgrade f18570e03440 -> bcf3126872fc, Add keyvalue table
INFO  [alembic.runtime.migration] Running upgrade f18570e03440 -> db0c65b146bd, update_slice_model_json
INFO  [alembic.runtime.migration] Running upgrade db0c65b146bd -> a99f2f7c195a, rewriting url from shortner with new format
INFO  [alembic.runtime.migration] Running upgrade a99f2f7c195a, bcf3126872fc -> d6db5a5cdb5d, empty message
INFO  [alembic.runtime.migration] Running upgrade d6db5a5cdb5d -> b318dfe5fb6c, adding verbose_name to druid column
INFO  [alembic.runtime.migration] Running upgrade d6db5a5cdb5d -> 732f1c06bcbf, add fetch values predicate
INFO  [alembic.runtime.migration] Running upgrade 732f1c06bcbf, b318dfe5fb6c -> ea033256294a, empty message
INFO  [alembic.runtime.migration] Running upgrade b318dfe5fb6c -> db527d8c4c78, Add verbose name to DruidCluster and Database
INFO  [alembic.runtime.migration] Running upgrade db527d8c4c78, ea033256294a -> 979c03af3341, empty message
INFO  [alembic.runtime.migration] Running upgrade 979c03af3341 -> a6c18f869a4e, query.start_running_time
INFO  [alembic.runtime.migration] Running upgrade a6c18f869a4e -> 2fcdcb35e487, saved_queries
INFO  [alembic.runtime.migration] Running upgrade 2fcdcb35e487 -> a65458420354, add_result_backend_time_logging
INFO  [alembic.runtime.migration] Running upgrade a65458420354 -> ca69c70ec99b, tracking_url
INFO  [alembic.runtime.migration] Running upgrade ca69c70ec99b -> a9c47e2c1547, add impersonate_user to dbs
INFO  [alembic.runtime.migration] Running upgrade ca69c70ec99b -> ddd6ebdd853b, annotations
INFO  [alembic.runtime.migration] Running upgrade a9c47e2c1547, ddd6ebdd853b -> d39b1e37131d, empty message
INFO  [alembic.runtime.migration] Running upgrade ca69c70ec99b -> 19a814813610, Adding metric warning_text
INFO  [alembic.runtime.migration] Running upgrade 19a814813610, a9c47e2c1547 -> 472d2f73dfd4, empty message
INFO  [alembic.runtime.migration] Running upgrade 472d2f73dfd4, d39b1e37131d -> f959a6652acd, empty message
INFO  [alembic.runtime.migration] Running upgrade f959a6652acd -> 4736ec66ce19, empty message
INFO  [alembic.runtime.migration] Running upgrade 4736ec66ce19 -> 67a6ac9b727b, update_spatial_params
INFO  [alembic.runtime.migration] Running upgrade 67a6ac9b727b -> 21e88bc06c02
INFO  [alembic.runtime.migration] Running upgrade 21e88bc06c02 -> e866bd2d4976, smaller_grid
Revision ID: e866bd2d4976
Revises: 21e88bc06c02
Create Date: 2018-02-13 08:07:40.766277
INFO  [alembic.runtime.migration] Running upgrade e866bd2d4976 -> e68c4473c581, allow_multi_schema_metadata_fetch
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
2018-05-02 01:28:23,498:INFO:root:Syncing role definition
2018-05-02 01:28:23,498:INFO:root:Creating database reference
2018-05-02 01:28:23,981:INFO:root:Syncing Admin perms
2018-05-02 01:28:24,367:INFO:root:Syncing Alpha perms
2018-05-02 01:28:25,977:INFO:root:Syncing Gamma perms
2018-05-02 01:28:27,078:INFO:root:Syncing granter perms
2018-05-02 01:28:27,877:INFO:root:Syncing sql_lab perms
2018-05-02 01:28:28,776:INFO:root:Fetching a set of all perms to lookup which ones are missing
2018-05-02 01:28:28,971:INFO:root:Creating missing datasource permissions.
2018-05-02 01:28:28,983:INFO:root:Creating missing database permissions.
2018-05-02 01:28:29,020:INFO:root:Creating missing metrics permissions
2018-05-02 01:28:29,033:INFO:root:Cleaning faulty perms
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
Loading examples into <SQLA engine='postgresql+psycopg2://superset:superset@postgres:5432/superset'>
Creating default CSS templates
Loading energy related dataset
Creating table [wb_health_population] reference
2018-05-02 01:28:33,996:INFO:root:Creating database reference
2018-05-02 01:28:34,143:INFO:root:Database.get_sqla_engine(). Masked URL: postgresql+psycopg2://superset:XXXXXXXXXX@postgres:5432/superset
Loading [World Bank's Health Nutrition and Population Stats]
Creating table [wb_health_population] reference
2018-05-02 01:30:33,601:INFO:root:Creating database reference
Creating slices
Creating a World's Health Bank dashboard
Loading [Birth names]
Done loading table!
--------------------------------------------------------------------------------
Creating table [birth_names] reference
2018-05-02 01:31:01,594:INFO:root:Creating database reference
Creating some slices
Creating a dashboard
Loading [Random time series data]
Done loading table!
--------------------------------------------------------------------------------
Creating table [random_time_series] reference
2018-05-02 01:31:15,878:INFO:root:Creating database reference
Creating a slice
Loading [Random long/lat data]
Done loading table!
--------------------------------------------------------------------------------
Creating table reference
2018-05-02 01:33:26,121:INFO:root:Creating database reference
Creating a slice
Loading [Country Map data]
Done loading table!
--------------------------------------------------------------------------------
Creating table reference
2018-05-02 01:33:26,392:INFO:root:Creating database reference
Creating a slice
Loading [Multiformat time series]
Done loading table!
--------------------------------------------------------------------------------
Creating table [multiformat_time_series] reference
2018-05-02 01:33:26,940:INFO:root:Creating database reference
Creating some slices
Loading [Misc Charts] dashboard
Creating the dashboard
Loading [Paris GeoJson]
Creating table paris_iris_mapping reference
2018-05-02 01:33:31,672:INFO:root:Creating database reference
2018-05-02 01:33:31,732:INFO:root:Database.get_sqla_engine(). Masked URL: postgresql+psycopg2://superset:XXXXXXXXXX@postgres:5432/superset
Loading [San Francisco population polygons]
Creating table sf_population_polygons reference
2018-05-02 01:33:31,883:INFO:root:Creating database reference
Loading [Flights data]
2018-05-02 01:34:08,502:INFO:root:Creating database reference
Done loading table!
Loading [BART lines]
Creating table bart_lines reference
2018-05-02 01:34:08,796:INFO:root:Creating database reference
Loading DECK.gl demo
Loading deck.gl dashboard
Creating Scatterplot slice
Creating Screen Grid slice
Creating Hex slice
Creating Grid slice
Creating Polygon slice
Creating Arc slice
Creating Path slice
Creating a dashboard
Navigate to http://localhost:8088 to view demo
Press RETURN to bring down demo

最後から2行目にNavigate to http://localhost:8088 to view demoとあるように、http://localhost:8088をブラウザで叩けば、デモ画面にたどり着けます。

デモ画面

ログイン画面 設定した管理者ユーザ名とパスワードを入力しましょう。

デモなのでデータが壊れているところもありますが、表示できるところはすごくきれいに表示されています

まとめ

  • OSS Gateの活動でsupersetを触ってみました。
  • 次回は自分のデータを流して表示してみたりしたいです。
  • グラフィカルかつ、画像では伝わりませんが動きもダイナミックでした
  • いろいろ調べていたら、metabaseを見つけたので次回はこれも触ってみます。