Hive技术问题：多文件建多表、按文件统计条目及单表合并遇阻

Hey there! Let's tackle your Hive questions one by one, starting with your main goal of merging entries from multiple files into a single table, then covering the other two requirements.

1. Fixing the Merge-to-Single-Table Issue #

First, let's address the problem with your current CREATE TABLE statement. Looking at your data example (student#A18645 student#V86541 student#X78543), your entries are separated by spaces, but your query uses FIELDS TERMINATED BY ','—that's the root mismatch!

Correct Table Creation for Merged Data #

We’ll split this into two scenarios based on how your data is structured:

Scenario A: Each line has multiple student entries (space-separated)

If each line contains multiple student IDs and you want each ID to be a separate row in your final table:

1. First, create a raw table to read full lines

CREATE EXTERNAL TABLE raw_students (
    raw_line STRING
)
ROW FORMAT DELIMITED
LINES TERMINATED BY '\n'
LOCATION 'hdfs:///hive-data';

2. Create your final merged table

CREATE TABLE merged_students (
    team STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'; -- Delimiter doesn't matter much here since we're inserting single values

3. Split lines into individual entries and insert
   Use split() and LATERAL VIEW to break down each line into rows:

INSERT OVERWRITE TABLE merged_students
SELECT student
FROM raw_students
LATERAL VIEW explode(split(raw_line, ' ')) exploded AS student
WHERE student != ''; -- Filter out empty strings from trailing spaces

Scenario B: Each line is a single student entry

If every line in your files is one student ID (e.g., one line = student#A18645), you can simplify to a single table creation:

CREATE EXTERNAL TABLE merged_students (
    team STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' ' -- Irrelevant here since each line has one field
LINES TERMINATED BY '\n'
LOCATION 'hdfs:///hive-data';

Hive will automatically pull all files in the hive-data directory into this table—no extra steps needed!

2. Creating Separate Tables for Each File #

If you want a distinct Hive table for each file in your HDFS directory, here are two approaches:

Option 1: Manual Creation (for a small number of files) #

For each file (e.g., file1.txt, file2.txt), create an external table pointing directly to that file:

-- For file1.txt
CREATE EXTERNAL TABLE student_file1 (
    team STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\n'
LOCATION 'hdfs:///hive-data/file1.txt';

-- Repeat this pattern for file2.txt, file3.txt, etc.

Option 2: Scripted Creation (for many files) #

If you have dozens/hundreds of files, use a shell script to automate table creation:

# List all files in the target HDFS directory
hdfs dfs -ls /hive-data | grep -v "^d" | awk '{print $8}' > file_list.txt

# Generate Hive DDL statements for each file
while read file_path; do
    file_name=$(basename "$file_path" .txt)
    echo "CREATE EXTERNAL TABLE student_${file_name} (team STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' LOCATION 'hdfs://${file_path}';" >> create_tables.hql
done < file_list.txt

# Run the generated script in Hive
hive -f create_tables.hql

3. Counting Entries per File #

To count how many entries exist in each source file, use Hive's built-in input_file_name() function—it returns the full HDFS path of the file containing the current row.

Query for Entry Count per File #

• If using the merged table (each row = one student entry):

SELECT
    input_file_name() AS source_file,
    COUNT(*) AS entry_count
FROM merged_students
GROUP BY input_file_name();

• If using the raw table (before splitting lines):

SELECT
    input_file_name() AS source_file,
    SUM(size(split(raw_line, ' '))) AS entry_count
FROM raw_students
GROUP BY input_file_name();

This will give you a clear breakdown of entry counts per individual file.

内容的提问来源于stack exchange，提问作者deeformvp