You can handle files on an SFTP server in Peliqan using the SFTP connector. Add an SFTP connection under “Connections” first (host, port, username, password etc.). Next, write a Python script in Peliqan to e.g. import data from CSV or JSON files. In your Python script you can read the data and insert into a table in your data warehouse.

☝

You can request an SFTP account from the Peliqan market place !

Reading data from SFTP

Example script to read a JSON file from an SFTP server:

dw = pq.dbconnect(pq.DW_NAME)     # your data warehouse
sftp = pq.sftpconnect("SFTP")

files = sftp.dir("/")
for file in files["detail"]:
	if file["type"] == "file": # type can be "file" or "folder"
		jsonStr = sftp.read_file(file["name"])  # assuming one object in file
		record = json.loads(jsonStr)
		dw.insert("my_db", "my_schema", "my_table", record)

Writing data to SFTP

Note: writing data to SFTP is not done through the pq function.

Example script to write a CSV file to an SFTP server:

import pysftp
import io
import csv

cnopts = pysftp.CnOpts()
cnopts.hostkeys = None # do not verify hostname

# Add a connection first of type "Peliqan Secret store"
password = pq.get_secret("Peliqan Secret Store - SFTP password")
sftpconn = pysftp.Connection(host = "sftp.eu.peliqan.io", username = "testuser", password = password, port = 2022, cnopts = cnopts)

dbconn = pq.dbconnect(pq.DW_NAME)
query = "SELECT * FROM odoo.accounts LIMIT 5"
df = dbconn.fetch(pq.DW_NAME, query = query, df = True)
st.write(df)

csv_buffer = io.StringIO()
df.to_csv(csv_buffer, index=False, quotechar='"', quoting=csv.QUOTE_ALL)
csv_buffer.seek(0)

with sftpconn:
    with sftpconn.open('data.csv', 'w') as remote_file:
        remote_file.write(csv_buffer.getvalue())

SFTP - Getting started in Peliqan

Reading data from SFTP

Writing data to SFTP