Issues with Metadata Table Creation and VARCHAR Length Constraints in Snowflake to Iceberg Conversion

Ayman1 — Wed, 15 Jan 2025 17:14:10 GMT

Hi Qlik Replicate Community,

We are working on a task to convert Snowflake tables into Iceberg format using Qlik Replicate. While we have successfully used Global Parameters to handle data type conversions (e.g., ensuring VARCHAR fields conform to Iceberg’s max length requirement of 16777216), we’re encountering issues related to metadata tables that are being created during the replication process.

For example, one of the problematic metadata tables looks like this:
CREATE ICEBERG TABLE "PUBLIC"."attrep_☺☺☺☺☺" EXTERNAL_VOLUME='iceberg_external_volume' BASE_LOCATION='ATTUNITY_CDC' CATALOG='SNOWFLAKE' ( "seq" INTEGER NOT NULL, "col1" VARCHAR(41), "col2" VARCHAR(41), "col3" VARCHAR(41), "col4" VARCHAR(41), "col5" VARCHAR(41), "col6" VARCHAR(41), "col7" VARCHAR(37), "col8" VARCHAR(37), "col9" VARCHAR(16777216), "col10" VARCHAR(16777216), "col11" VARCHAR(16777216) )

SQL Compilation error: For Iceberg tables, only max length (16777216) is supported for 'VARCHAR(L)/STRING(L)' or use STRING directly.

Challenges:

Metadata Tables in PUBLIC Schema:
- What is the purpose of these metadata tables (e.g., attrep_☺☺☺☺☺, attrep_apply_exceptions)?
- Are these tables essential, or is there a way to disable their creation entirely?
Handling Data Type Conversions for Metadata Tables:
- While we are applying Global Parameters to manage VARCHAR length for our main tables, this does not seem to cover the metadata tables.
- Is there a way to enforce VARCHAR length or precision conversion at the endpoint level instead of relying on Global Parameters?
Iceberg-Specific Data Type Constraints:
- Iceberg requires TIMESTAMP with microsecond precision (scale 6). Currently, we’re handling this through Global Parameters, but this approach doesn’t extend to metadata tables. Is there a way to address this at a broader configuration level?

We’d appreciate any insights or recommendations on how to address these issues, specifically around:

Disabling or bypassing metadata table creation.
Applying broader data type handling, including for metadata tables, at the endpoint level.

Re: Issues with Metadata Table Creation and VARCHAR Length Constraints in Snowflake to Iceberg Conversion

john_wang — Fri, 24 Jan 2025 08:29:26 GMT

Hello @Ayman1 ,

Thanks for reaching out to Qlik Community!

By default, Qlik Replicate does not support creating Iceberg tables, and, in my opinion, it hasn’t been fully QAed. If you wish to test this functionality, you will need to customize the Snowflake endpoint syntax. This customization falls under the scope of Professional Services (PS). I recommend reaching out to the PS team for assistance.

Regarding the error you encountered:

SQL Compilation Error:
For Iceberg tables, only max length (16777216) is supported for 'VARCHAR(L)/STRING(L)' or use STRING directly.

As the error message indicates, Iceberg tables only support the STRING data type, rather than VARCHAR(16777216). While customizing the syntax may resolve this issue, I strongly suggest proceeding with the guidance of the PS team to ensure a proper implementation.

Hope this helps.

John.

topic Re: Issues with Metadata Table Creation and VARCHAR Length Constraints in Snowflake to Iceberg Conversion in Qlik Replicate

Issues with Metadata Table Creation and VARCHAR Length Constraints in Snowflake to Iceberg Conversion

Challenges:

Re: Issues with Metadata Table Creation and VARCHAR Length Constraints in Snowflake to Iceberg Conversion