Auto upload CRM/offline data in a Google Big Query using Cloud Function and Cloud Storage

Google Cloud Platform is very, very cool. It’s a fully capable, enterprise-grade, scalable cloud ecosystem which lets even total novices get started with building their first cloud applications. In this article, I am using Google Cloud Function which can help to automate many regular tasks. This article can be used to store CRM or any other data in a Google Big Query. You will need to pass JSON formatted data to the Cloud Storage and Cloud Function will auto create the Big Query table with the passed data.

There will be 3 Google cloud services which we will be using and talking about.

1) Google Big Query
2) Google Cloud Storage
3) Google Cloud Function

I will try to explain all of the above in layman terms so, that anyone with little or no technical knowledge can understand.

The Google Big Query can be used to store data in a tabular format similarly like SQL. For all the reader who doesn’t know what SQL is, can imagine an excel sheet which has data in a tabular format. However, in the case of Excel, you cannot execute a query. In both the cases SQL or Excel or any other database, the user can face size limit. Due to the size limit, Big Query comes in a picture which can be used to store as much as data we want and query the same.

The Google Cloud Storage can be used to store data in any format. It is similar to your computer, you can store images, excel, or whatever you want. Google Cloud Storage has no size limit.

The Google Cloud Function can be used to write logic in python or Node.js and work with different Google services. In our case, we will be using Big Query and Cloud Storage.

I have written a function which checks our bucket (Bucket is like a folder in your computer and you can store whatever files you want within a bucket) and whenever we upload any new delimited JSON file then function create a corresponding table for it.

Please note, Our function will create a table with the same name as uploaded file name.

 

Google Cloud

Steps:

    1. Create a bucket in the Google Cloud Storage. We have created a bucket with the name test
    2. Create a cloud function with the below configuration
    3. Enter below Python code in main.py
    4. Enter below in a requirement.txt

Configuration:

Cloud Function Config

 

Code:

“””Import a json file into BigQuery.”””

import logging
import os
import re

from google.cloud import bigquery

GCP_PROJECT = os.environ.get(‘GCP_PROJECT’)

def bigqueryImport(data, context):
“””Import a json file into BigQuery.”””
# get storage update data
bucketname = data[‘bucket’]
filename = data[‘name’]
timeCreated = data[‘timeCreated’]
tablename = filename.replace(‘.json’, ”)
datasetname = ‘test’  #Big Query Dataset name where table will be created

# parse filename
table_id = ‘%s.%s.%s’ % (GCP_PROJECT, datasetname, tablename)

# log the receipt of the file
uri = ‘gs://%s/%s’ % (bucketname, filename)
print(‘Received file “%s” at %s.’ % (
uri,
timeCreated
))

# create bigquery client
client = bigquery.Client()

# get dataset reference
dataset_ref = client.dataset(datasetname)

# check if dataset exists, otherwise create
try:
client.get_dataset(dataset_ref)
except Exception:
logging.warn(‘Creating dataset: %s’ % (datasetname))
client.create_dataset(dataset_ref)

# create a bigquery load job config
job_config = bigquery.LoadJobConfig()
job_config.autodetect = True
job_config.create_disposition = ‘CREATE_IF_NEEDED’,
job_config.source_format = ‘NEWLINE_DELIMITED_JSON’,
job_config.write_disposition = ‘WRITE_TRUNCATE’,

# create a bigquery load job
try:
load_job = client.load_table_from_uri(
uri,
table_id,
job_config=job_config,
)
print(‘Load job: %s [%s]’ % (
load_job.job_id,
table_id
))
except Exception as e:
logging.error(‘Failed to create load job: %s’ % (e))

 

requirement.txt:

google-cloud-bigquery

 

Enter “bigqeuryImport” in a Function to execute field and save.

Cloud Function Config2

After all the above setting has been done, whenever you will upload a delimited JSON file in a test bucket corresponding table will get created in a test dataset in a Big Query.

 

 

Please follow and like us:
569

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

www.000webhost.com