This topic provides the DDL syntax that is used to create a FileSystem dimension table, describes the parameters in the WITH clause, and provides sample code.

Prerequisites

An Object Storage Service (OSS) bucket is created. For more information, see Create buckets.

Limits

Only Flink that uses Ververica Runtime (VVR) 3.0.0 or later supports FileSystem connectors.

DDL syntax

CREATE TABLE filesystem_dim (
  id STRING,
  name STRING
) WITH (
  'connector' = 'filesystem',
  'path' = 'oss://my_path/my_file',
  'format' = 'csv' 
)

Parameters in the WITH clause

Parameter Description Required Remarks
connector The type of the dimension table. Yes Set the value to filesystem.
path The directory in which a file is saved. Yes The file path is in the Uniform Resource Identifier (URI) format. Example: oss://my_path/my_file.
format The format of a file. Yes Valid values:
  • csv
  • json
  • avro
  • parquet
  • orc
  • raw

For more information about the format parameter, see Formats.

lookup.join.cache.ttl The time-to-live (TTL) at which data is re-read. No Default value: 60. Unit: minutes. This value indicates that data is re-read every 60 minutes.

Sample code

-- Create the event source table. 
CREATE TABLE event (
  id STRING, 
  data STRING
) WITH (
  'connector' = 'datahub'
   ...
);

-- Create the white_list dimension table. 
CREATE TABLE white_list (
  id STRING,
  name STRING,
) WITH (
  'connector' = 'filesystem',
  'path' = '${remote_path_uri}',
  'format' = '${format}'
);

-- Join the event source table with the white_list dimension table. 
SELECT e.*, w.*
FROM event AS e
JOIN white_list FOR SYSTEM_TIME AS OF proctime() AS w
ON e.id = w.id;