1. Indicative
  2. Tech Docs
  3. Integration Partners

GCP BigQuery Integration

FreeProEnterprise

Overview

“BigQuery is Google's serverless, highly scalable, low cost enterprise data warehouse designed to make all your data analysts productive. Remove the headache of planning for data warehouse capacity and reach for infinity with elastic capacity scaling that has no limit. Google BigQuery meets the challenges of real-time analytics by leveraging Google’s serverless infrastructure that uses automatic scaling and high-performance streaming ingestion to load data.

BigQuery supports a standard SQL dialect which is ANSI:2011 compliant, reducing the need for code rewrite and allowing you to take advantage of advanced SQL features. BigQuery provides free ODBC and JDBC drivers to ensure your current applications can interact with BigQuery’s powerful engine.  With Indicative for BigQuery, take the enhanced and powerful data insights from Indicative to the next level by combining it with other popular BI tools like Tableau, MicroStrategy, Looker, Google DataStudio.” - Google Cloud Platform

With Indicative for BigQuery, your data analysis potential is limitless.

BigQuery as a Source

The BigQuery integration with Indicative is available for Enterprise customers only. If interested, please contact us. You must grant 'bigquery.dataViewer' access to Indicative for your BigQuery project. 

In order to perform the following steps you must have administrative access to the BigQuery console as well as your BigQuery database.

BigQuery Integration

1. Open the BigQuery console on Google Cloud Platform and Select a project.

2. In the menu panel along the left side of your screen, under Resources, click on the triangle to the left of your project name to expand the view. This will display the available datasets.
 
3. Select a dataset from Resources, then click Share dataset near the right side of the window.
 
share_dataset.png
 
4. In the Share dataset panel, in the Dataset permissions tab, click Add members.
 
5. In the Add members panel, enter "integrations@indicative-988.iam.gserviceaccount.com" into the New members text box.
 
6. For Select a role, select BigQuery and choose BigQuery Data Viewer.
 
Screen_Shot_2020-06-29_at_2.29.07_PM.png
 
7. Click Done.
 
8. Grant Indicative the necessary information to move forward with your integration by completing this Data Integration Questionnaire.
 
If you have any questions or concerns about the above Integration, please contact your Customer Support Manager, or email support@indicative.com.
 

BigQuery as a Destination

Overview

Indicative for BigQuery is a fully managed data warehousing service that encapsulates the effort required to fully support, maintain and load your Indicative data into BigQuery. Because there is no infrastructure to manage, you can focus on analyzing data to find meaningful insights. The modern and infinitely scalable data warehouse provided by BigQuery combined with the enriched data from Indicative allows you to leverage additional value from your investment through a built-in SQL interface.

The BigQuery Export Integration provided by Indicative allows customers to easily export their raw Indicative data to BigQuery for further analysis via a SQL interface. The purpose of this section is to provide an overview of how Indicative loads raw data into BigQuery and what customers need to provide to configure and maintain the integration.

This document is intended for customers who are familiar with both Google BigQuery and the Indicative data model.  

Getting Started

In order to use the BigQuery Export Integration for Indicative, the customer must provide programmatic BigQuery access to the Indicative Platform. The customer is required to grant dataViewer BigQuery access to Indicative.

In order to perform the following steps you must have administrative access to the BigQuery Console as well as your BigQuery database.

Create a BigQuery Dataset (Optional)

  1. Open the BigQuery web UI in the GCP Console.

  2. In the navigation panel, in the Resources section, select your project.

  3. On the right side of the window, in the details panel, click Create dataset.

  4. On the Create dataset page:

    • For Dataset ID, enter a unique dataset name.

    • (Optional) For Data location, choose a geographic location for the dataset. If you leave the value set to Default, the location is set to US. After a dataset is created, the location can't be changed.

    • For Default table expiration, choose one of the following options:

      • Never: (Default) Tables created in the dataset are never automatically deleted. You must delete them manually.
      • Number of days after table creation: This value determines when a newly created table in the dataset is deleted. This value is applied if you do not set a table expiration when the table is created.

    • Click Create dataset.

Share Your Dataset with Indicative

1. In the menu panel along the left side of your screen, under Resources, click on the triangle to the left of your project name to expand the view. This will display the available datasets.
 
2. Select a dataset from Resources, then click Share dataset near the right side of the window.
 
share_dataset.png
 
3. In the Share dataset panel, in the Dataset permissions tab, click Add members.
 
4. In the Add members panel, enter "integrations@indicative-988.iam.gserviceaccount.com" into the New members text box.
 
5. For Select a role, select BigQuery and choose bigquery.dataViewer.
 
6. Click Done.
 
7. Contact your account manager once you have granted Indicative 'bigquery.dataViewer' access to ensure that the connection is properly configured.
 

Advanced Information 

Partitioning

It is recommended that you partition your data based on the ingestion time, rather than the date in which each event was performed. Under this methodology, once a table is published in BigQuery, it is not amended or changed. Instead, a new table is added, which includes the supplemental data from previous days.

Schema

Each event type passed to Indicative is represented as a separate table within the BigQuery schema. Within each event table exist columns representing the event’s timestamp and unique ID. The remaining columns correspond to the different properties of each event type.

Custom Event Table Schema (Illustrious)

User Subscribe 

TIMESTAMP
  
ID
  
Platform
  
CouponCode
  
PlanType
2018-03-17 04:32:11.324
  
ABC12345
  
iOS
  
THANKS
  
1m
2018-03-17 13:14:45.567
  
54321XYZ
  
iOS
  
THANKS
  
1m
2018-03-17 16:32:38.489
  
30093ERI
  
Android
  
SUBWAY
  
12m
2018-03-18 02:45:19.223
  
CDB32103
  
iOS
  
SUBWAY
  
3m
2018-03-19 18:39:22.301
  
ABZ30345
  
Android
  
INTRO
  
12m


Logged in

TIMESTAMP
  
ID
  
iOSVersion
  
PlanID
  
PaymentType
2018-03-10 08:22:41.444
  
30093ERI
  
Not defined
  
Intro_1m
  
PayPal
2018-03-13 23:09:17.320
  
CDB32103
  
11.2.1
  
Explore_12m
  
CC
2018-03-14 01:42:01.004
  
ABC12345
  
10.8.0
  
Explore_12m
  
Paypal
2018-03-17 18:32:14.213
  
54321XYZ
  
11.2.5
  
Intro_1m
  
CC

 

Alias Table Schema

Indicative supports aliasing between anonymous IDs and user IDs to allow customers to unify event streams submitted with separate unique keys. Aliasing is typically used to connect the activity stream of an anonymous user to their known activity stream after they have been identified as a known user

An anonymous ID is an ID used to identify a user before they’ve registered, logged in, or otherwise identified themselves. A user ID is the ID used to uniquely identify a single user within your application

After receiving an alias call, all data submitted in the future from either the anonymous ID or a user ID will be processed as coming from the same user. As a best practice, we recommend that an alias call between a pair of IDs is made exactly once. It’s sensible to make this call the first time a user identifies themselves, such as upon registration within your application.

TIMESTAMP
  
PreviousID
  
NewID
2018-03-13 04:21:45.223
  
DLZ3312
  
4213DER_15
2018-03-10 19:57:11.312
  
HVF4829
  
0938PLZ_34
2018-03-10 08:33:25.522
  
YUH4737
  
3921UEX_99

 

Identify Table Schema

Use the Identify call to set or update user attributes of a particular user without sending an event. The Identify table schema is almost identical to the custom event table schema. It contains a timestamp, user ID and relevant properties.

User Charge

TIMESTAMP
  
ID
  
Country
  
PlanID
  
PayMethod
  
2018-03-11 09:12:41.554
  
YUH4737
  
USA
  
M2M_111
  
CC
  
2018-03-14 21:19:17.590
  
CDB32103
  
USA
  
yearly_302
  
CC
  
2018-03-15 03:43:01.104
  
HVF4829
  
CAN
  
yearly_522
  
Paypal
  
2018-03-19 19:24:14.003
  
54321XYZ
  
PHI
  
M2M_111
  
CC
  

 

 

Additional Services

Google’s BigQuery data warehouse also supports additional data sets from within your existing infrastructure. Indicative is happy to assist customers with loading additional data sets into BigQuery. For more information, please contact support@indicative.com.

3 users found this helpful