如何用Notepad++批量替换Hive建表语句多行文本并保留指定内容

Got it, let's tackle this Hive DDL cleanup task step by step using Notepad++ v7.8.2. We'll split this into two straightforward regex replacement jobs since you're okay with multi-step operations—this keeps things simple and reduces the chance of mistakes.

Step 1: Replace Partition/Storage Clause with STORED AS PARQUET (Preserve LOCATION) #

This step targets everything between the PARTITIONED BY clause and the LOCATION line, swapping it out for the Parquet storage directive while keeping your original location paths intact.

1. Open your Hive DDL file in Notepad++
2. Press Ctrl+H to launch the Replace dialog
3. Switch to the Regular expression radio button
4. Check both Wrap around and . matches newline (critical for matching multi-line content between clauses)
5. In the Find what field, paste this regex pattern:

   (PARTITIONED BY\s*\(.*?\)\s*)(.*?)(\s*LOCATION)
   

   Breakdown of the regex:
   • $1: Captures the full PARTITIONED BY (...) clause (we keep this intact)
   • .*?: Non-greedy match for all content between PARTITIONED BY and LOCATION (the stuff we want to replace)
   • $3: Captures the LOCATION keyword plus any leading whitespace (we keep this to preserve your original path)
6. In the Replace with field, paste:

   $1STORED AS PARQUET$3
   

7. Test with a single Replace first on one table to verify it works, then click Replace All to apply to all tables.

Step 2: Overwrite TBLPROPERTIES to Only Include Snappy Compression #

This step replaces any existing TBLPROPERTIES block with the single compression setting you need.

1. Keep the Replace dialog open (or re-open with Ctrl+H)
2. Stay in Regular expression mode with Wrap around enabled (keep . matches newline checked if your TBLPROPERTIES spans multiple lines)
3. In the Find what field, paste:

   TBLPROPERTIES\s*\(.*?\)
   

4. In the Replace with field, paste:

   TBLPROPERTIES ('parquet.compression'='SNAPPY')
   

5. Again, test with a single Replace if you want, then hit Replace All.

Quick Verification #

After running both steps, spot-check a few tables to confirm:

• The PARTITIONED BY clause is unchanged
• STORED AS PARQUET is correctly placed right before LOCATION (with your original path still there)
• TBLPROPERTIES only contains the 'parquet.compression'='SNAPPY' setting

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