「コードとしてのデータベース」エクスペリエンス

SQL、これ以上に簡単なものはないでしょうか? 私たちはそれぞれ簡単なリクエストを書くことができます - 私たちは入力します select、必要な列をリストしてから、 から、テーブル名、いくつかの条件 コラボレー それだけです - 有用なデータはポケットの中にあり、その時点でどの DBMS が内部にあるか (あるいはおそらく) 関係なく (ほぼ) まったくDBMSではありません）。 その結果、ほぼすべてのデータ ソース (リレーショナルであってもそうでなくても) の操作を、通常のコードの観点から考えることができます (バージョン管理、コード レビュー、静的分析、自動テストなど、すべてのことを意味します)。 そして、これはデータ自体、スキーマ、移行だけでなく、一般にストレージの寿命全体に当てはまります。 この記事では、「コードとしてのデータベース」という観点から、さまざまなデータベースを操作する際の日常的なタスクと問題について説明します。

そして、すぐに始めましょう 蛇。 「SQL 対 ORM」タイプの最初の戦いが注目されたのは、 ペトリーヌ・ルーシ以前.

オブジェクトリレーショナルマッピング

ORM サポーターは伝統的に、開発のスピードと容易さ、DBMS からの独立性、クリーンなコードを重視しています。 私たちの多くにとって、データベース (そして多くの場合データベース自体) を操作するためのコードは、

通常はこんな感じになります...

@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
        @UniqueConstraint(columnNames = "STOCK_NAME"),
        @UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "STOCK_ID", unique = true, nullable = false)
    public Integer getStockId() {
        return this.stockId;
    }
  ...

モデルは巧妙な注釈でハングされており、舞台裏のどこかで勇敢な ORM が大量の SQL コードを生成して実行します。 ちなみに、開発者は、何キロにもわたる抽象化を使用してデータベースから自分自身を分離しようと最善を尽くしています。 「SQL嫌い」.

バリケードの反対側では、純粋な「手作り」SQL の支持者は、追加のレイヤーや抽象化なしで DBMS からすべての機能を絞り出すことができることに注目しています。 その結果、「データ中心」プロジェクトが出現し、特別な訓練を受けた人々（彼らは「基礎主義者」でもあり、「基礎主義者」でもあり、「基礎主義者」でもありますなど）と開発者がデータベースに関与します。詳細には触れずに、既成のビューとストアド プロシージャを「プル」するだけで済みます。

両方の長所を兼ね備えていたらどうなるでしょうか? 人生を肯定する名前の素晴らしいツールでこれがどのように行われるか はいSQL。 私の無料翻訳で一般的な概念の数行を紹介します。より詳しく知ることができます。 ここで.

Clojure は DSL を作成するための優れた言語ですが、SQL 自体も優れた DSL なので、別の言語は必要ありません。 S 表現は素晴らしいですが、ここでは何も新しいものは追加されません。 その結果、括弧のための括弧が得られます。 不賛成？ 次に、データベース上の抽象化が漏れ始め、関数との戦いが始まる瞬間を待ちます。 (生のSQL)

それで、どうすればいいでしょうか？ SQL を通常の SQL のままにしておきます (リクエストごとに XNUMX つのファイル)。

-- name: users-by-country
select *
  from users
 where country_code = :country_code

...そしてこのファイルを読み取り、通常の Clojure 関数に変換します。

(defqueries "some/where/users_by_country.sql"
   {:connection db-spec})

;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)

「SQL 単体、Clojure 単体」の原則に従うことで、次のことが得られます。

• 構文的に驚くようなことはありません。 あなたのデータベースは (他のデータベースと同様に) SQL 標準に 100% 準拠しているわけではありませんが、これは Yesql にとっては問題ではありません。 SQL と同等の構文を持つ関数を探して時間を無駄にすることはありません。 関数に戻る必要はありません (raw-sql "some('funky'::SYNTAX)")).
• 最高のエディターのサポート。 エディタにはすでに優れた SQL サポートが備わっています。 SQL を SQL として保存すると、簡単に使用できます。
• チームの互換性。 DBA は、Clojure プロジェクトで使用する SQL を読み書きできます。
• パフォーマンスのチューニングが容易になります。 問題のあるクエリの計画を立てる必要がありますか? クエリが通常の SQL である場合、これは問題になりません。
• クエリの再利用。 これは単なる古い SQL なので、同じ SQL ファイルを他のプロジェクトにドラッグ アンド ドロップして、共有するだけです。

私の意見では、このアイデアは非常にクールであると同時に非常にシンプルであり、そのおかげでこのプロジェクトは多くの支持を得ました。 フォロワー さまざまな言語で。 そして次に、SQL コードを ORM をはるかに超えた他のすべてのコードから分離するという同様の哲学の適用を試みます。

IDE および DB マネージャー

まずは簡単な日常業務から始めましょう。 多くの場合、データベース内のオブジェクトを検索する必要があります。たとえば、スキーマ内のテーブルを見つけて、その構造 (どのような列、キー、インデックス、制約が使用されているか) を調べる必要があります。 そして、グラフィカル IDE や小さな DB マネージャーには、まず第一に、まさにこれらの機能が期待されます。 高速で、必要な情報を含むウィンドウが表示されるまで XNUMX 分も待つ必要がなく (特にリモート データベースへの接続が遅い場合)、同時に受け取った情報が新鮮で関連性があるため、キャッシュされたジャンクではありません。 さらに、データベースが複雑で大規模になり、その数が増えるほど、これを行うことは難しくなります。

しかし、通常はマウスを捨ててコードを書くだけです。 「HR」スキーマにどのテーブル (およびどのプロパティ) が含まれているかを確認する必要があるとします。 ほとんどの DBMS では、information_schema からの次の単純なクエリで目的の結果を得ることができます。

select table_name
     , ...
  from information_schema.tables
 where schema = 'HR'

このような参照テーブルの内容は、各 DBMS の機能に応じてデータベースごとに異なります。 たとえば、MySQL の場合、同じ参考書から、この DBMS に固有のテーブル パラメータを取得できます。

select table_name
     , storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
     , row_format     -- Формат строки ("Fixed", "Dynamic" etc)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle は information_schema を知りませんが、知っています。 Oracle メタデータ大きな問題は発生しません。

select table_name
     , pct_free       -- Минимум свободного места в блоке данных (%)
     , pct_used       -- Минимум используемого места в блоке данных (%)
     , last_analyzed  -- Дата последнего сбора статистики
     , ...
  from all_tables
 where owner = 'HR'

ClickHouse も例外ではありません。

select name
     , engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
     , ...
  from system.tables
 where database = 'HR'

Cassandra でも同様のことができます (テーブルの代わりに列ファミリーがあり、スキーマの代わりにキースペースがあります)。

select columnfamily_name
     , compaction_strategy_class  -- Стратегия сборки мусора
     , gc_grace_seconds           -- Время жизни мусора
     , ...
  from system.schema_columnfamilies
 where keyspace_name = 'HR'

他のほとんどのデータベースでも、同様のクエリを作成できます (Mongo でさえ、 特別なシステムコレクション、システム内のすべてのコレクションに関する情報が含まれています)。

もちろん、この方法では、テーブルに関する情報だけでなく、オブジェクト全般に関する情報も取得できます。 たとえば、habra の一連の記事「PostgreSQL データベースを文書化するための関数」(アイブ, ベン, ジム）。 もちろん、この山のようにクエリを頭の中に入れて常に入力するのはとても楽しいので、お気に入りの IDE/エディターには、頻繁に使用するクエリのスニペットのセットがあらかじめ用意されており、あとは次のクエリを入力するだけです。オブジェクト名をテンプレートに追加します。

その結果、オブジェクトをナビゲートして検索するこの方法はより柔軟になり、時間を大幅に節約し、必要な形式で情報を正確に取得できるようになります (たとえば、次の投稿で説明されています)。 「データベースから任意の形式でデータをエクスポート: IntelliJ プラットフォームで IDE ができること」).

オブジェクト操作

必要なオブジェクトを見つけて調べたら、それらを使って何か役に立つことをしてみましょう。 もちろん、キーボードから指を離す必要もありません。

テーブルを削除するだけでは、ほとんどすべてのデータベースで同じように見えることは周知の事実です。

drop table hr.persons

しかし、テーブルを作成すると、さらに面白くなります。 ほとんどすべての DBMS (多くの NoSQL を含む) は、何らかの形式で「テーブルを作成」​​でき、その主要部分 (名前、列のリスト、データ型) はわずかに異なりますが、その他の詳細は大幅に異なる可能性があり、データベースに依存します。内部デバイスと特定の DBMS の機能。 私のお気に入りの例は、Oracle のドキュメントには「テーブル作成」構文の「裸の」BNF しか存在しないことです。 31ページを占める。 他の DBMS には、より控えめな機能しかありませんが、それぞれの DBMS には、テーブルを作成するための興味深いユニークな機能が多数あります (ポストグレス, mysqlの, ゴキブリ, カサンドラ）。 別の IDE (特にユニバーサル) のグラフィカルな「ウィザード」がこれらの機能をすべてカバーできるとは考えにくく、たとえそれができたとしても、気の弱い人にとっては壮観なものではないでしょう。 同時に、正確かつタイムリーに書かれた声明 テーブルを作成する これらをすべて簡単に使用できるようになり、ストレージとデータへのアクセスが信頼性が高く、最適かつ可能な限り快適になります。

また、多くの DBMS には、他の DBMS では使用できない独自の特定のタイプのオブジェクトがあります。 さらに、データベース オブジェクトだけでなく、DBMS 自体に対しても操作を実行できます。たとえば、プロセスを「強制終了」し、一部のメモリ領域を解放し、トレースを有効にし、「読み取り専用」モードに切り替えるなどです。

では、少し描いてみましょう

最も一般的なタスクの XNUMX つは、データベース オブジェクトを含む図を構築し、オブジェクトとオブジェクト間の接続を美しい図で確認することです。 ほぼすべてのグラフィカル IDE、個別の「コマンド ライン」ユーティリティ、特殊なグラフィカル ツール、およびモデラーでこれを行うことができます。 彼らはあなたのために「できる限り最善を尽くして」何かを描画し、設定ファイル内のいくつかのパラメータまたはインターフェイスのチェックボックスを使用するだけで、このプロセスに少し影響を与えることができます。

しかし、この問題は、コードの助けを借りて、はるかに単純で、より柔軟で、洗練された方法で解決できます。 複雑な図を作成するために、いくつかの特殊なマークアップ言語 (DOT、GraphML など) と、そのような命令を読み取り、さまざまな形式で視覚化できる散在するアプリケーション (GraphViz、PlantUML、Mermaid) が用意されています。 。 さて、私たちはオブジェクトとオブジェクト間の接続に関する情報を取得する方法をすでに知っています。

PlantUML を使用してこれがどのようになるかを示す小さな例を次に示します。 PostgreSQL のデモ データベース (左側は PlantUML に必要な命令を生成する SQL クエリ、右側は結果です):

select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|> ' ||
       tc.table_name as val
  from table_constraints as tc
  join key_column_usage as kcu
    on tc.constraint_name = kcu.constraint_name
  join constraint_column_usage as ccu
    on ccu.constraint_name = tc.constraint_name
 where tc.constraint_type = 'FOREIGN KEY'
   and tc.table_name ~ '.*' union all
select '@enduml'

そして、少し試してみると、それに基づいて、 PlantUML 用の ER テンプレート 実際の ER 図に非常に似たものを得ることができます。

SQLクエリは少し複雑です

-- Шапка
select '@startuml
        !define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
        !define primary_key(x) <b>x</b>
        !define unique(x) <color:green>x</color>
        !define not_null(x) <u>x</u>
        hide methods
        hide stereotypes'
 union all
-- Таблицы
select format('Table(%s, "%s n information about %s") {'||chr(10), table_name, table_name, table_name) ||
       (select string_agg(column_name || ' ' || upper(udt_name), chr(10))
          from information_schema.columns
         where table_schema = 'public'
           and table_name = t.table_name) || chr(10) || '}'
  from information_schema.tables t
 where table_schema = 'public'
 union all
-- Связи между таблицами
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %s"', ccu.table_name, tc.table_name)
  from information_schema.table_constraints as tc
  join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
  join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
 where tc.constraint_type = 'FOREIGN KEY'
   and ccu.constraint_schema = 'public'
   and tc.table_name ~ '.*'
 union all
-- Подвал
select '@enduml'

よく見ると、多くの視覚化ツールも内部で同様のクエリを使用しています。 確かに、これらの要求は通常、深いものです アプリケーション自体のコードに「組み込まれている」ため、理解するのが難しいそれらの変更は言うまでもありません。

メトリクスとモニタリング

従来から複雑なトピックであるデータベース パフォーマンスの監視に移りましょう。 私は「友人の一人」が私に語った小さな実話を覚えています。 別のプロジェクトには、ある強力な DBA が住んでおり、彼を個人的に知っている、または直接会ったことがある開発者はほとんどいませんでした (噂によれば、彼は隣の建物のどこかで働いていたという事実にもかかわらず)。 「X」時間、大手小売店の販売システムが再び「調子が悪くなり」始めたとき、彼は黙って Oracle Enterprise Manager からグラフのスクリーンショットを送信し、「分かりやすさ」のために重要な場所を赤いマーカーで慎重に強調表示しました (控えめに言っても、これはあまり役に立ちませんでした)。 そして、この「フォトカード」をもとに治療をすることになりました。 同時に、貴重な (両方の意味で) Enterprise Manager にアクセスできる人は誰もいませんでした。 システムが複雑で高価なため、突然「開発者が何かにつまずいてすべてを壊してしまう」のです。 したがって、開発者は「経験的に」ブレーキの場所と原因を特定し、パッチをリリースしました。 DBA からの脅迫的な手紙が近い将来再び届かなかったら、誰もが安堵のため息をつき、現在の仕事に戻るでしょう (新しい手紙が届くまで)。

しかし、監視プロセスはより楽しくフレンドリーに見える可能性があり、最も重要なことは、誰にとってもアクセス可能で透明性があることです。 少なくともその基本的な部分は、主要な監視システム (確かに便利で、多くの場合代替不可能なもの) への追加としてのものです。 DBMS は、現在の状態とパフォーマンスに関する情報を完全に無料で共有できます。 同じ「血まみれ」の Oracle DB では、プロセスやセッションからバッファ キャッシュの状態に至るまで、パフォーマンスに関するほとんどすべての情報をシステム ビューから取得できます (たとえば、 DBA スクリプト、セクション「モニタリング」)。 Postgresql には、さまざまなシステム ビューもあります。 データベース監視、特に、DBA の日常生活に欠かせないものです。 pg_stat_activity, pg_stat_database, pg_stat_bgwriter。 MySQL には、このための別のスキーマもあります。 パフォーマンススキーマ。 A In Mongo 内蔵 プロファイラー パフォーマンス データをシステム コレクションに集約します システム プロファイル.

したがって、カスタム SQL クエリを実行できるある種のメトリクス コレクター (Telegraf、Metricbeat、Collectd)、これらのメトリクスのストレージ (InfluxDB、Elasticsearch、Timescaledb)、およびビジュアライザ (Grafana、Kibana) を備えているため、かなり簡単にそして、他のシステム全体のメトリクス (たとえば、アプリケーション サーバー、OS などから取得) と緊密に統合される柔軟な監視システム。 たとえば、これは pgwatch2 で行われ、InfluxDB + Grafana の組み合わせと、システム ビューへの一連のクエリを使用します。これらのビューにもアクセスできます。 カスタムクエリを追加する.

合計で

これは、通常の SQL コードを使用してデータベースで実行できることのおおよそのリストにすぎません。 他にもたくさんの使い方が見つかると思いますので、コメントに書いてください。 次回は、これらすべてを自動化して CI/CD パイプラインに組み込む方法 (そして最も重要な理由) について説明します。

出所： habr.com