Realtime Data Ingestion into GCP BigQuery with Oracle GoldenGate for Distributed Applications and Analytics

Overview

This Quickstart covers a step-by-step process showing how to ingest real-time data into GCP BigQuery with Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA).

GCP BigQuery is a cloud-based data warehousing platform that provides a fully managed service for storing, processing, and analysing data.

GG for DAA supports stage and merge and streaming api designs. In stage and merge, the change data is staged in a temporary location in microbatches and eventually merged into to the target table. GCS is used as the staging location.

All replication process is automatically handled by GoldenGate for Distributed Applications and Analytics (GG for DAA) GCP BigQuery Stage and Merge Handler.

• Prerequisites for Google Cloud Platform BigQuery Stage and Merge

• Install Dependency Files

• Create a Replicat in Oracle GoldenGate for Distributed Applications and Analytics

Prerequisites for Google Cloud Platform BigQuery Stage and Merge

• An Google BigQuery Connection.

• Google Cloud Platform (GCP) account set up.

• A Google Cloud Platform (GCP) service account key with relevant BigQuery Permissions. Copy your GCP service account key to a directory on your Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) Server.

• A Google Cloud Storage bucket with relevant permissions. Ensure that the GCS bucket and the BigQuery dataset exist in the same location or region.

• Target BigQuery tables can be created before configuring the replicat. If necessary permissions are provided, then GG for DAA can auto create the target BigQuery tables.

In this quick start, we will use a sample trail file (named tr) which is shipped with GG for DAA. If you want to continue with sample trail file, it is located at GG_HOME/opt/AdapterExamples/trail/ in your GG for DAA instance.

GG for DAA will create the tables automatically.

Install Dependency Files

Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) uses Java SDK provided by Google. You can download the SDKs using Dependency Downloader utility shipped with GG for DAA. Dependency downloader is a set of shell scripts that downloads dependency jar files from Maven and other repositories.

1. In your GG for DAA VM, go to dependency downloader utility. It is located at GG_HOME/opt/DependencyDownloader/.

2. Run gcs.sh and bigquery.sh with the required versions. Figure 91: Run gcs.sh and bigquery.sh with the required versions

1. 2 directories are created in GG_HOME/opt/DependencyDownloader/dependencies. Make a note of the directories:

   • /u01/app/ogg/opt/DependencyDownloader/dependencies/bigquery_1.111.1

   • /u01/app/ogg/opt/DependencyDownloader/dependencies/gcs_1.113.9

Create a Replicat in Oracle GoldenGate for Distributed Applications and Analytics

To create a replicat in Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA):

1. In the Administration Service tab, click the + sign to add a replicat. Figure 92: Click + in the Administration Service tab.

1. Select the Classic Replicat Replicat Type and click Next. There are two different Replicat types available: Classic and Coordinated. Classic Replicat is a single threaded process whereas Coordinated Replicat is a multithreaded one that applies transactions in parallel. Figure 93: Add Replicat

1. Enter the Replicat options, and click Next:

   a. Trail Name: Name of the required trail file (if using sample trail, provide as tr)

   b. Subdirectory: Provide as GG_HOME/opt/AdapterExamples/trail/ if using the sample trail.

   c. Target: Google BigQuery

   Enable Stage and Merge and select Google Cloud Storage as Available staging location. Figure 94: Replicat Options

1. Leave Managed Options as is and click Next. Figure 95: Managed Options

1. Enter Parameter File details and click Next. In the Parameter File, you can specify source to target mapping. If you are using the sample trail file (tr), then enter as follows: MAP QASOURCE.*, TARGET <your_schema_name>.*;

   If Coordinated Replicat is selected as the Replicat Type, an additional parameter needs to be provided: TARGETDB LIBFILE libggjava.so SET property=<ggbd-deployment_home>/etc/conf/ogg/your_replicat_name.properties

   Figure 96: Parameter File

1. In the next screen, you need to update the properties only tagged as TODO and click Create and Run.

   Provide your GCS bucket name:

   **#TODO**: Edit the GCS bucket name
   gg.eventhandler.gcs.bucketMappingTemplate=<gcs-bucket-name>

   Provide path to your GCP service account key:

   #TODO: Edit the GCS credentialsFile
   gg.eventhandler.gcs.credentialsFile=/path/to/gcp/credentialsFile

   Provide path to dependency jar files that you downloaded in prerequisites:

   #TODO: Edit to include the GCS Java SDK and BQ Java SDK.
   gg.classpath=/path/to/gcs_dependencies/*:/path/to/bq_dependencies/*

   Figure 97: Properties File

1. If replicat starts successfully, it will be in running state. You can go to action/details/statistics to see the replication statistics. Figure 98: Replication Statistics {: #GUID-C0E90722-C758-4BEA-8394-0E446A8741D9__FIG_PGG_2F5_LBC .titleinfigure} Figure 99: GCPBQ Statistics {: #GUID-C0E90722-C758-4BEA-8394-0E446A8741D9__FIG_JFV_MF5_LBC .titleinfigure}

2. You can go to GCP Big Query console and check the tables. It may take a short while for tables to be created and loaded. Figure 100: GCPBQ Console

  <div class="infoboxnote" markdown="1">

  **Note:**

  - You can run an initial load with BigQuery replicat. For more information see [BigQuery Handler](google-bigquery.html#GUID-489AAA81-CBED-4734-BA78-7A773DD70CE6 "Learn how to use the Google BigQuery Handler, which streams change data capture data from source trail files into Google BigQuery.").

  </div>