如何自动转换Google Play Console UTF-16报告为UTF-8并导入BigQuery？

Looks like you're already on the right track with your PowerShell script to fetch Google Play Console reports from GCS. The key missing piece is handling the UTF-16 to UTF-8 conversion so BigQuery can parse the CSV correctly. Here's how to modify your script to automate this step:

Updated PowerShell Script with Encoding Conversion #

# Define date and file variables
$date = (Get-Date).AddDays(-2).Date.ToString('yyyy-MM')
$date2 = $date.Replace('-', '')
$typefile = 'app_version'
$table = $typefile + '$' + $date2 + '01'
$gcs_csv_path = 'gs://pubsite_prod_rev_******_'+ $date2 + '_' + $typefile + '.csv'

# Local paths for original UTF-16 file and converted UTF-8 file
$local_utf16_path = 'C:\***\Scripts\gc\' + $date2 + '_' + $typefile + '.csv'
$local_utf8_path = 'C:\***\Scripts\gc\' + $date2 + '_' + $typefile + '_utf8.csv'

# Step 1: Download the UTF-16 CSV from Google Cloud Storage
& gsutil cp $gcs_csv_path $local_utf16_path

# Step 2: Convert from UTF-16LE to UTF-8 (removes BOM, which BigQuery prefers)
Get-Content -Path $local_utf16_path -Encoding Unicode | Set-Content -Path $local_utf8_path -Encoding UTF8

# Optional: Upload the UTF-8 file back to GCS (if you want to load directly from GCS to BigQuery)
& gsutil cp $local_utf8_path 'gs://your-target-bucket/' + $date2 + '_' + $typefile + '_utf8.csv'

# Step 3: Load the UTF-8 CSV into BigQuery (example command)
& bq load --source_format=CSV --autodetect --skip_leading_rows=1 your-project-id:your-dataset-id.$table $local_utf8_path

Key Details to Note: #

• Encoding Handling: PowerShell uses Unicode as the parameter value for UTF-16LE, which is the standard encoding for Google Play Console's CSV exports. When writing the UTF-8 file, Set-Content -Encoding UTF8 creates a UTF-8 file without a BOM—this is critical because BigQuery can misinterpret UTF-8 files with BOMs.
• Skip Header Rows: Most Google Play Console reports include a header row, so adding --skip_leading_rows=1 to your bq load command ensures BigQuery doesn't treat the header as data.
• Automation: If you want to run this script on a schedule, you can set it up as a Windows Task Scheduler job to fetch and convert reports automatically.

Alternative: Server-Side Conversion (No Local Script) #

If you prefer to avoid local processing, you can use a Cloud Function (Python) to trigger conversion whenever a new report is uploaded to GCS. The core logic would involve reading the UTF-16 file from GCS, converting it to UTF-8, and writing it back to a target bucket for BigQuery loading. But for your existing PowerShell workflow, the local conversion method is the most straightforward.

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