4.2 Direct File SQL, Save Modes, and Persistent Layout

SQL without a registered table

A file path can be queried through a supported data-source provider without first saving a persistent catalog table. Spark SQL uses the provider name followed by a dot and a backtick-delimited path. This is an identifier syntax, not a quoted string literal in the FROM clause. It is useful for examining an export or applying a one-off transformation to data already stored in a supported format.

SELECT region, SUM(amount) AS revenue
FROM parquet.`/data/orders`
GROUP BY region;

SELECT * FROM orc.`/data/events_orc`;
SELECT * FROM json.`/data/events_json`;
SELECT * FROM csv.`/data/events_csv`;
SELECT value FROM text.`/data/application_logs`;
SELECT * FROM delta.`/data/delta_orders`;

These examples assume accessible paths and installed providers. Delta Lake is a transactional table format built on data files and a log; plain Apache Spark needs the Delta dependency/configuration. Do not read a Delta table as raw Parquet and assume that deleted, replaced or uncommitted files have been excluded correctly. A Delta reader resolves the log-defined snapshot.

Parsing is part of correctness

A direct CSV query uses default reader options unless an options-aware relation is defined. A header can otherwise become a data row and columns can appear as _c0, _c1 and so on. CSV values default to strings unless type inference or a supplied schema changes them. JSON typically infers its schema; line-delimited JSON differs from a multiline JSON document. Text exposes each line through a string value column, rather than interpreting a CSV or JSON payload automatically.

Use a temporary data-source view when column names, types or reader options matter:

CREATE OR REPLACE TEMPORARY VIEW csv_orders (
    order_id BIGINT, region STRING, amount DOUBLE
)
USING csv
OPTIONS (path '/data/orders_csv', header 'true', mode 'FAILFAST');

SELECT region, COUNT(*) AS n, SUM(amount) AS revenue
FROM csv_orders
GROUP BY region;

Equivalent Python code can use spark.read.schema(...).option("header", True).csv(path) followed by createOrReplaceTempView(). The view registers a queryable relation; it does not copy source rows into driver memory or persist a new table. For permissive parsing, explicitly inspect malformed-record and null rates rather than treating successful execution as proof of valid input.

Four write modes

cleaned = spark.sql("SELECT order_id, region, amount FROM csv_orders WHERE amount >= 0")
(cleaned.write.format("parquet")
    .mode("overwrite")
    .partitionBy("region")
    .save("/data/clean_orders"))

The action writes distributed output files. It does not promise one file per region, and overwrite scope depends on provider, table/path operation and partition-overwrite settings. A static overwrite can remove more data than the incoming partition subset; a dynamic partition overwrite policy differs. Name the intended scope before choosing an option. Delta provides transactional commits, but its overwrite and schema policies still need deliberate configuration.

Three meanings often confused

Execution partitions are the units processed by tasks. repartition(8, "region") changes row distribution and normally introduces a shuffle. Directory partitioning via write.partitionBy("region") organizes persisted paths such as region=north/. It can enable partition pruning on a later filtered read. It does not mean a single executor permanently owns north, or guarantee one directory equals one task.

Sorting also has distinct scopes. orderBy establishes a global result ordering, often using range exchange and local sorts. sortWithinPartitions orders each current execution partition and does not establish a global order across all output files. A later query of a table has no guaranteed presentation order without its own ORDER BY, even if files were sorted when written.

Persistent tables with partitioning and sorting

(cleaned.write.format("parquet")
    .mode("overwrite")
    .partitionBy("region")
    .bucketBy(8, "order_id")
    .sortBy("order_id")
    .saveAsTable("orders_layout_demo"))

For a compatible Spark catalog/provider this records table metadata, directory partitions, hash buckets and within-bucket sorting. bucketBy/sortBy in this writer API require saveAsTable, not a generic path save. Bucket count is a logical layout parameter, not a guaranteed physical file count after multiple writers or appends. Delta and Unity Catalog do not universally support these legacy bucket APIs; verify provider capabilities.

A compatible later join can reuse recognized bucket distribution and avoid an exchange, while a mismatch may still need a shuffle or sort. The actual plan is the evidence. Excessive directory cardinality can produce many tiny files: partitioning by near-unique order_id usually makes poor directories, even though it could be a useful join/bucket key in a supported layout.

For a scenario, ask whether the requirement is to read a path, parse its records, reduce scanned directories, organize compatible joins, or display ordered results. Each requires a different API decision. See load/save and direct-file SQL and CSV parsing.