Use ST_ExportTo to export trajectory data to an OSS bucket - PolarDB

This function is not supported in later versions. Use Cold data tiered storage instead.

Exports the time, space, event, and property data of a trajectory object to a folder in an Object Storage Service (OSS) bucket. The data is stored in .gtf files.

Syntax

trajectory ST_ExportTo(trajectory traj, text path, text config);

Parameters

Parameter	Description
traj	The trajectory object to export.
path	The OSS folder path. Only OSS folders are supported. See OSS path format below.
config	A JSON string. The supported key is `compress`.

OSS path format

OSS://<AccessKey ID>:<AccessKey secret>@<Endpoint>/<Bucket>/<Directory>

Field	Description
`<AccessKey ID>`	Your Alibaba Cloud AccessKey ID.
`<AccessKey secret>`	Your Alibaba Cloud AccessKey secret.
`<Endpoint>`	The OSS endpoint for the region where the trajectory data is stored. For the list of endpoints, see OSS domain names. Deploy your PolarDB cluster in the same region as the OSS bucket to get the best export performance.
`<Bucket>`	The name of the OSS bucket.
`<Directory>`	The target folder path within the bucket.

Compression options (`config`)

The config parameter takes a JSON object. Use the compress key to set the compression algorithm.

Value	Notes
`none`	No compression. Default.
`lz4`	—
`lzo`	—
`zstd`	Best overall efficiency.
`snappy`	—
`zlib`	Highest compression ratio.

Example: '{"compress": "zstd"}'

Output file behavior

Exported data is appended to .gtf files in the specified OSS folder:

File naming: <sequence_number>_0_1.gtf, sorted in descending order by sequence number.
File size: Each file is approximately 2 GB, controlled by the ganos.trajectory.ext_storage_block_size parameter.
Append logic: If a .gtf file already contains data for the trajectory object, the function appends to it. If the data already exists in the folder, the function skips writing. If the data does not exist in the folder, the function reads and writes it.

Warning

Changes made to the database are not reflected in the OSS files. If you modify the OSS files directly, the corresponding data in the database may become unavailable.

Example

Export all trajectory objects in the trajs table to an OSS folder:

-- Export all trajectories to OSS without compression
UPDATE trajs
SET traj = ST_ExportTo(
    traj,
    'OSS://<AccessKey ID>:<AccessKey secret>@oss-cn-beijing-internal/<Bucket>/<Directory>',
    '{}'
);

Replace the placeholders with your actual values:

Placeholder	Description
`<AccessKey ID>`	Your Alibaba Cloud AccessKey ID
`<AccessKey secret>`	Your Alibaba Cloud AccessKey secret
`<Bucket>`	Your OSS bucket name
`<Directory>`	The target folder path in the bucket

To enable compression, replace '{}' with a compress value, for example '{"compress": "zstd"}'.