Spark 3: Consider providing better support for path-based tables

[aokolnychyi]

In Spark 3, support for path-based tables is limited. In particular, I don't see a way to create a Hadoop table at a given location through Spark. Users have to use the Iceberg API for that as we use HadoopTables only for reading.

I see a lot of use cases with no metastore where tables are persisted in a location. Usually, these are HDFS use cases. While we can leverage HadoopCatalog for such cases, it has its own drawbacks: list operations to find a table and what is even more important it requires a special layout. The latter point is important as we cannot use HadoopCatalog for path-based tables that were migrated to Iceberg. I want Iceberg to support migration of path-based as well as metastore-based tables through SQL extensions.

I'd consider adding support to our Spark catalogs to create/load a table using a table path as an identifier. Under the hood, it will use HadoopTables.

For example, CREATE TABLE `path/to/table` USING iceberg or SELECT * FROM `path/to/table` WHERE pred.

[aokolnychyi]

We will have to figure out caching and a few other things but I want to discuss whether this makes sense or not.

@rdblue @rdsr @prodeezy @RussellSpitzer @jerryshao any thoughts on this?

[RussellSpitzer]

Couldn't we use the existing Create table to do something like

CREATE TABLE "name" USING iceberg OPTIONS (path = "x")

Or are we trying to keep from ever naming the table representing the directory?

As for the select, I think that sounds reasonable as long as those kind of strings with slashes can be passed through easily. I'm assuming we would just branch off in the Catalog load table method?

I'm sorry I'm still a bit new here, so I'm not fully up to date on what's going on here. But it seems as though we just want a way of treating a directory as a table?

[rdblue]

I was also thinking about adding support for identifiers with a path. We could use that to delegate from IcebergSource to a catalog, which would the bring support for all of the SaveMode options in the v1 DataFrameWriter. Since that write path is very confusing and often broken, I think adding that is a very minor benefit.

I think there may already be support for tables that are identified using ```iceberg.`fs:/path/to/table````. That works for v1 sources, like Parquet. Russell's option could work as well.

Isn't the main blocker that we need an atomic operation? HadoopTableOperations works for HDFS tables, but any other file system, including file:, needs something for coordination. I'm not sure what that would be for path-based tables.

[aokolnychyi]

CREATE TABLE "name" USING iceberg OPTIONS (path = "x")

@RussellSpitzer, create table statements are delegated to either HadoopCatalog or HiveCatalog in Iceberg. The example above can be processed only by HiveCatalog as we cannot set a table location in HadoopCatalog. While HiveCatalog allows arbitrary table locations, it requires HMS. While HadoopCatalog doesn't need HMS, it requires a special layout and does list operations. We have one more API to work with Iceberg tables: HadoopTables. As opposed to HadoopCatalog, the location can be arbitrary.

I see a lot of cases when people just refer to a table by location. While we can load such tables, we cannot create them through Spark and there is no SQL support. I wonder if there is something we could do differently and provide better user experience with tables identified by path.

I think there may already be support for tables that are identified using ```iceberg.`fs:/path/to/table````.

@rdblue, I actually forgot about the SQL on files syntax. Unfortunately, it works only with built-in v1 internal file sources. Ideally, we should see whether we can enable support for such syntax in all V2 data sources. Even if we do so, this will only address query support. Users will have to call Iceberg APIs to create tables. An alternative is to interpret path identifiers in our Spark catalogs. For example, we could support iceberg_spark_catalog.`path/to/table/` syntax.

Isn't the main blocker that we need an atomic operation? HadoopTableOperations works for HDFS tables, but any other file system, including file:, needs something for coordination. I'm not sure what that would be for path-based tables.

I'd just use HadoopTables and document the limitations.

[rdblue]

I'd just use HadoopTables and document the limitations.

That sounds dangerous. I would really rather not provide an easy way to have tables that work most of the time, but fail in ways that are hard to track down. I think it would be fine to build a catalog that supports this, but I don't think that we should make a recommended way to interact with Iceberg.

[aokolnychyi]

I think it would be fine to build a catalog that supports this

I was referring to a catalog as one option. Shouldn't it use HadoopTables API internally, though?

Then we can support the following use cases:

CREATE TABLE catalog_name.`path/to/table` USING iceberg
SELECT * FROM catalog_name.`path/to/table` WHERE predicate
SELECT * FROM catalog_name.`path/to/table`.snapshots

[aokolnychyi]

I think the current plan is to support loading path-based tables in all Spark catalogs by using HadoopTables.

[rymurr]

Just a heads up, I have picked this up and have a partial impl which I hope to publish a draft PR of soon.

As per #1306 (comment) it is not clear to me how this will work for filesystems that don't offer atomic primitives. Should we check the uri schema and reject if it isn't supported?