Skip to main content

Hive tables in object storage

A Hive catalog can create databases and tables whose files live in Amazon S3, Azure Data Lake Storage (ADLS), or Google Cloud Storage (GCS) instead of HDFS. Gravitino passes the location of a schema or table to the Hive metastore, and the metastore creates the directory there, so the metastore itself must be able to reach the object store. Configure that once on the metastore, then give schemas and tables an s3a://, abfss://, or gs:// location.

Quick start​

The example assumes the metastore already reaches S3, as set up in Hive metastore configuration, and that the metastore is connected as the catalog hive_catalog; see Hive catalog. It creates the schema sales_s3 at an S3 location and a Parquet table in it, whose files land under that location.

  1. Create the schema at an S3 location. For ADLS use an abfss:// location, and for GCS a gs:// location.

    Create a schema with an S3 location
    curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/hive_catalog/schemas" \
      -H "Accept: application/vnd.gravitino.v1+json" \
      -H "Content-Type: application/json" \
      -H "Authorization: Bearer $TOKEN" \
      -d '{
        "name": "sales_s3",
        "properties": {"location": "s3a://{bucket}/sales_s3"}
      }'

  2. Create a table in it. With no location of its own, the table is placed under the schema's.

    Create a Parquet table
    curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/hive_catalog/schemas/sales_s3/tables" \
      -H "Accept: application/vnd.gravitino.v1+json" \
      -H "Content-Type: application/json" \
      -H "Authorization: Bearer $TOKEN" \
      -d '{
        "name": "orders",
        "columns": [
          {"name": "id", "type": "long", "nullable": true},
          {"name": "amount", "type": "decimal(10,2)", "nullable": true}
        ],
        "properties": {"format": "PARQUET"}
      }'

  3. Check the location. The location property of the loaded table is s3a://{bucket}/sales_s3/orders.

    Load the table
    curl -sS "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/hive_catalog/schemas/sales_s3/tables/orders" \
      -H "Accept: application/vnd.gravitino.v1+json" \
      -H "Authorization: Bearer $TOKEN"

Configure the metastore​

The settings go in the metastore's hive-site.xml. Add the ones for your object store and restart the metastore.

hive-site.xml
<property>
  <name>fs.s3a.access.key</name>
  <value>{access_key_id}</value>
</property>
<property>
  <name>fs.s3a.secret.key</name>
  <value>{secret_access_key}</value>
</property>
<property>
  <name>fs.s3a.endpoint</name>
  <value>{s3_endpoint}</value>
</property>

To place every new database in the object store without giving each a location, also set hive.metastore.warehouse.dir to a path there, such as s3a://{bucket}/warehouse.

The metastore also needs the Hadoop file system jars for the store on its classpath:

Copy the file system jars to the metastore classpath
# S3
cp ${HADOOP_HOME}/share/hadoop/tools/lib/*aws* ${HIVE_HOME}/lib
# ADLS
cp ${HADOOP_HOME}/share/hadoop/tools/lib/*azure* ${HIVE_HOME}/lib
# GCS, with the connector release that matches your Hadoop version
cp gcs-connector-hadoop3-2.2.22-shaded.jar ${HIVE_HOME}/lib

The GCS connector is published on the Hadoop connectors releases page. Restart the metastore after adding the settings and jars.

Create a schema in object storage​

Give the schema a location in the store. Tables created in it without their own location are placed under it.

Create a schema with an object storage location
curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/hive_catalog/schemas" \
  -H "Accept: application/vnd.gravitino.v1+json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "name": "hive_schema",
    "comment": "Schema in object storage",
    "properties": {"location": "s3a://{bucket}/hive_schema"}
  }'

For ADLS use a location such as abfss://{container}@{storage_account}.dfs.core.windows.net/hive_schema, and for GCS one such as gs://{bucket}/hive_schema. Tables are covered in Tables and views.

Read the tables from Hive​

Hive sees the database and its tables in the metastore with their object storage locations, and reads and writes them directly once it has the same file system settings and jars as the metastore.

Inspect and query from the Hive CLI
SHOW CREATE DATABASE hive_schema;
USE hive_schema;
SHOW CREATE TABLE hive_table;
INSERT INTO hive_table VALUES (1, '2022-11-12', 'hello');
SELECT * FROM hive_table;

SHOW CREATE DATABASE shows the LOCATION in the object store, and the table's LOCATION is under it.

Read the tables from Spark​

With the Spark connector, pass the file system settings for the store to the Hive catalog in Spark as spark.sql.catalog.{hive_catalog}.* settings:

Configure a SparkSession for a Hive catalog on S3
SparkSession spark = SparkSession.builder()
    .config("spark.plugins", "org.apache.gravitino.spark.connector.plugin.GravitinoSparkPlugin")
    .config("spark.sql.gravitino.uri", "https://{gravitino_host}")
    .config("spark.sql.gravitino.metalake", "{metalake}")
    .config("spark.sql.gravitino.authType", "basic")
    .config("spark.sql.gravitino.basic.username", "{user}")
    .config("spark.sql.gravitino.basic.password", "{password}")
    .config("spark.sql.catalog.hive_catalog.fs.s3a.access.key", "{access_key_id}")
    .config("spark.sql.catalog.hive_catalog.fs.s3a.secret.key", "{secret_access_key}")
    .config("spark.sql.catalog.hive_catalog.fs.s3a.endpoint", "{s3_endpoint}")
    .config("spark.sql.catalog.hive_catalog.fs.s3a.impl", "org.apache.hadoop.fs.s3a.S3AFileSystem")
    .enableHiveSupport()
    .getOrCreate();

spark.sql("SELECT * FROM hive_catalog.hive_schema.hive_table").show();

For ADLS, set fs.abfss.impl and fs.azure.account.key.{storage_account}.dfs.core.windows.net the same way; for GCS, fs.gs.auth.service.account.enable and fs.gs.auth.service.account.json.keyfile. For S3-compatible stores that need it, add fs.s3a.path.style.access set to true.

Spark also needs the file system jars on its classpath: the Hadoop AWS jar and the AWS SDK bundle for S3, the Hadoop Azure jar and the Azure Storage Blob SDK for ADLS, and the GCS connector for GCS.

Last updated on