Untitled
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