Fetch from cloud storage

Status

Variants

Connect to a store as a file system

Open a connection to a store that downstream read and write steps then use.

List the objects in a store

Return one row per file or object in a bucket, folder or server, with its metadata.

Copy, rename or delete remote files

Copy, move, rename, check or delete files and folders in a remote store.

Read rows from files in cloud storage

Read the files at a cloud, Hadoop or file-transfer path straight into rows.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

In Amazon S3, a bucket is a container for objects, and an object key is the unique identifier for an object within a bucket.^[5] Amazon S3 general purpose buckets have a flat structure, and the S3 console shows folders by grouping objects whose key names share a prefix.^[6] When the S3 console creates a folder, S3 stores a 0-byte object whose key is the folder name followed by a forward slash.^[6] A step that lists an S3 prefix should expect zero-byte objects whose keys end in a slash, and leave them out before reading anything. (Meridian's view)

In Amazon S3, a ListObjectsV2 request returns some or all of the objects in a bucket, up to 1,000 with each request.^[7] A ListObjectsV2 response sets IsTruncated to true when more keys are available to return.^[7] A listing that reads only its first response can miss objects, so check that the tool follows the listing to its end. (Meridian's view)

By default, a long-running DuckDB query re-reads an S3 object at whatever version is current at read time, which can change if the object is overwritten.^[8] DuckDB's s3_version_id_pinning setting pins reads to one captured version of each object, so a query sees a consistent version even if the object is overwritten mid-query.^[8] When files in a bucket may be replaced while a job runs, pin the version or copy the files first, so that every step reads the same bytes. (Meridian's view)

FTP users may authenticate with a plain-text sign-in protocol, normally in the form of a username and password.^[4] For secure transmission that protects the username and password, FTP is often secured with an encryption layer (FTPS) or replaced with the SSH File Transfer Protocol (SFTP).^[4] Where a server offers SFTP, prefer it to plain FTP. (Meridian's view)

The method

Object storage was proposed at Garth Gibson's Carnegie Mellon University lab as a research project in 1996.^[3] Amazon Web Services launched Amazon S3 in the United States on March 14, 2006.^[2] S3 offers a standard application programming interface (API) to read and write remote files, while regular web servers, which predate S3, offer no common write API.^[8] The broad adoption of Amazon S3 has given rise to competing services based on the S3 API, such as Cloudian, Backblaze B2 and Wasabi.^[2]

DuckDB's httpfs file system is tested with Amazon S3, Minio, Google Cloud and lakeFS, and other services that implement the S3 API should also work, though not all features may be supported.^[8] In DuckDB, public file reads from S3 need range requests, private reads need secret key or session token authentication, file globs need ListObjectsV2 and writes need multipart upload.^[8] DuckDB's preferred way to configure and authenticate to S3 endpoints is with secrets.^[8] DuckDB implements file globbing on S3 with the ListObjectsV2 call, which allows filesystem-like glob patterns to match multiple files.^[8] DuckDB's read_parquet takes a filename option that adds a column naming the file each row came from.^[8] Keep the filename column when reading many files at once, so that each row can be traced back to the object it came from. (Meridian's view)

The original FTP specification was written by Abhay Bhushan and published on 16 April 1971.^[4] FTP is built on a client–server model, using separate control and data connections between the client and the server.^[4] The SSH File Transfer Protocol was designed by the Internet Engineering Task Force (IETF) as an extension of the Secure Shell protocol (SSH) version 2.0.^[9] SFTP is not FTP run over SSH, but a new protocol designed from the ground up by an IETF working group.^[9] An SFTP client's capabilities beyond transfer include resuming interrupted transfers, directory listings and remote file removal.^[9]

Compared with the alternatives

Object storage was not designed to replace network-attached file access and sharing, and does not support the locking and sharing mechanisms needed to keep a single, accurately updated version of a file.^[3] Object stores differ from key–value stores in attaching a set of attributes to each piece of data and in being optimised for large amounts of data, where a key–value store expects small values.^[3] Treat a bucket as a place where whole files are published and fetched, not as a shared drive that several jobs edit at once. (Meridian's view)

Sources

1. [1]httpfs Extension for HTTP and S3 Support – DuckDBdocumentation
2. [2]Amazon S3 - Wikipediasecondary source
3. [3]Object storage - Wikipediasecondary source
4. [4]File Transfer Protocol - Wikipediasecondary source
5. [5]What is Amazon S3? - Amazon Simple Storage Servicedocumentation
6. [6]Organizing objects in the Amazon S3 console by using folders - Amazon Simple Storage Servicedocumentation
7. [7]ListObjectsV2 - Amazon S3documentation
8. [8]S3 API Support – DuckDBdocumentation
9. [9]SSH File Transfer Protocol - Wikipediasecondary source