Curious if anyone has seen any limitation with Fli...
# troubleshooting
c
Curious if anyone has seen any limitation with Flink SQL when using the ROW () param. I need to produce avro encoded data that has a nested record.
Copy code
CREATE TABLE kafka_license_pool_ent_summary (
    entitlement_tag_id BIGINT,
    license_pool_id BIGINT,
    available_quantity BIGINT,
    alert_type BIGINT,
    entitlement_status STRING,
    is_active BIGINT,
    creation_date TIMESTAMP(3),
    pending_quantity BIGINT,
    billing_type STRING,
    status STRING,
    borrowing_quantity BIGINT,
    lending_quantity BIGINT,
    future_dated_quantity BIGINT,
    future_pending_quantity BIGINT,
    license_pool ROW<
        factory_install_reqd STRING,
        va_id BIGINT,
        is_active BIGINT,
        root_id STRING,
        name STRING
    >
) WITH (
    'connector'                              = 'kafka',
    'topic'                                  = 'ts3_pool_entitlement_summaries_enriched',
    'properties.bootstrap.servers'           = 'broker:29092',
    'properties.group.id'                    = 'ts3_pool_entitlement_summaries_enriched',
    'format'                                 = 'avro-confluent',
    'avro-confluent.schema-registry.url'     = 'http://schema-registry:8081',
    'avro-confluent.schema-registry.subject' = 'license_pool_ent_summary',
    'scan.startup.mode'                      = 'earliest-offset'
);
Inserting fixed values works fine:
Copy code
INSERT INTO kafka_license_pool_ent_summary
VALUES
(
    13890, -- entitlement_tag_id
    106431, -- license_pool_id
    23794, -- balance_quantity
    0, -- alert_type
    'OK', -- entitlement_status
    1, -- is_active
    TIMESTAMP '2023-09-15 18:46:10', -- creation_date
    0, -- pending_quantity
    'PREPAID', -- billing_type
    'ACTIVE', -- status
    5, -- borrowing_quantity
    10, -- lending_quantity
    0, -- future_dated_quantity
    0, -- future_pending_quantity
    ROW(
        'N', -- factory_install_reqd
        106431, -- id
        1, -- is_active
        '1234', -- root_id
        'CollaborationEA' -- virtual_account_name
    )
);
But when I try it on my actual sources:
Copy code
INSERT INTO kafka_license_pool_ent_summary
SELECT
    pes.after.alert_type AS alert_type,
    pes.after.available_quantity AS available_quantity,
    pes.after.billing_type AS billing_type,
    pes.after.borrowing_quantity AS borrowing_quantity,
    pes.after.created_at AS creation_date,
    pes.after.entitlement_status AS entitlement_status,
    pes.after.entitlement_tag_id AS entitlement_tag_id,
    pes.after.future_dated_quantity AS future_dated_quantity,
    pes.after.future_pending_quantity AS future_pending_quantity,
    pes.after.is_active AS is_active,
    pes.after.lending_quantity AS lending_quantity,
    pes.after.license_pool_id AS license_pool_id,
    pes.after.pending_quantity AS pending_quantity,
    pes.after.status AS status,
    ROW (
        csw.factory_install_reqd,
        csw.va_id,
        csw.is_active,
        csw.root_id,
        csw.name
    ) AS license_pool
FROM
    pool_entitlement_summaries AS pes
JOIN
    csw_virtual_accounts AS csw
ON
    pes.after.license_pool_id = csw.va_id;
I get:
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "." at line 18, column 12.
Was expecting one of:
    ")" ...
    "," ...
Thanks for any insight anyone may have.
I did test with a MAP instead and it works. Maybe a work around just wanted to more tightly control the key/values pairs in our messages:
Copy code
NSERT INTO kafka_license_pool_ent_summary
SELECT
    pes.after.alert_type,
    pes.after.available_quantity AS balance_quantity,
    pes.after.billing_type,
    pes.after.borrowing_quantity,
    pes.after.created_at AS creation_date,
    pes.after.entitlement_status,
    pes.after.entitlement_tag_id,
    pes.after.future_dated_quantity,
    pes.after.future_pending_quantity,
    pes.after.is_active,
    pes.after.lending_quantity,
    pes.after.license_pool_id,
    pes.after.pending_quantity,
    pes.after.status,
    MAP[
        'factory_install_reqd', csw.factory_install_reqd,
        'id', CAST(csw.va_id AS STRING), -- If id is expected as STRING in map
        'is_active', CAST(csw.is_active AS STRING), -- If is_active is expected as STRING in map
        'root_id', csw.root_id,
        'virtual_account_name', csw.name
    ] AS license_pool
FROM
    pool_entitlement_summaries AS pes
JOIN
    csw_virtual_accounts AS csw
ON
    pes.after.license_pool_id = csw.va_id;
Adjusted the CREATE TABLE accordingly with:
Copy code
license_pool MAPSTRING, STRING
Leaving this here in case anyone finds in in search.. the workaround is a subquery. So a simple example is:
Copy code
INSERT INTO kafka_license_pool_ent_summary
SELECT
    entitlement_tag_id,
    ROW (
        factory_install_reqd
    ) AS license_pool
FROM (
    SELECT
        pes.after.entitlement_tag_id AS entitlement_tag_id,
        csw.factory_install_reqd AS factory_install_reqd
    FROM
        pool_entitlement_summaries AS pes
    JOIN
        csw_virtual_accounts AS csw
    ON
        pes.after.license_pool_id = csw.va_id
) subquery;
You need to get the
.
notation out of the
ROW
statement. Works as expected now.