Automating data ingestion into a BQ native table

[Mike888]

Hey all, I'm fairly new to GCP and BigQuery so please be kind. 😥

I've got data coming from an on-premise MS SQL Server that I'd like to use for BI Reports. From my understanding, ideally the BI tool needs to point to a BQ native table to use for reports. 

I'm able to automate the daily (batch CSV) data push into Cloud Storage so that the data can be accessed in BigQuery as an External table, but now I'm unsure how the data can flow directly (in an automated fashion) into a BQ native table. What I would like to happen is that every time I push data into Cloud Storage, it can immediately be accessible in BQ as native data so that any BI tool can use the info. 

How do I achieve this? Thank you in advance for your help!

[caryna]

Hi @Mike888,

Welcome to the Google Cloud Community!

To automate the process of loading data into a BigQuery native table, you can combine Cloud Functions and Dataflow. This approach will create robust and scalable data pipelines capable of handling both simple and complex processing tasks.

1. Cloud Functions - is an event-driven serverless compute platform that will trigger a BigQuery load job whenever a new file is added to your Cloud Storage bucket. You may check this documentation for more information.
2. Dataflow -This creates data pipelines that read from one or more sources. It will clean and transform the data, and write the data to a destination.

You can read the related Medium article, How to use Dataflow and Google Cloud Functions to automate processing of uploaded files and watch the Automating Data Loading from Google Cloud Storage to BigQuery using Cloud Function and Dataflow video on YouTube, both of which cover Cloud Functions and Dataflow.

Important notes: Check the costs of each solution by looking at how much data they handle, how long they take to process, and how much storage they need.

I hope the above information is helpful.