Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DRE/SREのプラクティス融合によるクラウドネイティブなデータ基盤作り / dre_sre

DRE/SREのプラクティス融合によるクラウドネイティブなデータ基盤作り / dre_sre

DRE / SREのプラクティスを理解し、どのように活動してデータとどう向き合っていくか
向き合うためにどのようにクラウドを利用していくのか
これから同じような取り組みにチャレンジしていく方向けにいくつかのヒントになれば幸いです

yuuki takezawa

December 28, 2023
Tweet

More Decks by yuuki takezawa

Other Decks in Technology

Transcript

  1. QSP fi MF  • ελʔϑΣεςΟόϧגࣜձࣾ΄͔ • σʔλॲཧ΍͍Ζ͍Ζ • (P4DBMB

    • ϚΠΫϩαʔϏεΞʔΩςΫνϟɾϦΞΫςΟϒγεςϜɺ ϨΨγʔվળ΍43&%3&ɺ֤छϞσϦϯάͷࢧԉͳͲ • 9IUUQTUXJUUFSDPNFY@UBLF[BXB
  2. Ϩίʔυྫ  create or replace view HOGE_DB.RAW_DATA.TEST_IU_VIEW ( ID, NAME,

    DEBEZIUM_PROCESSED_TS, SOURCE_PROCESSED_TS, SOURCE_SERVER, SOURCE_DB, SOURCE_TABLE, DML_OPERATOR ) as SELECT record_content:"after"."id"::NUMBER as ID, record_content:"after"."name"::NUMBER as NAME, record_content:"ts_ms"::STRING::DATETIME as DEBEZIUM_PROCESSED_TS, record_content:"source"."ts_usec"::STRING::DATETIME as SOURCE_PROCESSED_TS, record_content:"source"."name"::STRING as SOURCE_SERVER, record_content:"source"."db"::STRING as SOURCE_DB, record_content:"source"."table"::STRING as SOURCE_TABLE, record_content:"op"::STRING as DML_OPERATOR FROM HOGE_DB.RAW_DATA.CDC_RAW_TESTS WHERE lower(DML_OPERATOR) in ('r', 'c', 'u');
  3. Ϩίʔυྫ  create or replace view HOGE_DB.RAW_DATA.TEST_IU_VIEW ( ID, NAME,

    DEBEZIUM_PROCESSED_TS, SOURCE_PROCESSED_TS, SOURCE_SERVER, SOURCE_DB, SOURCE_TABLE, DML_OPERATOR ) as SELECT record_content:"after"."id"::NUMBER as ID, record_content:"after"."name"::NUMBER as NAME, record_content:"ts_ms"::STRING::DATETIME as DEBEZIUM_PROCESSED_TS, record_content:"source"."ts_usec"::STRING::DATETIME as SOURCE_PROCESSED_TS, record_content:"source"."name"::STRING as SOURCE_SERVER, record_content:"source"."db"::STRING as SOURCE_DB, record_content:"source"."table"::STRING as SOURCE_TABLE, record_content:"op"::STRING as DML_OPERATOR FROM HOGE_DB.RAW_DATA.CDC_RAW_TESTS WHERE lower(DML_OPERATOR) in ('r', 'c', 'u'); ෺ཧ࡟আͷ৔߹͸CFGPSFΛࢀর
  4. Ϩίʔυྫ  create or replace view HOGE_DB.RAW_DATA.TEST_IU_VIEW ( ID, NAME,

    DEBEZIUM_PROCESSED_TS, SOURCE_PROCESSED_TS, SOURCE_SERVER, SOURCE_DB, SOURCE_TABLE, DML_OPERATOR ) as SELECT record_content:"after"."id"::NUMBER as ID, record_content:"after"."name"::NUMBER as NAME, record_content:"ts_ms"::STRING::DATETIME as DEBEZIUM_PROCESSED_TS, record_content:"source"."ts_usec"::STRING::DATETIME as SOURCE_PROCESSED_TS, record_content:"source"."name"::STRING as SOURCE_SERVER, record_content:"source"."db"::STRING as SOURCE_DB, record_content:"source"."table"::STRING as SOURCE_TABLE, record_content:"op"::STRING as DML_OPERATOR FROM HOGE_DB.RAW_DATA.CDC_RAW_TESTS WHERE lower(DML_OPERATOR) in ('r', 'c', 'u'); ෺ཧ࡟আͷ৔߹͸E
  5.  MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt USING ( SELECT *

    FROM ( SELECT *, ROW_NUMBER() over ( PARTITION BY ID ORDER BY DEBEZIUM_PROCESSED_TS DESC ) as row_num FROM ( SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW UNION ALL SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_D_VIEW ) as u ) as t1 WHERE t1.row_num = 1 ) as src ON tgt.ID = src.ID WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN UPDATE SET tgt.ID = src.ID, tgt.NAME = src.NAME WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN INSERT ( ID, NAME ) VALUES ( src.ID, src.NAME );
  6.  MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt USING ( SELECT *

    FROM ( SELECT *, ROW_NUMBER() over ( PARTITION BY ID ORDER BY DEBEZIUM_PROCESSED_TS DESC ) as row_num FROM ( SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW UNION ALL SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_D_VIEW ) as u ) as t1 WHERE t1.row_num = 1 ) as src ON tgt.ID = src.ID WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN UPDATE SET tgt.ID = src.ID, tgt.NAME = src.NAME WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN INSERT ( ID, NAME ) VALUES ( src.ID, src.NAME ); 3%4ͳͲ͔ΒϨίʔυ͕࡟আ͞Εͨ৔߹͸ 4OPX fl BLF͔Β΋࡟আ
  7.  MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt USING ( SELECT *

    FROM ( SELECT *, ROW_NUMBER() over ( PARTITION BY ID ORDER BY DEBEZIUM_PROCESSED_TS DESC ) as row_num FROM ( SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW UNION ALL SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_D_VIEW ) as u ) as t1 WHERE t1.row_num = 1 ) as src ON tgt.ID = src.ID WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN UPDATE SET tgt.ID = src.ID, tgt.NAME = src.NAME WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN INSERT ( ID, NAME ) VALUES ( src.ID, src.NAME ); Ϩίʔυ͕ߋ৽͞Εͨ৔߹͸ 4OPX fl BLFͷϨίʔυ΋ߋ৽
  8.  MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt USING ( SELECT *

    FROM ( SELECT *, ROW_NUMBER() over ( PARTITION BY ID ORDER BY DEBEZIUM_PROCESSED_TS DESC ) as row_num FROM ( SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW UNION ALL SELECT ID, NAME, DEBEZIUM_PROCESSED_TS, DML_OPERATOR FROM HOGE_DB.RAW_DATA.TEST_D_VIEW ) as u ) as t1 WHERE t1.row_num = 1 ) as src ON tgt.ID = src.ID WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN UPDATE SET tgt.ID = src.ID, tgt.NAME = src.NAME WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN INSERT ( ID, NAME ) VALUES ( src.ID, src.NAME ); 4OPX fl BLFʹσʔλ͕ͳ͍͕ɺ ࡟আҎ֎ͷϨίʔυૢ࡞͕͋ͬͨ৔߹͸ *OTFSU