Querying Raw Analytics Data in BigQuery

Validating Data with Raw Data

After you collect game events, you need to check whether player actions were recorded at the intended time and whether all the values needed for analysis are included. If the query period is long or you need to apply several conditions together, you may need to check the raw events directly.

For example, if an event you sent does not appear in Hive Analytics, check whether it failed to meet the collection criteria and was stored in the quarantine table. If aggregated numbers are higher than expected, you can check whether the same event is being sent more than once, and you can also use the raw logs to see whether abnormal values keep appearing for a particular period, app, or country. When store settlement amounts differ from revenue metrics, you need to compare large volumes of purchase logs with the settlement data to find where the difference occurs.

In BigQuery, you can query the data you need for these situations with SQL and perform preprocessing such as removing duplicates, converting time zones, and extracting properties. This lets you pinpoint the causes of collection errors and aggregation differences that are hard to see from the results shown on screen alone.

The BigQuery Query Environment in Hive Analytics

Hive Analytics provides an environment where you can query collected event data directly in BigQuery. The process from requesting access to running SQL is as follows.

1. Prepare a Google account
   Prepare a Gmail account or the Google account you use at work to access BigQuery.
2. Request access
   A user with Hive Analytics administrator permission registers the Google account in BigQuery Access Permission Management in the account menu. Access applies only to the Hive Analytics console environment where you requested it, so if you need to query both production and sandbox data, request access in each environment. After requesting, check the processing status, such as Pending, Approved, or Rejected, in Requests. Access is usually granted immediately, but if the request is pending, it can take up to 2 business days.
3. Prepare a Google Cloud project
   Prepare a Google Cloud project to run SQL. If you do not have a Google Cloud billing account, you can start within a limited free tier by setting up the BigQuery sandbox. For setup steps and limitations, see Try BigQuery using the sandbox in the Google Cloud documentation.
4. Connect the Hive Analytics project
   Add the habq-storage project to your favorites in the BigQuery Explorer. If the project does not appear, select Star a project by name under Add data and enter habq-storage.
5. Check datasets and tables
   Expand the project to see the datasets and event tables you have been granted access to. Production data is stored in habq-storage.analytics_N_live, and sandbox data in habq-storage.analytics_N_sandbox. The N in the dataset name is an identifier for each company, and only the datasets you can access appear in the Explorer, so you do not need to look up this value separately. For the types and purposes of the tables you can query and their main columns, see BigQuery Tables on Hive Developers.
6. Run SQL
   To run SQL, you need the BigQuery Job User role in the Google Cloud project you selected. Projects created with the BigQuery sandbox can run queries up to the free usage limit without additional permission settings. Select the project, then run the query in the SQL editor.

For accounts that no longer need to query raw data because of a change in duties or leaving the company, request revocation in BigQuery Access Permission Management in Hive Analytics. Keeping only the accounts and environments you need lets you clearly manage the scope of access to raw data.

For detailed steps on requesting and revoking BigQuery query access and preparing a Google Cloud project, see BigQuery Setup on Hive Developers.

Try Querying Raw Event Data in BigQuery

For example, suppose you want to check the basic information and properties of contents_success events collected in the production environment. Open Google BigQuery, then select the Google Cloud project to run SQL in the project selector at the top left of the BigQuery console. Open the SQL editor and set up the query conditions as follows.

SELECT
  userId,
  deviceId,
  appIdGroup,
  appId,
  DATETIME(TIMESTAMP_ADD(dateTime, INTERVAL 32400 SECOND)) AS dateTime_kst,
  attributes
FROM `habq-storage.analytics_N_live.t_raw_event_log`  --analytics_N_live: production dataset you have access to
WHERE identifierProvider = 'hive'
  AND eventName = 'contents_success'  --event name to query
  AND appIdGroup = 'com.sample.test'  --actual appIdGroup of the game to query
  AND dateTime >= TIMESTAMP_SUB(TIMESTAMP('2026-10-01 00:00:00'), INTERVAL 32400 SECOND)  --start date of the query period
  AND dateTime < TIMESTAMP_SUB(TIMESTAMP('2026-10-08 00:00:00'), INTERVAL 32400 SECOND)  --end date of the query period
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY checksum
  ORDER BY bigqueryRegistTimestamp DESC
) = 1
LIMIT 100

Before running the query, change the values marked in the comments to match your environment.

dateTime is stored in UTC, so the example adds 9 hours to display it in KST. To set the query period in KST as well, subtract 9 hours from the start and end times to convert them into UTC conditions.

If the same event is sent again, duplicate data may be stored. The QUALIFY clause in the example keeps only the row with the most recent bigqueryRegistTimestamp among rows with the same checksum. Apply deduplication criteria that suit your analysis purpose and how events are sent.

t_raw_event_log is a table partitioned by day on dateTime. By including a date condition in your query, you can query only the partitions you need and reduce query costs. Checking the estimated amount of data to be processed that BigQuery shows before you run the query also helps reduce unnecessary data queries and costs.

Query results include not only player and app information but also event properties stored in attributes. To view a specific property as its own column, extract the value with JSON_VALUE and type conversion functions.

Get Started with Hive Analytics

Once your BigQuery query environment is ready, you can check specific events and property values under the conditions you want and compare how metrics are aggregated against the raw data.

Hive Analytics is an analytics solution built for games that provides an environment for querying events collected through the Hive SDK directly in BigQuery. Request BigQuery query access in Hive Analytics now and explore your game data directly.