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:
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;