nastra commented on code in PR #9878: URL: https://github.com/apache/iceberg/pull/9878#discussion_r1516115104
########## docs/docs/spark-ddl.md: ########## @@ -566,3 +566,118 @@ Tags can be removed via the `DROP TAG` sql ```sql ALTER TABLE prod.db.sample DROP TAG `historical-tag` ``` + +### Iceberg views in Spark + +Iceberg views are a [common representation](../../view-spec.md) of a SQL view that aim to be interpreted across multiple query engines. +This section covers how to create and manage views in Spark using Spark 3.4+ (earlier versions of Spark are not supported). + +!!! note + + All the SQL examples in this section follow the official Spark SQL syntax: + + * [CREATE VIEW](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-create-view.html#create-view) + * [ALTER VIEW](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-alter-view.html) + * [DROP VIEW](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-drop-view.html) + * [SHOW VIEWS](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-views.html) + * [SHOW TBLPROPERTIES](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-tblproperties.html) + * [SHOW CREATE TABLE](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-create-table.html) + + +#### Creating a view + +Create a simple view without any comments or properties: +```sql +CREATE VIEW <viewName> AS SELECT * from <tableName> +``` + +Using `IF NOT EXISTS` prevents the SQL statement from failing in case the view already exists: +```sql +CREATE VIEW IF NOT EXISTS <viewName> AS SELECT * from <tableName> +``` + +Create a view with a comment, including aliased and commented columns that are different from the source table: +```sql +CREATE VIEW <viewName> (ID COMMENT 'Unique ID', ZIP COMMENT 'Zipcode') + COMMENT 'View Comment' + AS SELECT id, zip FROM <tableName> +``` + +#### Creating a view with properties + +Create a view with properties using `TBLPROPERTIES`: +```sql +CREATE VIEW <viewName> + TBLPROPERTIES ('key1' = 'val1', 'key2' = 'val2') + AS SELECT * from <tableName> +``` + +Display view properties: +```sql +SHOW TBLPROPERTIES <viewName> +``` + +#### Dropping a view + +Drop an existing view: +```sql +DROP VIEW <viewName> +``` + +Using `IF EXISTS` prevents the SQL statment from failing if the view does not exist: +```sql +DROP VIEW IF EXISTS <viewName> +``` + +#### Replacing a view + +Update a view's schema, its properties, or the underlying SQL statement using `CREATE OR REPLACE`: +```sql +CREATE OR REPLACE <viewName> (updated_id COMMENT 'updated ID') + TBLPROPERTIES ('key1' = 'new_val1') + AS SELECT id FROM <tableName> +``` + +#### Setting and removing view properties + +Set the properties of an existing view using `ALTER VIEW ... SET TBLPROPERTIES`: +```sql +ALTER VIEW <viewName> SET TBLPROPERTIES ('key1' = 'val1', 'key2' = 'val2') +``` + +Remove the properties from an existing view using `ALTER VIEW ... UNSET TBLPROPERTIES`: +```sql +ALTER VIEW <viewName> UNSET TBLPROPERTIES ('key1', 'key2') +``` + +#### Showing available views + +The below SQL will list all views in the currently set namespace (via `USE <namespace>`): +```sql +SHOW VIEWS +``` + +List all available views in the defined catalog and/or namespace using one of the below variations: +```sql +SHOW VIEWS IN <catalog> +``` +```sql +SHOW VIEWS IN <namespace> +``` +```sql +SHOW VIEWS IN catalog.namespace +``` + +#### Showing the CREATE statement of a view + Review Comment: makes sense, updated ########## docs/docs/spark-ddl.md: ########## @@ -566,3 +566,118 @@ Tags can be removed via the `DROP TAG` sql ```sql ALTER TABLE prod.db.sample DROP TAG `historical-tag` ``` + +### Iceberg views in Spark + +Iceberg views are a [common representation](../../view-spec.md) of a SQL view that aim to be interpreted across multiple query engines. +This section covers how to create and manage views in Spark using Spark 3.4+ (earlier versions of Spark are not supported). + +!!! note + + All the SQL examples in this section follow the official Spark SQL syntax: + + * [CREATE VIEW](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-create-view.html#create-view) + * [ALTER VIEW](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-alter-view.html) + * [DROP VIEW](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-drop-view.html) + * [SHOW VIEWS](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-views.html) + * [SHOW TBLPROPERTIES](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-tblproperties.html) + * [SHOW CREATE TABLE](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-create-table.html) + + +#### Creating a view + +Create a simple view without any comments or properties: +```sql +CREATE VIEW <viewName> AS SELECT * from <tableName> +``` + +Using `IF NOT EXISTS` prevents the SQL statement from failing in case the view already exists: +```sql +CREATE VIEW IF NOT EXISTS <viewName> AS SELECT * from <tableName> +``` + +Create a view with a comment, including aliased and commented columns that are different from the source table: +```sql +CREATE VIEW <viewName> (ID COMMENT 'Unique ID', ZIP COMMENT 'Zipcode') + COMMENT 'View Comment' + AS SELECT id, zip FROM <tableName> +``` + +#### Creating a view with properties + +Create a view with properties using `TBLPROPERTIES`: +```sql +CREATE VIEW <viewName> + TBLPROPERTIES ('key1' = 'val1', 'key2' = 'val2') + AS SELECT * from <tableName> +``` + +Display view properties: +```sql +SHOW TBLPROPERTIES <viewName> +``` + +#### Dropping a view + +Drop an existing view: +```sql +DROP VIEW <viewName> +``` + +Using `IF EXISTS` prevents the SQL statment from failing if the view does not exist: +```sql +DROP VIEW IF EXISTS <viewName> +``` + +#### Replacing a view + +Update a view's schema, its properties, or the underlying SQL statement using `CREATE OR REPLACE`: +```sql +CREATE OR REPLACE <viewName> (updated_id COMMENT 'updated ID') + TBLPROPERTIES ('key1' = 'new_val1') + AS SELECT id FROM <tableName> +``` + +#### Setting and removing view properties + +Set the properties of an existing view using `ALTER VIEW ... SET TBLPROPERTIES`: +```sql +ALTER VIEW <viewName> SET TBLPROPERTIES ('key1' = 'val1', 'key2' = 'val2') +``` + +Remove the properties from an existing view using `ALTER VIEW ... UNSET TBLPROPERTIES`: +```sql +ALTER VIEW <viewName> UNSET TBLPROPERTIES ('key1', 'key2') +``` + +#### Showing available views + +The below SQL will list all views in the currently set namespace (via `USE <namespace>`): +```sql +SHOW VIEWS +``` + +List all available views in the defined catalog and/or namespace using one of the below variations: +```sql +SHOW VIEWS IN <catalog> +``` +```sql +SHOW VIEWS IN <namespace> +``` +```sql +SHOW VIEWS IN catalog.namespace Review Comment: done -- 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: issues-unsubscr...@iceberg.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@iceberg.apache.org For additional commands, e-mail: issues-h...@iceberg.apache.org
