Created
August 29, 2023 01:36
-
-
Save kmatt/a43ff0c95ffdd3eaba5a2c292e22eeac to your computer and use it in GitHub Desktop.
DuckDB over SSH
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
""" | |
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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.