shobylogy

叩けシンプルの杖

喧嘩を生まないSQLコーディング規約の作り方

多くのエンジニアが関わるシステムにコーディング規約が必要なように、多くのデータサイエンティストが関わる分析プロジェクトにもSQLのコーディング規約が必要です。 良いコーディング規約は生産性をもたらしてくれますが、悪いコーディング規約は喧嘩を生みます。

今回はSQLの社内コーディング規約を決めるにあたって、プログラミング言語全般のコーディング規約を参考に、どのような規約を定めるのが適切かを考えました。

最終的には、チームメンバー全員でDataGripのFormatterを使ってスタイルを統一し、命名規則だけ必要最低限のルールだけを決めることになりました。

概要

  • 社内コーディング規約を作る上での前提
  • 喧嘩を生むコーディング規約
  • 喧嘩を生まないコーディング規約の運用方法
  • SQLの社内コーディング規約

社内コーディング規約を作る上での前提

社内コーディング規約を考える上で重要なことは、合意と生産性の2点です。

関係者全員に規約の内容ついて合意が得られており、規約を設けることで生産性が向上するのであればどのような規約を定めても問題ありません。

とはいえ、関係者全員が合意しやすく、生産性の高いコーディング規約を作るには、 業界標準のコーディング規約をベースにし、社内向けにカスタマイズして使用するのが現実的です。

喧嘩を生むコーディング規約とは

解釈により複数の意味に捉えられるルールがあったり、ルールが多く運用が難しい規約は、チーム内で喧嘩が生まれる原因になることがあります。

解釈が個人によって変わったり、ルールが多すぎる規約をきちんと運用しようとすると、コードレビューで書き方についての指摘が頻発するようになります。

これが続くと指摘する側もされる側も疲弊してしまい、下手をするとチームに不穏な空気が生まれるようになります。

コーディング規約で喧嘩をしないためには、必要最低限の厳格なルールのみを、容易な方法で運用していく必要があります。

喧嘩を生まないコーディング規約の運用方法

LinterやFormatterを用いることで、喧嘩を生まずにコーディング規約を運用することができます。

近年、多くのプログラミング言語では、業界標準のルールに沿ったLinterやFormatterが存在します。*1 チーム全員でこのようなLinterやFormatterを用いることで、必要最低限のルールを用意に運用していくことができます。

LinterやFormatterを使う上で重要なことは、LinterやFormatterで指摘されなかったことは極力問題にしないことです。 LinterやFormatterに指摘されなかった書き方については個人の好みの範疇の問題であり、それを押し付けるのは適切ではありません。

ただし、変数などの命名規則に関してはLinterやFormatterでは対応することができないため、 命名規則についてのみ何らかのルールを設けるのが良いと思います。

SQLの社内コーディング規約

上記考察から、私のチームでは、FormatterとしてDataGripを採用し、命名規則に関してはKickstarter SQL Style Guideを参考にすることにしました。

DataGrip: Cross-Platform IDE for Databases & SQL by JetBrains

Kickstarter SQL Style Guide · GitHub

現状、SQLには業界標準となるコーディング規約がなく*2、それに沿ったLinterやFormatterも存在しません。

そのため、SQL向けのIDEとしては実質的な業界標準であるDataGripをチーム全員で採用することで、統一したスタイルでSQLを書けるようにしました。

また、命名規則に関しては、比較的モダンで必要最低限であるという理由で、Kickstarter SQL Style Guideを参考にすることにしました。 こちらに関しては、参考であり、強制はしないようにしました。

まとめ

コーディング規約は、社内で合意が得られるルールを、生産性を上げるために導入するものですが、 導入の仕方が不適切な場合、喧嘩を生む原因になります。

コーディング規約で喧嘩を生まないためには、業界標準の必要最低限なルールをLinterやFormatterを用いて運用するのが適切です。 LinterやFormatterでカバーできない命名規則に関しては、追加でルールを設ける必要があります。

SQLにおいては業界標準の規約が存在しないため、実質的な業界標準のIDEであるDataGripをチーム全員で用いることで、統一したスタイルでSQLが書けるようになります。 また、命名規則に関してはモダンなKickstarter SQL Style Guideを参考にすることで適切な命名をすることができるようになります。

*1:RuboCopSwiftLintなど

*2:SQL Style Guideという規約が提案されてはいますが、業界標準と言われるほど浸透しておらず、長い命名を避ける傾向など、全体として内容が古いように感じられます。SQL向けのIDEであるDataGripが登場し、SQLのコード補完が効くようになった現状、多少長くても適切な名前を付けるのが適切だと思います。

Redashを用いたデータの民主化を進めやすくするための工夫

エンジニア、データサイエンティストの皆さん、こんにちは。

事業におけるデータの重要性が増した昨今、皆さんはビジネスサイドからの「こんなデータを出して欲しい!」といった要望に忙殺されていませんか? 往々にして、データ抽出作業は専門家による聖域になりがちであり、聖域化が進むとあらゆるデータ抽出の依頼が専門家の前に積み上がることになります。

この問題を解決する方法は大きく二つ、データ抽出作業の自動化と、データ抽出作業の民主化*1です。

今回は後者のデータ抽出の民主化について、Redashを用いて進めていく方法をお話しします。

データ抽出作業が専門家の聖域になりがちな理由

データ抽出作業は、環境構築と専門家のプライドという2つのハードルが理由で聖域になりがちです。

ビジネスサイドの人がデータが欲しいと思っても、すぐにSQLを実行できる環境がなく、 専門家が自分の仕事を手放さない場合、「じゃあ任せるか...」となってしまいます。

特に専門家は自分の仕事にプライドを持って行うあまり、ビジネスサイドの人の能力を軽視してしまう場合があります。*2

ITに関しては、プランナーをよく言えば「弱者」、悪く言えば「バカ扱い」する風潮があるように感じています。 「これはIT専門知識が必要で難しいからプランナーに直接扱わせるのは無理だからこちらでやろう、せめて××のインターフェースを被せよう」という方向になりがちです。

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く からの引用

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)

この問題を解決するためには、お互いが歩み寄り、SQLの実行環境を準備した上で、知識を伝えながら信頼して抽出作業を任せていくことが必要になります。

Redashを用いたデータの民主化

Redashを導入し、誰でもRedash上でSQLを書くことができる状態を作り、データを民主化を進めるすることができます。

RedashとはいわゆるBIツールの一種で、Web上でSQL実行環境とクエリの共有を提供するシンプルなツールです。

redash.io

Redashを導入することで、環境構築の手間がなくなり、専門家の秘匿だったSQLを共有化して知識の伝達を促すことができます。

Redashを社内に広げるための工夫

Redashを用いてデータの民主化を行っていく際の工夫をご紹介します。 以下のような工夫をすることで、ビジネスサイドの人もが戸惑わずにデータ抽出を行うことができるようになります。

  • きちんとドメインを設定する
  • Google SignInを導入する
  • SQLのテンプレートを用意する

きちんとドメインを設定する

Redashはあくまでも社内ツールですが、分かりやすいドメインを設定することで利用者の利便性が高まります。

IPアドレスやAWSのデフォルトドメインなどで利用してもらうのはやめましょう。

Google SignInを導入する

RedashにGoogle SignInを導入することで、利用者は「管理者にアカウント発行を依頼する」という作業が必要なくなり、使いたいときにRedashを使い始めることができます。

Setting up a Redash Instance | Redash

SQLのテンプレートを用意する

RedashのQuery Parameters機能やQuery Snippets機能を利用して、良く使われるクエリーのテンプレートを用意しておきましょう。

新規利用者の学習コストが下がります。

Query Parameters | Redash Query Snippets | Redash

以上のような細かい工夫をしながら、データの民主化を進めていきましょう。

データ抽出作業を「専門家の独占業務」から、「必要な人が自分で行う業務」に変えることができればデータの民主化は完了です。

まとめ

Redashを用いることでデータ抽出を専門家の独占業務から、必要な人が自分で行う業務に変えることができる。

きちんとドメインを設定し、Google SignInを導入し、SQLのテンプレートを用意することで、ビジネスサイドの人も戸惑わずにデータ抽出を行うことができるようになります。

*1:ところてんさんのこの記事が好きで表現を拝借しています。

*2:ビジネスサイドの人もその分野の専門家であることがほとんどなので、単にお互いの知識不足と理解不足が原因であることが多い印象です。

Redashでユーザーのpasswordをリセットする

Redash 4.0.0以降の場合、admin権限があれば管理画面から任意のユーザーのpasswordを再発行することができる。

Redash 4.0.0未満、もしくはadminユーザーのpasswordが分からなくなった場合、サーバーに入ることができれば、cliからpasswordをリセットすることができる。

$ cd /opt/redash/current
$ sudo -u redash bin/run ./manage.py users password {user's email} {new password}

github.com

Amazon AthenaでCSVに含まれていない情報をS3のフォルダ名から取得する

Amazon AthenaでCSVに含まれていない情報をS3のフォルダ名から取得する場合に苦労したため、解決方法を紹介しておきます。

結論から言うと、partition分割を使い、フォルダ毎にpartitionとして読み込むことで、AthenaのSQLからフォルダ名を参照することできます。

S3上のCSVファイルをAthenaから読む際に問題が発生するケース

以下のようなケースでは、S3上のCSVファイルにはuser_idが存在しないため、そのままCSVのカラムをAthenaのテーブルとしてマッピングすることができません。

  • S3上にCSV形式でユーザーの行動ログが入っている
  • ユーザーごとにS3上でフォルダが分けられている
  • CSVファイルにはユーザーIDが含まれておらず、ユーザーIDはS3のpathからしか分からない

行動ログCSV

date action
2018-04-24 view_page
2018-04-25 payment

ログ保存場所(user_idが1の場合)

s3://user-action-logs/1/action.log

解決方法

このような場合は、Athenaのテーブルをuser_idごとにpartition分割し、それぞれのフォルダとパーティションとして読み込むことで、SQL上から参照できるようになります。

docs.aws.amazon.com

テーブルの作成

以下のように PARTITIONED BYuser_id を指定します。

CREATE EXTERNAL TABLE user_actions (
    date timestamp,
    action string
)
PARTITIONED BY (user_id int)
LOCATION 's3://user-action-logs/'
-- 以下メタデータ省略

partitionの追加

以下のクエリを実行し、特定ディレクトリ以下をpartitionとして追加します。 読み込みたいpath全てに対して実行する必要があります。

ALTER TABLE user_actions ADD PARTITION (user=1) location 's3://user-action-logs/1/'

SQLでの参照

partitonは通常のカラムと同様に参照することができます。

SELECT
  user_id,
  date,
  action
FROM user_actions
WHERE user_id = 1

まとめ

Amazon AthenaでCSVに含まれていない情報をS3のフォルダ名から取得したい場合、partition分割を使い、フォルダ毎にpartitionとして読み込むことで、AthenaのSQLからフォルダ名を参照することできます。

S3に貯めてあるログデータを短い準備期間で分析可能な状態にする

S3に貯めているログデータを短い準備期間で分析可能な状態にする必要に迫られたため、AWSのサービスで実現する方法を検討しました。

基本的には、Amazon Athena、もしくはAmazon Redshift Spectrumを使い、S3上のファイルに対して直接クエリを打てる状態にするのが準備速度的にも好ましいと思われます。*1

今回は、主にそれらの二つのサービスの比較検討を行いました。

Amazon Athena

Amazon S3上のデータに対して、標準SQLでクエリを書くことができるサービスです。

aws.amazon.com

データの抽出、加工、読み込みはAmazon Glueを利用しており、CSVなどは簡単にテーブルにマッピングでき、サーバーレスで実行できます。*2

aws.amazon.com

料金はクエリ課金で、1 TB あたり 5 USDと格安です。

ただし、データのパーティションを適切に設定しないとフルスキャンになる上、S3のpathを /key=value/ 形式にしておかないと自動ではパーティションが設定できません。pathが規定のルールに則っていない場合、手動でpartitionを設定する必要があります。

aws.amazon.com

pathなどの制約はありつつも、サクッとS3のデータを分析したい場合は良い選択肢に入ると思います。

Amazon Redshift Spectrum

S3のデータをRedShift上に読み込み、PostgreSQL互換のクエリを書くことができるサービスです。

https://aws.amazon.com/jp/redshift/spectrum/aws.amazon.com

Athenaがサーバーレスなのに対して、こちらはRedShiftクラスターのインスタンスを立ち上げる必要があります。 こちらも1 TB あたり 5 USDのクエリ課金ですが、立ち上げたRedShiftクラスター分の費用は別途かかります。

aws.amazon.com

複雑なクエリに対しては、クラスター数を積めばAthenaよりも高速に処理できるのがメリットとなりそうです。

AthenaとRedshift Spectrumどちらを選ぶか

準備期間が短い分析案件の場合、Athenaを選択するのが良さそうです。 セットアップが容易なのに対して必要十分な機能が得られ、ResShiftクラスターの管理といった煩雑な作業をする必要がありません。

まとめ

S3に貯めてあるログデータを短い準備期間で分析可能な状態にする必要が生じた場合、 Amazon Athena、もしくはAmazon Redshift Spectrumを用いるのが良さそうです。

*1:なお、十分な準備期間がある場合は、RedShiftやBigQueryといったデータウェアハウスにS3のデータをimportする方法も選択肢に入ると思います。

*2:裏側はPrestoが使われているようです

スタートアップでの分析用ツール選定基準

今回は、スタートアップで分析用のツール導入を求められた場合の選定基準について、考えてみました。

スタートアップで分析業務にツールを使用する場合は、そのツールで問題解決が可能かどうか、費用対効果に見合うのかを考えた上で、そのツールが分析の専門家向けなのか、ビジネス上の意思決定のためか、2つの軸で考えると良さそうだと考えました。

概要

  • ツール選定の基本
  • 専門家向けツールか、ビジネス上の意思決定のためか
  • 専門家のためのツール
  • ビジネス上の意思決定ツール

ツール選定の基本

まず、そのツールが組織が抱える問題を解決できるか、費用対効果が見合うかを考え、試験導入した上で正式に決定しましょう。

ツールはあくまでも手段であるため、ツールの導入で組織が抱える問題を解決できるかを最初に考えましょう。 新しいツールを導入せず、現状使っている分析ツール(ExcelやSpreadSheetなど)で問題が解決できるのであれば、まずはそれで十分です。「なんとなく流行ってそうだから」といった理由でツールを導入するのはやめましょう。ツールは銀の弾丸ではありません。

また、ツールの導入にはコストが発生します。その費用対効果が導入コストに見合うかを考えましょう。

新しいツールを導入すると費用という実際のコストだけでなく、ツールを受け入れる側のチームメンバーの学習コストなど、目に見えないコストも発生します。それらも含めて費用対効果が見合うと判断できるのであれば、試用をして実際に問題が解決できるのかを試してみましょう。

実際にツールを導入するのはそれからでも遅くはないはずです。

専門家向けか、ビジネス上の意思決定のためか

分析ツールの導入の際は、そのツールが専門家向けのツールなのか、ビジネス上の意思決定のためのツールなのかは、はっきりさせておくと良いと思います。

専門家向けのツールは費用面、運用面でも高いコストを支払ってもよく、 ビジネス上の意思決定のためのツールは費用面、運用面でもリーズナブルである必要があります。

具体的に、求められることは以下のような違いがあります。

専門家向けツール

  • 高機能、多機能
  • 少人数しか使わない
  • 高価でもOK
  • 導入、管理コストは無視できる(専門家自身ができる範囲内)

ビジネス上の意思決定ツール

  • 必要十分な機能があれば良い
  • マーケターやディレクターなど、多くの人数が使う
  • 高価だと厳しい。アカウントが増えても問題ない料金体系が望ましい
  • 利用者は専門家ではないため、導入コストが無視できない
  • 利用者のサポートやツールの管理コストが無視できない

専門家向けの分析ツールは、専門家自身がきちんと活用でき、管理ができる範囲内であれば、ツール導入に高いコストを支払っても費用対効果が見合う場合が多いです。

対照的に、ビジネス上の意思決定のための分析ツールの場合、マーケターやディレクターなどの多くの人が使える必要があるため、必要十分な機能を誰でも使える状態で提供でき、導入コストが低く、維持管理にコストがかからないツールを選択するのが望ましいです。

専門家向けツール

高価なツールや、導入に手間がかかるツールでも、分析の専門家自身が管理できるのであれば何でも大丈夫です。 専門家のパフォーマンスが上がるのであれば、会社側としても支払いやすいと思います。

以下のようなツールが考えられます。

ビジネス上の意思決定ツール

基本的には、無料、もしくは安価なSaasを選択するのが良いと考えています。 スタートアップでは、ツールの管理のために人員を割り当てるのは難しいためです。 ただし、管理コストよりも導入によるメリットが大きい場合、OSSのツールも選択肢に入ります。

以下のようなツールが考えられます。

まとめ

スタートアップで分析ツールを導入する際は、そのツールで問題が解決可能かどうか、費用対効果が見合うかを考えましょう。 また、そのツールが専門家向けツールなのか、ビジネス上の意思決定ツールなのかを考えた上で選定すると、正しい意思決定がしやすいです。

分析業務でSQLを書くのに使っているツール

皆さんは分析業務でSQLを書く際には、どのようなツールを使っているでしょうか。

主に以下の2つのツールを使っています。

  • Sequel Pro
  • DataGrip

主にSequel Proはビューアとして、DataGripはエディタとして利用しています。

Sequel Pro

Sequel ProはオープンソースのMySQLのGUIクライアントです。

SQLを書かなくても特定テーブルのデータや、テーブル定義を閲覧することができるため、ビューアとして利用するのが便利です。

Sequel Pro

f:id:shoby:20180307224016p:plain

おおよそ以下のようなことができます。

  • 特定テーブルのデータの一部を閲覧
  • テーブル定義の閲覧
  • 特定カラムでの絞り込み
  • CSVでのデータのexport

DataGrip

DataGripはJetBrainsの提供しているSQL用のIDEです。

強力なコード補完とフォーマット機能により、高速にSQLを書くことができます。 あるとなしでは生産性が大きく変わるレベルです。

www.jetbrains.com

個人であれば年間$89、会社であれば年間$199で利用することができます。 30日間試用することができるため、ぜひ試してみましょう。

まとめ

私は分析業務でSQLを書く際、Sequel Proをビューア、DataGripをエディタとして利用しています。

他にもおすすめのツールがあればぜひご紹介ください。