Redash Advent Calendar 2018の12/9の記事です。

アイスタイルで分析基盤の構築・運用を行っているやすです。主にBigQuery, Digdag, Embulk, Redashといった技術を日々の業務では取り扱っています。最近は化粧品メーカー向けマーケティング支援サービス「ブランドオフィシャル」のETL処理部分を担当しています。

この記事ではRedashに接続しているPrestoのカタログ名を変更した場合の対応をまとめています。

RedashとPresto

RedashはBigQuery、MSSQL、MySQLといった様々なデータソースに接続できるダッシュボードツールです。Prestoも様々なデータソースに接続でき、クエリ上で異なるデータソースをJOINすることができます。RedashはPrestoにも対応しています。

prestodb.io

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の後に呼ばれるテーブルを抜き出します。

github.com

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のカタログの命名規則が固まっていればこんなことにはならなかったという自戒も込めた記事です笑

ann-toque’s diary

@YASU11552288 の思うことをつらつらと

Prestoのカタログ名を変更した際にRedashにでた影響と対応策