bigqueryのテーブルについて

Question

bigqueryのテーブルに対して、加工処理を実施したいです。

T_RS_TRAN_TESTのデータに、M_SUBINV_TESTとM_ORG_TESTのデータから必要なカラム値を入れ込みます。

SQLエラーが出力されている状況となります。

●加工用のSQL
update `embulk_test.T_RS_TRAN_TEST` rs set from_org_name = (select org_name from `embulk_test.M_ORG_TEST` where org_code = rs.from_org_code and row_num = 1 ),to_org_name = (select org_name from `embulk_test.M_ORG_TEST` where org_code = rs.to_org_code and row_num = 1 ),from_subinv_name = (select subinv_name from `embulk_test.M_SUBINV_TEST` where org_code = rs.from_org_code and subinv_code = rs.subinv_code and row_num = 1 ),to_subinv_name = (select subinv_name from `embulk_test.M_SUBINV_TEST` where org_code = rs.to_org_code and subinv_code = rs.subinv_code and row_num = 1 ) where ( rs.from_org_name is null or rs.to_org_name is null or from_subinv_name is null or to_subinv_name is null);

●エラー
Unrecognized name:row_num at[1:145]

●テーブル定義
CREATE TABLE T_RS_TRAN_TEST (
    ORDER_ID             VARCHAR2(16),
    FACTORY_NAME         VARCHAR2(10),
    FROM_FACTORY_NAME    VARCHAR2(10),
    TO_FACTORY_NAME      VARCHAR2(10),
    FROM_ORG_NAME        VARCHAR2(10),
    FROM_SUBINV_NAME     VARCHAR2(10),
    TO_ORG_NAME          VARCHAR2(10),
    TO_SUBINV_NAME       VARCHAR2(10),
    TO_ORG_CODE          VARCHAR2(10),
    FROM_ORG_CODE        VARCHAR2(10)
    PRIMARY KEY(ORDER_ID)
);


CREATE TABLE M_SUBINV_TEST (
    ORG_CODE             VARCHAR2(10),
    SUBINV_CODE          VARCHAR2(10),
    SUBINV_NAME          VARCHAR2(10),
    PRIMARY KEY(ORG_CODE)
);

CREATE TABLE M_ORG_TEST (
    ORG_CODE             VARCHAR2(10),
    ORG_NAME             VARCHAR2(10),
    PRIMARY KEY(ORG_CODE)
);

※20190318 実行状況

●SQL
update `embulk_test.T_RS_TRAN_TEST` rs set 
from_org_name = (select org_name from `embulk_test.M_ORG_TEST` where org_code = rs.from_org_code and ROW_NUMBER() OVER `1`),
to_org_name = (select org_name from `embulk_test.M_ORG_TEST` where org_code = rs.to_org_code and ROW_NUMBER() OVER `1`),
from_subinv_name = (select subinv_name from `embulk_test.M_SUBINV_TEST` where org_code = rs.from_org_code and subinv_code = rs.subinv_code and ROW_NUMBER() OVER `1`),
to_subinv_name = (select subinv_name from `embulk_test.M_SUBINV_TEST` where org_code = rs.to_org_code and subinv_code = rs.subinv_code and ROW_NUMBER() OVER `1`) where ( rs.from_org_name is null or rs.to_org_name is null or from_subinv_name is null or to_subinv_name is null);


●エラー
Analytic function not allowed in WHERE clause at [1:145]

Answer 1

row_numって、oracle独自のrownum疑似列と混同していませんか？
何度も言いますけど、エラーが出たら、メッセージの内容確認して、リファレンスみましょうよ。

Comments

[Orlofsky]

Oracleの疑似列はROWNUMです。
マニュアルを確認する習慣を身に着けない質問者さんは困り者ですね。

[sazi]

指摘ありがとうございます。
回答を修正しました。

Answer 2

Analytic function ROW_NUMBER cannot be called without an OVER clause at [1:145]

翻訳サイトも使えることですし、エラーメッセージはちゃんと読みましょう。
ROW_NUMBER は OVER とセットで使います。

追記

SQLは横1行で書くのではなく、ある程度読み易さを考えましょう。

update `embulk_test.T_RS_TRAN_TEST` rs set
    from_org_name = (
                    select org_name
                    from `embulk_test.M_ORG_TEST`
                    where org_code = rs.from_org_code
                    and row_num = 1
                    )
  , to_org_name = (
                  select org_name
                  from `embulk_test.M_ORG_TEST`
                  where org_code = rs.to_org_code
                  and row_num = 1
                  )
以下略

Comments

[raccoondog]

ご指摘有難うございます。
実行させたいSQLが違っていたので、質問内容を更新させて頂きました。

エラー調査してみます。

[Orlofsky]

>Unrecognized name:row_num at[1:145]
これもエラーメッセージはちゃんと読みましょう。
row_num はどこから持ってきましたか？
いちいち質問しないと先に進めませんか？

[raccoondog]

ご指摘有難うございます。

row_numはOracle特有の関数であること理解しました。

bigqueryでは、ROW_NUMBER() OVERを使って、レコード情報を取得する。

bigqueryのリファレンスを参照しているのですが、具体的な使い方がわからない状況です。

Oracleの場合：row_num = 1
BigQueryの場合：ROW_NUMBER() OVER(PARTITION BY 1) ？

[Orlofsky]

saziさんの回答を読み直すとrow_numではないことがわかるはずですが、読んでいませんか？

BigQuery の SQL リファレンス ... でググると、
(https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts)
分析関数の中に ROW_NUMBER があります。
BigQueryの各テーブルの中のデータをINSERT文で提示しないでどんなデータでUPDATE後はどんな結果になって欲しいのかわかりませんが、今回は分析関数が必要な案件でしょうか？

ここは掲示板は無料です。raccoondog さんにUPDATEが完成するまで付き合う義務はありません。
システムの納期もあるでしょうから、上長にスキルのあるメンバーの増員を依頼することも考えては？
Oracleの経験もほとんどないのに、BigQueryを使わないといけない理由もわかりません。

[Orlofsky]

BigQueryにはOracleのサポートセンターのようにサポート契約すると技術的な質問に答えてくれるところってないのでしょうか？

[raccoondog]

いつもご指摘、ご回答有難うございます。

現在、無料の検証環境(bigquery、embulk、Oracle)を利用していまして
有料の環境購入に向けて、対応しております。

プロジェクト側も限られた人員で対応しておりまして、どうしても私個人で結果を出さないといけない状況となります。

BigQueryに関しましては、google社と有料の技術サポート契約を結ぶことにより専門エンジニアに質問することが可能です。ただ現状は難しく。。

[Orlofsky]

去年、大森でOracle PL/SQLのバグの修正で呼ばれました。何本か調べて、SQLもPL/SQLもとてもお金を稼げる水準にないないメンバーだけで作ったプログラムが100本以上。バグ対応ってレベルじゃないです。使い物にならないから全部作り直しですよ。
システム開発の最初から呼んでいただけたらメンバーの教育する分時間がかかるけど、まともに動くシステムにできたので、何千万円も節約できたんですが。
で1日で調査終了。
最初にシステムを作った会社はまともに開発費を払ってもらえず倒産したかも？

[raccoondog]

ご経験談有難うございます。

現在は都内でなく、地方にある僻地でのPJでして、PJメンバも少数

私自身もインフラ専門(ハードウェア、ミドルウェア、OS領域)でして、OracleDBは物理設計からOracleインスタンス作成、テーブルスペース作成とOracleパッチ適用、Oracleインスタンスの起動停止スクリプト作成、アラートログやリスナーログをみてインフラレベルの障害対応、というインフラレベルでしかOracleDBに触れた経験しかなく、テーブルの中身を考慮してSQLを組んだり、PL/SQL作成したり、論理設計が初体験となります。。。

また、顧客様から費用を頂くのも検証結果が出てからになり、現PJ費用と現メンバで対応する以外方法がない状況となります。