编写Hive正则表达式从指定URL与文件名中提取关键词

Alright, let's tackle this problem of extracting key keywords from those Android file path URLs using Hive's regex functions. I'll break this down step by step, covering both a general approach that works for most cases and tailored tweaks for each specific file.

核心思路 #

First, we need to strip off the file path and extension to get the raw filename, then extract the core keywords from it. Hive primarily uses two functions here: regexp_extract() (to capture matched content) and regexp_replace() (to clean up formatting like redundant underscores/hyphens).

通用基础正则：提取文件名（不含路径和扩展名） #

Use this regex to pull out the filename part (removing path and .mp4/.3gp suffixes) from any of the URLs:

regexp_extract(file_url, '.*\\/([^\\/]+)\\.[^.]+$', 1)

• Breakdown:
  • .*\\/: Matches all content before the final slash (the entire path)
  • ([^\\/]+): Captures all characters between the final slash and the last dot (this is the filename without extension, our target group 1)
  • \\.[^.]+$: Matches the file extension (like .mp4 or .3gp) at the end of the string

Now let's apply this (and adjust for edge cases) to each of your files:

1. SHAREit Movie File #

Original URL: file:///storage/emulated/0/SHAREit/videos/Dangerous_Hero_(2017)____Latest_South_Indian_Full_Hindi_Dubbed_Movie___2017_.mp4

Extract Full Movie Details

First pull the filename, then replace multiple underscores with spaces for readability:

SELECT 
  regexp_replace(
    regexp_extract('file:///storage/emulated/0/SHAREit/videos/Dangerous_Hero_(2017)____Latest_South_Indian_Full_Hindi_Dubbed_Movie___2017_.mp4', 
                   '.*\\/([^\\/]+)\\.[^.]+$', 1),
    '_+', ' '
  ) AS movie_keywords;

Output: Dangerous Hero (2017) Latest South Indian Full Hindi Dubbed Movie 2017

Extract Core Movie Title (Only Main Name + Year)

If you just need the core movie title, use a more targeted regex to capture it directly:

SELECT 
  regexp_replace(
    regexp_extract('file:///storage/emulated/0/SHAREit/videos/Dangerous_Hero_(2017)____Latest_South_Indian_Full_Hindi_Dubbed_Movie___2017_.mp4', 
                   '.*\\/(Dangerous_Hero_\\(2017\\))', 1),
    '_', ' '
  ) AS core_movie_name;

Output: Dangerous Hero (2017)

2. VidMate Downloaded Song File #

Original URL: file:///storage/emulated/0/VidMate/download/%E0%A0_-_Promo_Songs_-_Khiladi_-_Khesari_Lal_-_Bho.mp4

This URL has URL-encoded characters (%E0%A0). Use Hive's url_decode() first to decode these, then extract keywords:

SELECT 
  regexp_replace(
    regexp_extract(
      url_decode('file:///storage/emulated/0/VidMate/download/%E0%A0_-_Promo_Songs_-_Khiladi_-_Khesari_Lal_-_Bho.mp4'),
      '.*\\/([^\\/]+)\\.[^.]+$', 1
    ),
    '-+', ' '
  ) AS song_keywords;

Output (assuming %E0%A0 maps to a regional character): [Regional Char] Promo Songs Khiladi Khesari Lal Bho

If you don't need to decode the special character, skip the url_decode() step:

SELECT 
  regexp_replace(
    regexp_extract('file:///storage/emulated/0/VidMate/download/%E0%A0_-_Promo_Songs_-_Khiladi_-_Khesari_Lal_-_Bho.mp4', 
                   '.*\\/([^\\/]+)\\.[^.]+$', 1),
    '-+', ' '
  ) AS raw_song_keywords;

Output: %E0%A0 Promo Songs Khiladi Khesari Lal Bho

3. WhatsApp Video File #

Original URL: file:///storage/emulated/0/WhatsApp/Media/WhatsApp%20Video/VID-20171222-WA0015.mp4

WhatsApp video filenames are unique identifiers, so we can capture them directly with a precise regex:

SELECT 
  regexp_extract('file:///storage/emulated/0/WhatsApp/Media/WhatsApp%20Video/VID-20171222-WA0015.mp4', 
                 '.*\\/(VID-[0-9]+-WA[0-9]+)\\.mp4$', 1) AS whatsapp_video_id;

Output: VID-20171222-WA0015

Or use the general regex plus decoding (though the filename itself isn't encoded here):

SELECT 
  url_decode(
    regexp_extract('file:///storage/emulated/0/WhatsApp/Media/WhatsApp%20Video/VID-20171222-WA0015.mp4', 
                   '.*\\/([^\\/]+)\\.[^.]+$', 1)
  ) AS decoded_filename;

Output: VID-20171222-WA0015

4. Bluetooth-Transferred Video File #

Original URL: file:///storage/emulated/0/bluetooth/%5DChitaChola%7B%7D%D8%B9%D8%A7%D9%85%D8%B1%24%20.3gp

This URL has encoded special characters (%5D = ], %7B = {, %D8%B9%D8%A7%D9%85%D8%B1 = Arabic text, %24 = $). Decode first, then extract the core name:

SELECT 
  regexp_extract(
    url_decode('file:///storage/emulated/0/bluetooth/%5DChitaChola%7B%7D%D8%B9%D8%A7%D9%85%D8%B1%24%20.3gp'),
    '\\](ChitaChola)\\{\\}', 1
  ) AS core_video_name;

Output: ChitaChola

To get the full decoded filename:

SELECT 
  url_decode(
    regexp_extract('file:///storage/emulated/0/bluetooth/%5DChitaChola%7B%7D%D8%B9%D8%A7%D9%85%D8%B1%24%20.3gp', 
                   '.*\\/([^\\/]+)\\.[^.]+$', 1)
  ) AS decoded_filename;

Output: ]ChitaChola{عامر$

Quick Recap #

• For most cases, use .*\\/([^\\/]+)\\.[^.]+$ to extract the filename, then regexp_replace to clean formatting
• For URL-encoded files, always run url_decode() first before extracting
• For platform-specific filenames (like WhatsApp), write a targeted regex to capture the unique identifier directly

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