Acquire / database · All operations

Read from a database

Read from a relational or NoSQL database, warehouse or lake.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the sqlite_scanner extension.

Shape of the result
a new table, from no input table
Level
needs a DuckDB extension

The DuckDB step

Attach a SQLite file and query it

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
INSTALL sqlite; LOAD sqlite;
ATTACH '<your_scratch_dir>/src.sqlite' AS src (TYPE sqlite);
CREATE TABLE src.orders AS SELECT order_id, customer, amount FROM orders;
DETACH src;
ATTACH '<your_scratch_dir>/src.sqlite' AS again (TYPE sqlite, READ_ONLY);
CREATE TEMP TABLE result AS
SELECT count(*) AS n, count(amount) AS amounts FROM again.orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 10 AND amounts = 9 FROM result)) THEN 'as expected' ELSE error('the SQLite file did not return the 10 rows written to it') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes src.sqlite itself, in <your_scratch_dir>.
  • It needs the sqlite_scanner extension.
  • The statement checks its own result and raises an error when it is wrong: “the SQLite file did not return the 10 rows written to it”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Export a table in bulk

Unload a whole table or database to files with the database's own bulk export.

Open or close a database connection

Set up, hand on or close the connection that later database steps use.

Read only new or changed rows

Fetch only the rows added or changed since the last run, by a watermark column or change data capture.

Other database operations

Database steps that read no table, such as returning the key of the last inserted record or managing a cluster.

Read a table or query result

Pull the rows of a database table, collection or query result into the pipeline.

What other tools call it

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

Alteryx Designer (3)
Apache DataFusion (1)
  • CREATE EXTERNAL CATALOGread 2026-09-27

    Register a catalog from a catalog provider factory, such as an Iceberg REST catalog

Apache Hop (12)
  • (Experimental) Beam Hive catalog inputread 2026-09-27

    Experimental: read tables registered in an Apache Hive metastore on the Beam engine

  • Beam BigQuery Inputread 2026-09-27

    Read a table or query result from Google BigQuery on the Beam engine

  • Beam Bigtable inputread 2026-09-27

    Read rows from Google Cloud Bigtable on the Beam engine

  • Bulk load from MySQL into fileread 2026-09-27

    Export a MySQL table to a text file with SELECT INTO OUTFILE

  • Cassandra Inputread 2026-09-27

    Read rows from a Cassandra table with a CQL query

  • LDAP Inputread 2026-09-27

    Read entries such as users and roles from an LDAP directory

  • MongoDB Inputread 2026-09-27

    Read documents from a MongoDB collection by query or aggregation pipeline

  • Neo4j Cypherread 2026-09-27

    Run a Cypher statement against Neo4j per row or batch, reading or writing

  • Neo4j Cypher builderread 2026-09-27

    Compose and run a Cypher statement from a list of node and relationship operations

  • Redis Inputread 2026-09-27

    Read string, hash, set or list values from Redis

  • Splunk inputread 2026-09-27

    Run a Splunk search query and return the results as rows

  • Table Inputread 2026-09-27

    Read rows from a database with a SQL query, optionally parameterised by input rows

Apache NiFi (8)
  • CaptureChangeMySQLread 2026-09-27

    Read change data capture events (insert, update, delete) from a MySQL database

  • ExecuteSQLread 2026-09-27

    Run a SQL select over JDBC and emit the result set as Avro

  • ExecuteSQLRecordread 2026-09-27

    Run a SQL select over JDBC and emit the result set through a record writer

  • GenerateTableFetchread 2026-09-27

    Generate SQL select statements that fetch a table in pages, optionally incrementally

  • ListDatabaseTablesread 2026-09-27

    List tables of a database connection, one FlowFile of metadata attributes per table

  • NoSQL, search and analytics store query processors (family)read 2026-09-27

    12 processors, MongoDB, DynamoDB, Elasticsearch, Azure Data Explorer, Splunk: GetMongo, SearchElasticsearch, …

  • QueryDatabaseTableread 2026-09-27

    Fetch rows whose maximum-value columns exceed the values seen before; output Avro

  • QueryDatabaseTableRecordread 2026-09-27

    Fetch rows whose maximum-value columns exceed the values seen before; output via record writer

Apache SeaTunnel (1)
  • Source connectors (family)read 2026-09-27

    80 connectors, one per system: Jdbc, MySQL-CDC, Kafka, S3File, LocalFile, Http, Iceberg, FakeSource, …

AWS Glue (1)
Azure Data Factory (6)
Bruin (2)
DuckDB (1)
  • ATTACH and DETACHread 2026-09-27

    Attach another database file or remote database as a catalog

Duckle (14)
EasyMorph (2)
Ibis (2)
  • DatabaseTableread 2026-09-27

    A named table that lives in a connected backend

  • UnboundTableread 2026-09-27

    Declare a table by name and schema alone, bound to a backend only when executed

KNIME Analytics Platform (26)
OpenRefine (1)
  • Database (SQL)read 2026-09-27

    Load the result of an SQL query from PostgreSQL, MySQL, MariaDB or SQLite, or from a local .db file

Pentaho Data Integration (12)
Polars (3)
  • Unity Catalog client (family)read 2026-09-27

    13 entries: Catalog with list_catalogs, list_namespaces, list_tables, get_table_info, scan_table, plus info classes

  • read_databaseread 2026-09-27

    Read the result of a SQL query through a connection object

  • read_database_uriread 2026-09-27

    Read the result of a SQL query from a database named by URI

Power Query M (2)
RapidMiner Studio (12)
Tableau Prep (3)
  • Built-in connectors (family)read 2026-09-27

    One connector per file type or server; count not stated, the list lives in the product

  • Custom connectorsread 2026-09-27

    Partner-built or Connector SDK connectors for ODBC- and JDBC-based data

  • Virtual Connectionsread 2026-09-27

    Read through a shared virtual connection, a central access point to data

Talend (6)
VisiData (1)
  • Database loaders (family)read 2026-09-23

    8 loaders: sqlite, postgres, rds, mysql, and via the vdsql app (Ibis) bigquery, clickhouse, snowflake, sqlite

Background

A SQL SELECT statement retrieves zero or more rows from one or more database tables or views.[1] The first phase of extract, transform, load (ETL) extracts data from source systems, and relational databases are a common source format.[2]

When to use it

Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.[1] Name an ORDER BY in the read itself whenever a later step depends on which row comes first. (Meridian's view)

Whether to do an operation in the database or outside it can involve a trade-off.[2] Removing duplicates with DISTINCT may be slow in the database, yet if it cuts the rows to be extracted a hundredfold it makes sense to do it there before unloading.[2] For better ETL performance, it may make sense to use a bulk unload instead of querying the database, to reduce the load on the source system.[2] Push filters and column choices into the read when they shrink what has to leave the database, and leave work the database does slowly for after the read. (Meridian's view)

Change data capture (CDC) is a set of software design patterns used to determine and track the data that has changed.[3] Change data capture can be used for incremental update of data loading.[3] One change data capture method uses a column holding each row's time of last change, treating a row as changed when that time is more recent than the last capture.[3] Apache NiFi's QueryDatabaseTable processor fetches the rows whose values in its Maximum Value columns are larger than the previously seen maxima.[4] Read only new or changed rows when a large table is read again on a schedule; read the whole table when it is small enough that a full reread costs little. (Meridian's view)

SQLite does not enforce column types, so a SQLite column declared INTEGER can hold the string 'hello'.[5] When DuckDB reads a SQLite column holding a value that does not fit the column's mapped type, it throws an error, which setting the sqlite_all_varchar option avoids.[5] Expect a weakly typed source to hold values its declared types do not promise, and check the types after the read rather than trusting the declaration. (Meridian's view)

The method

DuckDB's postgres extension reads data directly from a running PostgreSQL database instance, and can load data from PostgreSQL tables into DuckDB tables.[6] DuckDB's postgres extension has a pg_enable_filter_pushdown setting, on by default, that controls whether filter pushdown is used.[6]

In KNIME Analytics Platform, the DB Table Selector node outputs the database connection information together with a database query that defines the data in the database.[7] In KNIME Analytics Platform, the DB Reader node executes its input query in the database and retrieves the result into a KNIME data table.[8] Apache NiFi's QueryDatabaseTable processor streams its results, so arbitrarily large result sets are supported.[4]

Sources

  1. [1]Select (SQL) - Wikipediasecondary source
  2. [2]Extract, transform, load - Wikipediasecondary source
  3. [3]Change data capture - Wikipediasecondary source
  4. [4]QueryDatabaseTable - Apache NiFidocumentation
  5. [5]SQLite Extension – DuckDBdocumentation
  6. [6]PostgreSQL Extension – DuckDBdocumentation
  7. [7]DB Table Selectordocumentation
  8. [8]DB Readerdocumentation