返回资讯中心
外部精选
软件工程
#开发者实时流

Igloo:基于 DuckDB 的自托管数据仓库开源发布

来源标题:Show HN: Igloo – Self-hosted data warehouse built on DuckDB

开源项目 Igloo 发布,这是一个可在用户自有云账户或硬件上运行的 SQL 数据仓库,基于 DuckDB 构建。作者称在 TPC-H 100GB 测试中,其速度是 Snowflake 和 Databricks 的 3 倍,成本低 25 倍。项目需 Docker Compose v2.20 以上,支持任意 Postgres 客户端连接。

Hacker News 最新AbrahamParangi2026/11/11 · 6 天前30 分钟阅读

以下正文同步自 Hacker News 最新,版权归原站所有,已转换为易读排版。

Igloo is an open-source SQL data warehouse that runs in your own cloud account or on your own hardware.

Igloo runs 3 times faster than Snowflake and Databricks at 25 times lower cost, and is faster and lower cost at every warehouse size from X-Small to X-Large. The 3 and 25 are medians over those sizes, on TPC-H at 100 GB in Amazon S3 at list prices, with Igloo on one machine: 32 cores against X-Small and Small, 64 from Medium up. See the benchmark for the method, every result and its caveats.

Screenshots use synthetic data from deploy/single-vm/sql/load-media.sql; the companies in it are invented.

Requires Docker with Compose v2.20 or later.

make up # generates deploy/single-vm/.env with random secrets and starts the stack make psql # SQL shell as the admin user

CREATE TABLE events (id INTEGER, kind VARCHAR, at_time TIMESTAMP); INSERT INTO events VALUES (1, 'signup', now()), (2, 'login', now()); SELECT kind, count(*) FROM events GROUP BY kind;

Any Postgres client can connect to 127.0.0.1:5433 as user igloo with the password IGLOO_ADMIN_PASSWORD from deploy/single-vm/.env (make up generates it; Igloo refuses to create the account with a placeholder such as change-me or fewer than 16 characters):

psql "host=127.0.0.1 port=5433 user=igloo dbname=igloo"

Or open the web console at http://127.0.0.1:8443 and sign in with the same user and password, or fetch results as Arrow over Flight SQL at grpc://localhost:31337:

import adbc_driver_flightsql.dbapi as flightsql

with flightsql.connect("grpc://localhost:31337", db_kwargs={"username": "igloo", "password": "..."}) as conn, conn.cursor() as cur: cur.execute("SELECT * FROM events") df = cur.fetch_df() # or fetch_arrow_table(), or polars.read_database(query, conn)

Try it on real data with the TPC-H benchmark:

make load-tpch # scale factor 1: 6 million lineitem rows make tpch # runs and times all 22 TPC-H queries

On a laptop, the scale factor 1 load takes about 20 seconds and the 22 queries take about 4 seconds in total with warm caches; their results match the official TPC-H answers.

Iceberg tables with CREATE [OR REPLACE] TABLE, CREATE TABLE AS, INSERT, UPDATE, DELETE, MERGE, DROP, schemas, views, comments, and time travel (SELECT * FROM t AT (TIMESTAMP => TIMESTAMP '2026-10-01 00:00')).

Zero-copy clones of tables and schemas: a full copy of production to develop and test against, made in seconds without copying data, with the source's policies; and Iceberg branches and tags of tables.

CREATE OR REPLACE SCHEMA dbt_ana CLONE analytics; CREATE TABLE dev.orders CLONE prod.orders AT (TIMESTAMP => now() - INTERVAL 1 DAY); ALTER TABLE prod.orders CREATE TAG q3_close;

Users, roles and grants: SCRAM-SHA-256 passwords, built-in roles for the whole warehouse, and custom roles with grants on schemas and tables, all managed in SQL.

CREATE USER ana WITH PASSWORD 'a-long-password' ROLE writer; -- admin, writer, reader or none CREATE USER nora WITH PASSWORD 'another-password' ROLE none; CREATE ROLE analysts; GRANT analysts TO nora; GRANT SELECT ON SCHEMA marts TO analysts;

Single sign-on to the web console with Okta, Microsoft Entra ID, Google Workspace or any OpenID Connect provider, with users created at first sign-in (or provisioned with SCIM) and the provider's groups mapped to roles; access tokens that BI tools, dbt and scripts send instead of a password; and service accounts for programs.

GRANT analysts TO SSO GROUP 'Data Team'; CREATE USER dbt_prod TYPE SERVICE ROLE none; CREATE TOKEN ci FOR USER dbt_prod EXPIRES '90d'; -- shown once, stored hashed

Row access and masking policies, and tags: show each team only its clients' rows, and mask emails, device IDs and other identifiers for everyone not allowed to see them, in every statement, the web console included.

CREATE ROW ACCESS POLICY client_rows AS (cid VARCHAR) RETURNS BOOLEAN -> EXISTS (SELECT 1 FROM governance.client_access a WHERE a.client_id = cid AND is_role_in_session(a.role_name)); ALTER TABLE marts.impressions ADD ROW ACCESS POLICY client_rows ON (client_id); CREATE MASKING POLICY mask_pii AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN is_role_in_session('pii_reader') THEN val ELSE sha256(val) END; CREATE TAG pii ALLOWED_VALUES 'email', 'device_id'; ALTER TAG pii SET MASKING POLICY mask_pii; ALTER TABLE marts.impressions ALTER COLUMN email SET TAG pii = 'email';

Delta Sharing, both ways: share tables with clients and partners, who read them with Spark, pandas, Power BI or Tableau through the open protocol, and query partners' shares as external tables, with grants as for any table.

CREATE SHARE acme_reporting; ALTER SHARE acme_reporting ADD TABLE marts.campaign_daily; CREATE RECIPIENT acme; -- returns a one-time activation link GRANT SELECT ON SHARE acme_reporting TO RECIPIENT acme; CREATE EXTERNAL TABLE partners.viewership FROM SHARE tvco_ads.tv.viewing.daily;

Connections to other databases: query PostgreSQL, MySQL and Snowflake databases in place, read-only, and join them with Iceberg tables, with filters pushed down to them and their tables governed by the same grants and policies, or copy them into Iceberg tables with CREATE TABLE AS.

CREATE CONNECTION crm TYPE postgres WITH (host 'crm.example.com', database 'crm', user 'igloo_ro', password '...'); GRANT SELECT ON SCHEMA crm.public TO analysts; SELECT count(*) FROM audiences a JOIN crm.public.customers c ON c.email = a.email;

Erasure of personal data for GDPR and CCPA requests: one statement deletes a person's rows from every table whose columns are tagged with their identifiers, hashed ones included, rewrites the files that held them, expires the snapshots that still reference them, deletes them from object storage and every replica's read cache, redacts the query history and verifies that nothing is left, with a record that shows it but not whose data it was. Data retentions limit how long tables keep old versions of rows.

GRANT ERASE ON ACCOUNT TO privacy_officer; ERASE SUBJECT WHERE pii.email = 'ann@example.com' OR pii.device_id = '6F9619FF-8B86-D011-B42D-00C04FC964FF'; SELECT status, verification FROM igloo.erasure_requests; ALTER TABLE marts.impressions SET (data_retention_days = 30);

Clean rooms with publishers, retailers and TV makers: audience overlap, reach and frequency, and conversion lift on data joined by hashed emails or device IDs, through templates every party approves, with thresholds on every result row, and neither side seeing the other's rows. Partners take part over HTTP with their Delta Sharing token.

ALTER CLEAN ROOM tv ADD TEMPLATE overlap FROM BUILTIN audience_overlap (left => crm.email_sha256, right => exposures.email_sha256, by => crm.segment) RUN BY (agency); RUN CLEAN ROOM tv TEMPLATE overlap;

Query and login history in igloo.query_history and igloo.login_history: every statement and login, kept for 90 days, with each statement's CPU time, bytes and memory.

Data catalog: search tables by name, description, column, tag and owner; owners and descriptions of schemas, tables and columns; size and freshness from the Iceberg metadata; usage and table-level lineage worked out from the query history, external tables and connections' tables included, also sent to OpenLineage tools such as Marquez and DataHub. Each user sees only the tables they may read.

COMMENT ON TABLE marts.daily_spend IS 'Spend per client, campaign and day'; ALTER TABLE marts.daily_spend OWNER TO analytics_eng; SELECT upstream_name, last_seen, last_job FROM igloo.lineage WHERE downstream_name = 'daily_spend';

Loading and scheduling with nothing else to run: COPY INTO loads CSV, TSV, Parquet and JSON drops from a bucket, each file once; pipes load new files as they land; tasks run SQL on a cron or interval schedule in any time zone; materialized views keep a query's result as a table. Everything runs as its owner, and a scaled-to-zero Igloo is started when work is due.

COPY INTO sales FROM 's3://drops/sales/' (FORMAT csv, PATTERN '.*[.]csv[.]gz'); CREATE TASK nightly SCHEDULE '0 6 * * *' TIMEZONE 'America/New_York' AS CREATE OR REPLACE TABLE report AS SELECT day, sum(spend) FROM sales GROUP BY day;

Alerts and notifications to Slack, Microsoft Teams, email and webhooks: alerts run a query on a schedule, as their owner and through their owner's policies, and notify when it returns rows; built-in alerts report failed runs and loads, pipes whose files stopped arriving, long queries, failed-login bursts and maintenance failures. Notifications reach only public addresses over TLS, and webhook URLs are stored encrypted.

CREATE NOTIFICATION INTEGRATION ops_slack TYPE slack URL 'https://hooks.slack.com/services/...' OPERATIONAL_ALERTS = (FAILED_RUNS, IDLE_PIPES = 26); GRANT USAGE ON INTEGRATION ops_slack TO ROLE analysts; CREATE ALERT pacing SCHEDULE '0 9 * * MON-FRI' IF (EXISTS ( SELECT campaign FROM marts.pacing WHERE abs(spend / planned - 1) > 0.2)) THEN NOTIFY ops_slack MESSAGE 'Pacing is off plan for {{campaign}}' WITH RESULT;

Media analytics: HLL and theta sketches that merge into deduplicated reach and audience overlap across campaigns, platforms and days; geometry and H3 functions for DMA, zip code and store-radius reporting; DuckDB's approximate, statistical and time zone functions.

Other engines through an Iceberg REST catalog on port 8181: Spark, Trino and PyIceberg sign in as Igloo users, Igloo's grants decide what they may read and change, their requests go to the query history, and they get storage credentials that last an hour and reach one table's files only.

Workload management: query tags (SET query_tag = 'client:acme'), per-client usage and estimated cost in igloo.usage, per-role concurrency, timeout and memory limits (ALTER ROLE analysts SET max_concurrent_statements = 4), and compute pools that give a client or team compute and a bill of its own.

A locked-down query engine: SQL cannot read the server's files or reach any location except the warehouse and locations you allow.

Automatic table maintenance: small files are compacted, snapshots older than the retention (7 days by default) are expired, and unreferenced files are deleted, with nothing to schedule.

Several replicas behind one endpoint, all reading and writing the same tables; concurrent writes to a table are retried automatically.

Scale to zero: a small gateway stops Igloo's machines or pods when nobody uses them and starts them when a client connects, on AWS, Google Cloud, Azure or Kubernetes.

Distributed execution: a query over a very large table runs in parts on worker machines that Igloo starts when it needs them and stops when they are idle, with the same grants, sandbox and results as on one machine.

Read cache: table files read from object storage are kept on local disk across restarts, so the first queries after a start (or a scale-to-zero resume) do not fetch them again.

Web console at http://127.0.0.1:8443: SQL worksheets, a data browser, a catalog with lineage graphs, query history, tasks and loads, and user and compute administration, signed in with the same users as SQL clients.

Arrow Flight SQL at grpc://localhost:31337: query results as DuckDB's Arrow record batches for ADBC (pandas, polars), pyarrow and the Flight SQL JDBC driver, with the same users, grants, timeouts and history as SQL clients.

Your tools, unchanged: dbt with the standard dbt-postgres adapter (incremental models, snapshots, tests, docs), Metabase, Superset, Power BI, Tableau and Looker through their PostgreSQL connectors, and SQLAlchemy, pandas and polars in notebooks, tested on every release.

Operations basics: health and readiness endpoints, Prometheus metrics, JSON logs, statement timeouts, connection limits, TLS, query cancel.

Where How

One machine (laptop, VM, on-prem server) deploy/single-vm with Docker Compose: make up

Any Kubernetes cluster Helm chart

AWS (EKS, S3, RDS) deploy/aws OpenTofu module

Google Cloud (GKE, GCS, Cloud SQL) deploy/gcp OpenTofu module

Azure (AKS, ADLS Gen2, PostgreSQL Flexible Server)

正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。

打开原文
去社区讨论 →