You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@iceberg.apache.org by "mderoy (via GitHub)" <gi...@apache.org> on 2024/06/07 18:15:45 UTC
[I] Max Length (And Padded) String Types [iceberg]
mderoy opened a new issue, #10461:
URL: https://github.com/apache/iceberg/issues/10461
### Proposed Change
Traditional SQL engines support four major string types
```
char[N]
varchar[N]
nchar[N]
nvarchar[N]
```
While the iceberg format supports only one
```
string (equivalent to nvarchar[unlimited])
```
This causes some friction when migrating data from a traditional SQL engine into iceberg tables.
### Proposal
We should add two new types to improve integrations with Traditional SQL engines.
```
string(L) <- L is the max number of utf-8 characers
string_padded(L) <- same as above, but padded with spaces to 'L' total characters
```
### Background
#### SQL incompatibility
firstly, there are some SQL statements who's behavior depends on the datatype having padding up to some length. For example the like operator which compares two strings including the padding. will differ when using a char(20) than when using a varchar(20)
```
SYSTEM.ADMIN(ADMIN)=> create table strexample(v1 char(20));
SYSTEM.ADMIN(ADMIN)=> insert into strexample values(' f ');
SYSTEM.ADMIN(ADMIN)=> insert into strexample values(' f');
SYSTEM.ADMIN(ADMIN)=> select * from strexample a, strexample b where a.v1 like b.v1;
V1 | V1
----------------------+----------------------
f | f
f | f
f | f
f | f
(4 rows)
SYSTEM.ADMIN(ADMIN)=> create table strexamplevar(v1 nvarchar(20));
SYSTEM.ADMIN(ADMIN)=> insert into strexamplevar values(' f ');
SYSTEM.ADMIN(ADMIN)=> insert into strexamplevar values(' f');
SYSTEM.ADMIN(ADMIN)=> select * from strexamplevar a, strexamplevar b where a.v1 like b.v1;
V1 | V1
-----+-----
f | f
f | f
(2 rows)
```
As such users cannot move their data into open formats and expect that their queries return the same results...even if they continue to use the same engine to query them (due to iceberg not supporting a fixed padded string type)
#### Better integration with long lived SQL engines that integrate with iceberg
Long lived SQL engines like DB2 and Netezza (and others) were written with the assumption that there is some max limit to strings. As such to support iceberg tables they either need to silently truncate data, or cause queries to fail if the strings get above some length. These engines may also use string size to optimize their query planning. Having support for strings that are capped at some length would allow users to offload data into iceberg and make use of other tooling/engines in the lakehouse (like spark, presto, etc) without compromising the memory requirements of these engines.
**for more info, see the proposal document**
### Proposal document
https://docs.google.com/document/d/1chIx22dNZcMSsS607F96ARu4m4Yyf1dbsYxQTrfQ-5o/edit?usp=sharing
### Specifications
- [X] Table
- [X] View
- [ ] REST
- [ ] Puffin
- [ ] Encryption
- [ ] Other
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Max Length (And Padded) String Types [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #10461: Max Length (And Padded) String Types
URL: https://github.com/apache/iceberg/issues/10461
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Max Length (And Padded) String Types [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #10461:
URL: https://github.com/apache/iceberg/issues/10461#issuecomment-2555996425
This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Max Length (And Padded) String Types [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #10461:
URL: https://github.com/apache/iceberg/issues/10461#issuecomment-2518818096
This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org