Skip to Content

ODBC

ODBC

Scan any database that has an ODBC driver — SQL Server, Oracle, DB2, Teradata, Informix or a data source set up on the scanning host.

Category
Databases
Source type
ODBC
Produces
table

ODBC is the interface almost every database ships a driver for. Use this source for one that has no connector of its own here — SQL Server, Oracle, DB2, Teradata, Informix, Firebird, Vertica, ClickHouse, a data source on a mainframe — or when your organisation only allows access through a specific driver.

What you need to connect

  • The database’s ODBC driver, installed where scans and queries run. The Classifyre image ships the unixODBC driver manager and the PostgreSQL driver. Any other driver is the vendor’s to supply: build an image from the Classifyre one, install the driver (and register it in /etc/odbcinst.ini), and use it for the CLI jobs (api.cliJobs.image in the Helm chart).

  • How to reach it, in one of three forms:

    • Server and driver (the usual one): the driver’s name, the host, and optionally the port and database. Classifyre writes the connection string, using each driver family’s own spelling (Server=host,port for SQL Server, DBQ=//host:port/service for Oracle, HostName / Port for DB2, DBCName for Teradata, Host / Port for Spark, Hive and Databricks, Server / Port / Database for the rest). The driver field suggests common names, and takes any other: it must match what the host registered (odbcinst -q -d).
    • Data source name (DSN): a data source defined in the host’s odbc.ini.
    • Connection string: the whole string, for a driver the other two cannot describe.

    More driver settings (TrustServerCertificate=Yes, SSLmode=require) go under Connection → Attributes. They are added after the fields, so one named like a field replaces it.

  • A user and password, stored encrypted. They are added to the connection string as UID and PWD, with any ; or } in them protected. Leave them empty if the data source signs in by itself.

Do not put a password in the connection string or an attribute: only the user and password fields are encrypted.

JDBC drivers are not supported: they need a Java runtime next to every scan. Most databases that ship a JDBC driver also ship an ODBC one.

What Classifyre reads

Tables and views in every catalog and schema the user can see, or only the ones you list. The database’s own system schemas (information_schema, sys, pg_catalog, DB2’s SYS*, Oracle’s SYS, and so on) are skipped.

Discovery uses ODBC’s own catalog calls (SQLTables, SQLColumns, SQLPrimaryKeys, SQLForeignKeys), which every driver answers in its own dialect, so nothing is guessed from an information_schema. Foreign keys become links between the tables they join.

Shared behaviour · SQL databases

One asset per table or view, never one per row. The asset carries the table's structure — database, schema, table name, object type, its columns and their types, and a row-count estimate — and its content is a sample of real rows, formatted so a detector reads actual values rather than a schema dump.

How many rows, and which ones, is entirely up to the sampling strategy. Large tables are paged through by key rather than by OFFSET, so a scan that stops halfway can resume from where it left off instead of re-reading from the top.

Read-only throughout. The connector issues catalog queries and bounded SELECTs. Nothing is written back, and a read-only account is the right account to give it.

Relationships come out of the engine's own catalog: foreign keys are recorded as REFERENCE links — useful, but they move no data, so they never become a lineage hop. See Lineage.

Metadata on every asset

Asset kind · table

FieldTypeAlways presentWhat it is
databasestringYesDatabase or catalog name
table_namestringYesTable name
table_typestringYesObject type (TABLE/VIEW)
schemastringNoSchema name
columnsobject[]NoColumns as {name, type} objects
row_countintegerNoEstimated number of rows
table_formatstringNoStorage format of the table (iceberg, delta, hudi, paimon, lance, native, external, view)
object_typestringNoSource object type
catalogstringNoCatalog the driver reported for the table (absent when the driver has none)
dbms_namestringNoDatabase product behind the driver, as the driver names it (SQL_DBMS_NAME)
qualified_namestringNoThe table as the driver's own SQL names it, quoted and qualified, ready for a FROM clause

A database that has no catalogs (Oracle, DB2, SQLite) shows its tables under the catalog name default.

Worth knowing

  • Rows are limited the way the database does it. The driver names its database, and that picks LIMIT … OFFSET … (PostgreSQL, MySQL, SQLite, Snowflake, Vertica, ClickHouse, Spark) or OFFSET … FETCH (SQL Server, Oracle, DB2). For a database that is not known, Classifyre reads from the start of the table and stops after the rows it needs: correct everywhere, slower for a window that starts deep into a large table. Set Paging to choose by hand.
  • Random sampling needs the database’s random function, known for the engines above. For any other, RANDOM reads the table in its own order.
  • A read-only user is the right user. ODBC has no portable read-only mode, and a scan only ever sends SELECT.
  • Types the driver reports but the client cannot map stop a table’s read with an error naming the type; the other tables are unaffected. Cast the column in a view on the database side.
  • Views have no lineage here. ODBC has no portable way to ask what a view selects from. A source with its own connector (PostgreSQL, MySQL, SQL Server, Oracle) reads it from the catalog.
  • The same table through two sources shares one name. For PostgreSQL, MySQL and SQL Server, a connection string that names the server gives each table the same cross-system identity the engine’s own source gives it, so a scan through ODBC and a scan through the native source meet on one object. A DSN hides the server, so it gets none.

SQL

An ODBC table can be queried with SQL. ODBC is the slowest way in: every query reads the table over the wire, so start with a LIMIT.

Configuration

Beyond the fields below, every source also has the settings shared by all of them: the sampling strategy, the augmentation notebook that enriches assets after extraction, the detectors to run, the scan schedule, and the compute limits for its scan jobs.

Required

Without these, the source will not save.

This section depends on which authentication method you pick — one of the following applies.

Server and driver

FieldTypeRequiredWhat it doesDefault
driverstringYesName of the ODBC driver as it is registered on the scanning host (odbcinst -q -d). Pick a common one or type your own.—
hoststringYesServer name or address (for Oracle: the listener; for Teradata: the DBC name)—
portintegerNoServer port; leave empty for the driver's default—
databasestringNoDatabase to connect to (for Oracle: the service name); leave empty for the user's default—

Data source name (DSN)

FieldTypeRequiredWhat it doesDefault
dsnstringYesName of the data source—

Connection string

FieldTypeRequiredWhat it doesDefault
connection_stringstringYesODBC connection string without the user name and password, e.g. Driver={ODBC Driver 18 for SQL Server};Server=tcp:db.internal,1433;Database=sales—

Secrets

Stored encrypted and never shown again after you save them. See Configuration & Fields.

FieldTypeRequiredWhat it doesDefault
maskedobjectYesAppended to the connection string as UID and PWD. Leave empty when the data source signs in by itself.no extra properties—
passwordstringNoPassword (PWD)—
usernamestringNoUser to sign in as (UID)—

Optional

Everything you can tune. Sensible defaults apply when you leave them alone.

FieldTypeRequiredWhat it doesDefault
optionalobjectNo—no extra properties—
connectionobjectNo—no extra properties—
connection.attributesarrayNoMore driver settings, added to the connection string as NAME=value (for example TrustServerCertificate=Yes, SSLmode=require, Encrypt=yes). They come after the fields above, so one named like a field overrides it. Credentials belong in the user and password fields, which are encrypted.—
connection.attributes[]objectNo—no extra properties—
connection.attributes[].namestringYesSetting name, e.g. SSLmode—
connection.attributes[].valuestringYesIts value—
connection.login_timeout_secondsintegerNoHow long to wait for the database to accept the connectionmin 1, max 60015
connection.pagingenumNoHow rows are limited: auto follows the database the driver reports (LIMIT/OFFSET, or OFFSET/FETCH). client_side reads from the start of the table and stops, for a database that supports neither. Allowed: auto, limit_offset, offset_fetch, client_sideauto
connection.query_timeout_secondsintegerNoTimeout for one query; 0 for none. Not every driver honours it.min 0, max 3600120
extractionobjectNo—no extra properties—
extraction.include_foreign_keysbooleanNoAsk the driver for foreign keys and link the tables they jointrue
scopeobjectNo—no extra properties—
scope.exclude_catalogsarrayNoSkip these catalogs—
scope.exclude_catalogs[]stringNo——
scope.exclude_schemasarrayNoSkip these schemas (system schemas are skipped already)—
scope.exclude_schemas[]stringNo——
scope.include_catalogsarrayNoOnly these catalogs (databases)—
scope.include_catalogs[]stringNo——
scope.include_objectsarrayNoOnly these objects (schema.table, or catalog.schema.table)—
scope.include_objects[]stringNo——
scope.include_schemasarrayNoOnly these schemas—
scope.include_schemas[]stringNo——
scope.include_tablesbooleanNoInclude tablestrue
scope.include_viewsbooleanNoInclude viewstrue
scope.table_limitintegerNoMaximum number of tables to scan per runmin 1—
Last updated on