Creating Hive tables in watsonx.data

After a watsonx.data metastore has been registered in Db2, it is possible to create Hive tables directly in watsonx.data from Db2. The table is then accessible from both systems.

To create a Hive table in the watsonx.data catalog from Db2, add the "bigsql.external.catalog" property to the standard CREATE Datalake TABLE statement, thus providing the name used to register the watsonx.data metastore. The table will exist in both Db2 and watsonx.data.

There must be a schema in watsonx.data with the same name as the schema in which the table is created in Db2.

It is possible to SELECT from and INSERT to this table from Db2, subject to SQL access control.

Notes

  • Rows inserted into the exported Hive table in watsonx.data side are not immediately visible on the Db2 side. One has either wait until the cache on the Db2 side is refreshed or call HCAT_CACHE_SYNC to manually force a data cache refresh.

  • When exporting tables from Db2 to the watsonx.data it might be necessary to refresh the watsonx.data web UI for the exported tables to show up.

For partitioned Datalake tables, an INSERT operation will implicitly create a new table partition when a value that was not present in the table is provided for a partitioning column. If the table is shared by Db2 Warehouse and watsonx.data, the partition list must be manually refreshed in the catalog of the system where the INSERT statement was not run:
  • If inserting in Db2 Warehouse, this is achieved by running the sync_partition_metadata procedure for the table in watsonx.data as documented at HIVE CONNECTOR PROCEDURES.
  • If inserting in watsonx.data, this is achieved by running the MSCK REPAIR TABLE statement for the table in Db2 Warehouse as documented in MSCK REPAIR TABLE.

Example

The following example statement creates a Hive table in the watsonx.data metastore registered in Db2 as 'watsonxdata' and is also accessible from Db2:
CREATE DATALAKE TABLE hiveschema.db2exported(id int, name varchar(32)) STORED AS PARQUET LOCATION 'DB2REMOTE://hive-bucket//hiveschema/db2exported' TBLPROPERTIES('bigsql.external.catalog' = 'watsonxdata')