Skip to main content
Version: 3.1.0

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 new the 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.

  1. Click the DETAILS tab.

  2. 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.
  1. Find your project and click the ... button next to it -> Click Create Dataset.
  1. 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
  1. In the Dataset Permission panel, click Add Principal.
  1. In the New principal field, paste the Service Account Email text you have copied in the Create Credentials section's steps.

  1. Set the Role to BigQuery Data Editor.

  2. Click SAVE.

Connect Ultinous AI Suite with your Google Bigquery dataset​

Add your credentials to Ultinous AI Suite​

  1. Go To Control Panel -> Integration.
  1. Scroll down to the Google BigQuery Credentials section, then upload the .json file you have downloaded in the Create Credentials step.
  1. 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.

    NOTE: Currently, only the Traffic Counting solution supports Google BigQuery integration. All solutions will support it in a future release.

  2. In the new solution's parameters, click Enable Bigquery integration.

  3. Type your dataset name.

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

    NOTE: For the same site, consider using the same table where you would like to handle data from the same site.

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 counting data​

Field NameTypeModeInfo
timestampTIMESTAMPNULLABLEThe time of the line crossing.
indexINTEGERNULLABLESince multiple objects can cross lines at the same time, these occurrences are differentiated by this value.
directionSTRINGNULLABLEOne of "IN", "OUT".
site_nameSTRINGNULLABLECurrently always null. Will be used in a future release.
camera_display_nameSTRINGNULLABLEThe Display name of the triggering camera set in the Cameras menu.
camera_technical_nameSTRINGNULLABLEThe Technical name of the triggering camera set in the Cameras menu.
object_typeSTRINGNULLABLEOne of "PERSON_FULL_BODY ", "CAR", "BUS", "TRUCK", "MOTORCYCLE", "BICYCLE", "TRAIN", "BOAT", "AIRPLANE".