Add column / update schema in an existing table without delete data

[mbiemann]

Hi @igorborgest ,

In a recent issue #198 was add the combination of catalog_versioning and mode overwrite in s3.to_parquet to update the table, however, I'm looking for some method that don't delete the old data and update the existing table adding the new columns.

Let me exemplify... This is the actual "insert":

wr.s3.to_parquet(
    df=pd.DataFrame([{
        'event_date':     datetime.now().strftime('%Y-%m-%d'),
        'event_datetime': datetime.now().isoformat(),
        'event_name':     f'Event {chr(random.randint(65,90))}',
        'event_value':    random.randint(10000,20000)
    }]),
    dataset=True,
    mode='append',
    database=databaseName,
    table=tableName,
    partition_cols=['event_date'],
    path=f's3://{bucket}/{databaseName}/{tableName}/',
    compression='snappy'
)

If I add a new column to DataFrame, the data is actual write in parquet, but I can't select via Athena.

wr.s3.to_parquet(
    df=pd.DataFrame([{
        'event_date':     datetime.now().strftime('%Y-%m-%d'),
        'event_datetime': datetime.now().isoformat(),
        'event_name':     f'Event {chr(random.randint(65,90))}',
        'event_value':    random.randint(10000,20000),
        'new_column1':    random.randint(1,10)
    }]),
    dataset=True,
    mode='append',
    database=databaseName,
    table=tableName,
    partition_cols=['event_date'],
    path=f's3://{bucket}/{databaseName}/{tableName}/',
    compression='snappy'
)

Note: Editing the schema via Console to add the new column on the table makes it possible to select it via Athena.

I would you like to know what do you thing about the solution:

Around catalog.py line 995, if catalog_versioning is true, compare the count of actual columns (need to get existing table definition) with the count of incoming columns (using table_input.columns_types).
If the counts is difference, update the table like like 993

I would like to contribute to this change if you agree with the solution or have another one in mind.

Best Regards,
Marcell Biemann

[igorborgest]

Hi @mbiemann, thanks for reaching out!

Yep, it makes sense and a PR would be great!

We just should be cautious about some details related to invalid schema evolution:

• The columns order will be relevant? We will need to sort it back to the original order?
• We will only accept new columns in the tail of the table? Or anywhere?
• What about type mismatch?

It will be a really cool feature, but schema evolution is not a trivial subject.

[mbiemann]

Thanks @igorborgest . I’m studying the best solution for Schema Evolution with Parquet.

[igorborgest]

Hi @mbiemann!

I've just submitted the PR above with a first propose about schema evolution on Parquet Datasets. What do you think?

To test it you can run:

pip install git+https://github.com/awslabs/aws-data-wrangler.git@schema-evolution

[igorborgest]

New tutorial about the subject added!

[igorborgest]

Released on version 1.2.0