Di Rostelecom, seperti di perusahaan besar mana pun, ada gudang data perusahaan (WCD). WCD kami terus tumbuh dan berkembang, kami membangun etalase, laporan, dan kubus data yang berguna. Pada titik tertentu, kami dihadapkan dengan fakta bahwa data berkualitas buruk mengganggu kami ketika membangun tampilan jendela, unit yang dihasilkan tidak menyatu dengan unit sistem sumber dan menyebabkan kurangnya pemahaman tentang bisnis. Misalnya, data dengan nilai Null di kunci asing (kunci asing) tidak terhubung ke data dari tabel lain.
Diagram singkat WCD:
Kami memahami bahwa untuk memastikan kepercayaan pada kualitas data, kami membutuhkan proses rekonsiliasi yang teratur. Tentu saja, otomatis dan memungkinkan setiap tingkat teknologi untuk memastikan kualitas data dan konvergensi mereka, baik secara vertikal maupun horizontal. Sebagai hasilnya, kami secara bersamaan meninjau tiga platform siap pakai untuk mengelola rekonsiliasi dari berbagai vendor dan menulis sendiri. Kami membagikan pengalaman kami di pos ini.
Kekurangan dari platform jadi diketahui semua orang: harga, fleksibilitas terbatas, kurangnya kemampuan untuk menambah dan memperbaiki fungsionalitas. Bagian pros - mdm (data emas, dll.), Pelatihan dan dukungan juga ditutup. Setelah menghargai ini, kami dengan cepat lupa tentang pembelian dan berkonsentrasi pada pengembangan solusi kami.
Inti dari sistem kami ditulis dalam Python, dan database metadata untuk menyimpan, mencatat dan menyimpan hasil ditulis dalam Oracle. Ada banyak pustaka untuk Python, kami menggunakan minimum yang diperlukan untuk koneksi Hive (pyhive), GreenPlum (pgdb), Oracle (cx_Oracle). Menghubungkan jenis sumber yang berbeda juga seharusnya tidak menjadi masalah.
Himpunan data yang dihasilkan (himpunan hasil) kami letakkan di tabel Oracle yang dihasilkan, saat mengevaluasi status rekonsiliasi (SUKSES / KESALAHAN). APEX dikonfigurasi pada tabel yang dihasilkan di mana visualisasi hasil dibangun, nyaman untuk pemeliharaan dan manajemen.
Untuk menjalankan pemeriksaan di Repositori, orkestra Informatica digunakan, yang mengunduh data. Setelah menerima status keberhasilan unduhan, data ini akan secara otomatis mulai diverifikasi. Penggunaan parameterisasi kueri dan metadata WCD memungkinkan penggunaan templat rekonsiliasi kueri untuk set tabel.
Sekarang tentang rekonsiliasi yang diterapkan pada platform ini.
Kami mulai dengan rekonsiliasi teknis, yang membandingkan jumlah data pada input dan lapisan WCD dengan penerapan filter tertentu. Kami mengambil file ctl yang datang ke input WCD, membaca jumlah catatan dari itu dan membandingkannya dengan tabel pada Stage ODL dan / atau Stage ODS (1, 2, 3 dalam diagram). Kriteria verifikasi didefinisikan dalam kesetaraan jumlah catatan (hitung). Jika kuantitas menyatu, maka hasilnya adalah SUKSES, tidak ada KESALAHAN dan analisis kesalahan secara manual.
Rantai rekonsiliasi teknis ini, dibandingkan dengan jumlah catatan, meluas ke lapisan ADS (8 dalam diagram). Filter diubah di antara lapisan, yang tergantung pada jenis pemuatan - DIM (buku referensi), HDIM (buku referensi historis), FACT (tabel akrual aktual), dll. - serta pada versi SCD dan lapisan. Semakin dekat ke lapisan tampilan, algoritma penyaringan yang lebih canggih yang kita gunakan.
Pemeriksaan teknis juga dilakukan pada input dengan Python, yang mendeteksi duplikat di bidang kunci. Di GreenPlum kami, bidang utama (PK) tidak dilindungi terhadap duplikat oleh alat sistem basis data. Jadi kami menulis skrip Python yang membaca bidang dari tabel yang dimuat dari metadata PK dan menghasilkan skrip SQL yang memeriksa ketiadaannya. Fleksibilitas pendekatan memungkinkan kita untuk menggunakan PK yang terdiri dari satu atau beberapa bidang, yang sangat nyaman. Rekonsiliasi semacam itu meluas ke lapisan ADS STG.
unique_check import sys import os from datetime import datetime log_tmstmp = datetime.now().strftime("%Y-%m-%d %H:%M:%S") def do_check(args, context): tab = args[0] data = [] fld_str = "" try: sql = """SELECT 't_'||lower(table_id) as tn, lower(column_name) as cn FROM src_column@meta_data WHERE table_id = '%s' and is_primary_key = 'Y'""" % (tab,) for fld in context['ora_get_data'](context['ora_con'], sql): fld_str = fld_str + (fld_str and ",") + fld[1] if fld_str: config = context['script_config'] con_gp = context['pg_open_con'](config['user'], config['pwd'], config['host'], config['port'], config['dbname']) sql = """select %s as pkg_id, 't_%s' as table_name, 'PK fields' as column_name, coalesce(sum(cnt), 0) as NOT_UNIQUE_PK, to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS') as sys_creation from (select 1 as cnt from edw_%s.t_%s where %s group by %s having count(*) > 1 ) sq; """ % (context["package"] or '0',tab.lower(), args[1], tab.lower(), (context["package"] and ("package_id = " + context["package"]) or "1=1"), fld_str ) data.extend(context['pg_get_data'](con_gp, sql)) con_gp.close() except Exception as e: raise return data or [[(context["package"] or 0),'t_'+tab.lower(), None, 0, log_tmstmp]] if __name__ == '__main__': sys.exit(do_check([sys.argv[1], sys.argv[2]], {}))
Rekonsiliasi keunikan contoh kode python. Panggilan, transfer parameter koneksi dan memasukkan hasilnya ke dalam tabel yang dihasilkan dilakukan oleh modul kontrol dengan Python.Rekonsiliasi untuk ketiadaan nilai NULL dibangun mirip dengan yang sebelumnya dan juga dalam Python. Kami membaca dari memuat bidang metadata yang tidak dapat memiliki nilai kosong (NULL) dan memeriksa kepenuhannya. Rekonsiliasi digunakan sebelum lapisan DDS (6 pada diagram pertama).
Di pintu masuk penyimpanan, analisis tren paket data yang tiba di input juga dilaksanakan. Jumlah data yang diterima saat paket baru tiba dimasukkan dalam tabel histori. Dengan perubahan signifikan dalam jumlah data, orang yang bertanggung jawab atas tabel dan SI (sistem sumber) menerima pemberitahuan melalui pos (dalam paket), melihat kesalahan dalam APEX sebelum paket data masuk ke dalam Gudang, dan mencari tahu alasannya dengan SI.
Antara STG (STAGE) _ODS dan ODS (layer data operasional) (3 dan 4 dalam diagram), bidang penghapusan teknis muncul (indikator penghapusan = dihapus_ind), kebenaran yang kami juga periksa melalui kueri SQL. Input yang hilang harus ditandai dihapus dalam ODS.
Hasil skrip rekonsiliasi diharapkan untuk melihat nol kesalahan. Dalam contoh rekonsiliasi yang disajikan, parameter ~ # PKG_ID # ~ dilewatkan melalui blok kontrol Python, dan parameter tipe ~ P_JOIN_CONDITION ~ dan ~ PERIOD_COL ~ diisi dari tabel metadata, nama tabel itu sendiri ~ TABEL ~ dari parameter peluncuran.
Berikut ini adalah rekonsiliasi parameter. Contoh kode rekonsiliasi SQL antara STG_ODS dan ODS untuk tipe HDIM:
select package_id as pkg_id, 'T_~TABLE~' as table_name, to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS'), coalesce(empty_in_ods, 0) as empty_in_ods, coalesce(not_equal_md5, 0) as not_equal_md5, coalesce(deleted_in_ods, 0) as deleted_in_ods, coalesce(not_deleted_in_ods, 0) as not_deleted_in_ods, max_load_dttm from (select max (src.package_id) as package_id, sum (case when tgt.md5 is null then 1 else 0 end) as empty_in_ods, sum (case when src.md5<>tgt.md5 and tgt.~PK~ is not null and tgt.deleted_ind = 0 then 1 else 0 end) as not_equal_md5, sum (case when tgt.deleted_ind = 1 and src.md5=tgt.md5 then 1 else 0 end) as deleted_in_ods from EDW_STG_ODS.T_~TABLE~ src left join EDW_ODS.T_~TABLE~ tgt on ~P_JOIN_CONDITION~ and tgt.active_ind ='Y' where ~
Contoh kode rekonsiliasi SQL antara STG_ODS dan ODS untuk tipe HDIM dengan parameter yang diganti:
Dimulai dengan ODS, sejarah dipertahankan dalam direktori, oleh karena itu, harus diperiksa untuk tidak adanya persimpangan dan kesenjangan. Ini dilakukan dengan menghitung jumlah nilai yang salah dalam sejarah dan menulis jumlah kesalahan yang dihasilkan ke tabel yang dihasilkan. Jika ada kesalahan riwayat dalam tabel, mereka harus dicari secara manual. Rekonsiliasi tergantung pada jenis unduhan - HDIM (panduan referensi sejarah) sejak awal. Kami melakukan rekonsiliasi kebenaran sejarah untuk direktori hingga lapisan ADS.
Pada layer DDS (6 dalam diagram pertama), SI yang berbeda (sistem sumber) digabungkan menjadi satu tabel; Tabel HUB untuk menghasilkan kunci pengganti untuk menghubungkan data dari sistem sumber yang berbeda muncul. Kami memeriksanya untuk keunikan dengan pemeriksaan python yang mirip dengan layer-stage.
Pada lapisan DDS, perlu untuk memverifikasi bahwa setelah menggabungkan dengan tabel HUB, nilai tipe 0, -1, -2 tidak muncul di bidang kunci, yang berarti salah bergabung dengan tabel, kurangnya data. Mereka bisa muncul tanpa adanya data yang diperlukan dalam tabel HUB. Dan ini adalah kesalahan untuk penguraian manual.
Rekonsiliasi paling kompleks untuk data lapisan showcase ADS (8 pada diagram pertama). Untuk kepercayaan penuh pada konvergensi hasil yang diperoleh, verifikasi dengan sistem sumber untuk agregasi jumlah biaya dikerahkan di sini. Di satu sisi, ada kelas indikator yang mencakup akumulasi akrual. Kami mengumpulkannya selama sebulan dari jendela WCD. Di sisi lain, kami mengambil agregat dari biaya yang sama dari sistem sumber. Perbedaan tidak lebih dari 1% atau nilai absolut tertentu dan disepakati diperbolehkan. Set hasil yang diperoleh melalui rekonsiliasi ditempatkan dalam set data yang dibuat khusus yang menerima tabel Oracle. Perbandingan data dilakukan dalam tampilan Oracle. Visualisasi hasil dalam APEX. Kehadiran seluruh set data (set hasil) memungkinkan kita, jika ada kesalahan, untuk lebih dalam dan menganalisis data detail dari hasil, menemukan artikel tertentu di mana perbedaan terjadi, dan mencari alasannya.
Presentasi hasil rekonsiliasi kepada pengguna di APEXSaat ini, kami telah memperoleh aplikasi yang bisa digunakan dan digunakan secara aktif untuk merekonsiliasi data. Tentu saja, kami memiliki rencana untuk lebih mengembangkan kuantitas dan kualitas rekonsiliasi, dan pengembangan platform itu sendiri. Pengembangan sendiri memungkinkan kami untuk mengubah dan memodifikasi fungsi dengan cukup cepat.
Artikel ini disiapkan oleh tim manajemen data Rostelecom