Oracle data (ETL) to Google BigQuery using Google Cloud Dataflow and Dataprep

Paulo Amaral
Apr 5, 2018 · 4 min read

ETL made easy

The Data Preparation and Transformations process consume up nearly 80% of one’s time of Data Scientist work.

In my case, the diagram below is based on cloud architecture that we have been created for our customer using Machine Learning Libraries like pandas, numpy, and others for Data Science. Our customer’s database table was imported into BigQuery for further data preparation and transformations using Google Cloud Dataprep.

  • I would like to mention the excellent job/work of TRIFACTA, a company that developed Dataprep together with Google.

This tool is really useful and time-saving, let’s say that it made our Data Scientists smile.

Data pipeline

Steps :

1. Export Oracle database table to CSV.


2. Upload file to Cloud Storage bucket.

GCP data storage bucket

3. Create Dataset and import csv data to Google Cloud Dataprep

Select Dataset and import data from cloud storage:

Dataprep — Select csv and create a dataset

Choose Cloud storage bucket and select file to import:

4. Add dataset into a flow to run a job on Google Cloud Dataflow

Add new recipe

Edit recipe

5. Clean Data and set job to run

I will not detail Dataprep so much because there are other great posts that you can use as a reference. ;)

6. Edit Job

7. Create a new Table on BigQuery

8. Save Settings and Run Job

9. View job running on Dataflow

Google Cloud Dataflow Job Detail

10. Check results

11- View Bigquery Table

  • Query complete (16.0s elapsed, 4.78 GB processed)
#standardSQL
SELECT * FROM
`project.BCM.AI` t
WHERE
REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), r’caixa marcha’)

Next Steps :

Datalab

References :

Google Cloud Platform - Community

A collection of technical articles published or curated by Google Cloud Platform Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Paulo Amaral

Written by

Humanitarian maker and CTO at OTIMIZAI #ICT #Adviser #ICT4D #tech http://www.otimizai.com

Google Cloud Platform - Community

A collection of technical articles published or curated by Google Cloud Platform Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.