Syncing to Hive Metastore

This document walks through the steps to register a OneTable synced table on Hive Metastore (HMS).

Pre-requisites

  1. Source table(s) (Hudi/Delta/Iceberg) already written to your local storage or external storage locations like S3/GCS/ADLS. If you don’t have the source table written in place already, you can follow the steps in this tutorial to set it up.
  2. A compute instance where you can run Apache Spark. This can be your local machine, docker, or a distributed system like Amazon EMR, Google Cloud’s Dataproc, Azure HDInsight etc. This is a required step to register the table in HMS using a Spark client.
  3. Clone the OneTable repository and create the utilities-0.1.0-SNAPSHOT-bundled.jar by following the steps on the Installation page
  4. This guide also assumes that you have configured the Hive Metastore locally or on EMR/Dataproc/HDInsight and is already running.

Steps

Running sync

Create my_config.yaml in the cloned OneTable directory.

Hudi

  1. sourceFormat: DELTA|ICEBERG # choose only one
  2. targetFormats:
  3.   - HUDI
  4. datasets:
  5.   -
  6.     tableBasePath: file:///path/to/source/data
  7.     tableName: table_name

Delta

  1. sourceFormat: HUDI|ICEBERG # choose only one
  2. targetFormats:
  3.   - DELTA
  4. datasets:
  5.   -
  6.     tableBasePath: file:///path/to/source/data
  7.     tableName: table_name
  8.     partitionSpec: partitionpath:VALUE # you only need to specify partitionSpec for HUDI sourceFormat

Iceberg

  1. sourceFormat: HUDI|DELTA # choose only one
  2. targetFormats:
  3.   - ICEBERG
  4. datasets:
  5.   -
  6.     tableBasePath: file:///path/to/source/data
  7.     tableName: table_name
  8.     partitionSpec: partitionpath:VALUE # you only need to specify partitionSpec for HUDI sourceFormat
  1. Replace with appropriate values for sourceFormat, tableBasePath and tableName fields.
  2. Replace file:///path/to/source/data to appropriate source data path if you have your source table in S3/GCS/ADLS i.e.
    • S3 - s3://path/to/source/data
    • GCS - gs://path/to/source/data or
    • ADLS - abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/<path-to-data>

From your terminal under the cloned OneTable directory, run the sync process using the below command.

  1. java -jar utilities/target/utilities-0.1.0-SNAPSHOT-bundled.jar --datasetConfig my_config.yaml

At this point, if you check your bucket path, you will be able to see .hoodie or _delta_log or metadata directory with relevant metadata files that helps query engines to interpret the data as a Hudi/Delta/Iceberg table.

Register the target table in Hive Metastore

Now you need to register the OneTable synced target table in Hive Metastore.

Hudi

A Hudi table can directly be synced to the Hive Metastore using Hive Sync Tool and subsequently be queried by different query engines. For more information on the Hive Sync Tool, check Hudi Hive Metastore docs.

  1. cd $HUDI_HOME/hudi-sync/hudi-hive-sync
  3. ./run_sync_tool.sh  \
  4. --jdbc-url <jdbc_url> \
  5. --user <username> \
  6. --pass <password> \
  7. --partitioned-by <partition_field> \
  8. --base-path <'/path/to/synced/hudi/table'> \
  9. --database <database_name> \
  10. --table <tableName>

Replace file:///path/to/source/data to appropriate source data path if you have your source table in S3/GCS/ADLS i.e.

  • S3 - s3://path/to/source/data
  • GCS - gs://path/to/source/data or
  • ADLS - abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/<path-to-data>

Now you will be able to query the created table directly as a Hudi table from the same spark session or using query engines like Presto and/or Trino. Check out the guides for querying the OneTable synced tables on Presto or Trino query engines for more information.

  1. SELECT * FROM <database_name>.<table_name>;

Delta

  1. spark-sql --packages io.delta:delta-core_2.12:2.0.0 \
  2. --conf "spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension" \
  3. --conf "spark.sql.catalog.spark_catalog=org.apache.spark.sql.delta.catalog.DeltaCatalog" \
  4. --conf "spark.sql.catalogImplementation=hive"

In the spark-sql shell, you need to create a schema and table like below.

  1. CREATE SCHEMA delta_db;
  3. CREATE TABLE delta_db.<table_name> USING DELTA LOCATION '/path/to/synced/delta/table';

Replace file:///path/to/source/data to appropriate source data path if you have your source table in S3/GCS/ADLS i.e.

  • S3 - s3://path/to/source/data
  • GCS - gs://path/to/source/data or
  • ADLS - abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/<path-to-data>

Now you will be able to query the created table directly as a Delta table from the same spark session or using query engines like Presto and/or Trino. Check out the guides for querying the OneTable synced tables on Presto or Trino query engines for more information.

  1. SELECT * FROM delta_db.<table_name>;

Iceberg

  1. spark-sql --packages org.apache.iceberg:iceberg-spark-runtime-3.2_2.12:1.2.1 \
  2. --conf "spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions" \
  3. --conf "spark.sql.catalog.spark_catalog=org.apache.iceberg.spark.SparkSessionCatalog" \
  4. --conf "spark.sql.catalog.spark_catalog.type=hive" \
  5. --conf "spark.sql.catalog.hive_prod=org.apache.iceberg.spark.SparkCatalog" \
  6. --conf "spark.sql.catalog.hive_prod.type=hive"

In the spark-sql shell, you need to create a schema and table like below.

  1. CREATE SCHEMA iceberg_db;
  3. CALL hive_prod.system.register_table(
  4.    table => 'hive_prod.iceberg_db.<table_name>',
  5.    metadata_file => '/path/to/synced/iceberg/table/metadata/<VERSION>.metadata.json'
  6. );

Replace the dataset path while creating a dataframe to appropriate data path if you have your table in S3/GCS/ADLS i.e.

  • S3 - s3://path/to/source/data
  • GCS - gs://path/to/source/data or
  • ADLS - abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/<path-to-data>

Now you will be able to query the created table directly as an Iceberg table from the same spark session or using query engines like Presto and/or Trino. Check out the guides for querying the OneTable synced tables on Presto or Trino query engines for more information.

  1. SELECT * FROM iceberg_db.<table_name>;

Conclusion

In this guide we saw how to,

  1. sync a source table to create metadata for the desired target table formats using OneTable
  2. catalog the data in the target table format in Hive Metastore
  3. query the target table using Spark