DEV Community

Michael
Michael

Posted on • Originally published at gbase8.cn

Avoid Incomplete SFTP/FTP Exports in GBase 8a with a Python UDF Rename Trick

#gbase #database #数据库

When exporting a large file (tens of GB) from GBase 8a to an FTP or SFTP server using SELECT INTO OUTFILE, the transfer takes time. If a downstream system picks up the file before the transfer is complete, it will read partial data — leading to corruption, parse errors, or silent data loss. This article shows how to use a pair of Python UDFs to make the file switch atomic, ensuring only a fully written file is ever seen by consumers.

The Strategy

The trick is simple: write to a temporary name, then rename.

  1. Export to a temporary name that your downstream scanner will ignore (e.g., data.csv.tmp or a staging directory).
  2. After the export completes, call a UDF that remotely renames (or moves) the file to its final name. The rename is an atomic metadata operation on the FTP/SFTP server, so the file instantly appears complete.

FTP Rename UDF

Create this function inside the gclusterdb database. It takes the FTP server address, credentials, source and target paths, and uses Python's built‑in ftplib to perform the rename.

use gclusterdb;
drop function if exists ftpRename;
create function ftpRename(server varchar(100),username varchar(100),password varchar(100),sourceFilename varchar(1000),targetFilename varchar(1000))
returns varchar
$$
from ftplib import FTP
ftp = FTP(server)
ftp.login(username, password)
ftp.rename(sourceFilename,targetFilename)
ftp.close()
return 'OK'
$$ language plpythonu;
Enter fullscreen mode Exit fullscreen mode

Example call:

gbase> select ftprename('10.0.2.201','gbase','gbase1234','/home/gbase/t1.csv.tmp','/home/gbase/t1.csv');
+------------------------------------------------------------------------------------------------+
| ftprename(...)                                                                                 |
+------------------------------------------------------------------------------------------------+
| OK                                                                                             |
+------------------------------------------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

SFTP Rename UDF

For SFTP we need the pysftp library (install with pip install pysftp). The function follows the same pattern.

use gclusterdb;
drop function if exists sftpRename;
create function sftpRename(server varchar(100),v_username varchar(100),v_password varchar(100),sourceFilename varchar(1000),targetFilename varchar(1000))
returns varchar
$$
import pysftp
with pysftp.Connection(server, username=v_username, password=v_password) as sftp:
    sftp.rename(sourceFilename,targetFilename)
    sftp.close()
return 'OK'
$$ language plpythonu;
Enter fullscreen mode Exit fullscreen mode

Example call:

gbase> select sftprename('10.0.2.201','gbase','gbase1234','/home/gbase/t1.csv.tmp','/home/gbase/t1.csv');
+------------------------------------------------------------------------------------------------+
| sftprename(...)                                                                                |
+------------------------------------------------------------------------------------------------+
| OK                                                                                             |
+------------------------------------------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

Complete Workflow

  1. Export to a temporary path (can be a different directory, as long as the user has write permission): 
select * from t1 into outfile 'sftp://gbase:gbase1234@10.0.2.201/home/gbase/t1.csv.tmp' fields terminated by ',';
Enter fullscreen mode Exit fullscreen mode
  1. Rename to the final filename (or move to the target directory): 
select gclusterdb.sftprename('10.0.2.201','gbase','gbase1234','/home/gbase/t1.csv.tmp','/home/gbase/t1.csv');
Enter fullscreen mode Exit fullscreen mode

You can also stage the file in a temporary directory and move it out:

select * from t1 into outfile 'sftp://gbase:gbase1234@10.0.2.201/home/gbase/tmp/t1_2.csv.tmp' fields terminated by ',';
select gclusterdb.sftprename('10.0.2.201','gbase','gbase1234','/home/gbase/tmp/t1_2.csv.tmp','/home/gbase/t1_2.csv');
Enter fullscreen mode Exit fullscreen mode

With this pattern, your gbase database exports become safe for automated ingestion pipelines. The downstream process never sees a half‑written file, eliminating an entire class of data consistency bugs in GBASE's MPP environment.

Top comments (0)