Great Lakes connectivity #

Great Lakes connectivity abstracts the details of using different table formats and file types when using certain write access statements for object storage systems. This connectivity is built into Starburst Galaxy, and is available to all users.

The following object storage catalogs support Great Lakes connectivity:

• Amazon S3 catalogs
• Azure Data Lake Storage catalogs
• Google Cloud Storage catalogs

This feature supports the following table formats described on separate pages:

• Iceberg table format
• Delta Lake table format
• Hive table format
• Hudi table format

Any access to existing tables works transparently for all table formats. Starburst Galaxy recognizes the format of your tables by reading the metastore associated with your object storage.

Great Lakes SQL support #

The SQL support with a catalog using Great Lakes connectivity depends partially on the table format in use, and set as default per catalog. The following additional details apply:

• Globally available statements are fully supported.
• Read operations are are fully supported.
• Data management support varies across table formats.
• Schema and table management support varies across table formats.
• Catalogs can use different table formats for different tables. This results in different SQL support and different metadata and storage options for different tables. See the individual table format pages listed above for details.
• View management is fully supported.
• Materialized view management automatically uses the Iceberg table format. This behavior is independent of the default table format of the catalog.

More specifics are available in the following sections about the different table formats, and their SQL support.

Specify default table format #

For data consumers who want to read from any object storage file in any supported table format, there are no configuration steps or commands. Starburst Galaxy reads each file’s type and format from the object store’s associated metadata. To check the format of a table, use the SHOW CREATE TABLE SQL statement and refer to the type property in the query result.

For data engineers who want to create new tables in an object storage catalog, Starburst Galaxy provides a simple way to specify the default format for all newly created tables. When creating a new catalog, select from one of the options in the Default table format dialog. Your selection applies only to newly created tables, and does not convert any existing tables.

You can also use a SQL statement to specify an individual table’s format when creating that table. With a statement such as CREATE TABLE or CREATE TABLE AS, specify the type parameter with iceberg, delta, or hive as its argument. A table created this way can override the catalog’s overall default table format. Examples are shown in the individual table format pages listed above.

Table properties #

Each table format has its own set of properties that are supported by Great Lakes connectivity as part of CREATE TABLE statements.

The following query lists the Starburst Galaxy table properties supported by Great Lakes connectivity:

SELECT * FROM system.metadata.table_properties;

Read more about the available table properties for each table format:

• Delta Lake table properties
• Hive table properties

CREATE VIEW #

The CREATE VIEW statement creates a new view based on a SELECT query. The view is a logical table that can be referenced by future queries. Views do not contain any data. Instead, the query stored by the view is executed every time the view is referenced by another query.

Views in Starburst Galaxy can be used to pull together federated data across any catalog. Read more about federating multiple data sources in Starburst Galaxy.

The use of an OR REPLACE clause is optional and causes the view to be replaced if it already exists, rather than raising an error.

The following examples show how to create a view:

Create a view:

CREATE VIEW view_name AS
SELECT col1, col2, col3
FROM table_name;

Create a view with a view comment:

CREATE VIEW view_with_comment
COMMENT 'This is a comment'
AS
SELECT col1, col2, col3
FROM table_name;

Create a view that summarizes the table_name table:

CREATE VIEW view_summary AS
SELECT col2, sum(col3) AS col_total
FROM table_name
GROUP BY col2;

Create a view replacing an existing view:

CREATE OR REPLACE VIEW view_name AS
SELECT col1, col2, col3
FROM table_name

In addition, Great Lakes connectivity also supports the following SQL statements for views:

• DROP VIEW
• ALTER VIEW
• SHOW CREATE VIEW

Security for views #

There are two different security modes for table views, DEFINER and INVOKER. The default security mode is DEFINER.

CREATE VIEW view_name
COMMENT 'this is a comment'
SECURITY DEFINER
AS
SELECT col1, col2
FROM table_name;

The DEFINER security mode allows the ability to restrict access to the underlying tables that the user may not be allowed to access. Tables referenced in the view are accessed using the permissions of the view owner (the creator or definer of the view) rather than the user executing the query.

A view created in INVOKER security mode is a stored query. Tables referenced in the view are accessed using the permissions of the user executing the query (the invoker of the view).

CREATE MATERIALIZED VIEW #

Create and validate the definition of a new materialized view view_name of a SELECT query. Run the REFRESH MATERIALIZED VIEW statement after the creation to repopulate the materialized view with data. This materialized view is a physical representation of the query results at the time of refresh. The data is stored and can be referenced by future queries.

If the materialized view is made from an Iceberg table, it only gets refreshed if there are new snapshots in the underlying tables.

Create a simple materialized view view_name over the table_name that only includes column_data_1:

CREATE MATERIALIZED VIEW catalog_name.schema_name.view_name
AS
SELECT column_data_1 FROM catalog_name.schema_name.table_name;

Read about the optional clauses and view additional usage examples for the CREATE MATERIALIZED VIEW SQL statement.

In addition, Great Lakes connectivity also supports the following SQL statements for materialized views:

• DROP MATERIALIZED VIEW
• SHOW CREATE MATERIALIZED VIEW
• REFRESH MATERIALIZED VIEW

CREATE SCHEMA #

The CREATE SCHEMA SQL statement creates a new, empty schema. A schema is a container that holds tables, views, and other database objects.

Create a new schema by using the default directory for the catalog or an explicit LOCATION. The location of the schema must be retrievable through the SHOW CREATE SCHEMA statement. In addition, your active role set must have the Location privilege for the intended location.

Assign location privileges by following these steps:

1. Click Access control > Roles and privileges.
2. Click the name of the role you want to change.
3. Click Privileges > Add privilege > Location.
4. In the Storage location name text field, enter the path of the location you want to use.

The following SQL statements are examples of creating a schema using the default directory location and using an explicit LOCATION:

Create a new schema schema_name in the default location:

CREATE SCHEMA schema_name;

Create a new schema schema_name_location, with a set LOCATION to s3://bucket-name/lakehouse/data/schema_name_location:

CREATE SCHEMA schema_name_location WITH (LOCATION = 's3://bucket-name/lakehouse/data/schema_name_location');

Create a new schema if it does not already exist.

CREATE SCHEMA IF NOT EXISTS schema_name;

Create a new schema schema_name, set the LOCATION property to s3://bucket-name/lakehouse/data/schema_name_location and set the owner to user_name:

CREATE SCHEMA schema_name AUTHORIZATION user_name WITH (LOCATION = 's3://bucket-name/lakehouse/data/schema_name_location');

View additional usage and clause examples for the CREATE SCHEMA SQL statement. In addition, the Great Lakes connectivity also supports the following SQL statements for schemas:

• ALTER SCHEMA
• DROP SCHEMA
• SHOW SCHEMA

DROP SCHEMA #

The DROP SCHEMA SQL statement drops an existing schema that is empty.

Drop an empty schema:

DROP SCHEMA schema_name;

Use the CASCADE clause to drop all objects within a schema. For example, this will drop tables, views, and materialized views.

Drop a non empty schema:

DROP SCHEMA schema_name CASCADE;