使用Sqoop导出指定列数据未达预期结果求助

Let's walk through the problems in your current command and get your data exported correctly to the baseoutput table.

Key Issues in Your Command #

1. Wrong Target Table Specified
   You've used --table base in your command, but you want to export to the baseoutput table. This means your command is trying to write data back to the original source table instead of your intended target.

2. Incorrect Column Mapping & Order
   The --columns parameter in Sqoop Export defines the columns in the target table that you're writing to, and the order must match the sequence of columns in your HDFS data. Your target table baseoutput has columns stat (maps to state from source) and num (maps to station_num from source), but your HDFS data (from the original base table import) is ordered as station_num, zipcode, city, state—so the columns you need are in the wrong positions and mixed with unwanted columns.

3. Unfiltered Source Data
   Your HDFS directory /user/trail/basestations contains all 4 columns from the original base table, but you only need 2 columns (state and station_num) for the target table. We need to filter and reorder these first.

Step-by-Step Solution #

1. Filter & Reorder HDFS Data

First, we'll extract only the state and station_num columns from your existing HDFS data, and reorder them to match the target table's column sequence (state first, then station_num).

Assuming your imported data uses commas as field separators (adjust the -F value if you used a different separator like tabs \t):

# Extract state (4th column) and station_num (1st column), reorder, save to a new file
hadoop fs -cat /user/trail/basestations/* | awk -F',' '{print $4","$1}' > filtered_basestations.csv

# Upload the filtered data to a new HDFS directory
hadoop fs -put filtered_basestations.csv /user/trail/basestations_filtered

2. Run the Corrected Sqoop Export Command

Now use this command to export the filtered, reordered data to baseoutput. We'll specify the correct target table, column mapping, and point to the filtered HDFS directory:

sqoop export --connect jdbc:mysql://localhost/loudacre --username training -P \
--table baseoutput \
--export-dir /user/trail/basestations_filtered \
--columns stat,num

Additional Checks #

• If your original imported data uses a different field separator (like tabs), add --fields-terminated-by '\t' to the Sqoop export command to match.
• Ensure the baseoutput table exists in your MySQL database with the correct column types (stat varchar(255), num int(11)).
• Verify the filtered HDFS data has no malformed rows that could cause type mismatches (e.g., non-numeric values in the station_num column).

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