How to load data from Redshift to BigQuery

1. Connect to Redshift: Use a SQL client or command-line tool to connect to your Redshift cluster.
2. Unload Data:
   
   • Choose the tables or data you want to transfer.
   • Use the UNLOAD command to export the data to Amazon S3 as delimited text files (CSV). For example:

UNLOAD ('SELECT * FROM your_table')

TO 's3://your-bucket/your-data-prefix'

CREDENTIALS 'aws_access_key_id=your_access_key_id;aws_secret_access_key=your_secret_access_key'

DELIMITER ','

ADDQUOTES

ALLOWOVERWRITE

PARALLEL OFF;

• Ensure the S3 bucket is in a region that is convenient for transferring to Google Cloud.

1. Verify Data Format:
   
   • Check that the data is in a format supported by BigQuery (CSV, JSON, Avro, Parquet, or ORC).
   • If necessary, transform the data into one of these formats.
2. Split or Compress Files (Optional):
   
   • If the files are very large, consider splitting them into smaller chunks or compress them using GZIP to speed up the transfer process.

1. Set up Google Cloud Storage:
   
   • Create a Google Cloud Storage (GCS) bucket in your Google Cloud project if you don’t already have one.
2. Transfer Files:
   
   • Use the gsutil command-line tool to transfer files from Amazon S3 to GCS. First, configure gsutil with your Google Cloud credentials.
   • Run the gsutil cp command to copy files from S3 to GCS. For example:

gsutil cp s3://your-bucket/your-data-prefix* gs://your-gcs-bucket/your-data-prefix

• Alternatively, you can use the Google Cloud Storage Transfer Service, which allows you to create a one-time transfer job or a schedule for recurring transfers from S3 to GCS.

1. Create a Dataset and Table in BigQuery:
   
   • Go to the BigQuery console.
   • Create a new dataset if necessary.
   • Define the schema for your table that matches the data you’re importing.
2. Load Data:
   
   • Use the BigQuery web UI, command-line tool (bq), or API to create a load job that points to the files in your GCS bucket.
   • Configure the job with the appropriate options such as file format, delimiters, etc.
   • For a CSV file, the command might look like:

bq load --source_format=CSV --autodetect --skip_leading_rows=1 your_dataset.your_table gs://your-gcs-bucket/your-data-prefix*

• Monitor the job for completion and check for any errors.

1. Check the Loaded Data:
   
   • After the load job is complete, verify that the data in BigQuery matches the original data from Redshift.
   • Run some test queries to ensure the data types and values are as expected.
2. Data Validation:
   
   • Consider performing a row count and some aggregation queries on both Redshift and BigQuery to ensure the data matches.
   • Look for discrepancies and re-import data if necessary.

1. Remove Temporary Files:
   
   • Once you have verified the data transfer, you can delete the files from S3 and GCS to avoid incurring storage costs.
   • Use the aws s3 rm and gsutil rm commands to remove the files.
2. Close Connections:
   
   • Ensure that all database connections to Redshift are closed and that you’ve logged out of the AWS and Google Cloud consoles.