如何将HDFS输出存储到MySQL表？含Pig、Hive处理场景

Hey Saurabh! Great question—moving data from HDFS to MySQL is a common task in the Hadoop ecosystem, and there are straightforward ways to do this both for your specific Pig/Hive outputs and as a general workflow. Let's break it down step by step:

For Pig #

Pig has a built-in JDBC storage handler (via PiggyBank) that lets you write directly from your Pig script to MySQL. Here's how to use it:

First, make sure you have the MySQL JDBC driver (e.g., mysql-connector-java-8.0.x.jar) in Pig's lib directory, or specify it when running your script with -Dpig.additional.jars=/path/to/mysql-connector.jar.

Then, update your Pig script to load the HDFS output and store it to MySQL:

-- Load your existing HDFS output (adjust delimiter to match your data)
hdfs_data = LOAD '/user/hadoop/pig_job_output' USING PigStorage('\t') 
            AS (id:chararray, value:int, score:float);

-- Store directly to MySQL
STORE hdfs_data INTO 'jdbc:mysql://your-mysql-host:3306/your_database?user=your_username&password=your_password' 
USING org.apache.pig.piggybank.storage.JDBCStorage(
    'com.mysql.cj.jdbc.Driver',  -- Use com.mysql.jdbc.Driver for pre-8.0 MySQL
    'INSERT INTO target_mysql_table (id, value, score) VALUES (?, ?, ?)'
);

Pro tip: Double-check that your Pig schema matches the MySQL table's column order and data types—mismatches will cause errors.

For Hive #

You have two solid options here: using Hive's JDBC storage handler or leveraging Sqoop (which is often simpler for large datasets).

Option 1: Hive JDBC External Table #

Create an external Hive table that maps directly to your MySQL table, then insert your Hive output data into it:

-- Create the external table linked to MySQL
CREATE EXTERNAL TABLE mysql_linked_table (
    id string,
    value int,
    score float
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
WITH SERDEPROPERTIES (
    'hive.sql.column.mapping' = 'id,value,score'
)
TBLPROPERTIES (
    'hive.jdbc.driver.class' = 'com.mysql.cj.jdbc.Driver',
    'hive.jdbc.url' = 'jdbc:mysql://your-mysql-host:3306/your_database',
    'hive.jdbc.username' = 'your_username',
    'hive.jdbc.password' = 'your_password',
    'hive.jdbc.table.name' = 'target_mysql_table'
);

-- Insert data from your Hive output table into the MySQL-linked table
INSERT INTO mysql_linked_table
SELECT id, value, score FROM your_hive_output_table;

Again, ensure the MySQL driver is in Hive's lib directory for this to work.

Option 2: Sqoop Export (Recommended for Large Data) #

Sqoop is designed for moving data between Hadoop and relational databases, so it's perfect for bulk exports from Hive (which stores data in HDFS under the hood).

If your Hive output is a table, you can export it directly:

sqoop export \
    --connect jdbc:mysql://your-mysql-host:3306/your_database \
    --username your_username \
    --password your_password \
    --table target_mysql_table \
    --query 'SELECT id, value, score FROM your_hive_output_table WHERE $CONDITIONS' \
    --split-by id \  # Use a column with even distribution for parallel processing
    --input-fields-terminated-by '\t'

Or if you want to export the raw HDFS directory from your Hive job:

sqoop export \
    --connect jdbc:mysql://your-mysql-host:3306/your_database \
    --username your_username \
    --password your_password \
    --table target_mysql_table \
    --export-dir /user/hive/warehouse/your_hive_output_table \
    --input-fields-terminated-by '\t'

If you need a method that works for any HDFS data (not just Pig/Hive outputs), these are the go-to approaches:

Recommended: Sqoop Export #

As mentioned earlier, Sqoop is the industry standard for this task. It handles parallelism, fault tolerance, and schema matching out of the box. The basic steps are:

• Ensure the MySQL JDBC driver is in Sqoop's lib directory.
• Verify your HDFS data's format (delimiter, line endings) matches what MySQL expects.
• Run the sqoop export command with the appropriate parameters (as shown in the Hive section).

Custom MapReduce Job #

For full control over data transformation or business logic, write a custom MapReduce job that reads from HDFS and writes to MySQL. Key points to remember:

• Use a connection pool (like Apache DBCP) in your Reducer to avoid creating a new JDBC connection for every record (this kills performance).
• Use batch inserts with PreparedStatement.addBatch() and executeBatch() to speed up writes.
• Package your job with the MySQL driver jar and submit it to the Hadoop cluster.

Local Export + MySQL Load #

For small datasets, this quick-and-dirty method works:

1. Download the HDFS data to your local machine:

   hadoop fs -get /path/to/hdfs/data /local/directory/
   

2. Use MySQL's LOAD DATA LOCAL INFILE command to import the local file:

   LOAD DATA LOCAL INFILE '/local/directory/data_file'
   INTO TABLE target_mysql_table
   FIELDS TERMINATED BY '\t'
   LINES TERMINATED BY '\n'
   (id, value, score);
   

Note: You'll need to enable local_infile in your MySQL config and ensure your user has permission to use this command.

• Data Type Alignment: Always match HDFS data types to MySQL's column types (e.g., Pig chararray → MySQL VARCHAR, Hive int → MySQL INT).
• MySQL Permissions: Grant access to your Hadoop nodes/local machine in MySQL:

  GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'hadoop-node-ip' IDENTIFIED BY 'your_password';
  FLUSH PRIVILEGES;
  

• Performance: For large datasets, use Sqoop's parallelism (--num-mappers parameter) or batch inserts to avoid overwhelming MySQL.
• Fault Tolerance: Consider importing to a temporary MySQL table first, validating the data, then moving it to the production table—this prevents partial data loads from breaking your main table.

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