Azure / Azure/azure-postgresql
Azure Storage Extension cannot Export UUID Columns in Parquet Format
- 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.