Azure / Azure/azure-postgresql

Azure Storage Extension cannot Export UUID Columns in Parquet Format

Open
#135 0 comments 1 reaction 0 assignees View on GitHub
Dominant language
Bicep
Stars
87
Forks
81
PR merge metrics
No merged PRs in 30d

Description

Dear,

I have a database with an UUID column and Azure Storage Extension (v1.7) cannot store it in parquet format.

This is the mininum reproducible example:

```sql
SELECT azure_storage.blob_put(
'my-storage-account',
'my-container',
'test.parquet',
row_data
) FROM (SELECT gen_random_uuid() as uuid) as row_data;
```

Error:

```
ERROR: azure_storage: rs: Failed to finish final Parquet batch: Cast error: Casting from Utf8 to FixedSizeBinary(16) not supported

SQL state: XX000
```

According to `pg-parquet`, UUID columns are supported and converted to `FixedSizeBinary(16)` internal type:

[Supported Types](https://github.com/CrunchyData/pg_parquet)

It seems that `azure_storage.blob_put` method is converting `UUID` columns to `Utf8` before sending them to `pg-parquet` for compression and the conversion between `Utf8` and `FixedSizeBinary(16)` is not supported by `pg-parquet`.

Since it is a supported column type, there should be no need for this conversion.

I have workarounded it by casting `UUID` to `text`:

```sql
SELECT azure_storage.blob_put(
'my-storage-account',
'my-container',
'test.parquet',
row_data
) FROM (SELECT gen_random_uuid()::text as uuid) as row_data;
```
But that demands me to convert back from `text` to `UUID` when reading back to the application and doesn't allow me to create Views to read back using the same database schema like this:

```sql
CREATE VIEW archive_table AS
SELECT * FROM azure_storage.blob_get(
'my-storage-account',
'my-container',
'test.parquet',
NULL::public.original_table -- Use original table schema
);
```

Instead, I have to describe all columns and convert back to original types to maintain application compatibility:

```sql
CREATE VIEW archive_table AS
SELECT CAST(uuid AS uuid) AS uuid FROM azure_storage.blob_get(
'my-storage-account',
'my-container',
'test.parquet'
) AS t(uuid text);
```

Please, kindly investigate this issue.

Thank you.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.