https://flink.apache.org/ logo
Join Slack
Powered by
Hi folks, I'm having a little trouble using Flink ...
# troubleshooting
j
Hi folks, I'm having a little trouble using Flink SQL to setup a table with 
JSON_OBJECT
component that references a 
DATE
column. Here's the schema I'm trying to create and the error:
Copy code
CREATE TABLE test_table (
  some_date DATE,
  object AS JSON_OBJECT(
    KEY 'some_date' VALUE some_date 
  )
)
COMMENT ''
WITH (
  'connector'='datagen'
)
; 
Flink SQL> select * from default_catalog.default_database.test_table ;
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.planner.codegen.CodeGenException: Type 'DATE' is not scalar or cannot be converted into JSON.
So I figure I need to use the 
DATE_FORMAT
function to convert 
some_date
to a string, however, there's an issue with using either `"`'s or `'`'s:
Copy code
Flink SQL> CREATE TABLE test_table (
>   some_date DATE,
>   object AS JSON_OBJECT(
>     KEY 'some_date' VALUE DATE_FORMAT(some_date, "YYYY-MM-dd")
>   )
> )
> COMMENT ''
> WITH (
>   'connector'='datagen'
> )
> ;
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "\"" at line 4, column 50.
Was expecting one of:
    "ABS" ...
    "ARRAY" ...
    "AVG" ...
    "CARDINALITY" ...
    "CASE" ...
    "CAST" ...
    "CEIL" ...
    "CEILING" ...
    "CHAR_LENGTH" ...
    "CHARACTER_LENGTH" ...
    "CLASSIFIER" ...
    "COALESCE" ...
    "COLLECT" ...
    "CONVERT" ...
    "COUNT" ...
    "COVAR_POP" ...
    "COVAR_SAMP" ...
    "CUME_DIST" ...
    "CURRENT" ...
    "CURRENT_CATALOG" ...
    "CURRENT_DATE" ...
    "CURRENT_DEFAULT_TRANSFORM_GROUP" ...
    "CURRENT_PATH" ...
    "CURRENT_ROLE" ...
    "CURRENT_SCHEMA" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "CURRENT_USER" ...
    "CURSOR" ...
    "DATE" ...
    "DEFAULT" ...
    "DENSE_RANK" ...
    "ELEMENT" ...
    "EVERY" ...
    "EXISTS" ...
    "EXP" ...
    "EXTRACT" ...
    "FALSE" ...
    "FIRST_VALUE" ...
    "FLOOR" ...
    "FUSION" ...
    "GROUPING" ...
    "HOUR" ...
    "INTERSECTION" ...
    "INTERVAL" ...
    "JSON_ARRAY" ...
    "JSON_ARRAYAGG" ...
    "JSON_EXISTS" ...
    "JSON_OBJECT" ...
    "JSON_OBJECTAGG" ...
    "JSON_QUERY" ...
    "JSON_VALUE" ...
    "LAG" ...
    "LAST_VALUE" ...
    "LEAD" ...
    "LEFT" ...
    "LN" ...
    "LOCALTIME" ...
    "LOCALTIMESTAMP" ...
    "LOWER" ...
    "MATCH_NUMBER" ...
    "MAX" ...
    "MIN" ...
    "MINUTE" ...
    "MOD" ...
    "MONTH" ...
    "MULTISET" ...
    "NEW" ...
    "NEXT" ...
    "NOT" ...
    "NTH_VALUE" ...
    "NTILE" ...
    "NULL" ...
    "NULLIF" ...
    "OCTET_LENGTH" ...
    "OVERLAY" ...
    "PERCENT_RANK" ...
    "PERIOD" ...
    "POSITION" ...
    "POWER" ...
    "PREV" ...
    "RANK" ...
    "REGR_COUNT" ...
    "REGR_SXX" ...
    "REGR_SYY" ...
    "RIGHT" ...
    "ROW" ...
    "ROW_NUMBER" ...
    "RUNNING" ...
    "SECOND" ...
    "SESSION_USER" ...
    "SOME" ...
    "SPECIFIC" ...
    "SQRT" ...
    "STDDEV_POP" ...
    "STDDEV_SAMP" ...
    "SUBSTRING" ...
    "SUM" ...
    "SYSTEM_USER" ...
    "TIME" ...
    "TIMESTAMP" ...
    "TRANSLATE" ...
    "TRIM" ...
    "TRUE" ...
    "TRUNCATE" ...
    "UNKNOWN" ...
    "UPPER" ...
    "USER" ...
    "VAR_POP" ...
    "VAR_SAMP" ...
    "YEAR" ...
    <UNSIGNED_INTEGER_LITERAL> ...
    <APPROX_NUMERIC_LITERAL> ...
    <DECIMAL_NUMERIC_LITERAL> ...
    <BINARY_STRING_LITERAL> ...
    <QUOTED_STRING> ...
    <PREFIXED_STRING_LITERAL> ...
    <UNICODE_STRING_LITERAL> ...
    <BIG_QUERY_DOUBLE_QUOTED_STRING> ...
    <BIG_QUERY_QUOTED_STRING> ...
    "(" ...
    <LBRACE_D> ...
    <LBRACE_T> ...
    <LBRACE_TS> ...
    <LBRACE_FN> ...
    "?" ...
    "+" ...
    "-" ...
    <BRACKET_QUOTED_IDENTIFIER> ...
    <QUOTED_IDENTIFIER> ...
    <BACK_QUOTED_IDENTIFIER> ...
    <HYPHENATED_IDENTIFIER> ...
    <IDENTIFIER> ...
    <UNICODE_QUOTED_IDENTIFIER> ...
    
Flink SQL> CREATE TABLE test_table (
>   some_date DATE,
>   object AS JSON_OBJECT(
>     KEY 'some_date' VALUE DATE_FORMAT(some_date, 'YYYY-MM-dd')
>   )
> )
> COMMENT ''
> WITH (
>   'connector'='datagen'
> )
> ;
> 
[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply 'DATE_FORMAT' to arguments of type 'DATE_FORMAT(<DATE>, <CHAR(10)>)'. Supported form(s): 'DATE_FORMAT(<TIMESTAMP>, <STRING>)'
'DATE_FORMAT(<STRING>, <STRING>)'
Well, looks like switching from a 
DATE
to a 
TIMESTAMP
, seems to fix the issue:
Copy code
Flink SQL> CREATE TABLE test_table (
>   some_date TIMESTAMP,
>   object AS JSON_OBJECT(
>     KEY 'some_date' VALUE DATE_FORMAT(some_date, 'YYYY-MM-dd')
>   )
> )
> COMMENT ''
> WITH (
>   'connector'='datagen'
> )
> ;
However, 
"
still fails:
Copy code
Flink SQL> CREATE TABLE test_table (
>   some_date TIMESTAMP,
>   object AS JSON_OBJECT(
>     KEY 'some_date' VALUE DATE_FORMAT(some_date, "YYYY-MM-dd")
>   )
> )
> COMMENT ''
> WITH (
>   'connector'='datagen'
> )
> ;
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "\"" at line 4, column 50.
Still not sure how to encode 
DATE
column as a JSON
Ok, I got something working by using 
JSON_STRING
+ 
YEAR
/ 
MONTH
/ `DAYOFMONTH`:
Copy code
CREATE TABLE test_table (
   some_date DATE,
   object AS JSON_OBJECT(
     KEY 'some_date' VALUE JSON_STRING(YEAR(`some_date`)) || '-' || JSON_STRING(MONTH(`some_date`)) || '-' || JSON_STRING(DAYOFMONTH(`some_date`))
   )
 )
 COMMENT ''
 WITH (
   'connector'='datagen'
 )
 ;

  some_date                         object
 2023-03-03       {"some_date":"2023-3-3"}
 2023-03-03       {"some_date":"2023-3-3"}
Put in a improvement request to modify 
DATE_FORMAT
to support 
DATE
types: https://issues.apache.org/jira/browse/FLINK-31320
2 Views
Open in Slack
PreviousNext
Powered by