Loading and mapping a field into Hive Table

Question

I'm new in apache Hive. I have two files in HDFS, One file contains business data and another is like a mapping table.

For example :

File 1 is like :

id;value
1;val1
2;val2
3;val3

File 2 is like this:

value;mappedValue
val1;newValue1
val2;newValue2
val3;newValue3

I want to create a hive table that contains data with mapped value.

The result I want is like this.

id;value    
1;newValue1
2;newValue2
3;newValue3

What is the best way to do this?

user_8571498 · Answer

There are many ways to do that.

One approach would be as following:

First: create database and tables in HIVE from the beeline(HIVE command line).

$ beeline -u jdbc:hive2://localhost:10000

CREATE DATABASE IF NOT EXISTS db_business;

SHOW databases;

USE db_business;

CREATE TABLE IF NOT EXISTS business_data (
  id INT, 
  value STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;'
STORED AS TEXTFILE
TBLPROPERTIES("skip.header.line.count"="1");

CREATE TABLE IF NOT EXISTS mapping_table (
  value STRING, 
  mapped_value STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;'
STORED AS TEXTFILE
TBLPROPERTIES("skip.header.line.count"="1");

SHOW tables;

Second: we have to load the data into the tables. LOAD DATA INPATH will remove the file from the origin.

LOAD DATA INPATH '/home/user/mydir/business_data.csv' INTO TABLE business_data;
LOAD DATA INPATH '/home/user/mydir/mapping_table.csv' INTO TABLE mapping_table;

You can use hdfs dfs commands to load data into hive table without removing data from the origin

$ hdfs dfs -cp /home/user/origin/file.csv /user/hive/warehouse/db_business.db/business_data
$ hdfs dfs -cp /home/user/origin/file1.csv /user/hive/warehouse/db_business.db/mapping_table

Third: We can create the third table with a CTAS(Create table as select) and joining both tables.

CREATE TABLE master_table
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;'
STORED AS TEXTFILE AS
SELECT id, mapped_value AS value
FROM business_data AS b
JOIN mapping_table AS m ON(b.value = m.value);

SELECT * FROM master_table;

+------------------+---------------------+--+
| master_table.id  | master_table.value  |
+------------------+---------------------+--+
| 1                | newValue1           |
| 2                | newValue2           |
| 3                | newValue3           |
+------------------+---------------------+--+

Loading and mapping a field into Hive Table

1 Answers