删除数据

# Creates new snapshots and marks old files for cleanup
spark.sql("""
    DELETE FROM r2dc.namespace.table_name
    WHERE column_name = 'value'
""")

# The following is effectively a TRUNCATE operation
spark.sql("DELETE FROM r2dc.namespace.table_name")

# For large deletes, use partitioned tables and delete entire partitions for faster performance:
spark.sql("""
    DELETE FROM r2dc.namespace.table_name
    WHERE date_partition < '2024-01-01'
""")

# Removes table from catalog but keeps data files in R2 storage
spark.sql("DROP TABLE r2dc.namespace.table_name")

# ⚠️  DANGER: Permanently deletes all data files from R2
# This operation cannot be undone
spark.sql("DROP TABLE r2dc.namespace.table_name PURGE")

# Use CASCADE to drop all tables within the namespace
spark.sql("DROP NAMESPACE r2dc.namespace_name CASCADE")

# You will need to PURGE the tables before running CASCADE to permanently delete data files
# This can be done with a loop over all tables in the namespace
tables = spark.sql("SHOW TABLES IN r2dc.namespace_name").collect()
for row in tables:
    table_name = row['tableName']
    spark.sql(f"DROP TABLE r2dc.namespace_name.{table_name} PURGE")
spark.sql("DROP NAMESPACE r2dc.namespace_name CASCADE")

# Remove old metadata and data files marked for deletion
# The following retains the last 5 snapshots and deletes files older than Nov 28, 2024
spark.sql("""
    CALL r2dc.system.expire_snapshots(
    table => 'r2dc.namespace_name.table_name',
    older_than => TIMESTAMP '2024-11-28 00:00:00',
     retain_last => 5
  )
""")

# Removes unreferenced data files from R2 storage (orphan files)
spark.sql("""
  CALL r2dc.system.remove_orphan_files(
    table => 'namespace.table_name'
  )
""")

# Rewrite data files with a target file size (e.g., 512 MB)
spark.sql("""
  CALL r2dc.system.rewrite_data_files(
    table => 'r2dc.namespace_name.table_name',
    options => map('target-file-size-bytes', '536870912')
  )
""")