3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

ASTERIAでSQL文自動生成に挑戦

Last updated at Posted at 2020-02-26

##きっかけ
CSVからDBにデータをUPLOADする際に下記のようなことができないか?という話題になった。
例えば

9040_元ネタ4.PNG

というテーブルとデータがあったとして次のようなCSV
9050_元ネタ5(CSV).PNG
を取り込んだ際に
9060_元ネタ6.PNG
という結果が欲しい。と。(id=2のオーナーidだけ更新したい)
当然素直に取り込めば
9070_元ネタ7(こうなる 更新後DB).PNG
という結果になるのだが「2,,3」はNULLではなく「更新しない」という扱いにしてほしい、と。

…イヤイヤ、それは無理でわ?
そもそも「,,」を「更新しない」なのか「NULL」なのかを判断できないのでは?というところだが「そこを何とか」と

そこで無理やり次のようなSQL文を放り込んだ。
###仮テーブルを作成してCSVを取り込んでから本テーブルにUPDATEをかける
(仮テーブルはここでは「tmpdog」という名前のテーブル)
9010_元ネタ1(SQLだらけのフロー).PNG

■参考にさせていただいたWebサイト様
 「【SQL】UPDATEでNULLだったら更新しない方法
ちなみに3つ目のSQLCallコンポーネント(COPYコマンドのやつ)の中身は

9020_元ネタ2(コピーコマンド).PNG

PostgreSQL
copy tmpdog from '/tmp/koushin.csv' with null '' csv;

で、
4つ目のSQLCallコンポーネント(UPDATEコマンドのやつ)の中身は
9030_元ネタ3(NULLにならないSQL文).PNG

PostgreSQL
update public.犬テーブル
set id=case 
 when tmpdog.tmpID is not null then tmpdog.tmpID
 else id
 end,
名前 = case
 when tmpdog.tmp名前 is not null then tmpdog.tmp名前
 else 名前
 end,
オーナーid = case
 when tmpdog.tmpオーナーID is not null then tmpdog.tmpオーナーID
 else オーナーid
 end
from tmpdog
where
public.犬テーブル.ID=tmpdog.tmpID;

です。

そして次に出た要望は「このSQL(のコンポーネントの使い方)だとテーブルごとに書かなきゃいけないから、自動で何かパパっとできない?」。

##環境
サーバ:WindowsServer2012 R2 StandardのHYPER-V上で動くCentOS8
クライアント:ごくごく普通のWindows10Pro
DB:PostgreSQL(9.2.24-1.el7_5)
ASTERIA:Asteria(1912)

###苦労した点
1.とにかくASTERIAの情報が少ない
2.筆者はASTERIAもPostgreSQLも2019年11月から触っている。とにかく初心者。
※執筆時点は2020年02月。
3.周りにも頼れる仲間がいない。
4.開発業務は初めて。当然Javaもできないので新規コンポーネントの開発などできない。
(余談ですがQiitaもこれが初投稿です;)

###困難は分割すべし
とりあえず構想をかき出してみる。

1.DBのフィールド名を読み込み(フィールド名を外部変数などから取得することはしたくなかった。あくまでできる範囲は自動化)
2.フィールドの個数を数える(例えば,の数+1)
3.フィールドの個数は処理を実行する回数
4.フィールドの個数分だけ
  id=case
  when tmpdog.tmpID is not null then tmpdog.tmpID
  else id
  end,
 のような定型文を作成する
 ※「id」、「tmpid」等は変数
5.SQL文を作成する
  update public.犬テーブル
  set
  …
  when tmpdog.tmpオーナーID is not null then tmpdog.tmpオーナーID
  …
  from tmpdog
  where
  public.犬テーブル.ID=tmpdog.tmpID;
というのが出発点でした。
この時点で
・Where句以降はその時々で異なる「条件」なので自動生成は出来ないだろう
 (できたとしても外部変数の指定などが多すぎて)

##結果
全体像はこのような形になりました。

NotNullUpdate.jpg

各処理の概要はコメント化しています。
細かい解説を入れます。

#####1.サブフローで呼び出すイメージで作成しているので外部変数が必要になります。
内容は図の通りです。

0010_外部変数.PNG

「tmp」と「work」はお好みで。「tmpSQLtxt」は定数ではなくセッション変数なんかで外から受け渡した方がよいのかもしれませんがパスはともかくファイル名、ということにしました。

#####2.次に外部変数を受け取るためのフロー変数です。
0020_フロー変数.PNG

外部変数を直接使ってもいいのかもしれませんが、一応フロー内で使うものはフロー変数に退避した方がいいですよ…とはだれからも言われてないのですが何となくそんな気がするので。

#####3.スタートコンポーネント直後のマッパーの内容です。
0030_Map1.PNG

外部変数を(多少加工して)フロー変数に退避します。

#####4.次のVelocityの内容です。

0040_Vel1.PNG

PostgreSQL
SELECT
    attname
FROM
    pg_attribute
WHERE
    attrelid = '${exvar.CSVNullCheck.Tablename}'::regclass
    and attnum>0;

これはカラム名を取得するPostgreSQLの命令文です。(修正>だ、そうです;)
この後でDBを読み込みたいのですが変数指定したSQL文はDBGetコンポーネントに直接記入はできない(動かない)そうなのでVelocityでテンプレを作成してそれをマッパーでDBGetコンポーネントのSQL文プロパティに突っ込みます。

■参考にさせていただいたWebサイト様
 「PostgreSQLでテーブル名カラム名を取得する方法

#####5.次のマッパーの内容です。
UPDATE分を一時ファイルに吐き出して置き、本命はVelocityの作成したSQLをDBGetに突っ込むという単純な内容です。
0050_Map2.PNG

#####6.DBGet、RecordTranspose、Converter
全体像のコメント通りです。

0055_フローDBGet周辺.PNG

補足するとすれば
DBGetでは

カラム名
id
名前
オーナーid

と縦に出力されるので縦横並び替えとカンマ区切りをしてテキストに変換、というところで結果は

id,名前,オーナーid

となります。
※DBのコネクションはマッパーなどから指定できるプロパティではないのでここはコンポーネントに対して直接指定が必要です。

#####7.Converterの次(左側)のマッパーです。

0060_Map3.PNG

レコード件数(最後のレコードになったらループ終了)としたかったのですが、上手くいかなかったので最後のカラム名を取得しています。

#####8.Converterの次(右側)のTextSplitLoopです。
カンマ区切りでループさせます。

0065_フローループ部分.PNG

#####9.分岐(BranchStart)条件は以下の通りです。
 最後のレコード右->(true)
最後のレコード以外
( $record.Object} = $flow.orgLastColName)
※不一致条件でも良かったのですが、最後のレコードだけ右に通す方がそれっぽかったので。

#####10.分岐後のマッパー、Velocityの違いはほとんどありません。
マッパーではオリジナルのカラム名(「id」など)、比較するための一時的なテーブル名、カラム名をVelocityのローカル変数に格納します。(個々の違いまはまったくありません)

0070_Map4.PNG

Velocityのテンプレートは次の図のような形になります。

0080_Vel2・3.PNG

PostgreSQL
set ${local.orgColName}=case
 when ${local.tmpTableName}.${local.orgColName} is not null then ${local.tmpTableName}.${local.orgColName}
 else ${local.orgColName}
 end,

結局

set...
when...
else...
end,

の成型をしたい(しているだけ)ですが全部繋げた後に最後のendのカンマを取ろうとしたのですがマッパー関数の文字列などでどうしても取れなかったのでテンプレートを分けている、とただそれだけです。

#####11.作成したテンプレート(SET~END)を合成しています。
合成中に改行コードを入れています。

0090_Map5.PNG

#####12.7同様、一時的に作成したSQL文を追記保存してループさせます。

#####13.ループが終わったらfrom句を追記するために一時保存SQLを読み込み、次のマッパーで追記させます。

0095_フロー最後周辺.PNG

0100_Map6.PNG

※本当はこのあたりでフロー変数などを初期化したほうがいいんでしょうね。  あと、本運用を想定していない練習版なので汎用エラーなども特に設定はしていません。

#####14.最後は分岐していますがあまり意味はありません。
Velocityでフローを続けてもよし、テキストに書き出しておいてメインフローでSQL文をテキストから読み込ませてWhere句以降をつなげてもよし、といった感じです。

##最後に
ここまで記載しておいてなんですが「どこまでできるかな?」で作ったものなので実際サブフロー化して使うかかどうかはわかりません。すみません。
ただ、

・こんなところまではできる
・改善できないだろうか(特に9の分岐、いちいち一時テキストに保存している箇所)というところ
・できることできないこと(DBのコネクションの指定)

など勉強になったのも事実です。

もっともっといろいろ簡単にできるよ~という情報あったら是非教えてください。
ご清覧ありがとうございました。

3
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?