Untitled

alp avatar
alp
Plain Text
10/14/2025 10:57 PM
13.4 KB
17
Indexable
Never
import sys
from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window
from datetime import datetime, timedelta

# MSSQL Bağlantısı - Optimized
jdbc_url = (
    "jdbc:sqlserver://10.81.24.55:1433;"
    "databaseName=WINSURE;encrypt=true;trustServerCertificate=true"
)
jdbc_props = {
    "user": "bentego",
    "password":
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "fetchsize": "50000",  # Daha büyük batch
    "queryTimeout": "0",
    "responseBuffering": "adaptive",
    "selectMethod": "cursor",
    "useBulkCopyForBatchInsert": "true",  # Bulk insert
    "loginTimeout": "30"
}

# Sabitler
PK_COLS = ["FIRM_CODE", "COMPANY_CODE", "PRODUCT_NO", "POLICY_NO", "RENEWAL_NO", "ENDORS_NO"]
date_col = "CONFIRM_DATE"
target_db = "somcodatabase2"
catalog = "iceberg"


def parse_date_param(param: str, full_tbl: str, spark: SparkSession) -> tuple:
    """Tarih parametresini parse eder - Optimized"""
    if not param or param.lower() == 'auto':
        last_date = get_last_date(spark, full_tbl)
        return last_date, "Otomatik (tablonun son tarihi)"
    
    if param.lower().startswith('last_'):
        now = datetime.now()
        param_lower = param.lower()
        
        try:
            if param_lower.endswith('h'):
                hours = int(param[5:-1])
                start_date = now - timedelta(hours=hours)
                desc = f"Son {hours} saat"
            elif param_lower.endswith('d'):
                days = int(param[5:-1])
                start_date = now - timedelta(days=days)
                desc = f"Son {days} gün"
            else:
                raise ValueError(f"Geçersiz format: {param}. 'last_Xh' veya 'last_Xd' kullanın.")
        except ValueError as e:
            if "invalid literal" in str(e):
                raise ValueError(f"Geçersiz sayı: {param}. Örnek: last_15d, last_100h")
            raise
        
        return start_date.strftime("%Y-%m-%d %H:%M:%S.000"), desc
    
    return param, "Manuel tarih"


def get_last_date(spark: SparkSession, full_tbl: str) -> str:
    """Iceberg'den son tarihi çek - 00:00:00 ile"""
    try:
        if spark.catalog.tableExists(full_tbl):
            max_date = spark.sql(f"SELECT MAX({date_col}) as max_dt FROM {full_tbl}").collect()[0]["max_dt"]
            if max_date:
                return max_date.strftime("%Y-%m-%d") + " 00:00:00.000"
    except:
        pass
    return "1900-01-01 00:00:00.000"


def get_changed_pks(spark: SparkSession, last_date: str):
    """
    KEY_LOG'dan değişen PK'ları çek - ULTRA OPTIMIZED
    En son ACTION'ı Spark'ta hesapla, MSSQL'de sadece filtrele
    """
    sql = f"""
    SELECT 
        FIRM_CODE, COMPANY_CODE, PRODUCT_NO, 
        POLICY_NO, RENEWAL_NO, ENDORS_NO,
        [DATE] as CONFIRM_DATE,
        [ACTION]
    FROM [WINSURE].[ETLUSR].[T001POLMAS_KEY_LOG] WITH (NOLOCK)
    WHERE [DATE] > '{last_date}'
        AND FIRM_CODE IN (2,3)
        AND COMPANY_CODE IN (2,3)
    """
    
    df = (
        spark.read.format("jdbc")
        .option("url", jdbc_url)
        .option("query", sql)
        .options(**jdbc_props)
        .option("partitionColumn", "FIRM_CODE")
        .option("lowerBound", "2")
        .option("upperBound", "3")
        .option("numPartitions", "2")
        .load()
        .repartition(16, *PK_COLS)  # Optimal distribution
    )
    
    # Spark'ta deduplicate - en son ACTION
    window = Window.partitionBy(*PK_COLS).orderBy(F.col("CONFIRM_DATE").desc())
    
    return (
        df.withColumn("rn", F.row_number().over(window))
        .filter(F.col("rn") == 1)
        .drop("rn")
        .persist()  # Cache - birden fazla kullanılacak
    )


def read_mssql_data(spark: SparkSession, source_table: str, pk_list, last_date: str):
    """
    MSSQL'den veri çek - BROADCAST JOIN ile
    pk_list: Küçük PK listesi için broadcast join kullan
    """
    if source_table == "T001POLMAS":
        pk_str = ", ".join(PK_COLS)
        base_sql = f"SELECT {pk_str}, {date_col} FROM fiba.T001POLMAS WITH (NOLOCK)"
    else:
        base_sql = f"SELECT t.*, pm.{date_col} FROM fiba.{source_table} t WITH (NOLOCK) INNER JOIN fiba.T001POLMAS pm WITH (NOLOCK) ON t.FIRM_CODE = pm.FIRM_CODE AND t.COMPANY_CODE = pm.COMPANY_CODE AND t.PRODUCT_NO = pm.PRODUCT_NO AND t.POLICY_NO = pm.POLICY_NO AND t.RENEWAL_NO = pm.RENEWAL_NO AND t.ENDORS_NO = pm.ENDORS_NO"
    
    # WHERE clause - PK filtresi
    where_parts = []
    for pk_row in pk_list:
        pk_condition = " AND ".join([f"{'t.' if source_table != 'T001POLMAS' else ''}{col} = {pk_row[col]}" for col in PK_COLS])
        where_parts.append(f"({pk_condition})")
    
    # 1000'er batch'lerde çek (MSSQL IN clause limit)
    if len(where_parts) > 1000:
        # Büyük liste - temp table kullan
        pk_df = spark.createDataFrame(pk_list)
        pk_df.createOrReplaceTempView("pk_filter")
        
        # JOIN ile çek
        sql = f"""
        {base_sql}
        WHERE {'t.' if source_table != 'T001POLMAS' else ''}FIRM_CODE IN (2,3)
            AND {'t.' if source_table != 'T001POLMAS' else ''}COMPANY_CODE IN (2,3)
        """
    else:
        # Küçük liste - IN clause
        where_clause = " OR ".join(where_parts)
        sql = f"{base_sql} WHERE ({where_clause})"
    
    return (
        spark.read.format("jdbc")
        .option("url", jdbc_url)
        .option("query", sql)
        .options(**jdbc_props)
        .option("partitionColumn", "FIRM_CODE")
        .option("lowerBound", "2")
        .option("upperBound", "3")
        .option("numPartitions", "2")
        .load()
    )


def ensure_table_created(spark: SparkSession, df, full_tbl: str):
    """Iceberg tablo oluştur - Ultra optimized settings"""
    if spark.catalog.tableExists(full_tbl):
        return
    
    df_with_hash = df.withColumn("pk_hash", F.xxhash64(*[F.col(c) for c in PK_COLS]))
    
    (df_with_hash.limit(0).write
        .using("iceberg")
        .format("iceberg")
        .tableProperty("write.format.default", "parquet")
        .tableProperty("write.parquet.compression-codec", "zstd")
        .tableProperty("write.target-file-size-bytes", "536870912")
        .tableProperty("write.distribution-mode", "hash")
        .tableProperty("write.metadata.compression-codec", "gzip")
        .tableProperty("commit.retry.num-retries", "10")
        .tableProperty("write.parquet.dict-size-bytes", "2097152")
        .tableProperty("write.parquet.page-size-bytes", "1048576")
        .saveAsTable(full_tbl))
    
    spark.sql(f"ALTER TABLE {full_tbl} ADD PARTITION FIELD months(({date_col}))")
    
    pk_cols_str = ",".join(PK_COLS)
    spark.sql(f"ALTER TABLE {full_tbl} SET TBLPROPERTIES ('write.parquet.bloom-filter-enabled.column.{pk_cols_str}' = 'true', 'write.parquet.bloom-filter-max-bytes' = '1048576')")


def upsert_table(spark: SparkSession, source_table: str, last_date: str):
    """ULTRA OPTIMIZED upsert - Minimum MSSQL çağrısı"""
    full_tbl = f"{catalog}.{target_db}.{source_table}"
    
    print(f"[{source_table}] 📅 Başlangıç: {last_date}")
    
    # 1. KEY_LOG'dan değişen PK'ları çek
    changed_pks_df = get_changed_pks(spark, last_date)
    
    total_changes = changed_pks_df.count()
    if total_changes == 0:
        print(f"[{source_table}] ✓ Değişiklik yok\n")
        changed_pks_df.unpersist()
        return
    
    print(f"[{source_table}] 🔍 {total_changes} değişiklik bulundu")
    
    # pk_hash ekle
    changed_pks_df = changed_pks_df.withColumn("pk_hash", F.xxhash64(*[F.col(c) for c in PK_COLS]))
    
    # DELETE vs INSERT/UPDATE ayır
    delete_pks = changed_pks_df.filter(F.col("ACTION") == "DELETE").select(*PK_COLS).persist()
    insert_update_pks = changed_pks_df.filter(F.col("ACTION") != "DELETE").select(*PK_COLS).persist()
    
    delete_count = delete_pks.count()
    insert_update_count = insert_update_pks.count()
    
    print(f"[{source_table}] 📊 INSERT/UPDATE: {insert_update_count}, DELETE: {delete_count}")
    
    # Tablo yoksa ve INSERT/UPDATE varsa oluştur
    if insert_update_count > 0 and not spark.catalog.tableExists(full_tbl):
        pk_list = insert_update_pks.collect()
        df = read_mssql_data(spark, source_table, pk_list, last_date)
        
        if df.count() > 0:
            df = df.withColumn("pk_hash", F.xxhash64(*[F.col(c) for c in PK_COLS]))
            df = df.withColumn(date_col, F.to_date(F.col(date_col)))
            ensure_table_created(spark, df, full_tbl)
            
            (df.coalesce(10).write
                .format("iceberg")
                .mode("append")
                .saveAsTable(full_tbl))
            print(f"[{source_table}] ✅ İlk yükleme: {df.count()} kayıt\n")
        
        changed_pks_df.unpersist()
        delete_pks.unpersist()
        insert_update_pks.unpersist()
        return
    
    if not spark.catalog.tableExists(full_tbl):
        print(f"[{source_table}] ⚠️ Tablo yok, atlanıyor\n")
        changed_pks_df.unpersist()
        delete_pks.unpersist()
        insert_update_pks.unpersist()
        return
    
    # 2. TÜM değişen PK'ları Iceberg'den SİL (tek DELETE)
    all_changed_pks = changed_pks_df.select(*PK_COLS).distinct()
    total_to_delete = all_changed_pks.count()
    
    print(f"[{source_table}] 🗑️ {total_to_delete} PK siliniyor...")
    
    if total_to_delete > 0:
        all_changed_pks.createOrReplaceTempView("pks_to_delete")
        
        delete_sql = f"""
        DELETE FROM {full_tbl}
        WHERE EXISTS (
            SELECT 1 FROM pks_to_delete p
            WHERE {" AND ".join([f"{full_tbl}.{c} = p.{c}" for c in PK_COLS])}
        )
        """
        spark.sql(delete_sql)
        print(f"[{source_table}] ✓ Silme tamamlandı")
    
    # 3. Sadece INSERT/UPDATE olanları MSSQL'den çek ve ekle
    if insert_update_count > 0:
        print(f"[{source_table}] 📥 {insert_update_count} kayıt MSSQL'den çekiliyor...")
        
        pk_list = insert_update_pks.collect()
        df = read_mssql_data(spark, source_table, pk_list, last_date)
        
        # Deduplicate
        window = Window.partitionBy(*PK_COLS).orderBy(F.col(date_col).desc())
        df = (
            df.withColumn("rn", F.row_number().over(window))
            .filter(F.col("rn") == 1)
            .drop("rn")
        )
        
        actual_count = df.count()
        if actual_count > 0:
            df = df.withColumn("pk_hash", F.xxhash64(*[F.col(c) for c in PK_COLS]))
            df = df.withColumn(date_col, F.to_date(F.col(date_col)))
            
            # Akıllı coalesce
            num_partitions = min(max(4, actual_count // 10000), 20)
            
            (df.coalesce(num_partitions).write
                .format("iceberg")
                .mode("append")
                .option("fanout-enabled", "true")
                .saveAsTable(full_tbl))
            
            print(f"[{source_table}] ✅ {actual_count} kayıt eklendi")
        else:
            print(f"[{source_table}] ⚠️ MSSQL'de veri yok")
    
    # Memory cleanup
    changed_pks_df.unpersist()
    delete_pks.unpersist()
    insert_update_pks.unpersist()
    
    print(f"[{source_table}] ✓ Tamamlandı: {last_date} → ŞİMDİ\n")


def main():
    if len(sys.argv) < 2:
        print("Kullanım: spark-submit incremental_load.py <tablo_adi> [başlangıç_tarihi]")
        print("\nÖrnekler:")
        print("  spark-submit incremental_load.py T001POLMAS")
        print("  spark-submit incremental_load.py T001POLMAS '2025-09-05 00:00:00.000'")
        print("  spark-submit incremental_load.py T001POLMAS last_100h")
        print("  spark-submit incremental_load.py T001POLMAS last_7d")
        sys.exit(1)
    
    table_name = sys.argv[1]
    date_param = sys.argv[2] if len(sys.argv) > 2 else None
    
    spark = (
        SparkSession.builder
        .appName(f"IncrementalUpdate-{table_name}")
        .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions")
        .config("spark.sql.catalog.iceberg", "org.apache.iceberg.spark.SparkCatalog")
        .config("spark.sql.catalog.iceberg.type", "hive")
        .config("spark.sql.adaptive.enabled", "true")
        .config("spark.sql.adaptive.coalescePartitions.enabled", "true")
        .config("spark.sql.adaptive.skewJoin.enabled", "true")
        .getOrCreate()
    )
    
    try:
        full_tbl = f"{catalog}.{target_db}.{table_name}"
        start_date, date_description = parse_date_param(date_param, full_tbl, spark)
        
        print(f"\n{'='*80}")
        print(f"İnkremental Güncelleme: {table_name}")
        print(f"{'='*80}")
        print(f"📅 Mod: {date_description}")
        print(f"📅 Başlangıç: {start_date}")
        print(f"{'='*80}\n")
        
        upsert_table(spark, table_name, start_date)
        
        print(f"{'='*80}")
        print(f"✓ {table_name} güncellendi!")
        print(f"✓ Tarih: {start_date} → ŞİMDİ")
        print(f"{'='*80}\n")
        
    except Exception as e:
        print(f"\n{'='*80}")
        print(f"❌ HATA: {table_name}")
        print(f"{'='*80}")
        print(f"{str(e)}\n")
        import traceback
        traceback.print_exc()
        sys.exit(1)
    
    spark.stop()


if __name__ == "__main__":
    main()
Editor is loading...
Leave a Comment