DuckDB over SSH

duckssh.py

""" 
  Run DuckDB query on over SSH to avoid scanning full file set on a remote server,
  and make results availble to local DuckDB process
"""

import io, paramiko, duckdb

sql = "SELECT * FROM read_json_auto('/path/to/data.json')"
cmd = f'duckdb -csv -s "{sql}"'

client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
client.connect(...)

stdin, stdout, stderr = client.exec_command(cmd)

# Load stdout into a buffer as DuckDB needs seek() which paramiko.channel.ChannelFile does not support
with io.BytesIO() as buf:
    buf.write(stdout.read(size=None))
    df = duckdb.read_csv(buf).fetchdf()
    #tbl = duckdb.read_csv(buf).fetch_arrow_table()

client.close()  # https://docs.paramiko.org/en/3.3/api/client.html#paramiko.client.SSHClient.close

print(df)

When a large amount of data is on a remote server, and we want to summarize it for local visualization without requiring a local DuckDB
process to transport and scan all source data, this runs a DuckDB process on the remote, returning summary results from query as CSV.

Summary data is then available to local DuckDB process for analysis, visualization, Excel, etc.