能否用Sqoop从Oracle导入表至Hadoop并新增时间戳列？

Absolutely, you can absolutely add a timestamp column to record the data load time during your Sqoop import—this is a common practice for troubleshooting and data lineage tracking. Here are two reliable approaches to achieve this with your existing command:

Approach 1: Use --query to Customize the Import SQL (Recommended) #

The --table parameter in Sqoop pulls the entire table as-is, so to add an extra column, you'll need to switch to the --query parameter to define a custom SELECT statement. This lets you include the current timestamp directly in the import process, avoiding extra post-processing steps.

Modified Command #

sqoop import \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:oracle:thin:@//MY_ORACLE_SERVER \
--username USERNAME \
--password PASSWORD \
--target-dir /MyDIR \
--fields-terminated-by '' \
--query "SELECT *, CURRENT_TIMESTAMP AS load_timestamp FROM SOURCE_TABLE WHERE \$CONDITIONS" \
--split-by <your_split_column> \  # Replace with a valid column from SOURCE_TABLE for parallel import
--hive-table DESTINATION_TABLE \
--hive-import \
--hive-overwrite \
--hive-delims-replacement '<newline>'

Key Notes: #

• Replace <your_split_column> with a column from SOURCE_TABLE (preferably a numeric or evenly distributed column) to enable parallel import. Skipping this will force Sqoop to run in single-threaded mode, which is slow for large datasets.
• The \$CONDITIONS placeholder is mandatory for --query—it tells Sqoop how to split the data for parallel processing (don’t forget to escape the $ with a backslash in bash).
• CURRENT_TIMESTAMP is an Oracle function that returns the current date and time with timezone; if you prefer a timezone-agnostic value, use SYSDATE instead (though it’s a DATE type, not a timestamp).

Approach 2: Post-Import Hive Alter (Alternative) #

If you prefer to keep using the --table parameter, you can first import the raw data into Hive, then add and populate the timestamp column in a second step:

1. Run your original Sqoop command to import the table into Hive.
2. Execute these Hive commands to add and fill the timestamp column:

   ALTER TABLE DESTINATION_TABLE ADD COLUMN load_timestamp TIMESTAMP;
   INSERT OVERWRITE TABLE DESTINATION_TABLE
   SELECT *, CURRENT_TIMESTAMP() FROM DESTINATION_TABLE;
   

This approach works but requires an extra step, and may not be ideal for large datasets since it rewrites the entire table.

Final Tips #

• Test the command with a small subset of data first to verify the timestamp column is correctly populated.
• If you’re using a non-bash shell (like zsh), the escape for $CONDITIONS might differ—adjust the syntax accordingly.

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