Hive Bigquery Storage Handler

Hive Storage Handler for interoperability between BigQuery and Apache Hive
Alternatives To Hive Bigquery Storage Handler
Project NameStarsDownloadsRepos Using ThisPackages Using ThisMost Recent CommitTotal ReleasesLatest ReleaseOpen IssuesLicenseLanguage
Scio2,45613418 hours ago82March 30, 2020149apache-2.0Scala
A Scala API for Apache Beam and Google Cloud Dataflow.
Hadoop Connectors267224610 days ago578December 12, 202250apache-2.0Java
Libraries and tools for interoperability between Hadoop-related open-source software and Google Cloud Platform.
Bigquery To Datastore47
3 years ago1Java
Export a whole BigQuery table to Google Datastore with Apache Beam/Google Dataflow
Kettle Beam30
3 years ago14apache-2.0Java
Kettle plugins for Apache Beam
Almaren Framework2922 months ago48July 21, 20223apache-2.0Scala
The Almaren Framework provides a simplified consistent minimalistic layer over Apache Spark. While still allowing you to take advantage of native Apache Spark features. You can still combine it with standard Spark code.
Data Pipeline23
5 years agoPython
Hive Bigquery Storage Handler16
a year ago8apache-2.0Java
Hive Storage Handler for interoperability between BigQuery and Apache Hive
Kuromoji For Bigquery14
5 days ago5Java
Tokenize Japanese text on BigQuery with Kuromoji in Apache Beam/Google Dataflow at scale
Nifi Bigquery Bundle11
4 years ago3Java
Bigquery bundle for Apache NiFi
Data Rivers8
5 days ago35Python
Apache Airflow and Beam ETL scripts for the City of Pittsburgh's data analysis pipelines
Alternatives To Hive Bigquery Storage Handler
Select To Compare

Alternative Project Comparisons

Hive-BigQuery StorageHandler [No Longer Maintained]

This is a Hive StorageHandler plugin that enables Hive to interact with BigQuery. It allows you keep your existing pipelines but move to BigQuery. It utilizes the high throughput BigQuery Storage API to read data and uses the BigQuery API to write data.

The following steps are performed under Dataproc cluster in Google Cloud Platform. If you need to run in your cluster, you will need setup Google Cloud SDK and Google Cloud Storage connector for Hadoop.

Getting the StorageHandler

  1. Check it out from GitHub.
  2. Build it with the new Google Hadoop BigQuery Connector
git clone  
cd hive-bigquery-storage-handler  
mvn clean install  
  1. Deploy hive-bigquery-storage-handler-1.0-shaded.jar

Using the StorageHandler to access BigQuery

  1. Enable the BigQuery Storage API. Follow these instructions and check pricing details

  2. Copy the compiled Jar to a Google Cloud Storage bucket that can be accessed by your hive cluster

  3. Open Hive CLI and load the jar as shown below:

hive> add jar gs://<Jar location>/hive-bigquery-storage-handler-1.0-shaded.jar;  
  1. Verify the jar is loaded successfully
hive> list jars;  

At this point you can operate Hive just like you used to do.

Creating BigQuery tables

If you have BigQuery table already, here is how you can define Hive table that refer to it:

CREATE TABLE bq_test (word_count bigint, word string)  
 'bq.dataset'='<BigQuery dataset name>', 
 'bq.table'='<BigQuery table name>', 
 ''='<Your Project ID>', 
 ''='gs://<Bucket name>/<Temporary path>', 
 ''='<Cloud Storage Bucket name>' 

You will need to provide the following table properties:

Property Value
bq.dataset BigQuery dataset id (Optional if hive database name matches BQ dataset name)
bq.table BigQuery table name (Optional if hive table name matches BQ table name) Your project id
mapred.temp.gcs.path Temporary file location in GCS bucket Temporary GCS bucket name

Data Type Mapping

INTEGER BIGINT Signed 8-byte Integer
FLOAT DOUBLE 8-byte double precision floating point number
DATE DATE FORMAT IS YYYY-[M]M-[D]D. The range of values supported for the Date type is 0001-­01-­01 to 9999-­12-­31
TIMESTAMP TIMESTAMP Represents an absolute point in time since Unix epoch with millisecond precision (on Hive) compared to Microsecond precision on Bigquery.
BOOLEAN BOOLEAN Boolean values are represented by the keywords TRUE and FALSE
STRING STRING Variable-length character data
BYTES BINARY Variable-length binary data
REPEATED ARRAY Represents repeated values
RECORD STRUCT Represents nested structures


The new API allows column pruning and predicate filtering to only read the data you are interested in.

Column Pruning

Since BigQuery is backed by a columnar datastore, it can efficiently stream data without reading all columns.

Predicate Filtering

The Storage API supports arbitrary pushdown of predicate filters. To enable predicate pushdown ensure hive.optimize.ppd is set to true.
Filters on all primitive type columns will be pushed to storage layer improving the performance of reads. Predicate pushdown is not supported on complex types such as arrays and structs. For example - filters like = "Sunnyvale" will not get pushdown to Bigquery.


  1. Ensure that table exists in bigquery and column names are always lowercase
  2. timestamp column in hive is interpreted to be timezoneless and stored as an offset from the UNIX epoch with milliseconds precision.
    To display in human readable format from_unix_time udf can be used as
    from_unixtime(cast(cast(<timestampcolumn> as bigint)/1000 as bigint), 'yyyy-MM-dd hh:mm:ss')      


  1. Writing to BigQuery will fail when using Apache Tez as the execution engine. As a workaround set hive.execution.engine=mr to use MapReduce as the execution engine
  2. STRUCT type is not supported unless avro schema is explicitly specified using either avro.schema.literal or avro.schema.url table properties. Below table contains all supported types defining schema explicitly. Note: If table doesn't need struct then specifying schema is optional
     CREATE TABLE dbname.alltypeswithSchema(currenttimestamp TIMESTAMP,currentdate DATE, userid BIGINT, sessionid STRING, skills Array<String>,
       eventduration DOUBLE, eventcount BIGINT, is_latest BOOLEAN,keyset BINARY,addresses ARRAY<STRUCT<status: STRING, street: STRING,city: STRING, state: STRING,zip: BIGINT>> )
       STORED BY ''
            "fields":[{"name":"currenttimestamp","type":["null",{"type":"long","logicalType":"timestamp-micros"}], "default" : null}
                     ,{"name":"currentdate","type":{"type":"int","logicalType":"date"}, "default" : -1},{"name":"userid","type":"long","doc":"User identifier.", "default" : -1}
                     ,{"name":"sessionid","type":["null","string"], "default" : null},{"name":"skills","type":["null", {"type":"array","items":"string"}], "default" : null}
                     ,{"name":"eventduration","type":["null","double"], "default" : null},{"name":"eventcount","type":["null","long"], "default" : null}
                     ,{"name":"is_latest","type":["null","boolean"], "default" : null},{"name":"keyset","type":["null","bytes"], "default" : null}
                     ,{"name":"addresses","type":["null", {"type":"array",
                        }}], "default" : null
Popular Apache Projects
Popular Bigquery Projects
Popular Web Servers Categories
Related Searches

Get A Weekly Email With Trending Projects For These Categories
No Spam. Unsubscribe easily at any time.