Ingesting data from object storage bucket

In this section of the tutorial, you are going to manage data in an object storage bucket and attach the bucket to HMS and associate with Presto engine.

1. Access any one of the object storage access tools like S3 Browser, AWS S3 console, direct S3 APIs, and various CLI/UI object storage tools.

2. Load data files to your object storage bucket by using the tool.

3. Register and attach the object storage bucket to HMS and associate with Presto engine by using watsonx.data UI.

4. Alternatively, you can also register and attach an object storage bucket with pre-existing data to HMS.

After you attach the object storage bucket to HMS, you need to load data files into Presto by creating schema and external tables through the Hive connector.

1. Run the following command to create schema for the data you want to access.

   CREATE SCHEMA <SCHEMA_NAME> WITH ( location = '<SCHEMA_LOCATION>' );

   For example:

   CREATE SCHEMA hive.gosales WITH ( location = 's3a://lhbeta/gosales' );

2. Run the following command to create table by using an external location by pointing to an uploaded data file.

   CREATE TABLE IF NOT EXISTS <TABLE_NAME> ("<COLUMN_NAMES>" <DATA_TYPE>) WITH ( format = '<DATA_FORMAT>', external_location = '<DATA_FILE_LOCATION>' );

   For example:

   CREATE TABLE IF NOT EXISTS hive.gosales.branch ("BRANCH_CODE" int, "ADDRESS1" varchar, "ADDRESS1_MB" varchar, "ADDRESS2" varchar, "ADDRESS2_MB" varchar, "CITY" varchar, "CITY_MB" varchar, "PROV_STATE" varchar, "PROV_STATE_MB" varchar, "POSTAL_ZONE" varchar, "COUNTRY_CODE" int, "ORGANIZATION_CODE" varchar, "WAREHOUSE_BRANCH_CODE" int) WITH ( format = 'CSV', external_location = 's3a://lhbeta/gosales/branch' );

If you want to use the data without creating a new copy for a different table format or more table optimizations, you can generate statistics alone with analyze table command.

1. To generate statistics, run the following analyze table command:

   analyze <TABLE_NAME>;

   For example:

   analyze hive.gosales.branch;

You can use the data for creating different table format and more table optimizations. It is recommended to convert the data files to analytics optimized format in Parquet or ORC to improve query performance, reduce server and storage resource consumption. Table format like Iceberg can provide more performance improvements and features like snapshots, time travel, and transactional support for insert, update, and delete.

1. To create table for a data in CSV format to Parquet format, run Create table as command:

   CREATE TABLE IF NOT EXISTS
   <TABLE_NAME>
   WITH ( format = 'PARQUET')
   AS
   SELECT *
   FROM <TABLE_NAME>;

   For example:

   CREATE TABLE IF NOT EXISTS
   hive.default.branch
   WITH ( format = 'PARQUET')
   AS
   SELECT *
   FROM hive.gosales.branch;

2. To change the table format to Iceberg, run Create table as command:

   CREATE TABLE IF NOT EXISTS
   <TABLE_NAME>
   WITH ( format = 'PARQUET')
   AS
   SELECT *
   FROM <TABLE_NAME>;

   For example:

   CREATE TABLE IF NOT EXISTS
   iceberg-beta.default.branch
   WITH ( format = 'PARQUET')
   AS
   SELECT *
   FROM hive.gosales.branch;