Does S3Tbales feature support concept of Primary Key

Amazon S3 Tables does not inherently support the concept of primary keys or unique constraints directly. However, you can achieve similar functionality using Delta Lake with AWS Glue. Delta Lake supports upsert operations (insert, update, delete) and can enforce unique constraints on combinations of columns:

1. Convert Your Data to Delta Format: Use Spark to read your data from S3 and write it to a Delta table.

2. Define Unique Constraints: When creating the Delta table, define the primary key or unique constraint on the combination of Key1 and Key2.

3. Perform Upsert Operations: Use Delta Lake's upsert functionality to insert new data or update existing data while enforcing the unique constraint.

from delta import *
from pyspark.sql.session import SparkSession

# Initialize Spark Session with Delta Lake configuration
spark = SparkSession.builder \
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension") \
    .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog") \
    .getOrCreate()

# Read data from S3
inputDF = spark.read.format("csv").option("header", "true").load('s3://your-bucket/data.csv')

# Write data to Delta table
inputDF.write.format("delta").mode("overwrite").save("s3://your-bucket/delta-table")

# Perform upsert operation
inputDF.write.format("delta").mode("overwrite").saveAsTable("delta-table")

Hello,

Thanks for reaching out to us at AWS re:Post. Dhivyaa this side from S3 Support Team and here to assist further on the question you asked here.

As from the post, I understand that you are using spark to insert data into S3 tables from S3 buckets and want to enforce unique constraint on a combination of 2 keys. Hence, you would like to know if S3Tables feature supports the concept of primary key. Please correct me if I have misunderstood your query.

• Amazon S3 Tables store tabular data in the Apache Iceberg format. Thus, tables in table buckets follow the Iceberg spec and protocols specified by the Iceberg open table format.
• Iceberg supports concept of both primary keys and upserts. As a natural extension, you can apply both of these constraints/operations to Iceberg tables in S3 Tables. However, these are enforced at an engine level. In this case, you will need to configure these settings in Apache Spark for it to apply to tables in S3 Tables. For more information please refer to this Iceberg documentation. [+]https://iceberg.apache.org/docs/1.4.3/spark-ddl/#alter-table-write-distributed-by-partition and [+]https://iceberg.apache.org/javadoc/latest/org/apache/iceberg/Schema.html