Connect to the Hive metastore using Beeline:

./beeline -u jdbc:hive2://node2:10000 -n root -p

Initialize the analytics environment:

CREATE DATABASE IF NOT EXISTS maritime_analytics;
USE maritime_analytics;

Establish an external staging table pointing to HDFS storage:

CREATE EXTERNAL TABLE external_titanic_stage (
    pid INT,
    survived_flag INT,
    cabin_tier INT,
    full_name STRING,
    gender STRING,
    passenger_age INT,
    siblings_spouse_cnt INT,
    parents_children_cnt INT,
    ticket_code STRING,
    ticket_fare DOUBLE,
    cabin_id STRING,
    port_code STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/user/hive/warehouse/titanic_external';

Load source data from the distributed filesystem:

hdfs dfs -put train.csv /user/hive/warehouse/titanic_external

Create an optimized ORC table for analytical workloads:

CREATE TABLE passenger_base (
    pid INT,
    survived_flag INT,
    cabin_tier INT,
    full_name STRING,
    gender STRING,
    passenger_age INT,
    siblings_spouse_cnt INT,
    parents_children_cnt INT,
    ticket_code STRING,
    ticket_fare DOUBLE,
    cabin_id STRING,
    port_code STRING
)
STORED AS ORC;

Ingest data into the optimized store:

INSERT OVERWRITE TABLE passenger_base
SELECT * FROM external_titanic_stage;

Implement static partitioning by passenger gender to optimize query performance for demographic analysis:

CREATE TABLE gender_partitioned (
    pid INT,
    survived_flag INT,
    cabin_tier INT,
    full_name STRING
)
PARTITIONED BY (gender_segment STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|';

Populate partitions explicitly:

INSERT OVERWRITE TABLE gender_partitioned PARTITION (gender_segment = 'F')
SELECT pid, survived_flag, cabin_tier, full_name 
FROM passenger_base 
WHERE gender = 'female';

INSERT OVERWRITE TABLE gender_partitioned PARTITION (gender_segment = 'M')
SELECT pid, survived_flag, cabin_tier, full_name 
FROM passenger_base 
WHERE gender = 'male';

Configure dynamic partitioning for automaetd cabin class segmentation:

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

CREATE TABLE class_partitioned (
    pid INT,
    survived_flag INT,
    full_name STRING
)
PARTITIONED BY (cabin_tier STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|';

Load data with automatic partition creation:

INSERT OVERWRITE TABLE class_partitioned PARTITION (cabin_tier)
SELECT pid, survived_flag, full_name, CAST(cabin_tier AS STRING) 
FROM passenger_base;

Implement bucketing for efficient sampling and join operations:

SET hive.enforce.bucketing = true;

CREATE TABLE age_bucketed (
    pid INT,
    full_name STRING,
    passenger_age INT
)
CLUSTERED BY (passenger_age) INTO 4 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|';

Populate the bucketed table:

INSERT OVERWRITE TABLE age_bucketed
SELECT pid, full_name, passenger_age 
FROM passenger_base;

Extract a representative sample using bucket sampling:

CREATE TABLE age_sample AS
SELECT * FROM age_bucketed 
TABLESAMPLE(BUCKET 1 OUT OF 2 ON passenger_age);

Execute analytical queries to derive survival insights. Filter for high-priority survivors:

SELECT * 
FROM passenger_base 
WHERE gender = 'male' 
  AND survived_flag = 1 
  AND cabin_tier = 1;

Aggregate survival statistics by socioeconomic class:

SELECT 
    cabin_tier,
    COUNT(*) AS total_passengers,
    SUM(survived_flag) AS survivors,
    ROUND(AVG(survived_flag) * 100, 2) AS survival_rate_pct
FROM passenger_base 
GROUP BY cabin_tier
ORDER BY survival_rate_pct DESC;

Analyze fare distribution patterns:

SELECT 
    full_name,
    ticket_fare,
    cabin_tier,
    NTILE(4) OVER (ORDER BY ticket_fare) AS fare_quartile
FROM passenger_base 
ORDER BY ticket_fare DESC
LIMIT 10;

Categorize passengers by age demographics:

SELECT
    SUM(CASE WHEN passenger_age >= 18 THEN 1 ELSE 0 END) AS adult_cnt,
    SUM(CASE WHEN passenger_age < 18 THEN 1 ELSE 0 END) AS minor_cnt,
    AVG(CASE WHEN passenger_age >= 18 THEN passenger_age END) AS avg_adult_age
FROM passenger_base;

Calculate age standard deviation using built-in statistical functiosn:

SELECT STDDEV_POP(passenger_age) AS age_std_dev,
       VAR_POP(passenger_age) AS age_variance
FROM passenger_base;

Implement ACID-compliant updates for data quality corrections:

SET hive.support.concurrency = true;
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.compactor.initiator.on = true;
SET hive.compactor.worker.threads = 1;

CREATE TABLE passenger_transactions (
    pid INT,
    survived_flag INT,
    cabin_tier INT,
    full_name STRING,
    gender STRING,
    passenger_age INT
)
STORED AS ORC
TBLPROPERTIES ('transactional' = 'true');

Seed the transactional table:

INSERT INTO passenger_transactions
SELECT pid, survived_flag, cabin_tier, full_name, gender, passenger_age 
FROM passenger_base;

Apply corrections to survivor records:

UPDATE passenger_transactions 
SET survived_flag = 0 
WHERE passenger_age > 60;

Remove invalid records:

DELETE FROM passenger_transactions 
WHERE passenger_age < 5;

Export processed datasets for downstream consumption:

EXPORT TABLE class_partitioned TO '/user/hive/exports/titanic_by_class';

Perform random sampling for validation:

SELECT *
FROM passenger_base
TABLESAMPLE(BUCKET 1 OUT OF 10 ON RAND());