会社の2018アドベントカレンダーに参加しました
投稿した記事
社内SNSの反応
ブランド・ロイヤルティが高いところ・低いところがわかる!!
ブランド・ロイヤルティ(brand loyalty)とは、消費者が、他の代替となるブランドがあるにもかかわらずある特定のブランドを購買し続けることをいう。 出典: フリー百科事典『ウィキペディア(Wikipedia)』
ブランド名でソートをかけてるこの表からだとぱっと見で傾向が掴みづらいので、共有ユーザ数でならべかえたりいろいろ見てみたいなと思いました!おっしゃってる通り割合で見るとまた違う傾向が見えそう。次楽しみにしてます!!
思うところ
社内でこのような分析はまだあまり浸透しておらず、平易な分析ではあるがどんな反応があるかを知りたくて、ブランドをテーマにしてみた。 「こういう分析を自分もしたい!!」という声が出てこなかったのが少し悲しい…
Prestoのカタログ名を変更した際にRedashにでた影響と対応策
Redash Advent Calendar 2018の12/9の記事です。
アイスタイルで分析基盤の構築・運用を行っているやすです。 主にBigQuery, Digdag, Embulk, Redashといった技術を日々の業務では取り扱っています。最近は化粧品メーカー向けマーケティング支援サービス「ブランドオフィシャル」のETL処理部分を担当しています。
この記事ではRedashに接続しているPrestoのカタログ名を変更した場合の対応をまとめています。
RedashとPresto
RedashはBigQuery、MSSQL、MySQLといった様々なデータソースに接続できるダッシュボードツールです。Prestoも様々なデータソースに接続でき、クエリ上で異なるデータソースをJOINすることができます。RedashはPrestoにも対応しています。
Prestoの詳細
Prestoでデータソースに接続する際のホスト名、ユーザー名やパスワードなどの接続情報を管理するファイルをカタログと呼びます。実際のカタログファイルの中身は以下のようになっています。
$ cat /export/presto/data/etc/catalog/sample.properties connector.name=sqlserver connection-url=jdbc:sqlserver://hostname;databaseName=sampledb connection-user=xxxx connection-password=xxxx
そしてクエリ内でカタログ名.DB名.テーブル名
と宣言することで、カタログに登録したデータソースにアクセスでき、異機種のDBで
もクエリ内で結合することが可能です。
SELECT * FROM catalog_name.database_name.table_name mssql JOIN other_catalog_name.database_name.table_name mysql ON msslq.id = mysql.id
RedashではQuery Resultsという機能がありますが、Prestoを使用することでより柔軟に異機種のDBの結合を可能にすることができます。
Prestoのカタログ名を変更した際にRedashにでた影響
先ほどのPrestoのサンプルのカタログ sample.properties
を使用したクエリをRedashに登録したとします。
SELECT * FROM sample.sample_db.sample_table mssql
カタログ名を変更する
例えば、適当につけてしまったカタログ名をあらたな命名規則に適応させたいなど、カタログ名を変更したい場合があったとして、変更したとします。
$ mv /export/presto/data/etc/catalog/sample.properties /export/presto/data/etc/catalog/msslq_sample_01.properties $ sudo /export/presto/bin/launcher restart
すると先ほどまでsample
で呼べていたカタログは呼べなくなり、msslq_sample_01
として呼ばなければいけなくなります。当然、Redashに登録しているクエリのカタログ名も変更しなくてはなりません。
どう対応するか
Prestoデータソースに接続しているRedshのクエリを取得する
Redashに登録されているクエリの情報はPostgreSQLに登録されていて、以下のクエリでどのデータソースにどのクエリが紐づいているかを取得することができます。
SELECT q.id AS query_id ,q.name AS query_name ,q.query AS query ,ds.id AS data_source_id ,ds.name AS data_source_name ,ds.type AS data_source_type FROM data_sources ds LEFT JOIN queries q ON ds.id = q.data_source_id
Python sqlparse モジュールを使用して、テーブルを抜き出す
変更されたカタログを使用しているクエリを特定するために、SQLでlike検索をかけてしまうと、カタログ名と似たような名前のカラムがあった場合はそのクエリも引っかかってしまいました。そのため、以下のPythonのモジュールを利用・少し変えて、クエリの中からFROMとJOINの後に呼ばれるテーブルを抜き出します。
import sqlparse from sqlparse.sql import IdentifierList, Identifier from sqlparse.tokens import Keyword, DML def is_subselect(parsed): if not parsed.is_group: return False for item in parsed.tokens: if item.ttype is DML and item.value.upper() == 'SELECT': return True return False def extract_from_part(parsed): from_seen = False for item in parsed.tokens: if from_seen: if is_subselect(item): for x in extract_from_part(item): yield x else: yield item elif item.ttype is Keyword and (item.value.upper() == 'JOIN' or item.value.upper() == 'FROM'): from_seen = True def extract_table_identifiers(token_stream): for item in token_stream: # print(item) if isinstance(item, IdentifierList): for identifier in item.get_identifiers(): yield identifier.get_name() elif isinstance(item, Identifier): yield item def extract_tables(sql): stream = extract_from_part(sqlparse.parse(sql)[0]) return list(extract_table_identifiers(stream))
さきほどRedashから取得したクエリの結果をresult
変数に入れ、上記の処理を実行、クエリのIDとクエリで使用されたテーブルの名前をカラムにもつpandasのDataFrameに格納します。
if __name__ == '__main__': table_list = pd.DataFrame(index=[], columns=['query_id','table_name' ]) for i in range(len(result.index)): query_id = result.at[i,0] query = result.at[i,2] if query == '': pass else: res = extract_tables(query) for table_name in res: series = pd.Series([query_id, table_name], index=table_list.columns) table_list = table_list.append(series, ignore_index = True)
結果のイメージです。
query_id | table_name |
---|---|
1 | sample.sample_db.sample_table mssql |
1 | users |
2 | sample.sample_db.sample_table data |
結果が入ったDataFrameに対して、str.contains()
を使うと指定カラムの中身に対し、特定の文字列を含むか検索することができます。今回はsample
カタログを使用しているレコードだけ取り出してみましょう。
table_list_contains = table_list[table_list['table_name'].astype(str).str.contains('sample')]
あとは新しいカタログ名にPostgreSQL登録されたクエリをSQLで書き換えるもよし、Redashの画面からクエリを書き換えるもよしという感じです。
おわり
RedashのPrestoデータソースを利用しているクエリをPostgreSQLにアクセスし取得し、Pythonで変更したいテーブルを使用してるクエリを特定しました。ツッコミどころとしては…最初からPrestoのカタログの命名規則が固まっていればこんなことにはならなかったという自戒も込めた記事です笑
MariaDB ColumnStore 検証
モチベーション
あ…ありのまま 昨年 起こった事を話すぜ!
おれはデータマートを作るPJTをやっていたと思ったら
いつのまにかデータウェアハウスを作っていた 何を言ってるか(ry
というわけでMariaDB の ColumnStore の環境を構築し、
以下のgithubに上がっている「InnoDBと比べたらColumnStore神やで」リポジトリを試して見た。
環境
- OS: centos7
- CPU: 4core
- Memory: 8GB
- SSD: 240GB
MariaDB ColumnStore 設定関連
下記のスライドの設定に基づいた。22スライド目ぐらいからインストール手順
www.slideshare.net ※ クラスター構成試したかったけど、今回はシングルノードで
結果
このスペックでの mariadb-columnstore-samples/flights/queries/
の
クエリの実行結果は以下の通り
query | time |
---|---|
2016_airline_summary.sql | 1.97 sec |
airline_delay_types_by_year.sql | 2.64 sec |
ca_2016_delays.sql | 1.33 sec |
bayarea_2016_delays.sql | 0.67 sec |
bayarea_nov_2016_delays.sql | 0.50 sec |
なるほど…InnoDBとの比較もして見ないと色々知らない自分には、早いのかすらわからないw
mariadb-columnstore-samples flights
でググルと
InnoDBやSparkとかと比べてるので、興味ある人はググってください ( ^ω^ )
Docker & Docker Compose勉強メモ
モチベーション
データ分析基盤に関する様々なミドルウェアを「手軽」に触りたいが、
毎回環境構築して、設定変えてインストールしてというのがめんどくさい。
dockerだと楽に色々試せると聞いたので、勉強し、
多くのミドルウェアに触れることで、自社に最適なデータ分析基盤の構想を固めたい。
dockerとは
コンテナ型の仮想環境構築ソフトウェア
dockerが早くて軽いのは、コンテナ型が既存のマシンリソース、OSを利用するため、
ハイパーバイザ型、ホスト型のように仮想ハードウェア、ゲストOSを
構築する必要がないからである。
コンテナ型:
Linux ContainerというLinuxのコンテナ技術をもちいた仮想化技術で、
ホストのOS上に他のプロセスから独立したプロセス上に実行環境を構築する
ハイパーバイザ型、ホスト型:
すでにインストールされているOS上に仮想マシン(仮想ハードウェア、ゲストOS)を
立ち上げ、その上に実行環境を構築する
インストール
動作環境:
MacBook Pro (Retina, 13-inch, Mid 2014) macOS High Sierra
インストール手順:
dockerの公式サイトからdmgファイルをダウンロードして、実行
コンテナ起動するまでの基本的な流れ
docekrイメージをDocker Hubから取得し、run コマンドでイメージファイルを指定し、
実行することでコンテナを立ち上げることができる。
docker composeとは
複数のコンテナを連携することで、1つのサービスの実行を可能にしてくれる。
下記リンクはMySQLとWordPressのコンテナをDocker Composeを用いて
リンクすることで、1つのサービスを実行している。