Support CTAS into Iceberg with timestamp(3)

[sshkvar]

Trino uses 3 digits of subsecond precision in Date and time functions, but Iceberg expects timestamp with 3 digits of subsecond precision.

As a result we can't create Iceberg table from Trino

create table iceberg.examples.test
as select now() as n;

Exception:
Timestamp precision (3) not supported for Iceberg. Use "timestamp(6) with time zone" instead.

Is it possible to make default precision configurable or allow use of Timestamp with precision (3) for Iceberg tables?

[hashhar]

I think this discussion will be more fruitful on the #iceberg channel on Trino Slack since there is a wide audience of other Iceberg users there who might have inputs/opinions.

I'd suggest closing this issue here and moving the discussion over there.

[sshkvar]

@hashhar thanks for you comment, but I think it is better to leave this issue open, because would be nice to have fix for it.
For me it looks as pretty common use case and would be great to have functionality which cast timestamp(3) to timestamp(6) automatically in Iceberg connector

[hashhar]

That makes sense. Let's rename this to be more descriptive then.

cc: @electrum @lxynov

[sshkvar]

I have renamed this issue and started discussion in slack

[MichaelTiemannOSC]

I found this issue looking for guidance on how to resolve this for my use case. Help?

Answering my own question: use 'timestamp(6)' instead of 'timestamp' as the SQL type for the Trino table. I'm using a system that's converting Pandas datatypes to Trino datatypes, and that was invisible to me.

[findepi]

As a result we can't create Iceberg table from Trino

create table iceberg.examples.test
as select now() as n;

use current_timestamp(6) instead

[daniil-dubin]

@findepi we have thousands of scripts (as well as many other companies I believe do) that we are migrating to iceberg automatically. We can't just mechanically replace all the occurrences of now() or current_timestamp() with current_timestamp(6) as it may brake other places unrelated to iceberg and requires semantical analysis of the script. It will take hundreds of hours of work for the data developers to change the scripts and validate them.
We are hacking iceberg connector to add conversion there but we'd like to avoid doing it as much as possible.

[MichaelTiemannOSC]

This is my hack to the Trino python client:

@@ -145,7 +145,7 @@ class TrinoTypeCompiler(compiler.GenericTypeCompiler):
        return self.visit_VARBINARY(type_, **kw)

    def visit_DATETIME(self, type_, **kw):
-       return self.visit_TIMESTAMP(type_, **kw)
+       return self.visit_TIMESTAMP(type_, **kw).replace('TIMESTAMP','TIMESTAMP(6)')


class TrinoIdentifierPreparer(compiler.IdentifierPreparer):

[findepi]

FWIW, all the JDBC connectors allow CTAS with timestamp precision other than supported by remote database's storage, including reducing the precision. This was a conscious choice, that we have debated about.

For JDBC connectors this was easy by the virtue of WriteFunction interface than can easily adapt what engine provides to what we store in the remote system, thus implementing the coercion. The unfortunate downsides are

• the connector needs to implement coercion logic that engine would normally do in INSERT case
• the CREATE TABLE also allows changing the type, which can be surprising for the users, because CT and CTAS do same logic for transforming from requested Trino type to storage type. Doing it differently would also be surprising to users anyway.

For Iceberg it's not as simple, because we don't have WriteFunction interface (or similar).
Also, we address the first drawback, and let engine coerce the data for CTAS same way as it does for INSERT.
We can do so by introducing intermediate step of planning CTAS query, where connector returns actual Trino types that will be persisted and engine applies coercions as necessary.

cc @losipiuk @martint @alexjo2144 @hashhar @wendigo

[rdblue]

@findepi, @bitsondatadev, any update on this? We're getting more reports about it and would love to have it fixed somehow.

[bitsondatadev]

@findepi, @bitsondatadev, any update on this? We're getting more reports about it and would love to have it fixed somehow.

#13981 (comment)

Looks like this was broken down into multiple PRs. Checking with @mdesmet on the status.