Share
Embed
DRE/SREのプラクティス融合によるクラウドネイティブなデータ基盤作り / dre_sre
DRE / SREのプラクティスを理解し、どのように活動してデータとどう向き合っていくか
向き合うためにどのようにクラウドを利用していくのか
これから同じような取り組みにチャレンジしていく方向けにいくつかのヒントになれば幸いです
yuuki takezawa
December 28, 2023
Transcript
70 slides
-
QSP fi MF • ελʔϑΣεςΟόϧגࣜձࣾ΄͔ • σʔλॲཧ͍Ζ͍Ζ • (P4DBMB
• ϚΠΫϩαʔϏεΞʔΩςΫνϟɾϦΞΫςΟϒγεςϜɺ ϨΨγʔվળ43&%3&ɺ֤छϞσϦϯάͷࢧԉͳͲ • 9IUUQTUXJUUFSDPNFY@UBLF[BXB -
4OPX fl BLFҠߦ • ϑΟδΧϧPSϩδΧϧͱʹղܾ͕Ͱ͖Δ • ඪ४తͳ42-͚ͩͰଟ͕͘ղܾͰ͖Δ • ֎෦ωοτϫʔΫΞΫηεՄೳ
• "UIFOBͳͲͰར༻͍ͯͨ͠1BSRVFU͕ͦͷ··ར༻Ͱ͖Δ -
Ϩίʔυྫ 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'); -
Ϩίʔυྫ 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Λࢀর -
Ϩίʔυྫ 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 -
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 ); -
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͔Βআ -
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ͷϨίʔυߋ৽ -
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