Hive table format #

Great Lakes connectivity abstracts the details of using different table formats and file types when using object storage catalogs.

This page describes the features specific to the Hive table format when used with Great Lakes connectivity.

Specify Hive format #

Hive connectivity allows querying data stored in an Apache Hive data warehouse. Hive tables are automatically detected and read based on information in the storage environment’s associated metadata.

For an object storage catalog that specifies Hive as its default table format, no special syntax is required. A simple CREATE TABLE statement creates a Hive format table.

To create a Hive table when the default format is not Hive, add type='hive' as a table property in your CREATE statement. For example:

CREATE TABLE customer (
  name varchar,
  address varchar)
WITH (type='hive');

Hive table format properties #

As part of a CREATE TABLE statement, specify the format of table data files with an additional format property with one of the values shown in the following:

Hive table properties #

As part of a CREATE TABLE statement, append further comma-separated table properties as needed. Note that many of the available table properties must be used in conjunction with a particular Hive file format.

Some SQL statements must be supplemented with the type='hive' property for use in Starburst Galaxy. For example:

CREATE TABLE hive.web.page_views (
  view_time timestamp,
  user_id bigint,
  page_url varchar,
  ds date,
  country varchar
)
WITH (
  type = 'hive',
  format = 'ORC',
  partitioned_by = ARRAY['ds', 'country'],
  bucketed_by = ARRAY['user_id'],
  bucket_count = 50
)

Hive metadata table #

The $properties metadata table provides general configuration information about a Hive table. Query the metadata table by appending $properties to a table name:

SELECT * FROM "hive.web.page_views$properties";

Table procedures #

Use the CALL statement to perform data manipulation or administrative tasks. Procedures must include a qualified catalog name. For example, for a catalog named examplecatalog:

CALL examplecatalog.system.example_procedure();

The following Hive table procedures are available:

create_empty_partition #

system.create_empty_partition('schema_name', 'table_name', partition_columns, partition_values);

Create an empty partition in the specified table.

The partition arguments can have a different type of varchar such as an integer or date.

The value must be specified as varchar in the partition_values and partition_columns array. For example, partition_values => ARRAY['2016-08-09', 'US']

drop_stats #

system.drop_stats('schema_name', 'table_name', partition_values);

Drops statistics for one or more partitions or the entire table. Specify the partitions as an array whose elements are arrays of partition_values. The following example shows partition_values for dropping stats for multiple partitions:

partition_values => ARRAY[ARRAY['2016-08-09', 'US'], ARRAY['2023-08-03', 'UK']]

The partition_values argument is optional. If the partition_values argument is omitted, stats are dropped for the entire table.

register_partition #

system.register_partition('schema_name', 'table_name', partition_columns,
    partition_values, location);

Registers the existing location as a new partition in the metastore for the specified table.

With the location argument omitted, the partition location is constructed using partition_columns and partition_values.

sync_partition_metadata #

system.sync_partition_metadata('schema_name', 'table_name', 'mode', case_sensitive);

Check and update partitions list in the metastore. There are three modes available:

• ADD: add any partitions that exist on the file system, but not in the metastore.
• DROP: drop any partitions that exist in the metastore, but not on the file system.
• FULL: perform both ADD and DROP.

The case_sensitive argument is optional. The default value is true for compatibility with Hive’s MSCK REPAIR TABLE behavior, which expects the partition column names in file system paths to use lowercase (for example, col_x=SomeValue). Partitions on the file system not conforming to this convention are ignored, unless the argument is set to false.

unregister_partition #

system.unregister_partition('schema_name', 'table_name', partition_columns,
    partition_values);

Unregisters given, existing partition in the metastore for the specified table. The partition data is not deleted.

Migrate to Iceberg #

Great Lakes connectivity supports migration from Hive to Iceberg table format. It can read and write to Hive tables that have been successfully migrated to Iceberg. The migration process creates metadata without copying any data. In addition, table statistics are not generated.

The migration process from Hive to Iceberg table format only supports Parquet, ORC, and AVRO file formats.

Use the following statement ALTER TABLE SET PROPERTIES to convert a table from the Hive table format to the Iceberg table format:

ALTER TABLE catalog_name.schema_name.table_name SET PROPERTIES type = 'ICEBERG';

The statement must be called for a specific catalog example with the relevant schema and table names supplied with the required parameters schema_name and table_name.

Once the migration process is complete, use the SHOW CREATE TABLE statement to confirm that the table type has changed:

SHOW CREATE TABLE example_catalog.schema_name.table_name;

Successful migration displays type='ICEBERG'.

Partition projection #

Partition projection is a feature used to speed up query processing. This feature computes partition values and locations statically from the partition projection definition of the table properties and does not rely on the metastore for retrieving them.

Great Lakes connectivity supports partition projection table properties stored in the metastore, and it reimplements this functionality. Currently, date projection only supports intervals of DAYS, HOURS, MINUTES, and SECONDS.

If there are any compatibility issues blocking access to a requested table when partition projection is enabled, set the partition_projection_ignore table property to true for a table to bypass any errors.

In addition, Great Lakes connectivity supports the partition_projection_type table property.

The partition_projection_type defines the type of partition projection to use on a specific column. It can be set as either enum or integer and date:

Read more about Hive table properties.

Using partition projection #

To use partition projection, specify the ranges of partition values and projection types for each partition column in the table properties in the AWS Glue Data Catalog or in an external Hive metastore.

Create a projection with the enum projection type:

CREATE SCHEMA catalog_name.schema_name;
use catalog_name.test_schema;

CREATE TABLE enum_table_name (
    name varchar,
    comment varchar,
    nationkey integer,
    regionkey integer,
    short_name varchar WITH (
        partition_projection_type = 'enum',
        partition_projection_values = array ['POL', 'CZE']
    )
)
WITH (
    partitioned_by = array['short_name'],
    partition_projection_enabled = true
);

SHOW CREATE TABLE enum_table_name;

INSERT INTO enum_table_name VALUES ('POLAND_1', 'Czesc', 0, 5, 'POL');
INSERT INTO enum_table_name VALUES ('POLAND_2', 'Czesc', 1, 5, 'POL');
INSERT INTO enum_table_name VALUES ('CZECH_1', 'Ahoj', 2, 5, 'CZE');
INSERT INTO enum_table_name VALUES ('CZECH_2', 'Ahoj', 3, 5, 'CZE');

The INSERT INTO statements adds the data corresponding to the POL and CZE partitions.

SELECT * FROM "enum_table_name$partitions";

The SELECT only displays the partitions POL and CZE corresponding to the partition projection defined while creating the table, not the metastore.

| short_name |
+------------+
| POL        |
+------------+
| CZE        |
+------------+

Create a projection with the integer projection range:

CREATE TABLE integer_table_name (
    name varchar,
    comment varchar,
    nationkey integer,
    regionkey integer WITH (
        partition_projection_type = 'integer',
        partition_projection_range = array ['0', '10']
    )
)
WITH (
    partitioned_by = array['regionkey'],
    partition_projection_enabled = true
);

INSERT INTO integer_table_name VALUES ('POLAND_1', 'Czesc', 0, 5);
INSERT INTO integer_table_name VALUES ('POLAND_2', 'Czesc', 1, 5);
INSERT INTO integer_table_name VALUES ('CZECH_1', 'Ahoj', 2, 5);
INSERT INTO integer_table_name VALUES ('CZECH_2', 'Ahoj', 3, 5);
INSERT INTO integer_table_name VALUES ('GERMANY_1', 'Hallo', 12, 8);
INSERT INTO integer_table_name VALUES ('GERMANY_2', 'Hallo', 13, 8);
INSERT INTO integer_table_name VALUES ('MEXICO_1', 'Hola', 22, 18);

The INSERT INTO statements adds the data corresponding to the defined partitions.

SELECT * FROM "integer_table_name$partitions";

The SELECT * FROM statement returns only the partitions corresponding to the partition projection defined while creating the table, not the metastore.

| regionkey |
+-----------+
| 0         |
+-----------+
| 1         |
+-----------+
| 2         |
+-----------+
| 3         |
+-----------+
| 4         |
+-----------+
| 5         |
+-----------+
| 6         |
+-----------+
| 7         |
+-----------+
| 8         |
+-----------+
| 9         |
+-----------+
| 10        |
+-----------+

Create a projection with the integer projection range using partition_projection_interval:

CREATE TABLE interval_table_name (
    name varchar,
    comment varchar,
    nationkey integer,
    regionkey integer WITH (
        partition_projection_type = 'integer',
        partition_projection_range = array ['0', '10'],
        partition_projection_interval = 3
    )
)
WITH (
    partitioned_by = array['regionkey'],
    partition_projection_enabled = true
);

INSERT INTO interval_table_name VALUES ('POLAND_1', 'Czesc', 0, 5);
INSERT INTO interval_table_name VALUES ('POLAND_2', 'Czesc', 1, 5);
INSERT INTO interval_table_name VALUES ('CZECH_1', 'Ahoj', 2, 5);
INSERT INTO interval_table_name VALUES ('CZECH_2', 'Ahoj', 3, 5);
INSERT INTO interval_table_name VALUES ('GERMANY_1', 'Hallo', 12, 6);
INSERT INTO interval_table_name VALUES ('GERMANY_2', 'Hallo', 13, 9);
INSERT INTO interval_table_name VALUES ('MEXICO_1', 'Hola', 22, 18);

The INSERT INTO statements adds the data corresponding to the defined partitions.

SELECT * FROM "interval_table_name$partitions";

The SELECT * FROM statement returns only the partitions corresponding to the partition projection defined while creating the table, not the metastore.

In the following example output, only 0, 3, 6, and 9 are displayed because of the chosen partition_projection_interval value of 3.

| regionkey |
+-----------+
| 0         |
+-----------+
| 3         |
+-----------+
| 6         |
+-----------+
| 9         |
+-----------+

Create a projection with the date projection range:

CREATE TABLE date_table_name (
    name varchar,
    comment varchar,
    nationkey integer,
    dt varchar WITH (
        partition_projection_type = 'date',
        partition_projection_format = 'yyyy-MM-dd',
        partition_projection_range = array['NOW-5DAYS', 'NOW']
    )
)
WITH (
    partitioned_by = array['dt'],
    partition_projection_enabled = true
);

INSERT INTO date_table_name VALUES ('POLAND_1', 'Czesc', 0, '2023-06-01');
INSERT INTO date_table_name VALUES ('POLAND_2', 'Czesc', 1, '2023-06-01');
INSERT INTO date_table_name VALUES ('CZECH_1', 'Ahoj', 2, '2023-06-29');
INSERT INTO date_table_name VALUES ('CZECH_2', 'Ahoj', 3, '2023-06-01');
INSERT INTO date_table_name VALUES ('GERMANY_1', 'Hallo', 12, '2023-06-01');
INSERT INTO date_table_name VALUES ('GERMANY_2', 'Hallo', 13, '2023-07-02');
INSERT INTO date_table_name VALUES ('MEXICO_1', 'Hola', 22, '2023-07-01');

The INSERT INTO statements adds the data corresponding to the defined partitions.

SELECT * FROM "date_table_name$partitions";

The SELECT * FROM statement returns only the partitions corresponding to the partition projection defined while creating the table, not the metastore.

The following SELECT * FROM statement example returns the partition, dt:

| dt         |
+------------+
| 2023-08-26 |
+------------+
| 2023-08-27 |
+------------+
| 2023-08-28 |
+------------+
| 2023-08-29 |
+------------+
| 2023-08-30 |
+------------+
| 2023-08-31 |
+------------+

Session properties #

See session properties on the Great Lakes connectivity page to understand how these properties are used.

The following table describes the session properties supported by the Hive table type.

Hive SQL support #

When using the Hive table format with Great Lakes connectivity, the general SQL support details apply, with the following additional consideration:

• Hive views are not supported.