如何将文件行号导入Hive表？多文件导入带行号方法咨询

Great question! Let's break this down clearly to address both parts of your ask:

1. Is there a built-in Hive variable like input__file__name for original file line numbers? #

Unfortunately, Hive doesn’t have a native built-in variable equivalent to input__file__name that directly retrieves the original line number from the source file. The core reason is that Hive processes data in splits (not full files) via execution engines like MapReduce, Tez, or Spark—there’s no default tracking of raw file line numbers in Hive’s standard input handlers.

But don’t worry, there are straightforward workarounds to preserve line numbers when importing your files.

2. How to import a.txt, b.txt, c.txt into Hive while keeping per-file line numbers? #

Below are three practical methods tailored to your scenario (your files have 3 lines each, so all approaches will work smoothly):

Method 1: Preprocess files locally first (simplest for small files) #

Since each file’s line numbers are independent, prepend the filename and line number to every line locally before uploading to HDFS. Use awk for this—super quick:

• For a.txt:

  awk '{print "a.txt:" NR ":" $0}' a.txt > processed_a.txt
  

• For b.txt:

  awk '{print "b.txt:" NR ":" $0}' b.txt > processed_b.txt
  

• For c.txt:

  awk '{print "c.txt:" NR ":" $0}' c.txt > processed_c.txt
  

Then create a Hive table and load the processed files:

CREATE TABLE file_data (
    source_file STRING,
    line_num INT,
    content STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ':';

LOAD DATA INPATH '/hdfs/path/to/processed_files/' INTO TABLE file_data;

After import, your table will have rows like:

Method 2: Use Hive TRANSFORM to generate line numbers on import #

If you prefer not to preprocess locally, use Hive’s TRANSFORM with an awk script to track line numbers per file. First, create an external table pointing to your raw files:

CREATE EXTERNAL TABLE raw_files (content STRING)
LOCATION '/hdfs/path/to/original_files/';

Then create your target table with line numbers using TRANSFORM:

CREATE TABLE file_data_with_lines AS
SELECT
    input__file__name AS source_file,
    line_num,
    content
FROM (
    TRANSFORM (input__file__name, content)
    USING 'awk ''BEGIN{prev_file=""; line=1} {if ($1 != prev_file) {line=1; prev_file=$1} print line, $2; line++}'''
    AS (line_num INT, content STRING)
    FROM raw_files
) t;

The awk script resets the line counter every time it encounters a new file, ensuring each file’s lines are numbered from 1 onward. Note: This works best for small files that fit into a single mapper split.

Method 3: Use Spark for reliable line number tracking (best for large files) #

If you have Spark available, this is the most robust approach—it handles large files split across multiple workers seamlessly:

import org.apache.spark.sql.functions._
import org.apache.spark.sql.expressions.Window

// Load raw text files
val rawDF = spark.read.text("/hdfs/path/to/original_files/")

// Add filename and per-file line number
val finalDF = rawDF
  .withColumn("source_file", input_file_name())
  .withColumn("line_num", row_number().over(Window.partitionBy("source_file").orderBy("value")))

// Save as Hive table
finalDF.write.saveAsTable("file_data")

This uses Spark’s window functions to group rows by filename and assign sequential line numbers—no risk of split-related line number errors.

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