BigQuery

BigQuery is a fully managed cloud-based data warehouse and analytics service for storing and analyzing data.

This integration sends your Airship Real-Time Data StreamingA service that delivers user-level events in real time to your backend or third-party systems using the Data Streaming API. event data as batched CSV files to a Google Cloud Storage (GCS) bucket. BigQuery loads those files into tables through the Data Transfer Service or LOAD DATA statements.

After configuring the data export, you create BigQuery tables using provided event schemas, and then load the CSV files from GCS into BigQuery using Google’s Data Transfer Service, BigQuery’s LOAD DATA statements, or another data pipeline method. The event schemas help you create the appropriate BigQuery tables and ensure your data loads correctly.

This integration gives you flexibility in designing your data pipeline while maintaining full control over your data. Exports run hourly, but you can choose how frequently to load data into BigQuery and implement the data transfer method that best fits your infrastructure and requirements.

Requirements

This integration requires the following:

• A Google Cloud Platform account with BigQuery and Cloud Storage enabled
• An Airship account with messaging and Real-Time Data Streaming

Configure Data Export in Airship

Complete the Airship Data Export integration to send your RTDS event data to a Google Cloud Storage bucket. Select GCS as your storage provider and configure the integration according to your needs.

Create Airship tables in BigQuery

Copy the SQL DDL script from the BigQuery event schemas reference page and run it in the BigQuery Studio SQL editor, or with bq query --use_legacy_sql=false. The script creates an AIRSHIP_EVENTS dataset and one table per event type, with column definitions that match the CSV files exported by Data Export.

When Airship adds new event types or new columns to existing events later, re-run the corresponding CREATE TABLE or ALTER TABLE statements to keep your tables in sync.

Load data into BigQuery tables

Once your tables are created and data is being exported to GCS, load the CSV files into your BigQuery tables. You can use any of the following methods:

• Google Data Transfer Service — Automatically transfer data from GCS to BigQuery on a schedule.
• BigQuery LOAD DATA statements — Manually or programmatically load data using SQL.
• Other data pipeline tools — Use your preferred ETL tool or custom scripts.

Airship generates one file per event type per hour when events occur, and organizes them in your GCS bucket by the file layout you selected during Data Export setup: Standard, Simplified, or Flattened. Each file includes a header row. If you plan to load data with Google Data Transfer Service, use the Simplified layout to avoid namespace collisions. See CSV structure and files in Airship Data Export for full details on each layout.

"id","offset","occurred","processed","app_key","channel","device_type","named_user","custom_identifiers","locale_variant","locale_country_code","locale_timezone","locale_language_code","iana_timezone","app_version","device_model","ua_sdk_version","push_opt_in","device_os","location_enabled","location_permission","background_push_enabled","web_browser_name","web_browser_type","web_browser_version","web_user_agent_string","open_platform_name","alerting","campaigns","push_id","group_id","variant_id"
"f5e05251-b128-11ec-8a12-0242eb00e5a9","1000042149690","2022-03-31 19:30:02.357 +00:00","2022-03-31 19:30:14.867 +00:00","YOUR_APP_KEY","b1ab9a9e-9634-4fb4-875a-9a02dcd68e66","ANDROID","","","","FR","7200","fr","Europe/Paris","2022-02-09T000134-goat","VOG-L29","16.3.0","true","10","false","NOT_ALLOWED","true","","","","","","true","","f5d2bdc0-b128-11ec-bae4-024205acadbe","ac4058f7-981b-4f0e-b9c4-8b7af3a647da",""
"f8fb4df1-b128-11ec-a0e3-0242e24c72c5","1000042149691","2022-03-31 19:30:07.567 +00:00","2022-03-31 19:30:16.760 +00:00","YOUR_APP_KEY","b1ab9a9e-9634-4fb4-875a-9a02dcd68e66","ANDROID","","","","FR","7200","fr","Europe/Paris","2022-02-09T000134-goat","VOG-L29","16.3.0","true","10","false","NOT_ALLOWED","true","","","","","","true","","f6586880-b128-11ec-99dd-02423b3f5d45","879056c8-0b34-4c8c-8cdc-1df4f3d40b40",""
"9aa98182-e854-4fa7-9c9f-ddea2082cc4c","1000042149694","2022-03-31 19:33:51.225 +00:00","2022-03-31 19:33:51.416 +00:00","YOUR_APP_KEY","62d92a24-0ced-40d1-ad1d-e1ea953189b7","SMS","","{""sender"":""17372004196""}","","","","","","","","","","","","","","","","","","","true","","75fdde30-b129-11ec-99dd-02423b3f5d45","0e2a3d4b-b4b4-4208-838f-08eb7333aa5e",""

Set skip_leading_rows = 1 in every load to skip the header row. BigQuery handles the other common CSV parsing cases automatically:

• Detects common timestamp formats
• Handles quoted fields
• Treats empty strings as NULL values

The example below shows how to load data using BigQuery’s LOAD DATA statement:

LOAD DATA INTO `PROJECT_ID.AIRSHIP_EVENTS.TAG_CHANGE`
FROM FILES (
  uris = ['gs://your-bucket/YOUR_APP_KEY/gcs/19c15b67-1dd5-46b9-a25a-dfbd5d60ce58/TAG_CHANGE/2022_03_31/*.csv'],
  format = 'CSV',
  skip_leading_rows = 1
);