Posted on

Basic Google BigQuery Operations With a Salesforce Sync Demo in Mule 4

If we think about data storage, the first thing that comes to our mind is a regular database. This can be any of the most popular ones, like MySQL, SQL Server, Postgres, Vertica, etc., but I noticed not too many have interacted with one of the services Google provides with the same purpose: Google BigQuery. Maybe it is because of the pricing, but in the end, many companies are moving to cloud services and this service seems to be a great fit for them.

In this post, I will demonstrate in a few steps how we can make a sync job that allows us to describe a Salesforce instance and use a few objects to create a full schema of those objects (tables) into a Google BigQuery dataset. Then with the schema created, we will be able to push some data into BigQuery from Salesforce and see it in our Google Cloud Console project.

Posted on

Migrating to Snowflake, Redshift, or BigQuery? Avoid these Common Pitfalls

The Drive to Migrate Data to the Cloud

With data being valued more than oil in recent years, many organizations feel the pressure to become innovative and cost-effective when it comes to consolidating, storing, and using data. Although most enterprises are aware of big data opportunities, their existing infrastructure isn’t always capable of handling massive amounts of data.

By migrating to modern cloud data warehouses, organizations can benefit from improved scalability, better price elasticity, and enhanced security. But even with all these benefits, many businesses are still reluctant to make the move.

Posted on

How to Run SQL Queries With Presto on Google BigQuery

Presto has evolved into a unified SQL engine on top of cloud data lakes for both interactive queries as well as batch workloads with multiple data sources. This tutorial will show you how to run SQL queries with Presto (running with Kubernetes) on Google BigQuery.

Presto’s BigQuery connector allows querying the data stored in BigQuery. This can be used to join data between different systems like BigQuery and Hive. The connector uses the BigQuery Storage API to read the data from the tables.

Posted on

Utilizing BigQuery as A Data Warehouse in A Distributed Application

Introduction

Data plays an integral part in any organization. With the data-driven nature of modern organizations, almost all businesses and their technological decisions are based on the available data. Let's assume that we have an application distributed across multiple servers in different regions of a cloud service provider, and we need to store that application data in a centralized location. The ideal solution for that would be to use some type of database. However, traditional databases are ill-suited to handle extremely large datasets and lack the features that would help data analysis. In that kind of situation, we will need a proper data warehousing solution like Google BigQuery.

What is Google BigQuery?

BigQuery is an enterprise-grade, fully managed data warehousing solution that is a part of the Google Cloud Platform. It is designed to store and query massive data sets while enabling users to manage data via the BigQuery data manipulation language (DML) based on the standard SQL dialect.

Posted on

Introduction to Google BigQuery

It is incredible to see how much businesses rely on data today. 80% of business operations are running in the cloud, and almost 100% of business-related data and documents are now stored digitally. In the 1960s, money made the world go around but in today’s markets, “Information is the oil of the 21st century, and analytics is the combustion engine.” (Peter Sondergaard, 2011)

Data helps businesses gain a better understanding of processes, improve resource usage, and reduce waste; in essence, data is a significant driver to boosting business efficiency and profitability.

Posted on

Using MySQL as a Cache Layer for BigQuery

Cache layer

BigQuery is great at handling large datasets but will never give you a sub-second response, even on small datasets. It leads to a wait time on dashboards and charts, especially dynamic, where users can select different date ranges or change filters. It is almost always okay for internal BIs but not for customer-facing analytics. We tolerate a lot of things such as poor UI and performance in internal tools, but not in those we ship to customers.

But we still can leverage BigQuery’s cheap data storage and the power to process large datasets while not giving up on the performance. As BigQuery acts as a single source of truth and stores all the raw data, MySQL can act as cache layer on top of it and store only small, aggregated tables and provide us with a desired sub-second response.

Posted on

Apache Parquet vs. CSV Files

You have surely read about Google Cloud (i.e. BigQuery, Dataproc), Amazon Redshift Spectrum, and Amazon Athena. Now, you are looking to take advantage of one or two. However, before you jump into the deep end, you will want to familiarize yourself with the opportunities of leveraging Apache Parquet instead of regular text, CSV, or TSV files. If you are not thinking about how to optimize for these new query service models, you are throwing money out the window.

What Is Apache Parquet?

Apache Parquet is a columnar storage format with the following characteristics:

Posted on

Error Handling for Apache Beam and BigQuery (Java SDK)

Design the Pipeline

Let’s assume we have a simple scenario: events are streaming to Kafka, and we want to consume the events in our pipeline, making some transformations and writing the results to BigQuery tables, to make the data available for analytics. 

The BigQuery table can be created before the job has started, or, the Beam itself can create it.

Posted on

Loading Terabytes of Data From Postgres Into BigQuery

Despite the fact that an ETL task is pretty challenging when it comes to loading big data sets, there’s still the scenario in which you can load terabytes of data from Postgres into BigQuery relatively easily and very efficiently. This is the case when you have a lot of immutable data distributed in tables by some timestamp. For example, a transactions table with a created_at timestamp column. BigQuery and Postgres have great tools in order to do this pretty quickly and conveniently.

Preparing Postgres Tables

Posted on

The Benefits of Combining Google BigQuery and BI

As businesses produce significantly larger amounts of data, it’s important to have the right tools in place to interact with it while deriving the insights you need more effectively and quickly. Simply storing it and organizing it is not enough, and it can become difficult to rapidly analyze millions of data points even in the most efficient data structures.

Google BigQuery, the search giant’s database analytics tool, is ideal for trawling through billions of rows of data to find the right data for each analysis. Thanks to its intelligent design and approach to columnar storage, it can create better aggregates and work across massive compute clusters. When paired with the right BI, it can be a powerful tool for any business. These are some of the top reasons why you should consider Google BigQuery for your BI tools.