CREATE EXTERNAL TABLE

Creates an external table that references data files at a storage location, enabling SQL queries without copying data into a managed Delta table.

Category: data-loadingDeltaForge extension

Syntax

CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] <table>
  LOCATION '<path>' [USING <format>]
  [(<col1> <type1>, ...)] [OPTIONS (<key>=<val>, ...)]

Description

## Overview CREATE EXTERNAL TABLE registers a read-only table backed by external data files at a specified storage location. The data remains at its original location and is not copied or converted. Queries against an external table read directly from the source files, with predicate pushdown and projection pushdown applied where the format supports it. External tables are the primary mechanism for querying data in non-Delta formats (CSV, JSON, Parquet, Avro, ORC, XML, Excel, HL7, EDI, FHIR, Protobuf, Iceberg) without first ingesting it into a managed Delta table, and for the seventeen subsurface and energy formats (SEG-Y, SEG-D, UKOOA, LAS, DLIS, LIS, WITSML, PRODML, RESQML, ZMAP+, GRDECL, ECLIPSE, ECLIPSEDECK, BINGRID, Shapefile, GeoJSON, GeoTIFF), which are read where they lie rather than exported to CSV first. ## Schema Inference vs. Explicit Schema When no column definitions are provided, the engine reads a sample of files from the source location and infers the schema. The inferred schema includes column names and data types derived from the data. For CSV files, column names come from the header row (if header = true) or are auto-generated (column_0, column_1, ...). For JSON, column names are derived from object keys with configurable flattening rules. For binary columnar formats (Parquet, Avro, ORC), the embedded schema metadata is used directly. Specifying an explicit schema overrides inference entirely. This is recommended when: - Source files have no header row - Type inference produces incorrect types (e.g., numeric strings inferred as integers) - Only a subset of columns is needed ## External vs. Managed Tables An external table references data at a location you control. DeltaForge does not manage the lifecycle of the underlying files. DROP EXTERNAL TABLE (without WITH FILES) only removes the metadata registration; the source files remain untouched. A managed Delta table (created with CREATE DELTA TABLE) stores data in Delta format under the engine's control, with full transactional guarantees, schema evolution, time travel, and merge support. | Aspect | External Table | Managed Delta Table | |---------------------|--------------------------|----------------------------| | Data location | User-controlled | Engine-managed | | Format | Any supported format | Delta Lake only | | Transactions | No | Yes (ACID) | | Time travel | No | Yes | | Schema evolution | No | Yes | | Write support | Read-only | Full DML | ## Supported Formats | Format | USING Value | Schema Source | Predicate Pushdown | |-----------|-----------------|------------------------|--------------------| | CSV | CSV | Header row or explicit | Row-level filter | | JSON | JSON, NDJSON | Key inference + flatten| Row-level filter | | Parquet | PARQUET | Embedded metadata | Yes (row groups) | | Avro | AVRO | Embedded schema | Row-level filter | | ORC | ORC | Embedded metadata | Yes (stripes) | | XML | XML | Tag inference | Row-level filter | | Excel | EXCEL, XLSX, etc| Header row or explicit | Row-level filter | | HL7 | HL7, HL7V2 | Segment/field mapping | Row-level filter | | EDI | EDI, X12, etc | Segment mapping | Row-level filter | | FHIR | FHIR, FHIR4, etc| Resource schema | Row-level filter | | Protobuf | PROTOBUF, PB | Descriptor inference | Row-level filter | | Iceberg | ICEBERG | Iceberg metadata | Yes (manifests) | | Delta | DELTA | Transaction log | Yes (file stats) | | Subsurface| see table below | The file's own header | Row-level filter | ## The subsurface and energy family Fifteen upstream energy formats read natively, so a subsurface table IS the file on disk rather than a CSV export of it. The row shape is per-format and deliberate, because these formats do not describe the same kind of thing: | Format | USING Value | One row is | |-----------|------------------------|--------------------------------------------------------| | SEG-Y | SEGY, SGY, SEG-Y | One trace; header as scalars, samples as FixedSizeList | | SEG-D | SEGD, SGD, SEG-D | One trace, as SEG-Y, from a field record | | UKOOA | UKOOA, P190, SEGP1 | One navigation position record | | LAS | LAS, LAS2, WELLLOG | One depth step; one column per curve | | DLIS | DLIS, RP66 | One frame; one column per channel | | LIS | LIS, LIS79 | One frame; one column per channel | | WITSML | WITSML | One object from the XML document (carrier profile) | | PRODML | PRODML | One object from the XML document (carrier profile) | | RESQML | RESQML, EPC | One object part of an .epc package | | ZMAP+ | ZMAP, ZMAP+ | One grid node, with its real-world coordinate | | GRDECL | GRDECL | One grid cell; one column per property keyword | | ECLIPSE | ECLIPSE, EGRID, UNRST | One array element (keyword, occurrence, index) | | ECLIPSEDECK| ECLIPSEDECK, DATADECK | One deck item (section, keyword, record, item) | | BinGrid | BINGRID, P611 | One bin grid position, in bin and map coordinates | | Shapefile | SHAPEFILE, SHP | One shape; geometry as WKB, .dbf attributes alongside | | GeoJSON | GEOJSON | One feature (carrier profile over the JSON engine) | | GeoTIFF | GEOTIFF, COG, TIF | One image directory, describing the raster | Three of these are carrier profiles rather than separate parsers: WITSML and PRODML are XML dialects and GeoJSON is a JSON dialect, read by the XML and JSON engines under a profile that knows the dialect's object model. Naming the dialect is what gets domain-shaped columns without hand-writing XPath. LAS reads versions 1.2, 2.0 and 3.0. A LAS 3.0 file is several tables sharing one well header, each with its own grain, so the `section` option selects which topic the table reads: `log` (the default), `core`, `tops`, `drilling`, `inclinometry`, `test`, `perforation`, or whatever else the file declares. LAS 1.2 and 2.0 have a single implicit topic and ignore the option. ECLIPSE binary output is read in long form (`keyword`, `occurrence`, `index`, `value`) because an EGRID, an INIT and a UNRST hold completely different things and one reader serves all three. `occurrence` separates report steps: the tenth PRESSURE array in a restart file is `occurrence = 9`, so a time series is a filter rather than a separate file. ## Cloud Storage For cloud paths (s3://, abfss://, gs://), the engine resolves object store credentials from the session's permission token. The object store is configured once during table creation and reused for all subsequent queries. If credentials are not available at creation time, the table is registered in catalog-only mode and becomes queryable once credentials are provided. ## Catalog Persistence When a catalog router is configured, external table metadata (name, location, format, options) is persisted to the control plane catalog. This allows the table to be rediscovered and re-registered automatically when a compute node starts or when the catalog is synced. ## Result Columns | Column | Type | Description | |--------------|--------|-------------| | column_name | STRING | Inferred or declared column name | | column_type | STRING | Arrow data type of the column | The result set lists all columns in the newly created external table.

Parameters

Name	Type	Description
`table`		Specifies the table name. Accepts unqualified names (e.g., events), two-part names (schema.events), or fully qualified three-part names (zone.schema.events). For three-part names, the zone and schema must already exist.
`location`		Specifies the storage path or URI pointing to the external data files. Supports local paths (file:///data/files/ or /data/files/), Amazon S3 (s3://bucket/prefix/), Azure Blob Storage and ADLS Gen2 (abfss://container@account.dfs.core.windows.net/path/), and Google Cloud Storage (gs://bucket/prefix/). Tilde expansion is supported for local paths (~/data/). For directories, all matching files are included.
`format`		Specifies the file format of the data at the source location, provided via the USING or FORMAT keyword. Accepted values: CSV, JSON (also NDJSON, JSONL), PARQUET, AVRO, ORC, XML, EXCEL (also XLS, XLSX, XLSM, XLSB, ODS), HL7 (also HL7V2, V2M), EDI (also X12, EDIFACT, TRADACOMS), FHIR (also FHIR4, FHIR5), PROTOBUF (also PROTO, PB), ICEBERG, DELTA. The subsurface and energy family adds: SEGY (also SGY, SEG-Y), SEGD (also SGD, SEG-D), UKOOA (also P190, P1/90, SEGP1), LAS (also LAS2, WELLLOG), DLIS (also RP66), LIS (also LIS79), WITSML, PRODML, RESQML (also EPC), ZMAP (also ZMAP+), GRDECL, ECLIPSE (also EGRID, UNRST, SMSPEC), ECLIPSEDECK (also ECLIPSE_DECK, ECLIPSE_DATA, DATADECK), BINGRID (also P611, P6), P2NAV (also P211, P2), SHAPEFILE (also SHP), GEOJSON, GEOTIFF (also COG, TIF). When omitted, the format is inferred from the file extension. If the extension is ambiguous, CSV is assumed. Extensions a dialect shares with its carrier (.json, .ndjson, .xml, .txt, .dat) are never inferred as a subsurface format, so GEOJSON, WITSML, PRODML, UKOOA and ZMAP must be named explicitly or detected by DISCOVER, which reads the file.
`schema`		Specifies an explicit schema as a parenthesized, comma-separated list of column name and data type pairs. Example: (id BIGINT, name STRING, created_at TIMESTAMP). When omitted, the engine reads sample data from the source location and infers the schema automatically. Explicit schemas are useful when source files lack headers or when type inference produces undesirable types.
`options`		Specifies format-specific and provider options as a parenthesized list of key = value pairs. Common options include: json_flatten_config (JSON flattening rules), file_metadata (system column injection), recursive (scan subdirectories), file_filter (glob pattern for file selection), batch_size (rows per read batch), max_rows (limit total rows), preserve_column_names (prevent name normalization). For CSV: header, delimiter, quote, escape, comment, null_value, skip_rows, trim_whitespace, encoding, infer_schema_rows. The subsurface and energy formats share recursive, batch_size, max_rows, file_metadata, file_filter and split_target_bytes, and add their own: SEGY takes long_form, include_samples, samples and sample_format; SEGD takes include_samples and samples; LAS takes section (which LAS 3.0 topic to read), include_well_header, include_parameters, well_prefix and parameter_prefix; DLIS takes frame; UKOOA takes dialect and record_types; ZMAP takes include_null_nodes; GRDECL takes keywords and include_inactive; ECLIPSE takes keywords; ECLIPSEDECK takes sections, keywords, follow_includes and max_include_depth; BINGRID takes record_types and bin_grid_slot; P2NAV takes record_classes and record_tags; SHAPEFILE takes include_geometry and include_attributes; RESQML takes object_types and include_xml. LIS and GEOTIFF take no format-specific keys, and WITSML, PRODML and GEOJSON are carrier profiles read by the XML and JSON engines, so they take their carrier's options rather than any of their own. Values must be single-quoted strings.

Examples

-- Create an external table over CSV files on S3
CREATE EXTERNAL TABLE bronze.raw.orders
  USING CSV
  LOCATION 's3://data-lake/orders/'
  OPTIONS (header = 'true', delimiter = '|');

-- Create an external table with explicit schema
CREATE EXTERNAL TABLE staging.transactions (
    txn_id BIGINT,
    amount DECIMAL(10,2),
    currency STRING,
    txn_date TIMESTAMP
  )
  USING PARQUET
  LOCATION '/data/parquet/transactions/';

-- Create a JSON external table with flattening configuration
CREATE EXTERNAL TABLE IF NOT EXISTS bronze.raw.customers
  USING JSON
  LOCATION '/data/json/customers/'
  OPTIONS (
    json_flatten_config = '{
      "root_path": "$",
      "include_paths": ["$.id", "$.email", "$.name"],
      "max_depth": 2,
      "separator": "_",
      "infer_types": true
    }',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
  );

-- Create an external table over ORC files with system columns
CREATE EXTERNAL TABLE IF NOT EXISTS warehouse.orc_data.inventory
  USING ORC
  LOCATION 'abfss://raw@storageacct.dfs.core.windows.net/inventory/'
  OPTIONS (
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
  );

-- Replace an existing external table with a new location
CREATE OR REPLACE EXTERNAL TABLE analytics.web.clickstream
  USING JSON
  LOCATION 'gs://analytics-bucket/clickstream/2024/';

-- Create an external table over XML files
CREATE EXTERNAL TABLE IF NOT EXISTS bronze.raw.product_catalog
  USING XML
  LOCATION '/data/xml/products/'
  OPTIONS (
    recursive = 'true',
    file_filter = '*.xml'
  );

Pitfalls

When no USING clause is provided and file extensions are absent or ambiguous, the format defaults to CSV. This can cause silent schema misinterpretation when pointing at JSON or binary files.
Schema inference requires reading sample data from the source location. For large directories or slow cloud storage, this adds latency to the CREATE statement. Providing an explicit schema avoids this cost.
External tables are read-only. INSERT, UPDATE, DELETE, and MERGE operations are not supported. To write data, first load it into a managed Delta table with COPY INTO or CREATE TABLE AS SELECT.
OR REPLACE drops and re-registers the table in the DataFusion session. Any cached query plans referencing the old table are invalidated. Active queries may fail if the table is replaced mid-execution.
OPTIONS values must be single-quoted strings, even for boolean and numeric values. For example: header = 'true', not header = true.
CREATE EXTERNAL TABLE resolves the LOCATION at creation time: it lists the files there and, when no explicit column list is given, reads a sample to infer the schema. The path must therefore exist and be readable when the statement runs; an absent local or accessible cloud path fails with 'Path does not exist'. When the data is produced by an upstream step such as INVOKE API ENDPOINT, create the external table AFTER that step has landed files, not before. An explicit column list skips inference but not the file listing, so it does not lift the existence requirement. The one deferral is a cloud path whose credentials are not yet available, which registers in catalog-only mode (see Cloud Storage).
A subsurface dialect that shares its carrier's extension is never inferred from the extension alone. GeoJSON is .json; WITSML, PRODML and RESQML are .xml; UKOOA and ZMAP+ are routinely .txt or .dat. Inferring those would reroute every ordinary JSON and XML table into a subsurface reader, so they must be selected by an explicit USING clause or by DISCOVER, which reads the file rather than its name.
LAS 3.0 files carry several tables at different grains under one well header. Without the `section` option the table reads the log curves only, and the core, tops, drilling, inclinometry, test and perforation topics are skipped. Reading one of those means a second external table over the same file with `section` set to that topic.
The subsurface readers refuse ambiguity rather than guessing. LIS decodes only representation codes 56, 68, 73 and 79 because the published layouts for the rest conflict, and SEG-D decodes only the demultiplexed format codes. A refused file names the code it could not decode; a silently mis-decoded log curve would be worse.