将每日更新的服务器CSV迁移至Google Cloud Storage的方案咨询

Hey there, I've run into this exact scenario before—Cloud Transfer Service is great for cloud-to-cloud transfers but falls short when pulling from external servers. Here are three solid, automated solutions to get your daily CSV into GCS for BigQuery:

This is my go-to for this kind of task since it's serverless, low-cost, and requires minimal maintenance.

• Step 1: Write a Cloud Function
  Create a function (I'll use Python here) that downloads your CSV from the server and uploads it to GCS. Make sure to enable the google-cloud-storage library and requests in your requirements.

  import requests
  from google.cloud import storage
  from datetime import datetime
  
  def upload_daily_csv(request):
      # Replace with your server's CSV URL
      csv_source_url = "https://your-external-server.com/daily-updates/data.csv"
      # Replace with your GCS bucket name and target path
      gcs_bucket_name = "your-gcs-bucket"
      timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
      gcs_target_file = f"daily-csvs/data_{timestamp}.csv"
  
      # Download the CSV from your server
      try:
          response = requests.get(csv_source_url)
          response.raise_for_status()  # Fail fast if request errors
      except requests.exceptions.RequestException as e:
          print(f"Failed to download CSV: {e}")
          return "Download failed", 500
  
      # Upload to GCS
      try:
          storage_client = storage.Client()
          bucket = storage_client.get_bucket(gcs_bucket_name)
          blob = bucket.blob(gcs_target_file)
          blob.upload_from_string(
              response.content,
              content_type="text/csv"
          )
          return f"Successfully uploaded {gcs_target_file} to {gcs_bucket_name}"
      except Exception as e:
          print(f"Failed to upload to GCS: {e}")
          return "Upload failed", 500
  

• Step 2: Set up Cloud Scheduler
  Create a daily trigger (e.g., 1 AM UTC) that calls your Cloud Function's HTTP endpoint. Use HTTP as the target type, select your function's URL, and set the method to GET (or POST if you prefer).

• Key Notes

  • Grant the Cloud Function's service account the Storage Object Creator role on your GCS bucket.
  • If your server requires authentication (Basic Auth, API keys), add the necessary headers/params to the requests.get call (e.g., headers={"Authorization": "Bearer YOUR_TOKEN"}).

If you need more control over the environment or have complex pre-processing steps, a small VM with cron works perfectly.

• Step 1: Create a small Compute Engine VM
  Pick a low-cost instance like e2-micro—you don't need much power for this task.

• Step 2: Write a shell script
  Save this as upload_csv.sh on your VM:

  #!/bin/bash
  # Download CSV from server
  curl -o daily_data.csv "https://your-external-server.com/daily-updates/data.csv" \
       -H "Authorization: Bearer YOUR_SERVER_TOKEN"  # Add auth if needed
  
  # Upload to GCS with timestamped filename
  gsutil cp daily_data.csv gs://your-gcs-bucket/daily-csvs/data_$(date +%Y%m%d_%H%M%S).csv
  
  # Clean up local file
  rm daily_data.csv
  

  Make it executable with chmod +x upload_csv.sh.

• Step 3: Set up cron job
  Edit your crontab with crontab -e and add a line to run the script daily:

  # Run at 1 AM UTC every day
  0 1 * * * /home/your-username/upload_csv.sh >> /var/log/csv_upload.log 2>&1
  

• Key Notes

  • Ensure the VM's service account has Storage Object Creator access to your GCS bucket.
  • The Google Cloud SDK (which includes gsutil) is pre-installed on most GCE images, but if not, install it with sudo apt-get install google-cloud-sdk.

If you need a containerized environment (e.g., specific dependencies or runtime versions), Cloud Run is a good middle ground between serverless and VMs.

• Step 1: Package your logic into a Docker image
  Create a Dockerfile that includes your download/upload code (similar to the Cloud Function logic) and all dependencies.

• Step 2: Deploy to Cloud Run
  Push the image to Container Registry or Artifact Registry, then deploy it to Cloud Run. Set the service to allow unauthenticated invocations (or use service account auth for security).

• Step 3: Schedule with Cloud Scheduler
  Create a daily trigger that sends an HTTP request to your Cloud Run service URL, just like with Cloud Functions.

Once your CSV is in GCS, you have two easy paths to query it in BigQuery:

• External Tables: Create an external table that points directly to your GCS CSV files. This lets you query the data without loading it into BigQuery storage.
• Automated Loads: Add a step to your function/script to trigger a BigQuery load job (using the BigQuery API or bq load command) to load the CSV into a native BigQuery table. You can also use BigQuery Transfer Service to load from GCS on a schedule.

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