Skip to main content
Version: Next

IMAP Data Connector

The IMAP Data Connector enables federated SQL query across emails stored in an IMAP email server.

datasets:
  - from: imap:[email protected]
    name: emails
    params:
      imap_password: ${secrets:IMAP_PASSWORD}

Schema​

Field NameData TypeNullableDescription
dateTimestamp(Millisecond, None)NoThe date and time when the email was sent, as milliseconds since the Unix epoch.
subjectUtf8YesThe subject line of the email.
fromList<Utf8>YesThe sender(s) of the email.
toList<Utf8>YesThe primary recipient(s) of the email.
ccList<Utf8>YesThe carbon copy recipient(s) of the email.
bccList<Utf8>YesThe blind carbon copy recipient(s) of the email.
reply_toList<Utf8>YesThe email address(es) to which replies should be sent.
message_idUtf8YesA unique identifier for the email message.
in_reply_toUtf8YesThe message_id of the email this message is replying to, if applicable.
contentUtf8YesThe raw email body of this message. Not retrieved when acceleration is disabled.

If a MIME-encoded value is retrieved for a field, it is not decoded and the MIME-encoded value is returned in SQL queries.

Most fields are optional, and depend on the implementation of the specific IMAP server being connected to. For example, the IMAP RFC specifies the message_id field SHOULD be supplied but the field is an optional field.

For more information, refer to the IMAP RFC 2822 - Section 3.6.

Retrieving email body contents​

When the IMAP Data Connector is used without acceleration, the email body will not be retrieved - only header/subject values. To load the email body contents, specify an acceleration:

datasets:
  - from: imap:[email protected]
    name: emails
    params:
      imap_password: ${secrets:IMAP_PASSWORD}
    acceleration:
      enabled: true

With an acceleration enabled, the content field will be populated with the complete email body including headers, without any decoding applied. This field could be used for post-processing the email, like retrieving custom header values or decoding MIME-encoded content.

Limitations
  • Email attachments are currently not parsed from the email body into separate dataset fields. To read email attachments, parse the multipart encodings from the content field.

Query performance​

Filters on the date column are pushed down to the IMAP server as a SEARCH command, so a query or refresh transfers only the matching messages instead of the whole mailbox.

  • Supported filter shapes. A comparison of date against a timestamp or date literal (>, >=, <, <=, =, with the column on either side) and conjunctions (AND) of those. Lower bounds become SENTSINCE, upper bounds SENTBEFORE.
  • Everything else scans the full mailbox. A filter on any other column, a disjunction (OR), !=, or a non-temporal literal contributes no SEARCH criteria, and the connector fetches every message and filters locally, exactly as before.
  • Server-side matching is a superset. IMAP SEARCH compares the calendar day of the Date: header, disregarding time and timezone (RFC 3501 §6.4.4), so each bound is widened by a day and Spice re-applies the predicate exactly on the returned rows. Results are identical to an unpushed scan; only the bytes transferred change.
  • Very large match sets fall back to a full fetch. When the set of matching messages is too large to express compactly as an IMAP identifier set, the connector fetches the whole mailbox instead. The filters are applied above the scan either way, so results are unaffected.

To make an acceleration refresh incremental rather than re-reading the mailbox each cycle, set time_column: date so that refresh_mode: append and refresh_data_window generate a date predicate for the connector to push down:

datasets:
  - from: imap:[email protected]
    name: emails
    time_column: date
    params:
      imap_password: ${secrets:IMAP_PASSWORD}
    acceleration:
      enabled: true
      refresh_mode: append
      refresh_check_interval: 10m

Scans also only request the raw message body when it is needed to populate content — that is, when acceleration is enabled (see Retrieving email body contents). A dataset without acceleration transfers envelopes only, and never the MIME parts and attachments of every message. Every body section is requested with .PEEK, so querying a dataset never marks messages as read (\Seen) on the server.

Configuration​

from​

The from field must contain the email address for the mailbox to connect to. For example, [email protected], or [email protected].

name​

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: imap:[email protected]
    name: emails
    params: ...
SELECT COUNT(*) FROM emails;
+----------+
| count(*) |
+----------+
| 1234     |
+----------+

The dataset name cannot be a reserved keyword.

params​

The IMAP connector supports the following connection and authentication parameters:

Parameter NameDescription
imap_usernameOptional. The username to use for the IMAP connection. Defaults to the value of the from: mailbox field.
imap_passwordRequired. The password to use for the IMAP connection, in plaintext authentication mode.
imap_hostOptional. The host or IP address of the IMAP server to connect to. Not required for known connections like Outlook or Gmail.
imap_portOptional. The port of the IMAP server to connect to. Defaults to 993.
imap_mailboxOptional. The mailbox to read mail from. Defaults to INBOX, the standard email inbox.
imap_ssl_modeOptional. The IMAP SSL mode to use. Defaults to auto, permitted values of tls, starttls, disabled or auto.

Examples​

Basic example​

datasets:
  - from: imap:[email protected]
    name: emails
    params:
      imap_host: mail.example.com
      imap_password: ${ secrets:IMAP_PASSWORD }

Secrets​

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook​