How to Explore On-Premises ORC Data Cost-Effectively in Google Cloud?

PDE By islever Editorial Last updated

Short answer

D Copy the ORC files on Cloud Storage, then create external BigQuery tables for the data scientist team.

Question

You created an analytics environment on Google Cloud so that your data scientist team can explore data without impacting the on-premises Apache Hadoop solution. The data in the on-premises Hadoop Distributed File System (HDFS) cluster is in Optimized Row Columnar (ORC) formatted files with multiple columns of Hive partitioning. The data scientist team needs to be able to explore the data in a similar way as they used the on-premises HDFS cluster with SQL on the Hive query engine. You need to choose the most cost-effective storage and processing solution. What should you do?
  1. Import the ORC files to Bigtable tables for the data scientist team.
  2. Import the ORC files to BigQuery tables for the data scientist team.
  3. Copy the ORC files on Cloud Storage, then deploy a Dataproc cluster for the data scientist team.
  4. Copy the ORC files on Cloud Storage, then create external BigQuery tables for the data scientist team. Correct Answer

Key Takeaways

This tests your understanding of BigQuery external tables with Hive partitioning versus running a Dataproc cluster, with the trap being that Dataproc feels 'similar to Hive' but is not cost-effective for ad-hoc exploration.

The question asks for the most cost-effective storage and processing solution for exploring on-premises Hive-partitioned ORC data on Google Cloud. Community consensus favors copying ORC files to Cloud Storage and creating external BigQuery tables, which allows direct SQL querying without data transformation or cluster management.

Common mistake: Choosing C (copy files to Cloud Storage and deploy a Dataproc cluster) is common because it seems to mirror the Hive experience, but it requires maintaining a running cluster, which is not the most cost-effective solution for exploratory analytics.

Expert Analysis

Why the Answer Is Correct

Option D is correct because it leverages BigQuery's serverless architecture to query ORC files directly from Cloud Storage without importing or transforming data. BigQuery external tables support Hive-partitioned ORC files, enabling the data science team to use SQL similarly to Hive. This approach minimizes storage costs (no duplicate data) and eliminates compute costs when the cluster is not running, making it the most cost-effective solution.

Why the Other Options Are Wrong

A (Bigtable) is wrong because Bigtable is designed for NoSQL workloads, not SQL-based exploration of ORC files. B (import to BigQuery) is viable but not cost-effective because it duplicates storage and incurs data loading costs, whereas external tables avoid this. C (Dataproc) is functionally similar to Hive but requires a persistent cluster (or paying for cluster startup each time), making it less cost-effective for exploratory workloads compared to serverless BigQuery.

Community Comment Notes

Comment [1] correctly notes that BigQuery external tables 'leverage the strengths of BigQuery for SQL-based exploration while avoiding additional costs and complexity.' Comment [2] argues for C due to similarity with Hive, but this misses the cost-effectiveness requirement; comment [7] raises concerns about external table limitations, but BigQuery's support for Hive-partitioned ORC files mitigates the main issue. The 80-vote majority for D reflects the well-established best practice of using BigQuery external tables for ad-hoc SQL analytics on raw data files in Cloud Storage.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

Look for keywords like 'most cost-effective' and 'explore data' in the question. When raw data files already exist, prefer BigQuery external tables over Dataproc clusters or importing into BigQuery, as serverless query engines eliminate idle compute costs and data duplication.

Community Votes

D
84%
C
16%

84% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 8 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 8

  1. raaad 👍 8 Answered D
    - It leverages the strengths of BigQuery for SQL-based exploration while avoiding additional costs and complexity associated with data transformation or migration. - The data remains in ORC format in Cloud Storage, and BigQuery's external tables feature allows direct querying of this data.
  2. kaisarfarel 👍 6
    I think C is the correct answer, DS want to explore the data in a "similar way as they used the on-premises HDFS cluster with SQL on the Hive query engine". Dataproc can help to create clusters quickly with the Hadoop cluster. CMIIW
  3. Smakyel79 👍 3 Answered D
    This approach leverages BigQuery's powerful analytics capabilities without the overhead of data transformation or maintaining a separate cluster, while also allowing your team to use SQL for data exploration, similar to their experience with the on-premises Hadoop/Hive environment.
  4. 0725f1f 👍 3 Answered C
    it is talking about partition as well
  5. Matt_108 👍 2 Answered D
    Option D - leverages BigQuery for SQL-based exploration on direct querying to cloud storage
Show 3 more comments
  1. Pime13 👍 1 Answered D
    D. Copy the ORC files on Cloud Storage, then create external BigQuery tables for the data scientist team. This approach allows you to leverage the scalability and cost-effectiveness of Cloud Storage while enabling your data scientists to query the data using BigQuery's powerful SQL engine without the need to move or transform the data. This setup also minimizes the need for additional infrastructure and maintenance, making it a practical choice for your analytics environment.
  2. SamuelTsch 👍 1 Answered B
    using external tables have always limitations - affecting performance, no preview of the data and no cost estimation. So, why option D is correct?
  3. hanoverquay 👍 1 Answered D
    option d

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Which Google Cloud database for on-prem PostgreSQL with analytics?

S-grade

← Back to PDE Study Guide