1. Download the Service Account Credentials JSON File from Google Cloud

1.1. Go to IAM & Admin → Service Accounts

1.2. Select the account and open Key Management

1.3. Create a new key, select JSON type — the file will be downloaded automatically

1.4. Base64-encode the key file

On Windows, use an online base64 encoding tool.

On Linux/Mac, run base64 service_account_json_file.json and copy the output, removing any line breaks.

2. Get the Google Cloud Project ID

Click the project name to find the project ID and save it for later use.

3. Store the Credentials in AWS Secrets Manager

In the Secrets Manager console, create a new secret. Choose "Other type of secret", set the key to credentials and the value to the base64-encoded string.

Name the secret bigquery_credentials_poc and click Store. Then copy the secret ARN for later:
arn:aws:secretsmanager:us-west-2:260527533511:secret:bigquery_credentials_poc-wCHyT3

4. Create S3 Bucket s3-redshift-glue

4.1. Create the bucket in the Tokyo region with public access blocked

Copy the bucket ARN: arn:aws:s3:::s3-redshift-glue

4.2. Create a folder inside the bucket for the data

Name the folder after the table to be exported, e.g. 311_service_requests.

5. Create IAM Policy policy_secrets_s3

Create a policy using the following JSON to allow access to the secret and the S3 bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "GetDescribeSecret",
      "Effect": "Allow",
      "Action": [
        "secretsmanager:GetResourcePolicy",
        "secretsmanager:GetSecretValue",
        "secretsmanager:DescribeSecret",
        "secretsmanager:ListSecretVersionIds"
      ],
      "Resource": "arn:aws:secretsmanager:us-west-2:260527533511:secret:bigquery_credentials_poc-wCHyT3"
    },
    {
      "Sid": "S3Policy",
      "Effect": "Allow",
      "Action": [
        "s3:GetBucketLocation","s3:ListBucket","s3:GetBucketAcl",
        "s3:GetObject","s3:PutObject","s3:DeleteObject"
      ],
      "Resource": [
        "arn:aws:s3:::s3-redshift-glue",
        "arn:aws:s3:::s3-redshift-glue/*"
      ]
    }
  ]
}

6. Create IAM Role roleGlueBigqueryS3

Create a role named roleGlueBigqueryS3 with Glue as the trusted entity. Attach these three policies:

• AmazonEC2ContainerRegistryReadOnly
• AWSGlueServiceRole
• policy_s3_secrets

7. Subscribe to AWS Glue Connector for Google BigQuery on AWS Marketplace

7.1. Search for and find the AWS Glue Connector for Google BigQuery

7.2. Click Continue to Subscribe

7.3. Review terms, pricing, and details

7.4. Click Continue to Configuration

7.5. Select fulfillment option and software version, then click Continue to Launch

7.6. Under Usage Instructions, select "Activate the Glue connector from AWS Glue Studio"

7.7. Create the Glue connection named bigquery

Enter connection name bigquery and select bigquery_credentials_poc as the secret.

8. Create an ETL Job in AWS Glue Studio

8.1. Create the Job

• In Glue Studio, select Jobs
• For source, choose AWS Glue Connector for Google BigQuery
• For target, choose S3
• Click Create

8.2. Remove the ApplyMapping node

Select ApplyMapping and delete it.

8.3. Configure the AWS Glue Connector

8.3.1. Select bigquery as the connection

8.3.2. Add the following connection options

• Key: parentProject, Value: the project ID from Step 2
• Key: table, Value: bigquery-public-data.austin_311.311_service_requests

8.4. Configure the S3 Target

8.4.1. Select the output format (JSON for this POC)

8.4.2. Select compression type

8.4.3. Specify the S3 target path

8.5. Configure Job Details

• Name: Glue_BigQuery_S3
• IAM Role: roleGlueBigqueryS3 (created in Step 6)
• Type: Spark
• Glue version: Glue 2.0 – Supports Spark 2.4, Scala 2, Python 3
• Leave other options as default and click Save

9. Run the ETL Job

9.1. Click Run in the top-right of the job details page

9.2. Click Runs to monitor execution status

9.3. After successful completion, verify the files in the S3 bucket

9.4. Download and inspect the output file format

10. Summary

The migration of data from BigQuery to S3 using the AWS Glue custom connector is now complete. To load the data into Redshift, create the corresponding table in Redshift and use the COPY command to import the S3 data.

Back to Tech Blog