MisterRaindrop commented on code in PR #1842:
URL: https://github.com/apache/cloudberry/pull/1842#discussion_r3655222183
##########
src/test/regress/sql/lake_table.sql:
##########
@@ -0,0 +1,158 @@
+--
+-- Test lake table DDL: FOREIGN CATALOG, FOREIGN VOLUME, LAKE TABLE
+--
+
+-- Display the lake table catalogs
+\d+ pg_foreign_catalog
+\d+ pg_foreign_volume
+\d+ pg_lake_table
+
+-- Setup: foreign servers for the catalogs and volumes to hang off
+CREATE FOREIGN DATA WRAPPER lake_test_fdw;
+CREATE SERVER lake_test_srv FOREIGN DATA WRAPPER lake_test_fdw;
+CREATE SERVER lake_test_srv2 FOREIGN DATA WRAPPER lake_test_fdw;
+
+-- CREATE FOREIGN CATALOG: TYPE is a required first-class property
+CREATE FOREIGN CATALOG lake_test_cat SERVER lake_test_srv TYPE 'hive' OPTIONS
(uri 'thrift://localhost:9083');
+CREATE FOREIGN CATALOG lake_test_notype SERVER lake_test_srv; --
fail, TYPE is required
+CREATE FOREIGN CATALOG lake_test_cat SERVER lake_test_srv TYPE 'hive';
-- fail, duplicate
+CREATE FOREIGN CATALOG IF NOT EXISTS lake_test_cat SERVER lake_test_srv TYPE
'hive'; -- skip with notice
+-- catalog names are global: the same name on another server is still a
duplicate
+CREATE FOREIGN CATALOG lake_test_cat SERVER lake_test_srv2 TYPE 'hive';
-- fail, duplicate
+CREATE FOREIGN CATALOG IF NOT EXISTS lake_test_cat SERVER lake_test_srv2 TYPE
'hive'; -- skip with notice
+CREATE FOREIGN CATALOG lake_test_bad SERVER no_such_server TYPE 'hive';
-- fail, no server
+SELECT fcname, fctype, fcoptions FROM pg_foreign_catalog WHERE fcname LIKE
'lake\_test%';
+
+-- CREATE FOREIGN VOLUME
+CREATE FOREIGN VOLUME lake_test_vol SERVER lake_test_srv OPTIONS (base_path
's3://bucket/prefix');
+CREATE FOREIGN VOLUME lake_test_vol SERVER lake_test_srv;
-- fail, duplicate
+CREATE FOREIGN VOLUME IF NOT EXISTS lake_test_vol SERVER lake_test_srv;
-- skip with notice
+-- volume names are global: the same name on another server is still a
duplicate
+CREATE FOREIGN VOLUME lake_test_vol SERVER lake_test_srv2;
-- fail, duplicate
+CREATE FOREIGN VOLUME IF NOT EXISTS lake_test_vol SERVER lake_test_srv2;
-- skip with notice
+CREATE FOREIGN VOLUME lake_test_bad SERVER no_such_server;
-- fail, no server
+SELECT fvname, fvoptions FROM pg_foreign_volume WHERE fvname LIKE
'lake\_test%';
+
+-- Object descriptions
+SELECT pg_catalog.pg_describe_object('pg_foreign_catalog'::regclass, oid, 0)
+ FROM pg_foreign_catalog WHERE fcname = 'lake_test_cat';
+SELECT pg_catalog.pg_describe_object('pg_foreign_volume'::regclass, oid, 0)
+ FROM pg_foreign_volume WHERE fvname = 'lake_test_vol';
+
+-- Catalog and volume rows are dispatched to all segments
+SELECT count(DISTINCT gp_segment_id) > 1 AS on_all_segments
+ FROM gp_dist_random('pg_foreign_catalog') WHERE fcname = 'lake_test_cat';
+SELECT count(DISTINCT gp_segment_id) > 1 AS on_all_segments
+ FROM gp_dist_random('pg_foreign_volume') WHERE fvname = 'lake_test_vol';
+
+-- Without a provider extension there is no iceberg table AM
+CREATE LAKE TABLE lake_test_t0 (a int) USING ICEBERG CATALOG lake_test_cat
VOLUME lake_test_vol; -- fail with hint
Review Comment:
@andr-sokolov @leborchuk @yjhjstz I looked into how Crunchy Data Warehouse
(now open-sourced by Snowflake as
[pg_lake](https://github.com/Snowflake-Labs/pg_lake)) handles the catalog —
hope this helps answer the question above.
**TL;DR: Crunchy doesn't specify a catalog at CREATE time, because Postgres
itself acts as the catalog.**
- Managed Iceberg tables are created with plain `CREATE TABLE ... USING
iceberg`. The Iceberg metadata lives inside Postgres and is exposed through an
`iceberg_tables` view compatible with the Iceberg JDBC/SQL catalog protocol, so
Spark / pyiceberg / iceberg-rust connect **to Postgres as the catalog** (JDBC
with Postgres credentials; `catalog_name` = the database name). ([Crunchy
docs](https://docs.crunchybridge.com/warehouse/iceberg))
- I couldn't find any syntax for attaching an external catalog (Glue / HMS /
REST) — the product doesn't seem to have that concept, which would explain why
it doesn't appear in the presentation.
- Storage location is a GUC default plus an optional per-table reloption:
`SET crunchy_iceberg.default_location_prefix TO 's3://...'`, or `CREATE TABLE
... USING iceberg WITH (location = 's3://...')`. With neither set, the
cluster's bundled managed storage is used — so in the demo nothing needs to be
specified at all. @leborchuk's reading ("good for pre-configured database
instances") matches what I found.
One more data point that may be relevant: pg_lake is currently adding
external Iceberg REST catalog support as a **client**
([pg_lake#94](https://github.com/Snowflake-Labs/pg_lake/issues/94)), and that
roadmap mentions handling multiple endpoints/credentials "likely using a
Postgres `SERVER`-like concept" — quite close to what `CREATE FOREIGN CATALOG
... SERVER` does in this PR. So the two designs may be converging from
different starting points.
For reference, the two syntax shapes side by side:
- Plain `CREATE TABLE ... USING iceberg`: the catalog/volume bindings move
into reloptions, e.g. `WITH (catalog 'c', volume 'v')`, with GUC defaults; no
new grammar, and plain `DROP TABLE` applies (the Spark / pg_lake convention).
- `CREATE LAKE TABLE ... USING <format> CATALOG c VOLUME v`: catalog/volume
are first-class clauses with dedicated `CREATE/DROP LAKE TABLE` commands (the
current PR).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
