Google BigQuery Integration

BigQuery is a serverless data warehouse providing scalable data analysis. Most business intelligence tools support data import from Google BigQuery giving access to the video analysis results in the familiar tools. Ultinous AI Suite produces alarms, counting and object detection information which can be made available as database records in Google BigQuery.

Before you start

Please make sure that you have:

• A registered Google account for Google Cloud.
• A new Google Cloud Project is created by you or your system administrator, and your Google Account has access to it.

Setup Google BigQuery

Create Credentials

1. Login to Google Cloud Console.

   

2. Open the hamburger menu on the upper left corner of the screen -> click APIs & Services.

   

3. Click Credentials.

4. Click + Create Credentials -> select Service Account

   

5. Fill in the Service account name field: e.g. "AI Suite Service Account".

   

6. You may leave the Service Account ID as default (auto generated).

7. Click the Create and Continue button.

8. The credentials are added to your Service Accounts list. Click on the new list item.

   

9. Click the KEYS tab.

   

10. Click Add Key -> Create new key.

11. Select .json format.

    

12. Click the Create button.

13. Download the .json file.

NOTE: Make sure you don't lose this file for it cannot be obtained again without creating new credentials as well. This file will have to be uploaded to Ultinous AI Suite in a later step.

14. Click the DETAILS tab.

    

15. Copy the text in the Email section.

Create a BigQuery dataset

1. Go to the hamburger menu on the upper left corner of the screen -> Click BigQuery.

2. Find your project and click the ... button next to it -> Click Create Dataset.

3. Add a new ID. and click the Create Dataset button.

Connect your Service Account with the new dataset

1. Find your new dataset and Click ... button next to it -> Share

2. In the Dataset Permission panel, click Add Principal.

3. In the New principal field, paste the Service Account Email text you have copied in the Create Credentials section's steps.

4. Set the Role to BigQuery Data Editor.

5. Click SAVE.

Connect Ultinous AI Suite with your Google BigQuery dataset

Add your credentials to Ultinous AI Suite

1. Go To Control Panel -> Integration.

2. Scroll down to the Google BigQuery Credentials section, then upload the .json file you have downloaded in the Create Credentials step.

3. Once it's successfully uploaded you can test if everything is set up correctly: To do so, type your dataset name in the Test Credentials section and click the button under it. If your settings are correct, a "Dataset accessed successfully" message will appear.

Enable BigQuery integration in your solution

1. Register a new solution in your Solutions menu.

2. In it configuration form, scroll down to Integration, then tick Google BigQuery.

   

3. Type your dataset name.

4. Add a table name. It will be created automatically if it does not exist yet.

   NOTE: Consider using the same table in which you would like to handle data from the same site.

5. Optionally, enter a bucket name for snapshots. If enabled, snapshots of events will be stored in Google Cloud Storage bucket of the same name (needs to be created beforehand). The same bucket may be used for multiple solutions' snapshots.

Note that Metadata Output solutions do not have snapshot functionality.

Using the analytics data

NOTE: In extremely rare cases Google inserts a record multiple times. To filter duplicate records you can use SELECT DISTINCT * .... in your query.

Traffic Counter data

Field Name	Type	Mode	Info
timestamp	TIMESTAMP	NULLABLE	The time of the line crossing.
index	INTEGER	NULLABLE	Since multiple objects can cross lines at the same time, these occurrences are differentiated by this value.
direction	STRING	NULLABLE	One of "IN", "OUT".
site_name	STRING	NULLABLE	The Site name of the triggering camera set in the Cameras menu.
camera_display_name	STRING	NULLABLE	The Display name of the triggering camera set in the Cameras menu.
camera_technical_name	STRING	NULLABLE	The Technical name of the triggering camera set in the Cameras menu.
object_type	STRING	NULLABLE	One of "PERSON_FULL_BODY ", "CAR", "BUS", "TRUCK", "MOTORCYCLE", "BICYCLE", "TRAIN", "BOAT", "AIRPLANE".
display_name	STRING	NULLABLE	Human readable name of source Traffic Counter configuration
technical_name	STRING	NULLABLE	Unique name of source Traffic Counter configuration
schema_version	INTEGER	NULLABLE	Version number of the table schema associated with the record

Multi Object Counter data

Field Name	Type	Mode	Info
from_timestamp	TIMESTAMP	NULLABLE	The starting timestamp of the aggregation interval (inclusive)
to_timestamp	TIMESTAMP	NULLABLE	The end timestamp of the aggregation interval (exclusive)
status	STRING	NULLABLE	One of "OK", "ERROR"
site_names	STRING	REPEATED	The list of Site names of the triggering cameras set in the Cameras menu.
cameras_with_error	STRING	REPEATED	The Technical names of the cameras in error state during the aggregation interval
object_type	STRING	NULLABLE	One of "PERSON_FULL_BODY ", "CAR", "BUS", "TRUCK", "MOTORCYCLE", "BICYCLE", "TRAIN", "BOAT", "AIRPLANE".
first	INTEGER	NULLABLE	The number of objects in the first record of the aggregation.
last	INTEGER	NULLABLE	The number of objects in the last record of the aggregation.
min	INTEGER	NULLABLE	The minimum number of objects in the aggregation.
max	INTEGER	NULLABLE	The maximum number of objects in the aggregation.
average	NUMBER	NULLABLE	The average number of objects in the aggregation.
median	INTEGER	NULLABLE	The average number of objects in the aggregation.
display_name	STRING	NULLABLE	Human readable name of source Multi Object Counter configuration
technical_name	STRING	NULLABLE	Unique name of source Multi Object Counter configuration
schema_version	INTEGER	NULLABLE	Version number of the table schema associated with the record

Metadata Output data

Field Name	Type	Mode	Info
frame_timestamp	TIMESTAMP	NULLABLE	The time of the video frame.
index	INTEGER	NULLABLE	Since multiple objects can appear on the same frame, these occurrences are differentiated by this value.
technical_name	STRING	NULLABLE	Unique name of the source Metadata Output configuration.
camera_display_name	STRING	NULLABLE	The Display name of the triggering camera set in the Cameras menu.
camera_technical_name	STRING	NULLABLE	The Technical name of the triggering camera set in the Cameras menu.
camera_site_name	STRING	NULLABLE	The Site name of the triggering camera set in the Cameras menu.
geo_coordinate_latitude	NUMERIC	NULLABLE	The latitude of the GPS coordinates set in the Cameras menu, in decimal format.
geo_coordinate_longitude	NUMERIC	NULLABLE	The longitude of the GPS coordinates set in the Cameras menu, in decimal format.
type	STRING	NULLABLE	One of "PERSON_FULL_BODY ", "CAR", "BUS", "TRUCK", "MOTORCYCLE", "BICYCLE", "TRAIN", "BOAT", "AIRPLANE".
x	INTEGER	NULLABLE	X coordinate of the top left point of the bounding box.
y	INTEGER	NULLABLE	Y coordinate of the top left point of the bounding box.
width	INTEGER	NULLABLE	Width of the bounding box.
height	INTEGER	NULLABLE	Height of the bounding box.
detection_confidence	NUMERIC	NULLABLE	Detection confidence in (0, 1]
track_id	STRING	NULLABLE	Track ID of the detection.
predicted	BOOLEAN	NULLABLE	True if the detection is based on tracking-based prediction.
end_of_track	BOOLEAN	NULLABLE	True if the record indicates end of a track.
resolution_width	INTEGER	NULLABLE	Width of the frame.
resolution_height	INTEGER	NULLABLE	Height of the frame.

Event data

Field Name	Type	Mode	Info
id	STRING	NULLABLE	The unique ID of the event record.
timestamp	TIMESTAMP	NULLABLE	The time of the event.
display_name	STRING	NULLABLE	Human readable name of source Alert configuration.
type	STRING	NULLABLE	The type of the Alert. One Of "CROWD_DETECTION", "INTRUSION_DETECTION", "MULTI_OBJECT_DETECTION", "COUNTER", "ZONE_CROSSING", "LOITERING_DETECTION", "STOPPED_DETECTION", "WRONG_DIRECTION_DETECTION", "PPE_DETECTION".
camera_id	STRING	NULLABLE	The ID of the triggering camera.
camera_display_name	STRING	NULLABLE	The Display name of the triggering camera set in the Cameras menu.
camera_technical_name	STRING	NULLABLE	The Technical name of the triggering camera set in the Cameras menu.
site_names	STRING	REPEATED	The list of Site name of the triggering cameras set in the Cameras menu.
frame_timestamp	TIMESTAMP	NULLABLE	The time of the triggering video frame.
schema_version	INTEGER	NULLABLE	Version number of the table schema associated with the record.
snapshot_url	STRING	NULLABLE	Google Storage URL referring to the snapshot attached to the event, in the following format: gs://bucket_name/object_name
metadata_json	JSON	NULLABLE	Metadata of the event.*

*See the relevant part of the integration guide for detailed description of the Event schema.