... wondering...do we have something funny at the ...
# troubleshooting
g
... wondering...do we have something funny at the data layer, as the command looks right, and even the err does not hint to a bad command.
Copy code
Flink SQL> create table c_paimon.outbound.adultsxx WITH (
>      'file.format'                       = 'parquet'
>     ,'compaction.min.file-num'           = '2'
>     ,'compaction.early-max.file-num'     = '50'
>     ,'snapshot.time-retained'            = '1h'
>     ,'snapshot.num-retained.min'         = '5'
>     ,'snapshot.num-retained.max'         = '20'
>     ,'table.exec.sink.upsert-materialize'= 'NONE'
> ) AS 
> select
>      JSON_VALUE(data, '$.nationalid')       AS nationalid
>     ,JSON_VALUE(data, '$._id')              AS _id
>     ,JSON_VALUE(data, '$.name')             AS name
>     ,JSON_VALUE(data, '$.surname')          AS surname
>     ,JSON_VALUE(data, '$.gender')           AS gender
>     ,JSON_VALUE(data, '$.dob')              AS dob
>     ,JSON_VALUE(data, '$.marital_status')   AS marital_status
>     ,JSON_VALUE(data, '$.status')           AS status
>     ,CAST(JSON_QUERY(data, '$.account') AS ARRAY<STRING>) as account
>     ,JSON_QUERY(data, '$.address')          AS address
>     ,created_at                             AS created_at
> FROM postgres_catalog.inbound.adults;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type VARCHAR(2147483647) to type VARCHAR(2147483647) ARRAY
e
I suspect something is happening when parsing accounts, maybe try something like the following
Copy code
CREATE TABLE c_paimon.outbound.adultsxx WITH (
     'file.format'                       = 'parquet',
     'compaction.min.file-num'           = '2',
     'compaction.early-max.file-num'     = '50',
     'snapshot.time-retained'            = '1h',
     'snapshot.num-retained.min'         = '5',
     'snapshot.num-retained.max'         = '20',
     'table.exec.sink.upsert-materialize'= 'NONE'
) AS 
SELECT
     JSON_VALUE(data, '$.nationalid')       AS nationalid,
     JSON_VALUE(data, '$._id')              AS _id,
     JSON_VALUE(data, '$.name')             AS name,
     JSON_VALUE(data, '$.surname')          AS surname,
     JSON_VALUE(data, '$.gender')           AS gender,
     JSON_VALUE(data, '$.dob')              AS dob,
     JSON_VALUE(data, '$.marital_status')   AS marital_status,
     JSON_VALUE(data, '$.status')           AS status,
     from_json(JSON_QUERY(data, '$.account'), 'ARRAY<MAP<STRING, STRING>>') as account,
     JSON_QUERY(data, '$.address')          AS address,
     created_at                             AS created_at
FROM postgres_catalog.inbound.adults;
g
thank you, will give it a try... i see the change/modification you did... 'array<map<string,string>>')
ooops, it's not liking the from_json it seems.
Copy code
Flink SQL> CREATE TABLE c_paimon.outbound.adultsxx WITH (
>      'file.format'                       = 'parquet',
>      'compaction.min.file-num'           = '2',
>      'compaction.early-max.file-num'     = '50',
>      'snapshot.time-retained'            = '1h',
>      'snapshot.num-retained.min'         = '5',
>      'snapshot.num-retained.max'         = '20',
>      'table.exec.sink.upsert-materialize'= 'NONE'
> ) AS 
> SELECT
>      JSON_VALUE(data, '$.nationalid')       AS nationalid,
>      JSON_VALUE(data, '$._id')              AS _id,
>      JSON_VALUE(data, '$.name')             AS name,
>      JSON_VALUE(data, '$.surname')          AS surname,
>      JSON_VALUE(data, '$.gender')           AS gender,
>      JSON_VALUE(data, '$.dob')              AS dob,
>      JSON_VALUE(data, '$.marital_status')   AS marital_status,
>      JSON_VALUE(data, '$.status')           AS status,
>      from_json(JSON_QUERY(data, '$.account'), 'ARRAY<MAP<STRING, STRING>>') as account,
>      JSON_QUERY(data, '$.address')          AS address,
>      created_at                             AS created_at
> FROM postgres_catalog.inbound.adults;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature from_json(<CHARACTER>, <CHARACTER>)
nope, added library, still same error.
from_json does not seem to be a flink sql function, i can find it in pyflink/pyspark etc.
I found the below...
Copy code
CREATE TABLE my_source_table (
    id INT,
    json_data STRING
) WITH (
    'connector' = 'datagen',
    'rows-per-second' = '1',
    'fields.id.kind' = 'sequence',
    'fields.id.start' = '1',
    'fields.id.end' = '10',
    'fields.json_data.kind' = 'sequence',
    'fields.json_data.start' = '{"name": "Alice", "age": 30}',
    'fields.json_data.end' = '{"name": "Bob", "age": 25}'
);

CREATE TABLE my_parsed_table (
    id INT,
    user_name STRING,
    user_age INT
) WITH (
    'connector' = 'print'
);

INSERT INTO my_parsed_table
SELECT
    id,
    parsed_json.name,
    parsed_json.age
FROM
    my_source_table,
    LATERAL TABLE(FROM_JSON(json_data, 'ROW<name STRING, age INT>')) AS parsed_json;
I refactored this into
Copy code
SELECT
     parsed_json.fspiagentaccountid
    ,parsed_json.accountid
    ,parsed_json.fspiid
    ,parsed_json.fspiagentid
    ,parsed_json.accounttype
    ,parsed_json.membername
FROM
    postgres_catalog.inbound.adults,
    LATERAL TABLE(FROM_JSON(JSON_QUERY(data, '$.account'), 'ARRAY<ROW<fspiagentaccountid STRING, accountid STRING, fspiid STRING, fspiagentid STRING, accounttype STRING, membername STRING, cardholder STRING, cardnumber STRING, expdate STRING, cardnetwork STRING, issuingbank STRING>>')) AS parsed_json;
and still getting
Copy code
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature FROM_JSON(<CHARACTER>, <CHARACTER>)
interestiing, executing example exactly as is also results in same error. which makes me think it's not syntax on my side.
Copy code
Flink SQL> CREATE TABLE my_source_table (
>     id INT,
>     json_data STRING
> ) WITH (
>     'connector' = 'datagen',
>     'rows-per-second' = '1',
>     'fields.id.kind' = 'sequence',
>     'fields.id.start' = '1',
>     'fields.id.end' = '10',
>     'fields.json_data.kind' = 'sequence',
>     'fields.json_data.start' = '{"name": "Alice", "age": 30}',
>     'fields.json_data.end' = '{"name": "Bob", "age": 25}'
> );
[INFO] Execute statement succeeded.

Flink SQL> CREATE TABLE my_parsed_table (
>     id INT,
>     user_name STRING,
>     user_age INT
> ) WITH (
>     'connector' = 'print'
> );
[INFO] Execute statement succeeded.

Flink SQL> INSERT INTO my_parsed_table
> SELECT
>     id,
>     parsed_json.name,
>     parsed_json.age
> FROM
>     my_source_table,
>     LATERAL TABLE(FROM_JSON(json_data, 'ROW<name STRING, age INT>')) AS parsed_json;
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature FROM_JSON(<CHARACTER>, <CHARACTER>)
ok... think i figured something out... yes from_json is what i need, but it's only available in 2.x the json 1.20 package does not include json nor from_json. and well if i try and use Flink 2.0... then run into a Postgresql CDC bug... rock and a hard place.