Chris Foster
06/21/2024, 7:38 PMCREATE 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:
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:
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:
[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.Chris Foster
06/22/2024, 2:22 PMNSERT 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:
license_pool MAPSTRING, STRINGChris Foster
06/24/2024, 10:32 PMINSERT 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.