Issues when using AWS Glue to load parquet files from S3 to S3Tables. Lake Formation doesn't show the s3tables.

Question

I want to load a parquet file from an S3 general bucket to an S3 table. I want the S3 table to be visible in the Lakeformation S3tables catalog. I am using an AWS Glue spark job to create the table with the Amazon S3 Tables Catalog for Apache Iceberg configuration. I do have the catalog jar added to the job.

If I create a simple spark dataframe within the job I am able to write the dataframe to an S3 table and see it in lakeformation. The following would work:

```
df = spark.createDataFrame([
    Row(a=1, b=2., c='string1', d=date(2000, 1, 1), e=datetime(2000, 1, 1, 12, 0)),
    Row(a=2, b=3., c='string2', d=date(2000, 2, 1), e=datetime(2000, 1, 2, 12, 0)),
    Row(a=4, b=5., c='string3', d=date(2000, 3, 1), e=datetime(2000, 1, 3, 12, 0))
])
df.writeTo("s3tablesbucket.{0}.{1}".format(namespace, table)).createOrReplace()
```

However if I instead create the dataframe by reading from a parquet file on S3 then the S3 table will be created but it won't be visible in lakeformation. The following would fail:

```
df = spark.read.parquet(s3 path)
df.writeTo("s3tablesbucket.{0}.{1}".format(namespace, table)).createOrReplace()
```

I'm at a loss as to why the table isn't visible in the catalog. For reference here is my spark session config:

```
spark = SparkSession.builder.appName("glue-s3-tables") \
    .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions") \
    .config("spark.sql.defaultCatalog", "s3tablesbucket") \
    .config("spark.sql.catalog.s3tablesbucket", "org.apache.iceberg.spark.SparkCatalog") \
    .config("spark.sql.catalog.s3tablesbucket.catalog-impl", "software.amazon.s3tables.iceberg.S3TablesCatalog") \
    .config("spark.sql.catalog.s3tablesbucket.warehouse", "arn:aws:s3tables:eu-west-1::bucket/") \
    .getOrCreate() 
```

TCantwell · Accepted Answer

I figured out what the issue was. It was to do with the fact that the column names in my parquet files were mixed case. So if the dataframe had a column name like SomeColumnName then I would be able to write the dataframe to a table in S3Tables but it wouldn't show up in the LakeFormation catalog. If I renamed all the dataframe columns so that they only contained lowercase (so something like somecolumnname or some_column_name) then I could see the table in both S3tables and the LakeFormation Catalog.

I used to following code to clean my dataframe before writing to S3.

```
from functools import reduce
oldColumns = df.schema.names
newColumns = []
for col in df.dtypes:
    newColumns.append(col[0][0].lower()+''.join(['_'+x.lower() if x.isupper() else x for x in col[0][1:]]))
df = reduce(lambda data, idx: data.withColumnRenamed(oldColumns[idx], newColumns[idx]), range(len(oldColumns)), df)
df.printSchema()
```

re:Post Agent · Answer

When working with AWS Glue to load parquet files from S3 to S3 Tables, there are several potential reasons why your tables might not be visible in Lake Formation after creation.

The issue appears to be related to how you're creating the S3 table from the parquet file. When you create a dataframe directly in your code and write it to an S3 table, it works correctly. However, when reading from an existing parquet file in S3, the table is created but not visible in Lake Formation.

Here are some potential solutions to consider:

1. Make sure your Spark session is properly configured with all the necessary S3 Tables Catalog for Apache Iceberg configurations. Your Spark session should include configurations like:
- spark.sql.extensions
- spark.sql.defaultCatalog
- spark.sql.catalog.s3tablesbucket
- spark.sql.catalog.s3tablesbucket.catalog-impl
- spark.sql.catalog.s3tablesbucket.warehouse

2. Ensure that the data lake location is properly registered in Lake Formation. If the S3 location isn't registered as a data lake location in Lake Formation, the tables might not appear.

3. Check that your AWS Glue job's IAM role has the necessary Lake Formation permissions (SELECT and DESCRIBE) on the tables you're trying to access.

4. Be aware that AWS Glue and Lake Formation don't support mixed-case column names and convert all column names to lowercase. If your parquet files have mixed-case column names, this could cause issues.

5. Try using a different approach to create the table. Instead of using the DataFrame API directly, you could try using Spark SQL to create the table first and then insert the data:

```
spark.sql(f"CREATE TABLE IF NOT EXISTS {namespace}.{table} USING iceberg AS SELECT * FROM parquet.`{s3_path}`")
```

6. Consider explicitly specifying the schema when creating the table rather than inferring it from the parquet file.

These approaches might help ensure that your S3 tables are properly registered with Lake Formation when creating them from parquet files in S3.  
**Sources**  
[Troubleshooting - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/security-lf-troubleshooting.html)  
[S3 tables catalog integration limitations - AWS Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/notes-s3-catalog.html)  
[Running ETL jobs on Amazon S3 tables with AWS Glue - Amazon Simple Storage Service](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-glue.html)

Issues when using AWS Glue to load parquet files from S3 to S3Tables. Lake Formation doesn't show the s3tables.

Add your answer

Relevant content