How to Load Oracle Data Into BigQuery

by Garrett Alley  
6 min read  • 18 Oct 2018

Getting data from your Oracle database into your BigQuery data warehouse is the first step in setting up a powerful analytical workflow and getting valuable insights from your data. Typically, having that data together with data from your various other sources in BigQuery delivers a compounding effect. The more data you have, the better your analysis, the better your insights.

Challenges with loading Oracle data into BigQuery

Let's say you want to take advantage of the power and scalability of the cloud to enrich your analytics data coming from your mobile devices with user attributes stored in your Oracle database. At first, the idea of moving data from Oracle into BigQuery may sound straight forward. You could use SQL Developer to export a table. Or you could use SQL*Plus to push your data out into a .csv file. There are several different approaches. Then you could load that into BigQuery. But what if you have huge Oracle tables with gigabytes of data? What if that data is being updated constantly? You'll need a secure and robust way to pipe that data into BigQuery. That's likely to require a lot of time and specialized resources.

And usually around this point you realize that you might want to set up a way to repeat this task, maybe put together some script to call the export and schedule that via cron. But that job is getting bigger and more complicated. Whether you want to perform the same migration into BigQuery periodically, or you want to add different tables (or even different data sources besides Oracle), you'll need someone to build a method for scheduling, tracking, and logging the process. And it will need to be scalable. Oh, and you'll need to make sure you have a way of catching and handling any errors that occur along the way.

You may also notice an opportunity to scrub PII (personally identifiable information) or enrich the data with things like geolocation data or currency conversion before the data is uploaded.

It's not an easy task, and unless you already have a seasoned team in place, you will need to train or hire someone who has the expertise to do the work. In reality, by the time you factor in security concerns, headcount, training, and technical complexity, you realize that you are, in essence, building your own ETL platform, just to extract your Oracle data.

An easier Oracle to BigQuery solution: Alooma

We recommend that you don't build a custom ETL tool and take on all of the technical challenge and resource costs. The better solution is to use a modern ETL platform designed to move data from Oracle (and other sources: databases, files, streams, applications) into BigQuery and make strategic transformations along the way.

Alooma is the enterprise data platform built for the cloud. With built-in support for Oracle and BigQuery, and bolstered by enterprise security and scalability, it's the ideal solution.

Steps to load data from Oracle to BigQuery

Getting your Oracle data into BigQuery is incredibly simple with Alooma. Let's break down the process:

Before you can create your Oracle input in Alooma, you'll need to decide on a data replication method. But don't worry, our documentation can help you decide. If you're using Log Miner (recommended) you'll need to perform some minor additional setup, as described in our documentation.

Once that's done, you can create your Oracle input in Alooma.

  1. On the Plumbing page, click "Add new input" and select Oracle from the list of integrations.
  2. oracle to bigquery step 1

  3. Name your input, click Next, and then enter your connection information.
  4. oracle to bigquery step 2

  5. That's all there is to it. You can basically repeat this process for any source of data you'd like to import. Once you save your input, assuming your credentials are correct, your Oracle data will automatically begin importing into BigQuery. Once you have the initial snapshot loaded into BigQuery, Alooma loads additional data according to the replication method you selected. See our documentation for more information.
  6. oracle to bigquery step 3

Of course, there's a lot more you could do along the way:

  • You could use the Code Engine to transform/enrich/cleanse data as it flows from Oracle to BigQuery.
  • You could change how the schema is mapped, via the Mapper; however, most of the time Alooma's powerful auto-mapping works just fine.
  • You could click on the Live tab for your Oracle input and monitor the data flow. Or click the Samples tab to see examples of the actual data being loaded.

What's next?

Put Your Data to Work: Now that you have your Oracle data in BigQuery you can take advantage of the scaling and processing power of having your data in the cloud, boosting your query performance so you can get more out of your data. And this is just the beginning.

Bust Data Silos: Don't just work with data from Oracle. Perform an information census and look for data silos within your company. Integrating multiple data sources into BigQuery is straightforward and simple, and each new source — whether it's a stream, a database, a file, an application, etc. — potentially increases the usefulness and impact of your analysis.

Automate the Process: Using an enterprise data platform means you can automate data extraction and transformation from multiple sources without having to build out your infrastructure.

Benefits

Enterprise scalability and performance: The Alooma platform provides horizontal scalability, handling as many events from as many data sources as you need.

Security at the core: The Alooma platform is built around a robust and flexible security architecture, providing full visibility and control over data. SOC 2 Type II, HIPAA and EU-US Privacy Shield, GDPR compliant, Alooma does not store any data permanently and encrypts all data in motion.

Guaranteed data integrity and reliability: The Restream Queue, Alooma's intelligent data integrity engine, is your safety net against data loss. The Restream Queue collects all the events that were not loaded to BigQuery, for whichever reason, making them easy to fix and enabling you to "restream" them into BigQuery later.

Flexible data enrichment: The Code Engine, a stateful, python-based processing engine, enables on the fly data enrichment for sophisticated use cases, such as real-time alerts, sessionization, anomaly detection, and more. Customize your data exactly how you want by writing real code to transform data on the stream.

Simple yet powerful data management: The Mapper automatically infers schemas, maps schema changes, or enables customization of mappings to your liking, ensuring you meet all your data governance requirements.

Cost effective: You won't need to hire or train staff to build the process, saving time and money. You won't need to buy more machines or processing power as your data grows, and adding new data sources to import into BigQuery is a breeze.

Getting started

Ultimately, you want the process of getting insights from your data, regardless of the source or structure, to be as simple as possible. The fewer steps, the lower the cost, the better. And if you can scale up to get data from other sources thrown in without requiring custom coding or processes, you're even further ahead of the game. Taking advantage of the power and scalability of the cloud to store and process that data is the natural next step.

Alooma was designed and built for the cloud. We enable businesses to use all of their data to make better data-driven decisions, providing Data Scientists and Data Engineers the ability to integrate, cleanse, enrich, and bring together batch or streaming data from various data silos at any time to any destination.

Alooma makes the whole process of getting your Oracle data into BigQuery simple and affordable.

Ready to get started? Alooma is here to help. Contact Alooma today to learn more about how a Oracle and BigQuery integration solution can benefit your business.

Like what you read? Share on

Get your data flowing

Contact us to start using Alooma for free

Get Started

This might interest you as well

Schedule a free demo!

We'll show you how Alooma can integrate all of your data sources in minutes.